n_settsql(), n_exectsql() - Prepare a SQL statement for processing

SYNOPSIS

#include "tstone.h"

int n_settsql(ts,stmt)
TSQL   *ts;
char   *stmt;

int n_exectsql(ts,...)
TSQL   *ts;


DESCRIPTION
n_settsql() takes a SQL statement, parses it, and prepares to execute it. All SQL statements must end with a semi-colon (;). Only one SQL statement at a time may be included in the stmt argument. The statement may contain printf like formatting codes. These formatting codes may appear anywhere in the SQL statement that data constants would otherwise appear (e.g.: as the values for insert or the data to perform comparisons on in a select). The data for the formatting codes are provided via the n_exectsql() function.

The following table summarizes the format codes and their respective data types. See the formatting codes man page for full descriptions.

Format Description C Type SQL Type
%b Raw binary data byte * BYTE
%p%n Raw binary data byte * BYTE
%s Character string char * CHAR
%lf Double precision floating point double DOUBLE
%lu Unix date stored as time_t long DATE
%f Single precision floating point float FLOAT
%d 32/64-bit signed integer long INTEGER
%ld 32/64-bit signed integer long INTEGER
%hd 16-bit signed integer short SMALLINT
%w 16-bit unsigned integer word UNSIGNED SMALLINT
%s Name of external file char * INDIRECT
%< Name of external file char * INDIRECT
%dw 32-bit unsigned integer dword UNSIGNED INTEGER
%64d 64-bit signed integer EPI_INT64 INT64
%64u 64-bit unsigned integer EPI_UINT64 UINT64
%c Unique serial number ft_counter * COUNTER
%ls A list of allocated strings char ** STRLST

n_exectsql() takes a variable argument list that is a list of pointers to the data to be passed into the query. n_exectsql() may be issued as many times as desired for the same statement. When issuing a statement where you want to get the resultant rows, such as SELECT, you will need to use n_gettsql() (see its man page). Variables passed to n_exectsql() are not modified or free()'d.

SELECT statements will always generate result rows. By default INSERT and DELETE statements will not. To enable result rows from INSERT and DELETE see n_resulttsql().

n_settsql() and n_exectsql() both return 0 on failure and non-0 on success.


EXAMPLE

/*
  This example loads records into a table called docs that has the
  following fields:

  Name    Type       Description
  ----    ----       -----------
  ctr     counter    a handy unique key field
  text    varchar    the text ocr'd off the image
  thumb   varbyte    a thumbnail of the original image
  image   indirect   the full size image

  Given a list of images, it OCR's any text, creates a small thumbnail,
  and uploads the original image file to the server.

  NOTE:  This example uses two fictitious calls, ocrimage() and
  shrinkimage(), to OCR images and make thumbnails of them.  We do
  not provide any such calls.  Also, Texis does not know any image
  formats.  Any image or other binary format data may be stored in
  a Texis field or indirect.
*/
TSQL   *ts;
char   *text;
byte   *thumbnail;
size_t  nthumbnail;
char  **imagefiles;
int     i, nimages;

   ...
              /* setup the insert statement for loading new records */
   if(n_settsql(ts,"insert into docs values (counter, %s, %p%n, %<);"))
   {
      for(i=0;i<nimages;i++)               /* each image to process */
      {
         text=ocrimage(imagefile[i]);              /* OCR the image */
                                                /* make a thumbnail */
         thumbnail=shrinkimage(imagefiles[i],&thumbnail);
                        /* execute the insert with the current data */
         if(!n_exectsql(ts,text,thumbnail,nthumbnail,imagefiles[i]))
            break;
      }
   }


EXAMPLE

/*
  This example accesses the same table described in the previous
  example.  Given a Metamorph query, it will retrieve all rows
  that have a match in the text field.
*/
TSQL   *ts;
char   *query;

   ...
                                    /* setup the select statement */
   if(n_settsql(ts,
      "select ctr,text,thumb,image from docs where text like %s;"))
   {
          /* execute the select with the supplied Metamorph query */
      if(n_exectsql(ts,query))
      {
         /* the n_gettsql() man page describes how to get results */
      }
   }
   ...


SEE ALSO

n_opentsql(), n_gettsql(), n_dotsql().


Copyright © Thunderstone Software     Last updated: Dec 10 2018
Copyright © 2019 Thunderstone Software LLC. All rights reserved.