[solved] beginner's qustion TIBQuery - TSQLQuery - when do I use which?

[Nicole]

if I need a query I key into my search window "query" and take one of them: IBQuery or TSQLQuery.
Sooner or later it works.

The only thing I know: This cannot be the best way to work.
How can I do better?

Is there one to prefer?
I have many queries of all kinds: insert and select
I cannot say: this is done more often.

If you want to know, how I work:
My units are sorted by topic which mostly means: by table(s).
Every unit has about 1 to 3 queries.
The queries are sorted by the duration of their tasks. Single request-queries have other names as some several-second-jobs.
If I fear, they may interact, - I employ a second one for a second request needed in a context.

[tonyw]

TIBQuery, TIBTable and TIBDataset exist to support data aware controls e.g. TDBGrid, TDBEdit, etc. They provide a buffered dataset that is the result of a "select" query. TIBDataset also allows you to provide supporting Insert, Update, Delete and Refresh queries that allow any changes made to the dataset to be written back to the database.You can also add a TIBUpdateSQL component to a form with a TIBQuery, and link it to the TIBQuery. The TIBUpdateSQL also allows you to add supporting Insert, Update, Delete and Refresh queries to the TIBQuery's dataset - again to any changes made to the dataset to be written back to the database. TIBQuery + TIBUpdateSQL is equivalent to a TIBDataset.

TIBSQL is a component that supports direct execution of any database query (Select, Insert, Update, Delete, Execute Procedure). You use it when you need programmatic access to the database but do not need to support any data aware controls.

For example, if you only need to insert a row into a database (using the query from your other question), then TIBSQL can do this inline:

Code: Pascal  [Select][+][-]

1.   with TIBSQL.Create(nil) do
2.   try
3.     Database := MyDatabase; {also sets the TIBSQL transaction to the default database transaction}
4.    SQL.Text := 'Insert into TBKONTRAKTE(NAME_,COMM,JAHR_AUS_NAME,MONATSKENNZAHL)' + //  ID_KONTRAKT,
5.         ' values (:NAME_,:COMM,:JAHR_AUS_NAME,:MONATSKENNZAHL) returning ID_KONTRACT';
6.    ParamByName('NAME_').AsString := Trim(name_);
7.    ParamByName('COMM').AsString := Trim(comm);
8.    ParamByName('JAHR_AUS_NAME').asSmallInt := Jahr_aus_Namen;
9.   ExecQuery;
10.   MyContractID := FieldByName('ID_KONTRAKT').AsInteger;
11. finally
12.   Free
13. end;
14.  
15.  

A select query can be similarly executed. You use TIBSQL.FetchNext to scroll between rows (unidirectional only) and TIBSQL.Close when you are done.

If you need to display the results of a TIBSQL.ExecQuery in an already open TIBQuery, etc. then you have to call TIBQuery.Refresh in order to force it to re-read the dataset from the database. In this case, you should also use the same transaction for both the TIBSQL and the TIBQuery.

If you really want advanced usage then you can also make direct use of the Firebird Pascal API to achieve the above in one line. e..g

Code: Pascal  [Select][+][-]

1. const
2.   sql = 'Insert into TBKONTRAKTE(NAME_,COMM,JAHR_AUS_NAME,MONATSKENNZAHL)' + //  ID_KONTRAKT,
3.         ' values (:NAME_,:COMM,:JAHR_AUS_NAME,:MONATSKENNZAHL) returning ID_KONTRACT';
4.  
5. MyContractID := MyDatabase.Attachment.ExecuteSQL(MyTransaction.TransactionIntf,sql,[Trim(name_),Trim(comm),Jahr_aus_Namen])[0].AsInteger;
6.  

If you dive into the underlying code for TIBSQL, you'fl find that it is no more than a wrapper for the above.

I hope that this is useful.

[Nicole]

Thank you so much.
* copy your text and put it into my knowledge file-folder *

And your way to write code
(" with TIBSQL.Create(nil) do ")
is coool.