cannot get returning data from insert statement when in ODBC version3 mode

[freakout42]

cannot get returning data from insert statement when in ODBC version3 mode:

$ /opt/odbc/bin/isql Formax
SQL> insert into emp (ename) values ('g') returning id,ename,job,mgr,hiredate,sal,comm,dept_id;
+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| id        | ename     | job       | mgr       | hiredate  | sal       | comm      | dept_id   |
+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 7945      | g         |           |           |           |           |           |           |
+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
SQLRowCount returns 1
1 rows fetched

$ /opt/odbc/bin/isql -3 Formax
SQL> insert into emp (ename) values ('f') returning id,ename,job,mgr,hiredate,sal,comm,dept_id;
[ISQL]INFO: SQLExecute returned SQL_NO_DATA
+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
|           |           |           |           |           |           |           |           |
+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
SQLRowCount returns 1

i want to use ODBC version3 but i also need the "INSERT ... RETURNING ..." functionalty. what setting / procedure must i use to get the data in ODBC version3?

[lurcher]

On 16/07/2024 10:12, Axel K. Reinhold wrote:

cannot get returning data from insert statement when in ODBC version3 mode:

$ /opt/odbc/bin/isql Formax SQL> insert into emp (ename) values ('g') returning id,ename,job,mgr,hiredate,sal,comm,dept_id; +-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+	id	ename	job	mgr	hiredate	sal	comm	dept_id	+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+	7945	g							+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+ SQLRowCount returns 1 1 rows fetched $ /opt/odbc/bin/isql -3 Formax SQL> insert into emp (ename) values ('f') returning id,ename,job,mgr,hiredate,sal,comm,dept_id; [ISQL]INFO: SQLExecute returned SQL_NO_DATA +-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+

+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+ +-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+ SQLRowCount returns 1 |

i want to use ODBC version3 but i also need the "INSERT ... RETURNING ..." functionalty. what setting / procedure must i use to get the data in ODBC version3?

— Reply to this email directly, view it on GitHub https://github.com/lurcher/unixODBC/issues/171, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABYK62MPNR37UPVSZNQLUHDZMTPYZAVCNFSM6AAAAABK6E3BMSVHI2DSMVQWIX3LMV43ASLTON2WKOZSGQYTANRTGI2TOOA. You are receiving this because you are subscribed to this thread.Message ID: @.***>

Offhand, no idea. What does driver manager traces show in the two cases?

[freakout42]

here the two logs. v2.log v3.log

[freakout42]

the new record is inserted in both cases!

[lurcher]

On 16/07/2024 11:29, Axel K. Reinhold wrote:

here the two logs. v2.log https://github.com/user-attachments/files/16247274/v2.log v3.log https://github.com/user-attachments/files/16247275/v3.log

— Reply to this email directly, view it on GitHub https://github.com/lurcher/unixODBC/issues/171#issuecomment-2230555782, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABYK62PHTNLPWXI5GIDU6RTZMTYW3AVCNFSM6AAAAABK6E3BMSVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDEMZQGU2TKNZYGI. You are receiving this because you commented.Message ID: @.***>

Ok,

So in the case of ODBC v3, the SQLExecute is returning from the driver SQL_NO_DATA. That's putting the statement into State S4, that's Executed, not State S5 to S7, that's Cursor. isql then calls SQLColAttribute which returns the error 24000 as there is no cursor.

From what I can see, the problem is that the driver SQLExecute is returning SQL_NO_DATA when it shouldn't when its set to ODBC 3.