Connecting without an ODBC Data Source

Troubleshooting

Problem

This document describes how an application programmer can make an ODBC connection without requiring an existing ODBC data source on the client.

Resolving The Problem

One potential problem with redistributing an application that uses ODBC is that a data source might need to be created on each user's PC. Data sources are normally created by using ODBC C APIs; however, this interface might be difficult to use from some programming languages. If an application uses ADO, other options include:

Use a 'DSN-less' connection string.
Distribute and use a file DSN.
Create the data source by using a high-level object model such as that used by DAO or other ODBC C APIs.

Note: IBM i Access ODBC might change keywords and default values used on the connect string during release boundaries (or even with a PTF). For user and system DSNs, the installation program converts any existing data sources as needed. This conversion cannot take place for unregistered file DSN or user-defined connect strings. Although IBM i Access attempts to maintain compatibility with older connect strings, administrators need to review the release notes and new connect strings for changes. See the IBM i Access ODBC setup help (available through the ODBC Administrator) or the IBM Documentation website for details on the connect string keywords.

ADO Example

Private Sub Command1_Click()

Dim cn As New ADODB.Connection

Dim  MinConStr as String

Dim S as String

  

'You can use a full or partial connect string.  



'Minimum connect string requires DRIVER and SYSTEM keywords.  

'All other values default to the same value as a new data source.

MinConStr = "DRIVER={iSeries Access ODBC Driver};SYSTEM=RCHAS194;"

cn.Open MinConStr

cn.Close



'Full connect string 

'NOTE:  Connect strings should be verified after each CA version upgrade

S = "DRIVER=iSeries Access ODBC Driver;"

S = S & "MGDSN = 0;"

S = S & "SEARCHPATTERN = 1;"

S = S & "ALLOWUNSCHAR = 0;"

S = S & "COMPRESSION = 0;"

S = S & "MAXFIELDLEN = 32;"

S = S & "SIGNON = 3;"

S = S & "SSL = 2;"

S = S & "SORTWEIGHT = 0;"

S = S & "LANGUAGEID = ENU;"

S = S & "DFTPKGLIB = QGPL;"

S = S & "PREFETCH = 0;"

S = S & "SORTTYPE = 0;"

S = S & "CONNTYPE = 0;"

S = S & "REMARKS = 0;"

S = S & "LIBVIEW = 0;"

S = S & "LAZYCLOSE = 1;"

S = S & "TRANSLATE = 0;"

S = S & "SCROLLABLE = 0;"

S = S & "BLOCKSIZE = 32;"

S = S & "RECBLOCK = 2;"

S = S & "XDYNAMIC = 1;"

S = S & "DEC = 0;"

S = S & "TSP = 0;"

S = S & "TFT = 0;"

S = S & "DSP = 1;"

S = S & "DFT = 5;"

S = S & "NAM = 0;"

S = S & "DBQ = DDILLING;"

S = S & "CMT = 0;"

S = S & "System = RCHASBDS;"



'UID and PWD can be passed in the connect string or on the open.  This example passes them on the open.

'S = S & "UID=DDILLING;"     'optional

'S = S & "PWD=abc;"       'optional



cn.Open S, uid, pwd 

cn.Close



'Or build and distribute a file dsn with the application.  Use ODBC administrator to create the file dsn text file.

cn.Open "File Name=C:\mydsn.dsn"

cn.Close



'Another option is to build the data source in your install

'   - Use the ODBC Installer C APIs. VB Example (mnuCreateDSN_Click) is shown below.

'   - Use the DAO DBEngine's RegisterDatabase method

'   - Create the registry key (not recommended)

'

End Sub



'From the vbODBCT4.zip sample available at ftp://public.dhe.ibm.com/services/us/igsc/cs2/ApiSamples/

Private Sub mnuCreateDSN_Click()

   'Example of how to create a datasource for your program.

   '  See the ODBC API reference (part of the Microsoft Platform SDK, Data Access) at msdn.microsoft.com

   '  Use ODBC_ADD_SYS_DSN for a system DSN, ODBC_ADD_DSN for user DSN.

   '

   '  Note:  User must be authorized to write to the registry.

   '         Assumes ODBC 3.0 or later (MDAC 1.5 or later installed)

   '         See frmConnect and the SQLDataSources code for an example of checking if the DSN exists.

   '         Uses SQLManageDataSource(me.hwnd) to dipslay the ODBC administrator dialog.

   

   Dim dsnName As String      'DSN to create

   Dim Attr As String         'Connect string attribute list

   Dim S As String

   

   

   dsnName = InputBox("Enter the name of the DSN to create.  NOTE:  Any existing datasource will be replaced!", _

gAppName, "aaNewCADSN")

   If Len(dsnName) = 0 Then Exit Sub    'cancel

   

   'Set up the keyword values, null delimited per the ODBC spec

   '  At a minimum set DSN and SYSTEM - all other values default.

   '  Optionally, set any connect string keywords as defined in the IBM i Access on-line help.

   Attr = "DSN=" & Trim(dsnName) & vbNullChar      'Each keyword pair must end in a null

   Attr = Attr & "SYSTEM=RCHAS194" & vbNullChar    'Each keyword pair must end in a null

   Attr = Attr & vbNullChar                        'The string must end with an additional null

      

   'use a null window handle if you don't want the user prompted.  Otherwise use Me.hWnd

   If SQLConfigDataSource(0, ODBC_ADD_DSN, "IBM i Access ODBC Driver (32-bit)", Attr) <> 0 Then

      S = "Created datasource " & dsnName & ".  Would you like to see the new datasource? "

      If MsgBox(S, vbYesNo, gAppName) = vbYes Then

         SQLManageDataSources Me.hwnd

      End If

   Else

      Dim ErrMsg As String * 256

      Dim ErrCode As Long

      Dim ErrMsgLen As Integer

      

      ErrMsg = Space(256)

      SQLInstallerError 1, ErrCode, ErrMsg, Len(ErrMsg), ErrMsgLen   'ODBC 3.0 or later

      S = "Creation of datasource failed: " & vbCr

      S = S & ErrMsg

      MsgBox S, vbOKOnly, gAppName

   End If

   

End Sub

Related Information

IBM i Access ODBC Connection String Keyword

[{"Product":{"code":"SWG60","label":"IBM i"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Data Access","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"Version Independent","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]

Historical Number

20383800

Was this topic helpful?

Document Information

Modified date:
30 August 2022

UID

nas8N1017696

Tips