Devart Forums

Posted: **Mon 31 Mar 2014 18:19**

I have comments on tables that I want to retrieve:
COMMENT ON TABLE "public"."MyTable" IS 'My table';

Here's some code which tries to query PostgreSQL's metadata tables.
* The sql query works fine in pgAdmin (database: PostgreSQL 9.3.4, compiled by Visual C++ build 1600, 64-bit)
* The code works fine in NpgSql, but not using the Devart driver v7.0.17.0
* (For clarity using the data reader is omitted)

Code: Select all

            const string sqlCommand = @"SELECT 
    ns.nspname AS SchemaOwner, 
    c.relname AS TableName, 
    d.description AS TableDescription
FROM pg_class c
INNER JOIN pg_namespace ns ON c.relnamespace = ns.oid
INNER JOIN pg_description d ON c.oid = d.objoid
WHERE 
    c.relkind = 'r' AND    
    d.objsubid = 0 AND
    (c.relname = @tableName OR @tableName IS NULL) AND 
    (ns.nspname = @schemaOwner OR @schemaOwner IS NULL)";
            const string connectionString = @"Server=127.0.0.1;User id=postgres;password=***;database=xxx;";
            string tableName = "Martin";
            var schemaOwner = "public";

            using (var connection = new Devart.Data.PostgreSql.PgSqlConnection())
            {
                connection.ConnectionString = connectionString;
                connection.Open();

                using (var command = connection.CreateCommand())
                {
                    command.CommandText = sqlCommand;
                    command.Parameters.AddWithValue("tableName", (object)tableName ?? DBNull.Value);
                    command.Parameters.AddWithValue("schemaOwner", schemaOwner);
                    command.ExecuteReader();
                }
            }

Exception:
Devart.Data.PostgreSql.PgSqlException was unhandled
HResult=-2147467259
Message=column "tablename" does not exist
Source=Devart.Data.PostgreSql
ErrorCode=-2147467259
CallStack=""
ColumnName=""
ConstraintName=""
DataTypeName=""
DetailMessage=""
ErrorSql=SELECT
ns.nspname AS SchemaOwner,
c.relname AS TableName,
d.description AS TableDescription
FROM pg_class c
INNER JOIN pg_namespace ns ON c.relnamespace = ns.oid
INNER JOIN pg_description d ON c.oid = d.objoid
WHERE
c.relkind = 'r' AND
d.objsubid = 0 AND
(c.relname = @tableName OR @tableName IS NULL) AND
(ns.nspname = @schemaOwner OR @schemaOwner IS NULL)
FileName=src\backend\parser\parse_relation.c
Hint=""
InternalPosition=""
InternalQuery=""
LineNumber=2655
Position=312
ProcedureName=errorMissingColumn
SchemaName=""
TableName=""
StackTrace:
at Devart.Data.PostgreSql.af.f(ad A_0)
at Devart.Data.PostgreSql.ad.n()
at Devart.Data.PostgreSql.PgSqlCommand.InternalPrepare(Boolean implicitPrepare, Int32 startRecord, Int32 maxRecords)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at Devart.Data.PostgreSql.PgSqlCommand.ExecuteReader()

Is my query doing something wrong? Where does the "errorMissingColumn" thing come from, when it doesn't occur in NpgSql?

Thanks

Posted: **Wed 02 Apr 2014 13:05**

dotConnect for PostgreSQL has two types of named parameters. The difference between them consists in prefixes ':' and '@'. The name of the PgSqlParameter object in the collection of the command should contain the '@' prefix if parameter in CommandText is used with the '@' prefix.

To fix the issue, add the '@' character before the parameter name. Your code should look like the following:

Code: Select all

command.Parameters.AddWithValue("@tableName", (object)tableName ?? DBNull.Value);
command.Parameters.AddWithValue("@schemaOwner", schemaOwner);

Devart Forums

PgSqlException: Error missing column

PgSqlException: Error missing column

Re: PgSqlException: Error missing column