Delphi6 Interbase Querys and Transactions handling

Posted on 2006-05-24
Last Modified: 2008-03-10
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
Question by:henryreynolds
    LVL 4

    Accepted 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.

    Author Comment

    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

    LVL 4

    Expert Comment

    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"

    Author Comment

    Thanx I have a look toady and let you know.
    Enjoy your day

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    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…
    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…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    734 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

    22 Experts available now in Live!

    Get 1:1 Help Now