CODEWITHSUNDEEP
.netwindowsentity-frameworkinformix
RagtimeWilly
RagtimeWilly

Reputation: 5445

Issues connecting to Informix via Entity Framework from Windows

I have been trying to connect to an Informix database using Entity Framework from a .Net application and have run into numerous problems at every step.

Trying to test the connection to the database using the testconn40 command I get the following:

SQL1159 Initialization error with DB .NET Data Provider, reason code 7

Trying to connect from my .Net application I get the following exception:

Could not load file or assembly 'IBM.Data.DB2, Version=9.7.4.4, Culture=neutral, PublicKeyToken=7c307b91aa13d208' or one of its dependencies.

Upvotes: 3

Views: 1117

Answers (2)

barrypicker
barrypicker

Reputation: 10108

For several weeks I was having this same problem and OP -

Could not load file or assembly 'IBM.Data.DB2, Version=9.7.4.4, Culture=neutral, PublicKeyToken=7c307b91aa13d208' or one of its dependencies.

Well, I adjusted my .NET project - set it to be 32 bit only. Now it works. Apparently my IBM DB2 drivers are only 32 bit, and the 64 bit would not load.

Upvotes: 0

RagtimeWilly
RagtimeWilly

Reputation: 5445

The below is all for a .NET 4.5.1 app connecting from a 64-bit machine running Windows 7.

Connecting from Windows:

First install the IBM Data Server Driver Package (DS Driver): http://www-01.ibm.com/support/docview.wss?uid=swg21385217

Intall the 10.5.5 patch via command line with new name:

v10.5fp5_ntx64_dsdriver_EN.exe -n "IBMDBCL2"

Set as default during install, if it fails to set as default then manually update the System Path to point at second install location. In my case: C:\Program Files\ibm\IBM DATA SERVER DRIVER_01\bin

Try to run the following from command line:

testconn40 "Database=<dbname>"; Server=<IP>:<Port>; User ID=<User>; Password=<Password>;"

If you get an error similar to the following:

SQL1159 Initialization error with DB .NET Data Provider, reason code 7

Open regedit.exe and navigate to: HKEY_LOCAL_MACHINE\SOFTWARE\IBM\DB2\InstalledCopies\ and make sure there are folders for IBMDBCL1 & IBMDBCL2. If you're missing IBMDBCL2 then something has gone wrong with install so you'll have to go back to start.

In the InstalledCopies folder make sure there is an entry for 10.5.5.DEF.4 pointing at IBMDBCL2. If there isn't add one. The testconn40 command should pass now.

Connecting from a .Net app using the EntityFramework.IBM.DB2 package:

Install package etc as normal and then try to run the application. If you get the following exception:

FileNotFoundException: Could not load file or assembly 'IBM.Data.DB2, Version=9.7.4.4, Culture=neutral, PublicKeyToken=7c307b91aa13d208' or one of its dependencies.

You're likely missing the assemblies from the GAC. So go to where the drivers are installed:

C:\Program Files\ibm\IBM DATA SERVER DRIVER_01\bin\netf40

And run the following on command line:

gacutil /i IBM.DATA.DB2.dll
gacutil /i IBM.DATA.informix.dll
gacutil /i IBM.DATA.DB2.entity.dll

You should then be able to see assemblies at: C:\Windows\Microsoft.NET\assembly\GAC_64\

If you get:

BadImageFormatException: Could not load file or assembly 'IBM.Data.DB2, Version=9.7.4.4, Culture=neutral, PublicKeyToken=7c307b91aa13d208' or one of its dependencies.

It means that the framework is trying to use the 32-bit DLL. You can either specifically tell Visual Studio to run as x64 or go to the project properties in Visual Studio and make sure that 'Prefer 32-bit' is un-ticked under the Build tab.

Issues with .dbo prefix:

The final problem I had was that Entity Framework automtically adds dbo. as a prefix to all table names. Informix expects the prefix to be the owner of the table (in my case user account used to create the table). In Server Studio you can see the Owner on the General tab of the table properties.

I was able to resolve by adding the following line of code to my context class (note the single quotes):

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.HasDefaultSchema($"'{<DatabaseOwner>}'");

    ...
}

Hopefully this helps anyone else trying to connect to Informix via Entity Framework.

Upvotes: 3

Related Questions