Need Zeos consultant for How to's

Gina_M · Post by **Gina_M** » 25.11.2007, 19:10

I am looking for a consultant to just answer questions regarding using My SQL 5/ Zeos/ and Delphi 2006 for a flat fee.

Along the lines of:

Connection properties
Cached updates
Handling lost connections
...

I have solid Delphi experience so no need to hold my hand. I am just on a very tight schedule and want to do this right the first time, not find out the hard way.

Thanks

~Gina_M~

piper62 · Post by **piper62** » 26.11.2007, 03:25

Hi Gina,

I suggest that you just post your questions here. I'm sure that there'll be somebody to help you.
Shortly I can say that we avoid to make cached updates. So we switch this functionality off. My way is here to leave this responsibility on the side of the system which is designed for that: the DBMS.
I think, it's better to configure the server right.

We develop in the field of hospital information systems and use ZeosLib as our main database abstraction layer.
We do develpment with FreePascal/Lazarus (Ver. 2.2 and 0.9.24) mainly under Linux but with crosscompile to MS-Windows.
We also use MySQL 5 as the DBMS with InnoDB tables due to transaction handling.

Regards,
Tibor

Gina_M · Post by **Gina_M** » 26.11.2007, 18:34

Thanks Tibor,

But my questions aren't getting answered. I can't wait a week for an answer.

How do you handle a lost database connection? I know it isn't suppose to happen but we have users that are reporting such a problem. Yes, their fault but doesn't help telling the user that. I know that I can go totally none data aware controls but I inherited this project recently and cannot go back and rewrite a 22 module application.

~Gina_M~

piper62 · Post by **piper62** » 26.11.2007, 18:46

Hi Gina,
what happens? do they loose the DB connection or is it network problem? There are settings on the MySQL server side where you can influence the timeout handling of a connection/session.

If that is not the reason I would try to figure out under what kind of conditions that happens.
Our users leave normally their application open and on a ward they have to walk around. I never had this problem but in another system for restaurants (order/entry) we are doing a timer approach.
We use a timer to update the order list for different working stations (kitchen, service etc.).
This works well. You can probably use a timer to check the connection.
I guess it needs further investigation what happens there really...

Regards,
Tibor

Post by **mdaems** » 26.11.2007, 23:34

Concerning the timeouts : there's a Connection method called Pingserver. When you call it before you attempt to do some database activity it should attempt to reconnect if the server has gone away since last activity. I know the HeidiSql Team uses this function for this purpose.
In a Cached Updates situation this is a nice way to make sure you're decently connected before you start doing the writes to the database with Query.ApplyUpdates.
Another nice method is Connection.Reconnect . It's more or less a Close+Open of the connection, but it shouldn't lose the queries bound to the connection component.

Mark

Gina_M · Post by **Gina_M** » 27.11.2007, 00:19

Thanks mdaems,

What transisolationlevel should I use for mysql?

~Gina_M~

Post by **mdaems** » 27.11.2007, 00:31

That mainly depends on the mysql storage engine you use. If you simply use MYISAM I don't think it makes a lot of difference. For Innodb you should check the available options with your requirements. I don't think there's a zeoslib limitiation.
Generally the Zeos default is READ UNCOMMITTED.
Effect of the different options :

Code: Select all

      case TransactIsolationLevel of
        tiNone, tiReadUncommitted:
          begin
            SQL := 'SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED';
            testResult := FPlainDriver.ExecQuery(FHandle, SQL);
          end;
        tiReadCommitted:
          begin
            SQL := 'SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED';
            testResult := FPlainDriver.ExecQuery(FHandle, SQL);
          end;
        tiRepeatableRead:
          begin
            SQL := 'SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ';
            testResult := FPlainDriver.ExecQuery(FHandle, SQL);
          end;
        tiSerializable:
          begin
            SQL := 'SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE';
            testResult := FPlainDriver.ExecQuery(FHandle, SQL);
          end;
        else
          SQL := '';
      end;

Gina_M · Post by **Gina_M** » 27.11.2007, 09:04

OK, thanks much.

If I insert a record with an autogenerated ID, how do I get the ID of the last inserted record? Any chance of someone adding a record in between the insert and my request for the ID?

Donation box anywhere?

Thanks

~Gina_M~

Gina_M · Post by **Gina_M** » 27.11.2007, 09:22

ok, I must have the wrong version. I swore I had 6.6x but apparently not. I will upgrade tomorrow.

I hope there is instruction on how to install on delphi 2006. I read a few posts of people having problems.

~Gina_M~

Post by **mdaems** » 27.11.2007, 11:53

Most important thing on upgrades : make sure all traces of old versions are gone or at least hidden to Delphi (DCU, BPL, ...). This avoids conflicts when compiling and installing.
Install = compile projects Core, Parsesql, Plain, Dbc and Components (in that order). Afterwards : compile and install ZComponentDesign package. This is easy when you open the project group in the packages/delphi10 directory.

To fetch the autogenerated ID (autoincrement) is easy when you use mysql. Since about a year it's fetched automatically when you insert null or 0 in a autoincrement field.

Donation box. No, we don't have one. Problem would be : who owns the money... So I would say : donate to the person that deserves the bounty and negotiate the details using private messages. Maybe I'll talk about it with Firmos and Michael.

Maybe you should fill your user profile a little more. It's always nice to know where somebody lives. Or (eventually) how you can be contacted by msn, irc, ...

Mark

piper62 · Post by **piper62** » 28.11.2007, 00:45

Hi Gina,

regarding last_insert_id in MySQL5:
-----------------------------------------------------------------------------------------
// made an insert statement just before this...
ZQuery1.SQL.Clear;
ZQuery1.SQL.Add('select last_insert_id() as last_id');
ZQuery1.Open;
label1.caption:=ZQuery1.FieldByName('last_id').asString;
-----------------------------------------------------------------------------------------
MySQL handles that by the actual connection so it's pretty safe that you really get your last_insert_id.
I got never a problem with it. Of course you shouldn't close the connection between the insert and the "select last_insert_id()" statement. But this goes normally so fast that this shouldn't be a problem.

Regards,
Tibor

Gina_M · Post by **Gina_M** » 28.11.2007, 07:47

Thanks piper62.

Two questions:

1) I want to make sure that getting the ID is 100% correct. Should I use a transaction? How foolproof is piper62's idea? I use a query to insert the record.

2) Regarding PingServer. Will this actually try to reconnect if the connection is lost, or does it just tell me that the connection is lost and I should reconnect?

Much appreciated!

I successfully installed the latest version of Zeos!!!!

~Gina_M~

Gina_M · Post by **Gina_M** » 28.11.2007, 07:54

Sorry 3rd question:

I don't see TZconnection.Reconnect.

Is it possible to reconnect and keep all the queries that were connected happy?

~Gina_M~

law · Post by **law** » 28.11.2007, 10:02

piper62's idea is foolproof. here's the corresponding part of the mysql 5.0 reference:

"You can retrieve the most recent AUTO_INCREMENT value with the LAST_INSERT_ID() SQL function or the mysql_insert_id() C API function. These functions are connection-specific, so their return values are not affected by another connection which is also performing inserts.

Note
For a multiple-row insert, LAST_INSERT_ID() and mysql_insert_id() actually return the AUTO_INCREMENT key from the first of the inserted rows. This allows multiple-row inserts to be reproduced correctly on other servers in a replication setup."

check "3.6.9. Using AUTO_INCREMENT" for more info.

Gina_M · Post by **Gina_M** » 28.11.2007, 17:32

Thanks law and piper62!!! Sounds great!!!