Page 1 of 1
Binding variables at runtime
Posted: Fri 05 Oct 2007 09:45
by 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
Posted: Fri 05 Oct 2007 10:26
by 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
Posted: Mon 08 Oct 2007 09:04
by 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.
Posted: Mon 08 Oct 2007 10:16
by 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 ?
Posted: Thu 11 Oct 2007 07:52
by Plash
We'll consider a possibility to improve IBDAC help.
Posted: Wed 07 Nov 2007 11:43
by 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.
Posted: Fri 09 Nov 2007 09:17
by 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;
Posted: Tue 13 Nov 2007 09:40
by 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