Wokking with ODBC

An Introductory ODBC Seminar

By John Middelveen

R&D Programmer

M. B. Foster Associates

82 Main St South

Chesterville, ON

448-2333

www.mbfoster.com

Abstract

ODBC Resources

Client Setup

ODBC Administrator

Installing ODBCLink/SE Driver

DSN Setup

How to make an ODBC Connection

MS Access

Visual Basic

ADO

RDO

DAO

ASP Application

VBS in IE

VBS in ASP

VBA

ODBCTest

Modifing Setup Parameters

Modifying Host Variables

Modifying Registry Variables

Other Tidbits

Debugging Techniques

Client Debugging

General call sequence

Examining Log files

What does it all mean

Using higher level debugging

ODBC Administrator SQL.LOG

Client debugging Programs

Debugging on the host

ODBCUTSE

Abstract

The Introductory ODBC User Workshop is intended for novice users or developers of Client Server Applications using ODBC to gain access to data on the HP3000/HP9000. The Information will primarily deal with the basics of how to use the ODBCLink/SE ODBC Driver with various applications. In many cases it will focus in on specific implementation issues dealing with the ODBCLink/SE Driver.

This workbook provides step by step instructions to follow while showing the basics of how to Setup and use the ODBCLink/SE driver to interface with various Client Applications.

ODBC Resources

There are a multitude of reference books and other material available for those who need a more in depth knowledge of ODBC. Three sources that I would recommend are.

For virtually any ODBC user the Microsoft ODBC 3.0 Programmers Reference and SDK Guide is a comprehensive and detailed reference dealing with virtually all aspects of the ODBC standard.

Information can also be found on the Microsoft web site

http://www.microsoft.com/odbc

For more advanced users or developers I would recommend obtaining a subscription to the Microsoft Developers Network.

Exercise Requirements

In this workbook you will setup the ODBCLink/SE driver and configure a DSN and learn how to connect to the DSN using various client applications.

In order to do this you will need access to a HP Server to download the ODBC Driver files (unless it is already installed on your system)

You will also need to know the location of a database on the server and an appropriate login to connect to it.

Finally you will need to have one at least one Client application installed on your PC that can access an ODBC Data-Source. Eg: MS Access, VB etc.

Client Setup

ODBC Administrator

The first program that anyone dealing with any ODBC Driver must be familiar with is the ODBC Administrator. For Windows platforms this is provided by Microsoft. Its main function is to allow users to configure data-sources for any ODBC driver that exists on the system.

Normally this program is invoked from the ODBC Administrator (ODBC Data Sources) icon in the Control Panel.

Start the ODBC Administrator on your PC….

The ODBC Administrator program allows you to configure data-sources, turn logging on and off; and for ODBC3.0 versions allows you to turn connection pooling on. You can also find out about what versions of ODBC drivers are installed on your PC.

Installing the ODBCLink/SE driver

You will need to have an ODBCLink/SE Driver installed on your PC to proceed with this exercise. You can see if it is installed by checking for a “ODBCLINK/SE-32 Driver’ in the list of drivers in the ODBC Administrator Driver Tab.

If it is not installed, install it now.

The steps are as follows:

Log onto the HP server and transfer the self-extracting odbccl32 file to your PC into the directory of your choice (temp will do). You will need to transfer it as a binary file and give it an ‘exe’ extension

On the host, the file is located where the other ODBCLink/SE files reside:

On MPE it should be in ODBCSE.SYS. On UNIX 9.x it is in /usr/bin/odbcse,

and for UNIX10.x its in /opt/allbase/bin/odbcse

Once you have the file, it is set up by running the file (to extract the files)

And then running the setup program setup.exe.

In rare cases you may have to run infsetup.bat prior to the setup program to tailor the odbc.inf file for WinNT or Win95.

DSN Setup

Once the ODBCLink/SE Driver is installed on your PC you are then able to set up a Data-source.

To setup a DSN you click on the Add button of the ODBC Administrator.

Then you select the driver that you wish to setup a DSN for; in this case the ODBCLink/SE-32 Driver.

Next click <Finish>.

The first ODBCLink/SE Setup window should appear:

Enter the DSN Name, DBE name (including path or group/account), Server Name (or IP address) and select the proper Server Type;

Then Click <Continue>

An MPE or Unix login Window will appear

Enter the appropriate Info:

You will need to login as a user who has access to whatever DBE you specified in the previous screen:

Press <Continue>

A Window for additional setup Information will appear.

Turn the Tracing ON and Press <OK>

A Translator Option Selection Window will appear.

Note that there are three translators installed with the ODBCLink/SE driver

The ‘BIG5’ dll (Asian symbols)

The r8ansi.dll (roman 8 to PC-ANSI)

The r9ansi.dll as of ver 5.57.08 (identical to roman8 except the sputnik or sun symbol is replaced with the Euro)

You may have other translators already installed on your system.

Once a DSN is set up, the next step is to use the DSN that you have set up in an ODBC connection with one of the many Client applications.

We will cover several of the most common applications here:

MS Access

After starting MS Access, you must either select an existing DBE or create a new one. (either is fine)

Next….

From the main MS Access Window, select

Get External Data,

Link Tables,

In the ‘Files of Type’ Dropdown box select ODBC Databases (usually one of the Last)

A Window will appear asking you to pick one of the configured data-sources.

Select the DSN that you created earlier.

After MS Access connects with the data source, a window will appear showing what tables are available to connect to.

When you have selected one or more tables hit <OK>

If there is no primary key detected on the table MS Access will prompt you to select one or more columns in the table which form a unique key.

Note that the key MUST be unique in order to update the table. If you update a table that does not have a unique key you will likely end up with an ERROR message in all boxes of the table.

A new entry should appear in the tables list.

Note for MS Access users:

One problem some users have with MS Access is that by default Large decimal columns (> 15) show as character in MS Access. A way around this will be discussed later in this Tutorial.

Visual Basic

There are several types of connections that can be made using ODBC with Visual Basic

These include ADOs, RDOs, and DAOs. You can also explicitly code your own connections if desired, though this is not commonly done in VB any more.

Connecting with ADO’s

The following are the steps required to create a simple ADO application that will display a table in a grid.

1. Start Visual Basic

2. Select a Standard exe Project

3. Double Click on the Form and paste the code below into Private Sub Form_Load()

4. Next you must ADD the object reference for ADO 2.0, On the main menu bar go to the heading project and then select references.

Find and select “Microsoft ActiveX Data Objects 2.0”.

5. To use the code below, you need to add the component MSFLEXGRID to the tool bar. On the main menu bar go to the heading ‘Project’ and then select ‘Components’. Find and select the Microsoft Flex Grid Control. Next add a MSFlexGrid (the yellow grid icon you just added to the tool bar) to the form. You should then resize it to fill most of your form.

ADO Code Explanation:

1 DECLARE YOUR VARIABLES FOR THE CONNECTION

2 OPEN THE CONNECTION TO THE DATABASE

3 SET THE ADO RECORDSET OPTIONS

4 OPEN THE ADO RECORDSET

5 USE THE RECORDSET INFORMATION YOU NEED AND PROCESS IT

ACORDINGLY

6 MAKE SURE YOU CLOSE ANY ODJECTS THAT YOU HAVE CREATED

WHEN ERRORS OCCUR, OR WHEN EXITING.

Sample ADO Code:

‘1

Dim ADOCN As New ADODB.Connection

Dim ADORS As New ADODB.Recordset

Dim colnum As Integer

Dim rownum As Long

‘2

'Your connection string data source name,User id ,Password

'in UDALINK you only need the DSN as the user id

'password and database are pulled from the UDALINK's DSN.

With ADOCN

.ConnectionString = "DSN=YOURDSN;UID=YOURUSERID;PWD=YOURPASSWORD;"

'Set the mode for the connection

.Mode = adModeReadWrite

'Open the connection

.Open

End With

‘3

With ADORS

'Set all option and parameters as needed

'Set cursor for the client or server end

.CursorLocation = adUseClient

'Set the cursor and lock type to be used with the recordset

.CursorType = adOpenKeyset

.LockType = adLockOptimistic

'Set max number of records to be returned

.MaxRecords = 200

'set the connection you are using

.ActiveConnection = ADOCN

'Set the SQL statement, table or stored procedure

.Source = "SELECT * FROM YOURTABLE"

'Open the recordset with the above source,

'connection,cursor type, lock type and options

‘4

.Open

‘5

MSFlexGrid1.Rows = .RecordCount + 1 'one extra for column names

MSFlexGrid1.Cols = .Fields.Count

'load column names

For colnum = 0 To .Fields.Count - 1

MSFlexGrid1.Row = 0

MSFlexGrid1.Col = colnum

MSFlexGrid1.Text = .Fields(colnum).Name

'load row data

For rownum = 1 To .RecordCount

MSFlexGrid1.Row = rownum

For colnum = 0 To .Fields.Count - 1

MSFlexGrid1.Col = colnum

If IsNull(.Fields(colnum).Value) Then

MSFlexGrid1.Text = "NULL"

Else

MSFlexGrid1.Text = .Fields(colnum).Value

End If

.MoveNext

End With

‘6

'Close record Set

ADORS.Close

'Close the Connection

ADOCN.Close

Connecting with RDO’s

RDOs generate calls to SQLExtendedFetch, which is not implemented in the driver.

As you will see below, you can use the Microsoft ODBC Cursor Library to handle the SQLExtendedFetch call.

It will translate the extended fetch into multiple fetches.

To do this you must set the object properties prior to connecting using the following line

rdoEngine.rdoEnvironments(0).CursorDriver = rdUseOdbc.

The steps required to create a simple RDO application are as follows:

1. Start Visual Basic

2. Select a Standard exe Project

3. Double Click on the Form and paste the code below into Private Sub Form_Load()

4. Next you must ADD the object reference for RDO 2.0, On the main menu bar go to the heading ‘Project’ and then select ‘References’.

Find and select “Microsoft Remote Data Objects 2.0”.

6. To use the code below you need to add the component MSFLEXGRID to the tool bar. On the main menu bar go to the heading project and then select Components. Find and select the Microsoft Flex Grid Control. Next add a MSFlexGrid (the yellow grid icon you just added to the tool bar) to the form.

7. Follow the code below and run the project.

RDO Code Explanation

1 DECLARE YOUR VARIABLES FOR THE CONNECTION

2 SET THE RDO ENIVROMENT

3 SET AND OPEN THE CONNECTION TO THE DATABASE

4 SET AND OPEN THE RECORDSET FOR THE TABLE

5 USE THE RECORDSET INFORMATION YOU NEED AND PROCESS IT

OCORDINGLY

6 MAKE SURE YOU CLOSE ANY ODJECTS THAT YOU HAVE CREATED

WHEN ERRORS OCCUR(OR WHEN EXITING)

SAMPLE RDO CODE

‘1

Dim RDOEN As RDO.rdoEnvironment

Dim RDOCN As New RDO.rdoConnection

Dim RDORS As RDO.rdoResultset

Dim RDOCL As RDO.rdoColumn

‘2

'Set Enviroment for RDO

Set RDOEN = rdoEngine.rdoEnvironments(0)

'setup line for ODBC link cursor library

RDOEN.CursorDriver = rdUseOdbc

‘3

Set RDOCN = RDOEN.OpenConnection(dsname:="YOURDSN", _

Prompt:=rdDriverNoPrompt, _

Connect:="UID=YOURUSERID;PWD=YOURPASSWORD;")

‘4

'Set Query for RDO Recordset Set

Set RDORS = RDOCN.OpenResultset(Name:="SELECT * FROM YOURTABLE", Type:=rdOpenKeyset)

‘5

Dim colnum As Integer

Dim rownum As Long

With RDORS

Grid1.Rows = .RowCount + 1 'the extra for column names

Grid1.Cols = .rdoColumns.Count

'load column names

For colnum = 0 To .rdoColumns.Count - 1

Grid1.Row = 0

Grid1.Col = colnum

Grid1.Text = .rdoColumns(colnum).Name

'load row data

For rownum = 1 To .RowCount

Grid1.Row = rownum

For colnum = 0 To .rdoColumns.Count - 1

Grid1.Col = colnum

If IsNull(.rdoColumns(colnum).Value) Then

Grid1.Text = "NULL"

Else

Grid1.Text = .rdoColumns(colnum).Value

End If

.MoveNext

End With

‘6

'Close resultset

RDORS.Close

'Close connection

RDOCN.Close

Connecting with DAO’s

The steps required to create a simple DAO application in VB are as follows:

1. Start Visual Basic

2. Select a Standard exe Project

3. Double Click on the Form and paste the code below into Private Sub Form_Load()

4. Next you must ADD the object reference for DAO 2.0-3.5, On the main menu bar go to the heading project and then select references.

Find and select “Microsoft Data Access Objects Compatibility 2-3”.

8. To use the code below you need to add the component MSFLEXGRID to the tool bar. On the main menu bar go to the heading project and then select Components. Find and select the Microsoft Flex Grid Control. Next add a MSFlexGrid (the yellow grid icon you just added to the tool bar) to the form.

9. Follow the code below and run the project.

DAO Code Explanation

1 DECLARE YOUR VARIABLES FOR THE CONNECTION

2 SET THE DAO WORKSPACE

3 SET AND OPEN THE CONNECTION TO THE DATABASE

4 SET AND OPEN THE RECORDSET FOR THE TABLE

5 USE THE RECORDSET INFORMATION YOU NEED AND PROCESS IT

OCORDINGLY

6 MAKE SURE YOU CLOSE ANY ODJECTS THAT YOU HAVE CREATED

WHEN ERRORS OCCUR(OR WHEN EXITING)

SAMPLE DAO CODE

‘1

Dim DAOWS As DAO.Workspace

Dim DAORS As DAO.Recordset

Dim DAODB As DAO.Database

Dim colnum As Integer

Dim rownum As Long

‘2

'SET A WORKSPACE ENVIROMENT FOR YOUR CONNECTION AND SET THE TYPE TO DBUSEODBC

Set DAOWS = CreateWorkspace("UDALinkWorkspace", "", "", dbUseODBC)

‘3

'OPEN THE CONNECTION with the options and parameters wanted

Set DAODB = Workspaces(0).OpenDatabase("", dbDriverComplete, False, "ODBC;DSN=YOURDSN;UID=YOURUSERID;PWD=YOURPASSWORD;")

‘4

'OPEN THE TABLE WITH THE SQL STATMENT AND CHOOSE THE RECORDSET ‘OPTIONS ETC.

Set DAORS = DAODB.OpenRecordset("Select * from YOURTABLE", dbOpenDynaset, dbSQLPassThrough)

‘5

With DAORS

'Do a move last and first to get a proper recordcount

.MoveLast

.MoveFirst

MSFlexGrid1.Rows = .RecordCount + 1 'the extra for column names

MSFlexGrid1.Cols = .Fields.Count

'load column names

For colnum = 0 To .Fields.Count - 1

MSFlexGrid1.Row = 0

MSFlexGrid1.Col = colnum

MSFlexGrid1.Text = .Fields(colnum).Name

'load row data

For rownum = 1 To .RecordCount

MSFlexGrid1.Row = rownum

For colnum = 0 To .Fields.Count - 1

MSFlexGrid1.Col = colnum

If IsNull(.Fields(colnum).Value) Then

MSFlexGrid1.Text = "NULL"

Else

MSFlexGrid1.Text = .Fields(colnum).Value

End If

.MoveNext

End With

‘6

'Close Resultset

DAORS.Close

'Close Data Base Connection

DAODB.Close

'Close the Workspace

DAOWS.Close

Web Access

There are numerous Applications that allow you to use an ASP (Active Server Page) to post a web page on your or another PC.

The examples below illustrate ADO in the environments for Microsoft Visual Basic Script Edition, used in Microsoft Internet Explorer, Microsoft Active Server Pages, Microsoft Visual Basic and Visual Basic for Applications

VBS in Internet Explorer X.X / HTML:

Using the <object> HTML tags, you must specify the entire Class ID ( CLSID), not the PROGID you may be familiar with, when using the CreateObject() function. Class IDs are globally unique identifiers. No other class in existence, whether invented by you or a software company, can have exactly the same CLSID because a unique sequence of numbers is used instead of a name. Although names are easy to remember, they're also easy for independent developers to accidentally use for completely different sets of objects.

The below CLSIDs are for ActiveX Data Objects ver 1.0. To find the CLSIDs for the current version you are using, check in your registry under H_KEY_CLASSES_ROOT, under ADODB.

Use this statement to create a recordset object using HTML <object> tags:

<OBJECT NAME=rs

CLSID="CLSID:00000281-0000-0010-8000-00AA006D2EA4"> </OBJECT>

To create a connection object using HTML <object> tags, use this statement:

<OBJECT NAME=cn

CLSID="CLSID:00000293-0000-0010-8000-00AA006D2EA4"> </OBJECT>

Here's how you create a command object using HTML <object> tags:

<OBJECT NAME=cm

CLSID="CLSID:0000022D-0000-0010-8000-00AA006D2EA4"> </OBJECT>

You must use the HTML <object>...</object> tag to create an ADO object. You must have the class GUID (CLSID) for the object you want to create as shown above.

<object id=rs

clsid="clsid:xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx">

</object>

rs.Open “DSN=YOURDSN;UID=YOURUID;PWD=YOURPWD;”

while not rs.EOF

' Insert your query processing code here.

rs.MoveNext

wend

rs.Close

</script>

VBS in Active Server Pages:

In an .asp file, use the CreateObject( ProgID ) function on the server object to create new ADO objects.

ADO constants must be declared explicitly because they are not loaded automatically from the typelib. The ADO constants are declared in the adovbs.inc file and can be manually inserted into your script.

set rs = server.CreateObject("ADODB.Recordset" )

rs.Open parameters

while not rs.EOF

' Insert your query processing code here.

rs.MoveNext

wend

rs.Close

VBA/VB5:

ADO objects can be created with the DIM statement and/or the CreateObject( ProgID ) function. ProgID names the desired class. Make sure you have installed Microsoft ADO objects on your system. If you have done so then Microsoft ActiveX Data Objects ver X.X Library can be selected in the references dialog of the tools menu, as shown below. ADODB is defined in the Reference, the ProgID can be used as a type name in the DIM statement.

Dim ADORS As ADODB.Connection

Dim ADOCN As ADODB.Recordset

Set ADORS = CreateObject("ADODB.Recordset")

Set ADOCN = CreateObject("ADODB.Connection")

DIM rs as New ADODB.Recordset

DIM cn as New ADODB.Connection

'Your connection string data source name,User id ,Password

'Note: in ODBCLINK SE you only need the DSN as the user id and

'password are pulled from the SE's DSN.

ADOCN.Open "DSN=YOURDSN;UID=YOURUID;PWD=YOURPWD;"

'Open the Recordset

With ADORS

'Set cursor for the client or server end

.CursorLocation = adUseClient

'Set the cursor type to be used with the recordset

.CursorType = adOpenDynamic

'Open the recordset with query, connection, parameters

.Open “SELECT * FROM TABLE, ADOCN

End With

MsgBox "ADO Connect Successful."

End Sub

‘Here is a more inclusive example.

DIM rs as ADODB.Recordset

set rs = CreateObject("ADODB.Recordset")

rs.Open parameters

while not rs.EOF

' Insert your query processing code here.

rs.MoveNext

wend

rs.Close

ODBCTest

ODBCTest is an excellent testing and debugging tool that will allow you to isolate problems either in your application or in the driver. It comes as part of the ODBC SDK from Microsoft.

This exercise will show how you can use ODBCTest to quickly Connect, see what tables are available and to select some information from one of the tables.

All the calls that an application might use while connecting with ODBC can be done using ODBCTest.

Special Note:

ODBCTest will not issue SQLTransact calls if it thinks that Autocommit is ON.

If AutoCommit is OFF in your driver setup you still must issue a SQLSetConnectOption to set Autocommit off to synchronize ODBCTest.

Connecting with ODBCTest

After Opening ODBCTest:

1. From the menu select Conn-Full Connect

A Window will appear with a list of all DSNs available to connect to.

2. Select the desired DSN to be connected to.

A Split window will be created. Note that the full connect command actually executes a number of SQLAPI Statements including one to allocate a Statement handle.

Getting Data with ODBCTest

You may now enter an SQL Statement into the input window.

To execute the statement, simply click the button with the exclamation icon

You can also execute any of the catalog functions, eg SQLTables, which will show you a list of all available tables on the system.

Once the statement is executed you must fetch the data (answer) from the server. To easily execute the repetitive steps required to fetch all the data simply click the button with the grid icon (beside the exclamation icon)

Modifying Host Variables

There are a few parameters that are settable on the host that can alter the host programs behavior.

The way you set a host variable for MPE is to edit the listener job and add a SETVAR line with the appropriate parameter. The following listener sample shows how you would add a setvar called ‘odbc_sample_setvar’to the ODBCJOB file.

ODBCLink/SE MPE Listner Job:

!job odbclnse,manager.sys,odbcse ;outclass=,1;pri=cs

!comment ODBCLink/SE (Special Edition) listener process

!setdump

!CONTINUE

!purge odbclog

!comment setvar odbc_debug 5

!setvar odbc_example_setvar 1

!odbclnse server

!eoj

Unix Listener

For Unix the procedure is slightly different because you normally invoke the server manually. The exact procedure is shell dependent. If you are in the c shell simply type:

export odbc_example_setvar =1

Then start the server: odbclnse server

Two Useful ODBCLink/SE setvars:

To trick MS Access into seeing large decimal columns (larger than 15) as numeric use the following:

SETVAR ODBC_LIMIT_NUMERIC_PRECISION 1

This will limit the precision figure returned in the SQLDescribeColumn call to 15.

Note : this only applies to version E.56.12 or later

If you wish to have all your fixed character fields return with all the trailing spaces

Rather than having them removed use the following:

SETVAR ODBC_REMOVE_TRAILING_SPACES 0

Note: This only applies to version E.57.08 or greater.

Modifying Client (Registry) Variables

For 32bit PCs (WinNT/95/98) DSN attributes are stored in the registry.

Attributes are normally changed from the client setup window (via the ODBC Administrator).

You can edit or add settings manually with the regedit.exe or regedt32 .exe program.

Editing a USER DSN Registry Setting

SYSTEM DSNs are in LOCAL_MACHINE-> SOFTWARE-> ODBC->ODBC.INI

and then the folder with the name of the DSN you are wanting to modify.

User DSNs are found the same way but in the CURRENT_USER window.

REGISTRY VARIABLES ARE CASE SENSITIVE.

Registry Variables you might want to change include:

DEGUG – The client debug setting

DEBUG_SQLX – Server Debug setting

These are normally 0 (debug box unchecked) or 3 (debug box checked)

In some cases when debugging a higher level of debug is useful.

Registry Variables you can Add:

These variables are normally defaulted and are not found in the registry.

If you want to modify these settings you must ADD a string variable with that name.

To do this Select Edit-New-String Value then add the appropriate String and Value.

CommandTimeout.

- Default is 180000 (3 minutes).

LoginTimeout

- Default is set to 60000 (1 minute)

- Applicable in cases where making the connection takes a long time

- Is rarely an issue, unless you have MANY isql DBE attached.

MAXSTMT

This tells the driver what to return to the applications SQLGetInfo call as the value for SQL_ACTIVE_STATEMENTS. The ODBCLink/SE default for MAXSTMT is 1. The driver actually supports up to 50 statements per connection.

Note on MAXSTMT: Allbase users may get “Cursor has been closed...” if one statement is closed (and committed) while others are still active. This is because Allbase commits and closes ALL statements on the connection if a COMMIT is issued. Because some applications do not expect this behavior the MAXSTMT setting was defaulted to 1 so that only one statement is made per connection (provided the application recognized the setting).

The disadvantage with setting the parameter to 1 is that more over-head is required on the client (more connections must be made) and you will reach your maximum Allbase concurrent transaction limit sooner.

Other Tidbits

Session Limit

Occasionally users get "Timeout on Data read" errors for no apparent reason.

Could be because Allbase transaction limit has been reached.

The default value is typically 5.

Particularly if MAXSTMTS is 1 you could quickly run out of connections.

You can set number of concurrent sessions allowed to a DBE in SQLUTIL.

Queue Tuning

On the first job statement of the listener job the command ‘PRI=CS’ is included.

It has no effect unless system manager issues the command ‘JOBPRI CS’.

System manager may elect to tune system queues so batch and online user cues overlap.

It is also possible to set up a cue (E) for the listener to allow special tuning between batch and online.

Connecting to other DBEs

An Undocumented feature of HP-PCAPI was ability to connect to a DBE other than the one set up in the datas-ource.

The SE driver now also has this capability.

Simply use the same DSN but put a new ‘lastuser’ string in the UID=... part of the connect string.

Mixing Client/Server Versions

To ease installation issues only major version number is checked.

eg: E.56.05 client will work with E.56.12 server version

If a change is made in the driver that requires both a client and server change then the major number is incremented.

Debugging Techniques

Client Debugging:

There are a number of ways to debug ODBC client-server problems. Usually the best thing to do when there is a problem is to examine a debug log. Two commonly used logs available on the client are odbclink.log and sql.log. Odbclink.log is much smaller as it prints only one line of output per SQL Call, however sometimes, when details of each parameter are needed SQL.LOG is more useful.

Where can tracing be done?

General call sequence

When examining logs you will generally see a call sequence similar to the one above..

The calls associated with generation and processing of the SQL statements and receiving the results is normally the largest part of the log.

Examining log files

ODBCLINK.LOG

The odbclink.log file is created when the Debug Client box in the setup is checked.

The logfile will be created in the working directory of the application being run. (eg : MSAccess will often put it in the My Documents directory)

When the debug box is checked, the registry variable DEBUG for the particular data-source you are using will be set to 3, the default debug level. Level 3 is adequate for most users needs, and gives all ODBC Calls.

Example logfile:

23:06:22 SQLDriverConnect(0) hdbc=0 hstmt=0 Ver=E.56.12 szConnStrIn=<DSN=se-32-trill-partsdbe;>,Completed_string=<DSN=SE-32-TRILL-PARTSDBE; UID=#mpeix/192.1.2.3:partsdbe.dbtest,,an#mgr/.odbclink/xxxxx> Login=<>

23:06:22 SQLError(100) hdbc=0 hstmt=0 pfNativeError=0,szErrorMsg=

23:06:22 SQLGetInfo(0) hdbc=0 hstmt=0 InfoType=CURSOR_COMMIT_BEHAVIOR rgbInfoValue=1

23:06:22 SQLGetInfo(0) hdbc=0 hstmt=0 InfoType=CURSOR_ROLLBACK_BEHAVIOR rgbInfoValue=0

23:06:22 SQLGetInfo(0) hdbc=0 hstmt=0 InfoType=ACTIVE_STATEMENTS rgbInfoValue=1

23:06:22 SQLGetInfo(0) hdbc=0 hstmt=0 InfoType=MAX_COLUMN_NAME_LEN rgbInfoValue=20

23:06:22 SQLGetInfo(0) hdbc=0 hstmt=0 InfoType=MAX_CURSOR_NAME_LEN rgbInfoValue=20

23:06:22 SQLGetInfo(0) hdbc=0 hstmt=0 InfoType=IDENTIFIER_QUOTE_CHAR rgbInfoValue=

23:06:22 SQLGetInfo(0) hdbc=0 hstmt=0 InfoType=IDENTIFIER_CASE rgbInfoValue=1

23:06:22 SQLGetInfo(0) hdbc=0 hstmt=0 InfoType=MAX_USER_NAME_LEN rgbInfoValue=0

23:06:23 SQLGetInfo(0) hdbc=0 hstmt=0 InfoType=USER_NAME rgbInfoValue=

23:06:23 SQLGetInfo(0) hdbc=0 hstmt=0 InfoType=DATA_SOURCE_NAME rgbInfoValue=SE-32-TRILL-X-PARTSDBE

23:06:23 SqlAllocStmt(0) hdbc=0 hstmt=0 Start

23:06:23 SQLAllocStmt(0) hdbc=0 hstmt=0

23:06:23 SQLTables(0) hdbc=0 hstmt=0 . Type=

23:06:23 SQLFetch(0) hdbc=0 hstmt=0

23:06:23 SQLNumResultCols(0) hdbc=0 hstmt=0 *pccol=5

23:06:23 SQLGetData(0) hdbc=0 hstmt=0 icol=1 fCType=CHAR cbValueMax=300 pcbValue=-1 rgbValue=

23:06:23 SQLGetData(0) hdbc=0 hstmt=0 icol=2 fCType=CHAR cbValueMax=300 pcbValue=12 rgbValue='MGR@ODBCLINK'

23:06:23 SQLGetData(0) hdbc=0 hstmt=0 icol=3 fCType=CHAR cbValueMax=300 pcbValue=10 rgbValue='M_CUSTOMER'

23:06:23 SQLGetData(0) hdbc=0 hstmt=0 icol=4 fCType=CHAR cbValueMax=300 pcbValue=5 rgbValue='TABLE'

23:06:23 SQLGetData(0) hdbc=0 hstmt=0 icol=5 fCType=CHAR cbValueMax=300 pcbValue=13 rgbValue='ALLBASE TABLE'

23:06:23 SQLFreeStmt(0) hdbc=0 hstmt=0 Close

23:06:24 SQLExecDirect(0) hdbc=0 hstmt=0 select * from member.membership

23:06:24 SQLNumResultCols(0) hdbc=0 hstmt=0 *pccol=15

23:06:27 SQLFetch(0) hdbc=0 hstmt=0

23:06:27 SQLNumResultCols(0) hdbc=0 hstmt=0 *pccol=15

23:06:27 SQLGetData(0) hdbc=0 hstmt=0 icol=1 fCType=CHAR cbValueMax=300 pcbValue=5 rgbValue='11595'

23:06:27 SQLGetData(0) hdbc=0 hstmt=0 icol=2 fCType=CHAR cbValueMax=300 pcbValue=6 rgbValue='kjhkjh'

23:06:27 SQLGetData(0) hdbc=0 hstmt=0 icol=3 fCType=CHAR cbValueMax=300 pcbValue=6 rgbValue='kjhkjh'

23:06:27 SQLGetData(0) hdbc=0 hstmt=0 icol=4 fCType=CHAR cbValueMax=300 pcbValue=6 rgbValue='kjhkjh'

23:06:27 SQLGetData(0) hdbc=0 hstmt=0 icol=5 fCType=CHAR cbValueMax=300 pcbValue=6 rgbValue='kjhkjh'

23:06:27 SQLGetData(0) hdbc=0 hstmt=0 icol=6 fCType=CHAR cbValueMax=300 pcbValue=6 rgbValue='kjhkjh'

23:06:27 SQLGetData(0) hdbc=0 hstmt=0 icol=7 fCType=CHAR cbValueMax=300 pcbValue=1 rgbValue=' '

23:06:27 SQLGetData(0) hdbc=0 hstmt=0 icol=8 fCType=CHAR cbValueMax=300 pcbValue=1 rgbValue=' '

23:06:27 SQLGetData(0) hdbc=0 hstmt=0 icol=9 fCType=CHAR cbValueMax=300 pcbValue=1 rgbValue='0'

23:06:27 SQLGetData(0) hdbc=0 hstmt=0 icol=10 fCType=CHAR cbValueMax=300 pcbValue=1 rgbValue='0'

23:06:27 SQLGetData(0) hdbc=0 hstmt=0 icol=11 fCType=CHAR cbValueMax=300 pcbValue=1 rgbValue=' '

23:06:27 SQLGetData(0) hdbc=0 hstmt=0 icol=12 fCType=CHAR cbValueMax=300 pcbValue=1 rgbValue=' '

23:06:27 SQLGetData(0) hdbc=0 hstmt=0 icol=13 fCType=CHAR cbValueMax=300 pcbValue=1 rgbValue='0'

23:06:27 SQLGetData(0) hdbc=0 hstmt=0 icol=14 fCType=CHAR cbValueMax=300 pcbValue=20 rgbValue='00000000000000000000'

23:06:27 SQLGetData(0) hdbc=0 hstmt=0 icol=15 fCType=CHAR cbValueMax=300 pcbValue=1 rgbValue='0'

23:06:27 SQLFreeStmt(0) hdbc=0 hstmt=0 Close

23:06:27 SQLCancel(0) hdbc=0 hstmt=0

23:06:27 SQLFreeStmt(0) hdbc=0 hstmt=0 Drop

23:06:27 SQLDisconnect(0) hdbc=0 hstmt=0

SQLFreeConnect(0)

What does it all mean?

Generally a client log note contains

Time, Call (return code), connection_handle, stmt_handle, other call specific information:

Eg: 14:22:25 SQLColAttributes(0) hdbc=0 hstmt=0 fDescType=7

The most common return codes are:

0 Success -1 Failure

1 Success with Info 100 End of Data

Using Higher debug levels

In rare cases a high level of debug is necessary (usually only useful for the lab). See “Registry Information” on how to change this setting.

DEBUG set at 10 shows much of the Client/Server Comms but ...

Caution** Creates HUGE log files before version E.56.07

ODBC Administrator SQL.LOG

In the ODBC Administrator (both ODBC 2.0 and 3.0) there is a way of turning logging on which will log ALL data-sources.

SQL.LOG also logs calls between the client and the ODBC32.dll so if the ODBC32.dll is translating any calls you will see what was sent by the application.

The ODBC 3.0 log will also tell you which app or DLL is making the call.

SQL.LOG example

The following is an ODBC 3.0 Administrator generated SQL.LOG showing a partial

log of a VB 5 application attempting to make an RDO connection without using the odbc cursor library.

RDODB2 fffc1679:fffa3f51 ENTER SQLBindCol

HSTMT 0x01ec16a8

UWORD 16

SWORD -16 <SQL_C_SLONG>

PTR 0x02478c54

SDWORD 4

SDWORD * 0x02478df0

RDODB2 fffc1679:fffa3f51 EXIT SQLBindCol with return code 0 (SQL_SUCCESS)

HSTMT 0x01ec16a8

UWORD 16

SWORD -16 <SQL_C_SLONG>

PTR 0x02478c54

SDWORD 4

SDWORD * 0x02478df0 (0)

RDODB2 fffc1679:fffa3f51 ENTER SQLExtendedFetch

HSTMT 0x01ec16a8

UWORD 1 <SQL_FETCH_NEXT>

SDWORD 1

UDWORD * 0x024710a4

UWORD * 0x02471218

RDODB2 fffc1679:fffa3f51 EXIT SQLExtendedFetch with return code -1 (SQL_ERROR)

HSTMT 0x01ec16a8

UWORD 1 <SQL_FETCH_NEXT>

SDWORD 1

UDWORD * 0x024710a4

UWORD * 0x02471218

DIAG [IM001] [Microsoft][ODBC Driver Manager] Driver does not support this function (0)

Client Debug Programs

Dr DeeBee Spy

Provided with the SE Driver.

Lists all ODBC Calls made to and from a particular data-source.

Provides more detail than odbclink.log in that ALL parameters are listed, one parameter per line.

BUT...Output is much larger.

ODBCSpy

Comes in the Microsoft SDK.

Similar to Dr DeeBee Spy (made by same company)

Allows you to not just record but also play back ODBC calls.

Can emulate either the driver or the client.

Useful for repetitive testing of a long series of ODBC Calls

Debugging on the Host

Log files

Logging goes to TWO places. Some logging is written to the ODBCLOG.

MORE logging is sent to the spool file (xx.out.hpspool)

Logging at level 3 is started by clicking on the host debug check box in the client setup API. The level of logging can also be set in the Listener job.

Level 3 Connection Prepares Commits etc.

Level 5 Image DBINFOs, DBGETs (not in SE)

Level 8 Some Program flow information

Level 10 Client/Server Data Stream Info

Level 12 More Program Flow of some modules (only in E.56.12 or later)

Although the spool file has more information the odbclog file has timestamp and pin information and is useful for synchronizing the client and server log.

ODBCUTSE

The Utility program ODBCUTSE is useful in debugging server side problems with the driver.

Turn debugging on with a setvar in your session before you start ODBCUTSE so you can see the debug log notes on your screen. Eg: “setvar odbc_debug 5”

start odbcutse by giving it the dbe to connect to as a parameter:

eg: “odbcutse partsdbe.dbtest”

Wokking with ODBC

Table of Contents

Connecting with RDO’s

RDO Code Explanation

SAMPLE RDO CODE

DAO Code Explanation

SAMPLE DAO CODE

Send email to Interex or to the Webmaster
©Copyright 1999 Interex. All rights reserved.

Wokking with ODBC

Table of Contents

Connecting with RDO’s

RDO Code Explanation

SAMPLE RDO CODE

DAO Code Explanation

SAMPLE DAO CODE

Send email to Interex or to the Webmaster©Copyright 1999 Interex. All rights reserved.

Send email to Interex or to the Webmaster
©Copyright 1999 Interex. All rights reserved.