Interbase 6.0 executeing SQL
How can i execute SQL-Scripts from Delphi ?
i would like to generate a Table from within my application, but the Table has some Triggers.
for example :
CREATE TABLE "PA"
(
"ID" INTEGER NOT NULL,
"PID" INTEGER,
"TIMESTAMPS" INTEGER,
"BS" SMALLINT,
PRIMARY KEY ("ID")
);
SET TERM ^ ;
/* Triggers only will work for SQL triggers */
CREATE TRIGGER "PA_INSERT" FOR "PA"
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
NEW.TIMESTAMPS = GEN_ID(UID, 1);
END
^
CREATE TRIGGER "PA_UPDATE" FOR "PA"
ACTIVE BEFORE UPDATE POSITION 0
AS
BEGIN
NEW.TIMESTAMPS = GEN_ID(UID, 1);
END
^
COMMIT WORK ^
SET TERM ;^
i would like to generate a Table from within my application, but the Table has some Triggers.
for example :
CREATE TABLE "PA"
(
"ID" INTEGER NOT NULL,
"PID" INTEGER,
"TIMESTAMPS" INTEGER,
"BS" SMALLINT,
PRIMARY KEY ("ID")
);
SET TERM ^ ;
/* Triggers only will work for SQL triggers */
CREATE TRIGGER "PA_INSERT" FOR "PA"
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
NEW.TIMESTAMPS = GEN_ID(UID, 1);
END
^
CREATE TRIGGER "PA_UPDATE" FOR "PA"
ACTIVE BEFORE UPDATE POSITION 0
AS
BEGIN
NEW.TIMESTAMPS = GEN_ID(UID, 1);
END
^
COMMIT WORK ^
SET TERM ;^
Who is Participating?
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
-
Course of the Month6 days, 1 hour left to enrollMonitoring and Operating a Private Cloud with System Center 2012 R2 273 lessons
1. give yourself a button:
object Button1: TButton
2. drop an IBDatabase component on the form
object IBDatabase1: TIBDatabase
Connected = True
DatabaseName = 'D:\zz\Work\A21.GDB' //use your path
Params.Strings = (
'user_name=sysdba' //use your login
'password=somepassword') //use your password
LoginPrompt = False
SQLDialect = 1
3. drop and IBSQL component on the form:
object IBSQL1: TIBSQL
Database = IBDatabase1
ParamCheck = True
SQL.Strings property
SQL.Strings = (
'CREATE TRIGGER PA_INSERT FOR PA '
'ACTIVE BEFORE INSERT POSITION 0'
'AS'
'BEGIN'
'NEW.TIMESTAMPS = GEN_ID(UID, 1);'
'END'
'')
Transaction = IBTransaction1
4. finally and IBTransaction component:
object IBTransaction1: TIBTransaction
Active = True
DefaultDatabase = IBDatabase1
end
now using one of the onClick events do something similar to the code below:
procedure TForm1.Button1Click(Sender
begin
IBSQL1.ExecQuery;
IBTransaction1.Commit;
end;
that's all. I verified this to work against IB6.
One last suggestion: do not use quotes around names of tables, fields in tables and other database objects in your SQL code.
for instance:
Wrong ---->>> :
CREATE TABLE "PA"
(
"ID" INTEGER NOT NULL,
"PID" INTEGER,
"TIMESTAMPS" INTEGER,
"BS" SMALLINT,
PRIMARY KEY ("ID")
);
Right ---->>> :
CREATE TABLE PA
(
ID INTEGER NOT NULL,
PID INTEGER,
TIMESTAMPS INTEGER,
BS SMALLINT,
PRIMARY KEY (ID)
);
Also, when creating a trigger which uses a generator, make sure you have created the generator first.
oh, and good luck !