|
Database Broker Interface Definition Database broker API Guide
Database Broker Interface Definition A Database Broker API Guide The Database Broker is a C++ class built on top of ODBC Layer. It encapsulates much of the complexity of the ODBC API and provides a uniform interface to application developers. The Database broker class is a higher abstraction of a group of classes developed via the raw ODBC APIs. It is capable of handling dynamic query, batch SQL commands, and database caching, and runtime binding. In the application developments using ODBC, developers usually define C/C++ variables that match what’s in the database table. The approach assumes the database table is defined before the corresponding database classes. There are several problems with the approach:
The Database Broker is designed to solve such problems. It can process any ANSI SQL supported by an ODBC driver at runtime, and perform runtime binding for result sets. Users of the database broker do not need to know ODBC at all.
The Definition of Database Broker In order to use the Database Broker, we first needs to look at the class definition. The detailed description of each function will be given later. class BROKERCLASS Broker { public: enum EnumType {ET_TABLE, ET_COLUMN, ET_DATASOURCE, ET_INDEX, ET_TYPEINFO}; enum BrokerType {DATA_BROKER, RPC_BROKER, HTTP_BROKER, OLE_BROKER, MULTIPLEX_BROKER}; enum InterpretType{IT_C, IT_SQL}; public: Broker(const char* szURL=NULL); Broker(const char* szDSN, const char* szUser, const char* szPasswd); virtual ~Broker(); public: // Database connection virtual BOOL Connect(const char* szDSN, const char* szUser=NULL, const char* szPasswd=NULL, BOOL bCache=FALSE)=0; virtual void Disconnect()=0; public: // direct SQL virtual BOOL ExecuteSQL(const char* szSQL=NULL)=0; public: // interpreter virtual BOOL Interpret(const char* szCmd, void *pIO, int nType)=0; public: // Query operations virtual int SetParameter(int nIndex, short nInOut, short fCType, short fSqlType, int nPrecision, short nScale, const char* value, int nMaxLen, long * pcbValue)=0; virtual long Move(long nRow)=0; virtual long Fetch(long nStartPos= ROWID_CURRENT)=0; virtual BOOL OpenSQL(const char* szQuery, unsigned int nBind= -1)=0; virtual void CloseSQL()=0; virtual BOOL IsOpen()=0; public : // record access virtual char * GetHeader()=0; virtual char * GetRecord(long nRow)=0; virtual long GetRecordLength()=0; virtual long GetRowCount()=0 ; virtual long GetCursorPos()=0; virtual char* GetTypeInfo(const int fSqlType)=0; public: // field access virtual unsigned int GetFieldCount()=0; virtual char * GetFieldValue(long nRow, int nCol)=0; virtual char * GetFieldName(int nCol)=0; virtual int GetFieldNameLen(int nCol)=0; virtual long GetFieldLength(int nCol)=0; virtual long GetFieldSpan(int nCol)=0; virtual int GetFieldType(int nCol)=0; virtual int GetFieldScale(int nCol)=0; virtual int GetFieldPrecision(int nCol)=0; virtual int GetFieldAttribute(int nCol, int fDescType)=0; public: // DML virtual BOOL Delete(long nRow)=0; virtual BOOL Update(long nRow, char**ppNames, char** ppValues)=0; virtual BOOL Insert(char* *ppNames, char** ppValues)=0; virtual char* Enumerate(EnumType nType, char* szParam)=0; public: // transactions virtual BOOL BeginTransaction()=0; virtual BOOL EndTransaction(BOOL bCommit)=0; public: // error handling virtual char* GetErrorString(BOOL bGet=TRUE)=0; public: // not dispatched . virtual BOOL IsFieldNumeric(int nCol)=0; virtual int MaxFieldNameLen()=0; virtual int IsFieldUpdatable(int nCol)=0; virtual int GetFieldPos(const char* szFieldName)=0; virtual const char* GetSQLStatement()=0; virtual const char* GetDSN()=0; public: virtual BOOL FindObject(const char* szDSN, const char* szTable, const char* szField=NULL, const char* szUser=NULL, const char* szPasswd=NULL)=0; char* GetServerURL(){return m_szURL;}
public: char * m_szURL; }; The Database Broker is a pure virtual class as can be seen in the definition. Different broker can be implemented without disturbing the user of the class. The BrokerType shows that the Database Broker, the HTTP Broker, the RPC Broker, the OLE Broker, and MULTIPLEX_BROKER are implemented. Each type of broker accesses database through different network protocols but the result should be the same. We focus to the DATA_BROKER in the document.The interface can be divided into three major categories:
Using Database Broker in Your Application: The distribution package of the Broker contains four subdirectories:
Processing SQL Statements Using the Database Broker to process a SQL statements other than query is very simple, just pass the SQL statement to the ExecuteSQL function after a successful database connection. The function returns TRUE if the statement was executed successfully. Here is a general procedure for processing such SQL statement.
You can use OpenSQL function if there are runtime parameters in the SQL Statement, and then use the SetParameter function to assign values to the parameters. Call the ExecuteSQL to execute the SQL command. Processing Query Statements The Database Broker automatically allocates required memory space for each record and it’s fields when a query statement is opened. The selected field values are converted to character strings.
You can use OpenSQL function if there are runtime parameters in the SQL Statement, and then use the SetParameter function to assign values to the parameters. Call the ExecuteSQL to execute the SQL command. The following code demonstrates how to process a SQL dynamically. The SQL statement is passed to the function at run-time. The function prints the returned records on the console screen. For simplicity, error handling is omitted from the sample. // this sample shows how to process a SQL query. long Query(const char * szDataSource, const char* szUser, const char * szPassword, const char * szSQL) { int nTotalRows=0; Broker *pBroker =CreateBroker(Broker::DATA_BROKER, NULL); // make a database connection , without user name and pwd if(!pBroker->Connect(szDataSource, szUser, szPassword)) { // we have trouble connecting to the database printf("Unable to connect to the database %s\n", szDataSource); return 0; }
if(pBroker->OpenSQL(szSQL)) { printf("%s\n", pBroker->GetHeader()); long rows=0; while((rows=pBroker->Fetch())>0) { // we fetched n rows for(int i=0; i<rows; i++) { printf("%s\n", pBroker->GetRecord(i)); } nTotalRows +=rows; } } else { // some thing wrong, get the error string printf("Error open query, %s\n", pBroker->GetErrorString()); } // we are done DestroyBroker(pBroker); return nTotalRows; } Processing Stored Procedures Stored procedures differ widely from one DBMS to another. How it is implemented and supported depends very much on the ODBC driver. In this section, we will demonstrate how to process a simple stored procedure and how to pass parameters to stored procedures. The steps for processing stored procedure is very much like processing a SQL statement, except that stored procedure may require input or output parameters that must be set:
The following example demonstrates how to pass input and output parameter to stored procedures. Our simple stored procedure has one input parameter and one output parameter. Both are numerical. The procedure does nothing but assign the input parameter to the output parameter. In other words, whatever number we pass to the store procedure as input will be returned as output. Store procedure for SQL Server: CREATE PROC assignment @in_param decimal(15, 0), @out_param decimal(15, 0) output AS @out_param = @in_param Store procedure for ORACLE server. create procedure assignment(in_param IN NUMBER, out_param OUT NUMBER) AS BEGIN out_param := in_param END; The following function invokes the stored procedure and returns the value passed back from the stored procedure. long foo(long nInput) { int nOutput= -1; Broker * pMyBroker = CreateBroker(0, NULL); if(!pMyBroker->Connect("YourDSN", "sa", "")) return rc; if(pMyBroker->OpenSQL("{call assignment(?, ?)}")) { long cbOut = sizeof(nInput); // bind the input parameter. rc=pMyBroker->SetParameter(0, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_DECIMAL, 15, 0, (char*)&nInput, 0, &cbOut); // bind the output parameter. rc=pMyBroker->SetParameter(0, SQL_PARAM_OUTPUT, SQL_C_LONG, SQL_DECIMAL, 0, 0, (char*)&nOutput, sizeof(nOutput), &cbOut); if(!pMyBroker->ExecuteSQL()) { printf("Huston, we got a problem.\n"); } } DestroyBroker(pMyBroker); // nOutput contains the returned values from the stored // procedure if successful. return nOutput; }
This section contains detailed description of the functions in the Database Broker class. Broker(const char* szURL=NULL) The constructor of the Broker object. szURL)Where szURL is the URL of the database server. szURL The URL of the sever that are running on another host. For instances, the szURL may point to a host name MyMachine with an instance of Database Broker running. The URL should also contain necessary information for logon to the DBMS. This constructor is used for distributed processing where the database operations are spread on different machines. The parameter should be NULL if the broker is local.
There is no returned value for constructors. Broker myBroker = new Broker("rpc://www.myhost.com?DataSource=TestDbase&UserName="tester");
Use this constructor to create a broker that is capable of accessing other Brokers in the operating environment. This constructor is also the default constructor, which create a broker without any database connection. Database connection can be established using the Connect function later. Broker(const char* szDSN, const char * szUserName, const char *password) The constructor of the Broker object with database connection information Broker(const char* szDSN, const char * szUserName, const char *szPassword)
The szUserName and szPassword parameter can be empty if no authentication is required on the specified database server.
There is no returned value for constructors. Broker myBroker = new Broker("TestDB", "scott", "tiger");
Use this constructor to create a broker that establishes a database connection immediately. The function makes an ODBC connection to the specified database.
The szUserName and szPassword parameter can be empty if no authentication is required on the specified database server.
The function returns true if successful, otherwise FALSE. If(!myBroker->Connect("TestDB", "scott", "tiger")) Printf("Database connection failed.\n");
Use this function to establish a database connection. Any open connection from previous calls will be closed. Any open SQL statement associated with previous connection will also be closed automatically.
BOOL OpenSQL(const char* szSQL, int nBindType = -1) The function open a SQL statement.
The function returns true if the SQL statement is opened successfully. FALSE if there is an error in the supplied statement. If(!myBroker->OpenSQL("select * from customer")) Printf("could not open the sql statement.\n");
Use this function to open a SQL statement. Any previous opened statement will be closed. The broker does automatic binding in most of the cases. It allocates necessary buffer for the result set if there is any. The default binding for a query statement is character strings. This is the preferred bind type for the object as all columns are bound uniformly and almost all data fields could be converted to strings. For a query statement, the function executes the SQL statement immediately if there is no parameter in the statement. You should always test if the function is successful or not before executing the statement or fetching the result set.
long Fetch(long nStartPosition=ROWID_CURRENT) The function fetches result set associated with a query statement.
The function returns the number of rows fetched from the database. The returned value is 0 if there is no data available and SQL_ERROR if the cursor is positioned at end of the result set. while((rows=myBroker->Fetch())>0) { // we fetched n rows for(int i=0; i<rows; i++) { printf("%s\n", myBroker->GetRecord(i)); } }
The broker performs batch fetching when the Fetch function is called. So there are multiple rows in the buffer after the operation. The cursor is positioned to the start position plus the number of rows fetched after each successful fetch. The broker calls the Move function to position the cursor to the specified row before fetching any data into the buffer. There are three special row position defined in the object:
long ExecuteSQL(const char* szSQL=NULL) The function executes the specified SQL statement or an opened SQL statement (Prepared statement).
The function returns TRUE if the statement is executed successfully. if((rows=myBroker->ExecuteSQL("delete from junktable"))>0) { printf("Number of rows deleted : %d\n", myBroker->GetRowCount()); }
The function can be used to execute any SQL statement other than query directly. Query statements without parameters are executed when OpenSQL is called. However, the ExecuteSQL function should be called if there is any runtime parameter for query statements. The number of rows affected can be obtained by calling GetRowCount function.
Move the cursor to the specified location.
The number of rows fetched after the move. A 0 or negative value indicates the move has failed. if((rows=myBroker->Move(0))>0) { printf("Number of rows fetched : %d\n", rows); }
The function not only moves the cursor to the specified location, but also fetches record into buffers from the location. The function can be used to randomly access the result set. Depends on your DBMS, Moving to the last row in a result set the first time may cause ODBC to fetch all data in the result set and may take a little longer. Delete a record in the result set.
The function returns TRUE if the record is deleted successfully. // delete the first row. if(myBroker->Delete(0)>0) { printf("The first row is deleted"); }
The function deletes the specified record in the current result set. The result set must be opened using the OpenSQL function and there should be no multiple table join. BOOL Update(long nRow, const char**ppNames, const char* ppValues) Update a record the new values. BOOL Update(long nRow, const char**ppNames, const char* ppValues);
The function returns TRUE if the record is updated successfully. // update the first row. char * pNames[]={"EmployeeId", "EmployeeName", "EmployeePhone", NULL}; char * pValues[]={"873245", "SQLData", "(301)-515-0477", NULL); if(myBroker->Update(0, &pNames, &pValues)>0) { printf("The first row is updated"); }
The function updates the specified record in the current result set with a set of new values. The result set must be opened using the OpenSQL function which select data from only a single table. Note, there may be more than one row updated if the field selected by the query statement could not uniquely identify a record. These can be avoided by put the key field in the SELECT clause of the query statement. BOOL Insert(const char**ppNames, const char* ppValues) Insert a record into the current table.
The function returns TRUE if the record is updated successfully. // insert the first row. char * pNames[]={"EmployeeId", "EmployeeName", "EmployeePhone", NULL}; char * pValues[]={"873245", "SQLData", "(301)-515-0477", NULL); if(myBroker->Insert(&pNames, &pValues)>0) { printf("The record is added into the table successfully."); }
The function adds the specified record to the current table. The result set must be opened using the OpenSQL function. Note: The newly added record may not be reflected in the current result set immediately. Reopen the result set to load the new record when needed.
Close an opened SQL Statement. None.
none Call the function to close a SQL statement immediately. A SQL Statement is closed automatically under the following situations:
Start a new transaction. None.
TRUE if the operation is successful. The default behavior of the server is to commit each SQL statement if successful. Use the BeginTransaction to control transactions in your own way. You must commit your transaction manually after the function call. Note that some DBMS do not support transaction, the function will have no effect under such situation. Consult your ODBC driver document for further information.
BOOL EndTransaction(BOOL bCommit) Terminate a transaction. BOOL EndTransaction(BOOL bCommit) bCommit: The transaction is committed if bCommit is TRUE. The transaction is rolled back otherwise.
TRUE if the operation is successful. Use this function to commit or roll back your transactions.
Retrieve an error string. None.
The formatted error string from the ODBC layer. Call the function if an operation fails. The error string contains the details about the nature of the error, the ODBC error code, the SQL state and the native error.
Get the total number of rows affected by the SQL statement. None.
The number of rows affected by the SQL statement. long nRows = pBroker->GetRowCount();
The function returns the total number of rows in the result set if the SQL statement is a query. The returned value is the number of rows affected by the statement in all other cases. Depends on the DBMS, the ODBC driver may have to fetch all records in order to determine the total number of rows selected by a query statement. So, it may be a very expensive operation in such a situation. The function should be used after OpenSQL function for queries and after ExecuteSQL for all other SQL statements.
Get the number of fields in a SQL statement. None.
The number of fields selected by a query statement. long nFields = pBroker->GetFieldCount();
The function should be used after a query statement is opened successfully.
Get a record in the current buffer.
A character string that contains all formatted field values in the record. NULL is returned if the specified row doesn’t exist in the buffer. while((rows=pBroker->Fetch())>0) { // we fetched n rows for(int i=0; i<rows; i++) { printf("%s\n", pBroker->GetRecord(i)); } nTotalRows +=rows; }
The function should be used after either a success move or fetch operation. The returned value is formatted based on the width of each column and the data type of the column. The formatted string align perfectly with the record header (contains a string of all field names) returned by the GetHeader function if fixed pitch font is used.
Get header of the result set. none.
A character string that contains all formatted field names in the result set. NULL is returned if there is an error. printf("%s\n", pBroker->GetHeader());
The function should be used after a query statement is opened successfully.. The returned value is formatted based on the width of each column and the data type of the column. The formatted string aligns perfectly with the data returned by the GetRecord function, if fixed pitch font is used.
char * GetFieldValue(long nRow, int nCol) Get a single field value in a record.
A character string that contains the value of the field, NULL is returned if either nRow or nCol is out of bound. int nFieldCount= pBroker->GetFieldCount(); while((rows=pBroker->Fetch())>0) { for(int i=0; i<rows; i++) { for(int j=0; j<nFieldCount; j++) printf("%s\n", pBroker->GetFieldValue(i, j)); } }
The function should be used after result is fetched into the internal buffer.
Get name of a field.
A character string that contains the name of the field, NULL is returned if nCol is out of bound. // Let’s print out all field names int nFieldCount= pBroker->GetFieldCount(); for(int j=0; j<nFieldCount; j++) printf("%s\n", pBroker->GetFieldName(j));
The function should be used after a query is opened successfully.
Get the current cursor positron. None.
The current cursor position.. long nRows = pBroker->GetCursorPos();
The current cursor position is actually the row id in the result set. You can use the function to remember the current cursor position, moves the cursor to another row and perform some operation, and then restore the cursor position. The returned value can be used as the parameter for the Move function.
Get the length of a field.
The size of the column as defined by the database table. int nFieldCount= pBroker->GetFieldLength(); char * szFieldValue = new char[nFieldCount+1]; strcpy(szFieldValue, pBroker->GetFieldValue(0, 0));
The length of a field can be understand as the maximum number of characters in the column when it is converted to a string.
Get the ODBC data type of a field.
The ODBC data type of the field. // here is some of the common ODBC data types: int nFieldType= pBroker->GetFieldType(); switch(nFieldType) { case SQL_SMALLINT: case SQL_INTEGER: case SQL_TINYINT: case SQL_BIGINT: case SQL_REAL: case SQL_FLOAT: case SQL_DOUBLE: case SQL_TIME: case SQL_DATE: case SQL_TIMESTAMP: break; case SQL_DECIMAL: case SQL_NUMERIC: break; case SQL_BINARY: case SQL_VARBINARY: case SQL_CHAR: case SQL_VARCHAR: break; case SQL_LONGVARCHAR: case SQL_LONGVARBINARY: break; }
The data type returned may not directly map to the native DBMS data types. int GetFieldAttribute(int nCol, int nDescType) Get the one of the attributes of a field.
The value of the attribute. //find out if the first field is searchable. int nSearchable= pBroker->GetFieldAttribute(0, SQL_DESC_SEARCHABLE);
The function returns only the numeric attribute associated with the field. A char * Enumerate(EnumType nType, char * szParam) Enumerate database tables, data sources and field names.
The function returns a value each time when it’s called. It returns NULL to indicate the end of the enumeration. //make a database connection pBroker->Connect("MyDbase", "admin", ""); // enumerate all tables in MyDbase while((szTableName= pBroker->Enumerate(ET_TABLES, NULL))) { printf("%s\n", szTableName); }
The function returns one value at a time until all the values are enumerated. Database connection is required for ET_TABLE and ET_COLUMN.
SetPrameter SetParameter binds a buffer to a parameter marker in an SQL statement. Syntax BOOL SetParameter(int nIndex, short nInOut, short fCType, short fSqlType, int nPrecision, short nScale, const char* value, int nMaxLen, long * pcbValue); Parameters Return The function returns TRUE if the parameter is set successfully, FALSE otherwise. Example
Remarks The function is just a wrapper of the ODBC SQLBindParameter function. \Please refer to your ODBC document if you need further information.
|
Send mail to info2-at-sqldata-dot-com with questions or comments about
this web site.
|