Generate SQL on runtime

[rdollinger]

Hi,

I would handle different db-tables with one TIBCQuery. Is there a method to generate the sql's (update, insert, delete) on TIBCQuery on runtime?

thanks for help in advanced

bye
Robert

[benek]

in Delphi
//select
TIBCQuery.SQL.Clear;
TIBCQuery.SQL.ADD('SELECT FIELD_NAME FROM TABLE');
TIBCQuery.OPEN;
IF NOT TIBCQuery.ISEMPTY THEN
BEGIN
FIELD := TIBCQuery.FIELDBYNAME('FIELD_NAME').VALUE;
END;
TIBCQuery.CLOSE;

//delete,update
TIBCQuery.SQL.Clear;
TIBCQuery.SQL.ADD('DELETE FROM TABLE where ...');
TIBCQuery.ExecSQL;

[rdollinger]

Ok that's clear. But that is not what I have answerd for.

I have many master-tables on my db, and I would not type for any of this master-tables the SQLUpdate, SQLInsert and SQLDelete statments. Designtime there is the SQL Generator on TIBCQuery to do this.

Because I don't wont use a separate TIBCQuery component for each table, I have to generate this SQL-Statements dynamically on runtime.

Is there a solution for this?

bye
Robert

[benek]

Eeeee.. I don't anderstand
"Because I don't wont use a separate TIBCQuery component for each table.."

You don't need to use a separate one, only change the sql statement in runtime
TIBCQuery.SQL.Clear;
TIBCQuery.SQL.ADD('... new statement

and open or execute

[rdollinger]

But in this way I have to change every time the code of my programm.
And I would avoid this with automating this procedure:

Pass the table name on my procedure
I create the "select * from table_name" in the way you have explained
At this point I don't know how many fields this table has
The missing part: create via a method (like SQL Generator) the update, insert and delete statements.

I hope I have explained it more clearly now

bye
Robert

[benek]

... I hope I have explained it more clearly now

Yes, You want to make life more complicated

Remember You can use params in sql statement (for instance for tables)
and iterate through fields in result set you are getting back

Good luck !

[Plash]

The TIBCQuery components generates the update SQLs automatically if you have empty values in the SQLInsert, SQLUpdate, and SQLDelete properties.

So you just need to clear these properties if you want to use one TIBCQuery component for different queries.