Cannot connect with PostgreSQL

Posts   
 
    
caffreys
User
Posts: 65
Joined: 25-Jan-2009
# Posted on: 13-Oct-2010 11:29:10   

Hi, I'm using LLBL V3.0 (Sept 8th) to build a Postgres project. I have npgsql v2.0.10.0 (Aug 10) and initially had provider exceptions when first connecting to the DB through the designer - even after installing the dll + mono security dll to GAC, so I instead copied the files to C:\Program Files (x86)\Solutions Design\LLBLGen Pro v3.0 and it allowed me to connect and build a project.

The factories section of the config looks like this:

    <DbProviderFactories>
        <!-- PostgreSql -->
        <add name="PostgreSql Client Data Provider"
         invariant="Npgsql"
         description=".Net Framework Data Provider for PostgreSql"
         type="Npgsql.NpgsqlFactory, Npgsql, Version=2.0.10.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" />
    </DbProviderFactories>

Once the project is built i've tried to build a client app but all the adapter operations throw the same exception:

[System.TypeInitializationException] = {"The type initializer for 'SD.LLBLGen.Pro.DQE.PostgreSql.DynamicQueryEngine' threw an exception."}

InnerException = {"Unable to find the requested .Net Framework Data Provider. It may not be installed."}

I've double-checked the GAC:

gacutil /l npgsql Microsoft (R) .NET Global Assembly Cache Utility. Version 4.0.30319.1 Copyright (c) Microsoft Corporation. All rights reserved.

The Global Assembly Cache contains the following assemblies: npgsql, Version=2.0.10.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7, pr ocessorArchitecture=MSIL

Number of items = 1

I've copied the DLLs to the bin folder and other possible locations but nothing seems to work.

What else can I do to "install" the provider?

Thanks.

caffreys
User
Posts: 65
Joined: 25-Jan-2009
# Posted on: 13-Oct-2010 11:53:45   

Figured it out:

You need to edit the machine config in your version of Net Framework. I'm using v4.0 so for me the file is C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config\machine.config

In there you'll find the <system.data> section which will look something like this:

<system.data>
    <DbProviderFactories>
        <add name="Microsoft SQL Server Compact Data Provider" invariant="System.Data.SqlServerCe.3.5" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=3.5.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
        <add name="PostgreSql Client Data Provider" invariant="Npgsql" description=".Net Framework Data Provider for PostgreSql" type="Npgsql.NpgsqlFactory, Npgsql, Version=2.0.10.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" />
    </DbProviderFactories>
</system.data>

You just need to make sure the version number matches your npgsql version

caffreys
User
Posts: 65
Joined: 25-Jan-2009
# Posted on: 13-Oct-2010 12:04:31   

As usual, not so straightforward....

Although this works on my dev machine, once I build a setup project and run on a test machine I find the same problem - no connect.

I don't really understand these provider configs. With V2.6 LLBLGen I could just create a project and off I go. Do I now have to set-up the provider factories manually on all the client machines?

Why can't I just ship the dlls with the assembly?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 13-Oct-2010 14:04:56   

Your application has a .config file, be it web.config or app.config. In that config file, add the factory setting exactly like we did in llblgenpro.exe.config. After that, you compile your app, and if it's a desktop app, you have a ...exe.config file (which is a copy of the app.config of your project) with the factory settings.

Frans Bouma | Lead developer LLBLGen Pro
caffreys
User
Posts: 65
Joined: 25-Jan-2009
# Posted on: 14-Oct-2010 05:35:35   

Thanks Otis, I placed the following in app.config and tested on the client machines and all worked fine. For some reason I had to manually copy the npgsql.dll & mono.security.dll as the installer project did not pick these up as dependencies even though they are no longer in the GAC and i've selected copy local, but anyway, for now it works....

<system.data>
    <DbProviderFactories>
        <!-- PostgreSql -->
        <add name="PostgreSql Client Data Provider"
         invariant="Npgsql"
         description=".Net Framework Data Provider for PostgreSql"
         type="Npgsql.NpgsqlFactory, Npgsql, Version=2.0.10.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" />
    </DbProviderFactories>
</system.data>

But now in my dev machine when I run in VS I get the same exception as above with the following message:

An error occurred creating the configuration section handler for system.data: Column 'InvariantName' is constrained to be unique. Value 'Npgsql' is already present. (C:\Projects\CDS\Bin\Debug\CDS.vshost.exe.Config line 54)

So clients work fine but not now my dev machine.

I googled and it seems others have had similar problems when referencing duplicate providers. I don't have that issue here.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 15-Oct-2010 11:12:31   

Do you by any change have defined the factory twice somewhere? I.e. in the machine.config file and locally in the config file of your application? It's about the invariant name 'Npgsql', which is the name used by our code to obtain the factory.

Frans Bouma | Lead developer LLBLGen Pro
Chaoyster
User
Posts: 40
Joined: 23-Mar-2011
# Posted on: 07-Jun-2011 20:14:29   

I have the same issue though and got figured it out, the problem for this in llblgen.exe.config. the npgsql entry has been added from preinstalled. it is cool for LLGLGen designer to get the schema from PG. but if you have .net code and try to use v3.1 runtime libraries in your code on the same computer as using designer. it requires you to add the npgsql entry in machine.config and remove the predefined on in llblgen.exe.config. now both llblgen and your .net code will use the global entry which sits in machine.config. otherwise you will have error message and say the invariant name exists already from your designer.

cheers.

MTrinder
User
Posts: 1461
Joined: 08-Oct-2008
# Posted on: 07-Jun-2011 20:17:15   

Thanks for the update.

Matt

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 08-Jun-2011 09:33:20   

Chaoyster wrote:

I have the same issue though and got figured it out, the problem for this in llblgen.exe.config. the npgsql entry has been added from preinstalled. it is cool for LLGLGen designer to get the schema from PG. but if you have .net code and try to use v3.1 runtime libraries in your code on the same computer as using designer. it requires you to add the npgsql entry in machine.config and remove the predefined on in llblgen.exe.config. now both llblgen and your .net code will use the global entry which sits in machine.config. otherwise you will have error message and say the invariant name exists already from your designer.

cheers.

The entry is in the llblgenpro.exe.config because npgsql didn't install an entry in the machine.config file. Did you add that manually or is the npgsql installer clever enough nowadays to add that entry?

Frans Bouma | Lead developer LLBLGen Pro
Chaoyster
User
Posts: 40
Joined: 23-Mar-2011
# Posted on: 08-Jun-2011 14:41:50   

I understand you have the settings in exe.config and try to be standalone. Maybe the LLBLGen installer should be smarter enough and more friendly to add the settings into machine.config. or at least to pop up and ask during the installation. smile

The setting is manually added into machine.config. We didn't use npgsql installer(I don't know if it exists). The npgsql.dll(2.0.11.0) is manually GAC(with powershell script). Since our project is referenced by a bunch of parent projects/solutions, we don't want to use app.config in DAL. In fact, we disabled the app.config generated task in designer. In our case, machine.config is the perfect spot for it to live.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 09-Jun-2011 10:40:53   

Chaoyster wrote:

I understand you have the settings in exe.config and try to be standalone. Maybe the LLBLGen installer should be smarter enough and more friendly to add the settings into machine.config. or at least to pop up and ask during the installation. smile

And then do what, exactly? simple_smile We're not going to install that provider nor move it to the GAC, nor modify the machine.config file, as the npgsql provider might be updated later and the machine.config file is forgotten (as the installer modified it). So we documented it instead.

The setting is manually added into machine.config. We didn't use npgsql installer(I don't know if it exists). The npgsql.dll(2.0.11.0) is manually GAC(with powershell script). Since our project is referenced by a bunch of parent projects/solutions, we don't want to use app.config in DAL. In fact, we disabled the app.config generated task in designer. In our case, machine.config is the perfect spot for it to live.

I agree, though the best way is that the npgsql installer puts in these definitions instead. Together with firebird's installer they don't do that, hence we documented the necessity. It's all we can do, really.

Frans Bouma | Lead developer LLBLGen Pro
Chaoyster
User
Posts: 40
Joined: 23-Mar-2011
# Posted on: 09-Jun-2011 15:12:41   

If LLBLGen is depending on firebird or postgreSQL installer, then I would suggest to remove the settings from LLBLGen.exe.config. and let those installers take care of it completely, rather than just some drivers settings being added and others didn't, why don't you have Oracle driver settings in your LLBLGen.exe.config file? What will happen if the designer connects to Oracle database?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 09-Jun-2011 16:00:16   

Chaoyster wrote:

If LLBLGen is depending on firebird or postgreSQL installer, then I would suggest to remove the settings from LLBLGen.exe.config. and let those installers take care of it completely, rather than just some drivers settings being added and others didn't, why don't you have Oracle driver settings in your LLBLGen.exe.config file? What will happen if the designer connects to Oracle database?

All ado.net providers install a line in machine.config except the firebird and npsql installers, which don't do that. If we avoid doing this, people will complain it doesn't work, and we have to point them to the docs. This is only necessary for npgsql and firebird, the rest works out of the box as their installers do what they should do: install the factory in the machine.config.

It's a problem, but as the maintainers of the installers don't want to install the line in machine.config (why is beyond me, everyone else does this), we provide a definition in the config file so people can at least get started.

Frans Bouma | Lead developer LLBLGen Pro