Binding variables at runtime

[reab]

Hi all,

Being new to using this technique of connecting to a Firebird database with IBDAC I've stumbled quite early on.
I have a form with a DBGrid and is set up to access my database - which it does quite well and I can look at all of the table contents including some views that I've constructed.
How ever when I try to do a insert using the following:

Code: Select all

RARsQuery.DMLRefresh:=true;
RARsQuery.SQL.Text := 'INSERT INTO DATALOG("DATALOG_DATE", "DATALOG_DESCRIPTION", "DATALOG_VALUE") 
VALUES (:"DATALOG_DATE", :"DATALOG_DESCRIPTION", :"DATALOG_VALUE")';
RARsQuery.ExecSQL;

I get a line inserted with an ID auto generated within Firebird but either no values or the values from the first line in the grid.
When I inspect the SQL text it does not appear to have inserted the values from the fields that I have enetered, I've named the fields to be the same as the database columns i.e.DATALOG_DATE, DATALOG_DESCRIPTION, DATALOG_VALUE.

Help - how do I bind the variable values into the sql statement at runtime?

Thanks for any pointers as I like to do things the hard way

[Boro]

Hi reab,

I am new to IBDAC too, so my answer isn't qualified.
Your SQL statement includes variables, those with colon prefix

VALUES (:"DATALOG_DATE", :"DATALOG_DESCRIPTION", :"DATALOG_VALUE")';

You have to ' bind' them (assign them) using the ParamByName property.
Something like a

RARsQuery.ParamByName('"DATALOG_DATE"').AsDate :=

Assign them before you call ExecSQL method.
Probably you will have to call also the RARsQuery.Prepare method before the first ParamByName call.

Hope this help.

Boro

[Plash]

IBDAC can automatically copy values that you have entered into fields to the parameters of your INSERT statement. To insert a row, use the same TIBCQuery component that is used to select data. Assign the text of the INSERT statement to the SQLInsert property. After you have entered values into the fields, call the Post method of the TIBCQuery component.

[Boro]

Oh, that is great. I didn't know about that.
This ability (automatic copy of values entered into fields to the parameters) is not mentioned in the help for TIBCQuery component. But now I have searched for the word 'Post' and have found it in the FAQ section of IBDAC help, question 'How can I execute a query saved in the SQLInsert ...'.
But who should find it this way ?

[Plash]

We'll consider a possibility to improve IBDAC help.

[reab]

Thanks to everyone for their replies - its got me on my way.

I can now look at any user selected table, insert, update and delete records using database aware spreadsheet style grids. However I am struggling to for instance retrieve average or counts into a simple field where I can use it in calculations - I can retrieve this data into the grids quite happily. The select statements are held as views on the Firebird database i.e.

Code: Select all

SELECT AVG(DATALOG.DATALOG_VALUE) FROM DATALOG

I'm sure there is a simple way to do this - it just eludes me at the moment, as did the last problem

I've only just worked out that I can use the grids to edit the tables direct if needed!

Thanks for any pointers.

[Plash]

To get value from a query to a variable, you can use a code like the following:

Code: Select all

var
  d: double;
begin
  OraQuery.SQL.Text := 'SELECT AVG(DATALOG.DATALOG_VALUE) FROM DATALOG';
  OraQuery.Open;
  d := OraQuery.Fields[0].AsFloat;
end;

[reab]

Many thanks for that - its helped me with a kick start to get things moving again and given me an insight into retrieving specific data fields.

Now I can concentrate on creating stored procedures etc