PostgreSQL problem

Mar 28, 2014 at 11:43 PM
Before anything, I wanna say I'm really impressed with this project. You made a really nice tool for developers! congratulations!!.

I tested this project with a MySQL database and everything worked like a charm, but when I tested a PostgresSQL database... the ReadAll() method did not work and it returns this error:

column "tablename" does not exist

I think the problem is happening because PostgreSQL by default folds all unquoted named to lowercase. So, when you execute something like:

select TableName from TableInformation (Just as example... )

Postgres translates it to:

select tablename from tableinformation

I would appreciate any help, because maybe I'm doing something wrong...

This is my code:
        string providername = "Devart.Data.PostgreSql";
        string postgreConnection = @"Server=localhost;Userid=postgres;password=****;database=test;";
        var dbReader = new DatabaseReader(postgreConnection, providername);

        dbReader.Owner = "public";
        var schema = dbReader.ReadAll();
Thanks for your help.
Coordinator
Mar 31, 2014 at 4:39 PM
Thanks for the congrats- I'm glad you find it useful!

The error has these details: "column "tablename" does not exist" is recorded in procedure "errorMissingColumn" line number 2655, source src\backend\parser\parse_relation.c

It occurs in two places, while trying to get table descriptions, and column descriptions.

It only occurs in Devart. Npgsql works okay.

There is another error because the Devart server version is not in standard version format (Devart says "PostgreSQL 9.3.4, compiled by Visual C++ build 1600, 64-bit", Npgsql says "9.3.4"). I've checked in a patch for this (and it error traps the description code, as a temporary patch for now).

I'll look further - maybe different SQL will work better, or Devart needs a fix!
Mar 31, 2014 at 8:32 PM
Thanks for your answer, you are totally right. I downloaded the source code to check if I could fix the problem and the error was happening in the TableDescription(string tableName) function. I took your advice about using Npgsql, I added the Npgsql referece and added this to my project:

<DbProviderFactories>
<add name="Npgsql Data Provider" invariant="Npgsql" description="Data Provider for PostgreSQL"type="Npgsql.NpgsqlFactory, Npgsql" /></DbProviderFactories>

but now I'm having another problem:

[NullReferenceException:]
Npgsql.NpgsqlSchema.GetMetaDataCollections() +223
Npgsql.NpgsqlConnection.GetSchema(String collectionName, String[] restrictions) +437
Npgsql.NpgsqlConnection.GetSchema(String collectionName) +46
DatabaseSchemaReader.SchemaReader.MetadataCollections(DbConnection connection) +44
DatabaseSchemaReader.SchemaReader.SchemaCollectionExists(DbConnection connection, String name) +72
DatabaseSchemaReader.SchemaReader.Users() +162
DatabaseSchemaReader.DatabaseReader.AllUsers() +119
DatabaseSchemaReader.DatabaseReader.ReadAll() +74

have you seen something like this before? because it seems like Npgsql is getting a null value.

Devart read the users without problem but Npgslq doesn't... and I'm usign the same DataBase.

Thanks for your help.
Coordinator
Apr 1, 2014 at 7:00 AM
It's trying to read the metadata collections from the driver, using DbConnection.GetSchema(DbMetaDataCollectionNames.MetaDataCollections).
So the input string ("MetaDataCollections") is part of the .net framework, and the ADO driver should be implementing this method.

Historically, NpgSql had some problems fully implementing this part of ADO- I already have code to trap a "NotSupportedException" when trying to load the DataTypes. The version I use is working OK. What version are you using?
Coordinator
Apr 1, 2014 at 7:21 PM
Hmm, the current Npgsql v2.1.2.0 from Nuget shows the error.
My version was v2.0.14.3 and that was working.
Just checked in a new version of DatabaseSchemaReader that traps and ignores the error (it still loads some useful schema metadata). So both PostgreSql drivers are now supported in spite of their problems :)
Marked as answer by AmilcarSerrano on 4/2/2014 at 3:46 PM
Apr 2, 2014 at 6:15 PM
Thanks!!! the project works fine now :D sorry that I couldn't reply yesterday, I had to work in other project.

But I tested your changes today and everything works like a charm!! :D now I just have to improve the database configuration and the string connection because I'm getting a lot of tables that I'm not sure if I created. But that's a problem on my side :)

I really appreciate your help! Thanks!
Coordinator
Apr 2, 2014 at 6:41 PM
Npgsql has just accepted a pull request from me to fix their GetSchema error. Their next version should work perfectly!
Marked as answer by AmilcarSerrano on 4/2/2014 at 3:46 PM
Apr 2, 2014 at 11:52 PM
Those are great news! I'm really glad to hear that! :D Be proud of this project, You created a really useful library. congratulations and keep going like this!

Thanks a lot for your support and patience, maybe some day I'll be able to return the favor. Best wishes!