Classics Forum

Informix, ODBC, ADO and VB

Posted By: KEVIN GODSMAN
Date: Tuesday, 21 October 2003, at 7:40 a.m.

Hello.

Some time ago I posted a question on this board about Informix, ODBC,
ADO and VB. I managed to solve my connection problems and wrote a VB
program to do an ADO based ODBC connection. I've been using this
program successfully for about six months.

Since then quite a few people have sent me email asking me how to do
the connection. Unfortunately I haven't got time to answer these emails
and I really don't have time to get into discussions re setup problems,
error messages and connection difficulties.

However in the interests of knowledge distribution, please find enclosed
a VB program, which should answer most of your questions. It has the
following attributes ...

1. It assumes you've taken care of the Unix Informix database naming.
(See section A)

2. It assumes you've taken care of the basic Windows ODBC setup.
(See Section B)

3. It uses ADO to create an ODBC connection to an Informix database.

4. I'm using it with Informix 7.3 but it should work on all versions.

5. It uses Informix Connect and Data Direct 4.1 Informix ODBC Driver.

6. It assumes your VB application is referencing Microsoft Active X
Data Objects 2.7 Library.

7. The code will work in VB Script, VB and VB.net as long as you do
the relevant tweeks for the relevant language.

8. If you can't work out from the VB code what forms, text boxes and
buttons you need, then I suggest you purchase a guide to VB
programming.

9. When I wrote the code it was beautifully and correctly indented.
It doesn't appear that way in the preview message area, so may
no look that way in the actual message area ... if so you'll
just have to indent it yourself!

Cheers

Kevin Godsman


--------------------------------------------------------------------------

Section A: Unix Database Naming

1. Ensure DBServerName and DBServerAliase are set in the
System Configuration section of your Informix onconfig file.

eg live_soc for a live database server / database server alias
... used as an example in Section B

--------------------------------------------------------------------------

Section B: Basic Windows ODBC Setup

1. Load Informix Connect to c:\informix on your Windows PC

2. Load Data Direct Informix 4.1 ODBC Driver on your Windows PC

3. Run Informix Setnet32

Set Environment/InformixDir to c:\informix
Set Environment/DBDate to DMY4 for UK format and MDY4 for US format
Set up a Server, eg live_soc

4. Create hosts, sqlhosts, services and onconfig files:

# Hosts File
# Columns are IP Address / Host Name

n.n.n.n informix_machine_name

# SQL Hosts File
# Columns are SQL Host Name / Protocol Name / Host Name / Service
# Host Name must be in Onconfig File
# Service links to service in Services File

live_soc onsoctcp informix_machine_name livesoc

# Services File
# Columns are Service / TCPIP Port Number
# Service is taken from SQL Hosts File

livesoc n/tcp

# On Config File
# Columns are DBSERVERNAME and Database Server Name
# Database Server Name is taken from SQL Hosts File

DBSERVERNAME live_soc

Copy these files to:

c:\informix\etc
c:\windows\system32\drivers\etc

5. Test the connection using Microsoft ODBC Administrator

If the ODBC connection fails check that you can ping your server,
that you created the files correctly and that you copied the files
to the correct directories.


--------------------------------------------------------------------------

Section B: Visual Basic Program


Public db As ADODB.Connection

' ADO database connection
' nb Would usually be put in a global module so the
' connection can be used throughout the application


Private Sub FormLoad()

' Populate connection setting text boxes
' nb The host, server, service and database values will
' be specific to your own Informix configuration

TBDriver.Text = "DataDirect 4.1 Informix"
TBHost.Text = "host"
TBServer.Text = "server"
TBService.Text = "service"
TBProtocol.Text = "onsoctcp"
TBDatabase.Text = "database"
TBUserName.Text = ""
TBPassword.PasswordChar = "*"
TBPassword.Text = ""
Me.Show
TBUserName.SetFocus

End Sub



Private Sub BTConnectToInformix_Click()

Dim msg As String ' Message
Dim style As Integer ' Message style
Dim title As String ' Message title
Dim dbdriver As String ' ODBC driver
Dim dbhost As String ' Host name
Dim dbserver As String ' Informix server
Dim dbservice As String ' Database service name
Dim dbprotocol As String ' Database protocol
Dim dbdatabase As String ' Database name
Dim dbusername As String ' Database user name
Dim dbpassword As String ' Database password
Dim dbconnection As String ' Database connection
Dim faultcount As Integer ' Number of ADO fault

On Error GoTo ErrorHandler

Screen.MousePointer = vbHourglass

' Ensure data has been entered

If TBDriver.Text = "" _
Or TBHost.Text = "" _
Or TBServer.Text = "" _
Or TBService.Text = "" _
Or TBProtocol.Text = "" _
Or TBDatabase.Text = "" _
Or TBUserName.Text = "" _
Or TBPassword.Text = "" Then
title = "Error"
style = vbOKOnly & vbCritical
msg = "Please enter all connection settings"
MsgBox msg, style, title
Screen.MousePointer = vbNormal
TBUserName.SetFocus
Exit Sub
End If

' Create database connection string

dbdriver = "DRIVER=" & TBDriver.Text & ";"
dbhost = "HOST=" & TBHost.Text & ";"
dbserver = "SRVR=" & TBServer.Text & ";"
dbservice = "SERV=" & TBService.Text & ";"
dbprotocol = "PRO=" & TBProtocol.Text & ";"
dbdatabase = "DB=" & TBDatabase.Text & ";"
dbusername = "UID=" & TBUserName.Text & ";"
dbpassword = "PWD=" & TBPassword.Text & ";"
dbconnection = dbdriver & dbusername & dbpassword & dbprotocol & dbserver & dbservice & dbhost & dbdatabase

' Configure database connection

Set db = New ADODB.Connection
db.Provider = "MSDASQL"
db.ConnectionTimeout = 30
db.ConnectionString = dbconnection
db.CursorLocation = adUseServer
'db.CursorLocation = adUseClient
db.IsolationLevel = adXactCursorStability
db.CommandTimeout = 60

' Open database connection

db.Open

' Display main screen

Screen.MousePointer = vbNormal
Unload Me
Load FormMain
FormMain.Show

Exit Sub

ErrorHandler:

' Covers standard VB errors and ADO database problems

title = "Error"
style = vbOKOnly + vbCritical
msg = "VB Error Number: " & Err & vbCr
msg = msg & "VB Description: " & Error & vbCr
If db.Errors.count > 0 Then
For faultcount = 0 To db.Errors.count - 1
msg = msg & vbCr
msg = msg & "VB Error Number: " & db.Errors(faultcount).Number & vbCr
msg = msg & "ADO Error Number: " & db.Errors(faultcount).NativeError & vbCr
msg = msg & "ADO Source: " & db.Errors(faultcount).Source & vbCr
msg = msg & "ADO Description: " & db.Errors(faultcount).Description & vbCr
Next faultcount
db.Errors.Clear
End If
MsgBox msg, style, title
Exit Sub

End Sub

Classics Forum is maintained by Administrator with WebBBS 5.12.