I cannot update record - getting strange message

[Zoran]

Only recently I found out about Lazarus, so I'm learning it now. I am experienced in Delphi programming, though.
I dowloaded and installed Lazarus 0.9.26.2 with FPC 2.2.2 on Windows XP. I have MySQL 5.0 installed, so I can use TMySQL50Connection from SQLdb tab.

In order to try using db components in Lazarus, I set up a very simple application - on main form I dropped these components: SQL50Connection, SQLTransaction, SQLQuery, Datasource and DBGrid. In SQL property of SQLQuery i wrote the simplest statement - "SELECT * FROM TABLE_NAME". After I run the project, the SQLQuery component gets connected and DBGrid is filled with data. However, when I edit some cell, after the programme comes to SQLQuery1.ApplyUpdates, it raises exception. I get the message:

"An error occured while applying the updates in a record: MySQL50Connection1: Error executing query: Duplicate entry '17' for key 1."

This error message seems as if I tried to insert a new record whith duplicate key, but all I did was changing the content of one cell.

[Zoran]

I think that there is a bug. Trying various combinations of properties in SQLdb components didn't get me anywhere. But, I found a solution in changing field types in database tables.

It seems that SQLQuery gets confused somehow if any field in a table is of any other integer type except 32-bit integer (smallint, byte). When I changed types of all such fields in database to integer, update and insert works.

However, delete still does not work.
At first SQLQuery.Delete removes the record correctly, but when it comes to ApplyUpdates, an exception is raised.

Could someone help me with this problem? Please, I lack of ideas what more I can try to get it to work...

[Zoran]

When I deleted fields which I set up in design mode everytihing works! Even SQLQuery.Delete method!

So, this is a bug, if you define fields in design mode, Delete does not work!

[José Mejuto]

[...]
So, this is a bug, if you define fields in design mode, Delete does not work!

Hello,

It could be related to: http://bugs.freepascal.org/view.php?id=14331

[Zoran]

[...]
So, this is a bug, if you define fields in design mode, Delete does not work!

Hello,

It could be related to: http://bugs.freepascal.org/view.php?id=14331

Thank you, Joshy.
I changed the unit sqldb according to patch enclosed there. However, this is something else, the problem there is when you set properties InsertSQL and DeleteSQL, which I did not do.

All my problems were solved when I removed all static fields from SQLQuery (fields defined in design time), so it seems that there is a problem only when working with static fields, and this is another bug.

[Loesje]

Your persistent fields, where they using the right case? MySQL is case-sensitive. (well, some mixture, bad designed, as the rest of mysql, but that's not the point now)

[Zoran]

Your persistent fields, where they using the right case? MySQL is case-sensitive. (well, some mixture, bad designed, as the rest of mysql, but that's not the point now)

Probably not. I didn't know that MySQL is case-sensitive. Actually, when I use SQLQuery's FieldByName function, I typed all in small case letters and there was no problem. The problem was only with persistent fields. Okay, I am going to test now.

BTW. Please, tell me, if MySQL is badly designed, what in your opinion would be the best free alternative?

[cdbc]

Hi Zoran.

Have you had a look at Firebird: http://www.firebirdsql.org/ I use it daily (vers. 2.0), it's free, speedy and stable. Together with Zeos Dbo: http://zeos.firmos.at/portal.php it works great and easy

Regards Benny

[Zoran]

Hi Zoran.

Have you had a look at Firebird: http://www.firebirdsql.org/ I use it daily (vers. 2.0), it's free, speedy and stable. Together with Zeos Dbo: http://zeos.firmos.at/portal.php it works great and easy

Regards Benny

Thank you, Benny. I have installed Zeos components now and with them I don't have any problem. Seems that SQLdb components are still more buggy. I'll take a look at Firebird, but, as I said, everything seems quite okay since I switched to Zeos, although I kept MySQL database.

[Zoran]

Thank you, Benny. I have installed Zeos components now and with them I don't have any problem. Seems that SQLdb components are still more buggy. I'll take a look at Firebird, but, as I said, everything seems quite okay since I switched to Zeos, although I kept MySQL database.

Only now I managed to realize what the problem was actually about. Then, I solved it. It has nothing to do with SQLdb, after a while the similar problems reappeared with Zeos components.

The real problem is between persistent string fields tied to utf8 encoded database string fields. I started a new topic about this, because I think that this should be documented separately.