[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 462
  • Last Modified:

Delphi6 Interbase Querys and Transactions handling

Good Day

I am new with Interbase/Firebird development, currently I am developing client server applications with Firebird using Interbase
components that are ship with Delphi 6 Enterprise.

I my plan is to develop small seperate .exe and then from a main menu application to
call each .exe as needed. The reason i am doing this is, If a problem occurs in a program then I can
quickly fix the program and the client are not affected in any way...I hope this is the best way..

Now my problem is as follows... In all my application I have a datamodule, in the datamodule
I got TIBDatabase component and a TIBtransaction component.
At this stage the properties of the tibtransaction are default.

My application do a normal select staments like this one.
with qryData do
  sql.add('select * from emp');

We I nedd to Update data, I call a stored procedure and all my code to update or insert are in the stored procedure.

NOW... my data doesnt refresh if I call my select statement, the old data still shows, but if I exit the app and re-run the application
the updated data are there.

Also If I  run two seperate Instances of the same application on my pc, and i run a select and do a update, and i go to the
other exe and run the same select the updated data doesnt show...


One more thing... I dont depend on dbnavigator to update or delete or to refresh data, I  right my one select and use
stored procedures to insert/update data.

Thank you

Henry Reynolds
  • 2
  • 2
1 Solution
Hi Henry

First of all if you are going to run a number of exe files as oppose to one project with a number of units all using a common datamodule, you will be using multiple logins, one for each exe. This may mean that you exceed your licenced number of users for the database.

If you have more than one client for your program, and you start issuing fixes for individule exe problems, you could cause yourself diverse problems as time passes, some clients may not identify a problem with an exe and not request an update. Not all clients will install an update when its provided if they have not identified a problem in this unit. You will end up with clients running a diverse mix of exe's of different issue your ability to debug reported problems depends on you knowing exactly what exe versions that they are currently running. Its much easier if you just recompile a single update of the entire program and send it to all users.

The current version number can be embedded via Project Menu -> Options -> Version Info tab
Major version / minor version / release / build.

For running SQL queries over a network I would suggest a scalable ibcomponent TIBQuery
If you use DataAccess Tab -> Datasource component, link its Dataset property to point to a TIBQuery component from the Interbase tab, and then use an IBUpdateSQL component also from the Interbase tab.

Full property settings can be seen in a previous question response at

If you also turn on dataset local caching by setting IBQuery1.CachedUpdates property to true, this will help to reduce network traffic. Remember to set IBQuery1.ApplyUpdates before ibTransaction.Commit as caching is on.

The default TIBTransaction properties should be OK for most purposes.

You have several methods open to you to enter SQL statements

with <Table> do
    GenerateParamNames:= false;
    ParamCheck:= false;
    { Load SQL Statements with parameters}
    ParamCheck:= true; // if you are using params
    SQL.Add('Select <Field> from <Table>');
    SQL.Add('Where <Field>  >= :<ParamName>');
    ParamByName('<ParamName>').Value:= <Variable / Value>; // load param value
    Prepared:= true;
    Active := true; // run query
ibTable.ApplyUpdates:= true;
ibTransaction.commit // any local changes will be updated on the main database
ibQuery1.refresh       // if you want to refresh the dataset            

A better way is to use an IBStoreProc, place on of these components on the datamodule and link it
to the database and then to an existing interbase procedure. Any existing parameters will then show up
in the IBStoreProc.params property. Assign values to the input params and run.

// Note The dataset must be closed when you specify or modify
// the SQL property.
// Clear any existing query
with <IBQueryName> do
    GenerateParamNames:= false;
    ParamCheck:= false;
    // Load SQL Statements
    SQL.Add('Select < * or comma separated fieldlist>');
    SQL.Add('from <Table-Name>');
    // SQL.Add('Where <Field1> LIKE :<InputParam1>');
    // Any Comparison operator =, >=, <=, <>, LIKE
    // SQL.Add('AND <Field2> = :<InputParam2>');

    // load input params for Qry SQL, if any params required for the query }
    GenerateParamNames:= true;
    // Types: ftString, ftSmallInt, ftInteger, ftWord, ftBoolean,
    //        ftFloat, ftCurrency, ftBCD, ftDate, ftTime, ftDateTime
    ParamByName('<piInputParam1>').<AsString>:= <StringInputVariable>;
    // Input variable types
    // String, SmallInt, Integer, Word, Boolean, Float, Currency, BCD, Date, Time, DateTime
    SQL.Add('Order by <field1, field2 ... >');
    ParamCheck:= true;
    Prepared:= true;
    Active := true;

If you want to run a separate instance of a ibQuery with a grid, either place it on the existing form or a new form, and run up another set of components to handle the dataset and queries. If you run another exe you will be using another database connection.

dbNavigator is handy to scroll through a dataset displayed in a grid, you can disable buttons that you do not want used by setting the dbNavigator1.VisibleButtons property with the Object Inspector.
henryreynoldsAuthor Commented:
Thank you I will try and see if this work and I will let you know, but I still not sure WHY and what is the correct way of
using transaction with select statements, and which properties of the transaction compent is the most efficient way for select, updates and insert statements, to
minimize network traffic and to avoid dead locks.

Thank you

Direct Quote from Delphi 7 Help:-
"All TIBCustomDataSet descendants and TIBSQL need to use a transaction along with a database component to gain access to data in a database."

While you are getting used to this component the ibTransaction.params property can be left blank,
the system will apply a set of default params which should be suitable for most applications.

To reduce the posibility of deadlocks you can use the following structure

ibQTable1.FieldByName('<FieldName>').AsString := Edit1.Text;
ibTransaction1.Commit; // end of transaction1
( or  IBTransaction1.Rollback or IBTransaction1.RollbackRetaining if applicable)

Have a look at "Interbase Developers Guide" which ships with Interbase in PDF format.
The section called "Using a transaction component" in Chapter 10 "Building One- and
Two-Tiered Applications " should give you a better idea of how this component works.

If you are working with a remote database also read "Interbase Developers Guide"
Chapter 16, “Working with Cached Updates.” as this covers some of your queries regarding
network traffic.

Interbase Operations Guide, Chapter 8 "Database and Server Statistics"
Section "Viewing lock statistics"
henryreynoldsAuthor Commented:
Thanx I have a look toady and let you know.
Enjoy your day

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.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now