BigInt problem in PgSqlCommand

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
che1972
Posts: 8
Joined: Thu 04 Sep 2014 12:02

BigInt problem in PgSqlCommand

Post by che1972 » Fri 12 Sep 2014 15:03

I tried a very similar code like the code below:

long oid = 5123456789;
PgSqlCommand cmd = new PgSqlCommand();
cmd.Connection = conn;
cmd.CommandText = "select id from table_a where oid = :OID";
cmd.Parameters.Clear();
cmd.Parameters.Add("OID", PgSqlType.BigInt);
cmd.Parameters["OID"].Value = oid;
object tmp = cmd.ExecuteScalar();

The connection is a working connection, that's sure. The problem is that I get an OverflowException when executing the last line with ExecuteScalar(). More details about the exception: "The value for Int32 was too big or too small".
I found out that the driver had problems to build the command. It seems that there is a problem to put the value 5123456789 into a bigint.
What is wrong here?
My workaround for now: When I don't use parameters, it works:
cmd.CommandText = "select id from table_a where oid = " + oid;

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: BigInt problem in PgSqlCommand

Post by Shalex » Tue 16 Sep 2014 13:42

che1972 wrote:The problem is that I get an OverflowException when executing the last line with ExecuteScalar(). More details about the exception: "The value for Int32 was too big or too small".
We are investigating the issue why "The value for Int32 was too big or too small" is generated instead of "OID out of range".
che1972 wrote:My workaround for now: When I don't use parameters, it works:
long oid = 5123456789;
cmd.CommandText = "select id from table_a where oid = " + oid;
This code generates "OID out of range" in our environment.
The reason is described at http://www.postgresql.org/docs/9.3/stat ... e-oid.html:
"The oid type is currently implemented as an unsigned four-byte integer."
Max value of unsigned four-byte integer is equal to 4294967295 (which is less than 5123456789).

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: BigInt problem in PgSqlCommand

Post by Shalex » Mon 22 Sep 2014 14:18

1. The "OID out of range" error is generated by PostgreSQL server. This error occurs when 5123456789 is used in a command text or in the parameter of a command with cmd.UnpreparedExecute=True.
2. When 5123456789 is used in the parameter of a command with cmd.UnpreparedExecute=False (default value), the value of the parameter is validated before passing it to the server.

Post Reply