URGENT!! SQLConfigDatasource with IBM DB2 ODBC DRIVER 1

[luvcal]

I have a program that creates DSN's for different vendor's ODBC drivers (oracle, etc) automatically. The only one that fails is the IBM DB2 ODBC DRIVER. I can't get any info on why this would fail. PLEASE HELP!!! If anyone knows anything, let me know asap. Thanks a bunch.

 

[Zyrenthian]

What exactly is the problem? The database is on the computer but you cant cofigure it properly? Can you do it manually through ODBC?

Matt

 

[luvcal]

Yes, the db is local and I can configure it manually through the ODBC GUI tool, and I can cfg it using DB2 CLP catalog commands, but it fails at creation time using SQLConfigureDataSource()...I don't know how to get the error. Like I said, it works with other drivers. Here is the call:

result = SQLConfigDataSource( NULL,
ODBC_ADD_SYS_DSN,
DB2_DRIVER,
"DSN=DB2_DRIVER\0"

If you have any ideas, or any ideas on how to get the error code, it would be appreciated.

 

[Zyrenthian]

An exception should be thrown

try{
result = SQLConfigDataSource( NULL,
ODBC_ADD_SYS_DSN,
DB2_DRIVER,
"DSN=DB2_DRIVER\0"
}
catch(CException* ex)
{
// get exception text from ex
}
catch(...)
{
// call get last error
}

See if this helps.

Matt

 

[luvcal]

I'm not working in java, but in C

 

[Zyrenthian]

This is a post in the Visual C++ forum. The above code is for Visual C++. The call to SQLConfigDataSource is also windows based and GetLastError is a windows api call. CException is part of the MFC library. you should have no problems with any of this.

Matt

 

[luvcal]

Whoops...obviously I don't know too much about MFC...sorry! Thanks for the help. I can't seem to get this to work...i.e. it never gets to the catch stmt... could you give me a specific code example? I imagine it would be similar to mine:
try{
printf("\nSetting up %s driver.\n",DB2_DRIVER);
result = SQLConfigDataSource( NULL,
ODBC_ADD_SYS_DSN,
DB2_DRIVER,
"DSN=DB2_DRIVER\0"
);
}
catch(CException *ex){
printf("Caught the exception for the DB2 driver"
char info[255];
ex->GetErrorMessage(info, 255);
printf("Message is: \n%s",info);
}

Is there a catch-all exception (like Exception in Java)? Thanks for all your help and patience.

 

[dds82]

Zyrenthian:

Why should an exception be thrown? SQLConfigDataSource() is an API function, not an MFC function. API functions don't throw exceptions, they return status codes.

However, the GetLastError() function should work. If SQLConfigDataSource() fails, then call GetLastError(). To get more information about the error, you can use FormatMessage() with the FORMAT_MESSAGE_FROM_SYSTEM flag.

HTH

 

[luvcal]

Thanks for the help dds82. GetLastError() returns a zero, and and FormatMessage() appeared to return successfully ("The Operation Completed Successfully&quot. This is apart from the fact that my DB2 ODBC DataSource cfg failed miserably. The funny part is this:
SQLConfigDataSource() return code is 0...
GetLastError() returns 0...
Yet, the ODBC datasource is not being created...don't know what to think...thanks for your help

 

[dds82]

Ah...

Looked up SQLConfigDataSource() in the MSDN and found this:

Diagnostics

When SQLConfigDataSource returns FALSE, an associated *pfErrorCode value can be obtained by calling SQLInstallerError. The following table lists the *pfErrorCode values that can be returned by SQLInstallerError and explains each one in the context of this function.

Apparently, SQLConfigDataSource() neither throws exceptions nor uses the usual Windows error reporting functions. Instead, it looks like you'll have to call SQLInstallerError() to get your error information.

 

[dds82]

Here's another thought...

If all else fails, you can actually see what Windows is trying to do when it creates the DSN. If the HWND parameter (the first one) isn't NULL, then it displays the same dialog box you get when you configure a DSN through the control panel--except all the fields are filled in based on the other parameters you passed to SQLConfigDataSource(). You can look at that to see exactly what options are really being passed to Windows.

If your app doesn't have a window, you can set the HWND to GetDesktopWindow().

 

[luvcal]

Very, very helpful, thank you!!! The GetDesktopWindow() call gives me a dialog with no values put in...and the error message returned from SQLInstallerError() is:
"Driver's ConfigDSN, ConfigDriver, or ConfigTranslator failed"
So, I am trying to find the right parameters for lpszAttributes...if you have any more good ideas, let me know...thanks again

 

[luvcal]

Ok, I've narrowed it down to the wrong key/value pairs for the IBM DB2 ODBC DRIVER...DSN,DESCRIPTION,and DATABASE are not being passed correctly. UID and PWD are being inserted in the datasource though...
Is there any way of programatically finding what the key/value pairs are supposed to be (syntax, etc)?? Here is my call to SQLConfigDataSource:

SQLConfigDataSource(GetDesktopWindow(),
ODBC_ADD_SYS_DSN,
DB2_DRIVER,
"DSN=DB2_DRIVER\0"
"DESCRIPTION=test\0"
"UID=myuid\0"
"PWD=mypwd\0"
"DATABASE=myDB\0"
);

where DB2_DRIVER = "IBM DB2 ODBC DRIVER"

Any help is appreciated.

 

[dds82]

OK, here's some more stuff.

First, the driver name in the second parameter must be passed in exactly as you see it in the ODBC control panel when you add a data source from there and it gives you the list of drivers. If the DB2 driver is called "IBM DB2 ODBC Driver (*.db2)", then you must include the " (*.db2)" part in your driver request. See which error code SQLConfigDataSource() returns. If it's ODBC_ERROR_INVALID_NAME, then this is your problem.

Also, the DSN name might have to be different than the driver name, but I'm not sure about that. In any case, it's a good idea for them to be different.

Key/value pairs such as DSN=, DESCRIPTION=, are the same no matter which driver you choose. You might want to try the key DBQ instead of DATABASE (not sure if that really matters). If all else fails, remove all the keys except DSN and DESCRIPTION and see what comes up in the dialog box. If the return value is ODBC_ERROR_INVALID_KEYWORD_VALUE, then your problem is here.

But...

The error message you got back would seem to indicate that the return value is ODBC_ERROR_REQUEST_FAILED. In that case, I don't know what went wrong (and Windows isn't telling us much).

 

[dds82]

Oops... when I said return values, I meant the *pfErrorCode that you get back from SQLInstallerError().

 

[luvcal]

I'm still failing miserably with the DB2 driver...The driver name is the correct name (from the Admin GUI and the registry). One strange thing is that in the ODBCINST.INI file, the DB2 driver is listed as: "IBM DB2 ODBC DRIVER (32 bit)" but if I try to use that, it complains that the driver is not registered...again, it is listed as "IBM DB2 ODBC DRIVER" in the registry...could this be the problem? Should I mess with the ini file? How do I register the DB2 odbc driver under the proper name? Any help is appreciated.

 

[luvcal]

YES, YES, YES!!! While digging through some DB2 docs, I found that DB2's ODBC driver config parameters ARE different than the rest of the worlds'. Instead of using "DSN=datasourcename", they use "DBALIAS=dbname" and it MUST match an existing database name cataloged in the system. There is no support for the "DATABASE=..." cfg parameter and now UID and PWD don't work, but I don't need to set them anyway!!! What a relief!! Thanks for all your help. Funny thing about this problem is that I've been working with DB2 support, and they could not figure it out! Oh well...thanks again!