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;
BigInt problem in PgSqlCommand
Re: BigInt problem in PgSqlCommand
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: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".
This code generates "OID out of range" in our environment.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;
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).
Re: BigInt problem in PgSqlCommand
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.
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.