User's Guide Part V. The SQL Anywhere Programming Interfaces Chapter 34. The Embedded SQL InterfaceThe SQL preprocessor generates calls to functions found in the interface library or DLL. In addition to the calls generated by the SQL preprocessor, there are several routines that are provided for the user to make database operations easier to perform. Prototypes for these functions are included by the EXEC SQL INCLUDE SQLCA command.
Following is a detailed description of these various functions by category. The prototypes given are those used in DOS and QNX.
The DLL entry points are the same except that the prototypes have a modifier appropriate for DLLs:
All pointers passed as parameters to the Windows DLL entry points or returned by these functions are far pointers.
For example, the first prototype listed below is db_init. For Windows, it would be:
unsigned short FAR PASCAL db_init( struct sqlca far *sqlca );
| Passing null pointers Care should be taken passing the null pointer as a parameter in Windows if your program is compiled in the small or medium memory models. You should use the _sql_ptrchk_() macro defined in SQLCA.H for any pointer parameter which is a variable that might contain the null pointer. This macro converts a null near pointer into a null far pointer. |
This section lists the functions concerned with initializing and releasing the interface.
unsigned short db_init( struct sqlca *sqlca );
Initializes the database interface library or DLL. This function must be called before any other library call is made and before any Embedded SQL command is executed. Resources required by the interface library for your program are allocated and initialized on this call. Use db_fini to free the resources at the end of your program. If there are any errors during processing, they will be returned in the SQLCA and 0 will be returned. If there are no errors, a non-zero value is returned and you can begin using Embedded SQL commands and functions.
In most cases, this function should be called only once (passing the address of the global sqlca variable defined in the SQLCA.H header file). If you are writing a DLL or an application that has multiple threads using Embedded SQL, you need to call db_init once for each SQLCA being used (see "Multi-Threaded or Reentrant Code").
| Caution Failure to call db_finifor each db_initon NetWare can cause the database server to fail, and the NetWare file serve to fail. |
unsigned short db_fini( struct sqlca *sqlca );
Frees resources used by the database interface or DLL. You must not make any other library calls or execute any Embedded SQL commands after db_fini is called. Any errors during processing will be returned in the SQLCA. If there are any errors during processing, they will be returned in the SQLCA and 0 will be returned. If there are no errors, a non-zero value is returned.
In most cases, this function should be called only once (passing the address of the global sqlca variable defined in the SQLCA.H header file). If you are writing a DLL or an application that has multiple threads using Embedded SQL, you need to call db_fini once for each SQLCA being used (see "Multi-Threaded or Reentrant Code").
| Caution Failure to call db_finifor each db_initon NetWare can cause the database server to fail, and the NetWare file serve to fail. |
The following functions provide a means to start and stop the database engine or SQL Anywhere Client (DBCLIENT.EXE), start or stop a database on an existing database engine or network database server; and connect to or disconnect from a database.
All of these functions take a NULL-terminated string as the second argument. This string is a list of parameter settings of the form KEYWORD=value, delimited by semicolons. The number sign "#" is an alternative to the equals sign, and should be used when setting the connection parameters string in the SQLCONNECT environment variable, as using "=" inside an environment variable setting is a syntax error.
The keywords are from the following table.
| Verbose keyword | Short form |
|---|---|
| Userid | UID |
| Password | PWD |
| ConnectionName | CON |
| EngineName | ENG |
| DatabaseName | DBN |
| DatabaseFile | DBF |
| DatabaseSwitches | DBS |
| AutoStop | AutoStop |
| Start | Start |
| Unconditional | UNC |
| DataSourceName | DSN |
Each function uses a subset of these parameters, but every function will allow any parameter to be set. An example connection parameter string is:
"UID=dba;PWD=sql;DBF=c:\sqlany50\sademo.db"
The SQLCONNECT environment variable can be used to specify default values for unspecified parameters (see "Registry entries and environment variables").
unsigned db_string_connect( struct sqlca * sqlca, char * parms );
Provides extra functionality beyond the Embedded SQL CONNECT command. This function will do the following steps:
The return value will be true (non-zero) if a connection was successfully established and false (zero) otherwise. Error information for starting the engine, starting the database or connecting will be returned in the SQLCA.
unsigned db_string_disconnect( struct sqlca * sqlca, char * parms );
Disconnects the connection identified by the ConnectionName parameter. All other parameters are ignored. If no ConnectionName parameter is specified in the string, the unnamed connection will be disconnected. This is equivalent to the Embedded SQL DISCONNECT command. The boolean return value will be true if a connection was successfully ended. Error information will be returned in the SQLCA.
This function will shut down the database if the database was started with the AutoStop=yes parameter and there are no other connections to the database. It will stop the engine if the engine was started with the AutoStop=yes parameter and there are no other databases running. It will stop the SQL Anywhere Client (DBCLIENT.EXE) if it was started with the AutoStop=yes parameter and there are no remaining connections through the SQL Anywhere Client.
unsigned db_start_engine( struct sqlca * sqlca,
char * parms );
Start the database engine or SQL Anywhere Client if it is not running. This function carries out the following steps:
The boolean return value is true if a database engine or SQL Anywhere Client was either found or successfully started. Error information will be returned in the SQLCA.
The following call to db_start_engine starts the database engine and names it sademo, but does not load the database, despite the DBF connection parameter:
db_start_engine( &sqlca, "DBF=c:\sqlany50\sademo.db; Start=DBENG50W" );
If you wish to start a database as well as the engine, you must include the database file in the START connection parameter:
db_start_engine( &sqlca,"ENG=eng_name;START=DBENG50W c:\sqlany50\sademo.db" );
This call starts the engine, names it eng_name, and starts the sademo database on that engine.
unsigned db_start_database( struct sqlca * sqlca, char * parms );
Start a database on an existing engine or network server if the database is not already running. This function will do the following steps:
The boolean return value will be true if the database was already running or successfully started. Error information will be returned in the SQLCA.
void db_stop_database( struct sqlca * sqlca,
char * parms );
Stop a database identified by DatabaseName on an engine or network server identified by EngineName. If EngineName is not specified, the default engine or network server will be used.
By default, this function will not stop a database that has existing connections. If Unconditional is yes, the database will be stopped regardless of existing connections.
void db_stop_engine( struct sqlca * sqlca,
char * parms );
Terminates execution of the database engine or SQL Anywhere Client (DBCLIENT.EXE). This function will do the following steps:
By default, this function will not stop a database engine or SQL Anywhere Client that has existing connections. If Unconditional is yes, the database engine or SQL Anywhere Client will be stopped regardless of existing connections.
This function can be used directly from a C program instead of spawning DBSTOP.
unsigned short db_find_engine( struct sqlca *sqlca, char *name );
Returns an unsigned short value indicating status information about the database engine whose name is name. If neither an engine nor multiuser server can be found with the specified name, the return value will be 0. A non-zero value indicates that the engine or client is currently running. Each bit in the return value conveys some information. Constants representing the bits for the various pieces of information are defined in the SQLDEF.H header file (see "SQLDEF.H header file"). If a null pointer is specified for name, then information is returned about the default database environment.
struct a_dblib_info *db_build_parms( struct sqlca *sqlca, char *connectstr, char *startstr );
Historical. See db_string_connect.
void db_destroy_parms( struct sqlca *sqlca, a_dblib_info *parms );
Historical. See db_string_connect.
void db_free_parms( a_dblib_info *parms );
Historical. See db_string_connect.
unsigned db_parms_connect( struct sqlca *sqlca, a_dblib_info *info );
Historical. See db_string_connect.
void db_parms_disconnect( struct sqlca *sqlca, a_dblib_info *info );
Historical. See db_string_disconnect.
unsigned db_start( struct sqlca *sqlca, a_dblib_info *info );
Historical. See db_start_engine and db_start_database.
void db_stop( struct sqlca *sqlca, char *name, short unconditional );
Historical. See db_stop_engine and db_stop_database.
The following functions are used to manage SQL Descriptor Areas (SQLDAs). "The SQL descriptor area (SQLDA)" describes the SQLDA structure in detail.
struct sqlda *alloc_sqlda_noind( unsigned numvar );
Allocates an SQLDA with descriptors for numvar variables. The sqln field of the SQLDA will be initialized to numvar. Space is not allocated for indicator variables; the indicator pointers are set to the null pointer. A null pointer will be returned if memory cannot be allocated.
struct sqlda *alloc_sqlda( unsigned numvar );
Allocates an SQLDA with descriptors for numvar variables. The sqln field of the SQLDA will be initialized to numvar. Space is allocated for the indicator variables, the indicator pointers are set to point to this space, and the indicator value is initialized to zero. A null pointer will be returned if memory cannot be allocated.
struct sqlda *fill_sqlda( struct sqlda *sqlda );
Allocates space for each variable described in each descriptor of sqlda and assigns the address of this memory to the sqldata field of the corresponding descriptor. Enough space is allocated for the database type and length indicated in the descriptor. Returns sqlda if successful and returns the null pointer if there is not enough memory available.
unsigned long sqlda_string_length( struct sqlda *sqlda, int varno );
Returns the length of a C string (type DT_STRING) required to hold the variable sqlda->sqlvar[varno] (no matter what its type is).
unsigned long sqlda_storage( struct sqlda *sqlda, int varno );
Returns the amount of storage required to store any value for the variable described in sqlda->sqlvar[varno].
struct sqlda *fill_s_sqlda( struct sqlda *sqlda, unsigned int maxlen );
Much the same as fill_sqlda except that it changes all the data types in sqlda to type DT_STRING (see "SQLDEF.H header file"). Enough space is allocated for the strings to hold the string representation of the type originally specified by the SQLDA up to a maximum of maxlen bytes. The length fields in the SQLDA (sqllen) are modified appropriately. Returns sqlda if successful and returns the null pointer if there is not enough memory available.
void free_filled_sqlda( struct sqlda *sqlda );
Free all space allocated to this sqlda including the memory allocated to each sqldata pointer. Any null pointer will not be freed. The indicator variable space is also freed as allocated in fill_sqlda.
void free_sqlda_noind( struct sqlda *sqlda );
Free the space allocated to this sqlda but do not free the memory referenced by each sqldata pointer. The indicator variable pointers are ignored.
void free_sqlda( struct sqlda *sqlda );
Free the space allocated to this sqlda but do not free the memory referenced by each sqldata pointer. The indicator variable space is also freed as allocated in fill_sqlda.
The db_backup function provides support for online backup. The SQL Anywhere backup utility makes use of this function. You should only need to write a program to use this function if your backup requirements are not satisfied by the SQL Anywhere backup utility.
Every database contains one or more files. Normally, a database contains two files, the main database file and the transaction log. Each file is divided up into fixed size pages, and the size of these pages is specified when the database was created. Backup works by opening a file, and then making a copy of each page in the file. Backup performs a checkpoint on startup. The database file(s) is backed up as of this checkpoint. Any changes made while the backup is running are recorded in the transaction log and will be backed up with the transaction log. This is why you always backup the transaction log last.
You must be connected to a user ID with DBA authority or REMOTE DBA authority (SQL Remote) to use the backup functions.
void db_backup( struct sqlca * sqlca, int op, int file_num, unsigned long page_num, struct sqlda * sqlda);
Must be connected to a user ID with DBA authority or REMOTE DBA authority (SQL Remote).
The action performed by the db_backup function depends on the value of the op parameter:
The file_num, page_num and sqlda parameters are ignored.
The page_num and sqlda parameters are ignored.
Although the DBBACKUP program reads the pages sequentially in the database file, the pages could be read in any order.
| Application must save buffer This call makes a copy of the specified database page into the buffer, but it is up to the application to save the buffer on some backup media. |
When you receive the SQLE_NOTFOUND condition, the transaction log has been backed up successfully and the file has been renamed. The name for the old transaction file will be returned in the sqlerrmc field of the SQLCA.
The page_num and sqlda parameters are ignored.
The file_num, page_num and sqlda parameters are ignored.
The algorithm used by the DBBACKUP program is as follows. Note that this is NOT C code, and does not include error checking.
db_backup( ... DB_BACKUP_START ... )allocate page buffer based on page size in SQLCODEsqlda = alloc_sqlda( 1 )sqlda->sqld = 1;sqlda->sqlvar[0].sqltype = DT_BINARYsqlda->sqlvar[0].sqldata = allocated bufferfor file_num = 0 to DB_BACKUP_MAX_FILEdb_backup( ... DB_BACKUP_OPEN_FILE, file_num ... )if SQLCODE == SQLE_NO_ERROR/* The file exists */num_pages = SQLCOUNTfile_time = SQLE_IO_ESTIMATEopen backup file with name from sqlca.sqlerrmcfor page_num = 0 to num_pages - 1db_backup( ... DB_BACKUP_READ_PAGE,file_num, page_num, sqlda )write page buffer out to backup filenext page_numclose backup filedb_backup( ... DB_BACKUP_CLOSE_FILE, file_num ... )end ifnext file_numbackup up file DB_BACKUP_WRITE_FILE as abovebackup up file DB_BACKUP_TRANS_LOG_FILE as abovefree page bufferdb_backup( ... DB_BACKUP_END ... )
void db_delete_file( struct sqlca * sqlca,
char * filename );
Must be connected to a user ID with DBA authority or REMOTE DBA authority (SQL Remote).
The db_delete_file function requests the database engine to delete filename. This can be used after backing up and renaming the transaction log (see DB_BACKUP_READ_RENAME_LOG above) to delete the old transaction log. Note that this request will work both with the single-user engine and the multiuser server. You must be connected to a userid with DBA authority.
unsigned int sql_needs_quotes( struct sqlca *sqlca, char *str );
Returns a boolean indicating whether or not the string str requires double quotes around it when it is used as a SQL identifier. This function actually formulates a request to the database engine to determine if quotes are needed.
char *sqlerror_message( struct sqlca *sqlca, char * buffer, int max );
Return a pointer to a string containing an error message. The error message contains text for the error code in the SQLCA. If no error was indicated, a null pointer is returned. The error message will be placed in the buffer supplied truncated to length max if necessary.
int db_abort_request( void );
Historical. See db_cancel_request.
int db_cancel_request( struct sqlca *sqlca );
Abort the currently active database engine request. This function will check to make sure a database engine request is active before sending the abort request. The return value indicates whether or not an abort request was sent; in other words, whether or not a database request was active. A non-zero return value does not mean that the request was aborted. There are a few critical timing cases where the abort request and the response from the database or server "cross". In these cases, the abort simply has no effect.
Note that db_cancel_request can be called asynchronously (from an interrupt handler, a Window procedure in Windows, or from another OS/2 or Windows NT thread). This function and db_is_working are the only functions in the database interface library that can be called asynchronously using an SQLCA that might be in use by another request.
In DOS, the default ctrl+Break handling mechanism in the database interface library calls this routine when a ctrl+Break is detected.
unsigned db_is_working( struct sqlca *sqlca );
Returns 1 if your application has a database request in progress that uses the given sqlca, and 0 if there is no request in progress that uses the given sqlca. This function can be called asynchronously (from an interrupt handler, a Window procedure in Windows, or from another OS/2 or Windows NT thread). This function and db_cancel_request are the only functions in the database interface library that can be called asynchronously using an SQLCA that might be in use by another request.
unsigned db_working( void );
Historical. See db_is_working.
This section does not apply to Windows NT or Windows 95.
You may have noticed when using ISQL under Windows that the Windows system is still active while ISQL is waiting for a response from the database. This is not the default behavior of the interface DLL. Default behavior puts the hour glass cursor on the screen and forces the user to wait for completion of the request.
The proper way to provide this active behavior for an application would be through the use of asynchronous database requests. The current release of SQL Anywhere does not support asynchronous requests but you can achieve application activity in Windows while a database request is in progress by providing a callback function. The key difference is that in this callback function you must not do another database request (except db_cancel_request). You can use the db_is_working function in your message handlers to determine if you have a database request in progress.
This callback function in your application will be called repeatedly while the database engine or SQL Anywhere Client is busy processing a request. You can then process Windows messages by calling GetMessage or PeekMessage. (These function calls allow Windows to be active.) The DBL50 DLL will continually call this function until the response from the database engine is received.
| Response in Windows message The response from the engine or SQL Anywhere Client comes to your application via a Windows message. You must either dispatch this message (the interface DLL will receive the message), or you must call the db_process_message function with each message you receive while in this callback function. The function will return TRUE if the message was the response; otherwise you can process the message normally by dispatching it. |
The following function is used to register your application callback functions:
void db_register_a_callback( struct sqlca *sqlca, a_db_callback_index index, FARPROC callback );
This function is used to register Windows callback functions. To remove a callback, pass a null pointer as the callback function. Following is a list of the allowable values for index and the appropriate function prototype. Note that you should call MakeProcInstance with your function address and pass that to the db_register_callback function.
If you don't register any callback functions, then the default action is to do nothing. Your application will block waiting for the database response and Windows will change the cursor to an hour glass.
The following values are allowed for the index parameter:
This function is called just before a database request is sent to the engine or SQL Anywhere Client.
This function is called after the response to a database request has been received by the interface DLL.
This function is called repeatedly by the interface DLL while the database engine or SQL Anywhere Client is busy processing your database request.
The following is a sample DB_CALLBACK_WAIT callback function:
void FAR PASCAL db_wait_request( struct sqlca *sqlca ){ MSG msgf( GetMessage( &msg, NULL, 0, 0 ) ) {( !db_process_a_message( sqlca, &msg ) ) {if( !TranslateAccelerator( hWnd, hAccel, &msg ) ) {TranslateMessage( &msg )DispatchMessage( &msg )}}}}
int db_process_a_message( struct sqlca *sqlca, MSG *msg );
This function is called from within your db_wait_request callback function to determine if the message you received from Windows was in fact the response to the active database request. The return value will be TRUE if msg is the response. Simply return from the callback function and the Embedded SQL library DLL will process the response by returning to the call that generated the original request.
void db_set_sqlca( struct sqlca *sqlca );
Historical (DOS and Windows). See "SET SQLCA statement".
struct sqlca *db_get_sqlca( void );
Historical (DOS and Windows). See "SET SQLCA statement".
In DOS and QNX, all database library functions use the following functions to allocate and free memory. On all other operating systems, the interface DLL manages its own memory.
These functions are in a module by themselves and simply call the normal C memory functions: malloc, realloc and free. In DOS and QNX, you can use different memory management functions by redefining DBAlloc, DBRealloc and DBFree in your application code (all three must be redefined together). Refer to the documentation for your C compiler and linker to see how to force the new definitions of these functions to be used instead of the versions supplied in the database library. Usually, this is a matter of naming your replacement file before the library in the linker command file.
void *DBAlloc( unsigned int size );
Allocates a piece of memory size bytes long and returns a pointer to it. This function is called by the interface library in order to dynamically allocate memory. The default routine in the library calls malloc.
void *DBRealloc( void *ptr, unsigned int size );
Reallocates the piece of memory pointed to by ptr to be size bytes long and returns a pointer to it. The contents of the original piece are present in the new piece of memory. This function is called by the interface library in order to dynamically reallocate memory. The default routine in the library calls realloc.
void DBFree( void *ptr );
Frees the dynamic memory piece pointed to by ptr. This function is called by the interface library in order to dynamically free a piece of memory. The default routine in the library calls free.
The database interface library contains default handling mechanisms for Ctrl+Break and the critical device error ("Abort,Retry,Ignore?" errors) in DOS. These handlers modify the default behavior as follows:
By default, the interrupt handlers are installed before each request to the database engine and deinstalled after each request is completed. Both the installation mechanism and the function performed by the interrupt handler can be modified. Be cautious though, noting the above mentioned DOS default behavior for Ctrl+Break and critical errors. In particular, the default behavior for Ctrl+Break is not appropriate; terminating the database engine in that way will cause your machine to hang up.
The functions db_sending_request and db_finished_request described below are called before and after a database engine request. If you wish to provide different handlers for the Ctrl+Break or critical device error interrupts, you should replace these functions with ones of your own.
You may want to catch these interrupts once when your application starts up by providing your own handlers for the interrupts. This will eliminate the overhead of catching the interrupts on every database request. This is the way that ISQL handles the Ctrl+Break and critical error interrupts.
void db_sending_request( void );
This function is called by the database library before a request is sent to the engine. The default action provided by the version of this routine in the interface library is:
db_catch_break( db_break_handler );
db_catch_critical( db_critical_handler );
To modify Ctrl+Break or critical error handling, provide your own version of this function.
void db_finished_request( void );
This function is called by the database library after a request to the engine is completed. The default action provided by the version of this routine in the interface library is:
db_release_critical();
db_release_break();
To modify Ctrl+Break or critical error handling, provide your own version of this function.
The following functions are used by db_sending_request() and db_finished_request() in catching and processing the Ctrl+Break and critical device error interrupts. They can also be used by your replacements for these routines.
void db_catch_break( void (interrupt far *rtn)( void ) );
This function installs rtn as the break handling routine. It will save the previous break handler so that it can be restored by db_release_break().
void db_release_break( void );
This function deinstalls the break handler previously installed by db_catch_break and reinstalls the old break handler. Note that if some other program has replaced the break handler since your call to db_catch_break, then that handler will get lost when the old break handler is reinstalled.
void interrupt db_break_handler( void );
This is the default break handler for the database interface library. It calls db_cancel_request to abort the active database request.
void db_catch_critical( void (interrupt far *rtn)( void ) );
This function installs rtn as the critical device error handling routine. It will save the previous handler so that it can be restored by db_release_critical().
void db_release_critical( void );
This function uninstalls the critical device error handler previously installed by db_catch_critical and reinstalls the old handler. Note that if some other program has replaced the critical error handler since your call to db_catch_critical, then that handler will get lost when the old critical error handler is reinstalled.
void interrupt db_critical_handler( void );
This is the default critical device error handler for the database interface library. The action specified in the library routine is to move a 3 into the AH register and issue an IRET instruction. This causes DOS to report a failure to the task that issued the I/O request. Note that by default, this handler is installed and deinstalled around each request to the database engine. Thus the task that issued the I/O request must have been the database engine. The engine expects to receive the failure status and will recover appropriately.