Solved

Interbase 6.0 executeing SQL

Posted on 2001-06-24
1
188 Views
Last Modified: 2010-04-06
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 ;^
0
Comment
Question by:Andi4553
1 Comment
 
LVL 1

Accepted Solution

by:
z_darius earned 100 total points
Comment Utility
you need the following objects on the form along with properties as suggested below

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: TObject);
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 !
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now