HP Neoview ODBC Drivers Manual HP Part Number: 544580-001 Published: September 2007 Edition: HP Neoview Release 2.
© Copyright 2007 Hewlett-Packard Development Company, L.P. Legal Notice Confidential computer software. Valid license from HP required for possession, use or copying. Consistent with FAR 12.211 and 12.212, Commercial Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are licensed to the U.S. Government under vendor’s standard commercial license. The information contained herein is subject to change without notice.
Table of Contents About This Document.........................................................................................................9 Intended Audience.................................................................................................................................9 New and Changed Information in This Edition.....................................................................................9 Document Organization.....................................................................
API Functions..................................................................................................................................27 Data Types.......................................................................................................................................27 Restricted ODBC API Functions......................................................................................................28 SQLBindParameter....................................................................
Maximum Log File Size...................................................................................................................47 5 HP Neoview ODBC Drivers Conformance................................................................49 ODBC API Functions............................................................................................................................49 SQL Conformance Level......................................................................................................
List of Tables 1-1 2-1 5-1 5-2 5-3 5-4 5-5 5-6 5-7 5-8 5-9 5-10 5-11 5-12 5-13 5-14 5-15 5-16 5-17 Release Considerations and Restrictions for Windows Driver.....................................................18 Release Considerations and Restrictions for Linux , HP-UX, and IBM AIX Drivers....................25 Connecting to a Data Source.........................................................................................................49 Obtaining Information About a Driver and Data Source..........
About This Document This manual describes how to install and configure HP Neoview ODBC drivers for Microsoft Windows, HP-UX, Linux, and IBM AIX®. The drivers enable applications developed for the Microsoft Open Database Connectivity (ODBC) application programming interface (API) to access the HP Neoview database.
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 Italic Letters Italic letters, regardless of font, indicate variable items that you supply. Items not enclosed in brackets are required.
| 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}...
Neoview Customer Library • Administration Neoview Database Administrator’s Guide Information about how to load and manage the Neoview database by using the Neoview DB Admin and other tools. Neoview DB Admin Online Help Context-sensitive help topics that describe how to use the HP Neoview DB Admin management interface. Neoview Guide to Stored Procedures in Java Information about how to use stored procedures that are written in Java within a Neoview database.
Publishing History Part Number Product Version Publication Date 544580–001 Neoview Release 2.2 August 2007 543779–001 Neoview Release 2.0 March 2007 543673–001 Neoview Release 1.2 November 2006 542729–002 Neoview Release 1.1 August 2006 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: pubs.comments@hp.
1 HP Neoview ODBC Driver Overview for Windows Open Database Connectivity (ODBC) is a widely accepted application programming interface (API) for database access. It is based on the Call-Level Interface (CLI) specifications from X/Open and ISO/IEC for database APIs and uses Structured Query Language (SQL) as its database access language.
Client Data Sources Client data sources reside on a client workstation. Each client data source is a logical name that identifies the attribute values used to connect to NDCS and defines some local behavior for the connection. Use the Microsoft ODBC Administrator to add, configure, and remove the HP Neoview ODBC driver data sources. NOTE: If an ODBC client tries to connect to a server data source that is stopped, NDCS rejects the connection.
This is a read-only connection attribute that returns either SQL_CD_TRUE or SQL_CD_FALSE. The value SQL_CD_TRUE means that the connection has been lost, while the value SQL_CD_FALSE means that the connection is still alive. A driver must implement this option efficiently, or it will impair the connection pooling performance. Specifically, a call to get this connection attribute should not cause a round trip to the server. Instead, a driver should just return the last known state of the connection.
Using the Microsoft OBDC Administrator at a client workstation, you can configure the default schema name used to qualify the table names in queries. The object name format is as follows: schema.object-name ANSI names have a maximum of 128 characters for each part of the name (not including the dots). Ensure the catalog name is not qualified while defining your schema. Password Expiration NDCS is configured to allow the driver to display password expiration warnings.
Table 1-1 Release Considerations and Restrictions for Windows Driver (continued) SQLCancel Function In an ODBC 2.0 application, the HP Neoview ODBC driver implements the SQLCancel function as an SQLFreeStmt with the SQL_CLOSE option. If the query is running, HP Neoview ODBC driver tries to stop the server. If the attempt is successful, the client application receives an error and must reconnect.In an ODBC 3.
• • • • MsgTime (SQL_DOUBLE): An estimate of the number of seconds it takes for the messaging for this query. The estimate includes the time for the number of local and remote messages and the amount of data sent. IdleTime (SQL_DOUBLE): An estimate of the maximum number of seconds to wait for an event to happen for this query. The estimate includes the amount of time to open a table or start an ESP process. TotalTime (SQL_DOUBLE): Estimated cost associated to execute the query.
0.0980000013441895, 0.1023000010183761, 129.0 1 row fetched from 7 columns. Unsupported ODBC API Functions and Data Types API Functions This release of HP Neoview ODBC driver does not support the following ODBC 3.51 API functions: • • • SQLBulkOperations SQLFetchScroll - supported for SQL_FETCH_NEXT attribute only SQLSetScrollOption For descriptions of the ODBC API functions, see “ODBC API Functions”.
2 HP Neoview ODBC Drivers Overview for Linux, HP-UX, and IBM AIX® Open Database Connectivity (ODBC) is a widely accepted application programming interface (API) for database access. It is based on the Call-Level Interface (CLI) specifications from X/Open and ISO/IEC for database APIs and uses Structured Query Language (SQL) as its database access language.
Driver components can be installed on any workstation running Linux, HP-UX, or IBM AIX®. These are the client components: MXODSN file Text file that contains client data sources. Sample program Run the sample program to verify a connection for HP Neoview ODBC Linux, HP-UX (32 and 64–bit), and IBM AIX® drivers. HP Neoview ODBC driver Is 3.0 compliant and implements a subset of the ODBC 3.51 APIs. Enables an ODBC client application to access the Neoview database.
ANSI names have a maximum of 128 characters for each part of the name (not including the dots). Ensure the catalog name is not qualified while defining your schema. Password Expiration NDCS is configured to allow the driver to display password expiration warnings. You can change your password during the current session in one of two ways: • Provide the old and new passwords in a dialog box that displays with the expiration warning. If you enter an invalid password, the connection is terminated.
Traces You can trace activity for the ODBC client. The following data is collected: • • • Entry and exit calls to the HP Neoview ODBC drivers Contents of the buffers, except password buffers Activity between the HP Neoview ODBC drivers and the transport layer Using the Driver With a Third Party Driver Manager For questions related to your third party driver manager, refer to your driver manager's documentation. You must configure the following data source attributes.
Example of INFOSTATS Suppose an application prepares a SELECT statement: “SELECT * FROM T1”. Use SQLGetCursorName to find the statement name. In this example, the returned statement name is SQL_CUR_3. To get statistics for “SELECT * FROM T1”, the application must allocate another statment handle and issue SQLExecDirect on INFOSTATS SQL_CUR_3. The statistics are returned as a result set. Use SQLFetch to retrieve the result.
Restricted ODBC API Functions SQLBindParameter The SQLBindParameter function has these restrictions: • • SQL_DEFAULT_PARAM is not supported for the pcbValue argument. The SQL_LEN_DATA_AT_EXEC(length) macro is not supported for the pcbValue argument. SQLPutData The SQLPutData function 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 used.
3 Installing the HP Neoview ODBC Drivers This section contains information on how to install the HP Neoview ODBC Drivers on Windows, Linux, HP-UX, and IBM AIX®.
If a compatible version is not installed, you can obtain the software to download from the HP Software Depot at: www.software.hp.com (search for “Neoview”). Considerations for HP Neoview ODBC Clients Connecting to Mixed-Version Neoview Platforms On the client platform, you can install and run a Release 2.2 HP Neoview ODBC driver concurrently with a Release 2.
NOTE: To install the driver, you must be logged on with a user ID that has administrative privileges. Download and Run the Client Installation File 1. 2. 3. Close all other applications running on the client workstation. Create a temporary client installation folder on the client workstation (for example: C:\ODBCTEMP). From the client workstation, start a browser and navigate to the download site: www.software.hp.com 4. 5. 6. 7. Search for “Neoview ODBC” in the Software Depot.
3. From the client workstation, start a browser and navigate to the download site: www.software.hp.com 4. 5. 6. 7. Search for “Neoview ODBC” in the Software Depot. Click the HP Neoview ODBC Windows Driver link that appears in the search results. Follow the instructions to download the HP Neoview ODBC Windows driver. Click HPODBC, and save the file to the temporary folder. Start the InstallShield Wizard and Reinstall the Driver 1. 2. 3. Double-click the HPODBC_vnn.
Select Repair Option When the InstallShield detects a reinstallation, it displays a screen as follows: Screen Button Description Modify, Repair or Remove the program Modify Repair Remove Adds and removes ODBC components. Click Repair to reinstall the ODBC files and repair your system. Uninstalls the client program Finish Finish Performs installation cleanup and exits the installation. Reboot Required Yes/No This screen displays only if rebooting your system is required.
ODBC Tools (in system directory) gtrtst32.dll odbc.cnt odbc.hlp odbcte32.exe OdbcTest.cnt Odbctest.hlp Self Registering system files (in system directory) mfc42.dll HP Neoview ODBC Driver Component: Driver files (in system directory) hp_oadm0200.dll hp_odbc0200.dll Driver support files (in system directory) hp_odbctrace.dll hp_odbcDrvMsg_intl0200.dll Transport files (in system directory) hp_tcpipv40200.dll hp_tcpipv60200.dll ODBC documentation (in selected directory) odbcmxds.chm readme.
Files contained in the distribution file: Linux: libhpodbc_1.so.x.0.0 (The value of x increases with succeeding major releases.) libhpodbc_drvr_1.so.x.0.0 (The value of x increases with succeeding major releases.) MXODSN MD5SUM sql.h sqlext.h sqltypes.h install.sh connect_test.cpp x86_64 Linux: libhpodbc_l64.so.x.0.0 (The value of x increases with succeeding major releases.) libhpodbc_drvr_l64.so.x.0.0 (The value of x increases with succeeding major releases.) MXODSN MD5SUM sql.h sqlext.h sqltypes.
IBM AIX®: libhpodbc.a.x.0.0 (The value of x increases with succeeding major releases.) libhpodbc_drvr.a.x.0.0 (The value of x increases with succeeding major releases.) MXODSN MD5SUM sql.h sqlext.h sqltypes.h install.sh connect_test.
HP-UX(IA-64) 64–bit: tar -xvf hpodbc_vvv_HPUX_I_64_pkg.tar HP-UX(PA-RISC): tar -xvf hpodbc_vvv_HPUX_P_pkg.tar IBM AIX®:tar -xvf hpodbc_vvv_AIX_pkg.tar 9. Install the product: cd PkgTmp ./install.sh NOTE: Except for the sample file, the install.sh script saves a copy (.SAV) of your previous installation files, if they exist. 10. Accept the terms of the license agreement. 11. Enter a directory for the library file, or press Enter to use the default directory (/usr/lib). 12.
gcc connect_test.cpp -L/usr/lib -I/usr/include/hpodbc \ -lhpodbc -o connect_test NOTE: The Linux driver is a 32–bit driver. If you are using an x86–64 machine, you need to explicitly compile your application as a 32–bit application. For example: gcc connect_test.cpp -m32 -L/usr/lib -I/usr/include/hpodbc \ -lhpodbc -o connect_test • On x86–64 Linux: gcc connect_test.cpp -L/usr/lib -I/usr/include/hpodbc \ -lhpodbc -o connect_test • On HP-UX(IA-64): aCC -AA connect_test.
1. Check the integrity of the library: • Verify MD5SUM md5sum library Compare the output with the contents of /etc/hpodbc/MD5SUM. • Find the version of the driver: — On Linux: nm libhpodbc.so | grep HPODBC or nm libhpodbc_drvr.so | grep HPODBC — On x86–64 Linux: nm libhpodbc64.so | grep HPODBC or nm libhpodbc_drvr64.so | grep HPODBC — On HP-UX(IA-64) and HP-UX(PA-RISC): nm libhpodbc.sl | grep HPODBC or nm libhpodbc_drvr.sl | grep HPODBC — On HP-UX(IA-64) 64–bit: nm libhpodbc64.i.
Debugging gdb is used to debug application on Linux and HP systems. Before starting gdb, make sure that the executable has been linked in with the right version of the library (use ldd). On Linux, if your test program uses a non default library, set the environment variable LD_LIBRARY_PATH to the directory where the library resides. For example: gdb program (gdb) list (gdb) break (break point is a line number, function name, etc.
2. Retrieve the version string: • For Linux: nm libhpodbc.so | grep HPODBC or nm libhpodbc_drvr.so | grep HPODBC • For x86_64 Linux: nm libhpodbc64.so | grep HPODBC or nm libhpodbc_drvr64.so | grep HPODBC • For HP-UX(IA-64) and HP-UX(PA-RISC): nm libhpodbc.sl | grep HPODBC or nm libhpodbc_drvr.sl | grep HPODBC • For HP-UX (IA-64) 64 bit: nm libhpodbc64.sl | grep HPODBC or nm libhpodbc_drvr64.sl | grep HPODBC • For IBM AIX® nm libhpodbc.a | grep HPODBC or nm libhpodbc_drvr.
4 Configuring Client Data Sources If you are configuring client data sources on Linux, HP-UX, and IBM AIX® see “Configuring the Client Data Source on Linux, HP-UX, and IBM AIX®”. To configure client data sources on Windows, use the Microsoft ODBC Administrator to add, configure, and remove HP Neoview ODBC Driver data sources. For information about supported versions of Windows, see “Driver Components”. NOTE: You can install and run HP Neoview ODBC Driver version 2.0.
Reconfiguring ODBC Client Data Sources 1. Start the Microsoft ODBC Administrator: Start> Programs> HP ODBC 2.0> MS ODBC Administrator 2. 3. 4. To display the list of data source names, click either the System DSN tab or the User DSN tab. (The tab on which a data source is listed depends on how the data source was originally assigned.) Select the data source name you want to reconfigure, and then click Configure.
3. Select the Custom Trace DLL (the trace file name) you want to use. Use the Select DLL button to locate the correct file. If you select the ODBC standard trace DLL (odbctrac.dll in the Windows\system32 directory), only the driver manager is traced; additional trace options you select are ignored. If you select the ODBC trace DLL (hp_odbctrace.dll in the Windows\system32 directory), all trace options you select for the client data source are operational. 4. 5. 6.
4. Specify the ODBC version. Call SQLSetEnvAttr and set the SQL_ATTR_ODBC_VERSION attribute to the application version (for example, SQL_OV_ODBC3). 5. Allocate a connection handle. Call SQLAllocHandle with the HandleType argument set to SQL_HANDLE_DBC, and the InputHandle argument set to the environment handle allocated for connection pooling. 6. Make the connection. Call SQLConnect or SQLDriverConnect.
Setting Trace Level You can set the trace level by setting traceFlags. Each level of tracing includes successively more tracing information. For example, if you choose the CONFIG level, you get messages from ERROR, WARNING, and additional messages for CONFIG. The values are listed in the following table. Name Description ERROR Indicates some failed SQL calls and communications problems. WARNING Triggered by anything that generates a diagnostic record. CONFIG Provides details of configuration calls.
5 HP Neoview ODBC Drivers Conformance This section contains tables that describe the HP Neoview ODBC driver conformance to standard ODBC API functions and SQL conformance levels.
Table 5-2 Obtaining Information About a Driver and Data Source Function ODBC Version Supported by HP ODBC Description SQLDataSources 1.0 Yes Returns the list of available data sources. Implemented by the Driver Manager. SQLDrivers 2.0 Yes Returns the list of installed drivers and their attributes. Implemented by the Driver Manager. SQLGetInfo 1.0 Yes Returns information about a specific driver and data source. SQLGetFunctions 1.0 Yes Returns supported driver functions. SQLGetTypeInfo 1.
Table 5-5 Preparing SQL Requests Function ODBC Version Supported by HP ODBC Description SQLPrepare 1.0 Yes Prepares an SQL statement for later execution. SQLBindParameter 2.0 Yes Assigns storage for a parameter in an SQL statement. SQLGetCursorName 1.0 Yes Returns the cursor name associated with a statement handle. SQLSetCursorName 1.0 Yes Specifies a cursor name. SQLSetScrollOption 1.0 No Sets the option that controls cursor behavior.
Table 5-7 Retrieving Results and Information About Results (continued) Function ODBC Version Supported by HP ODBC Description SQLBindCol 1.0 Yes Assigns storage for a result column and specifies the data type. SQLFetch 1.0 Yes Returns multiple result rows. SQLFetchScroll 3.0 Yes Supported for SQL_FETCH_NEXT attribute only. Returns scrollable result rows. SQLGetData 1.0 Yes Returns part or all of one column of one row of a result set (useful for long data values). SQLExtendedFetch 1.
Table 5-8 Obtaining Information About a Data Source’s System Tables (continued) Function ODBC Version Supported by HP ODBC Description SQLPrimaryKeys 1.0 Yes Returns a list of column names that make up the primary key for a table.Note: See the discussion of SQLPrimaryKeys in SQLPrimaryKeys Function in the Release Considerations table in Chapter 1 “ HP Neoview ODBC Driver Overview for Windows” SQLProcedureColumns 1.
Table 5-9 Terminating a Statement Function ODBC Version Supported by HP ODBC Description SQLFreeStmt 1.0 Yes Ends statement processing, discards pending results; can also free all resources associated with the statement handle. SQLCancel 1.0 Yes Cancels an SQL statement.Note: See the considerations under the . SQLCloseCursor 3.0 Yes Closes a cursor that has been opened on a statement handle. SQLCancel 1.0 Yes Closes a cursor that has been opened on a statement handle.
Table 5-11 Numeric Functions (continued) ODBC Function SQL Equivalent Function COSH COSH COT Not supported in this release. DEGREES DEGREES EXP EXP FLOOR FLOOR LOG LOG LOG10 LOG10 MOD MOD PI PI POWER POWER RADIANS RADIANS RAND RAND ROUND Not supported in this release. SIGN SIGN SIN SIN SINH SINH SQRT SQRT TAN TAN TANH TANH TRUNCATE Not supported in this release.
Table 5-12 String Functions (continued) ODBC Function SQL Equivalent Function RTRIM TRIM...TRAILING SOUNDEX Not supported SPACE SPACE SUBSTRING SUBSTRING UCASE UPPER | UPSHIFT ODBC string functions in queries can return unexpected results for fixed-length (CHAR) column names, because SQLpads a fixed-length string with blanks up to the length of the definition. Therefore, the results from some ODBC string functions can include trailing blanks at the end of the string.
CONVERT Function The HP Neoview ODBC driver uses the SQL CAST function to support the ODBC CONVERT function. The ODBC CONVERT function has the following format: { fn CONVERT( value_exp, data_type ) } The SQL CAST has this format: CAST( {value_exp | NULL } AS data_type ) SQL translates the CONVERT syntax to the CAST syntax, converting the data type argument to its equivalent SQL value.
Table 5-15 ODBC Data Types (continued) ODBC Data Type SQL Data Type Supported by HP ODBC 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 MINUTE INTERVAL DAY(p) TO MINUTE Yes INTERVAL DAY(p) TO SECOND INTERVAL DAY(p) TO SECOND Yes INTERVAL HOUR(p) TO MINUTE INTERVAL HOUR(p) TO MINUTE Yes INTERVAL HOUR(p) TO SECOND(q) INTERVAL HOUR(p) TO SECO
Table 5-17 Microsoft Escape Clauses (continued) Microsoft Escape Clause SQL Equivalent { escape 'escape-character' } Not supported in the current release. { [?=]call procedure-name... } Not supported in the current release. 1. ODBC syntax does not include nested joins, while SQL does. HP Neoview ODBC extends the Microsoft syntax for an outer join. 2. Functions are controlled by SQLGetInfo. Only SQL native functions are supported.
6 HP Neoview ODBC Drivers Messages HP Neoview ODBC Drivers Error Codes HP Neoview ODBC Client Messages SQLSTATE Message 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. 01S02 Option value changed. 07001 Wrong number of parameters. 07003 Dynamic SQL error. Cursor specification cannot be executed. 07005 Dynamic SQL error.
SQLSTATE Message 21S01 Cardinality violation; insertion value list does not match column list. 21S02 Cardinality violation; parameter list does not match column list. 22001 String data right truncation. 22003 Numeric value out of range. 22005 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.
SQLSTATE Message S0021 Invalid column name; column already exists. S0022 Invalid column name; column not found. SQLSTATE Message S1000 General error. S1000 General error: Ongoing transaction has been committed. S1000 General error. Failed since resource governing policy is hit. S1000 The stored procedure required to complete this operation could not be found on the server (they were supplied with the HP Neoview ODBC driver setup disk for the SQL Server driver). Contact your service provider.
SQLSTATE Message S1098 Scope type out of range. S1099 Nullable type out of range. SQLSTATE Message S1100 Uniqueness option type out of range. S1101 Accuracy option type out of range. S1102 Table type out of range. S1103 Direction option out of range. S1105 Invalid parameter type or parameter type not supported. S1106 Fetch type out of range. S1107 Row value out of range. S1108 Concurrency option out of range. S1109 Invalid cursor position; no keyset defined.
Index A ABS function, 54 ACOS function, 54 API functions, ODBC supported, 49 API reference, 34 architecture;HP Neoview ODBC driver components, description of, 15, 24 ASCII function, 55 ASIN function, 54 association server, functions of, 16, 24 ATAN function, 54 ATAN2 function, 54 B BIGINT data type, 57 BINARY data type, 57 BIT data type, 57 C CAST function, 57 catalog restrictions, 18, 25 CEILING function, 54 CHAR data type, 57 function, 55 client connecting to data sources, 16, 24 data sources for, 16, 2
components of, 15, 23, 24 Driver manager, Microsoft ODBC, 30 See Microsoft ODBC driver manager, 15, 24 driver, getting the version information, 40 driver-platform incompatibility, 29 E error codes, client, 61 escape clauses, support for, 58 EXP function, 55 external specification, ODBC, 34, 41 EXTRACT function, 56 F files installed, client interface, 33 FLOAT data type, 57 FLOOR function, 55 functions descriptor fields, 50 driver attribute, 50 numeric, 55 retrieving results and information about, 51 strin
ODBC external specification, 34, 41 ODBC, driver description, 30 P password expiration, 18, 25 performance improvements using connection pooling, 16 PI function, 55 pooling connections, 16 POWER function, 55 Q QUARTER function, 56 R RADIANS function, 55 RAND function, 55 REAL data type, 57 Reconfiguring client data sources, 44 reinstalling HP Neoview ODBC driver for Linux , HP-UX, and IBM AIX®, 34 reinstalling HP Neoview ODBC driver for Windows, 31 release restrictions, 25 release restrictions for Window
SQLSpecialColumns function, 53 SQLStatistics function, 19, 25, 53 SQLTablePrivileges function, 53 SQLTables function, 19, 25, 53 SQRT function, 55 statements, data source functions for terminating, 54 stored procedures, 59 string functions, 55 SUBSTRING function, 56 system functions supported, 56 system tables, functions for obtaining information about, 52 T tables, system;functions data source system table information, 52 TAN function, 55 TANH function, 55 TDM_Default_DataSource, 15, 24, 32, 43 terminatin
