dbExpress (Inprise/Borland's new cross-platform data access layer) Draft Specification

By: John Kaster

Abstract: The Kylix project will introduce dbExpress. It will also be in future versions of Delphi and C++ Builder on Windows. Now you can find out what it is, and also provide us suggestions that could make it into its first release.

Internals of dbExpress

by Ramesh Theivendran, Inprise/Borland R&D

Note: This is a draft of a working specification for dbExpress. The specification is subject to change without notice. dbExpress is Borland's new cross-platform data access layer that will first be available when the products produced by the Kylix project are released. It will also be available in Delphi 6 and C++ Builder 6, and later. We are providing this information at this early stage because we invite feedback on the architecture of dbExpress. Simply attach a comment to this article to let us know what you think. Because this is a new cross-platform data access layer designed specifically for SQL-oriented database management systems, your feedback is very important and very appreciated. You can download the header files and slides from the Inprise/Borland conference presentations by Ramesh Theivendran (Internals of dbExpress) and John Kaster (Overview of DataCLX and dbExpress) from CodeCentral.

Introduction

Data storage and access play a major role in today's IT industry. There can hardly be any business application without interacting with databases. Key to such database intensive business applications is performance.

In the past, standards like ODBC and JDBC have provided a database independent layer to access a variety of databases. By providing a common interface, database specific functionality important for a particular database are left in the dark. At the same time, trying to aggregate the various database features made the data-access layer complex and bulkier.

dbExpress is a cross-platform, database-independent and an extensible interface that provides a set of methods for dynamic SQL processing. dbExpress will serve as the next generation data-access layer for Kylix, Delphi and C++ Builder. The main focus of this paper is to explain the data-access interface, see how it differs from existing standards and to discuss the internals of how to design a database driver for your favorite database.

dbExpress vs BDE

dbExpress is capable of processing queries and stored procedures like the BDE (Borland Database Engine). BDE is a feature rich, bulkier client whereas dbExpress is simpler, easy to implement and best suits the MIDAS provider and resolver model.

Database intensive applications should use queries or stored procedures to get to the data. Opening table through BDE not only consumes client resources but also locks up server resources. dbExpress eliminates the concept of opening tables, and clients are recommended to use SQL queries with better selectivity for optimized data-access.

Unlike the BDE, dbExpress returns only unidirectional cursors and therefore does no caching. The MIDAS ClientDataset can be used for caching, and scrolling, indexing, and filtering on the result set.

BDE caches metadata so that they can be readily available for future requests. dbExpress does no metadata caching, and the design time metadata access interface is implemented using the core data-access interface.

BDE's runtime performance with SQL databases is affected by the internal query generation for navigation, blob access and metadata retrieval. dbExpress executes only queries requested by the user, thereby optimizing database access by not introducing any extra queries.

dbExpress manages a record buffer or a block of record buffers internally, and provides clients with individual field values. BDE on the other hand uses a client-allocated record buffer and this may be error prone as the client might pass an insufficent or a corrupted record buffer.

Among the other differences from the BDE, dbExpress has no concept of live queries, cached updates, schema creation support, heterogeneous query processing, batchmove, and so on.

dbExpress also addresses the following issues we currently have with the BDE.

  • Complexity involved with BDE configuration, BDE deployment and adapting to a new data source.
  • Run-time resource overheads from caching blobs, metadata and loading BDE configuration.
  • Performance.

Database driver features

The various features currently provided by the existing database connectivity standards like BDE, ODBC and JDBC can be broadly classified into three main areas: data access, metadata retrieval, and schema creation.

The most basic feature a database driver should provide is an interface to process dynamic SQL queries. Dynamic SQL allows your application to process any valid SQL statement at runtime. Most database vendor client libraries provide interfaces for dynamic SQL processing like OCI or PRO *C (Dynamic Method 4) for ORACLE, CLI for DB2 and other ODBC data sources, CTLIB for Sybase and E/SQL for Informix.

Once the basic dynamic SQL processing is built, then metadata retrieval and schema creation can be easily built on top. Now, we will get into the basic steps for dynamic SQL processing.

Initializing the environment

Most database vendor clients have to initialize certain handles before the client can connect to the database server. Usually these initializations involve allocating client resources such as the SQLDA and SQLCA for end-to-end communication with the server. X/Open SQL CAE requires an environment handle and a connection handle to be initialized before establishing a database connection.


SQLHANDLE henv, hdbc;
SQLRETCODE rc;
/* allocate an environment handle */
rc = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv ) ;
/* allocate a connection handle */
if ( SQLAllocHandle( SQL_HANDLE_DBC, henv, hdbc ) != SQL_SUCCESS ) 
{ 
  printf( "ERROR while allocating a connection handle-----n" ) ;
  return SQL_ERROR;
}

Connecting to the database server

Once the required handles are allocated, a connection can be established by specifying a server, user name and password. You can also set and get certain pre-connect and post-connect properties with the connection handle like autocommit on/off, connection time out (pre-connect set property) and database version info (post-connect get property).


/* Set AUTOCOMMIT OFF */
rc = SQLSetConnectAttr( hdbc, SQL_ATTR_AUTOCOMMIT, ( void * ) SQL_AUTOCOMMIT_OFF, SQL_NTS) ; 
if ( SQLConnect( hdbc, Server, SQL_NTS, UserName, SQL_NTS, Password, SQL_NTS) != SQL_SUCCESS )
{
  printf( "ERROR while connecting to %s ------n",Server ) ;
  return SQL_ERROR;
}

Initializing statement handles

A statement handle for a connection must be allocated before any SQL statement can be processed. Most databases allow you to have more than one active statement per connection. On servers that don't, you may have to establish a separate connection to process multiple SQL statements simultaneously. You can also set and get statement level properties with a statement handle.


#define ROWSET_SIZE 20
SQLHANDLE hstmt;
rc =SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
/* Set BLOCK FETCH SIZE */
rc = SQLSetStmtAttr( hstmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER) ROWSET_SIZE, 0);

Preparing a SQL statement

Preparing a SQL statement validates the SQL statement and creates an execution plan on the server. Once a SQL statement is prepared, most databases allow you to execute the SQL many times by passing different parameters at runtime. After a SQL statement is successfully prepared, certain database vendor clients will provide information on whether it's an INSERT, DELETE, UPDATE or SELECT statement, the number of parameter markers, etc.


if ( SQLPrepare(hstmt, szSQL, SQL_NTS) != SQL_SUCCESS )
{
  printf( "ERROR while preparing a SQL statement ------n" ) ;
  return SQL_ERROR;
}

Passing runtime parameters

Runtime parameters can be bound by specifying placeholders or parameter markers in the SQL statement. Depending upon the database we are talking with, there could be one or more ways to specify parameter markers, like binding by name or binding by number. Once a parameterized SQL statement is successfully prepared, you must bind parameter buffers and provide data or set NULL indicators to all the parameter markers before executing.


SQLCHAR insert_data[21] ;
SQLINTEGER insert_data_ind ;
for ( iPos = 1; iPos <= noOfParams; iPos ++)
{
  ... 
  ...
  switch ( dataType )
  {
    case SQL_CHAR:
    /* Bind data to parameter marker */
    rc = SQLBindParameter( hstmt, iPos, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR,20, 0, 
    insert_data, 21, &insert_data_ind );
    break;
    ... 
    ...
  }
}

Executing a SQL statement

SQL statements do not always need to be prepared before executing. There are interfaces that will allow direct execution of a SQL statement. While executing SQL directly, all the client and server resources are freed immediately after execution, and all the resultset cursors are fetched. So, if an application needs to process the same SQL repeatedly, it is better to prepare the SQL once and execute it many times.


if ( SQLExecute( hstmt ) != SQL_SUCCESS )
{
  printf( "ERROR while executing a SQL statement ------n" ) ;
  return SQL_ERROR;
}
rc = SQLExecDirect( hstmt, szSQL, SQL_NTS ) ;

Binding a record buffer

After successfully executing a SQL statement, if it's a SELECT query we can retrieve metadata about the resultset columns. For a INSERT, DELETE or UPDATE SQL we can get the number of rows affected on the server. Once we have determined there is a resultset, we need to allocate a record buffer, which will be used to fetch records. The record buffer size can be computed by summing up all the resultset column lengths plus a four byte null indicator for each column. For example if you have two CHAR(20) columns in the resultset, the record buffer size will be (20 + 1 + 4) * 2 = 50 bytes. So, depending upon the datatype we may need to provide addition buffer for a NULL terminator, size prefixed data, blob handles, etc. For a clear understanding on the bind buffer requirements for a particular data type, consult the database vendor client library documents.


SQLSMALLINT noResultCols;
SQLINTEGER noofRowsAffected;
SQLNumResultCols ( hstmt , &noResultCols);
if ( noResultCols )
{
  /* Resultset is available */
  SQLCHAR colname[32] ;
  SQLSMALLINT coltype ;
  SQLSMALLINT colnamelen ;
  SQLSMALLINT nullable ;
  SQLUINTEGER collen;
  SQLSMALLINT scale ;
  SQLINTEGER displaysize ;
  for ( iCol = 1; iCol<= noResultCols; iCol++ ) 
  {
    /* Describe each column in the resultset */
    rc = SQLDescribeCol( hstmt, ( SQLSMALLINT ) iCol, colname, sizeof(colname),
    colnamelen, &coltype, &collen, &scale, &nullable ) ;
    /* get display length for column */
    rc = SQLColAttribute( hstmt, ( SQLSMALLINT ) iCol, SQL_DESC_DISPLAY_SIZE, NULL, 
    0, NULL, &displaysize ) ;
  }
}
else
  /* No resultset, should be a INSERT, UPDATE, DELETE or a DDL statement */
  rc = SQLRowCount ( hstmt , &noofRowsAffected );

Fetching records

Once we have prepared a record buffer we can bind the buffer for fetching data and null indicators by iterating through all the resultset columns. Most database vendors provide one or more ways to bind buffers, like bind by position or bind by name. After providing buffer for all the columns we can fetch a record into the record buffer by calling appropriate fetch calls. Once a record is fetched, all the column null indicators should be examined to see if there is any valid data. If there is a valid data, make a copy of the data from the record buffer to a buffer provided by the application. Subsequent fetches will fetch records one by one until the EOF (End Of File) is reached.


for ( iCol = 1; iCol <= noOfColumns; iCol ++)
{
  ... 
  ... 
  switch ( dataType )
  {
  case SQL_CHAR:
    /* Bind buffer for resultset column */ 
    rc = SQLBindCol(hstmt, iCol, SQL_C_CHAR, (SQLPOINTER) data, 15, ind1);
    break;
    ...
	...
  }
}
while ((rc = SQLFetch(hstmt)) == SQL_SUCCESS) 
{
  /* copy record to the client buffer */ 
  ...
  ...
}
if (rc == SQL_NO_DATA_FOUND)
{
  /* copy the last record to the client buffer */
  ... 
  ...
  rc = SQL_SUCCESS;
}

Free handles and disconnect

There could be more than one cursor returned as a result of executing a stored procedure on certain servers. So, after fetching the first cursor till EOF you can continue to fetch the next cursor and so on. Once all the cursors are fetched, if the statement handles are no longer needed for repeated execution, we can free the statement handle, disconnect from the database, and free connection and other handles.


rc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
rc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_COMMIT ) ;
/* Disconnect for the database */
rc = SQLDisconnect( hdbc ) ;
rc = SQLFreeHandle( SQL_HANDLE_DBC, hdbc ) ;
rc = SQLFreeHandle( SQL_HANDLE_DBC, henv ) ;

dbExpress abstraction

The four core classes in dbExpress are SQLDriver, SQLConnection, SQLCommand and SQLCursor. SQLMetaData is the metadata access interface that's built on the core classes. All the classes have methods to set and get runtime properties that clients can use to take advantage of database specific features or fine tune data access.

SQLDriver

SQLDriver does database specific initialization like loading the vendor client, initializing the environment and allocating necessary handles and gets a SQLConnection object.


abstract COMINTF SQLDriver
{
   virtual INT32 GCCSTDC QueryInterface( GUID riid, ppVOID ppv ) = 0;
   virtual UINT32 GCCSTDC AddRef( ) = 0;
   //Destructs the SQLDriver object
   virtual UINT32 GCCSTDC Release( ) = 0;

   //Gets a new SQLConnection object
   virtual SQLResult GCCSTDC getSQLConnection (
      ppSQLConnection ppConn) = 0;

   //Set/Get options for database driver level properties
   virtual SQLResult GCCSTDC setOption (
      eSQLDriverOption  eDOption, 
      INT32             lValue )  = 0;

   virtual SQLResult GCCSTDC getOption (
      eSQLDriverOption  eDOption,
      pINT32            plValue,
      INT16             iMaxLength,
      pINT16            piLength )  = 0;

};

SQLConnection

SQLConnection implementation takes care of establishing a connection to a database, gets a SQLCommand object for query and stored procedure processing, gets a SQLMetaData object for metadata retrieval and handles transactions.


abstract COMINTF SQLConnection
{
   
   virtual INT32 GCCSTDC QueryInterface( GUID riid,  ppVOID ppv ) = 0;
   virtual UINT32 GCCSTDC AddRef( ) = 0;
   //Destructs the SQLConnection object
   virtual UINT32 GCCSTDC Release( ) = 0;

   //Attach to a database server
   virtual SQLResult GCCSTDC connect (
      pCHAR pszServerName,
      pCHAR pszUserName, 
      pCHAR pszPassword ) = 0;

   //Detach from a database server
   virtual SQLResult GCCSTDC disconnect () = 0;

   //Destructs the SQLConnection object
  
   //Gets a new SQLCommand object 
   virtual SQLResult GCCSTDC getSQLCommand (
      ppSQLCommand ppComm) = 0;

   //Gets a new SQLMetaData object 
   virtual SQLResult GCCSTDC getSQLMetaData (
      ppSQLMetaData ppMeta) = 0;

   //Set/Get options for database connection level properties
   virtual SQLResult GCCSTDC setOption (
      eSQLConnectOption eCOption, 
      INT32             lValue )  = 0;

   virtual SQLResult GCCSTDC getOption (
      eSQLConnectOption eCOption,
      pINT32            plValue,
      INT16             iMaxLength,
      pINT16            piLength )  = 0;

   //Transaction support
   virtual SQLResult GCCSTDC beginTransaction(
      UINT32 ulTrasnID );

   virtual SQLResult GCCSTDC commit(
      UINT32 ulTrasnID );

   virtual SQLResult GCCSTDC rollback(
      UINT32 ulTrasnID );

   //Error handling
   virtual SQLResult GCCSTDC getErrorMessage (
      pBYTE pszError ) = 0;

   virtual SQLResult GCCSTDC getErrorMessageLen (
      pUINT16 puErrorLen ) = 0;

};

SQLCommand

SQLCommand provides methods for processing a query or a stored procedure and returns a SQLCursor object if available. It also supports executing a prepared query multiple times with parameter binding, returning multiple cursors from stored procedure.


abstract COMINTF SQLCommand
{
   virtual INT32  GCCSTDC QueryInterface( GUID riid, ppVOID ppv ) = 0;
   virtual UINT32 GCCSTDC AddRef( ) = 0;
   //Destructs the SQLCommand object
   virtual UINT32 GCCSTDC Release( ) = 0;

   //Set/Get options for command level properties
   virtual SQLResult GCCSTDC setOption (
      eSQLCommandOption eSOption,
      INT32             lValue ) = 0;

   virtual SQLResult GCCSTDC getOption (
      eSQLCommandOption eSOption,
      pINT32            plValue,
      INT16             iMaxLength,
      pINT16            piLength )  = 0;


   //Set/Get  methods for parameter binding support
   virtual SQLResult GCCSTDC setParameter (
      UINT16     uParameterNumber,
      STMTParamType ePType,
      UINT16     uLogType,
      UINT16     uSubType,
      INT32      lMaxPrecision,
      INT32      lMaxScale,
      UINT32     ulLength,
      pVOID      pBuffer,
      BOOL       bIsNull ) = 0;


   virtual SQLResult GCCSTDC getParameter (
      UINT16  uParameterNumber,
      pVOID   pData,
      UINT32  ulLength,
      pINT32  plInd ) = 0;


   //Prepare and Execute SQL and Stored Procedure
   virtual SQLResult GCCSTDC prepare (
      pCHAR pszSQL ) = 0;

   virtual SQLResult GCCSTDC execute (
     ppSQLCursor ppCur) = 0;


   //Direct execution of Query or Stored Procedure or DDL
   //No parsing will be done
   virtual SQLResult GCCSTDC executeImmediate (
      pCHAR pszSQL, 
      ppSQLCursor ppCur) = 0;


   //A new SQLCursor object will be returned if there is another result set 
   //available from a stored procedure execution
   virtual SQLResult GCCSTDC  getNextCursor (
      ppSQLCursor ppCur);

   //No.of rows affected as a result of INSERT/DELETE/UPDATE
   virtual SQLResult GCCSTDC getRowsAffected (
      pINT32 plRows) = 0;

   //Free resources used for parameter binding
   virtual SQLResult GCCSTDC close () = 0;


   //Error Handling
   virtual SQLResult GCCSTDC getErrorMessage (
      pBYTE pszError ) = 0;

   virtual SQLResult GCCSTDC getErrorMessageLen (
      pUINT16 puErrorLen ) = 0;

};

SQLCursor

SQLCursor holds data and metadata with respect to the executed query or stored procedure and has methods to get individual field values.


abstract COMINTF SQLCursor
{
   virtual INT32  GCCSTDC  QueryInterface( GUID riid, ppVOID ppv ) = 0;
   virtual UINT32 GCCSTDC  AddRef( ) = 0;
   //Destructs the SQLCursor object
   virtual UINT32 GCCSTDC  Release( ) = 0;

   //Error handling
   virtual SQLResult GCCSTDC getErrorMessage (
      pBYTE pszError );

   virtual SQLResult GCCSTDC getErrorMessageLen (
      pUINT16 puErrorLen );

   //Metadata access methods
   virtual SQLResult GCCSTDC getColumnCount (pUINT16 puColumns) = 0;

   virtual SQLResult GCCSTDC getColumnNameLength (
      UINT16  uColumnNumber,
      pUINT16 puLen ) = 0;

   virtual SQLResult GCCSTDC getColumnName (
      UINT16 uColumnNumber, 
      pCHAR  pColumnName) = 0;
      
   virtual SQLResult GCCSTDC getColumnType (
      UINT16  uColumnNumber,
      pUINT16 puType,
      pUINT16 puSubType ) = 0;

   virtual SQLResult GCCSTDC getColumnLength (
      UINT16  uColumnNumber,
      pUINT32 pulLength ) = 0;

   virtual SQLResult GCCSTDC getColumnPrecision (
      UINT16 uColumnNumber,
      pINT16 piPrecision ) = 0;

   virtual SQLResult GCCSTDC getColumnScale (
      UINT16 uColumnNumber,
      pINT16 piScale ) = 0;

   virtual SQLResult GCCSTDC isNullable (
      UINT16 uColumnNumber,
      pBOOL  pbNullable ) = 0;

   virtual SQLResult GCCSTDC  isAutoIncrement (
      UINT16 uColumnNumber,
      pBOOL  pbAutoIncr ) = 0;

   virtual SQLResult GCCSTDC isReadOnly (
      UINT16 uColumnNumber,
      pBOOL  pbReadOnly ) = 0;

   virtual SQLResult GCCSTDC isSearchable(
      UINT16 uColumnNumber,
      pBOOL  pbSearchable ) = 0;

   virtual SQLResult GCCSTDC isBlobSizeExact(
      UINT16 uColumnNumber,
      pBOOL  pbBlobExactSize ); 
      
   //Fetch the next record or the next set of records 
   virtual SQLResult GCCSTDC  next() = 0;


   //Data access methods
   virtual SQLResult GCCSTDC getString (
      UINT16 uColumnNumber,
      pCHAR  pData,
      pBOOL  pbIsNull );

   virtual SQLResult GCCSTDC getShort (
      UINT16 uColumnNumber,
      pINT16 pData,
      pBOOL  pbIsNull );

   virtual SQLResult GCCSTDC getLong (
      UINT16 uColumnNumber,
      pINT32 pData,
      pBOOL  pbIsNull );

   virtual SQLResult GCCSTDC getDouble (
      UINT16 uColumnNumber,
      pDFLOAT  pData,
      pBOOL  pbIsNull );

   virtual SQLResult GCCSTDC getBcd (
      UINT16 uColumnNumber,
      pFMTBcd  pData,
      pBOOL  pbIsNull );

   virtual SQLResult GCCSTDC getTimeStamp (
      UINT16          uColumnNumber,
      pSQLLTIMESTAMP  pData,
      pBOOL           pbIsNull ); 

   virtual SQLResult GCCSTDC getTime (
      UINT16     uColumnNumber,
      pINT32     pData,
      pBOOL      pbIsNull );

   virtual SQLResult GCCSTDC getDate (
      UINT16     uColumnNumber,
      pINT32     pData,
      pBOOL      pbIsNull );

   virtual SQLResult GCCSTDC getBytes (
      UINT16     uColumnNumber,
      pBYTE      pData,
      pBOOL      pbIsNull );

   virtual SQLResult GCCSTDC getBlobSize (
      UINT16  uColumnNumber,
      pUINT32 pulLength,
      pBOOL   bIsNull );

   virtual SQLResult GCCSTDC getBlob (
      UINT16 uColumnNumber,
      pVOID  pData,
      pBOOL  bIsNull,
      UINT32 ulLength );

};

SQLMetaData

The SQLMetaData interface defines the various database metadata that can be obtained on an opened database connection. The getSQLMetaData() method from a SQLConnection object will return a SQLMetaData object. SQLMetaData provides only the metadata needed by MIDAS and Kylix, Delphi, C++ Builder data-access components. There are numerous other database metadata properties skipped to make the implementation simpler. However, most of these can be surfaced using get and set options on the SQLMetaData object and without changing the interface.


abstract COMINTF SQLMetaData
{
   virtual INT32 GCCSTDC  QueryInterface( GUID riid, ppVOID ppv ) = 0;
   virtual UINT32 GCCSTDC AddRef( ) = 0;
   //Destructs the SQLMetaData object
   virtual UINT32 GCCSTDC Release( ) = 0;

   //Set/Get options for database metadata properties
   virtual SQLResult GCCSTDC  setOption (
      eSQLMetaDataOption  eMOption, 
      INT32               lValue ) = 0;

   virtual SQLResult GCCSTDC  getOption (
      eSQLMetaDataOption  eDOption,
      pINT32              plValue,
      INT16               iMaxLength,
      pINT16              piLength ) = 0;

   //Get Object list
   virtual SQLResult GCCSTDC  getObjectList (
      eSQLObjectType eObjType, 
      ppSQLCursor    ppCur) = 0;

   //Get Table list
   virtual SQLResult  GCCSTDC  getTables ( 
      pCHAR pszTableName,
      UINT32 uTableType, 
      ppSQLCursor ppCur) = 0;

   //Get Procedure list
   virtual SQLResult GCCSTDC  getProcedures ( 
      pCHAR pszProcName,
      UINT32 uProcType, 
      ppSQLCursor ppCur) = 0;

   //Get Column metadata for a given table 
   virtual SQLResult  GCCSTDC  getColumns (
      pCHAR pszTableName,
      pCHAR pszColumnName,
      UINT32 uColType, 
      ppSQLCursor ppCur) = 0;

   //Get Parameter metadata for a given Stored Procedure
   virtual SQLResult  GCCSTDC getProcedureParams (
      pCHAR pszProcName,
      pCHAR pszParamName,
      ppSQLCursor ppCur) = 0;
   
   //Get Index info associated with a given table   
   virtual SQLResult GCCSTDC  getIndices (
      pCHAR pszTableName,
      UINT32 uIndexType,
      ppSQLCursor ppCur) = 0;

   //Error handling
   virtual SQLResult GCCSTDC getErrorMessage (
      pBYTE pszError );

   virtual SQLResult GCCSTDC getErrorMessageLen (
      pUINT16 puErrorLen );

};

SQLMetaData Methods

For all the methods below a search pattern can be specified for catalog name and schema name by setting the eMetaCatalogName and eMetaSchemaName properties by calling setOption().

Search Pattern

A search pattern can be specified to constrain results returned from the following methods and can contain the following characters, based on the standard SQL wildcard characters.

  • An underscore (_) represents any single character,
  • A percentage (%) represents a sequence of one or more character and
  • An escape character, when preceded to the underscore or the percentage characters allows them to be used as literal characters in search patterns.

A sequence of two escape characters will allow the escape character to be used as a literal character in search patterns.

Database specific escape characters can be obtained from a SQLMetaData object by calling getOption(eMetaSQLEscapeChar).

getObjectList()


getObjectList(
  eSQLObjectType eObjType, 
  ppSQLCursor ppCur)

Given an eSQLObjectType, this method returns a SQLCursor with a list of available objects in the database.

Columns in the returned cursor are

ColumnType
1.RECNOfldINT32
2.CATALOG_NAMEfldZSTRING
3.SCHEMA_NAMEfldZSTRING
4.OBJECT_NAMEfldZSTRING

Cursor columns are ordered by the object name (OBJECT_NAME).

getTables()


getTables(
  pCHAR pszTableNamePattern,
  UINT32 uTableType,
  ppSQLCursor ppCur)

Given an eSQLTableType, this method returns a SQLCursor with a list of tables or views or synonyms, and so on, in the database. One or more eSQLTableType can be ORed and passed in uTableType to get a list of more than one table types. A search pattern for the table name can also be specified in the first argument. If it is NULL, no criteria will be used in the search.

Columns in the returned cursor are

ColumnType
1.RECNOfldINT32
2.CATALOG_NAMEfldZSTRING
3.SCHEMA_NAMEfldZSTRING
4.TABLE_NAMEfldZSTRING
5.TABLE_TYPEfldINT32

Cursor columns are ordered by the table name (TABLE_NAME).

getProcedure()


getProcedures ( 
  pCHAR pszProcNamePattern,
  UINT32 uProcType,
  ppSQLCursor ppCur)

Given an eSQLProcType, this method returns a SQLCursor with a list of procedures or functions e.t.c in the database. One or more eSQLProcType can be ORed and passed in uProcType to get a list of more than one stored procedure types. A search pattern for the procedure name can also be specified in the first argument. If it's NULL, no criteria will be used in the search.

Columns in the returned cursor are:

ColumnType
1.RECNOfldINT32
2.CATALOG_NAMEfldZSTRING
3.SCHEMA_NAMEfldZSTRING
4.PROC_NAMEfldZSTRING
5.PROC_TYPEfldINT32
6.IN_PARAMSfldINT16
7.OUT_PARAMSfldINT16

Cursor columns are ordered by the procedure name (PROC_NAME).

getColumns()


getColumns (
  pCHAR pszTableName,
  pCHAR pszColumnNamePattern,
  UINT32 uColType,
  ppSQLCursor ppCur)

Given a table name, this method returns a SQLCursor with a list of columns in the table. A search pattern to filter certain column names can also be specified in the second parameter. One or more eSQLColType can be ORed and passed in uColType to get a list of more than one specific column types like RowId, etc.

Columns in the returned cursor are:

ColumnType
1.RECNOfldINT32
2.CATALOG_NAMEfldZSTRING
3.SCHEMA_NAMEfldZSTRING
4.TABLE_NAMEfldZSTRING
5.COLUMN_NAMEfldZSTRING
6.COLUMN_POSITIONfldINT16
7.COLUMN_TYPEfldINT32
8.COLUMN_DATATYPEfldINT16
9.COLUMN_TYPENAMEfldZSTRING
10.COLUMN_SUBTYPEfldINT16
11.COLUMN_PRECISIONfldINT32
12.COLUMN_SCALEfldINT16
13.COLUMN_LENGTHfldINT32
14.COLUMN_NULLABLEfldINT16

Cursor columns are ordered by the procedure name (COLUMN_NAME).

getProcedureParams()


getProcedureParams (
  pCHAR pszProcName,
  pCHAR pszParamName,
  ppSQLCursor ppCur)

Given a procedure name, this method returns a SQLCursor with a list of parameters needed to call the procedure. A search pattern to filter certain parameter names can also be specified in the second parameter.

Columns in the returned cursor are

ColumnType
1.RECNOfldINT32
2.CATALOG_NAMEfldZSTRING
3.SCHEMA_NAMEfldZSTRING
4.PROC_NAMEfldZSTRING
5.PARAM_NAMEfldZSTRING
6.PARAM_TYPEfldINT16
7.PARAM_DATATYPEfldINT16
8.PARAM_SUBTYPEfldINT16
9.PARAM_TYPENAMEfldZSTRING
10.PARAM_PRECISIONfldINT32
11.PARAM_SCALEfldINT16
12.PARAM_LENGTHfldINT32
13.PARAM_NULLABLEfldINT16

Cursor columns are ordered by the procedure name (PARAM_NAME).

getIndices()


getIndices (
  pCHAR pszTableName,
  UINT32 uIndexType,
  ppSQLCursor ppCur)

Given a table name, this method returns a SQLCursor with a list of index columns in the table. One or more eSQLIndexType can be ORed and passed in uIndexType to get metadata about more than one specific index types like Unique , etc.

Columns in the returned cursor are

ColumnType
1.RECNOfldINT32
2.CATALOG_NAMEfldZSTRING
3.SCHEMA_NAMEfldZSTRING
4.TABLE_NAMEfldZSTRING
5.INDEX_NAMEfldZSTRING
6.PKEY_NAMEfldZSTRING
7.COLUMN_NAMEfldZSTRING
8.COLUMN_POSITIONfldINT16
9.INDEX_TYPEfldINT16
10.SORT_ORDERfldZSTRING
11.FILTERfldZSTRING

Cursor columns are ordered by the procedure name (INDEX_NAME).

New methods can be added to SQLMetaData to return metadata about table and column privileges, available SQL functions and SQL keywords, referential integrity constraints, and so on.

Data Type Mapping

Data sources represent data in various formats and a common interface like dbExpress should provide a set of generic data types to get individual field values. dbExpress defines a set of logical data types that will surface most of the SQL data types supported by various databases. These logical datatypes match the ones in BDE for compatibility and also for existing applications to adapt them easily.

However, the mapping from SQL to logical data type may not be the same as in BDE. for example, a new fldDATETIME is introduced to represent timestamp data without any data loss. Similarly, numeric data that can't fit in a double are directly mapped into BCD eliminating the need for enabling or disabling BCD. Even though there is a translation between SQL to logical types and viceversa the performance impact is negligible.

Internally, dbExpress maps database specific SQL types into database specific physical data types and one or more physical data type maps into a logical data type. Unlike the BDE, dbExpress will not expose the physical data types to the clients, however with the current frame work database driver developers can choose to return physical data types as well.

Conclusion

On top of the core interface, application developers can create a layer for caching and thereby provide forward and backward scrolling on the result set. Database connections are sometime very expensive, so a layer for connection pooling can be built. Interfaces for schema creation , import and export data across data sources and all other data-access needs can also be built easily.

However, it is very important to have the core runtime data-access layer thin and simpler to provide a high performance database connectivity and for easily adapting to new data sources.



Server Response from: ETNASC04