TOraQuery: Parameters

[cinch]

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

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;

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?

Code: Select all

SELECT
  NVL(h.has_been_fixed, 'N')
[...]

Thanks for your assistance. Let me know if you need more information.

[AlexP]

hello,

1) You can specify parametrized queries only in the OraQuery.SQL property (the SQL tab in the OraQuery editor), in this case, parameters will be displayed at the Parameters tab, and you will be able to call them with the help of the ParamByName and Params[n] methods. If you specify your INSERT/UPDATE operators in the SQLInsert,SQLUpdate, etc. properties, than the parameters should be the field names of the query specified in OraQuery.SQL. When inserting/updating data, appropriate DataSet field values will be put into these parameters.
2) The field and the parameter used in the RETURNING expression must coincide with the field name in the DB, and you should call the value, retrieved in the trigger, with the help of the FieldByName method instead of the ParamByName one. Moreover, you should also set the OraQuery.Options.ReturnParams property to True.
3) Judging by the error, you are using a NVL operator for a nonexistent field. Please send the script for creating tables and the full code of your SQL query.

[cinch]

hi,

1) would be nice if parameters could be specified in the Update/Delete/Insert statements too. the use case is: i'm using a TDatePicker control to manipulate dates and i don't have a database aware control for that.
using an invisible TDBText field is fine however for now.

2) thanks! didn't know about the ReturnParams setting. it's working now

3) after some schema changes and changes to the query, i can't reproduce it anymore. NVL on the field works as expected. probably something i overlooked.

[AlexP]

Hello,

You can use your parameters in the SQLInsert,SQLUpdate, and other properties if you process them correctly (set an appropriate value for each StatementType operation type) in the ToraQuery.BeforeUpdateExecute method