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();
}
}
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