Mini SQL 2.0 (Beta) C Programming API


Introduction

Included in the distribution is the mSQL API library, libmsql.a. The API allows any C program to communicate with the database engine. The API functions are accessed by including the msql.h header file into your program and by linking against the mSQL library (using -lmsql as an argument to your C compiler). The library and header file will be installed by default into /usr/local/ Hughes/lib and /usr/local/Hughes/include respectively.

Like the mSQL engine, the API supports debugging via the MSQL_DEBUG environment variable. Three debugging modules are currently supported by the API: query, api, and malloc. Enabling "query" debugging will cause the API to print the contents of queries as they are sent to the server. The "api" debug modules causes internal information, such as connection details, to be printed. Details about the memory used by the API library can be obtained via the "malloc" debug module. Information such as the location and size of malloced blocks and the addresses passed to free() will be generated. Multiple debug modules can be enabled by setting MSQL_DEBUG to a colon separated list of module names. For example setenv MSQL_DEBUG api:query

The API has changed slightly from the original mSQL API. Any additions or modifications to the API are marked with NEW!


Query Related Functions

  • msqlConnect()

  • int msqlConnect ( host )
    char * host ;


    msqlConnect() forms an interconnection with the mSQL engine. It takes as its only argument the name or IP address of the host running the mSQL server. If NULL is specified as the host argument, a connection is made to a server running on the localhost using the UNIX domain socket /dev/msqld. If an error occurs, a value of -1 is returned and the external variable msqlErrMsg will contain an appropriate text message. This variable is defined in "msql.h".

    If the connection is made to the server, an integer identifier is returned to the calling function. This values is used as a handle for all other calls to the mSQL API. The value returned is in fact the socket descriptor for the connection. By calling msqlConnect() more than once and assigning the returned values to separate variables, connections to multiple database servers can be maintained simultaneously.

    In previous versions of mSQL, the MSQL_HOST environment variable could be used to specify a target machine if the host parameter was NULL. This is no longer the case.

    •  

    msqlSelectDB()

  • int msqlSelectDB ( sock , dbName )
    int sock ;
    char * dbName ;


    Prior to submitting any queries, a database must be selected. msqlSelectDB() instructs the engine which database is to be accessed. msqlSelectDB() is called with the socket descriptor returned by msqlConnect() and the name of the desired database. A return value of -1 indicates an error with msqlErrMsg set to a text string representing the error. msqlSelectDB() may be called multiple times during a program's execution. Each time it is called, the server will use the specified data- base for future accesses. By calling msqlSelectDB() multiple times, a program can switch between different databases during its execution.

    •  

    msqlQuery()

  •  
    int msqlQuery ( sock , query )
    int sock ;
    char * query ;


    Queries are sent to the engine over the connection associated with sock as plain text strings using msqlQuery(). As with previous releases of mSQL, a returned value of -1 indicates an error and msqlErrMsg will be updated to contain a valid error message. If the query generates output from the engine, such as a SELECT statement, the data is buffered in the API waiting for the application to retrieve it. If the application submits another query before it retrieves the data using msqlStoreResult(), the buffer will be overwritten by any data generated by the new query.


    In previous versions of mSQL, the return value of msqlQuery() was either -1 (indicating an error) or 0 (indicating success). mSQL 2 adds to these semantics by providing more information back to the client application via the return code. If the return code is greater than 0, not only does it imply success, it also indicates the number of rows "touched" by the query (i.e. the number of rows returned by a SELECT, the number of rows modified by an update, or the number of rows removed by a delete).

    •  

    msqlStoreResult()

  • m_result * msqlStoreResult ( )

    Data returned by a SELECT query must be stored before another query is submitted or it will be removed from the internal API buffers. Data is stored using the msqlStoreResult() function which returns a result handle to the calling routines. The result handle is a pointer to a m_result structure and is passed to other API routines when access to the data is required. Once the result handle is allocated, other queries may be submitted. A program may have many result handles active simultaneously.


    • msqlFreeResult()

  • void msqlFreeResult ( result )
    m_result * result ;


    When a program no longer requires the data associated with a particular query result, the data must be freed using msqlFreeResult(). The result handle associated with the data, as returned by msqlStoreResult() is passed to msqlFreeResult() to identify the data set to be freed.


    • msqlFetchRow()

  • m_row msqlFetchRow ( result )
    m_result * result ;


    The individual database rows returned by a select are accessed via the msqlFetchRow() function. The data is returned in a variable of type m_row which contains a char pointer for each field in the row. For example, if a select statement selected 3 fields from each row returned, the value of the 3 fields would be assigned to elements [0], [1], and [2] of the variable returned by msqlFetchRow(). A value of NULL is returned when the end of the data has been reached. See the example at the end of this sections for further details. Note, a NULL value is represented as a NULL pointer in the row.


    • msqlDataSeek()

  • void msqlDataSeek ( result , pos )
    m_result * result ;
    int pos ;


    The m_result structure contains a client side "cursor" that holds information about the next row of data to be returned to the calling program. msqlDataSeek() can be used to move the position of the data cursor. If it is called with a position of 0, the next call to msqlFetchRow() will return the first row of data returned by the server. The value of pos can be anywhere from 0 (the first row) and the number of rows in the table. If a seek is made past the end of the table, the next call to msqlFetchRow() will return a NULL.

    •  

    msqlNumRows()

  •  
    int msqlNumRows ( result )
    m_result * result ;


    The number of rows returned by a query can be found by calling msqlNumRows() and passing it the result handle returned by msqlStoreResult(). The number of rows of data sent as a result of the query is returned as an integer value.


    If a select query didn't match any data, msqlNumRows() will indicate that the result table has 0 rows (note: earlier versions of mSQL returned a NULL result handle if no data was found. This has been simplified and made more intuitive by returning a result handle with 0 rows of result data)

    •  

    msqlFetchField()

  • m_field * msqlFetchField ( result )
    m_result * result ;


    Along with the actual data rows, the server returns information about the data fields selected. This information is made available to the calling program via the msqlFetchField() function. Like msqlFetchRow(), this function returns one element of information at a time and returns NULL when no further information is available. The data is returned in a m_field structure which contains the following information:

    •
    typedef struct {
    		char * name ; /* name of field */
      char * table ; /* name of table */
      int type ; /* data type of field */
      int length , /* length in bytes of field */
      int flags ; /* attribute flags */
    } m_field;

  • Possible values for the type field are defined in msql.h as

    INT_TYPE, CHAR_TYPE and REAL_TYPE. The individual attribute flags

    can be accessed using the following macros:

    •

  • msqlFieldSeek()

  • void msqlFieldSeek ( result , pos )
    m_result * result ;
    int pos ;

    The result structure includes a "cursor" for the field data. It's

    position can be moved using the

    msqlFieldSeek() function. See msqlDataSeek() for further details.

    •

     

    msqlNumFields()

  • int msqlNumFields ( result )
    m_result * result ;

    The number of fields returned by a query can be ascertained by calling

    msqlNumFields() and passing it the result handle. The value returned

    by msqlNumFields() indicates the number of elements in the data

    vector returned by msqlFetchRow(). It is wise to check the number of fields

    returned before, as with all arrays, accessing an element that is

    beyond the end of the data vector can result in a segmentation fault.

    •

     

    msqlClose()

  • int msqlClose ( sock )
    int sock ;

    The connection to the mSQL engine can be closed using msqlClose().

    The function must be called with the connection socket returned by

    msqlConnect() when the initial connection was made.

    •



    Schema Related Functions

  • msqlListDBs()

  • m_result * msqlListDBs ( sock )
    int sock ;

    A list of the databases known to the mSQL engine can be obtained via

    the msqlListDBs() function. A result handle is returned to the calling

    program that can be used to access the actual database names. The

    individual names are accessed by calling msqlFetchRow() passing it the result

    handle. The m_row data structure returned by each call will contain one

    field being the name of one of the available databases. As with all

    functions that return a result handle, the data associated with the result

    must be freed when it is no longer required using msqlFreeResult().

    •


    msqlListTables()

  • m_result * msqlListTables ( sock )
    int sock ;

    Once a database has been selected using msqlInitDB(), a list of the

    tables defined in that database can be retrieved using

    msqlListTables(). As with msqlListDBs(), a result handle is

    returned to the calling program and the names of the tables are

    contained in data rows where element [0] of the row is the name of

    one table in the current database. The result handle must be freed

    when it is no longer needed by calling msqlFreeResult().

    •


    msqlListFields()

  • m_result * msqlListFields ( sock , tableName ) ;
    int sock ;
    char * tableName;

    Information about the fields in a particular table can be obtained

    using msqlListFields(). The function is called with the name of a

    table in the current database as selected using msqlSelectDB()

    and a result handle is returned to the caller. Unlike msqlListDBs()

    and msqlListTables(), the field information is contained in field

    structures rather than data rows. It is accessed using msqlFetchField().

    The result handle must be freed when it is no longer needed by

    calling msqlFreeResult().

    •

    msqlListIndex()

  • m_result * msqlListIndex ( sock , tableName , index ) ;
    int sock ;
    char * tableName;
    char * index;

    The structure of a table index can be obtained from the server using the

    msqlListIndex() function. The result table returned contains one field.

    The first row of the result contains the symbolic name of the index

    mechanism used to store the index. Rows 2 and onwards contain the name

    of the fields that comprise the index. For example, if a compund index was

    defined as an AVL Tree index and was

    based on the values of the fields first_name and

    last_name, then the result table would look like

    •

    row[0]

    avl
    first_name
    last_name

  • Currently the only valid index type is 'avl' signifying a memory mapped AVL tree.

     

    •