Is this expected a bind to BIGINT to be surrounded by quotes?

[JacoboSanchez]

I was testing the folowing:

• Prepare a statement with a parameter

odbct32w        7d60-31c0	ENTER SQLPrepareW 
		HSTMT               0x00000000020DAFA0
		WCHAR *             0x000000000053C320 [      50] "select * from public.testview where longfield < ?"
		SDWORD                    50

odbct32w        7d60-31c0	EXIT  SQLPrepareW  with return code 0 (SQL_SUCCESS)
		HSTMT               0x00000000020DAFA0
		WCHAR *             0x000000000053C320 [      50] "select * from public.testview where longfield < ?"
		SDWORD                    50

• Bind the parameter to BIGINT:

odbct32w        7d60-31c0	ENTER SQLBindParameter 
		HSTMT               0x00000000020DAFA0
		UWORD                        1 
		SWORD                        1 <SQL_PARAM_INPUT>
		SWORD                       99 <SQL_C_DEFAULT>
		SWORD                       -5 <SQL_BIGINT>
		SQLULEN                    0
		SWORD                        0 
		PTR                0x0000000001F30000
		SQLLEN                     0
		SQLLEN *            0x0000000002DD0000

odbct32w        7d60-31c0	EXIT  SQLBindParameter  with return code 0 (SQL_SUCCESS)
		HSTMT               0x00000000020DAFA0
		UWORD                        1 
		SWORD                        1 <SQL_PARAM_INPUT>
		SWORD                       99 <SQL_C_DEFAULT>
		SWORD                       -5 <SQL_BIGINT>
		SQLULEN                    0
		SWORD                        0 
		PTR                0x0000000001F30000
		SQLLEN                     0
		SQLLEN *            0x0000000002DD0000 (4294967293)

When I execute I see that the executed sentence does contain the number quoted. Example (probably not exactly from the same test than previous trace logs):

[14.554]PQsendQuery: 00000000005B4DB0 'BEGIN;declare "SQL_CUR000000000053DB60" cursor with hold for select * from public.testview where longfield< '5545';fetch 10000 in "SQL_CUR000000000053DB60"'

I think the reason for SQL_INTEGER and SQL_SMALLINT not being quoted (or quoted with ::int4) is in convert.c#L5310 but I don't figure out why this is not the same for SQL_BIGINT.

It is not throwing an error but does not seems correct to me to use a text literal there

[davecramer]

interesting. One thing is that if it is quoted then it will automatically cast. That said I doubt there is a reason to cast it.

[JacoboSanchez]

It is automatically cast when using a plain SQL. In JDBC if you use the parameter in the prepared statement and set a String object then an error is thrown due to incompatible types.That JDBC failure was the reason to end up looking at this in ODBC.

The error In JDBC when assigning is the following (only using a parameter at the statement, not with explicit text literal):

operator does not exist: bigint = character varying
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts

[davecramer]

So you are using setString to set a bigInt ?

[JacoboSanchez]

Well it is a bit more complicated

Think on it as a middleware receiving the query from ODBC and generating a delegation to JDBC.

• client prepares a select * from view where field = ? and binds it to bigint 333
• Driver translates it to select * from view where field = '333' (this works on PostgreSQL so there is no bug there)
• I get that query and translate it to PostgreSQL JDBC prepared statement select * from view where field = ? and bind it in the JDBC way by using a setString
• This fails

This is more or less how ended up asking for the reason to format the bigint binding with quotes in ODBC :)

[davecramer]

Ok, the JDBC driver uses V3 protocol so it will attempt to bind that ? to a string. It has no idea that the actual field is a bigint.
It may work if you use simple query mode https://github.com/pgjdbc/pgjdbc/blob/17cac52fe9cb80001cfd1fd72ec2b03fa4c5d64e/pgjdbc/src/main/java/org/postgresql/PGConnection.java#L233

Dave