Solved

Problem with Delphi and Firebird (interbase) database

Posted on 2006-06-27
6
409 Views
Last Modified: 2010-08-05
We have two different programs.  One is written in Borland C++ builder and the other in Delphi.  THey are accessing one central database.  When the C app updates the database the database refects the change without a problem.  When the Dephpi app udates it works as well, but if the Delphi program runs the same Query twice :

Select * from X

and the C app has updated the X table in between the quereies the the Delphi app does not see the update.

If the Delphi app closes it's connection to the database and then re-opens it the it sees the change that was made by the C++ app.  This works but uses alot of resources to accomplish.

Does anyone have any better ideas......



Thanks!!!


0
Comment
Question by:danielrsmith
6 Comments
 
LVL 4

Expert Comment

by:JDSkinner
ID: 16997079
Hi
What database are you using.
It would help if you posted the section of code used to make the delphi SQL enquiry.
0
 
LVL 9

Expert Comment

by:sun4sunday
ID: 16998057
The record will reflect in the database after the Commit.
After commit, try to run the query and check the result in Delphi.
Still giving the problem, try
-- Close and  open the query
-- Refresh query

Work around this.

Still problems, post the code here.

sun4sunday
0
 
LVL 11

Expert Comment

by:calinutz
ID: 16999534
The application that does the update should do it in a UPDATE statement and then the update will be seen by the other application on a refresh table. If the update is made in a DBGrid that has a TTable as datasource, then you must check to see if TableDirect is set to true (for ADO components) or just ensure that you call Table1.CommitUpdates for BDE components.
You must realize that the dataset components are not live ones... you must do the refresh yourself. Also consider commiting the updates more often in your applications so that the updates can reach the database and not be kept locally in a cache.
   What components do you use for working with Fireebird... it could be something about those components.

Regards
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 

Author Comment

by:danielrsmith
ID: 17002032
I will post some of the code in the morning.  the person working on the Delphi side is not available today.
I am commiting the UPDATE changes after each change.  I have no problem seeing these updates on the C++ side immediately.

The Delphi side refreshes its Query about every 30 seconds and does not get the updates fro some reason.  If I look at the DB through ibconsole the changes are alway there immediately.

Thanks for the help so far!!
0
 
LVL 17

Accepted Solution

by:
TheRealLoki earned 500 total points
ID: 17006229
Try checking the TIBTransaction's Isolation properties (double click on the TIBTransaction in delphi)
It should be set to at least
read_committed (read_committed, rec_version, nowait)
This could be the problem.

If the delphi app commits the transaction, and starts a new transaction before running the sql query, then it will see the changes.

btw, another approach to the "polling every 30 seconds" would be to use Interbase/Firebird "Events"
so that the database would tell the delphi app if a change had been made, and it needed to refresh its' list

CREATE TRIGGER TRG_SOMETABLE_AFTERINSERT FOR SOMETABLE
ACTIVE AFTER INSERT POSITION 0
as
begin
    post_event 'UpdatedSomeTable';
end

Then, in the delphi app, you drop a TIBEvents on teh form, edit it's properties and add 'UpdatedSomeTable'
Then, when it fires the event, you can do a refresh if convenient
0
 
LVL 4

Expert Comment

by:Delphian
ID: 17112418
Appear to me that TheRealLoki got a good clue, since appear that the
TransIsolations is set to SnapShot (or Repeatable Read - if we use ANSI
terminology).

The "only getting refreshed after closing the application" sympton denote
other thing: some problems with transaction management. Maybe you have more
than TIBTransaction component and it being held open - therefore blocking
the Query to see the updates. Since FireBird/Interbase can have multiple
transactions for a same connection, this scenario is very possible.


0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Best Firemonkey component pack 1 119
Press three keys together and trigger a function 3 60
how to resize animated Gif image in delphi ? 1 35
CheckListBox usage 3 71
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…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

860 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