Changing database from access to my sql - changing in coding required?

[user_1]

Hi...

So far i was working with Labview and Access.As the requirement,we have to migrate from access to MySql.

Now dealing with queries i am facing lots of problem.Many of my queries worked well with Access is not working with MySQL.

I had used some complex queries.Even queries to extract monthly data(where in the database  its in year-month-date format),

where i have used format function and group by clause is not working properly with My SQL.

Why its like that?In both access and mysql we are using sql queries.Is there syntax difference in MySQL? 

the following query worked well with access ...

SELECT DISTINCTROW  format$(Avg([ID]),"#0.00"), Format$(avg([Date]),"dd/mm/yyyy"),format$(Avg([Res Lvl]),"#0.00"), format$( Avg([IW-B-(L)]) ,"#0.00") FROM `DPD-BTU-SEEP`GROUP BY Format$([DPD-BTU-SEEP].[Date],"yyyy/mm"), Year([DPD-BTU-SEEP].[Date]) ORDER BY Format$([DPD-BTU-SEEP].[Date],"yyyy/mm");

But its not working with mysql.

I tried to execute the query in the query browser directl also but getting error.Date is in the format of YYYY-mm-dd in my sql so i have to change.But dont know what is the problem with the format function format$(Avg([ID]),"#0.00").Its also having error.

Even the quesion is more related to data base than labview can any body help me regarding this?

Thanks in advance...

[mikeporter]

First a little correction in terms. Access is not a database. This is not intended to be a snide comment, just a clarification. The Access program is an application development environment that is built on top of a database engine built into Windows called Jet. To see this is true you can take code that I have posted elsewhere for talking to "Access databases", copy it on a Windows machine without Access installed and it still operates as advertised. In fact Microsoft provides mechanisms for connecting Access to other database engines such as SQL Server and even Oracle.

Second, any time you are migrating from one database to another, operations related to time and date values are problematic because historically the SQL standard hasn't defined this area very well. (This is changing but it takes time for vendors to catch up.) You need the mySQL version of the format command that you have in the query. You will find this in the mySQL documentation and will help with both your formatting issues. By the way, mySQL has a users forum like this one. Keep it in mind as another resource for you as well.

Other areas of potential conflict are places where Jet or mySQL don't conform to the SQL standards or the standard is silent on a particular point. This is why in my work I try to keep the SQL I use as generic as possible. However, on the bright side, remember that you are not the first person to make this transition. In fact I would guess that moving from Jet to something faster, securer and more reliable is positively common. In any case, the mySQL forum would be an excellent place to find out details of issues that other people ran into. As a place to start, check here.

Hope this helps,
Mike...