libdbSQL (and MySQL) Documentation

written by Joey Mukherjee -
Last Updated - 5/16/00

Table of contents

  1. Overview
  2. Conversion from libserver/Getting Started
  3. dbInitialize
  4. dbClose
  5. dbGetDataKey
  6. dbIDFSGetRealTimeFile/dbIDFSGetFile
  7. dbVirtualName
  8. dbKeyToStrings
  9. dbErrorMsg
  10. dbCfgItem/dbCfgPath
  11. dbQuery/dbQueryStore
  12. dbBadQuery
  13. dbFreeResult


The libdbSQL is the successor to the libserver interface between the database and SDDAS. Most of the functionality from libserver is replicated in this library, but instead of using the old SDDAS database, it now uses the MySQL database. Should the database product MySQL be replaced in the future, only the sdcatalog, this library, and libPromote would have to change.

Conversion from libserver/Getting Started...

In your Makefile, you must link with -ldbSQL and -lmysqlclient. If you are converting from an old program, remove -ldB and -lserver. If you use our SDDAS.rules files, add the variable $(MYSQL_LIB) to your link line which will have the directory of -lmysqlclient and a -lmysqlclient in it.

To use the routines below, include the file libdbSQL.h in your code.

The database must now be initialized at the start of the program and closed at the end. This will save time since the database is now handled by the MySQL daemon which essentially listens for requests and answers them as they are received. Opening the database is accomplished by calling dbInitialize and closed with dbClose.



void dbInitialize ();
SDDAS_BOOL dbInitializeMsg ();

This should be the first routine you call to use any of the database routines. It will open a connection to the MySQL server and open the SDDAS database. This routine will exit the program on failure and return on success. The first routine makes a huge assumption in assuming you wish to exit the program if the database can not be opened. The dbInitializeMsg will do the same thing, but return either sTrue or success and sFalse on failure. Any error message can be retrieved by dbErrorMsg.



void dbClose ();

This routine will close the connection to the database. You are encouraged to call this at the end of your program.



SDDAS_ULONG dbGetDataKey (char *p, char *m, char *e, char *i, char *v);

Given a full hierarchy, this routine will return the data key from the database. It will return a positive number on success and a zero on failure. The old get_data_key routine is preserved for compatibility; however, you only get one error code back on failure now (DKEY_VINST).



int dbIDFSGetRealTimeFile (SDDAS_ULONG data_key, RequestedDataType dataType,
                           char *exten, char *name1, char *name2);
int dbIDFSGetFile (SDDAS_ULONG data_key, 
                   SDDAS_SHORT btime_yr, SDDAS_SHORT btime_day, SDDAS_LONG btime_msec,
                   SDDAS_SHORT etime_yr, SDDAS_SHORT etime_day, SDDAS_LONG etime_msec,
                   RequestedDataType dataType,
                   char *exten, char *name1, char *name2);

(Old compatibility routine)

int dbGetFile (SDDAS_ULONG data_key, Time_t btime, Time_t etime, RequestedDataType dataType,
               char *exten, char *name1, char *name2);

The old compatibility routine exists if your program already makes use of a Time_t structure and wish to preserve its usage. The Time_t is a structure we wish to deprecate since it is not a true object and zero initialization can not be guaranteed. If the structure changes, the application may not be notified of such changes by the compiler, thus creating an error.

You are encouraged to make use of the dbIDFS... routines since they will guarantee a correct filename.

If you need access to real time data files, you must call dbIDFSGetRealTimeFile. The filenames will be built up based on data from the configuration files. If you need playback data, call dbIDFSGetFile. This routine will build a file name for you based on the data key, exten, RequestedDataType, and the time. The RequestedDataType can be one of:

  1. _H_AND_D_
  2. _HEADER_
  3. _DATA_
  4. _VIDF_

The exten variable is an extension which is added on the end of the filename, it is normally set to NULL. The difference between the two routines is that the dbGetFile routine will query the database for the time sent and build the filename with that information.

The last two arguments are pointers to character buffers which must be allocated by the caller. They should be sized to MAXPATHLEN since they are fully qualified filenames. The first field (name1) is always returned. The second field (name2) is only returned when _H_AND_D_ is passed as the RequestedDataType. For the purposes of SDDAS, the header name and the data file name are identically named with the exception of the last letter.

The return values of either of these functions is either ALL_OKAY or one of the following errors:

  1. DBF_ERROR - error in the database
  2. CONFIG_FILE_ERROR - error in the configuration files
  3. NO_DATA - data does not exist in the database



SDDAS_BOOL dbKeyToStrings (SDDAS_ULONG data_key, char *p, char *m, char *e, char *i, char *v);

Given a data key, this will return the full hierarchy in the space provided by the caller. The variables p, m, e, i, v must be allocated to be of MAX_IDFS_NAME length.

Return code is either sTrue if everything succeeded or an sFalse if there was an error condition.



char *dbVirtualName (SDDAS_ULONG data_key);

Given a data key, this will return the name of the virtual instrument.



char *dbErrorMsg ();

If you get a database error, this string will have the text description of the error.



char *dbCfgPath (SDDAS_ULONG data_key, char *item);
char *dbCfgItem (SDDAS_ULONG data_key, char *item, int element);

If you need information from the configuration files and have the data key, use these convenience methods to retrieve the given path or item. See the libCfg documentation for further details.



void *dbQuery (char *query_str);
void *dbQueryStore (char *query_str);

These routines will issue a SQL query to the database, and return to you a voided MYSQL_RES *. The output is voided so the internals of mysql will not be exposed, nor will people have to compile in mysql.h. If you absolutely need to see the results of the query, typecast the return value back to a MYSQL_RES * and see the MySQL documentation on how to interpret it. In some cases, a query needs no return value (INSERT and DELETE) and this is of no consequence.

The difference between these two routines is that the dbQueryStore takes more memory in that it issues the query and saves the result in a temporary variable. If you access the database such that all queries are sequential, use the dbQuery routine. If you issue a query, and before you completely get all the results of that query, you re-access the database, you must use dbQueryStore.



void dbFreeResult (void *result);

This will free the results of the query from the above routine. Call this after you have issued a query.