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
the DB.

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?

Hi!

DBNavigator is the db-aware control to have navigation via dataset. And which components do you use to have an acces to MySQL table? Any TQuery, TTable or something like that?

Hi khibinite,

I was using adoconnect, adodataset and adoquery - communicating to by use of the DBNav. I since dropped the query and added adocommand to update, triggered from a button - this on advice received.

Thanks for the interest. Whether or not tha above works (which I suspect it will - I have more to learn but at the end, more control) I would love to hear your take on the original problem.

Thanks,

John.

Hi!

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 .

Are you saying MySQL has problems with ODBC and/or ADO? Or merely that there are better was of dealing directly with MySQL?

I have a strategic reason for using middleware.

Regards and thanks,

John

Yes

Yes - well I heard of Zeos, downloaded it and struck difficulties compiling & installing. I'm not a very techy sort of guy.

As I said, a prime objective is to use and master a 'universal' middleware product.

Can you comment more on use of Zeos?

Regards & thanks,

John.

Which version of Delphi do you use?

http://www.thedelphimagazine.com/Samples/1540/1540.htm

Delphi 6 Pro

Hm, I have Delphi 7 Ent, but I haven't any problems. Which kind of trouble did you get?

I will have to revisit this later and come back to you. I do hope I can do that as I want to follow up on this.

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.

'nuff said.

Thank you for responding.

You suggest something like using multiple files or multiple statements from which user selects an appropriate file or statement set, probably by means of a run-time parameter selection?

An interesting idea. Isn't that what middle-ware components normally attempt?

Regards,

John.