postgresql-interfaces/psqlodbc GitHub issues and pull requests (mirror)help / color / mirror / Atom feed
[postgresql-interfaces/psqlodbc] issue #82: Is this expected a bind to BIGINT to be surrounded by quotes? 6+ messages / 2 participants [nested] [flat]
* [postgresql-interfaces/psqlodbc] issue #82: Is this expected a bind to BIGINT to be surrounded by quotes? @ 2024-12-13 18:47 "JacoboSanchez (@JacoboSanchez)" <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: JacoboSanchez (@JacoboSanchez) @ 2024-12-13 18:47 UTC (permalink / raw) To: postgresql-interfaces/psqlodbc <[email protected]> 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/conv...) 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 ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #82: Is this expected a bind to BIGINT to be surrounded by quotes? @ 2024-12-15 12:10 "davecramer (@davecramer)" <[email protected]> 4 siblings, 0 replies; 6+ messages in thread From: davecramer (@davecramer) @ 2024-12-15 12:10 UTC (permalink / raw) To: postgresql-interfaces/psqlodbc <[email protected]> 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. ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #82: Is this expected a bind to BIGINT to be surrounded by quotes? @ 2024-12-15 21:05 "JacoboSanchez (@JacoboSanchez)" <[email protected]> 4 siblings, 0 replies; 6+ messages in thread From: JacoboSanchez (@JacoboSanchez) @ 2024-12-15 21:05 UTC (permalink / raw) To: postgresql-interfaces/psqlodbc <[email protected]> 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 ``` ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #82: Is this expected a bind to BIGINT to be surrounded by quotes? @ 2024-12-19 16:01 "davecramer (@davecramer)" <[email protected]> 4 siblings, 0 replies; 6+ messages in thread From: davecramer (@davecramer) @ 2024-12-19 16:01 UTC (permalink / raw) To: postgresql-interfaces/psqlodbc <[email protected]> So you are using setString to set a bigInt ? ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #82: Is this expected a bind to BIGINT to be surrounded by quotes? @ 2024-12-19 16:40 "JacoboSanchez (@JacoboSanchez)" <[email protected]> 4 siblings, 0 replies; 6+ messages in thread From: JacoboSanchez (@JacoboSanchez) @ 2024-12-19 16:40 UTC (permalink / raw) To: postgresql-interfaces/psqlodbc <[email protected]> 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 :) ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #82: Is this expected a bind to BIGINT to be surrounded by quotes? @ 2024-12-20 13:20 "davecramer (@davecramer)" <[email protected]> 4 siblings, 0 replies; 6+ messages in thread From: davecramer (@davecramer) @ 2024-12-20 13:20 UTC (permalink / raw) To: postgresql-interfaces/psqlodbc <[email protected]> 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/... Dave ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-12-20 13:20 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-12-13 18:47 [postgresql-interfaces/psqlodbc] issue #82: Is this expected a bind to BIGINT to be surrounded by quotes? "JacoboSanchez (@JacoboSanchez)" <[email protected]> 2024-12-15 12:10 ` "davecramer (@davecramer)" <[email protected]> 2024-12-15 21:05 ` "JacoboSanchez (@JacoboSanchez)" <[email protected]> 2024-12-19 16:01 ` "davecramer (@davecramer)" <[email protected]> 2024-12-19 16:40 ` "JacoboSanchez (@JacoboSanchez)" <[email protected]> 2024-12-20 13:20 ` "davecramer (@davecramer)" <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox