Introduction
Open Database Connectivity (ODBC) is a standard definition of an application programming interface (API) used to access data in a relational database. Mimer SQL supports ODBC as one of its native APIs for applications written in C, C++, Microsoft Visual Basic and a large number of other development tools.
ODBC enables an SQL database to become an integral part of an application. SQL statements can be incorporated into the application, allowing the application to retrieve and update values from a database. Values from the database can be placed in program variables for manipulation by the application. Conversely, values in program variables can be written to the database.
ODBC defines a call-level interface (CLI). A CLI is defined as a set of function calls and their associated parameters. A CLI definition uses a native programming language to call functions; therefore a CLI requires no extensions to the underlying programming language. This contrasts with an embedded API, such as Embedded SQL, where the API is defined as an extension of the source code for a programming language and applications using the API must be precompiled in a separate step.
ODBC aligns with the following specification and standard for relational SQL database CLI definitions:
- The X/Open CAE specification Data Management: SQL Call-Level Interface (CLI)
- ISO/IEC 9075-3:1995 (E) Call-Level Interface (SQL/CLI)
The Mimer SQL ODBC driver complies with the Microsoft Win32 ODBC 3.51 specification. The driver supports applications written with the ODBC 2.5 or earlier versions of the ODBC functions in a manner defined in the ODBC 3.51 specification.
The Mimer SQL setup process automatically installs an ODBC driver when the Mimer SQL client is installed on any Windows, Linux or macOS platform. Mimer SQL programs that are written using the ODBC API communicate with Mimer SQL through C function calls. The Mimer SQL-specific versions of the ODBC functions are implemented in a Mimer SQL ODBC driver. The driver passes SQL statements to Mimer SQL and returns the results of the statements to the application.
This is not intended to be a complete guide to the functionality provided by ODBC but it does provide a good introduction to accessing Mimer SQL through ODBC. Where examples are provided, C is used. The ODBC function calls in the examples use ODBC 3.5 syntax, although they are not dependent on a Windows platform. It should be possible to use the examples as a basis for translation into other languages.
The examples are based on the sample schema that is provided as part of the Mimer SQL distribution. They assume that an ODBC data source named HOTELDB has been created, which connects to the Mimer SQL database that contains the example schema.
See also the information on ODBC in the Programmer’s Manual part of the Mimer SQL Documentation Set.
Function
ODBC allows an application to access a Database Management Systems (DBMS) by requiring a DBMS-specific library, or driver. The driver implements the functions of the ODBC API for the specific DBMS. To use a different DBMS the application does not need to be changed; instead a new driver is loaded and the application calls the functions within it. How drivers are supported is operation system specific, in the Windows environment drivers are dynamic-link libraries (DLLs).
Applications can submit SQL statements using ODBC or DBMS-specific grammar. If a statement uses ODBC grammar that is different from the DBMS-specific grammar, the driver converts it before sending it to the database server.
Although the use of drivers solves the problem of accessing multiple DBMSs, there is still the requirement to load the correct driver; ODBC provides a Driver Manager to perform this task. The Driver Manager implements all of the ODBC functions, mainly by passing calls onto the relevant driver, and is either statically linked with the application or loaded at run time.
When an application requires a specific driver, it first requests a connection handle with which to identify the driver and then makes a request to the Driver Manager to load that driver. To call an ODBC function in the driver, the application actually makes a call to the function in the Driver Manager and passes the connection handle for the relevant driver.
The Microsoft ODBC Driver Manager is supplied with the Mimer SQL client in the Windows environment but a third-party product has to be installed on other platforms if the functionality of an ODBC Driver Manager is desirable (it is possible to use the Mimer SQL ODBC Driver directly from the application – the driver manager is transparent).
So that an application can determine which features a driver and the underlying DBMS support, ODBC provides two functions (SQLGetInfo and SQLGetFunctions) that return information about the driver and DBMS capabilities and a list of functions the driver supports. ODBC defines API and SQL grammar conformance levels, which indicate the range of features supported by the driver. The development of Mimer SQL is committed to supporting the relational database standards defined by organizations such as ANSI, ISO and X/Open and Mimer SQL provides a high level of conformance.
The final concept within ODBC is a data source, which specifies the DBMS to connect to. Each ODBC data source on a client computer has a unique data source name (DSN) and ODBC applications connect to a database through an ODBC data source. An ODBC data source for Mimer SQL maps onto the database name to connect to. The definition of the Mimer SQL database (e.g. name, network address of where the database resides and network protocol to be used) is managed through the Mimer Administrator on Windows or by using the sqlhosts file on other platforms. It is important to realize that there is a distinction between a database name and a data source name. There can be more than one data source name pointing to a single database and there can be a data source named default that defines the default database to connect to if a specific data source is not specified. The application is not aware of whether the database is held locally or remotely and it is very simple for a programmer to develop against a local copy of a database and then switch to a central database by redefining the database that the data source points to.
There are three types of data source – system, user and file. User data sources are only visible to a particular user but system data sources are available to all users of the client machine. File data sources are discussed under Making a Connection.
In the Windows environment the data sources are managed through the Mimer Administrator or the ODBC Administrator (which is installed in Control Panel) and under Linux there is a .odbc.ini file that defines the data source names.
Example
As a basis for the example below, the HOTELDB example schema can be installed into your Mimer SQL database using the crehotdb.sql statements file. Use, for example, the read command in the BSQL program to executed it.
To make ODBC function calls, a C or C++ program must include the sqlext.h header file (a copy is supplied with the Mimer SQL distribution); by including this header file, sql.h is automatically included. ODBC applications must be linked with the Driver Manager; on Windows this is the file ODBC32.LIB, on other platforms you will need to check the documentation supplied with the ODBC Driver Manager.
In the various examples there are various references to macros (e.g. SQL_ERROR), these are defined in the sql.h header file. SQL_NTS indicates a null terminated string; hopefully other names are self-explanatory.
A C program that calls the ODBC API typically requires the following declarations:
#if defined(_MSDOS) || defined(WIN32) #include #endif #include #include #include #include "sqlext.h" SQLHENV henv; // Environment handle for application SQLHDBC hdbc; // Connection handle SQLHSTMT hstmt; // Statement handle
Handles identify a particular item; in ODBC this item can be an environment, connection, statement or descriptor. When the application calls SQLAllocHandle, the Driver Manager creates a new item of the specified type and returns the handle to the application. The application uses the handle to identify that item when calling ODBC functions.
The first task for any ODBC application is to initialize the ODBC environment by allocating an environment handle (SQL_HANDLE_ENV):
/* Allocate environment handle */
if ( SQLAllocHandle( SQL_HANDLE_ENV,
SQL_NULL_HANDLE,
&henv ) == SQL_ERROR )
{
printf( "Failed to allocate environment handle\n" );
. . .
}
Before an application allocates a connection it should declare the version of ODBC that it has been written for (this mainly affects SQLSTATE values and datetime data types), and then allocate a connection handle:
/* Set the ODBC version environment */
SQLSetEnvAttr( henv,
SQL_ATTR_ODBC_VERSION,
(SQLPOINTER)SQL_OV_ODBC3,
SQL_IS_INTEGER );
/* Allocate connection handle */
if ( SQLAllocHandle( SQL_HANDLE_DBC,
henv,
&hdbc ) == SQL_ERROR )
{
printf( "Failed to allocate connection handle\n" );
. . .
}
Making a Connection
After an ODBC data source has been defined, ODBC applications can connect to Mimer SQL by using the data source name. There are a number of mechanisms to get the information required to make a connection; some applications supply the connection details, others use the ODBC dialog box to allow the user to complete the information.
The simplest form of connection uses SQLConnect, which requires a data source name, user ID and password:
SQLRETURN retcode;
. . .
/* Set connection timeout - 10 seconds */
SQLSetConnectAttr( hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)10, 0 );
/* Connect - DSN, User ID, Password */
retcode = SQLConnect( hdbc,
(SQLCHAR*) "HOTELDB",
SQL_NTS,
(SQLCHAR*) "HOTELADM",
SQL_NTS,
(SQLCHAR*) "HOTELADM",
SQL_NTS );
if ( retcode == SQL_SUCCESS
|| retcode == SQL_SUCCESS_WITH_INFO )
{
/* User connected */
SQLDriverConnect allows the driver to connect by supplying the connection information as a number of keyword-value pairs:
"DSN=HOTELDB;UID=HOTELADM;PWD=HOTELADM;"
There is an option for the Driver Manager to enter into a dialog with the user to complete any missing connection information (the handle of the parent window needs to be supplied to use this facility). In the following example, the Driver Manager displays a window containing a combo box listing all the Mimer SQL database names and prompts for the username and password:
SQLCHAR OutConnectString[256];
SQLSMALLINT StringLength;
. . .
retcode = SQLDriverConnect( hdbc,
hwnd,
(SQLCHAR*) "DRIVER=Mimer;",
SQL_NTS,
(SQLCHAR*)OutConnectString,
sizeof(OutConnectString),
&StringLength,
SQL_DRIVER_COMPLETE );
if (SQL_SUCCEEDED(retcode))
{
/* User connected */
printf( "connection string used: %s\n", OutConnectString );
Note that the macro SQL_SUCCEEDED replaces the test against SQL_SUCCESS or SQL_SUCCESS_WITH_INFO.
You may wish to have more control over the interaction with the user, SQLDataSources provides a mechanism to get information about the data sources configured on the client:
SQLCHAR DSNname[SQL_MAX_DSN_LENGTH+1];
SQLCHAR driver[33];
. . .
/* Enumerate the system data source names */
retcode = SQLDataSources( henv,
SQL_FETCH_FIRST_SYSTEM,
(SQLCHAR*)DSNname,
sizeof(DSNname),
NULL,
(SQLCHAR*)driver,
sizeof(driver),
NULL );
while (SQL_SUCCEEDED(retcode))
{
printf( "%-32s %s\n", DSNname, driver ); // Display details
/* Fetch next */
retcode = SQLDataSources( henv,
SQL_FETCH_NEXT,
(SQLCHAR*)DSNname,
sizeof(DSNname),
NULL,
(SQLCHAR*)driver,
sizeof(driver),
NULL );
}
Another way of making a connection is to create a File data source. The file has a .dsn extension and contains the keyword-value pairs to make the connection. Although it is possible to include the password, this would make the system insecure and therefore has little to recommend it:
[ODBC] DSN=HOTELDB UID=HOTELADM
To make a connection using a File data source, use the option for the Driver Manager to enter into a dialog with the user to complete any missing connection information (again, the handle of the parent window needs to be supplied to use this facility):
retcode = SQLDriverConnect( hdbc,
hwnd,
(SQLCHAR*) "FILEDSN=hotel.dsn;",
SQL_NTS,
(SQLCHAR*)OutConnectString,
sizeof(OutConnectString),
&StringLength,
SQL_DRIVER_COMPLETE );
if (SQL_SUCCEEDED(retcode))
{
/* User connected */
After connection to the database use SQLGetInfo to determine the capabilities of the driver and the data source associated with the connection:
/* Display DBMS version details */
SQLGetInfo( hdbc,
SQL_DBMS_VER,
(SQLPOINTER)&str_value,
sizeof(str_value),
&str_len );
printf( "%s\n", str_value );
/* Display SQL conformance level */
SQLGetInfo( hdbc,
SQL_SQL_CONFORMANCE,
(SQLPOINTER)&int_value,
sizeof(int_value),
NULL );
if (int_value & SQL_SC_SQL92_ENTRY)
printf( "Entry level SQL-92\n" );
if (int_value & SQL_SC_FIPS127_2_TRANSITIONAL)
printf( "FIPS 127-2 transitional level\n" );
if (int_value & SQL_SC_SQL92_INTERMEDIATE)
printf( "Intermediate level SQL-92\n" );
if (int_value & SQL_SC_SQL92_FULL)
printf( "Full level SQL-92\n" );´
When the application has finished using a data source, it calls SQLDisconnect. After disconnecting, the application should call SQLFreeHandle to release the connection handle and, if appropriate, to release the environment handle.
Error Handling
ODBC returns diagnostic information in two ways: a return code indicating the success or failure of the ODBC function and diagnostics records, providing detailed information. In general, program logic uses the return code to detect a failure and then the diagnostic records to detail the reason for the failure.
If the ODBC driver returns a code indicating anything other than SQL_SUCCESS then the application can call SQLGetDiagRec to retrieve any warning or error messages:
SQLCHAR msg[SQL_MAX_MESSAGE_LENGTH+1];
SQLCHAR sqlstatus[6];
SQLSMALLINT msglen, msgno;
SQLINTEGER nativeerror;
. . .
msgno = 1;
while (SQLGetDiagRec( SQL_HANDLE_DBC,
hdbc,
msgno++,
sqlstatus,
&nativeerror,
msg,
sizeof(msg),
&msglen) == SQL_SUCCESS)
{
msg[msglen] = '\0';
printf( "SQLSTATE: %s\n", sqlstatus );
printf( "Native: %d\n", nativeerror );
printf( "Message: %s\n", msg );
printf( "\n" );
}
Diagnostic records are associated with the ODBC handles: environment, connection, statement and descriptor. SQLGetDiagRec requires the handle type and the handle, making the coding of a general-purpose error handler more complex than other programming interfaces.
A warning is indicated by an SQLSTATE class value of ’01’ (e.g. ‘01000’).
Transaction Processing
A transaction is an essential part of database programming. It defines the beginning and end of a series of database operations that are regarded as a single unit. For example, to transfer money between two bank accounts, an amount is subtracted from one account and the same amount is added to the other account. It is essential that either both of these operations succeed or neither does.
Mimer SQL uses a method for transaction management called Optimistic Concurrency Control (OCC). OCC does not involve any locking of rows as such, and therefore cannot cause a deadlock. Most other DBMSs offer pessimistic concurrency control. Pessimistic concurrency control protects a user’s reads and updates by acquiring locks on rows (or possibly database pages, depending on the implementation). These locks may force other users to wait if they try to access the locked items. The user that ‘owns’ the locks will usually complete their work, committing the transaction and thereby freeing the locks so that the waiting users can compete to attempt to acquire the locks. By completely eliminating the complicated locking overheads required by other DBMSs, Mimer SQL is able to avoid problems such as data being left inaccessible as a result of locks being held over user interactions or as a result of client processes failing.
Transactions in ODBC are usually managed at the connection level, although there is the option of applying a commit or rollback for all connections within an environment. When a transaction is explicitly committed against a connection, the operations performed on all statement handles on that connection are committed. On a transaction commit, Mimer SQL checks whether the transaction is valid and returns an error if a conflict is identified. If a conflict is encountered the application determines how to continue, for example whether to automatically retry the transaction or inform the user of the failure.
There are two modes for managing transactions within ODBC. SQLSetConnectAttr is used to switch between the Autocommit and Manual-commit modes. Autocommit mode is the default transaction mode for ODBC; when a connection is made, it is in autocommit mode until SQLSetConnectAttr is used to switch autocommit mode off.
In autocommit mode each individual statement is automatically committed when it completes successfully, no explicit transaction management functions are necessary; however, the return code from the function must still be checked as it is possible for the implicit transaction to fail. When autocommit is disabled (i.e. manual-commit) all executed statements are included in the same transaction until calling SQLEndTran specifically completes it. When an application turns autocommit off, the next statement against the database starts a transaction. The transaction continues until SQLEndTran is called with either SQL_COMMIT or SQL_ROLLBACK; the next command sent to the database after that starts a new transaction.
Transactions are completed (either committed or rolled back) by use of the ODBC function SQLEndTran rather than using the SQL COMMIT or ROLLBACK statements.
Calling SQLEndTran with a request to rollback a transaction causes Mimer SQL to discard any changes made since the start of the transaction and to end the transaction.
/* Disable transaction autocommit mode */
SQLSetConnectAttr( hdbc, SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_OFF, 0 );
SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt);
retry:
/* First statement against Mimer SQL starts a transaction */
SQLExecDirect( hstmt,
"update ROOM_PRICES set PRICE = PRICE * 1.05 \
where HOTELCODE = 'LAP'", SQL_NTS );
SQLExecDirect( hstmt,
"update ROOM_PRICES set PRICE = PRICE * 1.08 \
where HOTELCODE = 'WIND'", SQL_NTS );
printf( "Commit transaction? : " );
scanf( "%s", ans );
if (ans[0] == 'Y'
|| ans[0] == 'y')
{
retcode = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_COMMIT );
if (retcode == SQL_ERROR)
{
/* Check SQLSTATE for transaction conflict */
SQLGetDiagField( SQL_HANDLE_DBC,
hdbc,
1,
SQL_DIAG_SQLSTATE,
sqlstatus,
sizeof(sqlstatus),
&msglen);
if (strcmp( sqlstatus, "40001" ) == 0) goto retry;
goto display_error;
}
}
else
{
SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_ROLLBACK );
}
To set the transaction isolation level, use the SQL_ATTR_TXN_ISOLATION connection attribute. The default isolation level for Mimer SQL is SQL_TXN_REPEATABLE_READ.
Execute a Command
The simplest way to execute a statement is to execute it directly using the SQLExecDirect function. Each INSERT, UPDATE, and DELETE statement returns the number of rows affected by the operation, the function SQLRowCount returns this count:
SQLINTEGER rowcount;
. . .
/* Allocate statement handle */
SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt);
SQLExecDirect( hstmt,
"update ROOM_PRICES set PRICE = PRICE * 1.05 \
where HOTELCODE = 'LAP'", SQL_NTS );
SQLRowCount( hstmt, &rowcount );
printf( "%d rows have been updated\n", rowcount );
Where an SQL statement will be repeatedly executed it is more usual to use prepared execution, as a means to reduce the parsing and compilation overheads. Mimer SQL version 8.2 reduces the performance difference between direct and prepared execution by maintaining and re-using compiled statements on the server.
In this example each of the parameters in the prepared SQL statement (indicated by ‘?’) are bound to a variable in the application before the statement is executed:
SQLREAL SQLFLOAT price;
SQLCHAR hotel[5];
SQLINTEGER priceInd, hotelInd;
. . .
SQLPrepare( hstmt,
"update ROOM_PRICES \
set PRICE = PRICE * ?cast((cast(PRICE as float) * ?) as integer) \
where HOTELCODE = ?", SQL_NTS );
SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, FLOAT,SQL_C_DOUBLE,
SQL_REAL, 7, 0,
&price, 0, &priceInd );
SQLBindParameter( hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, 4, 0,
hotel, sizeof(hotel), &hotelInd );
/* Set parameter values and length/indicator */
price = 1.05;
strcpy( hotel, "LAP" );
hotelInd = SQL_NTS;
SQLExecute( hstmt );
SQLRowCount( hstmt, &rowcount );
printf( "%d rows have been updated\n", rowcount );
Similarly it is possible to prepare an SQL statement that calls a stored procedure:
SQLCHAR room[7];
SQLCHAR charge[4];
SQLINTEGER roomInd, chargeInd;
. . .
SQLPrepare( hstmt, "{call CHARGE_ROOM( ?, ? )}", SQL_NTS );
SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, 6, 0,
room, sizeof(room), &roomInd );
SQLBindParameter( hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, 3, 0,
charge, sizeof(charge), &chargeInd );
/* Set parameter values and length/indicator */
strcpy( room, "SKY101" );
roomInd = SQL_NTS;
strcpy( charge, "900" );
chargeInd = SQL_NTS;
SQLExecute( hstmt );
Parameters in procedure calls can be input, input/output, or output. A more complicated example illustrates how to handle an output parameter:
SQLCHAR hotel[4];
SQLCHAR roomtype[7];
SQLCHAR arrival[20];
SQL_DATE_STRUCT depart;
SQLUINTEGER rooms;
SQLINTEGER hotelInd, roomtypeInd, arrivalInd, departInd, roomsInd;
SQLSMALLINT numparams;
. . .
SQLPrepare( hstmt, "{call FREEQ( ?, ?, ?, ?, ? )}", SQL_NTS );
SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, 3, 0,
hotel, sizeof(hotel), &hotelInd );
SQLBindParameter( hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, 6, 0,
roomtype, sizeof(roomtype), &roomtypeInd );
SQLBindParameter( hstmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_TYPE_DATE, 0, 0,
arrival, sizeof(arrival), &arrivalInd );
SQLBindParameter( hstmt, 4, SQL_PARAM_INPUT, SQL_C_TYPE_DATE,
SQL_TYPE_DATE, 0, 0,
&depart, 0, &departInd );
SQLBindParameter( hstmt, 5, SQL_PARAM_OUTPUT, SQL_C_ULONG,
SQL_INTEGER, 10, 0,
&rooms, 0, &roomsInd );
/* Set statement timeout - 10 seconds */
SQLSetStmtAttr( hstmt, SQL_ATTR_QUERY_TIMEOUT, (SQLPOINTER)10, 0 );
SQLNumParams( hstmt, &numparams );
printf( "statement contains %d parameters\n", numparams );
/* Set parameter values and length/indicator */
strcpy( hotel, "STG" );
hotelInd = SQL_NTS;
strcpy( roomtype, "SSGLS" );
roomtypeInd = SQL_NTS;
strcpy( arrival, "{d'2002-12-24'}" ); // Date as char string
arrivalInd = SQL_NTS;
depart.year = 2003; // Date as data structure
depart.month = 1;
depart.day = 8;
SQLExecute( hstmt );
printf( "%d rooms are available\n", rooms );
A statement handle is released by calling SQLFreeHandle; however, it is more efficient to reuse statement handles rather than freeing them and allocate new ones. When SQLFreeHandle is called, the driver releases the associated structure. SQLDisconnect automatically frees all statements on a connection.
Result-set Processing
There are two ways of processing a result-set. One method uses SQLBindCol to bind applications variables to the columns of the result set. When each row of data is fetched, the column data is copied to the application variables. The following example also illustrates how to use the indicator variable; this either returns the length of character data (a negative length indicates that truncation has taken place), or SQL_NULL_DATA if the data is NULL:
SQLCHAR name[16];
SQLCHAR city[16];
SQLINTEGER nameInd, cityInd;
. . .
/* Allocate statement handle */
SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt);
SQLBindCol( hstmt, 1, SQL_C_CHAR, name, sizeof(name), &nameInd );
SQLBindCol( hstmt, 2, SQL_C_CHAR, city, sizeof(city), &cityInd );
SQLExecDirect( hstmt, "select NAME, CITY from HOTEL", SQL_NTS );
while ((retcode = SQLFetch( hstmt )) != SQL_NO_DATA)
{
printf( "%-15s ", name );
if (cityInd == SQL_NULL_DATA)
printf( "*null*\n" );
else
printf( "%s\n", city );
}
/* Close the cursor */
SQLCloseCursor( hstmt );
The second method of processing the result-set is to use SQLGetData; the equivalent of the previous example can be written:
SQLExecDirect( hstmt, "select NAME, CITY from HOTEL", SQL_NTS );
while ((retcode = SQLFetch( hstmt )) != SQL_NO_DATA)
{
SQLGetData( hstmt, 1, SQL_C_CHAR, name, sizeof(name), &nameInd );
SQLGetData( hstmt, 2, SQL_C_CHAR, city, sizeof(city), &cityInd );
printf( "%-15s ", name );
if (cityInd == SQL_NULL_DATA)
printf( "*null*\n" );
else
printf( "%s\n", city );
}
These two methods can be combined.
The previous two examples used forward-only cursors, which means that they only support fetching rows serially from the start to the end of the cursor. In modern screen-based application, the user expects to be able to scroll backwards and forwards through the data. While it is possible to cache small result sets in memory on the client, this is not feasible when dealing with large result sets. Scrollable cursors provide the answer.
Scrollable cursors allow you to move forward and back to any row within the result-set. A statement attribute of SQL_SCROLLABLE specifies that the cursor be opened in scroll mode. The function SQLFetchScroll supports fetching the next, prior, first and last rows, as well as absolute and relative positioning:
/* Allocate statement handle */
SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt);
/* Set cursor scrollable */
retcode = SQLSetStmtAttr( hstmt, SQL_ATTR_CURSOR_SCROLLABLE,
(SQLPOINTER)SQL_SCROLLABLE, 0 );
if (retcode == SQL_ERROR) goto error;
SQLExecDirect( hstmt, "select NAME, CITY from HOTEL", SQL_NTS );
SQLBindCol( hstmt, 1, SQL_C_CHAR, name, sizeof(name), &nameInd );
SQLBindCol( hstmt, 2, SQL_C_CHAR, city, sizeof(city), &cityInd );
printf( "Original sort order\n" );
while ((SQLFetchScroll( hstmt, SQL_FETCH_NEXT, 0 )) != SQL_NO_DATA)
printf( "%-15s %s\n", name, city );
printf( "Reverse order\n" );
while ((SQLFetchScroll( hstmt, SQL_FETCH_RELATIVE, -1 ))!=SQL_NO_DATA)
printf( "%-15s %s\n", name, city );
/* Close the cursor */
SQLCloseCursor( hstmt );
Updating Data
Applications can update data by executing the UPDATE, DELETE, and INSERT statements. An alternative method is to position the cursor on a particular row and then use DELETE CURRENT, or UPDATE CURRENT statements. The following example illustrates how this can be done by using two statement handles:
SQLHSTMT hscroll, hupdate;
SQLCHAR name[16];
SQLCHAR city[16];
SQLINTEGER nameInd, cityInd;
. . .
/* Allocate statement handles */
SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hscroll);
SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hupdate);
/* Set scroll cursor attributes */
SQLSetStmtAttr( hscroll, SQL_ATTR_CURSOR_SCROLLABLE,
(SQLPOINTER)SQL_SCROLLABLE, 0 );
SQLSetStmtAttr( hscroll, SQL_ATTR_CONCURRENCY,
(SQLPOINTER)SQL_CONCUR_VALUES, 0 );
/* Name the cursor */
SQLSetCursorName( hscroll, "HTL", SQL_NTS );
SQLExecDirect( hscroll, "select NAME, CITY from HOTEL \
for update of CITY", SQL_NTS );
SQLBindCol( hscroll, 1, SQL_C_CHAR, name, sizeof(name), &nameInd );
SQLBindCol( hscroll, 2, SQL_C_CHAR, city, sizeof(city), &cityInd );
/* Set the update cursor to use optimistic concurrency */
SQLSetStmtAttr( hupdate, SQL_ATTR_CONCURRENCY,
(SQLPOINTER)SQL_CONCUR_VALUES, 0 );
/* Prepare the positioned update statement using scroll cursor name */
SQLPrepare( hupdate, "update HOTEL set CITY = ? \
where current of HTL", SQL_NTS );
/* Bind the city parameter in the update statement */
SQLBindParameter( hupdate, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, 15, 0,
city, sizeof(city), &cityInd );
/* Position within the result-set on the scrolling cursor */
SQLFetchScroll( hscroll, SQL_FETCH_ABSOLUTE, 3 );
SQLSetPos( hscroll, 1, SQL_POSITION, SQL_LOCK_NO_CHANGE );
/* Update city using update statement handle */
if (strcmp( city, "New York" ) == 0)
strcpy( city, "Uppsala" );
else
strcpy( city, "New York" );
cityInd = SQL_NTS;
SQLExecute( hupdate );
Benefits
ODBC enables applications to access a Mimer SQL database from a wide variety of platforms.
Mimer SQL implements ODBC as one of its native interfaces. Using ODBC with Mimer SQL is therefore as effective as any other API.
