failed to find conversion function from unknown to integer

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
ZZZ_Projects
Posts: 4
Joined: Wed 21 Sep 2016 01:14

failed to find conversion function from unknown to integer

Post by ZZZ_Projects » Sun 21 Jan 2018 21:27

Version: 7.10.1061.0

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

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: failed to find conversion function from unknown to integer

Post by Pinturiccio » Wed 24 Jan 2018 15:38

We have reproduced the issue. We will investigate it and post here about the results as soon as possible.

ZZZ_Projects
Posts: 4
Joined: Wed 21 Sep 2016 01:14

Re: failed to find conversion function from unknown to integer

Post by ZZZ_Projects » Thu 08 Feb 2018 13:14

Hello Pinturiccio,

Do you have an ETA on this issue?

Best Regards,

Jonathan

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: failed to find conversion function from unknown to integer

Post by Pinturiccio » Fri 09 Feb 2018 15:49

This is a designed behavior. The thing is that dotConnect for PostgreSQL creates a prepared (or compiled) version of the command on the server. During prepare, the parameter type сan't be determined on the assumption of the SQL query - it is a core of the error.
Npgsql always executes commands without preparation. You can receive the same behavior in dotConnect for PostgreSQL by adding the following text to your connection string:

Code: Select all

UnpreparedExecute=true
For more information, please refer to
https://www.devart.com/dotconnect/postg ... ecute.html
https://www.devart.com/dotconnect/postg ... ecute.html

Post Reply