Solved

Interbase 6.0 executeing SQL

Posted on 2001-06-24
1
196 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 1

Accepted Solution

by:
z_darius earned 100 total points
ID: 6223243
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
When I am typing into a TEdit last word repeats 7 179
Delphi selector screen 2 97
Magic Software info 18 155
Convert GUI app into console app for Win32 Env 5 138
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…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

751 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