The Internal Driver API

The set of function points that a database driver implements and what AOLserver expects is as follows:

1. Name

2. Database type

3. Server initialization

4. Open a database connection

5. Close a database connection

6. Get row from table

7. Flush

8. Cancel a database connection

9. Get information about a table

10. Get list of tables

11. Best row identifier (identifies each row uniquely)

12. Execute SQL query

13. Optional: Reset a database handle when it gets checked back into the pool.

The database driver's job is to make the appropriate DBMS-specific function calls that will implement these functions. The driver must provide an implementation of the function Ns_DbDriverInit; This function is executed once, when the server starts up. The most important thing it does is call Ns_DbRegisterDriver with an array of all the functions implemented by the driver. Here's an example from the Postgres95 db driver:

    static Ns_DbProc PgProcs[] = {
       {DbFn_Name, (void *) Ns_PgName},
       {DbFn_DbType, (void *) Ns_PgDbType},
       {DbFn_OpenDb, (void *) Ns_PgOpenDb},
       {DbFn_CloseDb, (void *) Ns_PgCloseDb},
       {DbFn_DML, (void *) Ns_PgCmd},
       {DbFn_Select, (void *) Ns_PgSelect},
       {DbFn_GetRow, (void *) Ns_PgGetRow},
       {DbFn_Flush, (void *) Ns_PgFlush},
       {DbFn_Cancel, (void *) Ns_PgFlush},
       {DbFn_GetTableInfo, (void *) Ns_PgGetTableInfo},
       {DbFn_TableList, (void *) Ns_PgTableList},
       {DbFn_BestRowId, (void *) Ns_PgBestRowId},
       {DbFn_ServerInit, (void *) Ns_PgServerInit},
       {DbFn_ResetHandle, (void *) NULL},
       {0, NULL}
    };
    DllExport int
    Ns_DbDriverInit(char *hDriver, char *configPath)
    {
        /* 
         * Register the Postgres95 driver functions with nsdb.
         * Nsdb will later call the Ns_PgServerInit() function
         * for each virtual server which utilizes nsdb. 
         */
        if (Ns_DbRegisterDriver(hDriver, &(PgProcs[0])) != NS_OK) {
            Ns_Log(Error, "Ns_DbDriverInit(%s):  Could not register 
the %s driver.",
                hDriver, pgName);
            return NS_ERROR;
        }
        Ns_Log(Notice, "%s loaded.", pgName);
        return NS_OK;
    }

In more detail, here's what each of these functions needs to do:

1. char *Ns_dbms-nameName(Ns_DbHandle *handle);

   This function returns the string which identifies the database driver.

2. char *Ns_dbms-nameDbType(Ns_DbHandle *handle);

   This function returns the string which identifies the database type. Usually it is the same as the name of the driver.

3. int Ns_dbms-nameServerInit(char *hServer, char *hModule, char *hDriver);

   Ns_dbms-nameServerInit calls another function named Ns_dbms_nameInterpInit which is responsible for adding the command Ns_dbms-name to a single Tcl interpreter. Ns_dbms-nameServerInit calls Ns_dbms-nameInterpInit for each interpreter in the virtual server that is being initialized.

4. int Ns_dbms-nameOpenDb(Ns_DbHandle *dbh);

   This function takes a pointer (typically known as the "handle") to the Ns_DbHandle structure as an argument. The handle contains information such as the driver name, name of the datasource, user name and password, name of the database pool and some other parameters. The structure is as follows:

       typedef struct Ns_DbHandle {
       char           *driver;
       char           *datasource;
       char           *user;
       char           *password;
       void           *connection;
       char           *poolname; 
       	/* poolname associated for this handle */
       	/* used by internal Db API */
       int             connected;
              int             verbose; 
       	/* used for verbose error messages */
       	/* equivalent to [ns/db/pool/poolname] Verbose=On */
       	/* used by internal Db API */
              Ns_Set         *row;     
       	/* used for row data during binds, getrows, etc.*/
       	/* used by internal Db API */
              char            cExceptionCode[6];
              Ns_DString      dsExceptionMsg;
              void           *context; 
              void           *statement; 
       	/* used by ODBC driver statements */
              int             fetchingRows;
          } Ns_DbHandle;
   

   The statement field is used as a pointer to your allocated statement handles (hstmt). This allows you to pass a DbHandle with a pointer to the statement handle to other driver functions to invoke cancels (e.g., SQLCancel()) and error reporting (e.g., SQLError()).

   This function takes the information contained in the handle and opens a connection to the database named in the handle along with the username and password. If a connection is successfully established, then OpenDb performs some database specific functions, and returns NS_OK. If it is unable to establish a connection to the database server, or if it is unable to perform the required database specific functions, it returns NS_ERROR. The status is logged by using the Ns_Log function.

   Note: For more information about database-specific functions, the documentation about its API should be consulted.

5. int Ns_dbms-nameCloseDb(Ns_DbHandle *handle);

   This function takes a handle as an argument and terminates the connection to the database server. It also cleans up the handle parameters. It returns with either NS_OK or NS_ERROR (in case CloseDb fails to close the connection).

6. int Ns_dbms-nameGetRow(Ns_DbHandle *handle, Ns_Set *row);

   The results of a SELECT query are usually a number of rows. Ns_dbms-nameGetRow is responsible to obtain these rows from the database and return them to AOLserver for display/manipulation. This function typically does the following:

   checks if it is being called from within a fetch row loop

   checks if the end of data is reached, and if so, stops

   returns the next row in the result set

   You can return the values something like this:

       Ns_SetPutValue(row, (int) i, colval);
   

   This function should return either NS_ERROR or NS_OK.

7. int Ns_dbms-nameFlush(Ns_DbHandle *handle);

   This function flushes any rows which are waiting to be retrieved after a SELECT is executed. The rows are irretrievably lost. The fields for columns, tuples, and current tuple in the connection structure are reset to 0. The result field is reset to NULL. This function should return either NS_ERROR or NS_OK.

8. int Ns_dbms-nameCancel(Ns_DbHandle *handle);

   A call to this function results in cancelling that particular database connection.

9. Ns_DbTableInfo *Ns_dbms-nameGetTableInfo(Ns_DbHandle *handle, char *table, int fExtended);

   This function returns table-specific information such as number of attributes in the table, their types etc. The third parameter is for internal use and can be ignored. Ns_DbTableInfo is a array of Ns_Set * and the sets contain something resembling the following:

       set->name = "column_name";
       set->field[0].name = "column_type";
       set->field[0].value = "text";
       set->field[1].name = "column_notnull";
       set->field[1].value = "t";
   

   A "t" or an "f" would represent whether the column is nullable or not.

10. char *Ns_dbms-nameTableList(Ns_DString *pds, Ns_DbHandle *handle, int includesystem);

    This function builds a database-specific SELECT statement which returns a list of all tables in the database. The function returns this list. In case of a NULL table name, a warning is logged by Ns_Log. The list of tables should be returned in the pds parameter in the form "string\0string\0string\0", which can be implemented as follows:

        Ns_DStringNAppend(pds, table, strlen(table) +1);
    

11. char *Ns_dbms-nameBestRowId(Ns_DString *pds, Ns_DbHandle *handle, char *table);

    This function returns the unique identifier for a table. In most cases, it is usually the primary key for the table. In some cases, the system table containing table information contains a unique identifier for each table. In such cases, this identifier is returned. The pds parameter is an initialized Ns_DString that gets the primary key (e.g., in Postgres, Ns_DStringNAppend(pds, "oid", 4)).

12. int Ns_dbms-nameExec(Ns_DbHandle *handle, char *sql);

    This function takes an SQL command and sends it to the DBMS. If the command is returns rows, the function should return NS_ROWS. If the command was DDL or DML, then the function should return NS_DML. And of course if there is an error executing the SQL, the function should return NS_ERROR. It is recommended that you define one Ns_dbms-nameExec procedure that handles both queries that return rows and those that do not. When Ns_DbExec is invoked in AOLserver, it calls whatever Ns_DbExec function is defined. When Ns_DbSelect is invoked, it tries Ns_DbExec first and then Ns_DbSelect.

13. int Ns_dbms-nameResetHandle(Ns_DbHandle *handle);

    This function will be called with the database handle every time one is returned to the database pool. You can use this to normalize it's state for the next use; for example always setting the handle to autocommit mode and aborting any uncommitted transactions.