Home

HP NonStop ODBC/MX Client Drivers User Guide

Contents

1. 46 SQL Datetime Retrieval Dr Petty Mi tete 48 SET EN 49 CODEC EBD iet eo ded ed iun Compiling and linking the sample 53 Example for compiling a threaded 53 Testing ERR HR 54 Sample application 54 55 NR NR RE RD I AI Tee ae NOL Ew noe eee we 61 TERRE 62 4 Contents About this document This manual describes how to install configure and use the ODBC MX client drivers These drivers allow HP UX Linux32 Linux64 OSS and Windows applications developed for Open Database Connectivity ODBC to access data from an SQL MX database on a NonStop system Intended audience This manual is intended for the following audience e ODBC application programmers who develop applications to access data from an SQL MX database e Administrators installing and configuring the driver on the HP UX Linux OSS and Windows platforms Document organization Chapter Name Description Chapter 1 Overview Provides an overview of the O
2. INTERVAL HOUR p Yes SECOND q SECOND q INTERVAL MINUTE p TO _ INTERVAL MINUTE p TO Yes SECOND q SECOND q The default size allocated for VARCHAR length is 4K 4096 The VARCHAR length can be up to 4059 for key sequenced files or 4070 for entry sequenced files Starting with SQL MX Release 3 0 the precision of the NUMERIC data type is extended to 128 digits If the precision is greater than 18 for signed and greater than 9 for the unsigned numeric data type use SQLBindParameter API to insert the data and the SQLBindCol to select the data with the SQL_C_CHAR data type Unsigned data types The behavior of unsigned data types is determined by the ODBC version and the SQL ATTR MSACCESS VERSION flag setting Table 7 explains the behavior of unsigned data types Unsigned data types 39 Table 7 Behavior of unsigned data types ODBC version 2 0 3 x when server data source is configured with SQL ATTR MSACCESS VERSION Behavior UNSIGNED SMALLINT is promoted to SIGNED INT and UNSIGNED INT is promoted to BIGINT All unsigned data types are promoted to the next signed 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 8 page 40 When you fetch partial DATE or TIME value character strings the valu
3. SOLIntervalBehaviour SQLIntervalBehaviour includes zeroes at the beginning or at end of an interval datatype for micro or nano MXODSN and ODBCDSN file formats 31 32 Table 4 MXODSN and ODBCDSN attributes continued File Section Attributes Description seconds SQLIntervalBehaviour has two values SYSTEM DEFAULT and 5 DEFAULT If not specified the default is SYSTEM DEFAUIT For more information see SQL Interval Behaviour property page 46 SQLDatetimeRetrieval SQLDatetimeRetrieval specifies how the SQL MP Datetime data type is retrieved from the driver SQLDatetimeRetrieval has two values SYSTEM DEFAULT and 5 DEFAULT If not specified the default is SYSTEM DEFAUIT For more information see SQL Datetime Retrieval property page 48 SQL ATTR CONNECTION _ TIMEOUT SOL ATTR CONNECTION TIMEOUT indicates the wait time to close an idle connection If set to SYSTEM DEFAULT the value is 60 seconds If set to 1 use the Conn Timeout value set on the MXCS server data source If set to NO TIMEOUT the driver waits until a ODBC server becomes available or MXCS is stopped SQL LOGIN TIMEOUT SQL LOGIN TIMEOUT indicates the wait time to establish a connection with MXCS If set to SYSTEM DEFAULT the value is 60 seconds If set to NO TIMEOUT the driver waits until a connection is established with MXCS SOL QUERY TIMEOUT SQL QUERY TIMEOUT indicates the wait time
4. Protected as an unpublished work The computer program listings specifications and documentation herein are the property of Hewlett Packard Development Company L P or a third party supplier and shall not be reproduced copied disclosed or used in whole or in part for any reason without the prior express written permission of Hewlett Packard Development Company L P END COPYRIGHT RK KKK KK II IIIA ck ck ck k kk ROR RRR KR aa ck ck k k k k k k k k k k KK KKK 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 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk include lt stdio h gt include lt stdlib h gt include lt sqlext h gt include lt string h gt define UnSignCharCast unsigned char define TRUE 1 typedef struct SDWORD pfNativeError 1 SWORD pcbErrorMsg 1 SWORD cbErrorMsgMax UCHAR szErrorMsg UCHAR szSqlState ERR INFO RETCODE odbc Error SQLHENV hEnv SQLHDBC hDbc SQLHSTMT hStmt RETCODE odbc Error SOLHENV hEnv SOLHDBC hDbc SOLHSTMT hStmt char szBuf inte pt chista 3 int prac ERR INFO Err RETCODE st 0 Err ERR INFO malloc sizeof ERR INFO E
5. 5sum file If the library is not present in the default location usr 1ib hpuxe4 ensure that the location of the driver libmxodbc ia64 sl libmxodbc ia64 drvr sl libsecCliente4 sl issetin environment variable SHLIB PATH To set the PATH variable enter the following command export SHLIB PATH directory where the driver is located gt Linux 32 After a successful installation the following files are installed e usr lib libmxodbc so e usr lib libmxodbc so 3 e usr lib libmxodbc so 3 2 1 e usr lib libmxodbc 1 drvr so Verifying the installation 21 usr lib libmxodbc 1 drvr so 3 usr lib libmxodbc 1 drvr so 3 2 1 e usr lib libhpsecClient so etc hpodbc MXODSN The checksum of the shared libraries must match the checksum in the md5sum file Linux 64 After a successful installation the following files are installed e usr lib64 libmxodbc64 so e usr lib64 libmxodbc64 so 3 e usr lib64 libmxodbc64 80 3 2 1 usr 1ib64 libmxodbc64 1 drvr so usr lib64 libmxodbc64 1 drvr so 3 usr lib64 libmxodbc64 1 drvr so 3 2 1 e usr lib64 libhpsecClient64 so e etc hpodbc MXODSN The checksum of the shared libraries must match the checksum in the md5sum file Windows After a successful installation of ANSI driver the following files are installed in Windows System folder tdm oadm0300 d11 tdm odbc0300 dll tdm odbcDrvMsg intl0300 dl1l tdm OdbcTrace0300 dl
6. MSDN_DEFAULT AUTOCOMMIT SERVER DEFAULT 34 Configuring data sources Using connection string without DSN You set AUTOCOMMIT as part of the Connection string AutoCommit ON OFF SERVER DEFAULT Example 2 Depicting AUTOCOMMIT set to ON Driver NonStop ODBCMX 3 2 ServerDSN TDM Default DataSource SERVER TCP lt hostname port gt UID lt userid gt PWD password AutoCommitzON NOTE For Windows driver if AutoCommit property is set to OFF without using SQLSetConnectAttrAPI then the application must execute the commit rollback as a statement to end an active transaction SQLEndTrans API cannot be used in this scenario Tracing Unix You can enable or disable tracing by modifying the TraceStart attribute in MXODSN or ODBCDSN file For more information about tracing options see MXODSN and ODBCDSN attributes NOTE If tracing is enabled the global trace flag is set and from then onwards all the traceable ODBC data is logged in the trace file Perform the following steps to enable tracing l Stop the application 2 Setthe TraceStart attribute to 1 in the MXODSN or ODBCDSN file and save 3 Start the application Perform the following steps to disable tracing l Stop the application 2 TraceStart attribute to O in the MXODSN or ODBCDSN file and save 3 Start the application Windows Perform the following steps to configure tracing for ANSI or Unicode drivers l
7. Seled Start gt Control Panel Administrative Tools Data Sources ODBC Click either the System DSN or the User DSN tab to get the list of data sources Select the data source name and click Configure Select the Tracing tab Choose NonStop ODBCMX API or Network Layer or both Enter a log file name Click Apply and then click OK Perform the following steps to start tracing NOOR WN 1 SelecttStart gt Control Panel gt Administrative Tools gt Data Sources ODBC 2 Select the Tracing tab 3 Select the Custom Trace DLL Use the Select DLL button to locate the correct file Tracing 35 4 9 6 If you select the ODBC standard trace DLL odbctrac d11 from the Windows System folder only the driver manager is traced additional trace options that you select are ignored If you select ODBC MX trace DLL tdm odbctrace0300 d11 or tdm odbctracew0300 d11 from the Windows System folder all trace options that you select for the client data source are considered Use the Browse button to locate the correct log file Click Start Tracing Now Click Apply and then click OK Perform the following steps to stop tracing 1 2 3 Select Start gt Control Panel gt Administrative Tools gt Data Sources ODBC Select the Tracing tab Click Stop Tracing Now Click Apply and then click OK 36 Configuring data sources 4 Compatibility and considerations The drivers are compatible with the
8. Vertical Line A vertical line separates alternatives in a horizontal list that is enclosed in brackets or braces For example expression NULL Ellipsis An ellipsis immediately following a pair of brackets or braces indicates that you can repeat the enclosed sequence of syntax items any number of times For example ATTRIBUTE S attribute attribute sql expression An ellipsis immediately following a single syntax item indicates that you can repeat that syntax item any number of times For example expression n Punctuation Parentheses commas semicolons and other symbols not previously described must be typed as shown For example 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 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 sh Line Spacing If the syntax of a command is too long to fit on a single line each continuation line is indented three spaces and is separated from the preceding line by a blank line This spa
9. 1 2 Goto the 641 ODBC 321 directory 3 To uninstall the driver enter the following command runme sh 4 Enter 2 and press Enter Enter yes if you want to continue with uninstall operation 5 Enter yes if you want to remove the configuration files 6 Upon successful uninstallation the following message is displayed UnInstalled Windows You can uninstall the ANSI or Unicode client interface using one of the following methods Log in to the Linux workstation as a root user Select Start Programs gt Release Remove Release Release be NonStop ODBCMX version NonStop ODBCMX version Unicode Click Yes Run TDMODBC EXE or NSODBC64 EXE for ANSI driver and ODBCW32 EXE or ODBCW64 EXE for Unicode driver and select Uninstall NOTE or data sources You cannot use these data sources until you reinstall the driver The uninstall operation deletes the driver but it does not delete the driver manager Uninstalling the driver 23 3 Configuring data sources This chapter describes the various procedures to configure the SQL MX data sources 1 Configuring a client data source 1 Configuring the drivers for IPv6 1 Configuring a secure ODBC connection using NonStop 551 1 MXODSN and ODBCDSN formats Managing data sources 1 Managing transactions page 34 Mi Tracing Configuring a client data source Unix The MXODSN fi
10. DEBUG to trace the internal details about calls made for debugging an issue TraceFile TraceFile attribute indicates the trace file name The trace file is located in the ODBC application directory For example if you specify the TraceFile as Tracefile trfetch the trace file is created as trfetch 6947088 1285815329 linux systems ODBC Data Sources DSN description DataSourceName List of data sources in the format DSN description DataSourceName attribute indicates the driver data source name one section for each DSN Description Description attribute describes the data source DataLang DataLang attribute indicates the character set for SQL commands The default is 0 FetchBufferSize FetchBufferSize indicates the buffer size to fetch rows The range is between O and 256 Kb If set to SYSTEM_DEFAULT the buffer size is 8 Kb SYSTEM_DEFAULT For a single row the value is O Server Server attribute indicates the IP address or domain name where MXCS is running The format is TCP numberi number2 SOLFractionValue SQLFractionValue indicates the fraction value for SQL C TIMESTAMP to be in nanoseconds or microseconds If set to 9 the fraction value is in nanoseconds If set to 501 DEFAULT not specified or set to a value other than 9 the fraction value is in microseconds For more information see Timestamp values with fraction page 42
11. DialTone and The Open Group are trademarks of The Open Group in the U S and other countries Open Software Foundation OSF logo OSF 1 OSF Motif and Motif are trademarks of the Open Software Foundation Inc OSF MAKES NO WARRANTY OF ANY KIND WITH REGARD TO THE OSF MATERIAL PROVIDED HEREIN INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE OSF shall not be liable for errors contained herein or for incidental consequential damages in connection with the furnishing performance or use of this material 1990 1991 1992 1993 Open Software Foundation Inc The documentation and the OSF software to which it relates are derived in from materials supplied by the following 1987 1988 1989 Carnegie Mellon University 1989 1990 1991 Digital Equipment Corporation 1985 1988 1989 1990 Encore Computer Corporation 1988 Free Software Foundation Inc 1987 1988 1989 1990 1991 Hewlett Packard Company O 1985 1987 1988 1989 1990 1991 1992 International Business Machines Corporation O 1988 1989 Massachusetts Institute of Technology 1988 1989 1990 Mentat Inc 1988 Microsoft Corporation 1987 1988 1989 1990 1991 1992 SecureWare Inc 1990 1991 Siemens Nixdorf Informationssysteme AG 1986 1989 1996 1997 Sun Microsystems Inc 1989 1990 1991 Transarc Corporation OSF software and documentation are based in part
12. ODBC with the SQL QUERY TIMEOUT option to specify the wait time for query execution The 501 QUERY TIMEOUT option protects MXCS and the application from long running queries If a query exceeds the specified time before the data source returns the result the driver returns HYTOO Timeout expired to the application For an ODBC 2 0 application when the query is not running the driver implements the SQLCancel as SQLFreeStmt with SQL_CLOSE option If the query is running MXCS tries to stop the ODBC server If the attempt is successful the application receives an error and must reconnect For an ODBC 3 x application with a query or queries running the behavior is the same as for a ODBC 2 0 application For an ODBC 3 x application with no queries running the driver does not implement the SQLCancel The application is responsible for closing the cursor SQLPrimaryKeys This API does not support pattern value arguments for table names and returns keys information for a specified table name in the SQL MX catalog Considerations 37 Table 5 Considerations continued Item SQLSpecialColumns Consideration This does not support pattern value arguments for table names and returns an optimal set of columns that the SQLSpecialColumns uniquely identifies a row in the table information for a specified table name in the SQL MX catalog SQLStatistics
13. SHOWLOG ODBSLOG The log file must contain a message similar to the following sample S ODBS3 05Marl2 09 54 19 89 20 ODBC MX server proxy started on target host 127 0 0 1 target port 23 source port 8402 00 53 05 12 09 54 19 93 50 Performed action CombinedAction CreateSocket ListenSocketAction successfully The host NonStop SSL Server process is now running and available for client access For more information about installing the NonStop SSL Server process for ODBC MX see the HP NonStop SSL Reference Manual Installing and configuring the Remote Proxy Client You must obtain the following information from your administrator before installing the Remote Proxy Client The IP address host name and port number of the SSL server running on the NonStop system The port number of the MXCS Association Server that you want to connect NOTE The MXCS Association Server port number must not be in use by any other program or service on your client workstation For information about obtaining the port number see the SQL MX Connectivity Service Administrative Command Reference Perform the following steps to install and configure the Remote Proxy client on Windows workstation Configuring a secure ODBC connection using NonStop SSL 27 6 Download the SYSTEM ZNSSSL PROXYEXE file in binary format and rename it to PROXY EXE Run PROXY EXE to start the RemoteProxy installation program and follow the install
14. components page 12 e Driver managers page 12 e Data sources page 13 e Tracing page 14 e Object naming and mapping page 14 e Unsupported ODBC APIs page 14 e Unsupported ODBC data types page 15 Considerations and limitations page 15 Architecture The applications interact with the drivers to access an SQL MX database The Unix drivers are thread safe and you can write multi threaded C C applications which use the POSIX thread library Figure 1 page 11 illustrates the architecture which comprise client and server components 10 Overview Figure 1 Architecture HP Linux HP UX ODBC MX Driver Application ODBC MX Client OSS ODBC MX Client Driver Linux or HP UX System Association Server Application Microsoft ODBC DM sources and B HP ANSI Unicode ODBC MX Driver Nonstop System A TDM Default Data Source ODBC MX Client B Data Source B Windows System DB SQL MX Database The application calls the SQLConnect ODBC API to open a connection to a data source which represents NonStop SQL on the host The NonStop SQL MX Connectivity Service MXCS provides support for remote connections to the SQL MX database For example support for remote connections from ODBC and JDBC app
15. etc hpodbe directory Upon successful installation the following message is displayed Installed Windows Perform the following steps to install the ANSI or Unicode drivers 1 2 3 en d Close all other applications Log in to the Windows workstation as an administrator Create a temporary installation folder to download the installation file For example ODBCTEMP Use FTP to download the following files from the SYSTEM ZMXODBC installation subvolume e ANSI TDMODBC or the Unicode ODBCW32 file for 32 bit driver install e ANSI NSODBC64 or the Unicode 64 file for 64 bit driver install Rename the files with EXE extension For example TDMODBC EXE Double click TDMODBC EXE NSODBC64 EXE ODBCW32 EXE or ODBCW64 EXE to launch the Installation Wizard Click the Next button on the Welcome Screen Accept the License Agreement and click Next You can retain the default or change the Destination Folder on the following screen by dicking the Browse button Click Next Installation procedures 19 Choose Destination Location Select folder where setup will install files Setup will install NonStop TM 3 2 in the following folder To install to this folder click Next To install to a different folder click Browse and select another folder Destination Folder C Hewlett Packard NonStop ODBCMX 32 InstallShield 10 You can retain the default or provide a different Pro
16. following e ODBC 3 5 509192 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 These subsequent sections list the compatibility of the drivers for the following Considerations 37 e ODBC data types page 38 e Unsigned data types page DATE or TIME values 39 page 40 e Microsoft escape clauses 41 e Stored Procedures page 41 e Transaction and cursor behavior 41 e Timestamp values with fraction page 42 Considerations Table 5 lists the considerations Table 5 Considerations ODBC MX client application Catalog and schema Consideration An ODBC client application must be installed on the client workstation An application can set the catalog and schema information in the MXODSN file on Linux or ODBCDSN file on OSS If the application does not set the catalog and schema for the client data source and no default catalog and schema are defined in MXCS for the server data source then MXCS by default uses NONSTOP SYSTEM NSK and PUBLIC ACCESS SCHEMA NOTE By default catalog and schema names are case insensitive and follow SQL MX rules Case sensitive catalog and schema names can be specified within quotes SQL QUERY TIMEOUT option SQLCancel API An application can call SoLSet Stmt Opt ion
17. for compilation gt 1 mxodbc Linux 64 Use the g compiler to compile the application with the header files that are shipped along with the driver odbc g m64 o lt output object name gt I lt location of the header files gt lt input file for compilation gt 1 mxodbce4 OSS Use the c89 compiler to compile the application with the header files that are shipped along with the driver gt C89 o output object name I usr include odbc input file for compilation gt By default the header files are located in the usr include odbc directory Example for compiling a threaded application HP UX The following is a sample compilation command odbc usr bin g m64 I lt path for include files gt ThreadODBCTestAppl c o ThreadODBCTestAppl 1 ia64 Linux 32 The following is a sample compilation command for a 32 bit application odbc usr bin g m32 path for include files gt ThreadODBCTestAppl c o ThreadODBCTestAppl 1 mxodbc Linux 64 The following is a sample compilation command for a 64 bit application odbc usr bin g m64 I path for include files gt ThreadODBCTestAppl c o ThreadODBCTestAppl 1 mxodbc6 4 OSS The following is a sample compilation command for a 32 bit application 055 gt c89 g Winspect Wextensions Ww Woptimize 0 4 Wsystype oss Wrefalign 8 Wfieldalign auto Wallow cplusplus comments Wcplusplus Wversion3 1 Wtarg
18. on the Fourth Berkeley Software Distribution under license from The Regents of the University of California OSF acknowledges the following individuals and institutions for their role in its development Kenneth C R C Arnold Gregory S Couch Conrad C Huang Ed James Symmetric Computer Systems Robert Elz 1980 1981 1982 1983 1985 1986 1987 1988 1989 Regents of the University of California Contents Pur BOWS ASCII acababa nbn pid rii debitae a 5 Intended eeiUio NNI esse 5 Document GIOSPZCUTOIL dee ae UL HER qupd 5 New and changed information in this aate cia mtu 5 Notation Conventions a 5 General syntax tus ERN IEEE 3 Related Documentation sisiran e Ee E agde nbn ui oA 7 NonStop SOLD MX customer 7 P blishing e E 9 HP encourages your COMMENIS 2 052 C 9 10 gt 10 dlient eid 12 Server COMPONEN 12 DRIVE nle ne l 12 Data TT i 13 Client data source PIN DUAE o a pere 13 Serve
19. or time 4 0 Timestamp values with fraction 45 Table 14 Sample values retrieved by the application continued Value in the column of the SQL MX Fraction value retrieved by the Table Column type 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 l or time 1 900000000 0 0 timestamp l or time 1 0 NOTE The applications can insert the time and timestamp values into time and timestamp columns of SQL MX table using the FRACTION STRUCT structure struct FRACTION STRUCT SQLUINTEGER day SQLUINTEGER hour SOLUINTEGER minute SQLUINTEGER second SOLUINTEGER fraction The structure fields are Table 15 includes a sample set of FRACTION STRUCT field values when SQL Fraction Precision Value is or 9 Table 15 FRACTION STRUCT field values with 501 Fraction Precision Value 6 SQL Interval Behaviour property The SQL Interval Behaviour property includes zeroes at the beginning or end of an interval or 9 Table precision fraction 6 5 4 3 2 1 123456 123456 12345 1234 123 12 1 12345 012345 12345 1234 123 12 1 1234 001234 01234 1234 123 12 1 123 000123 00123 0123 123 12 1
20. printf Error in Connection 3 n odbc Error henv hdbc SQL NULL HSTMT else printf Connection 3 Successful WMn st SQLGetInfo hdbc SQL DBMS NAME infoValueBuf sizeof infoValueBuf amp StringLengthPtr infoValuePtr infoValueBuf if st 501 SUCCESS printf Error in SQLGetInfo n henv hdbc hstmt else printf n tDBMS NAME s n n infoValuePtr printf n tUsing Connection 1 n n st SOLAllocHandle SQL HANDLE STMT SOLHDBC hdbc amp hstmt if hstmt NULL printf Error in allocating Statement Handle n odbc Error henv hdbc SQL NULL HSTMT st SQLExecDirect hstmt SQLCHAR CreateTable strlen CreateTable if st SQL SUCCESS printf Error in Create Table 1 odbc Error henv hdbc hstmt else printf Create table GGTest Successful n st SQLExecDirect hstmt SQLCHAR InsertTable strlen InsertTable if st 501 SUCCESS printf Error in Insert Table 1 GGTest n odbc Error henv hdbc hstmt else printf Insert Successful d n valuel memset InsertTable 0 sizeof InsertTable sprintf InsertTable 5 85 insert into GGTest c1 values n value2 st SQLExecDirect hstmt SOLCHAR InsertTable SOL NTS if st 501 SUCCESS printf Error in Insert Table 2 odbc Error henv hdbc hstmt else printf Insert Successful va
21. the connection string the driver uses the SERVERDSN as the default DSN Configuring a client data source 25 Configuring the drivers for HP UX Linux 32 and Linux 64 You can directly enter the address instead of IPv4 address in the MXODSN or odbc ini when using the unixODBC driver manager configuration files The driver recognizes the address format and connects to the server The following is a sample from the configuration file Server 2620 0 a17 e03e a00 8eff fe08 cd6 5 3033 055 Perform the following steps for the driver to use IPv l Ensure that the TCPIP DEFINE is set to use when you start the application The value IPv is case sensitive 2 Add the TCPIP PROCESS NAME DEFINE e Delete the existing DEFINE using the following command del define TCPIP PROCESS NAME e Add the DEFINE using the following command PARAM TCPIP PROCESS NAME lt TCPSAM process name gt add define TCPIP PROCESS NAME class MAP file lt system name gt lt TCPSAM process name gt For more information see the TCP IPv Configuration and Management Manual 3 Start the OSH or OSS process If the process is started directly the environmental variable PROCESS NAME must point to process Windows Perform the following steps for the ANSI or Unicode driver to use l Select Start gt Programs gt Release Release can be NonStop ODBCMX version NonSto
22. to close the cursor and return control to the ODBC application If set to SYSTEM DEFAUIT the value is 60 seconds If set to NO TIMEOUT the driver waits till the query completes Catalog Catalog indicates the catalog name If not specified the driver uses the MXCS defined catalog name Schema Schema indicates the schema name If not specified the driver uses the MXCS defined schema name AUTOCOMMIT The values for AutoCommit can be SERVER_DEFAULT ON and OFF For more information see Managing transactions page 34 DataSourceName Driver driver List of drivers This value must match the driver name defined by DataSourceName attribute in the ODBC Data Sources section The following sample MxoDsN file defines connections to three client data sources TDM Default DataSource DS1 and 052 In this example the attributes Catalog and Schema for data source DS1 are defined as DS1CAT and DS1SCH Configuring data sources ODBC IraceFlags 6 TraceStart 0 TraceFile trlog ODBC Data Sources TDM Default DataSource NonStop ODBC MX 3 x DS1 NonStop ODBC MX 3 x DS2 NonStop ODBC MX 3 x DataSourceName Driver TDM Default DataSource Description Default Data Source Catalog CAT Schema SCH DataLang 0 FetchBufferSize SYSTEM DEFAULT Server TCP XXX XXX XXX XXX XXXX SOL ATTR CONNECTION TIMEOUT SYSTEM DEFAULT SO
23. val SDWORD pcbValue 0 0 SOLCHAR infoValueBuf 100 o SQLCHAR infoValuePtr 0 SQLSMALLINT StringLengthPtr NULL char Password 40 0 char UserID 40 0 char serverName TDM short valuel 10 short value2 20 bool defaultDS false printf n t PURPOSE DRIVER n n if gt 1 if stremp argv 1 Default DataSource TO TEST ODBC API S ON ODBC 1 printf USAGE odbcdemo DataSourceName printf Eg odbcdemo DEMO n printf Note Default Data Source will be used in case no DS specified n return 0 serverName argv 1 printf User Name scanf s UserID printf Password 56 Sample ODBC application scanf s Password strcpy CreateTable create table GGTest c1 int sprintf InsertTable 5 5 insert into GGTest cl values nt valuel Ny strcpy SelectTable select from GGTest printf n tUsing Data Source s n n serverName if strncmp serverName TDM Default DataSource strlen serverName defaultDS true st SQLAllocEnv amp henv if henv NULL printf Error in allocating Env Handle n SQL NULL HENV SQL NULL HDBC SQL NULL HSTMT st SQLSetEnvAttr henv 501 ATTR ODBC VERSION void SQL OV ODBC3 0 if st 501 SUCCESS printf Error SQLSetEnvAttr d n st odbc Error henv SQL NULL HDBC SQL NULL HST
24. 005 Dynamic SQL error Prepared statement is not a cursor specification 07006 Restricted data type attribute violation 07008 Dynamic SQL error Wrong number of bound columns 07009 The value specified for the argument ColumnNumber was greater than the number of columns in the result set 08001 No more MXCS servers available to connect 08001 MXCS services not yet available 08001 Data source not yet available or not found 08001 No more ports available to start MXCS servers 08001 Retry attempts to connect to the data source failed May be MXCS server not able to register to the MXCS service process 08001 No more MXCS servers available to connect 08002 Connection in use 08003 Connection not open 08004 Data source rejected establishment of connection since the MXCS server is connected to a different client now 08004 Data source rejected establishment of connection for implementation defined reasons 08005 Communication failure 08006 Transaction rolled back 08007 Connection failure during transaction 08501 Communication link failure 08502 TRANSPORT LAYER ERROR 50 Table 17 Error codes and error messages continued Error codes Error messages 21001 violation insert value list does not match column ist 21002 Cardinality violation inserti
25. 12 000012 00012 0012 012 12 1 1 000001 00001 0001 001 01 1 datatype for micro or nano seconds The 501 Interval Behaviour has two values SYSTEM DEFAULT Trailing zeroes are added to the fraction For example 0 1234 changes fo 0 123400 e MSDN DEFAULT Leading zeroes are added to the fraction For example 0 1234 changes fo 0 001234 46 Compatibility and considerations If not specified the default is 575 DEFAULT Set the SQL Interval Behaviour property using the MXODSN property e following example describes when the value of SQL Interval Behaviour is set to SYSTEM DEFAULT the behavior of the drivers is same as in earlier releases of SQL MX In this example zeroes are suffixed in the following way For example create table interval frac ID2S interval day to second ID2S0 interval day to second 0 ID2S1 interval day to second 1 ID2S2 interval day to second 2 ID2S3 interval day to second 3 10254 interval day to second 4 For the above when you insert values for all columns as 11 22 33 44 1 and 02 11 21 12 1234 binding to valid datatypes the values are inserted as follows Table 16 lt 01 Interval Behaviour property set to SYSTEM DEFAULT ID2S 10250 0251 0252 0253 0254 1 22 33 44 22 33 441 1 22 33 44 100000 22 33 44 10 22 33 44 100 22 33 44 1000 22 33 44 22 33 44 1 22 33 44 123400 22 33 44 12 22 33 44 123 22 33 4412
26. 34 e following example describes when the SQL Interval Behaviour property is set to MSDN DEFAULT this example zeroes are prefixed in the following way For example create table interval frac ID2S interval day to second ID2S0 interval day to second 0 ID2S1 interval day to second 1 ID2S2 interval day to second 2 I 3 I 4 D2S3 interval day to second D2S4 interval day to second For the above DDL when you insert values for all columns as 11 22 33 44 1 and 02 11 21 12 1234 binding to valid datatypes the following values are inserted Table 17 SQL Interval Behaviour property set to MSDN DEFAULT ID2S 10250 0251 0252 0253 0254 22 33 44 22 33 44 1 22 33 44 000001 22 33 44 01 22 33 44 001 22 33 44 0001 1 22 33 44 22 33 441 1 22 33 44 001234 22 33 44 12 22 33 44 123 22 33 441234 NOTE In the above examples the SQL Fraction Precision Value is set to SYSTEM DEFAULT microseconds SQL Interval Behaviour property 47 SQL Datetime Retrieval property 48 The SQL Datetime Retrieval specifies how the SQL MP Datetime data type is retrieved from the drivers The SQL Datetime Retrieval has two values e SYSTEM DEFAULT Year is set to 0001 e MSDN DEFAULT Year is set to 1900 If not specified the default is 575 DEFAULT This property is applicable only when SQL MP Datetime datatype is bound to SQL MX d
27. AM 501 C SLONG SQL INTEGER 0 0 amp val 0 amp pcbValue val 50 if st SOL SUCCESS printf Error in SQLBindParameter d n st odbc Error henv hdbc1 hstmt1 st SQLExecute hstmt1 if st SOL SUCCESS printf Error in SQLExecute d n st odbc Error henv hdbc1 hstmt1 else Testing the sample 59 60 printf Insert Successful d n val strcpy SelectTable select from GGTest st SQLExecDirect hstmtl SQLCHAR SelectTable strlen SelectTable if st 501 SUCCESS printf Error in select table GGTest n odbc Error henv hdbc1 hstmt1 st SQLBindCol hstmt1 1 SOL C SLONG amp value 0 amp ValInd if st SOL SUCCESS printf Error in SQLBindCol Wn odbc Error henv hdbc1 hstmt1 while TRUE if st SQLFetch hstmt1 SOL NO DATA FOUND break if st SOL SUCCESS amp amp st SOL SUCCESS WITH INFO printf Error in SQLFetch Returned Status d n st break if ValInd SOL NULL DATA printf No Data Fetched Mn 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 henv hdbc1 hstmt1 else printf nTable GGTEST dropped n st SQLEndTran SQL HANDLE DBC SQLHANDLE hdbc S
28. CHAR instead of SQL C WCHAR to match with the SQL data type of the column or parameter Consider the following byte length for IN or OUT WCHAR parameter buffer length Number of characters 2 for double byte Unicode characters Number of characters 4 for supplementary Unicode characters When the trace is on the Unicode characters in the SQL statements may not get displayed properly in the log file 2 Installing the drivers The hardware and software requirements for the drivers are described in the softdoc or README file delivered with the products either through the product CD or through the Scout website for NonStop servers in the HP NonStop eServices portal https onepoint nonstop compag com This chapter describes the following e Installation requirements e Installation procedures e Verifying the installation Installation requirements HP UX A standard HP UXia 64 installation is required for the driver If you want the applications to use the unixODBC driver manager then download and install unixODBC driver manager release 2 2 14 or 2 3 1 and later For installing unixODBC see the unixODBC website http www unixODBC org Linux 32 The Linux driver is LSB 4 1 compliant and is tested on RHEL 5 1 6 0 and ubuntu 11 4 A standard Linux installation is required for the driver If you want the applications to use the unixODBC driver manager then download and install unixODBC driver manager release 2 2 14
29. DBC Mx client drivers Chapter 2 Installing the drivers Describes procedures to install the ODBC MX client drivers Chapter 3 Configuring data sources Describes steps to configure the client data sources Chapter 4 Compatibility and considerations Lists the compatibility for the drivers Chapter 5 Error messages Lists the error codes and messages Appendix A Sample ODBC application Provides a sample ODBC application New and changed information in this edition Changes to the 734873 002 manual e Added information about AutoCommit in Managing transactions page 34 e Added information about Linux 64 bit driver across the manual This manual combines the following manuals e System Services ODBC MX Client Driver SQL MX Release 3 2 1 ODBC MX Client Driver for SQL MX Release 3 2 1 Driver for Windows Manual for SQL MX Release 3 2 1 This manual also contains added information on configuring secure ODBC connection for NonStop SSL HP UX driver and Unicode support in Windows ODBC MX client driver Notation conventions General syntax notation This list summarizes the notation conventions for syntax presentation in this manual UPPERCASE LETTERS Uppercase letters indicate keywords and reserved words Type these items exactly as shown Items not enclosed in brackets are required For example SELECT Intended audience 5 Italic Letters Italic letters regardless of font indicate variable item
30. Documents related information 7 DOUBLE PRECISION data type 38 Driver considerations 38 E error codes 52 F FLOAT data type 38 INTEGER data type 38 INTERVAL MONTH data type 39 INTERVAL YEAR data type 39 IPV6 26 L Linux ODBC Client Driver overview 10 LONG VARBINARY data type 39 LONGVARCHAR data type 38 N NUMERIC data type 38 62 Index R REAL data type 38 Server data source configuring 13 Server side components 12 SMALLINT data type 38 SQL Datetime Retrieval 48 SQL Interval Behaviour 46 SQL QUERY TIMEOUT SQLSetStmtOption 37 SQLBindParameter consideration 38 SQLCancel consideration 37 SQLPrimaryKeys consideration 37 SQLPutData consideration 38 SQLSpecialColumns consideration 38 SQLStatistics consideration 38 SQlLTables SQL_ALL_CATALOGS 38 SQL_ALL_SCHEMAS 38 SQL ALL TABLE TYPES 38 T TIME data type 39 TIMESTAMP data type 39 TINYINT data type 38 tracing data sources 35 U unixODBC driver manager 12 Unsupported ODBC APIs 14 Unsupported ODBC data types 15 V VARBINARY data type 39 VARCHAR data type 38
31. ETIME 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 Table 9 lists the SQL MX equivalents Table 9 SQL MX equivalents for Microsoft escape clauses Microsoft escape clause date literal SQL MX equivalent DATE date literal t time literal TIME time literal ts timestamp literal TIMESTAMP timestamp literal oj join expression join expression INTERVAL sign interval string interval qualifier INTERVAL sign interval string interval qualifier n scalar function scalar function call Supported procedure name escape escape character Not supported 11 Not supported procedure name ODBC syntax does not include nested joins while SQL MX does ODBC MX extends the Microsoft syntax for an outer join Functions are controlled by SQLGet Info Only SQL MX native functions are supported Stored Procedures The drivers support NonStop SQL MX stored procedures with the following exceptions Pointers to cursors and return codes are not supported For more information about stored procedures see the SQL MX Guide to Stored Procedures in Java Transaction and cursor behavior The transaction and cursor behavior is determined
32. HP NonStop ODBC MX Client Drivers User Guide for SQL MX Release 3 2 1 HP Part Number 734873 002 Published November 2013 Edition 10616 and subsequent J series RVUs 06 27 and subsequent H series RVUs Copyright 2013 Hewlett Packard Development Company L P 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 The only warranties for HP products and services are set forth in the express warranty statements accompanying such products and services Nothing herein should be construed as constituting an additional warranty HP shall not be liable for technical or editorial errors or omissions contained herein Export of the information contained in this publication may require authorization from the U S Department of Commerce Microsoft Windows and Windows NT are U S registered trademarks of Microsoft Corporation Intel Pentium and Celeron are trademarks or registered trademarks of Intel Corporation or its subsidiaries in the United States and other countries Java is a registered trademark of Oracle and or its affiliates Motif OSF 1 UNIX X Open and the X device are registered trademarks and IT
33. L LOGIN TIMEOUT SYSTEM DEFAULT SOL QUERY TIMEOUT NO TIMEOUT DS1 Description Sample Data Source 1 Catalog DS1CAT Schema DS1SCH Server TCP XXX XXX XXX XXX XXXX SQLIntervalBehaviour SYSTEM DEFAULT SOLDatetimeRetrieval SYSTEM DEFAULT DS2 Description Sample Data Source 2 Catalog DS2CAT Schema DS2SCH SQLIntervalBehaviour SYSTEM DEFAULT DataSourceName Driver NonStop ODBC MX Managing data sources Unix To add a new DSN entry in the MXODSN or ODBCDSN file include the DSN and its description If the values of the attributes are not specified in the MKODSN or ODBCDSN file the driver uses the default values when creating a connection Perform the following steps to modify or delete a DSN entry 1l Modify the attributes or remove the DSN entry and save the file 2 Restart the application to reflect the changes Managing data sources 33 Windows To add a new data source or configure an existing data source see Configuring a client data source Perform the following steps to delete a client data source 1 Seled Start gt Control Panel gt Administrative Tools gt Data Sources ODBC 2 Click either the System DSN or the User DSN tab to get the list of data sources 3 Select the data source name that you want to delete and click Remove 4 Click Yes in the confirmation dialog box to remove the data source Managing transactions The AutoCommit property indicates whether SQL MX must aut
34. MT st SQLAllocConnect henv amp hdbc if hdbc NULL printf Error in allocating connection Handle 1 n odbc Error henv SQL NULL HDBC SQL NULL HSTMT st SQLAllocConnect henv amp hdbc1 if 1 NULL printf Error in allocating connection Handle 2 n odbc Error henv SQL NULL HDBC SQL NULL HSTMT st SQLAllocConnect henv amp hdbc2 if hdbc2 NULL printf Error in allocating connection Handle odbc Error henv SQL NULL HDBC SQL NULL HSTMT st SOLConnect hdbc SOLCHAR serverName SQL NTS SQLCHAR UserID SQ L NTS SOLCHAR Password SQL NTS if st SQL SUCCESS printf Error in Connection 1 n henv hdbc SQL NULL HSTMT exit 1 else printf Connection 1 Successful n st SQLSetConnectAttr hdbc SOL ATTR TXN ISOLATION SQLPOINTER 8 0 if st SOL SUCCESS printf Error in SQLSetConnectAttr n odbc Error henv hdbc hstmt st SQLConnect hdbc1 SOLCHAR serverName SQL NTS SQLCHAR UserID SOL NTS SOLCHAR Password SQL NTS if st SQL SUCCESS printf Error in Connection 2 n henv hdbc SQL NULL HSTMT Testing the sample 57 else printf Connection 2 Successful n st SOLConnect hdbc2 SOLCHAR serverName SQL NTS SQLCHAR UserID SOL NTS SOLCHAR Password SQL NTS if st 501 SUCCESS
35. ONNECTION TIMEOUT lt value in seconds SOL LOGIN TIMEOUT value in seconds gt SQL QUERY TIMEOUT lt value in seconds For more information about the attributes see MXODSN and ODBCDSN file formats Table 2 explains the driver connection attributes specific to Windows driver Table 2 Connection attributes Attribute Description DSN Name of the data source as returned by SQLDataSources API or the data sources dialog box of SQLDriverConnect API FILEDSN Name of a file The driver builds the connection string for the data source with the information stored in this file SAVEFILE The file name of a dsn file The attribute values of keywords used for the successful connection are saved in this file DRIVER Description of the driver as returned by the SQLDrivers API For example Rdb or SQL Server ERRORMSGLANG Client error message language This is equal to SYSTEM_DEFAULT The default is ENGLISH CTRLINFERNCHAR Used for setting Infer charset default is SYSTEM_DEFAULT which is the client local character set TRANSLATIONDLL Translates the data from one character set to other Set the TRANSLATIONDLL in either of the following ways e TRANSLATIONDLL tdm translation dll Click Browse to select a DLL The DLL you specify is used to map the character set for a particular language TRANSLATIONOPTION Options supported by the translation DLL NOTE If DSN attribute is not provided in
36. QL ROLLBACK if hstmt SOLFreeStmt hstmt SQL CLOSE if hstmt1 SOLFreeStmt hstmt1 SQL CLOSE SQLDisconnect hdbc SQLFreeHandle SQL HANDLE DBC hdbc SQLDisconnect hdbc1 SOLFreeHandle SQL HANDLE DBC hdbc1 SQLDisconnect hdbc2 SQLFreeHandle SQL HANDLE DBC hdbc2 SQLFreeHandle SQL HANDLE henv return 0 Sample ODBC application Glossary 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 HP UX and Linux drivers support both the Nonstop driver manager and the unixODBC driver manager Open Database Connectivity ODBC An interface specification for an API that defines a standard set of routines that an ODBC application can use to access data in a database ODBC MX The ODBC components and products to support the ODBC functionality for the SQL MX database 61 Index A AutoCommit 42 B BIGINT data type 39 BINARY data type 39 BIT data type 38 Catalog and schema consideration 37 CHAR data type 38 Client data source configuring 13 D Data sources managing 13 data sources configuring 24 managing 33 data types unsigned 39 data types supported 39 DATE data type 39 DATETIME 41 DECIMAL data type 38 default data source TDM Default DataSource 13
37. QLGetTranslator Installer Config Mode SQLSetConfigMode SQLGetConfigMode Bulk operations SQLBulkOperations SOLSetScrollOption Scroll operations SQLFetchScroll supported only for SQL NEXT attribute Unsupported ODBC data types The drivers do not support the BINARY VARBINARY and LONG VARBINARY data types Considerations and limitations The Unix drivers do not support the following e Module File Caching MFC e GUI for configuring the driver manager Unsupported ODBC data types 16 The following are considerations and limitations for the Windows Unicode driver Overview You cannot execute DDL statements with character set qualifiers UCS2 or N Prefix 1882 or N to the string literal for the following columns to execute DML statements CHAR CHARACTER SET UCS2 column column the default NATIONAL character set is UCS2 o VARCHAR CHARACTER SET UCS2 column VARYING column the default NATIONAL character set is UCS2 For example insert into CAT SCH TAB unicodechar col values _UCS2 abcd The driver does not support KANJI and KSC5601 as NATIONAL character set Prefix kanji or ksc5601 to the string literal for NCHAR column if SQL MX is installed with KANJI and KSC5601 as NATIONAL character set SQL statements and SOLBindParameter values must be in UTF 16 format The driver considers C variable of type SQL DEFAULT as SQL
38. STEM ZNSSSL SETUP Enter 7 to select ODBC MxX server in run mode Enter the home terminal The default value is SYMIOP CLCI Enter the CPU on which you want to run SSL The default value is CPU 3 Enter the SSL process name The default value is SODBS3 Enter the TCP IP process name for the subnet on which the ODBC MX MXCS service runs The default value is zTCO Enter a port number for the SSL ODBC MX connection The default port number is 8402 Enter or n to specify whether the startup and error messages must be sent to EMS Enter a name for SCF IN file for ODBCMXS configuration The default value is ODBSIN3 Enter a name for the SCF configuration file for the ODBCMXS configuration The default value is ODBSCF3 The following message indicates that SSL configuration successfully created the script files These script files are used to configure and start the process as a kernel managed persistent process SETUP has completed successfully Files created SCF IN file and SCF configuration file Enter the following command to configure NonStop SSL as a persistent process SYSTEM ZNSSSL 11 SCF IN SCF IN filename For example if you use the default name the command is SCF IN ODBSIN3 Enter the following command to start the process SYSTEM ZNSSSL 12 gt SCF START PROCESS ZZKRN SSL ODBCMXS 3 Enter the following command to check whether the process started correctly SYSTEM ZNSSSL 13
39. 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 When CatalogName is SQL ALL CATALOGS Returns all catalogs defined in the SQL MX metadata SQLTables API When SchemaName is SQL ALL SCHEMAS Returns all the schemas defined in the SQL MX metadata SQLTables API When TableType is SQL ALL TABLE TYPES Returns all object types defined in the SQL MX metadata SQL Table Names SQL Catalog and Schema Names The drivers use SQL METADATA 10 to determine whether to upshift a table name This applies for catalog APls SQLTables SQLPrimaryKey and SQLStatistics This does not apply to SOLColumns API SQLBindParameter API For this SOL DEFAULT PARAMand SQL LEN DATA AT EXEC length are not supported for the pcbvalue argument SQLPutData This can send data in parts only for the SQL LONGVARCHAR data type For all other data types only the latest value in the rgbValue argument is sent For information on migrating SQL MP ODBC client applications to SQL MX environment see SQL MX Comparison Guide for NonStop SQL MP Users ODBC data types Table 6 lists the compatibility of drivers for ODBC data types Table 6 ODBC Data Types ODBC data type SQL MX data Type SQL MP data Type Supported by the driver CHAR n CHAR n CHAR n Ye
40. anager OSS You must configure the data source in the ODBCDSN file Windows You must configure the client data sources with Microsoft ODBC Data Source Administrator The driver accesses the configuration details when it sends a connect request to MXCS If the data source is not configured on the server MXCS connects the application to the default data source Default DataSource Server data source configuration The server data sources are configured on a NonStop server For more information about configuring and managing server data sources see the HP NonStop SQL MX Connectivity Service Manual for SQL MX Release 3 x Default data source TDM Default DataSource is the pre configured default server data source You can stop and start Default DataSource with any one of the following but you cannot delete it e NSM web e SQl MX Administrative Command Library MACL mode mxcs invoked from SQL MX Remote Conversational Interface RMXCI SQL MX Database Manager available from SQL MX Release 3 2 onwards MXCS establishes a connection with TDM Default DataSource when there is a mismatch in the client and server data source names For more information about MACL see the SQL MX Connectivity Service Administrative Command Reference Data sources 13 Tracing HP UX Linux 32 and Linux 64 The trace information is captured in a log file You can enable or disable tracing and specify the trace file
41. atatypes such as DATE or TIMESTAMP Set the SQL Datetime Retrieval property using MXODSN property e Ifthe value of SQL Datetime Retrieval is set to SYSTEM DEFAULT the behavior of the drivers is the same as in earlier releases of SQL MX For example 1 Create a table in SQL MP with Datetime column as month to day 2 Insert the data 12 31 into the table using SQL MX or SQL MP 3 Retrieve the column data from the driver by binding to DATE The retrieved value is 0001 12 31 e the value of SQL Datetime Retrieval is set to MSDN DEFAULT there is a change in the behavior of the driver when compared to earlier releases of SQL MX For example 1 Create a table in SQL MP with Datetime column as month to day 2 Insert the data 12 31 into the created table using SQL MX or SQL MP 3 Retrieve the column data from the driver by binding to DATE The retrieved value is 1900 12 31 Compatibility and considerations 5 Error messages Table 17 lists the driver error codes and error messages Table 17 Error codes and error messages 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 01502 Option value changed 07001 Wrong number of parameters 07003 Dynamic SQL error Cursor specification cannot be executed 07
42. ation instructions in the wizards Double click the NonStop SSL RemoteProxy icon in your system tray The RemoteProxy configuration window is displayed Select Session gt New The HP NonStop 551 RemoteProxy dialog is displayed 1l Select ODBCMX Client in the Protocol menu 2 Enter the IP address or host name of the system where the MXCS Association Server process is running in the Target Information field 3 Enter the port number of the SSL server in the Target Connecting Port field For example 8402 4 Enter the port number of the MXCS Association Server in the Local Accepting Port field Click Start to start the Remote Proxy session Check the startup messages to verify that the Remote Proxy session started successfully Configuring a client data source for NonStop SSL 28 Perform the following steps to configure a client data source on your workstation 1 2 3 Select Start gt Control Panel gt Administrative Tools gt Data Sources ODBC Click either the System DSN or the User DSN tab to get the list of data sources Select the data source name that you want to configure and click Configure Change the MXCS IP Address in Network tab to localhost in the screen shot after table 3 Go to Testing tab and click Test Connection to test the connection Upon successful connection Connected Successfully message is displayed Configuring data sources DataSource configuration in Windows driver NonS
43. by AUTOCOMMIT setting Table 10 lists the transaction and cursor behavior Microsoft escape clauses 41 Table 10 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 Timestamp values with fraction The SQLFractionValue attribute indicates whether the fraction value for SQL TIMESTAMP data type must be nanoseconds or microseconds If set to 9 the fraction value is in nanoseconds If set to SQL_DEFAULT not specified or set to a value other than 9 the fraction value is microseconds Table 11 includes a set of sample values and expected values in the column of an SQL MX table in microseconds Table 11 Sample values inserted from an application and expected values in microsecond Expected value in the column of Fraction value in the application Column type SQL MX table 987654 timestamp 6 or time 6 0 987654 87654 timestamp 6 or time 6 0 087654 7654 timestamp 6 or time 6 0 007654 654 timestamp 6 or time 6 0 000654 54 timestamp 6 or time 6 0 000054 4 timestamp 6 or time 6 0 000004 987654 timestamp 5 or time 5 0 98765 87654 timestamp 5 or t
44. cess for 26 Installing and configuring the Remote Proxy Client rris 27 Configuring a client data source for NonStop 28 DataSource configuration in Windows scettr rtr 29 and ODBCDSN file ato o etin n RENE veo M MES n 30 Managing dole SOUICBS eec epa en LAM pi DEEP UU 33 Managing TOTO 34 Setting Ziufe 34 35 4 Compatibility and terne erbe nine etin aros 37 qe bati e tv 37 Mind st Eie Sr 38 Unsigned datda e eee 39 Contents 3 Partial DATE or _ __8_ 40 Microsoft escape lt 1 Ne een 41 Stored I TT E 41 Transaction and cursor DN OVIOE 41 Timestamp values with TRIGGER 42 SQL Interval Behaviour
45. cing distinguishes items in a continuation line from items in a vertical list of selections For example match value NOT LIKE pattern ESCAPE esc char expression Related Documentation This manual is part of the HP NonStop SQL MX library of manuals NonStop SQL MX customer library The manuals in the SQL MX customer library are listed here for your convenience e Introductory guides SQL MX Comparison Guide for Describes differences between NonStop SQL MP and NonStop SQL MX SQL MP Users databases SQL MX Quick Start Guide Describes basic techniques for using SQL in the SQL MX conversational interface MXCI Includes information about installing the sample database Related Documentation 7 Installation guides SQL MX Installation and Describes how to plan for install and upgrade an SQL MX database Upgrade Guide SQL MX Management Manual Describes how to manage an SQL MX database NSM web Installation Guide Describes how to install NSM web and troubleshoot NSM web installations Reference manuals SQL MX Reference Manual Describes the syntax of SQL MX statements 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 This manual is an installation guide and describes how to install and manage Manual SQL MX Connectivity Service MXCS which enabl
46. e dots ANSI names for SQL MX objects ANSI names for SQL MX database objects are registered in the metadata in SQL MX tables ANSI alias names for SQL MP objects Only ANSI name types are supported for SQL MP database objects Alias names are ANSI standard names that map to physical Guardian names for existing SQL MP objects To add ANSI alias names for SQL MP tables use the command CREATE SQLMP ALIAS which stores the alias name in the SQL MX catalog For more information about CREATE SQLMP ALIAS command see the SQL MX 3 x Reference Manual Unsupported ODBC APls The drivers are ODBC 3 5 compliant Table 1 page 15 lists the unsupported ODBC 14 Overview Table 1 Unsupported ODBC APIs Category Unsupported APIs Installer Data Sources SQLManageDataSources SQLRemoveDefaultDataSource SQLConfigDataSource SQLCreateDataSource SOLValidDSN SQLRemoveDSNFromIni SOLWriteDSNToIni Installer Drivers SQLConfigDriver SQLRemoveDriver e SQLInstallDriver e SQLInstallDriverEx SOLValidDSN SOLGetInstalledDrivers Installer Driver Manager SQLRemoveDriverManager e SQLInstallDriverManager Installer Installer Errors e SQLInstallerError SQLPostInstallerError Installer Profile Strings SQLWritePrivateProfileString SQLGetPrivateProfileString Installer Translator SQLInstallTranslator e SQLInstallTranslatorEx SQLRemoveTranslator e S
47. es ODBC and other connectivity APIs to use NonStop SQL MX SQL MX Connectivity Service Describes the SQL MX Administrative Command Library MACL available with Administrative Command the SQL MX conversational interface MXCI Reference SQL MX Remote Describes how to use SQL MX Remote Conversational Interface to run the RMXCI Conversational Interface commands and SQL statements interactively or from script files RMXCI Guide HP NonStop JDBC Type 4 Describes the NonStop JDBC Type 4 Driver functionality which allows Java Driver 3 0 Programmer s programmers to remotely develop applications deployed on client workstations Reference to access NonStop SQL MX databases ODBC MX Client Drivers User Describes how to install and configure the ODBC MX client drivers These Guide products enable applications developed for the Open Database Connectivity ODBC application programming interface to access data from an SQL MX database on a NonStop system Migration guides SQL MX Database and Describes how to migrate databases and applications to NonStop SQL MX Application Migration Guide how to manage different versions of NonStop SQL MX NonStop NS Series Database Describes how to migrate NonStop SQL MX NonStop SQL MP Enscribe Migration Guide databases and applications to HP Integrity NonStop NS series systems Data management guides SQL MX Data Mining Guide Describes SQL MX data structures and operations for data m
48. es are returned as partial even if the driver returns a full DATE or TIME or TIMESTAMP data type Default values are returned as a structure 1 for YEAR MONTH and DAY and 0 for HOUR MINUTE SECOND and FRACTION Table 8 SQL MP to ODBC MX Date Time Mappings SQL MP data type ODBC MX data type DATETIME YEAR SQL DATE DATETIME YEAR TO MONTH SQL DATE DATETIME YEAR TO DAY SQL DATE DATETIME YEAR TO HOUR SQL TIMESTAMP DATETIME YEAR TO MINUTE SQL TIMESTAMP DATETIME YEAR TO SECOND SQL TIMESTAMP DATETIME YEAR TO FRACTION SQL TIMESTAMP DATETIME MONTH SQL DATE DATETIME MONTH TO DAY SQL DATE DATETIME MONTH TO HOUR DATETIME MONTH TO MINUTE SQL TIMESTAMP SQL TIMESTAMP DATETIME MONTH TO SECOND SQL TIMESTAMP DATETIME MONTH TO FRACTION SQL TIMESTAMP DATETIME DAY SQL DATE DATETIME DAY TO HOUR SQL TIMESTAMP DATETIME DAY TO MINUTE SQL TIMESTAMP DATETIME DAY TO SECOND SQL TIMESTAMP DATETIME DAY TO FRACTION SQL TIMESTAMP DATETIME HOUR SQL TIME DATETIME HOUR TO MINUTE SQL TIME DATETIME HOUR TO SECOND SQL TIME DATETIME HOUR TO FRACTION SQL TIMESTAMP DATETIME MINUTE SQL TIME DATETIME MINUTE TO SECOND SQL TIME 40 Compatibility and considerations Table 8 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 DAT
49. essages continued Error codes Error messages 51000 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 51000 Unknown token received SQL Server 51000 Unable to load communication module Driver has not been correctly installed 51000 Communication module is not valid Driver has not been correctly installed 51000 Data type mismatch 51000 Program Error Contact your service provider 51001 Memory allocation error 51002 Invalid column number 51003 Program type out of range 51004 SQL data type out of range 51005 Parameter number out of range 51006 Invalid conversion specified 51007 Row count not available from the data source 51008 Operation cancelled 51009 Invalid argument value S1010 Function sequence error 51012 Invalid transaction operator code specified 51015 No cursor name available 1090 Invalid string or buffer length 1091 Descriptor type out of range 1092 Option type out of range 1093 Invalid parameter number 51094 Invalid scale value 51095 Function type out of range 51096 Information type out of range 51097 Column type out of range 51098 Scope type out of range 51099 Nullable type out of range S1100 Uniqueness option type out of ra
50. et tns e DYOSEMITE Wcall shared 1 D GNUC DOSS DRIVER D DEBUG DNSK PLATFORM V DTCL MEM DEBUG D TANDEM Dset fieldalign 4 D TNS TARGET WIEEE float I usr include odbc Compiling and linking the sample application 53 c ThreadODBCTestAppl c o ThreadODBCTestAppl o The following is a sample compilation command for a 64 bit application 055 gt c89 g Winspect Wextensions Ww Woptimize 0 1 64 Wsystype oss Wrefalign 8 Wfieldalign auto 4 Wallow cplusplus comments Wcplusplus Wversion3 Wtarget tns e DYOSEMITE Wcall shared D PUT MODEL D GNUC DOSS DRIVER D DEBUG DNSK PLATFORM 1 DTCL MEM DEBUG D TANDEM Dset fieldalign D TNS TARGET WIEEE float I usr include odbc 4 c ThreadODBCTestAppl c o ThreadODBCTestAppl o NOTE For C programming applications add 11 cplusplus comments flag Use the eld linker to link the application with the driver 32 bit ZODBCDLL 64 bit YODBCDLL By default the driver is located in SYSTEM ZMXODBC directory Testing the sample HP UX and Linux To run the sample test application issue any of the following commands on the bash shell 15 application name DS Name odbc application name help Considerations e library is not present in the default location usr 1ib or usr 1ib64 in 64 Bit Linux ensure that the location of the driver libmxodbc so libmxodbc64 so in 64 Bit Linux is set in the environ
51. gram Folder on the following screen Select Program Folder Please select a program folder Setup will add program icons to the Program Folder listed below You may type a new folder name or select one from the existing folders list Click Next to continue Program Folder NonStop 3 2 Existing Folders Activldentity Administrative Tools Adobe Adobe LiveCycle ES2 Arbortext COE Utils Games Get IT Support InstallShield Click Next You can see the installation progress in the Setup Status progress bar After a successtul installation the following screen appears 20 Installing the drivers InstallShield Wizard Complete Setup has finished installing NonStop TM 3 2 on your computer would like to view the README I would like to to add a new data source now Click Finish to complete NonStop TM 3 2 Setup 12 Click Finish NOTE e recommends that you restart the PC before you use the ODBC Mx client interface e When the installation is complete delete the temporary installation folder Verifying the installation HP UX After a successful installation the following files are installed e usr lib hpux64 libmxodbc ia64 s1 e usr lib hpux64 libmxodbc ia64 drvr sl e usr lib hpux64 libhpsecClient64 s1l etc mxodbc MXODSN The checksum of the shared libraries must match the checksum in m
52. he file name is ODBC64HI and is located in the volume SSYSTEM ZMXODBC Log on to the HP UXia64 workstation as a root user Go to download directory and enter the following command to extract the files tar xvf ODBC64HI The files are extracted to HPUXia64 ODBC 64 directory The install scripts and the README file are also extracted with the driver Go to the HPUXia64 ODBC 64 directory To start the installation enter the following command runme sh Enter 1 and press Enter If you have already installed the driver a warning message appears The library files are installed in the usz 1ib hpuxe4 directory and a sample MXODSN file is copied to the etc mxodbc directory Upon successful installation the following message is displayed Installed Linux 32 Perform the following steps to install the driver 1 Download the driver from NonStop system to the Linux workstation in binary mode You can use FTP utility for downloading The driver is packaged as a tar file in the Software Update Tape SUT and is available on the NonStop system when DSM SCM is used to install the SUT The file name is LODBCTAR and is located in the volume SSYSTEM ZMXODBC Log in to the Linux workstation as a root user Go to download directory and enter the following command to extract the files tar xvf LODBCTAR The files are extracted to Linux ODBC 32 directory The install scripts and the README file are also extracted with the dr
53. ime 5 0 08765 7654 timestamp 5 or time 5 0 00765 654 timestamp 5 or time 5 0 00065 54 timestamp 5 or time 5 0 00005 4 timestamp 5 or time 5 0 00000 987654 timestamp 4 or time 4 0 9876 87654 timestamp 4 or 4 0 0876 7654 timestamp 4 or time 4 0 0076 654 timestamp 4 or 4 0 0006 54 timestamp 4 or time 4 0 0000 4 timestamp 4 or time 4 0 0000 987654 timestamp 3 or time 3 0 987 87654 timestamp 3 or time 3 0 087 7654 timestamp 3 or time 3 0 007 654 timestamp 3 or time 3 0 000 42 Compatibility and considerations Table 11 Sample values inserted from an application and expected values in microsecond continued Expected value in the column of Fraction value in the application Column type 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 00 987654 timestamp l or time 1 0 9 87654 timestamp l or time 1 0 0 7654 timestamp l or time 1 0 0 654 timestamp l or time 1 0 0 54 timestamp l or time 1 0 0 4 timestamp 1 or time 1 0 0 Table 12 includes a set of sample values and expected values in the column of an SQL MX table in nanoseconds Table 12 Sample values inserted from an application and expected values in na
54. ining SQL MX Report Writer Guide Describes how to produce formatted reports using data from an SQL MX database Dataloader MX Reference Describes the features and functions of the Dataloader MX product a tool to Manual load SQL MX databases e Application development guides SQL MX Programming Manual Describes how to embed SQL MX statements in ANSI C and COBOL programs for C and COBOL SQL MX Query Guide SQL MX Queuing and Publish Subscribe Services SQL MX Guide to Stored Procedures in Java e Online help Reference Help Messages Help Glossary Help NSM web Help Visual Query Planner Help SQL MX Database Manager Help Publishing history Part Number 734873 001 Product Version Describes how to understand query execution plans and write optimal queries for an SQL MX database Describes how NonStop SQL MX integrates transactional queuing and publish subscribe services into its database infrastructure Describes how to use stored procedures that are written in Java within NonStop SQL MX Overview and reference entries from the SQL MX Reference Manual Individual messages grouped by source from the SQL MX Messages Manual Terms and definitions from the SQL MX Glossary Context sensitive help topics that describe how to use the NSM web management tool Contextsensitive help topics that describe how to use the Visual Query Planner graphical user interface Contents and reference entrie
55. iver Go to the Linux ODBC 32 directory To start the installation enter the following command runme sh Enter 1 and press Enter If you have already installed the driver a warning message appears The library files are installed in the usr 1ib directory and a sample MXODSN file is copied to etc hpodbc directory Upon successful installation the following message is displayed Installed Linux 64 Perform the following steps to install the driver 1 Download the driver from NonStop system to the Linux workstation in binary mode You can use FTP utility for downloading The driver is packaged as a tar file in the Software Update SUT and is available on the NonStop system when DSM SCM is used to install the SUT The file name is LODBC64 and is located in the volume SSYSTEM ZMXODBC Log in to the Linux workstation as a root user 18 Installing the drivers 3 download directory and enter the following command to extract the files tar xvf LODBC64 The files are extracted to 641 inux ODBC 321 directory The install scripts and the README file are also extracted with the driver 4 Goto 64Linux ODBC 321 directory 5 To start the installation enter the following command runme sh 6 Enter 1 and press Enter If you have already installed the driver a warning message appears The library files are installed in the usr 1ib64 directory and a sample MXODSN file is copied to
56. l tdm 0300 411 tdm 40300 411 tdm 0300 411 tdm translation dll After a successful installation of Unicode driver the following files are installed in Windows System folder tdm oadmw0300 dll tdm odbcw0300 dll tdm odbcDrvMsg intlw0300 dll e tdm OdbcTracew0300 dll tdm oresw0300 dll e tdm tcpipv4w0300 dll tdm tcpipv6w0300 dll tdm translationw dll Uninstalling the driver HP UX Perform the following steps to uninstall the driver 22 Installing the drivers m Log in to the HP UXia64 workstation as a root user Go to the HPUXia64 ODBC 64 directory To uninstall the driver enter the following command runme sh Enter 2 and press Enter Enter yes if you want to continue with uninstall operation Enter yes if you want to remove the configuration files Upon successful uninstallation the following message is displayed UnInstalled Linux 32 Perform the following steps to uninstall the driver k 2 Goto the Linux ODBC 32 directory 3 To uninstall the driver enter the following command runme sh 4 Enter 2 and press Enter Enter yes if you want to continue with uninstall operation 5 Enter yes if you want to remove the configuration files 6 Upon successful uninstallation the following message is displayed UnInstalled Linux 64 Log in to the Linux workstation as a root user Perform the following steps to uninstall the driver
57. le on HP UX and Linux or the ODBCDSN file on OSS which is a text file is a repository of all client data sources 24 You can place the MXODSN or ODBCDSN file in any one of the following locations the search rder for these files are also mentioned 1 Directory specified in 5 environment variable 2 ODBC application directory 3 Default system location e On HP UX etc mxodbc e Onlinux32 and Linux64 etc hpodbc e On OSS G system system which is the OSS name for the Guardian location To configure a client data source edit the MKODSN or ODBCDSN file Change this file so that the attributes are set to the correct values for your system for example names of data sources a nd attributes describing them The DSN in the MXODSN or ODBCDSN file must exactly match the server data source name data source names are case sensitive If the data source names do not match the application 9 T If ets a warning that connection is established with the default data source DM Default DataSource you have not defined the CATALOG and SCHEMA attributes in the MXODSN or ODBCDSN files the driver uses the default values Windows Perform the following steps to add a client data source 1 2 3 4 Select Start gt Control Panel gt Administrative Tools gt Data Sources ODBC Click Add to add a new data source To configure a data source get the list of data source names by clicking eithe
58. lications The drivers forward the request to MXCS which authenticates the connection parameters After successful authentication MXCS assigns an ODBC server instance to the application MXCS matches the client data source name with server data source name If the client data source name matches the server data source name MXCS attempts a connection MXCS connects to the data source based on the following conditions data source must be configured and started e Ifthe data source is not configured on the server then MXCS forwards the connect request to the default data source TDM Default DataSource After the connection is established MXCS forwards the application requests to the SQL MX database and routes the responses from the database to the driver The communication between the driver and MXCS is over TCP IP sockets in the client server parlance Configure the IP address and the port number for the HP UX and Linux drivers in the Architecture MXODSN file and for the OSS driver in the ODBCDSN file Use the Microsoft ODBC Data Source Administrator to configure the parameters for the Windows driver Client components The common client components are the ODBC applications which are user written or third party products that use the ODBC APIs and the driver for the application platform Install the following client components for HP UX Linux and Windows platforms HP UX e unixODBC driver manager only required for s
59. lue2 st SQLRowCount hstmt amp RowCount if st SOL SUCCESS printf Error in SQLRowCount odbc Error henv hdbc hstmt 58 Sample ODBC application else printf Row s affected dWMn RowCount st SQLExecDirect hstmt SQLCHAR SelectTable strlen SelectTable if st 501 SUCCESS printf Error in Select Table GGTest n odbc Error henv hdbc hstmt st SOLBindCol hstmt 1 SOL C SLONG amp value 0 amp ValInd if st 501 SUCCESS printf Error in SQLBindCol Wn odbc Error henv hdbc hstmt while TRUE if st SQLFetch hstmt SOL NO DATA FOUND break if st SQL SUCCESS amp amp st SQL SUCCESS WITH INFO printf Error in SQLFetch Returned Status d n st break if ValInd SOL NULL DATA printf No Data fetched n break else printf c1 d n value k k Done with connection 1 x x k xkkdkkkkkk printf n tUsing Connection 2 n n st SQLA1llocHandle SQL HANDLE STMT SOLHDBC hdbci amp hstmt1 if hstmtl1 NULL printf Error in allocating statement handle n odbc Error SQL NULL HENV SQL NULL HDBC hstmt st SQLPrepare hstmt1 SQLCHAR insert into GGTest c1 values SOL NTS if st 501 SUCCESS printf Error in SQLPrepare n henv hdbc1 hstmt1 st SQLBindParameter hstmt1 1 SQL PAR
60. ment variable LD LIBRARY PATH Linux 32 or Linux 64 or SHLIB_PATH HP UX To set the LD_LIBRARY_PATH variable enter the following command export LD LIBRARY PATH driver directory e Ensure that the MXODSN file is present in the same location as that of the sample application not ensure that the default file is present in etc hpodbc Linux or etc mxodbc HP UX or set DSN_PATH to MXODSN location e To enable tracing set the TraceStart attribute in the MXODSN file to 1 You must edit the MXODSN file to include your preferred DSN Update all the mandatory fields for the DSN OSS To run the sample test application issue any of the following commands on the OSS shell 055 gt application name DS Name 055 gt application name help Considerations e Ensure that the ODBCDSN file is present in the same location as that of the sample application If not ensure that the ODBCDSN file is present in the default location SYSTEM SYSTEM or in the DSN PATH e To enable tracing set the TraceStart attribute in the ODBCDSN file to 1 You must edit the ODBCDSN file to include your preferred DSN Update all the mandatory fields for the DSN Sample application code kk kk kk KK KK KkKKKK START COPYRIGHT 54 Sample ODBC application Copyright 2007 HP CONFIDENTIAL NEED TO KNOW ONLY Copyright Hewlett Packard Development Company L P
61. n 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 A 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 000 timestamp 3 or time 3 0 0 98 timestamp 2 or time 2 98 0 09 timestamp 2 or time 2 9 0 00 timestamp 2 or time 2 0 0 9 fimestamp 1 or time 1 9 0 0 fimestamp 1 or time 1 0 Table 14 includes a set of sample values retrieved by the application when SQLFractionValue is 9 The values are in nanoseconds Table 14 Sample values retrieved by the application Value in the column of the SQL MX Fraction value retrieved by the Table Column type application 0 987654 timestamp 6 or time 6 987654000 0 087654 timestamp 6 or time 6 87654000 0 007654 timestamp 6 or time 6 7654000 0 000654 timestamp 6 or time 6 654000 0 000054 timestamp 6 or time 6 54000 0 000004 timestamp 6 or time 6 4000 0 98765 timestamp 5 or time 5 987650000 0 08765 timestamp 5 or time 5 87650000 0 00765 timestamp 5 or time 5 7650000 0 00065 timestamp 5 or time 5 650000 0 00006 timestamp 5 or time 5 60000 0 00000 timestamp 5 or time 5 0 0 9876 timestamp 4 or time 4 987600000 0 0876 timestamp 4 or time 4 87600000 0 0076 timestamp 4 or time 4 7600000 0 0007 timestamp 4 or time 4 700000 0 0000 timestamp A
62. name in MXODSN file When using the unixODBC driver manager you can enable the driver tracing by setting the following environment variables e HPODBC TRACE LEVEL For trace values see MXODSN and ODBCDSN file formats page 30 e TRACEFILE NAME to set the trace file name PID and current time are appended to this name to form the final trace file name e TRACEFILE SIZE to set the maximum trace file size The default file size is 1 GB OSS The trace information is captured in an OSS log file You can enable or disable tracing and specify the trace file name in ODBCDSN file Windows You can trace the entry and exit calls from the Microsoft driver manager to the driver contents of the buffers except password buffers and activity between the driver and transport layer To get the tracing help click the Help button in the Microsoft ODBC Data Source Administrator panel NOTE For information about MXCS tracing see SQL MX Connectivity Service Administrative Command Reference manual Object naming and mapping The drivers 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 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 th
63. nge 5101 Accuracy option type out of range 51102 Table type out of range S1103 Direction option out of range S1105 Invalid parameter type or parameter type not supported 51 52 Table 17 Error codes and error messages continued Error messages Error codes Error messages 51106 Fetch type out of range 5107 Row value out of range 5108 Concurrency option out of range 51109 Invalid cursor position keyset defined 51 00 Driver not capable STLDO No long data values pending S1TOO Timeout expired 6001 INVALID DLL HANDLE 6002 LOAD PROCADDRESS 6003 WRONG WINSOCK VERSION 6004 WRONG SIGNATURE 6005 WRONG VERSION 6006 ERROR FROM SERVER 6007 INCORRECT LENGTH 6008 MEMORY ALLOCATE 6009 WRONG IP ADDRESS 6010 Connection lost invalid code path A Sample ODBC application Compiling and linking the sample application HP UX Use the g compiler to compile the application with the header files that are shipped along with the driver odbc g m64 o lt output object name gt I lt location of the header files gt 1 lt input file for compilation gt 1 1 64 Linux 32 Use the g compiler to compile the application with the header files that are shipped along with the driver odbc g m32 o lt output object name gt I lt location of the header files gt 1 lt input file
64. noseconds Fraction value in the application Column type Expected value in the column of SQL MX table 987654321 timestamp 6 or 6 0 987654 87654321 timestamp 6 or 6 0 087654 7654321 timestamp 6 or 6 0 007654 65432 timestamp 6 or 6 0 000654 54321 timestamp 6 or 6 0 000054 4321 timestamp 6 or 6 0 000004 987654321 timestamp 5 or time 5 0 98765 87654321 timestamp 5 or time 5 0 08765 7654321 timestamp 5 or time 5 0 00765 654321 timestamp 5 or time 5 0 00065 54321 timestamp 5 or time 5 0 00005 4321 timestamp 5 or time 5 0 00000 987654321 timestamp 4 or time 4 0 9876 87654321 timestamp 4 or time 4 0 0876 7654321 timestamp 4 or time 4 0 0076 654321 timestamp 4 or time 4 0 0006 54321 timestamp 4 or time 4 0 0000 Timestamp values with fraction 43 Table 12 Sample values inserted from an application and expected values in nanoseconds continued Table 13 includes set of sample values retrieved the application when SQLFractionValue is set to SYSTEM DEFAULT or to a value other than 9 Table 13 Values retrieved by the application for SYSTEM DEFAULT setting Value in the column of the SQL MX Fraction value in the application Column type Expected value in the column of SQL MX table 4321 timestamp 4 or 4 0 0000 987654321
65. omatically commit the transaction or not The following are the valid values for AutoCommit property ON OFF e SERVER_DEFAULT specifies to use the server side AutoCommit settings This option is useful for existing applications where AutoCommit is set on the server side data source and the user wants to persist with the same settings without making any changes to the client application or client side data source This is the default option AutoCommit property can be set in the following ways e Using SQLSetConnectAttr API the values are ON OFF e Using DSN properties the values are SERVER_DEFAULT ON OFF Using connection string without DSN the values are ON OFF SERVER DEFAULT NOTE value set using SQLSetConnectAttr API takes the highest precedence Setting AutoCommit Windows driver You can set the AutoCommit property from the Data Access tab Unix drivers AUTOCOMMIIT case sensitive is the keyword to set AutoCommit property in property file MXODSN for Linux and HP UX ODBCDSN for OSS Example 1 Depicting AUTOCOMMIT set to SERVER DEFAULT TDM Default DataSource Description Default Data Source Catalog CAT Schema SCH DataLang 0 FetchBufferSize SYSTEM DEFAULT Server TCP hostname portnumber SOL ATTR CONNECTION TIMEOUT SYSTEM DEFAULT SOL LOGIN TIMEOUT SYSTEM DEFAULT SOL QUERY TIMEOUT NO TIMEOUT SQLFractionValue SYSTEM DEFAULT SQLDatetimeRetrieval
66. on value list does not match column list 21501 violation parameter list does not match column ist 21502 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 22012 Division by zero 22015 Interval field overflow 22018 Invalid character value for cast specification 23000 Integrity constraint violation 24000 Invalid cursor state 25000 Invalid transaction state 26000 Invalid SQL statement identifier 28000 Invalid authorization specification 28000 Invalid authorization specification access to selected database is denied 34000 Invalid cursor name 37000 Syntax error in SQL dynamic statement 3C000 Duplicate cursor name 40001 Attempt to initiate new SQL server operation with data pending 42000 Syntax error or access rule violation 70100 Operation aborted server did not process cancel request 50001 Invalid table base table or already exists S0002 Invalid table name table or view not found 5001 Invalid index name index already exists 50012 Invalid index name index not found 50021 Invalid column name column already exists S0022 Invalid column name column not found 51000 General error 51000 General error Ongoing transaction has been committed 51000 error Failed since resource governing policy is it Error messages Table 17 Error codes and error m
67. or 2 3 1 and later For installing unixODBC see the unixODBC website http www unixODBC org Linux 64 The 64 Bit Linux driver is LSB 4 1 compliant and is tested on 64 Bit RHEL 6 2 6 3 and A standard Linux installation is required for the driver If you want the applications to use the unixODBC driver manager then download and install unixODBC driver manager release 2 2 14 or 2 3 1 and later For installing unixODBC see the unixODBC website http www unixODBC org Windows The following table lists the installation requirements for the Windows driver Component Installation requirement Memory Minimum 32 MB Disk space Minimum 30 MB additional free space Operating System ANSI driver Microsoft Windows NT 4 0 Windows 98 Windows 2000 Windows 2003 or Windows XP Windows Vista and Windows 7 Unicode driver Windows 2003 Windows XP Service Pack2 or later Windows Vista and Windows 7 Driver Manager Microsoft ODBC version 3 51 or later Browser Microsoft Internet Explorer 4 0 or later HP recommends version 8 0 Installation procedures HP UX Perform the following steps to install the driver Installation requirements 17 6 Download the driver from NonStop system to the HP UXia64 workstation in binary mode You can use FTP utility for downloading The driver is packaged as tar file in the Software Update Tape SUT and is available on the NonStop system when 05 5 is used to install the SUT T
68. p ODBCMX version Unicode 2 Create or modify a DSN 3 Select the Network tab to enter the address in the MXCS IP Address field is not supported for the driver through HP 551 For more information about SSL see the HP NonStop SSL Reference Manual NOTE 551 ODBC MX and MXOAS processes must run on the same TCP IP process TargetPort must not be specified with HP NonStop SSL REMOTE PROXY SETUP macro Configuring a secure ODBC connection using NonStop SSL Perform the following tasks to encrypt the ODBC connection by using NonStop SSL with Windows driver 1 Install a NonStop SSL Server process for the ODBC MX Association Server for the target MXCS subsystem 2 the RemoteProxy software and configure Remote Proxy to route ODBC connections to the NonStop SSL Server process 3 Modify the connection attributes of client data source for Windows drivers to connect to Remote Proxy Installing a NonStop SSL Server process for ODBC MX Installing a NonStop SSL Server process is restricted to the super super user Perform the following steps on the NonStop server to install a NonStop SSL Server process for ODBC Mx 26 Configuring data sources 14 15 log on as super super Enter the following command to change to the NonStop SSL directory SSYSTEM STARTUP 1 volume Ssystem znsssl Enter the following command at the TACL prompt to start the setup ZNSSSL 10 gt run SY
69. pplications can use either the unixODBC driver manger versions 2 2 14 or 2 3 1 and later or the SQL MX driver manager When compiling the applications can directly link with the unixODBC library or can use connectors to communicate with the driver manager The driver manager uses the information provided by the applications and routes the ODBC API calls to the driver For more information about unixODBC driver manager see the unixODBC website htip www unixODBC org The Windows applications use the Microsoft ODBC Driver Manager version 3 5 1 or later 12 Overview Data sources Data sources are logical groupings of connections to the SQL MX database There are two types of data sources e Client data source commonly known as Data Source DSN The DSN is configured on the client workstation e Server data source which you must define and start on the NonStop system The driver uses the client data source configuration to route the ODBC requests to MXCS 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 dient data sources are configured on the client workstation HP UX Linux32 and Linux 64 e Configure the client data sources in the MXODSN file when the driver uses the SQL MX driver manager e Configure the client data sources in the odbc ini and odbcinst ini files when the driver uses the unixODBC driver m
70. r data source nen ee 13 data SOURCE ele SUE a 13 to nu ce 14 Object naming Rd UM iUe 14 name E EE 14 ANSI names 14 ANSI alias names SQL MP 14 Unsupported ODBC AP CT 14 Unsupported ODBC e us rtu mU neca 15 Considerations and MMR ONE ceci is hace buco ta spite 15 7 IPTE OR 17 Installation 7 17 Verifying ute 21 Uninstalling the d 22 ee 24 Configuring a client data SOU Goro 24 Configuring the drivers 26 Configuring a secure ODBC connection using NonStop 551 0 2 2 2 2 00 26 Installing a NonStop SSL Server pro
71. r the System DSN or the User DSN tab Choose NonStop TM ODBCMX lt Release gt from the list and then click Finish lt Release gt 15 version or Unicode version Enter the details in the Create Data Source screens Perform the following steps to configure a client data source 1 2 Select Start gt Control Panel gt Administrative Tools gt Data Sources ODBC Click either the System DSN or the User DSN tab to get the list of data sources Configuring data sources o Select the data source name that you want to configure and click Configure 4 Each tab in the configuration view displays different fields in the data source configuration Make the required changes For more information about the Microsoft ODBC Data Source Administrator screens and tabs click Help NOTE e While creating a new data source ensure the following special characters are not part of the data source name TES Ute e When creating a new client data source ensure that it matches that of a server data source name and have the matching case a client data source name does not exist on the server the default server data source TDM Default DataSource is used The following connection string is used to configure the client data source DSN DataSource name catalog catalog name schema schema name Uid lt userid gt Pwd lt password gt FETCHBUFFERSIZE lt value in bytes SOLFRACTIONVALUE value ATTR C
72. rr gt szErrorMsg UnSignCharCast malloc 200 Err gt szSqlState UnSignCharCast malloc 50 szBuf char malloc 600 memset Err gt szErrorMsg 0 200 memset Err gt szSqlState 0 50 Testing the sample 55 memset szBuf 0 600 if hStmt Err gt pfNativeError else if hDbc Err gt pfNativeError else if hEnv Err gt pfNativeError st SQLGetDiagRec SQL HANDLE STMT hStmt 1 Err gt szSqlState Err gt szErrorMsg 150 Err gt pcbErrorMsg st SQLGetDiagRec SQL HANDLE DBC hDbc 1 Err gt szSqlState Err gt szErrorMsg 150 Err gt pcbErrorMsg st SQLGetDiagRec SQL HANDLE ENV hEnv 1 Err gt szSqlState Err gt szErrorMsg 150 Err gt pcbErrorMsg if st SQL SUCCESS st SQL SUCCESS WITH INFO sprintf szBuf s s n char Err gt szErrorMsg Err gt szSqlState printf s n szBuf free Err gt szErrorMsg free Err gt szSqlState free Err free szBuf return SQL SUCCESS int main int argc char RETCODE st 0 SQLHENV henv SQLHENV NULL SQLHDBC hdbc SQLHDBC NULL SQLHDBC hdbcl SQLHDBC NULL SQLHDBC hdbc2 SQLHDBC NULL SQLHSTMT hstmt SQLHSTMT NULL SQLHSTMT hstmt1 SQLHSTMT NULL CHAR CreateTable 100 o CHAR InsertTable 100 CHAR SelectTable 100 o int value SOLINTEGER ValInd SOLINTEGER RowCount SOLINTEGER
73. s VARCHAR n VARCHAR n VARCHAR n Yes LONGVARCHAR VARCHAR n VARCHAR n Yes DECIMAL p s DECIMAL p s DECIMAL p s Yes NUMERIC p s NUMERIC p s NUMERIC p s Yes SMALLINT SMALLINT SMALLINT Yes INTEGER INTEGER INTEGER Yes REAL REAL REAL Yes FLOAT p FLOAT p FLOAT p Yes DOUBLE PRECISION DOUBLE PRECISION DOUBLE PRECISION Yes BIT Not supported Not supported No TINYINT Not supported Not supported No 38 Compatibility and considerations Table 6 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 INTERVAL YEAR p TO INTERVAL YEAR p TO Yes MONTH MONTH INTERVAL DAY p INTERVAL DAY p Yes INTERVAL HOUR p INTERVAL HOUR p Yes INTERVAL MINUTE p INTERVAL MINUTE p Yes INTERVAL SECOND p INTERVAL SECOND p Yes INTERVAL DAY p TO HOUR INTERVAL DAY p TO HOUR Yes INTERVAL DAY p TO INTERVAL DAY p TO Yes MINUTE MINUTE INTERVAL DAY p TO INTERVAL DAY p TO Yes SECOND SECOND INTERVAL HOUR p TO INTERVAL HOUR p Yes MINUTE MINUTE INTERVAL HOUR p
74. s from the SQL MX Database Manager User Guide Publication Date ODBC MX Client Drivers User August 2013 Guide for SQL MX Release 3 2 1 734873 002 ODBC MX Client Drivers User November 2013 Guide for SQL MX Release 3 2 1 HP encourages your comments HP encourages your comments concerning this document We are committed to providing documentation that meets your needs Send any errors found suggestions for improvement or compliments to docsfeedback hp com Include the document title part number and any comment error found or suggestion for improvement you have concerning this document Publishing history 9 1 Overview The following ODBC MX client drivers enable ODBC applications running on various platforms to access a NonStop SQL MX dotabase linux 32 bit and 64 bit drivers e HP UX 64 bit driver e Windows 32 bit and 64 bit drivers for ANSI and Unicode applications OSS 32 bit and 64 bit libraries The HP UX Linux OSS Windows ANSI and Unicode ODBC MX client drivers are collectively referred as drivers In this manual the HP UX Linux and OSS drivers are collectively referred as Unix drivers The drivers implement the ODBC 3 5 set of APls The ODBC client applications applications send the requests to access data from an SQL MX database through these drivers This chapter describes the following Architecture page 10 e Client components page 12 e Server
75. s that you supply Items not enclosed in brackets are required For example file name Computer Type Computer type letters within text indicate case sensitive keywords and reserved words Type these items exactly as shown Items not enclosed in brackets are required For example myfile sh 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 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 For example DROP schema CASCADE RESTRICT DROP schema CASCADE RESTRICT Braces Braces enclose required syntax items For example FROM grantee grantee A group of items enclosed in braces is a list from which you are required to select one item The items in the list can be arranged either vertically with aligned braces on each side of the list or horizontally enclosed in a pair of braces and separated by vertical lines For example INTERVAL start field TO end field single field INTERVAL start field TO end field single field
76. tandalone driver libmxodbc ia64 drvr sl which is the open source driver manager implementation that you can download from the unixODBC website http www unixODBC org driver manager which is the driver manager implementation from HP and includes a set of APIs which internally makes multiple ODBC calls Linux 32 unixODBC driver manager only required for standalone driver libmxodbc_1_drvr so which is the open source driver manager implementation that you can download from the unixODBC website http www unixODBC org driver manager which is the driver manager implementation from HP and includes a set of APIs which internally makes multiple ODBC calls Linux 64 e unixODBC driver manager only required for standalone driver libmxodbc64 1 drvr so which is the open source driver manager implementation that you can download from the unixODBC website http www unixODBC org driver manager which is the driver manager implementation from HP and includes a set of APIs which internally makes multiple ODBC API calls Windows e Microsoft ODBC Driver Manager version 3 5 1 or later which manages ODBC requests to ODBC drivers e Microsoft ODBC Data Source Administrator Server components The following server components are required on the NonStop system database release 3 2 1 e MXCS Driver managers The HP UX or Linux a
77. 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 000 987654321 timestamp 2 or time 2 0 98 87654321 timestamp 2 or time 2 0 08 7654321 timestamp 2 or time 2 0 00 654321 timestamp 2 or time 2 0 00 54321 timestamp 2 or time 2 0 00 4321 timestamp 2 or time 2 0 00 987654321 timestamp 1 or time 1 0 9 87654321 timestamp 1 or time 1 0 0 7654321 timestamp 1 or time 1 0 0 654321 timestamp 1 or time 1 0 0 54321 timestamp 1 or time 1 0 0 432 timestamp 1 or time 1 0 0 Fraction value retrieved by the Table Column type application 0 987654 timestamp 6 or time 6 987654 0 087654 timestamp 6 or time 6 87654 0 007654 timestamp 6 or time 6 7654 0 000654 timestamp 6 or time 6 654 0 000054 timestamp 6 or time 6 54 0 000004 timestamp 6 or time 6 4 0 98765 timestamp 5 or time 5 98765 0 08765 timestamp 5 or time 5 8765 0 00765 timestamp 5 or time 5 765 0 00065 timestamp 5 or time 5 65 0 00005 timestamp 5 or time 5 5 0 00000 timestamp 5 or time 5 0 44 Compatibility and considerations Table 13 Values retrieved by the application for SYSTEM DEFAULT setting continued Value in the column of the SQL MX Fraction value retrieved by the Table Column type applicatio
78. top TM ODBC MX Data Source Default DataSource Configuration NonStop MXCS IP Address 15 213 84 37 ODBC MX Login Timeout seconds SYSTEM DEFAULT des open access Connection Timeout seconds SYSTEM DEFAULT Query Timeout seconds TIMEOUT Fetch Buffer Size kbytes SYSTEM_DEFAULT The current configuration is displayed MXCS IP address is specified during the NonStop SQL MX installation Port is the starting port number for the MXCS specified during the NonStop SQL MX installation default is 18650 Login Timeout default is 0 no timeout Connection Timeout default is 60 seconds TIMEOUT Fetch Buffer Size default is 8 kbytes SQL Fraction Precision property has default option set as SYSTEM DEFAULT 6 Cms DataSource configuration in Windows driver 29 NonStop TM ODBC MX Data Source Default DataSource Configuration ODBC MX Schema SCH Provides open access to HP NonStop SQL SQL Interval Insertion SYSTEM DEFAULT the world s most scalable most avail able and most reli SQL Datetime Retrieval SYSTEM DEFAULT able database on HP NonStop servers AutoCommit SERVER_DEFAULT The curent jon is displayed Catalog and Schema are used to qualify NonStop TM SQL MX object names The default values are NONSTOP SYSTEM NSK and PUBLIC ACCESS SCHEMA SQL Interval Insertion property is
79. used during the insertion of Interval data types SQL Datetime Retrieval property is used during retrieval of DATE TIME TIMESTAMP and SQL MP DATETIME related datatypes SQL Datetime Retrieval and SQL Interval insertion have default option set as SYSTEM DEFAULT AutoCommit Property is set to default value SERVER DEFAULT MXODSN and ODBCDSN file formats The following table describes the various sections in the MXODSN and ODBCDSN files Table File sections File Section Description ODBC This section lists the tracing options ODBC Data Sources This section lists the data sources Data source specification Each DSN listed in the ODBC Data Sources section must have this section that describes the DSN DataSourceName Associates a driver with the DSN Table 4 describes the MXODSN and ODBCDSN attributes Table 4 MXODSN and ODBCDSN attributes Fesin emm ODBC TraceStart TraceStart can have two values 0 or 1 If set to O tracing is off If set to 30 Configuring data sources Table 4 MXODSN and ODBCDSN attributes continued File Section Attributes Description 1 tracing is on By default tracing is off TraceFlags TraceFlags indicates the trace level It can have the following values e ERROR to trace failed SQL calls and communication problems WARNING to trace the warnings e CONFIG to trace configuration calls e INFO to trace details about calls made e

Download Pdf Manuals

Related Search

Related Contents

HE225A,B and HE265A,B By-Pass Flow-Through  Smeg SCV36XS drink cooler  Logic Pro 9 Control Surfaces Support  to PDF Manual  Samsung Galaxy S Advance  Instruction manual  2. Allgemeiner Teil  CORTINA DE AIRE  Version micrologiciel : 4.0x FR SÉCURITÉ ET MODE D`EMPLOI  SC54 - Swivel Head Clamp Meter  

Copyright © All rights reserved. Failed to retrieve file