HP Neoview ODBC Drivers Manual HP Part Number: 546130-001 Published: March 2009 Edition: HP Neoview Release 2.
© Copyright 2009 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.........................................................................................................7 Intended Audience.................................................................................................................................7 New and Changed Information in This Edition.....................................................................................7 Document Organization.....................................................................
Connections...........................................................................................................................................27 Connecting a Client to a Data Source..............................................................................................27 Associating a Connection with a WMS Service...............................................................................27 Object Naming and Mapping............................................................................
SQL Conformance Level.......................................................................................................................50 SQL Scalar Functions.......................................................................................................................50 CONVERT Function...................................................................................................................52 ODBC Data Types..............................................................................
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 6 Release Considerations and Restrictions for Windows Driver.....................................................17 Release Considerations and Restrictions for HP Neoview ODBC Drivers for UNIX...................28 Connecting to a Data Source.........................................................................................................45 Obtaining Information About a Driver and Data Source..........
About This Document This manual describes how to configure HP Neoview ODBC drivers for Microsoft Windows and the HP Neoview drivers for UNIX (HP-UX, Linux, IBM AIX®, and Sun Solaris for SPARC®). The drivers enable applications developed for the Microsoft Open Database Connectivity (ODBC) application programming interface (API) to access the HP Neoview database. Installation instructions are included in the README for the HP Neoview ODBC Driver for Windows and README for the HP Neoview ODBC Drivers for UNIX.
In previous releases, this manual contained a chapter related to installing the ODBC drivers. That information is now contained in the README for the HP Neoview ODBC Driver for Windows and README for the HP Neoview ODBC Drivers for UNIX, both available at docs.hp.com. 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.
INTERVAL { start-field TO end-field } { single-field } INTERVAL { start-field TO end-field | single-field } | 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}...
Related Documentation This manual is part of the HP Neoview customer library. Neoview Customer Library The manuals in the Neoview customer library are listed here for your convenience.
Neoview Workload Management Information about using Neoview Workload Management Services (WMS) to Services Guide manage workload and resources on a Neoview data warehousing platform.
Include the document title, part number, and any comment, error found, or suggestion for improvement you have concerning this document.
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.
to the Microsoft Developer Network Online Library at http://msdn.microsoft.com/en-us/library/ ms716287.aspx. For installation instructions, see the README for the HP Neoview ODBC Driver for Windows, located at www.software.hp.com. Data Sources To configure ODBC client access to the Neoview database, the HP Neoview Database Connectivity Service (NDCS) requires data source definitions on both the Neoview database and client workstation.
NOTE: Connection pooling can be used by an ODBC application exhibiting ODBC 2.x behavior, as long as the application can call SQLSetEnvAttr. When using connection pooling, the application must not execute SQL statements that change the database or the context of the database, such as changing the database name, which changes the catalog used by a data source. The connection pool is maintained by the Driver Manager.
The actual connection to be used is not determined by the Driver Manager until SQLConnect or SQLDriverConnect is called. 4. Calls SQLConnect or SQLDriverConnect to make the connection. The Driver Manager uses the connection options in the call to SQLConnect (or the connection keywords in the call to SQLDriverConnect) and the connection attributes set after connection allocation to determine which connection in the pool should be used.
• 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. You can also cancel the password change and just continue with the current session.
Table 1-1 Release Considerations and Restrictions for Windows Driver (continued) SQL_QUERY_TIMEOUT Option An ODBC application can call SQLSetStmtOption with the SQL_QUERY_TIMEOUT option to specify the number of seconds to wait for a query to execute. The SQL_QUERY_TIMEOUT option protects the NDCS server and ODBC client from long running queries. If a query exceeds the specified time before the data source returns the result set, the HP Neoview ODBC driver returns HYT00 (Timeout expired) to the ODBC client.
• • If SQL_ATTR_IGNORE_CANCEL is set to "1", SQLCancel will not attempt to stop the server and will return SQL_ERROR with SQLSTATE HY018 "Server declined cancel request." * If SQL_ATTR_IGNORE_CANCEL is set to "0" or is not set at all, SQLCancel will attempt to stop the server if the query is still running (default value). Traces You can trace activity for the ODBC client. The following data is collected: • • • Entry and exit calls from Microsoft ODBC driver manager to the HP Neoview ODBC driver.
NOTE: If you reuse the same job ID over time, all work done under that ID is assumed to be related to the same job. Such reuse over a long period of time can result in undefined statistical aggregation, because some aged work statistics might have been archived and removed from the active statistics repository. Example In the following example, an application allocates environment and connection handles.
To use this feature, include the Neoview-specific header file called hpsqlext.h in your application, and specify the connection attribute SQL_ATTR_APPLNAME before the connection is established. Once the connection is established, you may not change the attribute value. Use the ODBC API SQLSetConnectAttr to set a connection attribute. Use the ODBC API SQLGetConnectAttr to retrieve the current value.
INFOSTATS Command INFOSTATS is a pass-through command that collects statistics for a prepared statement. Statistics are returned to the ODBC application as a result set as soon as the PREPARE is finished. The result set has these columns: • Query ID (SQL_CHAR) • CPUTime (SQL_DOUBLE): An estimate of the number of seconds of processor time it might take to execute the instructions for this query. A value of 1.0 is 1 second.
In this example, INFOSTATS is issued directly on the SQL statement. Again, use SQLFetch to retrieve the result: SQLExecDirect: In: Return: hstmt = 0x003B1968 szSqlStr = "infostats "select * from t1"", cbSqlStr = -3 SQL_SUCCESS=0 Get Data All: "QueryID", "CPUTime", "IOTime", "MsgTime", "IdleTime", "TotalTime" "Cardinality" "MXID115000897212014649972695973_60_STMT_INFOSTATS ", 0.0986710164301648, 0.1023000010183761, 129.0 0.0980000013441895, 0.0980000013441895, 0.1023000010183761, 129.
2 HP Neoview ODBC Drivers Overview for UNIX 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. ODBC is designed for maximum interoperability — that is, the ability of a single application to access different database management systems (DBMSs) with the same source code.
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.
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. Edit your data source configuration file (MXODSN) to add a new data source or change the data source configuration. NOTE: If an ODBC client tries to connect to a server data source that is stopped, NDCS rejects the connection.
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. You can also cancel the password change and just continue with the current session.
Table 2-1 Release Considerations and Restrictions for HP Neoview ODBC Drivers for UNIX (continued) SQLTables Function Returns all schemas defined in the SQL metadata. When SchemaName is: Returns all object types supported by SQL. SQL_ALL_SCHEMAS When TableType is: SQL_ALL_TABLE_TYPES SQL Table Names SQL Schema Names The ODBC driver uses SQL_ATTR_METADATA_ID to determine whether to upshift a table name. This applies for catalog API to SQLTables, SQLPrimaryKey, and SQLStatistics.
DataLang = 0 FetchBufferSize = SYSTEM_DEFAULT Server = TCP:arc0101.caclab.cac.cpqcorp.net:5000 SQL_ATTR_CONNECTION_TIMEOUT = SYSTEM_DEFAULT SQL_LOGIN_TIMEOUT = SYSTEM_DEFAULT SQL_QUERY_TIMEOUT = NO_TIMEOUT For questions related to your third party driver manager, refer to your driver manager's documentation.
connection attribute SQL_ATTR_SESSIONAME, and processes data. When it has finished processing data, it disconnects from the data source and frees the handles. #include “hpsqlext.
Example The following examples demonstrate the use of SQLSetConnectAttr to specify the application name: SQLRETURN rc; SQLHDBC hdbc; char *value = “MSI”; . . . rc = SQLSetConnectAttr(hdbc, SQL_ATTR_APPLNAME, value, strlen(value)); . . . #include SQLHENV SQLHDBC SQLHSTMT SQLRETURN “hpsqlext.
• • • 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. Cardinality (SQL_DOUBLE): Estimated number of rows that will be returned. INFOSTATS {stmt-name | cursor-name | sql-stmt } A pass-through command that collect statistics for a prepared statement.
Unsupported ODBC API Functions and Data Types API Functions This release of the HP Neoview ODBC drivers does not support the following ODBC 3.
3 Accessing Neoview SQL Data From Microsoft ODBC .NET Data Provider The Microsoft .NET framework is a Windows component that supports building and running applications and XML Web services. For more information about Microsoft .NET framework, see http://msdn.microsoft.com/en-us/netframework/default.aspx. Neoview Release 2.4 provides the ability to use the ODBC .NET Data Provider collection of classes.
Class Name Description OdbcPermission Enables the .NET Framework Data Provider for ODBC to help make sure that a user has a security level sufficient to access an ODBC data source. OdbcPermissionAttribute Associates a security action with a custom security attribute. OdbcRowUpdateEventArgs Provides data for the RowUpdated event. OdbcRowUpdatingEventArgs Provides data for the RowUpdating event. OdbcTransaction Represents an SQL transaction to be made at a data source. ODBC .
reader.Close() End Using End Sub ODBC .NET Environment Setup To set up the ODBC .NET environment, you must download and install the .NET framework SDK, version 3.5 or later from the Microsoft download website: http://msdn.microsoft.com/en-us/ netframework/default.aspx If you have not already done so, you must download and install Microsoft Visual Studio 2005 or newer from the Microsoft download website: http://msdn.microsoft.com/en-us/vs2005/ aa718332.
Sample Application Code Using ODBC .NET Data Provider To use the ODBC .NET Data Provider, the application must import the System.Data.Odbc namespace. For example, • Visual Basic imports System.Data.Odbc • C++ uses namespace System::Data • C# uses System.Data.Odbc The following source code samples demonstrate setting up (tear down) the connection to the data source. • Visual Basic Dim connString As String = "DSN=neo_datasrc;UID=userid,PWD=passWd" Dim myConn As New OdbcConnection(connString) ... myConn.
4 Configuring Client Data Sources If you are configuring client data sources on Linux, HP-UX, IBM AIX®, and Sun Solaris see “Configuring the Client Data Source on Linux, HP-UX, IBM AIX®, and Sun Solaris”. 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”.
3. 4. Select the data source name you want to reconfigure, and then click Configure. Each tab in the configuration view displays different fields in the data source configuration. Make the required changes. For more information about a tab, click Help. Configuring ODBC Client Data Sources for Update Operations To perform “update . . .
4. 5. 6. Select the Log file Path you want to use. Use the Browse button to locate the correct log file. Click Start Tracing Now. Click Apply then OK. Stop Tracing on a Client Data Source 1. Bring up the Microsoft ODBC Administrator: Start> Programs> HP ODBC 2.0> MS ODBC Administrator 2. 3. 4. Select the Tracing tab. Click Stop Tracing Now. Click Apply then OK. Enabling Connection Pooling The connection pooling mechanism is enabled by the client ODBC application.
6. Make the connection. Call SQLConnect or SQLDriverConnect. The driver manager uses the connection options and the SQL_ATTR_CP_MATCH attribute to determine which connection in the pool to assign to the application. 7. Set connection attributes.
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. INFO Provides details of what calls are made and other important markers. DEBUG Shows the detailed contents of many 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 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. See the “Release Considerations and Restrictions” (page 17) SQLProcedureColumns 1.0 Yes Returns the list of input and output parameters, as well as the columns that make up the result set for the specified procedures. SQLProcedures 1.
Table 5-9 Terminating a Statement (continued) Function ODBC Version Supported by HP ODBC Description SQLCancel 1.0 Yes Closes a cursor that has been opened on a statement handle. See the “SQLCancel Function” (page 18) SQLEndTran 3.0 Yes Commits or rolls back a transaction. Table 5-10 Terminating a Connection Function ODBC Version Supported by HP ODBC Description and Notes SQLDisconnect 1.0 Yes Closes the connection. SQLFreeHandle 3.
Table 5-11 Numeric Functions (continued) ODBC Function SQL Equivalent Function 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 ODBC Function SQL Equivalent Function ASCII ASCII CHAR CHAR CONCAT CONCAT DIFFERENCE Not supported INSERT INSERT LCASE LOWER LEFT SUBSTRING LENGTH Supported, but no equivalent SQL function.
Table 5-13 Time and Date Functions ODBC Function SQL Equivalent Function CURDATE, CURRENT_DATE CURRENT_DATE CURTIME, CURRENT_TIME CURRENT_TIME DAY DAY EXTRACT EXTRACT DAYNAME Not supported DAYOFMONTH Not supported DAYOFWEEK DAYOFWEEK DAYOFYEAR DAYOFYEAR HOUR HOUR MINUTE MINUTE MONTH MONTH MONTHNAME MONTHNAME NOW Supported, but no equivalent SQL function.
Table 5-15 ODBC Data Types ODBC Data Type SQL Data Type Supported by HP ODBC CHAR(n) CHAR(n) Yes VARCHAR(n) VARCHAR(n) * Yes LONGVARCHAR VARCHAR(n) Yes DECIMAL(p,s) DECIMAL(p,s) Yes NUMERIC(p,s)** NUMERIC(p,s) Yes SMALLINT SMALLINT Yes INTEGER INTEGER Yes REAL REAL Yes FLOAT(p) FLOAT(p) Yes DOUBLE PRECISION DOUBLE PRECISION Yes BIT Not supported No TINYINT Not supported No TINYINT UNSIGNED Not supported No BIGINT LARGEINT Yes BINARY(n) Not supported No VARBINA
Table 5-15 ODBC Data Types (continued) ODBC Data Type SQL Data Type Supported by HP ODBC INTERVAL MINUTE(p) TO SECOND(q) INTERVAL MINUTE(p) TO SECOND(q) Yes * The VARCHAR length can be up to 4059 for key-sequenced files or 4070 for entry-sequenced files. ** The numeric precision can be up to 128.
Table 5-17 Microsoft Escape Clauses (continued) Microsoft Escape Clause SQL Equivalent { [?=]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. Stored Procedures The HP Neoview ODBC driver supports stored procedures, including results sets.
6 HP Neoview ODBC Drivers Messages HP Neoview ODBC Drivers Error Codes Timestamps for all driver error messages appear immediately following the SQL error text. HP Neoview ODBC Client Messages When an ODBC API call returns SQL_ERROR or SQL_SUCCESS_WITH_INFO, an associated SQLSTATE value can be obtained by calling SQLGetDiagRec with the appropriate arguments.
SQLSTATE Message 08S01 Communication link failure. 08001 No more NDCS servers available to connect. SQLSTATE Message 21001 Cardinality violation; insert value list does not match column list. 21002 Cardinality violation; parameter value list does not match column list. 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.
SQLSTATE Message IM001 Driver does not support this function. IM009 Unable to load DLL. SQLSTATE Message S0001 Invalid table name; base table or view already exists. S0002 Invalid table name; table or view not found. S0011 Invalid index name; index already exists. S0012 Invalid index name; index not found. S0021 Invalid column name; column already exists. S0022 Invalid column name; column not found. SQLSTATE Message S1000 General error.
SQLSTATE Message S1094 Invalid scale value. S1095 Function type out of range. S1096 Information type out of range. S1097 Column type out of range. 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.
Index Symbols .NET data provider and Neoview ODBC driver, 36 classes, 35 sample application, 38 .
Documents, related information, 10 DOUBLE PRECISION data type, 53 driver components of, 13, 25, 26 Driver manager, Microsoft ODBC See Microsoft ODBC driver manager, 13, 26 E Environment variable SQL_ATTR_WARNING, 17 error codes, client, 57 escape clauses, support for, 54 EXP function, 50 EXTRACT function, 52 F FLOAT data type, 53 FLOOR function, 50 functions descriptor fields, 46 driver attribute, 46 numeric, 51 retrieving results and information about, 47 string, 51 system, 52 terminating connections, 50
client data sources, 40 ODBC client data sources, 40 REPEAT function, 51 REPLACE function, 51 requests functions for preparing, 47 functions for submitting, 47 restrictions, HP Neoview ODBC driver, 17 results, SQL, functions for, 47 RIGHT function, 51 ROUND function, 51 RTRIM function, 51 S sample program description, 26 scalar functions, support for, 50 schema restrictions, 17, 28 SECOND function, 52 security, HP Neoview ODBC driver, 16, 27 SIGN function, 51 SIN function, 51 SINH function, 51 SMALLINT dat
TIME data type, 53 time functions, 52 timeouts, effect of, 18 TIMESTAMP data type, 53 TIMESTAMPADD function, 52 TIMESTAMPDIFF function, 52 TINYINT data type, 53 TINYINT UNSIGNED data type, 53 trace level, 42 trace on, 42 tracing client data sources overview, 19, 29 tracing client data sources:configuring for, 40 tracing client data sources:starting, 40 tracing client data sources:stopping, 41 TRIM...LEADING function, 51 TRIM...
