Forum > Databases
Database Experiences MySQL 5.0 and Firebird 2.0
DougNettleton:
Hi everyone:
I offer the following brief descriptions of my experiences trying to do even trivial database programming with Lazarus. Most of my experience has been with recent nightly builds of lazarus for 32-bit windows 2.1.5 FPC.
I know that these are not guaranteed to work - use at your own risk etc, but they are at least getting close enough to be able to perform something bordering on useful.
I must confess that the delineation between what's a FPC problem and what's a Lazarus problem is to me at least murky at best. Judging from some the posts in these forums, I guess I'm not alone in this.
I have poured over these forums - particulary the database one - and often wonder whether anyone is actually doing any of the things the posting imply.
MySQL success.
Using a MySQL 5.0 connection, SQLTransaction and multiple SQLQueries, I have successfully:
1) Scrolled through data in a dbgrid
2) Editted / Updated data using the following controls DBEdit, DBComboBox, DBCheckBox, DBRadioGroup
3) Inserted data in a table
4) Logically deleted / undeleted records
5) Purged logically deleted records
Unsatisfied with MySQL in particular the tedious coding requirements needed because it doesn't support parameters in queries, I downloaded and installed the latest version of Firebird.
Firebird success
I converted my MySQL database to Firebird. Like MySQL it doesn't support a "Logical / boolean" type but fortunately it supports domains, so adding the following:
create domain logical as char(1) default 'N' not null check (value in ('Y', 'N')) ;
to my database definition statements, allowed much more satisfying table definitions like:
create table fund
(fundnumb char(4) primary key,
fundname varchar(40),
workmonth char(7),
delflag logical) ;
And now with paramterized queries like:
select * from employer where fundnumb = :fundnumb
order by ...
tedious coding like:
procedure TUbpsDataMod.SQLQuery1AfterScroll(DataSet: TDataSet);
begin
SQLQuery3.Close;
SQLQuery3.SQL.Strings[1] := 'where fundnumb = ' +
QuotedStr(SQLQuery1.FieldByName('fundnumb').Value);
SQLQuery3.Open;
end;
is no longer needed.
And all the successes with MySQL above have been duplicated with Firebird. It was not a smooth process believe me. I downloaded and installed UIB and FIBL and played around with each - the original documentation from the web for FIBL was particularly useful for getting to the point of updating data. But, in the end, I used the "native" components on the SQLdb tab.
My "logical" variables required some small coding changes and the code sequence ...
UbpsDataMod.SQLQuery2.Post;
UbpsDataMod.SQLQuery2.ApplyUpdates;
UbpsDataMod.SQLTransaction1.Commit;
from MySQL had to be replaced with ...
UbpsDataMod.SQLQuery2.Post;
UbpsDataMod.SQLQuery2.ApplyUpdates;
UbpsDataMod.SQLTransaction1.CommitRetaining;
since UbpsDataMod.SQLTransaction1.Commit;
closed all the Queries attached to the transaction.
There are many posts referring to Post / ApplyUpdates / Commit for MySQL 5.0, in order to get data changes to be made to the underlying data, but I didn't notice any that pointed out that this requires a Primary Key constraint.
From the firebirdsql.org web-site: What Is Firebird?
Firebird is a relational database offering many ANSI SQL standard features that runs on Linux, Windows, and a variety of Unix platforms. Firebird offers excellent concurrency, high performance, and powerful language support for stored procedures and triggers. It has been used in production systems, under a variety of names, since 1981.
I believe, therefore that updating a database table and having those updates become visible to other users accessing the database tables concurrently must be possible. If anyone wants to way in on how to do this it wolud be greatly appreciated.
Of course when, "Edit Fields" is working completely not causing Lazarus to stop responding and have to be shut down and Edit and display masks work properly, Lazarus be ready for serious database application development.
Frustrations:
Today: I downloaded "spawner" - the source code will not load with a recent nightly build - MaskEdit has been "gutted" because of copyright problems and is not installed on the "Additional" compnents tab
Today: I downloaded a contribution MySQL5Test to see if I could get some ideas on SQL programming techniqes for Lazarus, it doesn't load with a recent Lazarus snapshot.
The message dialogs were broken for a couple of weeks in the nightly snapshots.
Doug
AkiFoblesia:
that is well said, sir.
lazarus, being relatively wanting in documentation to begin with, would even turn out as disappointing when one tries to a solution he finds at the remotest of sites.
nevertheless i hope lazarus would make its way towards professional acceptance. pascal is good thing, as promoted be borland with turbo pascal and delphi.
lazarus and freepascal can become best as pascal was before, provided some neat documentation.
meanwhile, have you tried turbo delphi for your project? there is a free version of turbo delphi. you cannot, however, add components with it, though you can add classes. fblib is a good choice to partner with delphi and/or lazarus when using firebird :D
DougNettleton:
Hi everyone again:
I guess I went on at too great a length, and my question was lost.
Does anyone know how to update a table in either a MySQL or Firebird Database so that another concurrent user can actually see the changes, short of disconnecting from the database and reconnecting?
I looked at FBLib, but when I saw ...
>> FBLib Firebird Library
>> Open Source Library No Data Aware for direct access to Firebird Relational Database ...
I assumed it didn't support data aware controls.
TIA,
Doug
P.S. I have bought the Delphi Turbo Professional, but the kick of writing something that will work at least with Windows and Linux is very great. Great strides have been made with FPC and Lazarus since I first looked at it, so I'm very hopeful for the future.
Marc_G:
Sorry for my bad english.
Firebird Events are often used for this purpose. You must define some Firebird Events (costumers changed event, products changed event, sales changed event, ...), and fire the right event in database triggers every time a record is updated.
So, in your application, you must register to listen to costumer events (in exemple) if you are showing costumer records. When another user changes a costumer, correspondant trigger whill be fired, who will fire the costumer changed event. Finally you must only code your application, to refresh costumer records, when a costumer changed event is received.
UIB has support to use Firebird Events (I don't know about FIBL).
DougNettleton:
Marc G:
Thanks for your reply. Your English is fine. The problem is:
"Finally you must only code your application, to refresh costumer records, when a costumer changed event is received. "
Let's say I have a ccustomer table with two records:
CustCode CustName LastUpdate
00001 Customer 00001 2007-01-01
00002 Customer 00002 2007-06-01
If two users are browsing the data and one changes the first customer's name to:
"Customer 00001 xx" the second user doesn't - for my purposes - need to see the changes right away, so I don't think I ned to worry about event triggers, but if the second user goes to edit the record, I for sure want him to be looking at the latest "version" of the record. In fact if the first user had logically deleted the record, I want to refresh the data and prevent editting.
In any case, whether using event triggers or not, the 64 thousand dollar question is: How to do this? I tried SQLQuery2.Refresh, which doesn't appear to do anything.
TIA, for any further information, you or anyone else can provide.
Doug
Navigation
[0] Message Index
[#] Next page