TOraQuery: Parameters
Posted: Fri 04 May 2012 17:33
Double clicking the component brings up the editor to define statements.
Here I've defined a SELECT statement with 4 parameters that show up in the parameters tab.
1) Then I've defined an INSERT and UPDATE statement. These also have parameters, but they don't appear in the parameters tab. Why not? Is there a convenient way to set the parameters, programmatically at runtime (à la TOraQuery.ParamByName(...))? Right now I'm using an invisible control, tied to the field, and before the update it reads the value from there.
2) In the INSERT, I'm using RETURNING to get the new id from the pre-insert trigger. After I call Post(), how can I access the returned value? The query get's executed and the row inserted, but calling ParamByName('NEW_ID') fails (EDataBaseError: Not found field corresponding parameter NEW_ID).
3) In the SELECT statement, if I add NVL around the column name, then I get an error (EDatabaseError: cb_has_been_fixed: Field 'HAS_BEEN_FIXED' not found). Is there a workaround for this?
Thanks for your assistance. Let me know if you need more information.
Here I've defined a SELECT statement with 4 parameters that show up in the parameters tab.
1) Then I've defined an INSERT and UPDATE statement. These also have parameters, but they don't appear in the parameters tab. Why not? Is there a convenient way to set the parameters, programmatically at runtime (à la TOraQuery.ParamByName(...))? Right now I'm using an invisible control, tied to the field, and before the update it reads the value from there.
2) In the INSERT, I'm using RETURNING to get the new id from the pre-insert trigger. After I call Post(), how can I access the returned value? The query get's executed and the row inserted, but calling ParamByName('NEW_ID') fails (EDataBaseError: Not found field corresponding parameter NEW_ID).
Code: Select all
QueryProspectDetail.Post;
// store the new download id
download_id := QueryProspectDetail.ParamByName('NEW_ID').AsInteger;
Code: Select all
INSERT INTO DSTEINER.DOWNLOAD_PLANED
(URL_WITH_VARIABLES, HAS_DOWNLOAD_ROUTINE, RETRY_HOURS, RETRY_DAYS, INTERVAL_DAYS, START_DATETIME)
VALUES
(:URL_WITH_VARIABLES, :HAS_DOWNLOAD_ROUTINE, :RETRY_HOURS, :RETRY_DAYS, :INTERVAL_DAYS, :START_DATETIME)
RETURNING download_id INTO :NEW_ID;Code: Select all
SELECT
NVL(h.has_been_fixed, 'N')
[...]