update table problem

[jovicat]

I have installed MYSQL 4.1.7 on Win2003 Std_Edt with standard my.ini.
With Delphi app. I have simply edit-post (update ..)
on table. When initial data are diferent from new one,
it's OK, but when I try to exec update with no diferences
in data, I have error that row data are modified from
other user.

Sample: Table - Tab1 with fields (ID, Desc);
1 Item1
2 Item2
3 Item3 ..
Query that exec OK :
update Tab1 set Desc = ItemX where ID=2

But query :
update Tab1 set Desc = Item2 where ID=2
came with error.

Problem persist in Delphi with MyODBC 3.51.12.

 

[r937]

DESC is a reserved word, either rename the column or place it in backticks

r937.com | rudy.ca

 

[jovicat]

Acctualy the tab1 table is not original, and the column names are something like (artsif, artopis, ..) with no
reserved words. The problem persist on one
instalation, but same exe on other instalation (server)
has no problems.

 

[r937]

[tt]update Tab1 set Desc = Item2 where ID=2[/tt]

this statement sets Desc to the value of the column Item2

[tt]update Tab1 set Desc = 'Item2' where ID=2[/tt]

this statement sets Desc to a string value

r937.com | rudy.ca

 

[jovicat]

Like I said in the first post, I use edit, post in Delphi
which is translated in (update) by myodbc, I gess.
Problem is that update come out with error when I try
to update data with no changes.
The same thing (same exe application, same version
of MySQL and MYODBC drivers) work on other machine.

 

[TonyGroves]

Are you using Delphi's TTable (etc.) components, with TTable::Edit, TTable:ost, etc? Those methods are really very inefficient when used with any SQL database server. They depend on Borland's database engine translating your TTable operations into properly-composed SQL statements, and I wouldn't be at all surprised if they get it wrong sometimes.

If you want to stick with Borland components, you would be better off just using TDatabase and TQuery; TQuery will accept any valid SQL statements you throw at it.

Even better again, I've written a very simple set of C++ classes that do not depend on BDE and ODBC, but only require the MySQL client library. It should be fairly easy to translate them into Pascal. I can let you have the code if you want.

 

[jovicat]

Thank's Tony. I use Delphi with edit/post methods.
I wonder where is the problem. Does it Delphi which is transalting edit/post into update query,ODBC or ADO.
Same exe file with same version of MyODBC(3.51.12); MySQL 4.1.7-nt via TCP/IP port, same DataBase (back-ed up/ and restored on other PC with same OS Win2003 Server Std. works fine on one instalation, and came up with errors
on other instalation.