Programming Interfaces Guide
CHAPTER 2. The Embedded SQL Interface
The SQL preprocessor generates calls to functions in the interface library or DLL. In addition to the calls generated by the SQL preprocessor, several routines 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.
This section contains a detailed description of these various functions by category.
The DLL entry points are the same except that the prototypes have a modifier appropriate for DLLs:
Windows: FAR PASCAL
OS/2: _System
Windows NT: __stdcall
All of the pointers that are 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 |
Interface initialization functionsThis section lists the functions that initialize and release the interface.
db_init functionunsigned short db_init( struct sqlca *sqlca );
This function initializes the database interface library. This function must be called before any other library call is made, and before any Embedded SQL command is executed. The resources the interface library requires 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 are returned in the SQLCA and 0 is 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, call db_init once for each SQLCA that is being used (see SQLCA management for multi-threaded or reentrant code).
Caution
Failure to call db_fini for each db_init on NetWare can cause the database server to fail, and the NetWare file server to fail.
db_fini functionunsigned short db_fini( struct sqlca *sqlca );
This function 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. If there are any errors during processing, they are returned in the SQLCA and 0 is returned. If there are no errors, a non-zero value is returned.
You need to call db_fini once for each SQLCA being used.
Caution
Failure to call db_fini for each db_init on NetWare can cause the database server to fail, and the NetWare file server to fail.
Connection and server management functionsThe following functions provide a means to start and stop the database server or, start or stop a database on an existing 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 the equals sign is a syntax error, such as in environment variables on some platforms..
For an available list of connection parameters, see Connection parameters.
Each function uses a subset of the available connection parameters, but every function will allow any parameter to be set. A sample connection parameter string is:
"UID=dba;PWD=sql;DBF=c:\asa6\asademo.db"
When included in Embedded SQL, the backslash character (\) must be escaped with a second backslash character in order to work:
"UID=dba;PWD=sql;DBF=c:\\asa6\\asademo.db"
db_string_connect functionunsigned db_string_connect( struct sqlca * sqlca, char * parms );
Provides extra functionality beyond the Embedded SQL CONNECT command. This function carries out the following steps:
Start the database server if there is not one running with the name EngineName (calls db_start_engine). The AutoStop parameter determines if the server automatically stops when the last used database is shut down.
If the database named by DatabaseName or DatabaseFile is not currently running, send a request to the server to start a database using the DatabaseFile, DatabaseName, and DatabaseSwitches parameters. The AutoStop parameter determines if the database automatically shuts down when the last connection to the database is disconnected.
Send a connection request to the database server based on the Userid, Password, and ConnectionName parameters.
The return value is true (non-zero) if a connection was successfully established and false (zero) otherwise. Error information for starting the server, starting the database, or connecting is returned in the SQLCA.
db_string_disconnect functionunsigned db_string_disconnect( struct sqlca * sqlca, char * parms );
This function 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 is disconnected. This is equivalent to the Embedded SQL DISCONNECT command. The boolean return value is true if a connection was successfully ended. Error information is returned in the SQLCA.
This function shuts down the database if it was started with the AutoStop=yes parameter and there are no other connections to the database. It also stops the server if it was started with the AutoStop=yes parameter and there are no other databases running.
db_start_engine functionunsigned db_start_engine( struct sqlca * sqlca,
char * parms );
Start the database server if it is not running. The steps carried out by this function are those listed in Starting a personal server.
The return value is true if a database server was either found or successfully started. Error information is returned in the SQLCA.
The following call to db_start_engine starts the database server and names it asademo, but does not load the database, despite the DBF connection parameter:
db_start_engine( &sqlca, "DBF=c:\\asa6\\asademo.db; Start=DBENG6" );
If you wish to start a database as well as the server, include the database file in the START connection parameter:
db_start_engine( &sqlca,"ENG=eng_name;START=DBENG6 c:\\asa6\\asademo.db" );
This call starts the server, names it eng_name, and starts the asademo database on that server.
db_start_database functionunsigned db_start_database( struct sqlca * sqlca, char * parms );
Start a database on an existing server if the database is not already running. The steps carried out to start a database are described in Starting a personal server
The return value is true if the database was already running or successfully started. Error information is returned in the SQLCA.
If a user ID and password are supplied in the parameters, they are ignored.
The permission required to start and stop a database is set on the database command line. For information, see The database server.
db_stop_database functionunsigned int db_stop_database( struct sqlca * sqlca,
char * parms );
Stop the database identified by DatabaseName on the server identified by EngineName. If EngineName is not specified, the default server is used.
By default, this function does not stop a database that has existing connections. If Unconditional is yes, the database is stopped regardless of existing connections.
A return value of TRUE indicates that there were no errors.
The permission required to start and stop a database is set on the database command line. For information, see The database server.
db_stop_engine functionunsigned int db_stop_engine( struct sqlca * sqlca,
char * parms );
Terminates execution of the database server. The steps carried out by this function are:
Look for a local database server that has a name that matches the EngineName parameter. If no EngineName is specified, look for the default local database server.
If no matching server is found, this function fails.
Send a request to the server to tell it to checkpoint and shut down all databases.
Unload the database server.
By default, this function will not stop a database server that has existing connections. If Unconditional is yes, the database server is stopped regardless of existing connections.
A C program can use this function instead of spawning DBSTOP. A return value of TRUE indicates that there were no errors.
db_find_engine functionunsigned short db_find_engine( struct sqlca *sqlca, char *name );
Returns an unsigned short value, which indicates status information about the database server whose name is name. If no server can be found with the specified name, the return value is 0. A non-zero value indicates that the server is currently running.
Each bit in the return value conveys some information. Constants that represent the bits for the various pieces of information are defined in the sqldef.h header file. If a null pointer is specified for name, information is returned about the default database environment.
SQLDA management functionsThe following functions are used to manage SQL Descriptor Areas (SQLDAs).
For a detailed description of the SQLDA, see The SQL descriptor area (SQLDA).
alloc_sqlda_noind functionstruct sqlda *alloc_sqlda_noind( unsigned numvar );
Allocates a SQLDA with descriptors for numvar variables. The sqln field of the SQLDA is initialized to numvar. Space is not allocated for indicator variables; the indicator pointers are set to the null pointer. A null pointer is returned if memory cannot be allocated.
alloc_sqlda functionstruct sqlda *alloc_sqlda( unsigned numvar );
Allocates a SQLDA with descriptors for numvar variables. The sqln field of the SQLDA is 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 is returned if memory cannot be allocated.
fill_sqlda functionstruct 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.
sqlda_string_length functionunsigned long sqlda_string_length( struct sqlda *sqlda, int varno );
Returns the length of the C string (type DT_STRING) that would be required to hold the variable sqlda->sqlvar[varno] (no matter what its type is).
sqlda_storage functionunsigned 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].
fill_s_sqlda functionstruct 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.. Enough space is allocated 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.
free_filled_sqlda functionvoid free_filled_sqlda( struct sqlda *sqlda );
Free the memory allocated to each sqldata pointer. Any null pointer is not freed. The indicator variable space, as allocated in fill_sqlda, is also freed.
free_sqlda_noind functionvoid free_sqlda_noind( struct sqlda *sqlda );
Free space that was allocated to this sqlda. You should first call free_filled_sqlda to free the memory referenced by each sqldata pointer. The indicator variable pointers are ignored.
free_sqlda functionvoid free_sqlda( struct sqlda *sqlda );
Free the space allocated to this sqlda. You should first call free_filled_sqlda to free the memory referenced by each sqldata pointer. The indicator variable space, as allocated in fill_sqlda, is also freed.
Backup functionsThe db_backup function provides support for online backup. The Adaptive Server 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 Adaptive Server Anywhere backup utility.
You can also access the backup utility directly using the Database Tools DBBackup function. For more information on this function, see DBBackup function.
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 into fixed size pages, and the size of these pages is specified when the database is created.
Backup works by opening a file, and then making a copy of each page in the file. Backup performs a checkpoint on startup, and the database files are backed up as of this checkpoint. Any changes that are made while the backup is running are recorded in the transaction log, and are backed up with the transaction log. This is why the transaction log is always backed up last.
You must be connected to a user ID with DBA authority or REMOTE DBA authority (SQL Remote) to use the backup functions.
db_backup functionvoid 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 depends on the value of the op parameter:
DB_BACKUP_START Must be called before a backup can start. Only one backup can be running at one time against any given database server. Database checkpoints are disabled until the backup is complete (db_backup is called with an op value of DB_BACKUP_END). If the backup cannot start, the SQLCODE is SQLE_BACKUP_NOT_STARTED. Otherwise, the SQLCOUNT field of the sqlca is set to the size of each database page. (Backups are processed one page at a time.)
The file_num, page_num and sqlda parameters are ignored.
DB_BACKUP_OPEN_FILE Open the database file specified by file_num, which allows pages of the specified file to be backed up using DB_BACKUP_READ_PAGE. Valid file numbers are 0 through DB_BACKUP_MAX_FILE for the main database files, DB_BACKUP_TRANS_LOG_FILE for the transaction log file, and DB_BACKUP_WRITE_FILE for the database write file if it exists. If the specified file does not exist, the SQLCODE is SQLE_NOTFOUND. Otherwise, SQLCOUNT contains the number of pages in the file, SQLIOESTIMATE contains a 32-bit value (POSIX time_t) which identifies the time that the database file was created, and the operating system file name is in the sqlerrmc field of the SQLCA.
The page_num and sqlda parameters are ignored.
DB_BACKUP_READ_PAGE Read one page of the database file specified by file_num. The page_num should be a value from 0 to one less than the number of pages returned in SQLCOUNT by a successful call to db_backup with the DB_BACKUP_OPEN_FILE operation. Otherwise, SQLCODE is set to SQLE_NOTFOUND. The sqlda descriptor should be set up with one variable of type DT_BINARY pointing to a buffer. The buffer should be large enough to hold binary data of the size returned in the SQLCOUNT field on the call to db_backup with the DB_BACKUP_START operation.
DT_BINARY data contains a two-byte length followed by the actual binary data, so the buffer must be two bytes longer than the page size.
Application must save buffer |
DB_BACKUP_READ_RENAME_LOG This action is the same as DB_BACKUP_READ_PAGE, except that after the last page of the transaction log has been returned, the database server renames the transaction log and starts a new one.
If the database server is unable to rename the log at the current time (there are incomplete transactions), you will get the SQLE_BACKUP_CANNOT_RENAME_LOG_YET error. In this case, don't use the page returned, but instead reissue the request until you receive SQLE_NOERROR and then write the page. Continue reading the pages until you receive the SQLE_NOTFOUND condition.
The SQLE_BACKUP_CANNOT_RENAME_LOG_YET error may be returned multiple times and on multiple pages. In your retry loop, you should add a delay so as not to slow the server down with too many requests.
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 is returned in the sqlerrmc field of the SQLCA.
You should check the sqlda->sqlvar[0].sqlind value after a db_backup call. If this value is greater than zero, the last log page has been written and the log file has been renamed. The new name is still in sqlca.sqlerrmc, but the SQLCODE value is SQLE_NOERROR.
You should not call db_backup again after this. If you do, you get a second copy of your backed up log file and you receive SQLE_NOTFOUND.
DB_BACKUP_CLOSE_FILE Must be called when processing of one file is complete to close the database file specified by file_num.
The page_num and sqlda parameters are ignored.
DB_BACKUP_END Must be called at the end of the backup. No other backup can start until this backup has ended. Checkpoints are enabled again.
The file_num, page_num and sqlda parameters are ignored.
The dbbackup program uses the following algorithm. 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 SQLCODE
sqlda = alloc_sqlda( 1 )
sqlda->sqld = 1;
sqlda->sqlvar[0].sqltype = DT_BINARY
sqlda->sqlvar[0].sqldata = allocated buffer
for file_num = 0 to DB_BACKUP_MAX_FILE
db_backup( ... DB_BACKUP_OPEN_FILE, file_num ... )
if SQLCODE == SQLE_NO_ERROR
/* The file exists */
num_pages = SQLCOUNT
file_time = SQLE_IO_ESTIMATE
open backup file with name from sqlca.sqlerrmc
for page_num = 0 to num_pages - 1
db_backup( ... DB_BACKUP_READ_PAGE,
file_num, page_num, sqlda )
write page buffer out to backup file
next page_num
close backup file
db_backup( ... DB_BACKUP_CLOSE_FILE, file_num ... )
end if
next file_num
backup up file DB_BACKUP_WRITE_FILE as above
backup up file DB_BACKUP_TRANS_LOG_FILE as above
free page buffer
db_backup( ... DB_BACKUP_END ... )
db_delete_file functionvoid 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 server 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. You must be connected to a user ID with DBA authority.
Canceling a requestThe following functions provide the ability to check whether a request is being processed, and to cancel a request.
db_cancel_request functionint db_cancel_request( struct sqlca *sqlca );
Cancels the currently active database server request. This function will check to make sure a database server request is active before sending the cancel request. The return value indicates whether a cancel request was sent; in other words, whether or not a database request was active.
A non-zero return value means that the request was not canceled. There are a few critical timing cases where the cancel request and the response from the database or server "cross". In these cases, the cancel simply has no effect.
The db_cancel_request function can be called asynchronously. 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.
If you cancel a request that is carrying out a cursor operation, the position of the cursor is indeterminate. You must locate the cursor by its absolute position, or close it, following the cancel.
db_is_working functionunsigned 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. 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.
Other functionsThe following functions are miscellaneous functions.
sql_needs_quotes functionunsigned int sql_needs_quotes( struct sqlca *sqlca, char *str );
Returns a Boolean value that indicates whether the string requires double quotes around it when it is used as a SQL identifier. This function formulates a request to the database server to determine if quotes are needed. Relevant information is stored in the sqlcode field.
There are three cases of return value/code combinations:
return = FALSE, sqlcode = 0 In this case, the string definitely does not need quotes
return = TRUE In this case, sqlcode is always SQLE_WARNING, and the string definitely does need quotes
return = FALSE If sqlcode is something other than SQLE_WARNING, the test is inconclusive
sqlerror_message functionchar *sqlerror_message( struct sqlca *sqlca, char * buffer, int max );
Return a pointer to a string that contains 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 is placed in the buffer supplied, truncated to length max if necessary.
Request management functionsThe default behavior of the interface DLL is for applications to wait for completion of each database request before carrying out other functions. This behavior can be changed using request management functions. For example, when using Interactive SQL, the operating system is still active while Interactive SQL is waiting for a response from the database, and Interactive SQL carries out some tasks in that time.
You can achieve application activity while a database request is in progress by providing a callback function. 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 is called repeatedly while the database server 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 dblib6w.dll continually calls this function until the response from the database server is received.
Response in Windows message |
The following two functions are used to register your application callback functions:
db_register_a_callback functionvoid db_register_a_callback( struct sqlca *sqlca,
a_db_callback_index index,
FARPROC callback );
This function is used to register callback functions. To remove a callback, pass a null pointer as the callback function. You should call MakeProcInstance with your function address and pass that to the db_register_a_callback function.
If you do not register any callback functions, the default action is to do nothing. Your application blocks, waiting for the database response, and Windows changes the cursor to an hourglass.
The following values are allowed for the index parameter:
DB_CALLBACK_START The prototype is as follows:
void FAR PASCAL db_start_request( struct sqlca *sqlca );
This function is called just before a database request is sent to the server.
DB_CALLBACK_FINISH The prototype is as follows:
void FAR PASCAL db_finish_request( struct sqlca *sqlca );
This function is called after the response to a database request has been received by the interface DLL.
DB_CALLBACK_WAIT The prototype is as follows:
void FAR PASCAL db_wait_request( struct sqlca *sqlca );
This function is called repeatedly by the interface DLL while the database server or client library 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 msg
if( GetMessage( &msg, NULL, 0, 0 ) ) {
( !db_process_a_message( sqlca, &msg ) ) {
if( !TranslateAccelerator( hWnd, hAccel, &msg ) ) {
TranslateMessage( &msg )
DispatchMessage( &msg )
}
}
}
}
DB_CALLBACK_MESSAGE This is used to enable the application to handle messages received from the server during the processing of a request.
The callback prototype is as follows:
void FAR PASCAL message_callback( SQLCA* sqlca,
unsigned short msg_type,
an_sql_code code,
unsigned length,
char* msg )
The msg_type parameter states how important the message is, and you may wish to handle different message types in different ways. The available message types are MESSAGE_TYPE_INFO, MESSAGE_TYPE_WARNING, MESSAGE_TYPE_ACTION and MESSAGE_TYPE_STATUS. These constants are defined in sqldef.h. The code field is an identifier. The length field tells you how long the message is. The message is not null-terminated, since it might be right in the data stream that we got from the server.
For example, the Interactive SQL callback displays STATUS and INFO message in the message window, while messages of type ACTION and WARNING go to a dialog box. If an application does not register this callback, there is a default callback, which causes all messages to be written to the server logfile (if debugging is on and a logfile is specified). In addition, messages of type MESSAGE_TYPE_WARNING and MESSAGE_TYPE_ACTION are more prominently displayed, in an operating system-dependent manner.
db_process_a_message functionint 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 that you received from Windows was in fact the response to the active database request. The return value is TRUE if msg is the response. 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.