Subject: [postgresql-interfaces/psqlodbc] issue #102: Statement using WITH and INSERT does not work when UseDeclareFetch=1

From: "siga0984 (@siga0984)" <[email-masked]>
Date: Mon, 07 Apr 2025 20:06:29 +0000

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

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 08 Apr 2025 14:09:20 +0000

Do you have log files from the server?

Dave

---

From: "siga0984 (@siga0984)" <[email-masked]>
Date: Tue, 08 Apr 2025 17:44:38 +0000

> Do you have log files from the server?

I'm looking for it 😄

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 08 Apr 2025 17:46:47 +0000

FYI, I strongly suspect that multiple statements may not be used with a cursor

---

From: "siga0984 (@siga0984)" <[email-masked]>
Date: Tue, 08 Apr 2025 18:07:14 +0000

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

```

---

From: "siga0984 (@siga0984)" <[email-masked]>
Date: Tue, 08 Apr 2025 18:17:26 +0000

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.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 08 Apr 2025 18:19:08 +0000

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

---

From: "siga0984 (@siga0984)" <[email-masked]>
Date: Tue, 08 Apr 2025 18:36:19 +0000

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

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 08 Apr 2025 18:41:59 +0000

you could use limit in your query

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 08 Apr 2025 18:42:32 +0000

> 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 ro…

it's not just libpq that does this. This is how the protocol works.

---

From: "siga0984 (@siga0984)" <[email-masked]>
Date: Tue, 08 Apr 2025 20:05:14 +0000

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,