Problem Using qConfigData with the Oracle ODBC Driver

 Keywords:

Question:

I have NOT been unable to configure a User DSN with the qConfigData function. The only items that are created are the DSN, Description and Driver. None of the other attributes are being created and all other default settings are never configured in the ODBC.INI.
Here is the code:
AddExtender("wwodb34i.dll") request=1 driver="Oracle ODBC Driver" displayflag=@FALSE attrs="DSN=Ora101|DESCRIPTION=Test DB|FAILOVER=T|LOBS=T|RESULTSETS=T|SERVERNAME=ora101|UserID=sysadm|" ret=qConfigData(request, driver, attrs, @FALSE) if ret==0 ;AskItemList("DSN Configuration failed",qConfigError(),@TAB,@UNSORTED,@SINGLE) err=qConfigError() message("qConfigData - Created DSN",err) exit endif exit
I am performing this on a Windows 2000 Professional workstation. I can configure the User DSN manually, but have had no luck with qConfigData and I do not receive any errors. Any ideas?

Answer:

So the DSN is getting created?

I would suspect all of the necessary attributes are not being specified. The qConfigData function simply obtains the list of attributes and passes them off to the appropriate ODBC Driver. Which in your case is the Oracle ODBC Driver. Unfortunately I do not have the docmentation for this driver. You might try locating a help file named Drvora32.hlp

'attributes', List of attributes in the form "keyname=value". These strings are separated by pipe symbols. These attributes will primarily be default driver-specific entries, which go into the ODBC.INI file for the new data source. One important key is "DSN" which specifies the name of the new data source. The rest of the entries are specific to the driver for the new data source. Often times it is not necessary to supply ALL of the entries because the driver can prompt the user (if 'display dialogs flag' is not @FALSE) with dialog boxes for the new values. You might want to explicitly supply default values so that the user is not prompted.

One way to find the keynames and their values is to examine the Registry Key ODBC.INI for an already configured data source (perhaps one that has been configured by the ODBC Administrator program):

Open the Registry.

For a User DSN, locate the key

[HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\YourDSNNameHere]

For a System DSN�

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\YourDSNNameHere]

These are the entries you will want to use in your Attributes parameter.

User reply:

That's exactly where I began, the problem with the registry Key ODBC.INI is that some of the Value names are not the same as the Attribute names for configuring the Oracle ODBC.

Example:
Registry has ServerName for a value, the Oracle ODBC attribute is Server. Another would be UID in the registry, but is User for the ODBC attribute.

I guess it's just something a little "special" for the Oracle ODBC.

Also the Oracle help file referenced the following, but they didn't work:

Keyword Meaning
DSN= ODBC data source name
UID= User ID or user name
PWD= Password (specify PWD=; for an empty password)
DBQ= Service Name
DBA=  Database attribute (W=write access, R=read-only access)
TLO= Translation option
TLL= Translation library name
PFC= Prefetch count (specify a value zero or greater)
APA= Applications Attributes (T=Thread Safety Enabled)
FEN= Failover Enabled (T=Failover Enabled)
FRC= Failover Retry Count (Number Value)
FDL= Failover Delay (Number Value)
LOB= LOBs Writes Enabled (T=LOBs Enabled)
RST= Results Sets  (T=Result Sets Enabled)
FRL= Forced Retrieval of Oracle Long Col (T=Forced Long Reads Enabled)
MTS= Microsoft Transaction Server Support (T=Disabled, F=Enabled)
QTO= Query Timeout option (T=Query Timeout Enabled)
CSR= Close Cursor Enabled (T=Close Cursor Enabled, F=(Default) Close Cursor
Disabled)

I ended up looking at SQORAS32.DLL with a hex editor, and found the internal attribute names needed to create new DSNs:

(Oracle ODBC Driver 8.1.7.0.0)
DSN=dsnname (as expected)
Description=description of DSN (as expected)
Server=server name (instead of DBQ=)
User=userid (instead of UID=)
Attributes=W (instead of DBA=)
TrnLib= (instead of TLL=)
TrnOpt=0 (instead of TLO=)
PreFetchCnt=10 (instead of PFC=)
AppAttributes=T (instead of APA=)
Failover=F (instead of FEN=)
FailoverRetryCount=10 (instead of FRC=)
FailoverDelay=10 (instead of FDL=)
Lobs=F (instead of LOB=)
ResultSets=F (instead of RST=)
Longs=F (instead of FRL=)
DisableMTS=F (instead of MTS=)
QueryTimeout=T (instead of QTO=)
CloseCursor=F (instead of CSR=)

Apparently there is a list of all the attributes for the Oracle ODBC for version 8i, that is not in the help file. This list is the actual attribute names for Windows 2000 and is what I used to get my script to work.

Just so you see my code here is what I have working on Windows 2000:

AddExtender("wwodb34i.dll")
;Create DSN
DSNName="TEST"
servername="MyServer"
uid="sysadm"
DSNDesc="TEST ORACLE ODBC" 
request=1;ODBC_ADD_DSN
driver="Oracle ODBC Driver";case sensitive
line="TLO=O|PFC=10|CSR=F|MTS=F|FRL=F|FAILOVER=T|FDL=10|FRC=10|LOBS=T|QTO=T|RESULTSETS=T|APA=T|DBA=W|"
atrrs=StrCat("DSN=%DSNName%|USER=%uid%|SERVER=%servername%|DESCRIPTION=%DSNDesc%|",line)
displayflag=@FALSE
ret=qConfigData(request, driver, atrrs, displayflag)
if ret==@False Then message("qConfigData failed", qConfigError())
exit

Article ID:   W15411

File Created: 2003:05:13:11:27:32

Last Updated: 2003:05:13:11:27:32

Database Search

TechHome