postgresql-interfaces/psqlodbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feed[postgresql-interfaces/psqlodbc] issue #102: Statement using WITH and INSERT does not work when UseDeclareFetch=1
11+ messages / 2 participants
[nested] [flat]
* [postgresql-interfaces/psqlodbc] issue #102: Statement using WITH and INSERT does not work when UseDeclareFetch=1
@ 2025-04-07 20:06 "siga0984 (@siga0984)" <[email protected]>
0 siblings, 0 replies; 11+ messages in thread
From: siga0984 (@siga0984) @ 2025-04-07 20:06 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
The below example works fine, since I don't set UseDeclareFetch=1 in Odbc.ini
First, create the necessary tables.
`CREATE TABLE ORIGEM(CAMPO char(4) )`
`CREATE TABLE DESTINO(CAMPO char(4) )`
`INSERT INTO ORIGEM(CAMPO) values ('0001')`
Then, using (Windows or Linux) ODBC thought any odbc client program ( even UnixODBC ISQL) , execute the following statement:
`WITH TRB(CAMPO) AS ( SELECT CAMPO FROM ORIGEM ) INSERT INTO DESTINO(CAMPO) SELECT CAMPO FROM TRB`
If ODBC.INI configuration UseDeclareFetch is zero, it works fine, as expected. But, if `UseDeclareFetch=1`, the result is :
`DIAG [42601] ERRO: erro de sintaxe em ou próximo a "INSERT";`
`Error while executing the query (1) `
This example is a reduced form, the smallest query I've test and got the issue. Tested with previous ODBC previous versions also fails, even the last one (17.00.0004). Let me know if I can help somehow.
Best regards,
Julio Wittwer
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #102: Statement using WITH and INSERT does not work when UseDeclareFetch=1
@ 2025-04-08 14:09 ` "davecramer (@davecramer)" <[email protected]>
9 siblings, 0 replies; 11+ messages in thread
From: davecramer (@davecramer) @ 2025-04-08 14:09 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
Do you have log files from the server?
Dave
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #102: Statement using WITH and INSERT does not work when UseDeclareFetch=1
@ 2025-04-08 17:44 ` "siga0984 (@siga0984)" <[email protected]>
9 siblings, 0 replies; 11+ messages in thread
From: siga0984 (@siga0984) @ 2025-04-08 17:44 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
> Do you have log files from the server?
>
> Dave
I'm looking for it 😄
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #102: Statement using WITH and INSERT does not work when UseDeclareFetch=1
@ 2025-04-08 17:46 ` "davecramer (@davecramer)" <[email protected]>
9 siblings, 0 replies; 11+ messages in thread
From: davecramer (@davecramer) @ 2025-04-08 17:46 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
FYI, I strongly suspect that multiple statements may not be used with a cursor
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #102: Statement using WITH and INSERT does not work when UseDeclareFetch=1
@ 2025-04-08 18:07 ` "siga0984 (@siga0984)" <[email protected]>
9 siblings, 0 replies; 11+ messages in thread
From: siga0984 (@siga0984) @ 2025-04-08 18:07 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
I think this is the important slice ... ODBC tries do declare a cursor with the entire statement ...
```
2025-04-08 15:00:09.516 -03 [30972] DEPURAÇÃO: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
2025-04-08 15:00:09.516 -03 [30972] COMANDO: BEGIN;declare "SQL_CUR000001FF38302570" cursor with hold for WITH TRB(CAMPO) AS ( SELECT CAMPO FROM ORIGEM ) INSERT INTO DESTINO(CAMPO) SELECT CAMPO FROM TRB;fetch 100 in "SQL_CUR000001FF38302570"
2025-04-08 15:00:09.516 -03 [30972] ERRO: erro de sintaxe em ou pr?imo a "INSERT" no caractere 110
2025-04-08 15:00:09.516 -03 [30972] COMANDO: BEGIN;declare "SQL_CUR000001FF38302570" cursor with hold for WITH TRB(CAMPO) AS ( SELECT CAMPO FROM ORIGEM ) INSERT INTO DESTINO(CAMPO) SELECT CAMPO FROM TRB;fetch 100 in "SQL_CUR000001FF38302570"
2025-04-08 15:00:09.524 -03 [26044] DEPURAÇÃO: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
2025-04-08 15:00:09.524 -03 [26044] COMANDO: delete from top_param where param_session = 26044
2025-04-08 15:00:09.525 -03 [30972] DEPURAÇÃO: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
2025-04-08 15:00:09.525 -03 [30972] COMANDO: delete from top_param where param_session = 30972
2025-04-08 15:00:09.526 -03 [26044] DEPURAÇÃO: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0 (used)
2025-04-08 15:00:09.526 -03 [26044] COMANDO: delete from top_param where param_session = 26044
```
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #102: Statement using WITH and INSERT does not work when UseDeclareFetch=1
@ 2025-04-08 18:17 ` "siga0984 (@siga0984)" <[email protected]>
9 siblings, 0 replies; 11+ messages in thread
From: siga0984 (@siga0984) @ 2025-04-08 18:17 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
If there is a way to ODBC "decide" that for some kind of statements the cursor approach does not fit, this will be awesome. But, I can also work around it just turning off UseDeclareFetch.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #102: Statement using WITH and INSERT does not work when UseDeclareFetch=1
@ 2025-04-08 18:19 ` "davecramer (@davecramer)" <[email protected]>
9 siblings, 0 replies; 11+ messages in thread
From: davecramer (@davecramer) @ 2025-04-08 18:19 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
> If there is a way to ODBC "decide" that for some kind of statements the cursor approach does not fit, this will be awesome. But, I can also work around it just turning off UseDeclareFetch.
Well that's sort of exactly what the `UseDeclareFetch` is telling it to do. I'm surprised it is using `WITH HOLD` cursors.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #102: Statement using WITH and INSERT does not work when UseDeclareFetch=1
@ 2025-04-08 18:36 ` "siga0984 (@siga0984)" <[email protected]>
9 siblings, 0 replies; 11+ messages in thread
From: siga0984 (@siga0984) @ 2025-04-08 18:36 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
So, if I undestand it right, the ODBC allows you to limit the amout of lines to be retrieved, but since ODCB wraps libpq, and libpq behavior is "get entire result set at once" or "get result set row by row ", the only way to accomplish that is declare and open a cursor, and fetch the amount of data desired ( default 100 rows ) ?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #102: Statement using WITH and INSERT does not work when UseDeclareFetch=1
@ 2025-04-08 18:41 ` "davecramer (@davecramer)" <[email protected]>
9 siblings, 0 replies; 11+ messages in thread
From: davecramer (@davecramer) @ 2025-04-08 18:41 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
you could use limit in your query
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #102: Statement using WITH and INSERT does not work when UseDeclareFetch=1
@ 2025-04-08 18:42 ` "davecramer (@davecramer)" <[email protected]>
9 siblings, 0 replies; 11+ messages in thread
From: davecramer (@davecramer) @ 2025-04-08 18:42 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
> So, if I undestand it right, the ODBC allows you to limit the amout of lines to be retrieved, but since ODCB wraps libpq, and libpq behavior is "get entire result set at once" or "get result set row by row ", the only way to accomplish that is declare and open a cursor, and fetch the amount of data desired ( default 100 rows ) ?
it's not just libpq that does this. This is how the protocol works.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #102: Statement using WITH and INSERT does not work when UseDeclareFetch=1
@ 2025-04-08 20:05 ` "siga0984 (@siga0984)" <[email protected]>
9 siblings, 0 replies; 11+ messages in thread
From: siga0984 (@siga0984) @ 2025-04-08 20:05 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
If you think it's not worth the effort, leave it this way. In any case, it would be interesting to have a note with more clarifications in the documentation of the "UseDeclareFetch" configuration, to inform that there may be restrictions on the execution of some SQL statements 😃
And, thank you for your time and attention 👍
Best regards,
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2025-04-08 20:05 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-04-07 20:06 [postgresql-interfaces/psqlodbc] issue #102: Statement using WITH and INSERT does not work when UseDeclareFetch=1 "siga0984 (@siga0984)" <[email protected]>
2025-04-08 14:09 ` "davecramer (@davecramer)" <[email protected]>
2025-04-08 17:44 ` "siga0984 (@siga0984)" <[email protected]>
2025-04-08 17:46 ` "davecramer (@davecramer)" <[email protected]>
2025-04-08 18:07 ` "siga0984 (@siga0984)" <[email protected]>
2025-04-08 18:17 ` "siga0984 (@siga0984)" <[email protected]>
2025-04-08 18:19 ` "davecramer (@davecramer)" <[email protected]>
2025-04-08 18:36 ` "siga0984 (@siga0984)" <[email protected]>
2025-04-08 18:41 ` "davecramer (@davecramer)" <[email protected]>
2025-04-08 18:42 ` "davecramer (@davecramer)" <[email protected]>
2025-04-08 20:05 ` "siga0984 (@siga0984)" <[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