C language API

Considerations

• Retrieved NULL fields will contain the data file NULL symbol as defined in your project config file (by default this is =).
• Any embedded underscores present in retrieved fields will have been converted to spaces, and it will be the responsibility of the application to convert spaces to underscores as appropriate.
  
  
  

Function library

Most of these functions take an argument called dbc which is an integer selector that allows multiple channels of concurrent SQL access. Normally 0 is used, but if a retrieval on channel 0 is currently in progress, a channel of 1 or higher value may be used. Up to 4 channels may be active simultaneously (shsql.h MAXCONNECTS).

All functions are of type int. See simple.c in the source code directory which demonstrates the use of this API. There is no concept of connect or disconnect.

SHSQL_allconfig()

Read project config file and initialize. This function must be called before any other. Function returns 0 if OK, or a non-zero error code.

SHSQL_sql( int dbc, char *sql )

Submit an SQL command. dbc is the channel selector. sql is an SQL command. For SELECT commands, subsequent call(s) to SHSQL_getrow() will be required to fetch the results. Function returns 0 if the SQL command was processed normally, or an error code.

SHSQL_getrow( int dbc, char *fields[], int *n )

Get one row of results from the most recently submitted SELECT command on channel dbc. Each fields pointer will be set to reference one result field; there should be enough array members to accomodate your retrievals (80 recommended). For example, an SQL statement of select lastname, id from.. should return 2 fields on each returned row; lastname will be found in fields[0] and id in fields[1]. The pointers reference memory that will be available until the next SQL command is submitted on the channel. All result fields are represented as character strings, including NULL which will be indicated using the internal null symbol which is equals-sign (=) by default. n will be set to the number of fields in the result. Function returns 0 if a row was fetched, 1 if there are no more rows, or an error code.

SHSQL_pushrow( int dbc )

Causes the next call to SHSQL_getrow() to get the same row again.

SHSQL_getnames( int dbc, char *fields[], int *n )

Get the names of the result fields from the most recently submitted SELECT command on channel dbc. Each fields pointer will be set to reference one result field name; there should be enough array members to accomodate your retrievals (shsql.h MAXITEMS) The pointers reference memory that will be available until the next SQL command is submitted on the channel. n will be set to the number of fields in the result. Function returns 0 if OK, or a non-zero error code.

SHSQL_tabdef( char *table, char *fields[], int *n )

Get the names of fields in table.

SHSQL_getnrows( int dbc )

Return the number of rows retrieved (or inserted, updated, or deleted) by the most recently submitted SQL command on channel dbc. For SELECT commands, the row count will be exact after all rows are fetched via SHSQL_getrow() but it may not be exact before this time because of late processing related to DISTINCT, GROUP BY, or LIMIT. However, it is always true that a count of zero indicates no rows found and a count greater than zero indicates at least one row found. The row count associated with a SELECT INTO is always exact.

SHSQL_writable()

Returns 0 if the current process can write to the database directory. Returns 1 if readonly is set in the config file. Returns 2 if writing is prevented by unix file or directory permissions.

Convenience routines

SHSQL_getitem( char *sql, char *result )

Get a single item from the database on channel 0. sql is an SQL SELECT command that retrieves one field, and the result field will be copied into result. Function returns 0 on success, or a non-zero error code.

SHSQL_getitems( char *sql, char *fields[] )

Get multiple items from the database on channel 0. sql is an SQL SELECT command that retrieves one or more fields. fields is an array of character pointers which will be set to reference the result fields in memory that will continue to be available until the next SQL command is submitted on channel 0. Function returns 0 on success, or a non-zero error code.

Additional contributed routines

The following routines have been contributed by Mitch Bradley (wmb at agile.tv), March 2006. They're untested by me but appear to be useful. I've put the code in file api2.c (available in 1.29+ distributions or see the shsql what's new page). To include them in libshsql edit sqlsrc/Makefile and add api2.o to the SQLOBJ list, then build.

int SHSQL_query(int dbc, char *formatstr, ...)

Constructs an executes a query from a printf-style format string and a list of variables. It is similar to:

     char query[MAXQUERY];
     snprintf(query, MAXQUERY, formatstr, ...);
     SHSQL_sql(dbc, query);

int SHSQL_formatrow(int dbc, char *formatstr, ... )

Invokes SHSQL_getrow() and extracts the result columns via pointers to variables in the varargs list, with data type conversion and error checking. The format string is of the form "ccccc", where each character 'c' specifies the conversion for the next column of the query. The format letters are:

     s - string (arg is char**)
     d - decimal decimal integer (arg is int*)
     u - unsigned decimal integer (arg is unsigned int*)
     x - hexadecimal unsigned integer (arg is unsigned int*)
     i - integer with base specified by string (e.g. 0x1a) (arg is int*)
     y - yes/no integer (arg is int*)
     case-independent "yes" or "y" or "true" or "t" yields 1
     anything else yields 0

If successful, returns the (positive) length of the format string. Otherwise returns:

     0:    no more rows for this database query
    -1001: database query returned an error code
    -1002: database query returned a different number of values
              than the length of the format string
    -N:    conversion error for field N-1 (e.g. -1 for field 0).

If the format string is empty, it is impossible to distinguish between success and "no more rows". If you wish to count rows with a no-results query, use SHSQL_getnrows().