Forum > Databases
[solved] SQL - IBX - "returning" - primary key
korba812:
I used this along with MDO (another fork of IBX). Try adding the return parameter:
--- Code: SQL [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---INSERT INTO tbtrades (zukonto, STATUS_ ,POSITION_ ,FK_KONTRAKT,NAME_,COMM ,ERGEBNIS,ENTRY_PRICE,ENTRY_DATE,EXIT_DATE,ENTRY_TIME,EXIT_TIME,STOP,WORST,TARGET,BEST,PL,MARGIN) VALUES (:zuKonto,:STATUS_,:POSITION_ ,:FK_KONTRAKT,:NAME_,:COMM ,:ERGEBNIS,:ENTRY_PRICE,:ENTRY_DATE,:EXIT_DATE,:ENTRY_TIME,:EXIT_TIME,:STOP,:WORST,:TARGET,:BEST,:PL,:MARGIN) returning id_trade INTO :somevarname;'then try to read from the parameters as I showed earlier:
--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---idEintrag:=IBQuery_TradeInDB.QInsert.ParamByName('somevarname').AsInteger;
rvk:
Returning should work fine but we need to see more code.
Besides the actual INSERT sql (which contains the returning) we also need to see what component is used and if open is used or execsql.
Everything else is just guesswork.
At least TIBSQL supports returning (not sure about TIBQuery at the moment).
tonyw:
--- Quote from: rvk on December 06, 2022, 07:15:59 pm ---Returning should work fine but we need to see more code.
Besides the actual INSERT sql (which contains the returning) we also need to see what component is used and if open is used or execsql.
Everything else is just guesswork.
At least TIBSQL supports returning (not sure about TIBQuery at the moment).
--- End quote ---
The returning clause is supported by TIBQuery, etc.
You do not use the INTO clause with IBX
The error message implies that you have no field defined called "id_trade". Checlk your select SQL to ensure that id_trade is included in the column list.
rvk:
--- Quote from: tonyw on December 06, 2022, 11:56:12 pm ---The error message implies that you have no field defined called "id_trade". Checlk your select SQL to ensure that id_trade is included in the column list.
--- End quote ---
For Firebird at least, the returning columns do not need to be in the inserted columns.
Maybe the TIBQuery does need it in the SELECT statement? Why?
I can only imagine it needs it when TIBQuery.Open is used (because of the open table fields).
But if TIBQuery.ExecSql is used with INSERT in SQL then it shouldn't be needed.
That's why I wanted to see more code. It's important how the components are used.
--- Quote ---Description: An INSERT statement adding at most one row may optionally include a RETURNING clause in order to return values from the inserted row. The clause, if present, need not contain all of the insert columns and may also contain other columns or expressions. The returned values reflect any changes that may have been made in BEFORE tiggers, but not those in AFTER triggers.
--- End quote ---
https://firebirdsql.org/refdocs/langrefupd21-insert.html
tonyw:
--- Quote from: rvk on December 07, 2022, 12:15:50 am ---
--- Quote from: tonyw on December 06, 2022, 11:56:12 pm ---The error message implies that you have no field defined called "id_trade". Checlk your select SQL to ensure that id_trade is included in the column list.
--- End quote ---
For Firebird at least, the returning columns do not need to be in the inserted columns.
Maybe the TIBQuery does need it in the SELECT statement? Why?
I can only imagine it needs it when TIBQuery.Open is used (because of the open table fields).
--- End quote ---
Because when an insert/update/delete query is executed (using an internal TIBSQL component), if the exec returns one or more columns then TIBCustomDataset.UpdateRecordFromQuery is called. This runs through the singleton row returned by the TIBSQL and if the a returned column's aliasname matches a fieldname then the returned value is used to update the field value. i.e. the column name used in returning clause must match a fieldname if the value is to be returned and saved in the current row.
Checking the code, if the alias name does not match a fieldname then it is silently ignored. Hence forget my original suggestion as to the problem being in the select sql. The error message probably comes from Firebird complaining that id_trade does not exist in the table tbtrades.
Navigation
[0] Message Index
[#] Next page
[*] Previous page