Subject Re: [IBO] Re: Trouble with Insert Into
Author Markus Ostenried
On Sun, Aug 14, 2011 at 21:09, squidsrus85 <squidsrus85@...> wrote:
> Hi Markus,
> Getting an error with that code you supplied yesterday.
> Error -104
> Token unknown Line 1, column 5
> term
> Here's what I was using to load the SQL
>  qry_DB.SQL.Add('set term ^;');
> Any suggestions? I suspect it is something I am doing or not doing.
> It is connected OK, as I have checked for the Table existence and if it does exist I Drop it before creating it anew.

What are you trying to do? I only have one place in my applications
that creates tables, and that is after connecting where I check if
there are not yet executed update sql scripts needed to update the
database structure to its latest version. I have a folder with .sql
files containing my update scripts and a table with a record for every
script that has been executed. Scripts are numbered so at application
start I can do "select max(script_number) as max_num from db_updates",
then get all update script file names and execute those starting with
a number higher than "max_num".

The SQL statements fall in two categories: Data definition language
(DDL) and data manipulation language (DML). DDL defines your metadata
(tables, views, triggers, foreign keys, stored procedures, check
constraints etc), with DML you do select, insert, update, and delete.

I guess qry_DB is of type TIB_Query? TIB_Query is for DML but my
script defines a table and so is DDL. To execute it you use a
TIB_Script component, assign it's Lines.Text property and call
Execute. There's also RECREATE TABLE but if the table already exists
then it will be dropped before its created. For stored procedures and
triggers you can use CREATE OR ALTER which will modify the
trigger/stored procedure if it already exists (no need to drop it

>  qry_DB.SQL.Add('set term ^;');

The SET TERM statement is needed to change the statement delimiter: If
you define a trigger then it contains ";" as statement delimiter
within its body. With SET TERM you tell the tool where your CREATE
TRIGGER statement ends.