HP NonStop Linux ODBC/MX Client Driver for SQL/MX Release 3.2 HP Part Number: 691122-001 Published: August 2012 Edition: J06.14 and subsequent J-series RVUs; H06.
© Copyright 2012 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...............................................................................
5 Error messages.........................................................................................32 A Sample ODBC application .......................................................................36 Compiling and linking the sample application............................................................................36 Example for compiling a threaded application.......................................................................36 Testing the sample.............................................
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.
?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. The items in the list can be arranged either vertically, with aligned brackets on each side of the list, or horizontally, enclosed in a pair of brackets and separated by vertical lines.
"{" 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. In this example, no spaces are permitted between the period and any other items: myfile.
• 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 Connectivity Service Administrative Command Reference Describes the SQL/MX Administrative Command Library (MACL) available with the SQL/MX conversational interface (MXCI).
• 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. Glossary Help Terms and definitions from the SQL/MX Glossary. NSM/web Help Context-sensitive help topics that describe how to use the NSM/web management tool. Visual Query Planner Help Context-sensitive help topics that describe how to use the Visual Query Planner graphical user interface.
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 1 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 2 MXODSN file format and attributes (continued) File section Attributes Description • INFO to trace details about calls made. • DEBUG to trace the internal details about calls made for debugging an issue. TraceFile attribute indicates the trace file name. The trace file is located in the Linux ODBC application directory. For example, if you specify the TraceFile as Tracefile = trfetch, the trace file is created as trfetch.6947088_1285815329.
Table 2 MXODSN file format and attributes (continued) File section Attributes Description NO_TIMEOUT, the driver waits until a connection is established with MXCS. SQL_QUERY_TIMEOUT indicates the wait time to close the cursor and return control to the Linux ODBC application. If set to SYSTEM_DEFAULT, the value is 60 seconds. If set to NO_TIMEOUT, the Linux ODBC/MX client driver waits till the query completes. Catalog indicates the catalog name.
Catalog = DS1CAT Schema = DS1SCH Server = TCP:xxx.xxx.xxx.xxx:xxxx SQLIntervalBehaviour = SYSTEM_DEFAULT [DS2] Description = Sample Data Source 2 Catalog = DS2CAT Schema = DS2SCH SQLIntervalBehaviour = SYSTEM_DEFAULT [DataSourceName] Driver = NonStop ODBC/MX Managing data sources To add a new DSN entry in the MXODSN file, include the DSN and its description. If the values of the attributes are not specified in the MXODSN file, the driver uses default values when creating a connection.
NOTE: • To remove the DSN_PATH entry, use the Linux UNSET command. • If the DSN_PATH is not defined, the driver uses the following search order to retrieve the MXODSN file: 1. The current working directory. 2. The default location, /etc/hpodbc.
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 3 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 4 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 5 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 6 (page 25).
Table 6 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 Microsoft escape clauses ODBC/MX accepts Microsoft escape clauses and translates them into equivalent SQL/MX clauses. For SQL/MX equivalents, see Table 7 (page 26).
Table 8 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 9 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 10 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 11 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 12 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.
5 Error messages Table 17 (page 32) lists the driver error codes and error messages. Table 17 Error codes and error messages 32 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) 34 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 15 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 Change bars, 5 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_De
