Mimer SQL and the ODBC API

ODBC enables your Mimer 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.

This chapter is not intended to be a complete guide to the functionality provided by ODBC. It is written to introduce you to accessing Mimer SQL through ODBC.

For a more detailed discussion of Mimer SQL, ODBC and other database API’s, please see https://developer.mimer.com/doc/database-apis/.

The Examples in this Chapter

The ODBC function calls in the examples use ODBC 3.5 syntax, although they are not generally dependent on a Windows platform. It should be possible to use the examples as a basis for translation into other languages.

In the 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.

The Mimer ODBC Driver

Mimer SQL-specific versions of ODBC functions are implemented in the Mimer SQL native ODBC driver. The driver passes SQL statements to the Mimer SQL server and returns the results of the statements to the application.

The Mimer SQL setup process automatically installs the ODBC driver when the Mimer SQL client is installed on a Windows, Linux or macOS platform.

Mimer SQL programs that are written using the ODBC API, communicate with Mimer SQL through C function calls.

Required Files

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, sqltypes.h, sqlucode.h and sql.h are automatically included.

ODBC applications are linked with the ODBC 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.

ODBC applications can also be linked directly to the native Mimer ODBC driver library, bypassing the ODBC Driver Manager.

To compile ODBC applications using Mimer specific functions and attributes, include the file mimodbc.h, which is supplied with the distribution. See Mimer Specific Descriptor Fields for details.

Unicode and ANSI Interfaces

The Mimer ODBC driver is Unicode based. This allows applications to use both the ANSI and Unicode interfaces when using Mimer SQL. Unicode based applications can both store and retrieve Unicode data through SQL statements and/or Unicode host variables.

ANSI applications can use Unicode host variables, but are restricted to character (8-bit) characters when passing data with SQL statements. Please note that database objects still have to be named with the same character set as before.

The Unicode SQL data types in Mimer SQL are called NATIONAL CHARACTER (or NCHAR), NATIONAL CHARACTER VARYING (NCHAR VARYING), and NATIONAL CHARACTER LARGE OBJECT (NCLOB).

You can find more information about these data types in the Mimer SQL Reference Manual. The ODBC documentation contains specifics about the SQL_WCHAR database type and the SQL_C_WCHAR and SQL_C_WLONGVARCHAR host language types.

External Character Set Support

The system follows the current locale setting on the machine to determine what characters are stored/retrieved when an application passes single-byte character strings to ODBC.

When character data is stored in Mimer SQL it can be stored in CHAR or VARCHAR columns or in NCHAR or NVARCHAR columns. Data in CHAR and VARCHAR columns use the Latin-1 character representation (also called ISO 8859-1). This character set can only be used to store 256 different characters. For the exact characters that can be stored see Mimer SQL Reference Manual, Character Sets. To store any other characters the data type NCHAR or NVARCHAR must be used. These column types can store any character.

If a locale is used by the application that has characters that are not included in Latin-1, it means that the columns in the database data must use an NCHAR or NVARCHAR column to store the correct characters. Previously, each character in the application was simply stored in a character column. When these characters were retrieved with, for example, DbVisualizer or other Unicode enabled applications, the interpretation of the characters were done differently and the wrong characters were displayed. With locale support the Mimer SQL client understands the representation of the characters in the application and maps them accordingly to its internal representation.

When retrieving data from the database, the translation works the other way. I.e. when retrieving data from a CHAR or NCHAR column to a SQL_C_CHAR variable, the current locale must be able to represent all the characters returned from the database. When this is not possible, a conversion error -10401 is returned. If characters stored in the database have no representation in the chosen locale, a wide character data type must be used by the application instead (SQLWCHAR rather than SQLCHAR).

Win:On Windows the setting used for the external character set is set in the Regional and Language Options in the Control Panel under the tab Advanced. This setting is used automatically by the Mimer ODBC client.

On Windows the environment variable is set to the desired code page, i.e. only numeric values may be specified (for example: 1250: ANSI Central Europe, 1251: ANSI Cyrillic, 1252: Latin1, 1253: ANSI Greek, 1254: ANSI Turkish, and so on.)

VMS:On VMS the system continues to use the Latin-1 character representation regardless of locale settings.

Linux:On other platforms (Linux, macOS, others) the application must call the runtime library routine setlocale to pick the locale to use. For example, the call setlocale(LC_CTYPE, "") sets the default locale as decided by the environment setting. The actual conversions made by the Mimer client are through the library routines mbstowcs (multibyte character set to wide char set) and wcstombs. Please note that if an application does not call setlocale a default 7-bit locale is used. This means that no 8-bit characters can be used without getting a conversion error. For applications where the source is not available it is possible to set an environment variable MIMER_LOCALE that will be used when calling the Mimer client. The value of the environment variable is used as the second argument to setlocale. For details, see the man-page for setlocale.

To use the default locale set MIMER_LOCALE to current.

The fact that the character type is considered a multi-byte character set allows any external character representation to be used. In particular various character sets such as Traditional Chinese Big5 and Japanese Shift-JIS may be used. The character set can also, of course, be a single byte character set as such as the Greek Latin-7 character set (code page 1253 on Windows). On Linux platforms the prevalent representation is UTF-8 that allows any Unicode character to be stored in a character variable.

Mimer Specific Descriptor Fields

Mimer SQL supports large objects of up to 8 terabytes. This poses a problem to ODBC applications since the ODBC API specifies length fields to be 32 bits. An ODBC compliant application is therefore unable to work with any objects larger than 2 gigabytes. The SQL/CLI standard has the same problem, so we won’t get much help there.

To remedy this situation, the Mimer ODBC driver has four vendor specific descriptor attributes. Each attribute mimics the behavior of an existing attribute, the only difference is that the existing attribute is working with a 32-bit integer while ours use 64-bit integers.

C-definitions for these attributes are available in the mimodbc.h header file. Include this file along with the regular ODBC include files (sql.h, sqlext.h) to gain access to these features.

Note that, although these attributes are most useful when working with large objects, they may be used for any type of data.

The Mimer specific descriptor attributes:

SQL_DESC_DISPLAY_SIZE_64 [IRDs]

This read-only SQLBIGINT record field contains the maximum number of characters required to display the data from the column. The value in this field is not the same as the descriptor field SQL_DESC_LENGTH because the SQL_DESC_LENGTH field is undefined for all numeric types.

SQL_DESC_LENGTH_64 [Implementation descriptors]

This SQLUBIGINT record field is either the maximum or actual character length of a character string or a binary data type. It is the maximum character length for a fixed-length data type, or the actual character length for a variable-length data type. Its value always excludes the null-termination character that ends the character string. For values whose type is SQL_TYPE_DATE, SQL_TYPE_TIME, SQL_TYPE_TIMESTAMP, or one of the SQL interval data types, this field has the length in characters of the character string representation of the datetime or interval value. Note that this field is a count of characters, not a count of bytes.

The value in this field may be different from the value for “length” as defined in ODBC 2.x. For more information, see Microsoft ODBC 3.0 Programmer’s Reference, Appendix D, “Data Types.”

SQL_DESC_OCTET_LENGTH_64 [Implementation descriptors]

This SQLBIGINT record field contains the length, in bytes, of a character string or binary data type. For fixed-length character or binary types, this is the actual length in bytes. For variable-length character or binary types, this is the maximum length in bytes. This value always excludes space for the null-termination character.

SQL_DESC_OCTET_LENGTH_PTR_64 [Application descriptors]

This SQLBIGINT* record field points to a variable that will contain the total length in bytes of a dynamic argument (for parameter descriptors) or of a bound column value (for row descriptors).

For an APD, this value is ignored for all arguments except character string and binary; if this field points to SQL_NTS, the dynamic argument must be null-terminated. To indicate that a bound parameter will be a data-at-execution parameter, an application sets this field in the appropriate record of the APD to a variable that, at execute time, will contain the value SQL_DATA_AT_EXEC or the result of the SQL_LEN_DATA_AT_EXEC macro. If there is more than one such field, SQL_DESC_DATA_PTR can be set to a value uniquely identifying the parameter to help the application determine which parameter is being requested.

If the OCTET_LENGTH_PTR_64 field of an ARD is a null pointer, the driver does not return length information for the column. When setting the SQL_DESC_OCTET_LENGTH_PTR_64 field to anything other than a null pointer, this field overrides the SQL_DESC_OCTET_LENGTH_PTR field. When both SQL_DESC_OCTET_LENGTH_PTR and SQL_DESC_OCTET_LENGTH_PTR_64 are set to null pointers the driver assumes that character strings and binary values are null-terminated. (Binary values should not be null-terminated, but should be given a length to avoid truncation.)

If the call to SQLFetch or SQLFetchScroll that fills in the buffer pointed to by this field did not return SQL_SUCCESS or SQL_SUCCESS_WITH_INFO, the buffer contents are undefined. This field is a deferred field: It is not used at the time it is set, but is used at a later time by the driver to determine or indicate the octet length of the data.

Operating Systems

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.

Declarations

A C program that calls the ODBC API typically requires the following declarations:

#if defined(WIN32)

#include <windows.h>

#endif

#include <stdlib.h>

#include <stdio.h>

#include <string.h>

#include "sqlext.h"

#include "mimodbc.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.

Initializing the ODBC Environment

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

If an ODBC data source has been defined, ODBC applications can connect to Mimer SQL by using the data source name. Alternatively SQLDriverConnect can be used.

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, for example:

SQLRETURN retcode;

. . .

 

/* Set connection timeout - 10 seconds */

SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)10, 0);

 

/* Connect - DSN, User ID, Password */

retcode = SQLConnect(hdbc,

                     (SQLCHAR*) "EXAMPLEDB", SQL_NTS,

                     (SQLCHAR*) "MIMER_ADM", SQL_NTS,

                     (SQLCHAR*) "admin",

                     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=EXAMPLEDB;UID=MIMER_ADM;PWD=admin;"

 

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 Windows example, the Driver Manager displays a window containing a combo box listing all the Mimer SQL database names and prompts for the user name and password:

SQLCHAR     OutConnectString[256];

SQLSMALLINT StringLength;

SQLHWND     hwnd;

 

hwnd = GetDesktopWindow();

. . .

 

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:The macro SQL_SUCCEEDED replaces the test against SQL_SUCCESS or SQL_SUCCESS_WITH_INFO.

On other platforms, such as Linux, the driver does not implement a GUI popup box. Instead the user will be prompted for the required login attributes. In this case, referring to the previous example, the hwnd variable is set to null:

hwnd = NULL;

Controlling Interaction with the User

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 );      

}

Connecting Using a File Data Source

Another way of making a connection is to create a file data source. The file contains keyword-value pairs to make the connection. On Windows, this file has a .dsn extension.

Although it is possible to include the password, this would make the system insecure and therefore is not recommended:

[ODBC]

DSN=EXAMPLEDB

UID=MIMER_ADM

 

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=example.dsn;",

                            SQL_NTS,

                            (SQLCHAR*)OutConnectString,

                            sizeof(OutConnectString),

                            &StringLength,

                            SQL_DRIVER_COMPLETE );

if (SQL_SUCCEEDED(retcode))

{

    /* User connected */

 

Mimer Specific Keywords to SQLDriverConnect

To allow an application to connect without specifying a data source in the connection string, the following driver-specific keywords have been added for the Mimer ODBC Driver:

•PROTOCOL

•NODE

•SERVICE

•INTERFACE

The PROTOCOL keyword is mandatory for this option to be used. The regular keyword DATABASE must also be specified. Other driver-specific keywords should be used depending on the specified protocol. When PROTOCOL is specified, no data source lookup is done in the registry (Windows) or MIMER_SQLHOSTS (Linux and VMS).

Supported protocols are LOCAL (shared memory), TCP, NAMEDPIPES (only for Windows), RAPI (only for Windows), and DECNET (only for VMS).

The protocol TCP requires keyword NODE specifying the network node name. If keyword SERVICE is not specified, 1360 is used as default.

Win:The protocol NAMEDPIPES requires keyword NODE. If keyword SERVICE is not specified, the database name is used as default.

VMS:The protocol DECNET requires keyword NODE.

Note:SQLDriverConnect has a parameter that enables prompting for missing information. When PROTOCOL is specified, this is not possible.

Examples of connection strings that can be used:

"DRIVER={Mimer};DATABASE=cartoons;UID=mickey;PWD=mouse;PROTOCOL=local"

 

"DRIVER={Mimer};DATABASE=musix;UID=discux;PWD=records;PROTOCOL=local"

 

"DRIVER={Mimer};DATABASE=strips;UID=winnie;PWD=thepooh;PROTOCOL=tcp; NODE=milne;SERVICE=1360"

 

"DRIVER={Mimer};DATABASE=pip;UID=mickey;PWD=mouse;PROTOCOL=NamedPipes; NODE=winpix;SERVICE=pip"

 

"DRIVER={Mimer};DATABASE=disney;UID=donald;PWD=duck;PROTOCOL=decnet; NODE=pictvms;INTERFACE=BG"

Determining Driver and Data Source Capabilities

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" );´</pre>

Connecting on Linux and similar platforms

On Linux, it is possible to link an ODBC application directly to the Mimer ODBC library, libmimodbc.so. But, usually an ODBC Driver Manager is used, mainly to be able to handle several ODBC Data Sources. In that case the Driver Manager library is linked to the application and the Mimer ODBC library is pointed out as the Driver in the ODBC Data Source definition.

When a connection attempt is made using ODBC, a DSN (Data Source Name) is specified via one of the connection methods describe above. The ODBC Driver Manager looks up the given ODBC Data Source in an odbc.ini file. There can be a system wide odbc.ini file located in a known location for the platform, usually in /etc. Alternatively, the user can have a personal .odbc.ini located in the home directory. Or, the ODBCINI environment variable can be set to point out the data source definition file to be used.

A possible match between the given DSN and an entry in the odbc.ini file gives the connection information needed to load the relevant ODBC Driver dynamically and to proceed with the database access.

The following is an example of an odbc.ini file, describing two DSN specifications, with their names within straight brackets:

[dsn_dbcust]

Driver=/opt/MimerSQL-11.0.1A/lib/libmimodbc.so

Database=customers

Host=kixie

Port=1360

 

[dsn_dbext]

Driver=/opt/MimerSQL-11.0.1A/lib/libmimodbc.so

Database=external

 

In the first case above the information defined is enough to do a direct access to the database named ‘customers’ on the network node ‘kixie’, using the port number 1360. When reaching the database the user will have to provide a database user name (ident name) and a password.

In the second definition there is not enough information to do a direct access. Instead the given database is looked for in the Mimer SQL database registry file called /etc/sqlhosts, and if found there, that information will be used to proceed with the connection. In this case, if the DSN name is the same as the database name, the Database attribute is optional.

The following are valid DSN attributes in the odbc.ini file when read by the Mimer ODBC Driver:

Disconnecting

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

•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.

Retrieving Warning and Error Messages

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').

See Return Codes for details.

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.

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.

Transaction Management Mode

There are two modes for managing transactions within ODBC, Autocommit and Manual-commit. SQLSetConnectAttr is used to switch between the modes.

Autocommit Mode

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 to manual commit mode.

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.

Manual-commit Mode

When in manual commit mode, 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.

Completing Transactions

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.

Example 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 mimer_store.currencies \

                   SET exchange_rate = exchange_rate * 1.05 \

                   WHERE code = 'USD'", SQL_NTS );

 

SQLExecDirect( hstmt,

               "UPDATE mimer_store.currencies \

                   SET exchange_rate = exchange_rate * 1.08 \

                   WHERE code = 'GBP'", 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 (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 );

}

Setting the Transaction Isolation Level

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.

Executing 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.

For example:

SQLINTEGER rowcount;

. . .

 

/* Allocate statement handle */

SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt );

SQLExecDirect( hstmt,

               "UPDATE mimer_store.currencies \

                   SET exchange_rate = exchange_rate * 1.05 \

                   WHERE code = 'USD'", SQL_NTS );

 

SQLRowCount( hstmt, &rowcount );

printf( "%d rows have been updated\n", rowcount );

Repeating – Prepared Execution

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 reduce the performance difference between direct and prepared execution by maintaining and re-using compiled statements on the server.

Prepared Statement Example

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:

SQLFLOAT   increase;

SQLCHAR    code[4];

SQLINTEGER increaseInd, codeInd;

. . .

 

SQLPrepare( hstmt,

            "UPDATE mimer_store.currencies \

                SET exchange_rate = exchange_rate * ? \

                WHERE code = ?", SQL_NTS );

 

SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_DOUBLE,

                  SQL_REAL, 7, 0,

                  &increase, 0, &increaseInd );

SQLBindParameter( hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,

                  SQL_CHAR, 4, 0,

                  code, sizeof(code), &codeInd );

 

/* Set parameter values and length/indicator */

increase = 1.05;

strcpy( code, "USD" );

codeInd = SQL_NTS;

 

SQLExecute( hstmt );

SQLRowCount( hstmt, &rowcount );

printf( "%d rows have been updated\n", rowcount );

Stored Procedure Example

Similarly, it is possible to prepare an SQL statement that calls a stored procedure:

SQLINTEGER  order_id, item_id;

SQLSMALLINT quantity;

SQLINTEGER  orderInd = 0, itemInd = 0, quantityInd = 0;

. . .

 

SQLPrepare( hstmt,

            "{CALL mimer_store.order_item( ?, ?, ? )}",

            SQL_NTS );

 

SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG,

                  SQL_INTEGER, 0, 0,

                  &order_id, SQL_IS_INTEGER, &orderInd );

SQLBindParameter( hstmt, 2, SQL_PARAM_INPUT, SQL_C_SLONG,

                  SQL_INTEGER, 0, 0,

                  &item_id, SQL_IS_INTEGER, &itemInd );

SQLBindParameter( hstmt, 3, SQL_PARAM_INPUT, SQL_C_SSHORT,

                  SQL_INTEGER, 0, 0,

                  &quantity, SQL_IS_SMALLINT, &quantityInd );

 

/* Set parameter values */

order_id = 700001;

item_id = 60158;

quantity = 2;

 

SQLExecute( hstmt );

Parameters in Procedure Calls

Parameters in procedure calls can be input, input/output, or output. A more complicated example illustrates how to handle an output parameter:

SQLCHAR     country[3];

SQL_INTERVAL_STRUCT interval;

SQLINTEGER  countryInd, intervalInd;

SQLSMALLINT numparams;

. . .

 

SQLPrepare( hstmt,

            "{CALL mimer_store.age_of_adult( ?, ? )}",

            SQL_NTS );

 

SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,

                  SQL_CHAR, 3, 0,

                  country, sizeof(country), &countryInd );

SQLBindParameter( hstmt, 2, SQL_PARAM_OUTPUT, SQL_C_INTERVAL_YEAR,

                  SQL_INTERVAL_YEAR, 0, 0,

                  &interval, sizeof(SQL_INTERVAL_STRUCT),

                  &intervalInd );

 

SQLNumParams( hstmt, &numparams );

printf( "statement contains %d parameters\n", numparams );

 

/* Set input parameter value and length */

strcpy( country, "US" );

countryInd = SQL_NTS;

 

SQLExecute( hstmt );

printf( "%d years\n", interval.intval.year_month.year );

 

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. The second method of processing the result set is to use SQLGetData.

Using SQLBindCOL

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    code[4];

SQLCHAR    currency[33];

SQLINTEGER codeInd, currencyInd;

. . .

/* Allocate statement handle */

SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt );

 

SQLExecDirect( hstmt,

               "SELECT code, currency \

                   FROM mimer_store.currencies",

               SQL_NTS );

 

SQLBindCol( hstmt, 1, SQL_C_CHAR,

            code, sizeof(code), &codeInd );

SQLBindCol( hstmt, 2, SQL_C_CHAR,

            currency, sizeof(currency), &currencyInd );

 

while ((retcode = SQLFetch( hstmt )) != SQL_NO_DATA)

{

   printf( "%s %s\n", code, currency );

}

 

/* Close the cursor */

SQLCloseCursor( hstmt );

Using SQLGetData

The second method of processing the result set is to use SQLGetData; the equivalent of the previous example can be written:

SQLExecDirect( hstmt,

               "SELECT code, currency \

                   FROM mimer_store.currencies",

               SQL_NTS );

 

while ((retcode = SQLFetch( hstmt )) != SQL_NO_DATA)

{

   SQLGetData( hstmt, 1, SQL_C_CHAR,

               code, sizeof(code), &codeInd );

   SQLGetData( hstmt, 2, SQL_C_CHAR,

               currency, sizeof(currency), &currencyInd );

 

   printf( "%s %s\n", code, currency );

Combining Result Set Processing Methods

SQLBindCol and SQLGetData 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

Scrollable cursors allow you to move forward and backward to any row within the result set. A statement attribute of SQL_SCROLLABLE specifies that the cursor will be opened in scroll mode.

The function SQLFetchScroll supports fetching the next, prior, first and last rows, as well as absolute and relative positioning.

For example:

/* 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 code, currency \

               FROM mimer_store.currencies \

               WHERE code LIKE 'A%'",

               SQL_NTS);

 

SQLBindCol(hstmt, 1, SQL_C_CHAR,

           code, sizeof(code), &codeInd);

SQLBindCol(hstmt, 2, SQL_C_CHAR,

           currency, sizeof(currency), &currencyInd);

 

printf("Original sort order\n");

while ((SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0)) != SQL_NO_DATA)

   printf("%s %s\n", code, currency);

 

printf("\nReverse order\n");

while ((SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, -1)) != SQL_NO_DATA)

   printf("%s %s\n", code, currency);

 

/* 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   cscroll, cupdate;

SQLCHAR    code[4];

SQLCHAR    currency[33];

SQLINTEGER codeInd, currencyInd;

. . .

 

/* Allocate statement handles */

SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &cscroll);

SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &cupdate);

 

/* Set scroll cursor attributes */

SQLSetStmtAttr(cscroll, SQL_ATTR_CURSOR_SCROLLABLE,

               (SQLPOINTER)SQL_SCROLLABLE, 0);

SQLSetStmtAttr(cscroll, SQL_ATTR_CONCURRENCY,

               (SQLPOINTER)SQL_CONCUR_VALUES, 0);

 

/* Name the cursor */

SQLSetCursorName(cscroll, "CRN", SQL_NTS);

 

SQLExecDirect(cscroll,

              "SELECT code, currency \

               FROM mimer_store.currencies \

               FOR UPDATE OF currency",

               SQL_NTS);

 

SQLBindCol(cscroll, 1, SQL_C_CHAR,

           code, sizeof(code), &codeInd);

SQLBindCol(cscroll, 2, SQL_C_CHAR,

           currency, sizeof(currency), &currencyInd);

 

/* Set the update cursor to use optimistic concurrency */

SQLSetStmtAttr(cupdate, SQL_ATTR_CONCURRENCY,

               (SQLPOINTER)SQL_CONCUR_VALUES, 0);

 

/* Prepare the positioned update statement using scroll cursor name */

SQLPrepare(cupdate,

           "UPDATE mimer_store.currencies \

               SET currency = ? \

            WHERE CURRENT OF crn",

            SQL_NTS);

 

/* Bind the code parameter in the update statement */

SQLBindParameter(cupdate, 1, SQL_PARAM_INPUT, SQL_C_CHAR,

                 SQL_CHAR, 33, 0,

                 currency, sizeof(currency), &currencyInd);

 

/* Position within the result set on the scrolling cursor */

SQLFetchScroll(cscroll, SQL_FETCH_ABSOLUTE, 3);

SQLSetPos(cscroll, 1, SQL_POSITION, SQL_LOCK_NO_CHANGE);

 

/* Update currency name using update statement handle */

if (strncmp(currency, "Leke", 4) == 0)

   strcpy(currency, "Albanian Leke");

else

   strcpy(currency, "Leke");

currencyInd = SQL_NTS;

 

SQLExecute(cupdate);

Native SQL Escape Clauses

Using native SQL escape clauses with short-form syntax is supported by the Mimer SQL Experience server, not only from the ODBC driver, but from all Mimer database API’s, including scripted or interactive execution from BSQL.

Long-form escape clauses are supported, but need to be handled by the ODBC driver before execution.

Short- and long-form syntax examples:

{ d'2001-01-01' }

--(* vendor(Microsoft), product(ODBC) d'2001-01-01' *)--

 

The function SQLNativeSQL transforms a long-form escape clause to a short-form escape clause, making it directly executable.

SQLNativeSql(hdbc,

    "UPDATE mimer_store.items SET release_date =

    --(* vendor(Microsoft), product(ODBC) d '2001-01-01' *)--

   WHERE product_id = 100",

   SQL_NTS, (keep)szSqlStr, 1200, pcbSqlStr);

SQLExecDirect(hstmt, szSqlStr, SQL_NTS);

 

The only accepted vendor is Microsoft, and the only accepted product is ODBC. All long-form escape clauses with other vendors or products will be discarded. A syntactically incorrect long-form escape clause will not be transformed.

Escaped functions

The first column lists functions supported in escaped function calls.

The second column tells if a function is supported in an unescaped form as well.

The third column describes the regular Mimer SQL equivalence, when different from the escaped function call form.