Hi. I'm a newbie to mysql, delphi, oop et al. I have two identical delphi projects. One is linked to an Access db, the other to a MySQL db, both through ODBC and wach linked to a table of the same structure..
I have a Delphi componen DbNavigator - data aware. It has a post (update) button. Clicking that button on the access project yields a post to the access table. Clicking the same button on the same data but on thhe MySQL project yields an SQL error.
"Check the manual that corresponds to your MySQL server version for the right syntax to use near ','detail' SET '(' "
So far as I can see, all MySQL permissions exist for read/write access to
My confusion arises from the fact that to give entry level experience, I
chose data aware controls which I presumed would not need coding to function at a basic level. This works with Access but apparently not with MySQL. Clearly I am missing something fundamental regarding differences between MySQL's and Access's interactions with data aware components.
Can anyone suggest what it is that I am missing and how can I coax my
project to write to MySQL?
I think you'd better to consider any another way to relate with MySQL. Ie, Zeos library - this is very popular for such tasks. Or you could find another components to have direct connect to MySQL on www.torry.net .
Obviously, if you managed to get that error from MySQL, you did just fine connecting to it. For each SQL engine, there is a different dialect of SQL, and MS Access hardly uses a standard dialect.
If you want your application to be able to switch between these two databases, your best bet is to put the SQL statements in an external file, something that gets loaded at run time. Have a version for Access and a version for MySQL. Then drop in the appropriate SQL statements file depending on which database you're working with.
Now that that's cleared up, you just need to work out the correct syntax for what you want to do in MySQL. Access requires quotes in some places and MySQL doesn't.