Informix OLEDB Provider – Resolving Connection Issues

informixoledb

I've been trying to establish a OLEDB Connection to an Informix Dynamic Server. (Version IBM Informix Dynamic Server Version 12.10.FC12WE)

Background Info:
A program, which will be used by my company, requires OLEDB and doesn't support ODBC. The colleague,who is currently tasked with managing the implementation, has a lot of other work piled up and so the implementation will be delayed. In the meantime, I've been tasked to figure out how to properly configure the Windows 10 Client PC (which will run the program) for the connection. I have very little experience with databases, so I've searched for about 2 weeks for answers in various documentations and forums. So far, I had mixed success.

What has already been done:

  • The coledbp.sql– script has been run against the sysmaster table on the database
  • Informix Client SDK and Microsoft Data Access Components have been installed on the Client PC.
  • Using setnet32 the registry entries for host:port have been set.
  • The driver ifxoledbc has been registered.
  • ODBC-Datasources (both 64-Bit and 32-Bit) have been created and tested (they work fine).

Various Tests

For all my tests I've been using the Connection String:

"Provider=ifxoledbc;Data Source=mydatabase@myserver;User ID=myuser;Password=mypassword;

First I tried to test the connection with an .udl-File. I select the IBM Informix OLE DB Provider and enter the data source and userdata. When I test the connection I get an Error like:

"Initialisation of the provider failed. Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

From what I could find, there are many possible reasons for this error, and I don't know if can get a more specific error message somehow.

With the Microsoft OLE DB Provider for ODBC Drivers and the ODBC-datasource for the Server the test connection worked.

In Microsoft Excel (32-Bit) I also tried both the first mentioned Connection String and the Connection String, which is built when entering the infos in the .udl settings. Same Error with both cases.

For my next test I used a .vbs – script, which was presented in the IBM Informix Developers Handbook:

On Error Resume Next

set conn=createobject("ADODB.Connection")

conn.connectionstring = "Provider=ifxoledbc;DataSource=mydatabase@myserver;UserID=myuser;Password=mypassword;"

conn.open 

If Err Then     
   WScript.Echo "Error!! Open"+conn.Errors(0).Description 
Else    
   WScript.Echo "Connected"
   conn.close 
end if

This results once again results in the "multi-step"-Error.

The last thing I tried was using the Rowset Viewer of MDAC. I used the exact same input as with the .udl – File. But to my surprise it managed to connect! (It returned the error "Unknown Interface", but still worked) It correctly read the entries I set with the setnet32-Tool. (I checked this by temporarily removing one of the Entries, which resulted in an additional error and no connection)

Now I have a few questions:

  • Is there anything additional to configure on the Client PC?
  • How does the Viewer succeed, where every other test fails?
  • Is there any way to get a more accurate error code?
  • Are there major flaws in my tackling of the problem?

I appreciate every suggestion anybody can give me.

Best Answer

So, after giving up searching for answers, I started trying every imaginable combination of properties with different registries set and not set and I managed to get it working.

The Connection String was missing UNICODE=TRUE.

I can't say for sure, if I already tried this before and the error originally came from incorrectly set registry entries or if that simple solution slipped past my testing.

Anyways here is what I did, in addition to what was mentioned in my original post (no idea if that made any difference):

  • registered the ifxoledbc.dll (32Bit and 64Bit) with the respective regsvr32.exe (Originally I used the 64-Bit regsvr32.exe for both)
  • Added registry entries in Computer\HKEY_CLASSES_ROOT\Ifxoledbc\CLSID (64Bit) and Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Ifxoledbc\CLSID (32Bit) with name: OLEDB_SERVICES type: REG_DWORD and value: 0xffffffff

Concluding:

I'm still surprised, that the RowsetViewer somehow bypassed the need for the UNICODE property. Maybe it automatically sets it to true?

The interface:unknown error I mentioned with the viewer seems to have been fixed in a newer version of the Informix Client SDK (Only happened with 64-Bit Operating Systems using 32Bit Tools).

During my successful testing I also noticed, that Excel seemingly didn't need the %INFORMIXDIR% environment variable. FYI, when I was testing the ODBC datasources i had to edit the %INFORMIXDIR" to either point to my 32Bit or 64Bit folder (Not doing so resulted in a unable to load shared library error). The 64Bit datasource was also incompatible with my 32Bit Excel. With the OLEDB Provider however, it connects just fine with %INFORMIXDIR% still pointing to the 64Bit folder.

Related Question