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

[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

[Marc_G]

Hello Doug.

I think you have a misunderstand of isolations levels in Firebird. Probably you are using Snapshot level (the default one in most components), in that level, every transaction haves an Snapshot of data at their start. You will not see updates made by other users, until you start a new transaction.

So, I see 2 easy solutions. First one is to replace your SQLQuery2.Refresh by :

Trans.Commit;
SQLQuery2.Open;
Trans.StartTransaction;

Second solution is to use a lower transaction isolation level, like READ COMMITED (the default level in most others databases)

NOTE: Yo should also try to replace SQLQuery2.Refresh by SQLQuery2.Close; SQLQuery2.Open;. Theorically there is not difference, but ...

Regards.

[DougNettleton]

Marc G:

Thanks for the follow-up.  The first suggestion would not be my favourite since: Trans.Commit closes all the tables attached to the transaction, necessitating re-opening and repositioning all the tables to where the user was.  

Even with one transaction for each table, if there are multiple tables with parameterized queries - a benefit of Firebird over MySQL - as children of the table just editted, these would all have to be re-opened and repositioned.  I can't believe people are doing this type of thing since dBase II.

The second solution "lowering the transaction isolation level" would be my preference.  How does one do this?  Before my initial posting, I spent hours "googling" various combinations of :

"Lazarus" "Concurrent" "database Programming" "Firebird" "Transaction Isolation Level".  

I guess I can't believe people would still be using a product after more than 20 years that was so tedious to do something useful with.  Like most things, I suspect once I find out how to do it, what I want to do is quite doable and straightforoward.

TIA,

Doug

[jrmarino]

Doug,

You may wish to check out http://pdo.sourceforge.net

For my own purposes, I built an SQL API specifically to use the native MySQL 5.0 library (as well as 4.0 and 4.1) to accomplish prepared statements, multiple result sets, stored procedures, etc.   A few months ago, I added support for Firebird 2.0.  Eventually I'll add PostgreSQL support as well.

It works equally well on Delphi and FPC projects, and windows / unix projects.  It does not require lazarus -- it's not component based.  Anyway, take a look, it may suit your needs.

[Marc_G]

Hello Doug.

I do'nt use Lazarus, I'am a Delphi programmer (but probably in a near future I will do something in Lazarus). So, I ca'nt say you exactly how to lower transaction isolation level in Lazarus components.

In Delphi normally it is very easy. You set this option in a property of the Transaction component (or sometimes, in a parameter of the StartTransaction call).

Firebird is a great database, I am using it for the last ten years and I ca'nt be more satisfied.