.
eloquence logo
.
  Home
.
 
Search
.
  Product Information
.
  Support
.
  Knowledge Base
.
  Documentation
.
  Download
.
  Feedback
.
 
.
Eloquence B.07.10 contact contact
Documentation / The Eloquence Odbc.DLL

The Eloquence Odbc.DLL
 
.
  The Eloquence ODBC (Open Data Base Connectivity) DLL enables Eloquence programs to interface with external database systems such as Oracle, Microsoft SQL Server or MySQL through the ODBC API on HP-UX, Linux and Windows platforms.

Contents


Prerequisites

The Eloquence Odbc.DLL requires the following components to be installed:

  • An ODBC Driver Manager

    This is a component which manages available ODBC data sources on a particular system.

    On Windows, this is typically part of the operating system or installed with applications like Microsoft Office or the Microsoft SQL Server. Data sources are managed with the ODBC Data Sources applet in the Windows Control Panel. The Windows ODBC components (driver manager and commonly used ODBC driver for Microsoft Access etc.) are available for download at http://www.microsoft.com/data/.

    On HP-UX and Linux, both the unixODBC and the iODBC open source ODBC driver managers are supported. Newer versions of unixODBC are recommended to be used preferably.

    The Eloquence Odbc.DLL on HP-UX and Linux expects to find the libodbc shared library (HP-UX: libodbc.sl.1, Linux: libodbc.so.1). If the library cannot be found because the unixODBC is installed in a nonstandard location, either the LD_LIBRARY_PATH environment variable may be set (for example LD_LIBRARY_PATH=/opt/unixODBC/lib) or a symbolic link may be created in /usr/lib to refer to the libodbc shared library. On Linux, ldconfig may be used to add the unixODBC lib directory to the dynamic linker's search path (see man ldconfig).

  • An appropriate ODBC driver

    This is a database-specific component which interfaces the particular database you intend to use.

    Examples for ODBC drivers are:

    • SQL/R ODBC from Marxmeier Software to access Eloquence databases
    • Microsoft MDAC to use Microsoft Access, FoxPro and SQL Server databases on Windows, typically installed with applications like Microsoft Office or the Microsoft SQL Server
    • FreeTDS on HP-UX and Linux to access SQL Server and Sybase databases running on Windows

    Drivers for other databases are either included with the particular database product or available as an option.

Before ODBC can be used, the appropriate data source(s) must be defined. A data source definition specifies:

  • the name of the data source (DSN: data source name)
  • which driver is used
  • which database is used (some drivers such as SQL/R ODBC allow to combine multiple databases into a single data source)
  • possibly some authentication information necessary to gain access to the database(s)
  • for client/server databases possibly the name of the remote system where the database server is operated

Except for the name of the data source and the driver these parameters depend on the requirements of the particular database and must be correctly specified according to the database vendor's documentation.

After appropriate configuration, the data source(s) can be accessed with the DSN (data source name) as shown in the examples below.

On Windows, the ODBC Data Sources applet in the Windows Control Panel is used for data source administration. It supports three different types of data sources:

  • A System data source is stored in the machine-specific area of the Windows Registry and therefore visible to all users. You need administrator privileges to configure System data sources.
  • A User data source is stored in the user-specific area of the Windows Registry and therefore only visible to the logged-on user.
  • A File data source is stored in a .dsn file located the file system, for example at C:\Program Files\Common Files\ODBC\Data Sources.

The Eloquence Odbc.DLL supports System and User data sources. File data sources cannot be used.

On HP-UX or Linux, the unixODBC driver manager uses two configuration files which are typically located either in the /etc or in the /etc/unixODBC or in the /etc/opt/unixODBC directory:

  • odbcinst.ini

    This configures the installed ODBC drivers. A driver's section starts with the driver name in square brackets, followed by a description and the location of the driver and setup shared libraries. The setup shared library is used with the unixODBC configuration GUI (if not supported by a driver, a default setup library can be used, as in the SQL/R example below). The FileUsage entry is used with the configuration GUI and should be set to 1.

    For example: 

    [MySQL]
Description     = MySQL ODBC Driver
Driver          = /usr/lib/libmyodbc.so
Setup           = /usr/lib/libodbcmyS.so
FileUsage       = 1

[PostgreSQL]
Description     = PostgreSQL ODBC Driver
Driver          = /usr/lib/libodbcpsql.so
Setup           = /usr/lib/libodbcpsqlS.so
FileUsage       = 1

[SQL/R]
Description     = SQL/R ODBC Driver
Driver          = /opt/sqlr2/lib/libsqlrodbc.so
Setup           = /usr/lib/libodbcdrvcfg1S.so
FileUsage       = 1

  • odbc.ini

    This configures the available data sources. A data source section starts with the DSN in square brackets, followed by a description and a reference to the associated driver configured in the odbcinst.ini file. Driver specific entries may be added as in the example below (please refer to the database vendor's documentation).

    The unixODBC driver manager first tries to locate a data source in a file named .odbc.ini in the current user's home directory (similar to the User data source concept on Windows). If this file does not exist or does not contain the specified data source, the global odbc.ini is searched (similar to the System data source concept on Windows).

    Please note: Early unixODBC versions before 2.2 (a) created an empty .odbc.ini file in the current user's home directory if it did not yet exist and then (b) did not fallback to the global odbc.ini file. The result was that a data source could not be accessed even if it was configured correctly. To solve this issue, either configure any data sources in the user's .odbc.ini file or create a symbolic link named .odbc.ini in the current user's home directory to refer to the global odbc.ini file.

    For example: 

    [MySQL-test]
Description      = A data source to test the MySQL ODBC driver
Driver           = MySQL
Trace            = Off
TraceFile        = /tmp/mysql-trace.log
DATABASE         = sample
SERVER           = 192.168.44.55
USER             = jdoe
PASSWORD         =
PORT             = 3306

[PostgreSQL-test]
Description      = A data source to test the PostgreSQL ODBC driver
Driver           = PostgreSQL
Trace            = Off
TraceFile        = /tmp/postgresql-trace.log
Database         = sample
Servername       = 192.168.44.66
UserName         = jdoe
Password         =
Port             = 5432
Protocol         = 6.4
ReadOnly         = No
RowVersioning    = No
ShowSystemTables = No
ShowOidColumn    = No
FakeOidIndex     = No
ConnSettings     =

[SQL/R-test]
Description      = A data source to test the SQL/R ODBC driver
Driver           = SQL/R
Server           = 192.168.44.77
Service          = 8003
User             = public
Quoting          = 0
Bulkfetch        = 1

    Please note: The SQL/R ODBC driver expects to find the data source either in the user's .odbc.ini file or in /etc/odbc.ini, otherwise it does not honor the Server, Service, User, Quoting or Bulkfetch options. If the global odbc.ini file is not located in /etc you could either symlink it to /etc/odbc.ini or set the ODBCINI environment variable, for example: ODBCINI=/etc/unixODBC/odbc.ini.


Using the Odbc.DLL

The Odbc.DLL must be loaded before it can be used in a program: 

LOAD DLL Odbc,1024

This loads the Odbc.DLL from its default location and establishes a communication buffer of 1024 bytes. For details about the Eloquence LOAD DLL statement please refer to the Eloquence documentation.

The DEL DLL statement may be used to unload the DLL: 

DEL DLL Odbc

The following procedures are available to return information on the Odbc.DLL or enable logging.

  • CALL DLL Odbc("Revision",Buf$)
    This call returns the revision of the Odbc.DLL in Buf$.

  • CALL DLL Odbc("LogFile","odbcdll.log")
    This call enables logging of debug messages to the specified file.

  • CALL DLL Odbc("LogFlags",LogFlags$)
    This call enables logging of debug messages as specified by LogFlags. Currently, LogFlags "*1" for additional information on failure and "*2" for debug messages are available.


ODBC connections and statements

  • CALL DLL Odbc("Connect",Conn_spec$,Conn_id)
    Establishes a connection to a data source. The syntax of the connection string depends upon which ODBC driver you use. Commonly, a data source name will be specified by means of the DSN= token; for example DSN=MY_DATA_SOURCE. Ordinarily, a data source will have been configured earlier. As an example, on HP-UX and Linux systems, the unixODBC driver manager uses an odbc.ini file where all available data sources are listed. On MS Windows systems, data sources are normally configured by means of the ODBC Administrator utility (the ODBC Data Sources applet in the control panel).
    The Connect call returns a connection id in Conn_id that is used to identify the connection in subsequent calls.

  • CALL DLL Odbc("GetConnection",Conn_id,Conn_spec$)
    Calling this procedure returns the complete connection specification in Conn_spec$ as used by the driver manager.

  • CALL DLL Odbc("Disconnect",Conn_id)
    Calling this procedure closes the specified connection. Any remaining statements which refer to this connection will be usable but once they are deleted, no new statements can use this connection.

  • CALL DLL Odbc("SetConnectOption",Conn_id,Option,Param[$])
    Calling this procedure specifies an option for the connection. Option is a numeric value that specifies the option, Param or Param$ specifies the value for the option.

    The supported options are driver specific. The following options are defined by the ODBC standard:
    101ACCESS_MODE
    102AUTOCOMMIT
    103LOGIN_TIMEOUT
    104OPT_TRACE
    105OPT_TRACEFILE
    106TRANSLATE_DLL
    107TRANSLATE_OPTION
    108TXN_ISOLATION
    109CURRENT_QUALIFIER
    110ODBC_CURSORS
    111QUIET_MODE
    112PACKET_SIZE

  • CALL DLL Odbc("SetAutocommit",Conn_id,Mode)
    This call sets the AUTOCOMMIT option for the connection. Mode is an integer value
    • 0 - AUTOCOMMIT_OFF
    • 1 - AUTOCOMMIT_ON
    The SetAutocommit call is a shortcut for: CALL DLL Odbc("SetConnectOption",102,Mode)

  • CALL DLL Odbc("Commit",Conn_id)
    This call performs a COMMIT on the transaction for the specified connection.

  • CALL DLL Odbc("Rollback",Conn_id)
    This call performs a ROLLBACK on the transaction for the specified connection.

  • CALL DLL Odbc("ExecDirect",Conn_id,Sql$,Stmt_id)
    Executes the specified SQL statement and returns the internal statement id in the variable Stmt_id. This Stmt_id is used in subseqent calls to identify the statement. A single connection may have multiple SQL statements, each represented by a separate statement id.

  • CALL DLL Odbc("Prepare",Conn_id,Sql$,Stmt_id)
    Prepares a SQL statement to be executed later (possibly multiple times using varying parameters). Similar to the ExecDirect call above it delivers a statement id. However, before any data can be retrieved any parameter values must be set as required and the Execute procedure must be called for this statement.

    Parameters are embedded into the SQL statement by means of question mark characters. Example: 

    SELECT * FROM CUSTOMERS WHERE MATCHCODE>=? AND MATCHCODE<=?
    This embeds two parameters: #1 for the lower and #2 for the upper limit of the matchcodes to be queried. They are preset with the NULL value.

    The values of these parameters can either be specified as arguments to the Execute call or explicitly set with SetParam.

  • CALL DLL Odbc("Tables",Conn_id,Qual$,Owner$,Name$,
     Type$,Stmt_id)
    Obtains information about databases (aka. "table owners") and tables in a data source and returns a statement id which then can be used to retrieve this information.

    The Owner argument can be used to limit information to a particular table owner (normally a database). With the Name argument a specific table name can be addressed. Both may contain or entirely consist of the % wildcard character.

    Examples: 

    ! Create a list of all tables in all databases
CALL DLL Odbc("Tables",Conn_id,"","","","",Stmt_id)

! Create a list of all tables in the SAMPLE database
CALL DLL Odbc("Tables",Conn_id,"","SAMPLE","","",Stmt_id)

! Create a list of all CUSTOMERS tables in all databases
CALL DLL Odbc("Tables",Conn_id,"","","CUSTOMERS","",Stmt_id)

! Create a list containing the CUSTOMERS table in the SAMPLE database
CALL DLL Odbc("Tables",Conn_id,"","SAMPLE","CUSTOMERS","",Stmt_id)

! Special enumeration mode: create a list of all databases
CALL DLL Odbc("Tables",Conn_id,"","%","","",Stmt_id)

    The resulting statement's columns are:

    TABLE_QUALIFIER -  Table qualifier (empty string if not supported by the driver)
    TABLE_OWNER -  Owner of the table (aka. database)
    TABLE_NAME -  Name of the table
    TABLE_TYPE -  Usually TABLE or VIEW or which types the driver supports
    REMARKS -  eg. usage notes for this table

  • CALL DLL Odbc("Columns",Conn_id,Qual$,Owner$,Name$,
     Column$,Stmt_id)
    Obtains information about columns in a data source and returns a statement id which then can be used to retrieve this information.

    The Owner and Name arguments can be used to limit information to a particular table owner (normally a database) and/or table. With the Column argument a specific column name can be addressed. All arguments may contain or entirely consist of the % wildcard character.

    Examples: 

    ! Create a list of all columns in all tables in all databases
CALL DLL Odbc("Columns",Conn_id,"","","","",Stmt_id)

! Create a list of all columns in all tables in the SAMPLE database
CALL DLL Odbc("Columns",Conn_id,"","SAMPLE","","",Stmt_id)

! Create a list of all columns in all CUSTOMERS tables in all 
! databases
CALL DLL Odbc("Columns",Conn_id,"","","CUSTOMERS","",Stmt_id)

! Create a list of all columns in the CUSTOMERS table in the 
! SAMPLE database
CALL DLL Odbc("Columns",Conn_id,"","SAMPLE","CUSTOMERS","",Stmt_id)

! Create a list of all CUSTNO columns in all tables in all 
! databases
CALL DLL Odbc("Columns",Conn_id,"","","","CUSTNO",Stmt_id)

! Create a list of all CUSTNO columns in all tables in the 
! SAMPLE database
CALL DLL Odbc("Columns",Conn_id,"","SAMPLE","","CUSTNO",Stmt_id)

! Create a list of all CUSTNO columns in all CUSTOMERS tables 
! in all databases
CALL DLL Odbc("Columns",Conn_id,"","","CUSTOMERS","CUSTNO",Stmt_id)

! Create a list containing the CUSTNO column in the CUSTOMERS 
! table in the SAMPLE database
CALL DLL Odbc("Columns",Conn_id,"","SAMPLE","CUSTOMERS","CUSTNO",Stmt_id)

    The resulting statement's columns are:

    TABLE_QUALIFIER -  Table qualifier (empty string if not supported by the driver)
    TABLE_OWNER -  Owner of the table containing the column (aka. database)
    TABLE_NAME -  Name of the table containing the column
    COLUMN_NAME -  Name of the column
    DATA_TYPE -  ODBC SQL data type (numerically encoded)
    TYPE_NAME -  ODBC SQL data type name
    PRECISION -  Display size (width) of the column
    LENGTH -  Required size in bytes to store the value (includes terminating null byte for string data)
    SCALE -  Number of column decimals if applicable
    RADIX -  Precision/scale unit (if 10 or NULL unit is 'digits', if 2 unit is 'bits')
    NULLABLE -  Nonzero if the column is nullable
    REMARKS -  eg. usage notes for this column

  • CALL DLL Odbc("DropStatement",Stmt_id)
    Calling this procedure drops the specified statement. Please note that statements are dropped automatically whenever a function that creates a new statement (such as ExecDirect or Tables) is called with a nonzero statement id. This makes it easy to re-use a statement without explicitly dropping it.

Example: 

    INTEGER Conn_id,Stmt1,Stmt2
    DIM Dsn$[256],Sql$[256]

! Load the ODBC module
    LOAD DLL Odbc,1024
!
! Connect to ODBC data source named 'SAMPLE'
! authorize with Login$ and Password$
    Login$="mike"
    Password$="secret"
    Dsn$="DSN=SAMPLE;UID="&Login$&";PWD="&Password$
    CALL DLL Odbc("Connect",Dsn$,Conn_id)

! Execute SQL statement
    Sql$="SELECT * FROM CUSTOMERS"
    CALL DLL Odbc("ExecDirect",Conn_id,Sql$,Stmt1)

! Prepare another SQL statement with two embedded parameters
    Sql$="SELECT * FROM CUSTOMERS WHERE MATCHCODE>=? AND MATCHCODE<=?"
    CALL DLL Odbc("Prepare",Conn_id,Sql$,Stmt2)


The ODBC statement

Once a statment id has been returned by the Odbc.DLL the following procedures may be called.

  • CALL DLL Odbc("Execute",Stmt_Id[,Param[$]...])
    Executes a previously prepared statement. A prepared statement may be executed more than once, each time with different parameters.

    Statement parameter values may be set by providing the appropriate number of arguments. They should match the required parameter's data type (see the GetParamType below). Alternatively, the SetParam procedure may be used to set parameter values before execution. In this case, Execute should be called without specifying any param values.

  • CALL DLL Odbc("Fetch",Stmt_id,Has_data[,Xbuf$])
    Fetches the next row of data for the specified statement id. The variable Has_data is nonzero if data could be obtained. It is set to zero if no more data are available. If the Xbuf$ variable is specified, the results are returned in this variable in the XPACK format. Otherwise, data can be retrieved with the GetData call.

  • CALL DLL Odbc("IsNull",Stmt_id,Col_number,Is_null)
    Checks to see if the specified result column (starting at 1) in the current row of data has the NULL value.

  • CALL DLL Odbc("GetData",Stmt_id,Col_number,Result[$])
    Returns the value of the specified result column (starting at 1) in the current row of data. The data is converted to the variable type. If the data type is incompatible, either an empty string or a zero value is returned.

  • CALL DLL Odbc("SetParam",Stmt_id,Par_number[,Value[$]])
    Sets the value of the specified parameter (starting at 1) for the next execution of the statement. Passing a value is optional. If a value is passed it should match the required parameter's data type (see the ODBC Parameter Information below). If no value is passed the parameter is set to NULL.

  • CALL DLL Odbc("RowCount",Stmt_id,Num_rows)
    Returns the number of affected rows to Num_rows. This value is returned for write commands and may optionally be available for SELECT statements as well (driver specific). A value of -1 is returned if the value is unknown or unsupported for the statement.

Example: 

! Using the first SQL statement from the previous example
!
! Sequentially fetch rows
    CALL DLL Odbc("GetNumColumns",Stmt_id,Num_columns)
    LOOP
       CALL DLL Odbc("Fetch",Stmt1,Has_data)
       EXIT IF NOT Has_data
       FOR Col=1 TO Num_columns
          CALL DLL Odbc("GetData",Stmt1,Col,Buf$)
          PRINT Buf$;"|";
       NEXT Col
       PRINT
    END WHILE

! Using the second SQL statement from the previous example
!
! Query matchcodes from A to E
    CALL DLL Odbc("Execute",Stmt2,"A","E")
!
! Fetch the data as shown above
    LOOP
       CALL DLL Odbc("Fetch",Stmt2,Has_data)
       EXIT IF NOT Has_data
       ...
    END LOOP

! Now query customer numbers F to M using SetParam
    CALL DLL Odbc("SetParam",Stmt2,1,"F")
    CALL DLL Odbc("SetParam",Stmt2,2,"M")
    CALL DLL Odbc("Execute",Stmt2)
    LOOP
       CALL DLL Odbc("Fetch",Stmt2,Has_data)
       EXIT IF NOT Has_data
       ...
    END LOOP


Using the XPACK format to retrieve data

As an option, the Fetch procedure may return the data in XPACK format. In this case, the variables are equivalent to the statement's result columns. For example, the statement 

SELECT CUSTNO, NAME1 FROM CUSTOMERS
would use the variable names Custno and Name1.

If a column title contains space or dot characters, they are replaced by underscores in the resulting variable name.

If a column title however cannot be transferred into a valid variable name (for example MIN(ORDER_DATE)) a generic variable name is used, artificial such as Column_1.

Example: 

! Execute SQL statement
    Sql$="SELECT CUSTNO,NAME1 FROM CUSTOMERS"
    CALL DLL Odbc("ExecDirect",Conn_id,Sql$,Stmt)

! Sequentially fetch rows
    LOOP
       CALL DLL Odbc("Fetch",Stmt,Has_data,Xbuf$)
       EXIT IF NOT Has_data
       XUNPACK Xbuf$
       PRINT Custno$,Name1$
    END LOOP


ODBC Column Information

The following procedures are available to retrieve information or change the data type on a result column of a statement. Column numbering starts at 1.

  • CALL DLL Odbc("GetNumColumns",Stmt_id,Num_columns)
    The number of result columns for the statement in Num_columns.

  • CALL DLL Odbc("GetColumnTitle",Stmt_id,Col_number,
     Col_title$)
    The column title is retured in Col_title$

  • CALL DLL Odbc("GetColumnVarName",Stmt_id,Col_number,
     Col_name$)
    The variable name associated by the Odbc.DLL with the column. this is used when the data is returned in XPACK format.

  • CALL DLL Odbc("GetColumnType",Stmt_id,Col_number,
     Col_type)
    The column data type is returned in Col_type
    • 1 = STRING
    • 2 = INTEGER
    • 3 = REAL
    • 4 = DATE
    • 5 = TIME
    • 6 = TIMESTAMP

    Note: Date, time and timestamp columns are currently delivered as INTEGER values. This is subject to change.

  • CALL DLL Odbc("SetColumnType",Stmt_id,Col_number,
     Col_type)
    The column data type is changed to Col_type
    • 1 = STRING
    • 2 = INTEGER
    • 3 = REAL
    • 4 = DATE
    • 5 = TIME
    • 6 = TIMESTAMP

    Note: This function does not check if a conversion to the specified data type is possible. Specifying an improper data type will probably result in unexpected column data.

  • CALL DLL Odbc("GetColumnPrecision",Stmt_id,Col_number,
     Col_prec)
    The display size (width) of the column is returned in Col_prec

  • CALL DLL Odbc("GetColumnScale",Stmt_id,Col_number,
     Col_scale)
    The number of column decimals is returned in Col_scale (if applicable)

  • CALL DLL Odbc("GetColumnNullable",Stmt_id,Col_number,
     Col_null)
    Returns a nonzero value in Col_null if the column is nullable

Example: 

! Execute SQL statement
    Sql$="SELECT * FROM CUSTOMERS"
    CALL DLL Odbc("ExecDirect",Conn_id,Sql$,Stmt_id)

! Print column titles
    CALL DLL Odbc("GetNumColumns",Stmt_id,Num_columns)
    FOR Col=1 TO Num_columns
       CALL DLL Odbc("GetColumnTitle",Stmt_id,Col,Col_title$)
       PRINT Col_title$,
    NEXT Col
    PRINT


ODBC Parameter Information

The following procedures are available to retrieve information on statement parameters. Parameter numbering starts at 1.

  • CALL DLL Odbc("GetParamType",Stmt_id,Par_number,Par_type)
    The parameter data type is returned in Par_type.
    • 1 = STRING
    • 2 = INTEGER
    • 3 = REAL
    • 4 = DATE
    • 5 = TIME
    • 6 = TIMESTAMP

    Note: Date, time and timestamp columns are currently expected to be passed as INTEGER values. This is subject to change.

  • CALL DLL Odbc("GetParamPrecision",Stmt_id,Par_number,
     Par_prec)
    Returns the display size (column width) of the parameter in Par_prec.

  • CALL DLL Odbc("GetParamScale",Stmt_id,Par_number,
     Par_scale)
    Returns the number of parameter decimals in Par_scale (if applicable)

  • CALL DLL Odbc("GetParamNullable",Stmt_id,Par_number,
     Par_null)
    Returns a nonzero value in Par_null if the parameter is nullable.


ODBC Error Handling

The Eloquence Odbc.DLL introduces the following new error codes:

1110 Failed to allocate ODBC resource.
The ODBC driver manager could not be loaded or ran out of internal resources.
1111 Internal ODBC failure.
An internal problem has occurred with the ODBC driver manager or the data source.
1112 Function sequence error.
A function sequence has been executed in a wrong order (eg. data has not yet been fetched).
1113 Failed to connect.
The ODBC driver could not be loaded, the data source does not exist or the authorization has failed.
1114 Failed to disconnect.
An internal problem has occurred while disconnecting.
1115 Already connected.
Tried to connect while a connection is already present.
1116 Not connected.
Tried to disconnect while no connection is present.
1117 Failed to prepare statement.
An invalid SQL statement has been provided.
1118 Failed to execute statement.
The provided SQL statement cannot be executed.
1119 Statement has not yet been prepared.
The provided SQL statement must be prepared before it can be executed.
1120 Statement has no result.
Tried to fetch data from a statement which does not have any result.
1121 Failed to fetch next row of data.
An internal problem has occurred while fetching the next row of data.
1122 Invalid column or parameter number.
An invalid column or parameter number has been provided.

The GetError procedure may be used to obtain detailed ODBC error information.

Executing an ODBC operation can cause errors of varying complexity. The problem may be recognized at different functional layers such as the ODBC driver manager or the database server involved in the particular operation.

Therefore, a single ODBC operation may provide information about multiple errors. After an ODBC function has failed, repeated use of the GetError procedure will sequentially obtain all error information.

Each error return consists of a text message, the ODBC SQL error code (called 'SQL state' according to the X/Open and SQL Access Group SQL CAE specifications) and the native error code which was returned by the particular ODBC driver. 

CALL DLL Odbc("GetError",Message$,Sql_state$,Native_code,Has_data)
  • STRING Message$
    Current error message

  • STRING Sql_state$
    Current SQL state

  • INTEGER Native_code
    Current native error code

  • INTEGER Has_data
    Returns zero if no more error information is available.

Example: 

    ON ERROR GOTO Error
 ...

Error:!
    OFF ERROR
    PRINT ERRM$
    IF (ERRN>=1110) AND (ERRN<=1122) THEN Odbc_error
    PRINT ERRMSG$(ERRN)
    STOP
! 
! ODBC.DLL specific error codes
! 
Odbc_error:!
    DIM Sql_msg$[256],Sql_state$[16]
    INTEGER Sql_native_code,Sql_has_data
    RESTORE Odbc_error
    REPEAT 
      READ Err,Sql_msg$
    UNTIL NOT Err OR (Err=ERRN)
    IF Err THEN PRINT Sql_msg$
    LOOP
      CALL DLL Odbc("GetError",Sql_msg$,Sql_state$,Sql_native_code,Sql_has_data)
      EXIT IF NOT Sql_has_data
      PRINT "ODBC Error: ";Sql_msg$
      PRINT "SQL State: ";Sql_state$;", Native code: ";Sql_native_code
    END LOOP
    STOP
! 
    DATA 1110,"ODBC resource failure (ERR_ODBCRESOURCE)"
    DATA 1111,"ODBC internal failure (ERR_ODBCINTERNAL)"
    DATA 1112,"Invalid call sequence (ERR_SEQUENCE)"
    DATA 1113,"Connection failed (ERR_CONNECT)"
    DATA 1114,"Disconnect failed (ERR_DISCONNECT)"
    DATA 1115,"Already connected (ERR_CONNECTED)"
    DATA 1116,"Not connected (ERR_NOTCONNECTED)"
    DATA 1117,"Statement preparation failed (ERR_PREPARE)"
    DATA 1118,"Statement execution failed (ERR_EXECUTE)"
    DATA 1119,"Statement not prepared (ERR_NOTPREPARED)"
    DATA 1120,"Statement has no result (ERR_NORESULT)"
    DATA 1121,"Failed to fetch next row (ERR_FETCH)"
    DATA 1122,"Column/parameter number out of range (ERR_INVALIDCOLUMN)"
    DATA 0,"Unknown error code"


Example programs

The following examples document how the Odbc.DLL could be used in a program.

The first example illustrates how to retrieve data using Prepare/Execute. It is installed as ODBC1.PROG in the Eloquence share/example installation subdirectory. 

! RE-STORE "ODBC1,EXAMPLE"
! 
! Eloquence ODBC DLL example program
! Retrieve data from the SAMPLE DSN using Prepare/Execute
! 
  DIM Buf$[256],Sql_msg$[256],Sql_state$[16]
  INTEGER Conn,Stmt,Col,Num_cols,Row,Has_data,Sql_native_code,Nrows
  INTEGER Col_type,Col_prec,Col_scale
  ON ERROR GOTO Error
  IF BACKGROUND THEN PRINTER IS STDOUT
! 
! Load the Eloquence Odbc.DLL and print version number
! 
  PRINT LIN(1);"** Loading the Eloquence Odbc.DLL"
  LOAD DLL Odbc,1024
  CALL DLL Odbc("Revision",Buf$)
  PRINT "Revision ";Buf$
! 
! The Eloquence Odbc.DLL can setup a debug log
! By default only critical messages are output to stderr
! 
! CALL DLL Odbc("LogFile","odbcdll.log")
! CALL DLL Odbc("LogFlags","*2")
! 
! Connect to the data source
! 
  Buf$="DSN=SAMPLE"
  PRINT LIN(1);"** Connecting to the data source ";Buf$
  CALL DLL Odbc("Connect",Buf$,Conn)
! 
! The completed connection string (by the ODBC driver manager)
  CALL DLL Odbc("GetConnection",Conn,Buf$)
  PRINT "Connection #"&VAL$(Conn)&": "&Buf$
! 
! Prepare a SQL statement
! 
  PRINT LIN(1);"** Preparing SQL statement"
  Buf$="SELECT * FROM CUSTOMERS WHERE MATCHCODE LIKE ? ORDER BY CUSTNO"
  CALL DLL Odbc("Prepare",Conn,Buf$,Stmt)
  PRINT "Statement #"&VAL$(Stmt)&": "&Buf$
! 
! The prepared statement has a result set
! 
  PRINT LIN(1);"** Result set"
  CALL DLL Odbc("GetNumColumns",Stmt,Num_cols)
  FOR Col=1 TO Num_cols
    PRINT "Col";Col;": ";
    CALL DLL Odbc("GetColumnTitle",Stmt,Col,Buf$)
    PRINT Buf$&" [";
    CALL DLL Odbc("GetColumnVarName",Stmt,Col,Buf$)
    PRINT Buf$&"]";
    CALL DLL Odbc("GetColumnType",Stmt,Col,Col_type)
    PRINT " Type=";VAL$(Col_type);
    CALL DLL Odbc("GetColumnPrecision",Stmt,Col,Col_prec)
    PRINT ",Prec=";VAL$(Col_prec);
    CALL DLL Odbc("GetColumnScale",Stmt,Col,Col_scale)
    PRINT ",Scale=";VAL$(Col_scale)
  NEXT Col
! 
! Execute the prepared statement
! 
  PRINT LIN(1);"** Executing prepared statement"
! The Execute call is used to specify any parameters (positional)
! Parameter not specified is assumed NULL
! 
  CALL DLL Odbc("Execute",Stmt,"KEL%")
! 
! As an alternative, the SetParam call may be used.
! CALL DLL Odbc("SetParam",Stmt,1,"KEL%")
! CALL DLL Odbc("Execute",Stmt)
! 
! For some calls, the server may already know the number of results
! If the value returned is -1 then it is unknown
! 
  CALL DLL Odbc("RowCount",Stmt,Nrows)
  PRINT Nrows;"results available"
! 
! Retrieve the data
! 
  PRINT LIN(1);"** Fetching results"
  Row=0
  LOOP
    CALL DLL Odbc("Fetch",Stmt,Has_data)
    EXIT IF NOT Has_data
    Row=Row+1
    PRINT "[";Row;"] ";
    CALL DLL Odbc("GetData",Stmt,1,Buf$)
    PRINT " "&CHR$(34)&Buf$&CHR$(34);
    CALL DLL Odbc("GetData",Stmt,2,Buf$)
    PRINT " "&CHR$(34)&Buf$&CHR$(34);
    CALL DLL Odbc("GetData",Stmt,3,Buf$)
    PRINT " "&CHR$(34)&Buf$&CHR$(34);
    CALL DLL Odbc("GetData",Stmt,9,Turnover)
    PRINT Turnover
  END LOOP
  PRINT Row;"rows fetched"
! 
  PRINT LIN(1);"** Disconnecting"
  CALL DLL Odbc("DropStatement",Stmt)
  CALL DLL Odbc("Disconnect",Conn)
  PRINT "done."
  STOP
! 
! Catch program error
! 
Error:! 
  OFF ERROR
  PRINT ERRM$
  IF (ERRN>=1110) AND (ERRN<=1122) THEN Odbc_error
  PRINT ERRMSG$(ERRN)
  STOP
! 
! ODBC.DLL specific error codes
! 
Odbc_error:! 
  ! PRINT "Problem detected by Odbc.DLL"
  RESTORE Odbc_error
  REPEAT 
    READ Err,Sql_msg$
  UNTIL NOT Err OR (Err=ERRN)
  IF Err THEN PRINT Sql_msg$
  LOOP
    CALL DLL Odbc("GetError",Sql_msg$,Sql_state$,Sql_native_code,Has_data)
    EXIT IF NOT Has_data
    PRINT "ODBC Error: ";Sql_msg$
    PRINT "SQL State: ";Sql_state$;", Native code: ";Sql_native_code
  END LOOP
  STOP
! 
  DATA 1110,"ODBC resource failure (ERR_ODBCRESOURCE)"
  DATA 1111,"ODBC internal failure (ERR_ODBCINTERNAL)"
  DATA 1112,"Invalid call sequence (ERR_SEQUENCE)"
  DATA 1113,"Connection failed (ERR_CONNECT)"
  DATA 1114,"Disconnect failed (ERR_DISCONNECT)"
  DATA 1115,"Already connected (ERR_CONNECTED)"
  DATA 1116,"Not connected (ERR_NOTCONNECTED)"
  DATA 1117,"Statement preparation failed (ERR_PREPARE)"
  DATA 1118,"Statement execution failed (ERR_EXECUTE)"
  DATA 1119,"Statement not prepared (ERR_NOTPREPARED)"
  DATA 1120,"Statement has no result (ERR_NORESULT)"
  DATA 1121,"Failed to fetch next row (ERR_FETCH)"
  DATA 1122,"Column/parameter number out of range (ERR_INVALIDCOLUMN)"
  DATA 0,"Unknown error code"


The second example illustrates how to retrieve data using XPACK. It is installed as ODBC2.PROG in the Eloquence share/example installation subdirectory. 

! RE-STORE "ODBC2,EXAMPLE"
! 
! Eloquence ODBC DLL example program
! Retrieve data from the SAMPLE DSN using XPACK
! 
  DIM Buf$[256],Sql_msg$[256],Sql_state$[16],Xbuf$[1024]
  INTEGER Conn,Stmt,Col,Num_cols,Row,Has_data,Sql_native_code
  DIM Custno$[6],Matchcode$[10],Name1$[32]
  REAL Turnover
  ON ERROR GOTO Error
  IF BACKGROUND THEN PRINTER IS STDOUT
! 
! Load the Eloquence Odbc.DLL and print version number
! 
  PRINT LIN(1);"** Loading the Eloquence Odbc.DLL"
  LOAD DLL Odbc,1024
  CALL DLL Odbc("Revision",Buf$)
  PRINT "Revision ";Buf$
! 
! Connect to the data source
! 
  Buf$="DSN=SAMPLE"
  PRINT LIN(1);"** Connecting to the data source ";Buf$
  CALL DLL Odbc("Connect",Buf$,Conn)
! 
! The completed connection string (by the ODBC driver manager)
  CALL DLL Odbc("GetConnection",Conn,Buf$)
  PRINT "Connection #"&VAL$(Conn)&": "&Buf$
! 
! Execute a SQL statement
! 
  PRINT LIN(1);"** Executing SQL statement"
  Buf$="SELECT CUSTNO,MATCHCODE,NAME1,TURNOVER__1 Turnover"
  Buf$=Buf$&" FROM CUSTOMERS WHERE MATCHCODE LIKE 'KEL%'"
  CALL DLL Odbc("ExecDirect",Conn,Buf$,Stmt)
  PRINT "Statement #"&VAL$(Stmt)&": "&Buf$
! 
! The prepared statement has a result set
! 
  PRINT LIN(1);"** Result set"
  CALL DLL Odbc("GetNumColumns",Stmt,Num_cols)
  FOR Col=1 TO Num_cols
    PRINT "Col";Col;": ";
    CALL DLL Odbc("GetColumnTitle",Stmt,Col,Buf$)
    PRINT Buf$&" [";
    CALL DLL Odbc("GetColumnVarName",Stmt,Col,Buf$)
    PRINT Buf$&"]"
  NEXT Col
! 
! Retrieve the data
! 
  PRINT LIN(1);"** Fetching results"
  Row=0
  LOOP
! The Fetch CALL returns all columns in the Xbuf$ variable in XPACK format
    CALL DLL Odbc("Fetch",Stmt,Has_data,Xbuf$)
    EXIT IF NOT Has_data
    Row=Row+1
! Unpack the result variables
    XUNPACK Xbuf$
! As an alternative the XUNPACK may also specify a subset of variables
    ! XUNPACK Buf$ FROM Custno$,Matchcode$,Name1$,Turnover
    PRINT "[";Row;"]";
    PRINT " "&CHR$(34)&Custno$&CHR$(34);
    PRINT " "&CHR$(34)&Matchcode$&CHR$(34);
    PRINT " "&CHR$(34)&Name1$&CHR$(34);
    PRINT Turnover
  END LOOP
  PRINT Row;"rows fetched"
! 
  PRINT LIN(1);"** Disconnecting"
  CALL DLL Odbc("DropStatement",Stmt)
  CALL DLL Odbc("Disconnect",Conn)
  PRINT "done."
  STOP
! 
! Catch program error
! 
Error:! 
  OFF ERROR
  PRINT ERRM$
  IF (ERRN>=1110) AND (ERRN<=1122) THEN Odbc_error
  PRINT ERRMSG$(ERRN)
  STOP
! 
! ODBC.DLL specific error codes
! 
Odbc_error:! 
  ! PRINT "Problem detected by Odbc.DLL"
  RESTORE Odbc_error
  REPEAT 
    READ Err,Sql_msg$
  UNTIL NOT Err OR (Err=ERRN)
  IF Err THEN PRINT Sql_msg$
  LOOP
    CALL DLL Odbc("GetError",Sql_msg$,Sql_state$,Sql_native_code,Has_data)
    EXIT IF NOT Has_data
    PRINT "ODBC Error: ";Sql_msg$
    PRINT "SQL State: ";Sql_state$;", Native code: ";Sql_native_code
  END LOOP
  STOP
! 
  DATA 1110,"ODBC resource failure (ERR_ODBCRESOURCE)"
  DATA 1111,"ODBC internal failure (ERR_ODBCINTERNAL)"
  DATA 1112,"Invalid call sequence (ERR_SEQUENCE)"
  DATA 1113,"Connection failed (ERR_CONNECT)"
  DATA 1114,"Disconnect failed (ERR_DISCONNECT)"
  DATA 1115,"Already connected (ERR_CONNECTED)"
  DATA 1116,"Not connected (ERR_NOTCONNECTED)"
  DATA 1117,"Statement preparation failed (ERR_PREPARE)"
  DATA 1118,"Statement execution failed (ERR_EXECUTE)"
  DATA 1119,"Statement not prepared (ERR_NOTPREPARED)"
  DATA 1120,"Statement has no result (ERR_NORESULT)"
  DATA 1121,"Failed to fetch next row (ERR_FETCH)"
  DATA 1122,"Column/parameter number out of range (ERR_INVALIDCOLUMN)"
  DATA 0,"Unknown error code"


The third example illustrated how to write data using a prepared statement. It is installed as ODBC3.PROG in the Eloquence share/example installation subdirectory. 

! RE-STORE "ODBC3,EXAMPLE"
! 
! Eloquence ODBC DLL example program
! Add data record using prepared statement
! 
  DIM Buf$[256],Sql$[256],Sql_msg$[256],Sql_state$[16]
  INTEGER Conn,Stmt,Has_data,Sql_native_code,Cnt
  ON ERROR GOTO Error
  IF BACKGROUND THEN PRINTER IS STDOUT
! 
! Load the Eloquence Odbc.DLL and print version number
! 
  PRINT LIN(1);"** Loading the Eloquence Odbc.DLL"
  LOAD DLL Odbc,1024
  CALL DLL Odbc("Revision",Buf$)
  PRINT "Revision ";Buf$
! 
  CALL DLL Odbc("LogFile","odbcdll.log")
  CALL DLL Odbc("LogFlags","*3")
! 
! Connect to the data source
! 
  Buf$="DSN=SAMPLE"
  PRINT LIN(1);"** Connecting to the data source ";Buf$
  CALL DLL Odbc("Connect",Buf$,Conn)
! 
! The completed connection string (by the ODBC driver manager)
  CALL DLL Odbc("GetConnection",Conn,Buf$)
  PRINT "Connection #"&VAL$(Conn)&": "&Buf$
! 
! Disable Autocommit
! 
  CALL DLL Odbc("SetAutocommit",Conn,0)
! 
! Prepare an INSERT SQL statement
! NOTE: All columns not specified will be set to NULL
! 
  PRINT LIN(1);"** Preparing SQL statement"
  Sql$="INSERT INTO CUSTOMERS(CUSTNO,MATCHCODE,NAME1) VALUES (?,?,?)"
  CALL DLL Odbc("Prepare",Conn,Sql$,Stmt)
  PRINT "Statement #"&VAL$(Stmt)&": "&Buf$
! 
! Execute the prepared statement
! 
  PRINT LIN(1);"** Add 3 customer records"
  CALL DLL Odbc("Execute",Stmt,"999990","TEST1","Test Name #1")
  CALL DLL Odbc("RowCount",Stmt,Cnt)
  PRINT "Affected rows:";Cnt
  CALL DLL Odbc("Execute",Stmt,"999991","TEST2","Test Name #2")
  CALL DLL Odbc("RowCount",Stmt,Cnt)
  PRINT "Affected rows:";Cnt
  CALL DLL Odbc("Execute",Stmt,"999992","TEST3","Test Name #3")
  CALL DLL Odbc("RowCount",Stmt,Cnt)
  PRINT "Affected rows:";Cnt
! 
! Read new entries
! 
  Sql$="SELECT COUNT(*) FROM CUSTOMERS WHERE CUSTNO IN('999990','999991','999992')"
  CALL DLL Odbc("ExecDirect",Conn,Sql$,Stmt)
  CALL DLL Odbc("Fetch",Stmt,Has_data)
  CALL DLL Odbc("GetData",Stmt,1,Cnt)
  PRINT "Found";Cnt;"entries"
! 
! ROLLBACK transaction
! NOTE #1: COMMIT or ROLLBACK will reset all active cursors
!          for the connection
! NOTE #2: The unixODBC driver manager has bug and also invalidates
!          any prepared statements
! 
  PRINT LIN(1);"** ROLLBACK transaction"
  CALL DLL Odbc("Rollback",Conn)
! 
! Check again for the new entries
! 
  CALL DLL Odbc("ExecDirect",Conn,Sql$,Stmt)
  CALL DLL Odbc("Fetch",Stmt,Has_data)
  CALL DLL Odbc("GetData",Stmt,1,Cnt)
  PRINT "Found";Cnt;"entries"
! 
  PRINT LIN(1);"** Disconnecting"
  CALL DLL Odbc("DropStatement",Stmt)
  CALL DLL Odbc("Disconnect",Conn)
  PRINT "done."
  STOP
! 
! Catch program error
! 
Error:! 
  OFF ERROR
  PRINT ERRM$
  IF (ERRN>=1110) AND (ERRN<=1122) THEN Odbc_error
  PRINT ERRMSG$(ERRN)
  STOP
! 
! ODBC.DLL specific error codes
! 
Odbc_error:! 
  ! PRINT "Problem detected by Odbc.DLL"
  RESTORE Odbc_error
  REPEAT 
     READ Err,Sql_msg$
  UNTIL NOT Err OR (Err=ERRN)
  IF Err THEN PRINT Sql_msg$
  LOOP
     CALL DLL Odbc("GetError",Sql_msg$,Sql_state$,Sql_native_code,Has_data)
     EXIT IF NOT Has_data
     PRINT "ODBC Error: ";Sql_msg$
     PRINT "SQL State: ";Sql_state$;", Native code: ";Sql_native_code
  END LOOP
  STOP
! 
  DATA 1110,"ODBC resource failure (ERR_ODBCRESOURCE)"
  DATA 1111,"ODBC internal failure (ERR_ODBCINTERNAL)"
  DATA 1112,"Invalid call sequence (ERR_SEQUENCE)"
  DATA 1113,"Connection failed (ERR_CONNECT)"
  DATA 1114,"Disconnect failed (ERR_DISCONNECT)"
  DATA 1115,"Already connected (ERR_CONNECTED)"
  DATA 1116,"Not connected (ERR_NOTCONNECTED)"
  DATA 1117,"Statement preparation failed (ERR_PREPARE)"
  DATA 1118,"Statement execution failed (ERR_EXECUTE)"
  DATA 1119,"Statement not prepared (ERR_NOTPREPARED)"
  DATA 1120,"Statement has no result (ERR_NORESULT)"
  DATA 1121,"Failed to fetch next row (ERR_FETCH)"
  DATA 1122,"Column/parameter number out of range (ERR_INVALIDCOLUMN)"
  DATA 0,"Unknown error code"


 
 
 
  Privacy | Webmaster | Terms of use | Impressum Revision: 2006-01-10  
  Copyright © 1995-2024 Marxmeier Software AG