Subject: [postgresql-interfaces/psqlodbc] issue #82: Is this expected a bind to BIGINT to be surrounded by quotes?

From: "JacoboSanchez (@JacoboSanchez)" <[email-masked]>
Date: Fri, 13 Dec 2024 18:47:11 +0000

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](https://github.com/postgresql-interfaces/psqlodbc/blob/72943916325e2443fdb85fa5d1e053d9ef72f683/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

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Sun, 15 Dec 2024 12:10:22 +0000

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.

---

From: "JacoboSanchez (@JacoboSanchez)" <[email-masked]>
Date: Sun, 15 Dec 2024 21:05:02 +0000

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
```

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 19 Dec 2024 16:01:58 +0000

So you are using setString to set a bigInt ?

---

From: "JacoboSanchez (@JacoboSanchez)" <[email-masked]>
Date: Thu, 19 Dec 2024 16:40:49 +0000

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 :)

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 20 Dec 2024 13:20:14 +0000

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