Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Interbase 6.0 executeing SQL

Posted on 2001-06-24
1
Medium Priority
?
203 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 400 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

877 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