SAP DB:ODBC, Manual

Page 1: ...ODBC Manual SAP DB ...

Page 2: ...tribute and or modify this document under the terms of the GNU Free Documentation License Version 1 1 or any later version published by the Free Software Foundation For more information on the GNU Free Documentaton License see http www gnu org copyleft fdl html SEC4 ODBC Manual SAP DB 2 ...

Page 3: ...tion codes table names and individual key words of a programming language when surrounded by body text for example SELECT and INCLUDE Example text Screen output This includes file and directory names and their paths messages source code names of variables and parameters as well as names of installation upgrade and database tools EXAMPLE TEXT Keys on the keyboard for example function keys such as F...

Page 4: ... Microsoft Windows 21 Installation Under Microsoft Windows 21 Creating Data Sources Under Microsoft Windows 23 Options for Data Sources Microsoft Windows 23 SQL Mode Microsoft Windows 24 Isolation Level Microsoft Windows 24 Logging SQL Statements Microsoft Windows 25 Installed Files Microsoft Windows 95 and following 26 SAP DB ODBC Driver Under UNIX Linux 26 Installation Under UNIX Linux 26 Integr...

Page 5: ... instance is performed using TCP IP For a complete description of the ODBC functions and an ODBC syntax reference see the Microsoft homepage http www microsoft com data ODBC in the ODBC Software Development Kit SDK under ODBC Programmer s Reference For general information about the SAP DB database system see the User Manual SAP DB See SAP DB Library and on the SAP DB Homepage at http www sapdb org...

Page 6: ...about the data types defined for the SAP DB database system see the Reference Manual SAP DB 7 4 data type See SAP DB Library section SAP DB data types that are supported by the SAP DB ODBC Driver Character CHAR VARCHAR LONG CHAR BYTE VARCHAR BYTE LONG BYTE Numeric DECIMAL FIXED INTEGER SMALLINT FLOAT REAL DOUBLE PRECISION Date DATE TIME TIMESTAMP Other BOOLEAN CHAR EBCDIC VARCHAR EBCDIC C Data Typ...

Page 7: ...The SAP DB ODBC driver allows the conversion for all supported data types Page 6 See also Microsoft Homepage http www microsoft com data ODBC in the ODBC Software Development Kit SDK under ODBC Programmer s Reference Converting Data from SQL to C Data Types and Converting Data from C to SQL Data Types sections Processing SQL Statements The SAP DB ODBC driver supports the complete ODBC SQL syntax F...

Page 8: ... three types of positionable cursors static dynamic and keyset driven These differ in the extent to which changes to the database data by the current session or the sessions of other users are visible As SAP DB can lock row by row a user can change data in a table even if another user has also opened parts of this table The prerequisite for this is that the rows to be changed are outside the part ...

Page 9: ...ch other parts of this LONG column with the SQLGetData function SQLCancel SQLColAttributes SQLConnect SQLDescribeCol SQLDisconnect SQLError SQLExecDirect SQLExecute SQLFetch SQLFreeConnect SQLFreeEnv SQLFreeStmt SQLGetCursorName Case sensitive maximum length 18 characters Take this into account for example with the statement SELECT FOR UPDATE as identifiers that are not enclosed in quotation marks...

Page 10: ...alue using the attribute SQL_NULL_DATA Otherwise you receive the message szSqlState S1000 SQLCODE 22002 SQLColumns SQLDriverConnect Keyword for the name of the database server SERVERNODE Keyword for the name of the database instance SERVERDB SQLGetConnectAttr Remark Page 11 SQLGetData SQLGetFunctions Remark Page 11 SQLGetInfo Remark Page 14 SQLGetStmtAttr SQLGetTypeInfo You can determine the data ...

Page 11: ..._AUTOCOMMIT_ON SQL_ATTR_CONNECTION_DEAD SQL_ATTR_CURRENT_CATALOG SQL_ ATTR_LOGIN_TIMEOUT pvParam 15 SQL_ATTR_ODBC_CURSORS SQL_ATTR_QUIET_MODE SQL_ ATTR_OPT_TRACE pvParam SQL_OPT_TRACE_OFF 0 SQL_ ATTR_OPT_TRACEFILE The filename set with SQLSetConnectAttr default value SQL LOG SQL_ ATTR_TRANSLATE_LIB SQL_ ATTR_TRANSLATE_OPTION pvParam 0 SQL_ ATTR_TXN_ISOLATION pvParam SQL_TXN_REPEATABLE_READ SQL_ MO...

Page 12: ..._SQLDATASOURCES SQL_API_SQLDESCRIBECOL SQL_API_SQLDESCRIBEPARAM SQL_API_SQLDISCONNECT SQL_API_SQLDRIVERCONNECT SQL_API_SQLDRIVERS SQL_API_SQLENDTRAN SQL_API_SQLERROR SQL_API_SQLEXECDIRECT SQL_API_SQLEXECUTE SQL_API_SQLEXTENDEDFETCH SQL_API_SQLFETCH SQL_API_SQLFETCHSCROLL SQL_API_SQLFOREIGNKEYS SQL_API_SQLFREECONNECT SQL_API_SQLFREEENV SQL_API_SQLFREEHANDLE SQL_API_SQLFREESTMT SQL_API_SQLGETCONNECT...

Page 13: ...PI_SQLPREPARE SQL_API_SQLPRIMARYKEYS SQL_API_SQLPROCEDURECOLUMNS SQL_API_SQLPROCEDURES SQL_API_SQLPUTDATA SQL_API_SQLROWCOUNT SQL_API_SQLSETCONNECTATTR SQL_API_SQLSETCONNECTOPTION SQL_API_SQLSETCURSORNAME SQL_API_SQLSETDESCFIELD SQL_API_SQLSETDESCREC SQL_API_SQLSETENVATTR SQL_API_SQLSETPARAM SQL_API_SQLSETPOS SQL_API_SQLSETSCROLLOPTIONS SQL_API_SQLSETSTMTATTR SQL_API_SQLSETSTMTOPTION SQL_API_SQLSP...

Page 14: ..._SET_COLUMN_DEFAULT SQL_BOOKMARK_PERSISTENCE rgbInfoValue SQL_BP_SCROLL SQL_BP_UPDATE SQL_CATALOG_LOCATION rgbInfoValue 1 SQL_CL_START SQL_CATALOG_NAME_SEPARATOR rgbInfoValue SQL_CATALOG_TERM rgbInfoValue Qualifier SQL_CATALOG_USAGE rgbInfoValue 0x00000000 SQL_COLUMN_ALIAS rgbInfoValue Y SQL_CONCAT_NULL_BEHAVIOR rgbInfoValue 0 SQL_CB_NULL SQL_CONVERT_BIGINT SQL_CONVERT_BINARY SQL_CONVERT_BIT SQL_C...

Page 15: ..._READ_COMMITTED SQL_DRIVER_HDBC rgbInfoValue Handle of the driver logon SQL_DRIVER_HENV rgbInfoValue Handle of the driver environment SQL_DRIVER_HLIB rgbInfoValue Handle of the library SQL_DRIVER_HSTMT rgbInfoValue Handle of the driver statement SQL_DRIVER_NAME rgbInfoValue sqlod32 dll libsqlod so UNIX SQL_DRIVER_ODBC_VER rgbInfoValue 03 51 SQL_DRIVER_ODBC_VER rgbInfoValue Version of the Driver Ma...

Page 16: ...nfoValue 128 SQL_MAX_COLUMNS_IN_INDEX rgbInfoValue 16 SQL_MAX_COLUMNS_IN_ORDER_BY rgbInfoValue 128 SQL_MAX_COLUMNS_IN_SELECT rgbInfoValue 1023 SQL_MAX_COLUMNS_IN_TABLE rgbInfoValue 1023 SQL_MAX_CONCURRENT_ACTIVITIES rgbInfoValue 32767 SQL_MAX_CURSOR_NAME_LEN rgbInfoValue 32 SQL_MAX_DRIVER_CONNECTIONS rgbInfoValue 0 SQL_MAX_INDEX_SIZE rgbInfoValue 1024 SQL_MAX_OWNER_NAME_LEN rgbInfoValue 32 SQL_MAX...

Page 17: ...DBC_SAG_CLI_CONFORMANCE rgbInfoValue 1 SQL_OSCC_COMPLIANT SQL_ODBC_SQL_CONFORMANCE rgbInfoValue 2 SQL_OSC_EXTENDED SQL_ODBC_SQL_OPT_IEF rgbInfoValue N SQL_ODBC_VER rgbInfoValue 03 520000 SQL_ORDER_BY_COLUMNS_IN_SELECT rgbInfoValue N SQL_OUTER_JOINS rgbInfoValue Y SQL_OWNER_TERM rgbInfoValue Owner SQL_OWNER_USAGE rgbInfoValue SQL_OU_DML_STATEMENTS SQL_OU_PROCEDURE_INVOCATION SQL_OU_TABLE_DEFINITION...

Page 18: ...K SQL_SCCO_OPT_ROWVER SQL_SCCO_OPT_VALUES SQL_SCROLL_OPTIONS rgbInfoValue SQL_SO_FORWARD_ONLY SQL_SO_KEYSET_DRIVEN SQL_SO_DYNAMIC SQL_SO_STATIC SQL_SEARCH_PATTERN_ESCAPE rgbInfoValue SQL_SERVER_NAME rgbInfoValue Name of the database server SQL_SPECIAL_CHARACTERS rgbInfoValue SQL_STATIC_SENSITIVITY rgbInfoValue 0x00000000 SQL_STRING_FUNCTIONS rgbInfoValue SQL_FN_STR_LEFT SQL_FN_STR_LTRIM SQL_FN_STR...

Page 19: ...QL_TXN_SERIALIZABLE SQL_UNION rgbInfoValue SQL_U_UNION SQL_U_UNION_ALL SQL_USER_NAME rgbInfoValue Name of the active user Level 2 API Overview of the functions that are defined in the SAP DB ODBC driver for the Level 2 API Function Remark SQLBrowseConnect SQLColumnPrivileges SQLDataSources SQLDescribeParam SQLExtendedFetch You can only insert rows using the function with the dynamic cursor model S...

Page 20: ...low are also available irrespective of whether the data source supports Unicode or not Under Microsoft Windows the Driver Manager basically uses the Unicode variants of the ODBC API If for example the SQLConnect function is called within the application SQLConnectW is called in the ODBC driver The conversions between ASCII and Unicode are performed by the Driver Manager Overview of the functions t...

Page 21: ...er Microsoft Windows Page 21 Creating Data Sources Under Microsoft Windows Page 23 Installation Under Microsoft Windows Use You have various options for installing the SAP DB ODBC driver under Microsoft Windows As part of the SAP DB server package As a separate software package such as for purely client installations These options differ only in the default value of the system for storing the inst...

Page 22: ...ssary Otherwise proceed as follows Open the InstallShield Wizard for the SAP DB ODBC driver by double clicking the file odbc7x setup exe The default value for the installation path is C Program Files SAPDB ODBC7x You can change this installation path Follow the instructions in the Wizard Result Information about all installed ODBC drivers is stored by the system in the file HKEY_LOCAL_MACHINE SOFT...

Page 23: ... to specify options for the new data source choose Settings Proceed with the entry of Options for the Data Source Page 23 If you do not want to specify any options choose OK Your entries become effective when the next ODBC connection is created Options for Data Sources Microsoft Windows You can specify various options to influence the functioning of the SAP DB ODBC driver These options become acti...

Page 24: ...data source the name of the database instance and the name of the database server Choose Options 5 In the SQL Mode field choose the desired SQL mode 6 Confirm the windows until the ODBC Data Source Administrator closes Result The next time that you log on to the database server using this data source the SAP DB ODBC driver functions in the selected SQL mode Isolation Level Microsoft Windows Use SA...

Page 25: ...ype 6 Confirm the windows until the ODBC Data Source Administrator closes Result The next time that you log on to the database server using this data source the SAP DB ODBC driver functions with the selected lock operation type Logging SQL Statements Microsoft Windows Use Select the Trace On option for a data source if you want to log the SQL statements that are transferred from the application to...

Page 26: ...oft Windows Page 21 Filename Description sql dll Libraries of the SAP DB ODBC driver odbcreg exe Program for entering the driver information in the registry SAP DB ODBC Driver Under UNIX Linux Installation Under UNIX Linux Page 26 Creating Data Sources Under UNIX Linux Page 28 Installation Under UNIX Linux You have various options for installing the SAP DB ODBC driver As part of the SAP DB server ...

Page 27: ...qlod so is found by the system at execution time see for example Idconfig LD_LIBRARY_PATH Procedure Use with a Driver Manager Using a Driver Manager such as iODBC or unixODBC simplifies the exchange of ODBC drivers and allows the concurrent use of different ODBC drivers The relevant documentation describes how you integrate an ODBC driver into the Driver Manager Use without a Driver Manager If you...

Page 28: ...d Committed Repeatable Serializable TraceFileName trace_file_path_and_name If you want to specify multiple data sources repeat the group of specifications above in accordance with the number of data sources 3 Save your entries Your entries become effective when the next ODBC connection is created Options for Data Sources UNIX Linux You can specify various options to influence the functioning of th...

Page 29: ...he SQL statements Syntax SQLMode INTERNAL DB2 ANSI ORACLE For a description of the SAP DB specific SQL mode INTERNAL see the Reference Manual SAP DB 7 4 SQL Mode See SAP DB Library section Procedure To change the SQLMode option follow the procedure described under Creating and Changing Data Sources Under UNIX Linux Page 28 The value defined in this way then applies for all connections of the data ...

Page 30: ...X Linux Page 28 The value defined in this way then applies for all connections of the data source Logging SQL Statements UNIX Linux Use The SQL statements transferred by the application to the database instance can be logged in a file The following data is recorded SQL statement Execution time Start and end of a session CONNECT parameters Input and output parameters Only one application can write ...

Page 31: ...scription libsqlod a libsqlod so SAP DB ODBC driver library incl sql h ODBC driver header file Core API incl sqlext h ODBC driver header file Level 1 API and Level 2 API incl sqltypes h ODBC driver header file Data types incl WINDOWS H Header file for the UNIX Linux platforms demo eng ODBC sqlexamp c C example program for an ODBC application demo eng ODBC sqladhoc c C example program for an ODBC a...