Using a derived table for insert seem that the parameter doesn’t know about the parameter type specified. The first command worked with Npgsql but throw an error if I execute it with dotConnect PostgreSQL provider.
The second example works fine, but I need to cast every parameter which I would like to avoid since I already pass the parameter type.
Do I’m missing a configuration?
Code: Select all
// Oracle 9.x => Error: "failed to find conversion function from unknown to integer"
// Oracle 10.x => Error: 42804: column "ColumnIdentityPK" is of type integer but expression is of type text
{
var command = connection.CreateCommand();
command.ParameterCheck = true;
command.CommandText = @"
INSERT INTO ""TableWithIntPK""
(""ColumnIdentityPK"", ""ColumnIntTest"")
SELECT ""ColumnIdentityPK"", ""ColumnIntTest"" FROM (
SELECT @0_0 AS ""ColumnIdentityPK"", @0_1 AS ""ColumnIntTest"") AS A;";
var param0 = command.Parameters.Add("@0_0", Devart.Data.PostgreSql.PgSqlType.Int);
param0.Value = 1;
var param1 = command.Parameters.Add("@0_1", Devart.Data.PostgreSql.PgSqlType.Int);
param1.Value = 1;
command.ExecuteNonQuery();
}
// Success
{
var command = connection.CreateCommand();
command.CommandText = @"
INSERT INTO ""TableWithIntPK""
(""ColumnIdentityPK"", ""ColumnIntTest"")
SELECT ""ColumnIdentityPK"", ""ColumnIntTest"" FROM (
SELECT CAST(@0_0 AS INT) AS ""ColumnIdentityPK"", CAST(@0_1 AS INT) AS ""ColumnIntTest"") AS A;";
var param0 = command.Parameters.Add("@0_0", Devart.Data.PostgreSql.PgSqlType.Int);
param0.Value = 1;
var param1 = command.Parameters.Add("@0_1", Devart.Data.PostgreSql.PgSqlType.Int);
param1.Value = 1;
command.ExecuteNonQuery();
}