SAP DB:ODBC User Manual

ODBC Manual: SAP DB

SAP AG November 2002

Permission is granted to copy, distribute 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

SAP AG November 2002

Icons

Icon Meaning

Caution

Example

Note

Recommendation

Syntax

Typographic Conventions

Type Style Description

Example text

Words or characters that appear on the screen. These include field names, screen titles, pushbuttons as well as menu names, paths and options.

Cross-references to other documentation.

Example text

Emphasized words or phrases in body text, titles of graphics and tables.

EXAMPLE TEXT Names of elements in the system. These include report names,

program names, transaction 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 F2) or the ENTER key.

Example text

Exact user entry. These are words or characters that you enter in the system exactly as they appear in the documentation.

Variable user entry. Pointed brackets indicate that you replace these words and characters with appropriate entries.

ODBC Manual: SAP DB 3

SAP AG November 2002

ODBC Manual: SAP DB ............................................................................................................ 5

Supported ODBC Versions .................................................................................................... 5

Properties of the SAP DB ODBC Driver ................................................................................ 5

Supported Data Types ....................................................................................................... 6

SQL Data Types ............................................................................................................. 6

C Data Types.................................................................................................................. 6

Conversion of SQL and C Data Types............................................................................... 7

Processing SQL Statements .............................................................................................. 7

Database Procedures ..................................................................................................... 8

Cursor Functions ............................................................................................................ 8

ODBC-API Compatibility .................................................................................................... 8

Core API ......................................................................................................................... 9

Level 1 API.................................................................................................................... 10

Remark for SQLGetConnectAttr ............................................................................... 11

Remark for SQLGetFunctions................................................................................... 11

Remark for SQLGetInfo ............................................................................................ 14

Level 2 API.................................................................................................................... 19

Unicode Support............................................................................................................... 20

SAP DB ODBC Driver Under 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

Integrating the SAP DB ODBC Driver .......................................................................... 27

Creating and Changing Data Sources Under UNIX/Linux ............................................... 28

Options for Data Sources (UNIX/Linux)........................................................................ 28

SQL Mode (UNIX/Linux) ........................................................................................... 29

Isolation Level (UNIX/Linux) ..................................................................................... 29

Logging SQL Statements (UNIX/Linux) .................................................................... 30

Installed Files (UNIX/Linux).............................................................................................. 31

ODBC Manual: SAP DB 4

SAP AG November 2002

ODBC Manual: SAP DB

This manual describes the basics and features of the SAP DB ODBC driver. The explanations apply to SAP DB database systems as of version 7.3.01.

To understand this documentation, you require a basic knowledge of the C programming language, knowledge of SQL, and a general knowledge of database systems.

Use

The SAP DB ODBC driver allows access to the SAP DB database system. Data is accessed using the Structured Query Language (SQL).

Depending on the operating system, the SAP DB ODBC driver is both 32 bit- and 64 bitcompatible. It runs on Linux, Microsoft Windows as of Microsoft Windows 95 and all UNIX systems that are supported by SAP DB.

If the ODBC application is operated on the same host as the database instance, communication takes place using shared memory. Remote access of the ODBC application to the database 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.

Supported ODBC Versions

The SAP DB ODBC driver fulfils the ODBC 3.51 interface specification.

When developing your own applications, note that these differentiate between ODBC 2.x and ODBC 3.x versions. You should therefore use the function SQLSetEnvAttr to set the SQL_ATTR_ODBC_VERSION before you request a connection handle with SQLAllocHandle.

Properties of the SAP DB ODBC Driver

The functionality of the SAP DB ODBC driver is described in more detail for certain areas:

Supported Data Types [Page

Conversion of SQL and C Data Types [Page

Processing SQL Statements [Page

ODBC-API Compatibility [Page 8]

Unicode Support [Page 20]

ODBC Manual: SAP DB 5

SAP AG November 2002

Supported Data Types

The ODBC interface differentiates between the data types made available by the database system (SQL data types) and the data types used in the ODBC application (C data types).

SQL Data Types [Page

C Data Types [Page 6]

SQL Data Types

Every database system defines its own SQL data types. An ODBC driver processes only those data types that are defined by the associated database system.

You can use the function SQLGetTypeInfo to determine how an ODBC driver maps the SQL data types of the database system to the ODBC supported data types and to its own driverspecific data types, as well as the specifications for length, decimal places (scale), and the number of valid digits (precision).

For more information 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 Types

ODBC C data types represent those data types of C variables in which the application data intended for communication with the database is processed.

Relationship Between SQL and C Data Types

fCType ODBC C Typedef C Type

SQL_C_CHAR SQLCHAR * unsigned char *

SQL_C_SSHORT SQLSMALLINT short int

SQL_C_SLONG SQLINTEGER long int (32 bit)

SQL_C_USHORT SQLUSMALLINT unsigned short int

SQL_C_ULONG SQLUINTEGER unsigned long int (32 bit)

SQL_C_FLOAT SQLREAL float

SQL_C_DOUBLE SQLDOUBLE double

SQL_C_BIGINT SQLBIGINT _int64, long (64 bit)

ODBC Manual: SAP DB 6

SAP AG November 2002

SQL_C_UBIGINT SQLUBIGINT unsigned _int64, unsigned long (64 bit)

SQL_C_BINARY SQLCHAR* unsigned char*

SQL_C_DATE SQL_DATE_STRUCT struct tagDATE_STRUCT {

SQLSMALLINT year;

SQLSMALLINT month;

SQLSMALLINT day;}

SQL_C_TIME SQL_TIME_STRUCT struct tagTIME_STRUCT {

SQLSMALLINT hour;

SQLSMALLINT minute;

SQLSMALLINT second;}

SQL_C_TIMESTAMP SQL_TIMESTAMP_STRUCT struct tagTIMESTAMP_STRUCT {

SQLSMALLINT year;

SQLSMALLINT month;

SQLSMALLINT day;

SQLSMALLINT hour;

SQLSMALLINT minute;

SQLSMALLINT second;

SQLUINTEGER fraction;}

SQL_C_BIT SQLCHAR unsigned char

Conversion of SQL and C Data Types

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.

For detailed information about the ODBC function calls, see the Microsoft

Homepage (http://www.microsoft.com/data/ODBC) in the ODBC Software

Development Kit (SDK) under ODBC Programmer‘s Reference, Chapter 8: SQL Statements, Escape Sequences in ODBC, Scalar Functions section.

Numeric functions ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COT,

EXP, FLOOR, LOG, MOD, SIGN, SIN, SQRT, TAN, PI,

ODBC Manual: SAP DB 7

SAP AG November 2002

DEGREES, RADIANS, ROUND

String functions LEFT, SQL_FN_STR_LTRIM, LENGTH, LCASE, REPLACE,

RIGHT, RTRIM, SUBSTRING, UCASE, SOUNDEX

System functions USERNAME, DBNAME, IFNULL

Time and date functions NOW, CURDATE, DAYOFMONTH, DAYOFWEEK,

DAYOFYEAR, MONTH, WEEK, YEAR, CURTIME, HOUR, MINUTE, SECOND, DAYNAME, MONTHNAME

SELECT {fn MONTH(NOW())} FROM dual

The SAP DB ODBC driver has extensions to the usual SQL syntax in the following areas:

Database Procedures [Page

Cursor Functions [Page

Database Procedures

Database procedures can be processed as SQL statements.

See also: Reference Manual: SAP DB, Database Procedure [See SAP DB Library] section.

The ODBC syntax for procedures does not support return values of functions. Therefore, a specification in the format

{ ? = call ...}

is invalid.

Cursor Functions

The SAP DB ODBC driver supports 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 of the table that the other user has opened. Locks for individual pages or entire tables lead to significantly longer waits than row locks in operation with multiple concurrent users.

Using the function SQLSetPos, an application can execute positioning INSERT, UPDATE, and DELETE statements.

ODBC-API Compatibility

In the SAP DB ODBC driver, all functions can be executed both under Microsoft Windows and under UNIX/Linux.

The ODBC API is structured into the areas core, level 1, and level 2.

There is an overview of the functions that are defined for the individual API compatibility levels below.

ODBC Manual: SAP DB 8

SAP AG November 2002

Core API [Page 9]

Level 1 API [Page 10]

Level 2 API [Page

19]

Core API

Overview of the functions that are defined in the SAP DB ODBC driver for the core API

Function Remarks

SQLAllocConnect

SQLAllocEnv

SQLAllocStmt

SQLBindCol

If the length specification pcbValueMax is smaller than the length of a bound LONG column, the SAP DB ODBC driver returns the value SQL_NO_TOTAL for pcbValue. In all other cases, pcbValue specifies the length of the copied bytes.

You can then fetch 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 are automatically converted into uppercase letters.

The SAP DB ODBC driver constructs default values for cursor names in accordance with the following schema:

SQL_CURSOR_nnnn, where nnnn corresponds to an internal counter. SQL_CUR_nnnnnnn, if n >= 10000

SQLNumResultCols

ODBC Manual: SAP DB 9

SAP AG November 2002

SQLPrepare

SQLRowCount

Specifies the number of affected rows for all SELECT, INSERT, DELETE statements, and so on.

If no result set was created, the value -1 is displayed for the undeterminable number of affected rows.

SQLSetCursorName

SQLTransact

Level 1 API

Overview of the functions that are defined in the SAP DB ODBC driver for the Level 1 API

Function Remark

SQLBindParameter

If you want to process more than one LONG column with an INSERT, UPDATE, or DELETE statement, and bind the parameters with the attribute SQL_DATA_AT_EXECUTE, note that, for the SQLPutData function, it is only possible to assign the NULL value to one LONG column, at most. If you want to set more than one LONG column to the NULL value with only one statement, you must bind the columns in the length specification (pcbValue) 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 types supported by the SAP DB database system using the function SQLGetTypeInfo.

The following data types are supported: CHAR, VARCHAR, DECIMAL, FIXED, INTEGER, SMALLINT, FLOAT, REAL, DOUBLE PRECISION, DATE, TIME, TIMESTAMP, LONG, CHAR() BYTE, VARCHAR() BYTE, LONG BYTE, BOOLEAN

If the database is configured so that Unicode characters can be processed, SAP DB also supports the following data types: CHAR() UNICODE, VARCHAR() UNICODE, LONG UNICODE

SQLParamData

For LONG columns that contain NULL values, see remark for

SQLBindParameter

SQLPutData

For LONG columns that contain NULL values, see remark for

ODBC Manual: SAP DB 10

+ 21 hidden pages

SAP DB:ODBC User Manual

Specifications and Main Features

Frequently Asked Questions

User Manual