HP NonStop Linux ODBC/MX Client Driver for SQL/MX Release 3.2.1 HP Part Number: 691122-003 Published: March 2013 Edition: J06.15 and subsequent J-series RVUs; H06.
© Copyright 2013 Hewlett-Packard Development Company, L.P. Legal Notice Confidential computer software. Valid license from HP required for possession, use or copying. Consistent with FAR 12.211 and 12.212, Commercial Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are licensed to the U.S. Government under vendor’s standard commercial license. The information contained herein is subject to change without notice.
Contents About this document......................................................................................5 Intended audience....................................................................................................................5 Document organization.............................................................................................................5 New and changed information in this edition...............................................................................
SQL Datetime Retrieval property...............................................................................................33 5 Error messages.........................................................................................34 A Sample ODBC application .......................................................................38 Compiling and linking the sample application............................................................................38 Example for compiling a threaded application.......
About this document This manual describes how to install, configure and use the Linux ODBC/MX Client Driver. This product allows Linux applications developed for the Open Database Connectivity (ODBC) application programming interface to access data from an SQL/MX database on a NonStop system.
Bold Text Bold text in an example indicates user input typed at the terminal. For example: ENTER RUN CODE ?123 CODE RECEIVED: 123.00 The user must press the Return key after typing the input. [ ] Brackets Brackets enclose optional syntax items. For example: DATETIME [start-field TO] end-field A group of items enclosed in brackets is a list from which you can select one item or none.
DAY (datetime-expression) @script-file Quotation marks around a symbol such as a bracket or brace indicate the symbol is a required character that you must type as shown. For example: "{" module-name [, module-name]... "}" Item Spacing Spaces shown between items are required unless one of the items is a punctuation symbol such as a parenthesis or a comma. For example: DAY (datetime-expression) DAY(datetime-expression) If there is no space between two items, spaces are not permitted.
• • Reference manuals SQL/MX Reference Manual Describes the syntax of SQL/MX statements, MXCI commands, functions, and other SQL/MX language elements. SQL/MX Messages Manual Describes SQL/MX messages. SQL/MX Glossary Defines SQL/MX terminology. Connectivity manuals SQL/MX Connectivity Service Manual Describes how to install and manage SQL/MX Connectivity Service (MXCS), which enables ODBC and other connectivity APIs to use NonStop SQL/MX.
• SQL/MX Queuing and Publish/Subscribe Services Describes how NonStop SQL/MX integrates transactional queuing and publish/subscribe services into its database infrastructure. SQL/MX Guide to Stored Procedures in Java Describes how to use stored procedures that are written in Java within NonStop SQL/MX. Online help Reference Help Overview and reference entries from the SQL/MX Reference Manual. Messages Help Individual messages grouped by source from the SQL/MX Messages Manual.
1 Overview The Linux ODBC/MX client driver (driver) implements the ODBC 3.5 set of APIs. The Linux ODBC applications (applications) send the requests to access data from an SQL/MX database to this driver.
driver forwards the request to MXCS, which authenticates the connection parameters. After successful authentication, MXCS assigns an ODBC server to the application. • If the data source is configured, but not started, then MXCS returns an error. • If the data source is not configured on the server, then MXCS forwards the connect request to the default data source, TDM_Default_DataSource.
For more information about data sources, see the HP NonStop SQL/MX Connectivity Service Manual for SQL/MX Release 3.x . Client data source configuration The client data sources are configured on the Linux workstation. When the driver uses the SQL/MX driver manager, the client data sources are configured in MXODSN file. When the driver uses unixODBC driver manager, the client data sources are configured in odbc.iniand odbcinst.ini files.
Object naming and mapping The driver can access SQL/MX database objects and SQL/MP objects that have an alias. ANSI name type Only ANSI name types are supported for SQL/MX database objects. You can configure the default catalog and schema names to be used to qualify table names in the queries. The name format is: catalog.schema.object-name ANSI names have a maximum of 128 characters for each part of the name (not including the dots).
Table 2 Unsupported ODBC APIs Category Unsupported APIs Installer – Data Sources • SQLManageDataSources • SQLRemoveDefaultDataSource • SQLConfigDataSource • SQLCreateDataSource • SQLValidDSN • SQLRemoveDSNFromIni • SQLWriteDSNToIni Installer – Drivers • SQLConfigDriver • SQLRemoveDriver • SQLInstallDriver • SQLInstallDriverEx • SQLValidDSN • SQLGetInstalledDrivers Installer – Driver Manager • SQLRemoveDriverManager • SQLInstallDriverManager Installer – Installer Errors • SQLInstallerError • SQLPost
2 Installing the driver The hardware and software requirements for the driver are described in the softdoc file delivered with the product, either through the product CD or the Scout website for NonStop servers through HP NonStop eServices portal (https://onepoint.nonstop.compaq.com/). Read the softdoc file before installing the product.
• /usr/lib/libhpsecClient.so • /etc/hpodbc/MXODSN The checksum of the shared libraries must match the checksum in the md5sum file.
3 Configuring data sources This chapter describes the SQL/MX driver manager configuration. For details about unixODBC driver manager configuration, see the unixODBC website: http://www.unixodbc.org.
Table 3 MXODSN file format and attributes File section Attributes Description [ODBC] TraceStart TraceStart can have two values, 0 or 1. If set to 0, tracing is off. If set to 1, tracing is on. By default, tracing is off. TraceFlags TraceFlags indicates the trace level. It can have the following values: • ERROR to trace failed SQL calls and communication problems. • WARNING to trace the warnings. • CONFIG to trace configuration calls. • INFO to trace details about calls made.
Table 3 MXODSN file format and attributes (continued) File section Attributes Description MSDN_DEFAULT. If not specified, the default is SYSTEM_DEFAULT. For more information, see “SQL Interval Behaviour property” (page 31). [DataSourceName] SQLDatetimeRetrieval SQL Datetime Retrieval specifies how the SQL/MP Datetime data type is retrieved from the ODBC/MX driver. SQLDatetimeRetrieval has two values, SYSTEM_DEFAULT and MSDN_DEFAULT. If not specified, the default is SYSTEM_DEFAULT.
[ODBC Data Sources] Sample_DS = NonStop ODBC/MX 3.x DS1 = NonStop ODBC/MX 3.x DS2 = NonStop ODBC/MX 3.x DataSourceName = Driver [Sample_DS] Description = Sample Data Source Catalog = CAT Schema = SCH DataLang = 0 FetchBufferSize = SYSTEM_DEFAULT Server = TCP:xxx.xxx.xxx.xxx:xxxx SQL_ATTR_CONNECTION_TIMEOUT = SYSTEM_DEFAULT SQL_LOGIN_TIMEOUT = SYSTEM_DEFAULT SQL_QUERY_TIMEOUT= NO_TIMEOUT [DS1] Description = Sample Data Source 1 Catalog = DS1CAT Schema = DS1SCH Server = TCP:xxx.xxx.xxx.
NOTE: If tracing is enabled, the global trace flag is set and from then onwards, all the traceable ODBC data is logged into the trace file. To enable tracing for the driver, complete the following steps: 1. Stop the application. 2. Set the TraceStart attribute to 1 in the MXODSN file, and save. 3. Start the application. To disable tracing, complete the following steps: 1. Stop the application. 2. Set the TraceStart attribute to 0 in the MXODSN file, and save. 3. Start the application.
4 Driver compatibility and considerations The driver is compatible with the following: • ODBC 3.5 • SQL-92 features supported by SQL/MX For more information about SQL/MX supported scalar and string functions, see the SQL/MX Reference Manual for Release 3.x .
Table 4 Driver considerations (continued) Item Consideration identifies a row in the table information for a specified table name in the SQL/MX catalog. SQLStatistics API This API does not support pattern value arguments for table names, and returns index information for a specified table name in the SQL/MX catalog. SQLTables API Returns all catalogs defined in the SQL/MX metadata. When CatalogName is SQL_ALL_CATALOGS SQLTables API Returns all the schemas defined in the SQL/MX metadata.
Table 5 ODBC Data Types (continued) ODBC data type SQL/MX data Type SQL/MP data Type Supported by the driver? TINYINT UNSIGNED Not supported Not supported No BIGINT LARGEINT LARGEINT Yes BINARY(n) Not supported Not supported No VARBINARY(n) Not supported Not supported No LONG VARBINARY Not supported Not supported No DATE DATE DATE Yes TIME(p) TIME TIME Yes TIMESTAMP TIMESTAMP TIMESTAMP Yes INTERVAL MONTH(p) INTERVAL MONTH(p) Yes INTERVAL YEAR(p) INTERVAL YEAR(p) Yes
Table 6 Behavior of unsigned data types ODBC version Behavior 2.0 UNSIGNED SMALLINT is promoted to SIGNED INT and UNSIGNED INT is promoted to BIGINT. 3.x, when server data source is configured with SQL_ATTR_MSACCESS_VERSION flag All unsigned data types are promoted to the next signed type. Partial DATE or TIME values To use partial DATE or TIME values as parameters, provide these values through the ODBC/MX data type returned. To fetch partial DATE or TIME values, see the mappings in Table 7 (page 25).
Table 7 SQL/MP to ODBC/MX Date/Time Mappings (continued) SQL/MP data type ODBC/MX data type DATETIME MINUTE TO FRACTION SQL_TIMESTAMP DATETIME SECOND SQL_TIME DATETIME SECOND TO FRACTION SQL_TIMESTAMP DATETIME VALUE RETRIEVAL SQL DATETIME RETRIEVAL Microsoft escape clauses ODBC/MX accepts Microsoft escape clauses and translates them into equivalent SQL/MX clauses. For SQL/MX equivalents, see Table 8 (page 26).
Table 9 Transaction and cursor behavior AUTOCOMMIT SQL/MX behavior Application action ON When any open statement reaches Not applicable end of data or end of cursor, SQL/MX closes all other open statements. OFF Not applicable The application must explicitly rollback or commit the transaction. All open statements are closed at that time.
Table 10 Sample values inserted from an application and expected values in microsecond (continued) Fraction value in the application Column type Expected value in the column of SQL/MX table 54 timestamp(3) or time(3) 0.000 4 timestamp(3) or time(3) 0.000 987654 timestamp(2) or time(2) 0.98 87654 timestamp(2) or time(2) 0.08 7654 timestamp(2) or time(2) 0.00 654 timestamp(2) or time(2) 0.00 54 timestamp(2) or time(2) 0.00 4 timestamp(2) or time(2) 0.
Table 11 Sample values inserted from an application and expected values in nanoseconds (continued) Fraction value in the application Column type Expected value in the column of SQL/MX table 4321 timestamp(4) or time(4) 0.0000 987654321 timestamp(3) or time(3) 0.987 87654321 timestamp(3) or time(3) 0.087 7654321 timestamp(3) or time(3) 0.007 654321 timestamp(3) or time(3) 0.000 54321 timestamp(3) or time(3) 0.000 4321 timestamp(3) or time(3) 0.
Table 12 Values retrieved by the application for SYSTEM_DEFAULT setting (continued) Value in the column of the SQL/MX Table Column type Fraction value retrieved by the application 0.9876 timestamp(4) or time(4) 9876 0.0876 timestamp(4) or time(4) 876 0.0076 timestamp(4) or time(4) 76 0.0007 timestamp(4) or time(4) 7 0.0000 timestamp(4) or time(4) 0 0.987 timestamp(3) or time(3) 987 0.087 timestamp(3) or time(3) 87 0.008 timestamp(3) or time(3) 8 0.
Table 13 Sample values retrieved by the application (continued) Value in the column of the SQL/MX Table Column type Fraction value retrieved by the application 0.987 timestamp(3) or time(3) 987000000 0.087 timestamp(3) or time(3) 87000000 0.008 timestamp(3) or time(3) 8000000 0.000 timestamp(3) or time(3) 0 0.98 timestamp(2) or time(2) 980000000 0.09 timestamp(2) or time(2) 90000000 0.00 timestamp(2) or time(2) 0 0.9 timestamp(1) or time(1) 900000000 0.
If not specified, the default is SYSTEM_DEFAULT. Set the SQL Interval Behaviour property using the MXODSN property. • The following example describes when the value of SQL Interval Behaviour is set to SYSTEM_DEFAULT, the behavior of the driver is same as in earlier releases of SQL/MX.
SQL Datetime Retrieval property The SQL Datetime Retrieval specifies how the SQL/MP Datetime data type is retrieved from the ODBC/MX driver. The SQL Datetime Retrieval has two values: • SYSTEM_DEFAULT — Year is set to 0001. • MSDN_DEFAULT — Year is set to 1900. If not specified, the default is SYSTEM_DEFAULT. This property is applicable only when SQL/MP Datetime datatype is bound to SQL/MX datatypes such as DATE or TIMESTAMP. Set the SQL Datetime Retrieval property using the MXODSN property.
5 Error messages Table 17 (page 34) lists the driver error codes and error messages. Table 17 Error codes and error messages 34 Error codes Error messages 01000 General Warning. 01000 General Warning. Connected to the default data source. 01002 Disconnect error. Transaction rolled back. 01004 Data truncated. 01006 Privilege not revoked. 01033 TRANSPORT LAYER ERROR. 01S02 Option value changed. 07001 Wrong number of parameters. 07003 Dynamic SQL error.
Table 17 Error codes and error messages (continued) Error codes Error messages 21001 Cardinality violation; insert value list does not match column list. 21002 Cardinality violation; insertion value list does not match column list. 21S01 Cardinality violation; parameter list does not match column list. 21S02 String data right truncation. 22001 Numeric value out of range. 22003 Error in assignment. 22005 Precision or scale out of range. 22008 Datetime field overflow.
Table 17 Error codes and error messages (continued) 36 Error codes Error messages S1000 The stored procedure required to complete this operation could not be found on the server (they were supplied with the ODBC/MX setup disk for the SQL Server driver). Contact your service provider. S1000 Unknown token received from SQL Server S1000 Unable to load communication module. Driver has not been correctly installed. S1000 Communication module is not valid. Driver has not been correctly installed.
Table 17 Error codes and error messages (continued) Error codes Error messages S1106 Fetch type out of range. S1107 Row value out of range. S1108 Concurrency option out of range. S1109 Invalid cursor position; no keyset defined. S1C00 Driver not capable. S1LD0 No long data values pending. S1T00 Timeout expired. 6001 INVALID DLL HANDLE. 6002 CANNOT LOAD PROCADDRESS. 6003 WRONG WINSOCK VERSION. 6004 WRONG SIGNATURE. 6005 WRONG VERSION. 6006 ERROR FROM SERVER.
A Sample ODBC application Compiling and linking the sample application Use the g++ compiler to compile the application with the header files that are shipped along with the Linux ODBC/MX Client Driver: odbc]$ g++ -o < output object name > -I < location of the header files shipped with the driver > \ < input file for compilation > -l mxodbc Example for compiling a threaded application The following is a sample compilation command for a threaded application: odbc]$ /usr/bin/g++ -m32 -I
// @@@ END COPYRIGHT @@@ ***************************************************************/ /*************************************************************** @@@@ ODBC TEST APPLICATION @@@ ================================================================ This application utilizes :: - Multiple connections - Gives information about DBMS NAME - ODBC API's - Uses insertion through parameter marker, - Setting the transaction isolation level as serializable and fetches the data.
Err->szSqlState); printf("%s \n",szBuf); } free(Err->szErrorMsg); free(Err->szSqlState); free(Err); free(szBuf); return SQL_SUCCESS; } int main(int argc, char *argv[]) { RETCODE st = 0; SQLHENV henv = (SQLHENV) NULL; SQLHDBC hdbc = (SQLHDBC) NULL; SQLHDBC hdbc1 = (SQLHDBC) NULL; SQLHDBC hdbc2 = (SQLHDBC) NULL; SQLHSTMT hstmt = (SQLHSTMT) NULL; SQLHSTMT hstmt1 = (SQLHSTMT) NULL; CHAR CreateTable[100] = {'\0'}; CHAR InsertTable[100] = {'\0'}; CHAR SelectTable[100] = {'\0'}; int value; SQLINTEGER ValInd; SQLIN
odbc_Error(SQL_NULL_HENV,SQL_NULL_HDBC,SQL_NULL_HSTMT); } st = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0); if (st != SQL_SUCCESS) { printf("Error in SQLSetEnvAttr :: %d\n", st); odbc_Error(henv,SQL_NULL_HDBC,SQL_NULL_HSTMT); } st = SQLAllocConnect(henv, &hdbc); if (hdbc == NULL) { printf("Error in allocating connection Handle 1!\n"); odbc_Error(henv,SQL_NULL_HDBC,SQL_NULL_HSTMT); } st = SQLAllocConnect(henv, &hdbc1); if (hdbc1 == NULL) { printf("Error in allocating connection Handle
st = SQLGetInfo(hdbc,SQL_DBMS_NAME, infoValueBuf, sizeof(infoValueBuf), &StringLengthPtr); infoValuePtr = infoValueBuf; if (st != SQL_SUCCESS) { printf("Error in SQLGetInfo\n"); odbc_Error(henv,hdbc,hstmt); } else printf("\n\tDBMS NAME : %s\n\n", infoValuePtr); printf("\n\tUsing Connection 1\n\n"); st = SQLAllocHandle(SQL_HANDLE_STMT, (SQLHDBC)hdbc, &hstmt); if (hstmt == NULL) { printf("Error in allocating Statement Handle\n"); odbc_Error(henv,hdbc,SQL_NULL_HSTMT); } st = SQLExecDirect(hstmt,(SQLCHAR *) Cre
odbc_Error(henv,hdbc,hstmt); } while(TRUE) { if ((st = SQLFetch(hstmt)) == SQL_NO_DATA_FOUND) break; if ( (st != SQL_SUCCESS) && (st != SQL_SUCCESS_WITH_INFO) ) { printf("Error in SQLFetch!!! Returned Status: %d\n", st); break; } if (ValInd == SQL_NULL_DATA) { printf("No Data fetched!\n"); break; } else { printf("c1 :: %d\n", value); } } // ***************** Done with connection 1 ******************** printf("\n\tUsing Connection 2\n\n"); st = SQLAllocHandle(SQL_HANDLE_STMT, (SQLHDBC)hdbc1, &hstmt1); if (hs
odbc_Error(henv,hdbc1,hstmt1); } while(TRUE) { if ((st = SQLFetch(hstmt1)) == SQL_NO_DATA_FOUND) break; if ( (st != SQL_SUCCESS) && (st != SQL_SUCCESS_WITH_INFO) ) { printf("Error in SQLFetch!!! Returned Status: %d\n", st); break; } if (ValInd == SQL_NULL_DATA) { printf("No Data Fetched!\n"); break; } else { printf("c1 :: %d\n", value); } } st = SQLExecDirect(hstmt1,(SQLCHAR*)"drop table GGTest",SQL_NTS); if (st != SQL_SUCCESS) { printf("Error in drop table : GGTest\n"); odbc_Error(henv,hdbc1,hstmt1); } els
Glossary The glossary terms are described in Table 18 Glossary Term Definition Driver manager The ODBC component that manages access to Database Management System (DBMS) drivers for ODBC applications. The driver manager loads and unloads drivers and passes the calls for ODBC functions to the correct driver. The Linux ODBC/MX Client Driver supports both the Nonstop driver manager and the unixODBC driver manager.
Index LONGVARCHAR data type, 23 A autocommit, 27 M MXCS, 10 B BIGINT data type, 24 BINARY data type, 24 BIT data type, 23 N C REAL data type, 23 Catalog and schema consideration, 22 CHAR data type, 23 Client data source configuring, 12 Client-side components, 11 Connections, 12 cursor, 26 D Data sources managing, 11 data sources configuring, 17 managing, 20 data types unsigned, 24 data types supported, 24 DATE data type, 24 DATETIME, 26 DECIMAL data type, 23 default data source TDM_Default_DataSourc
