sql_mode=ANSI

[peterlaursen]

to use mysql with the program ThumbsUp http://www.cerious.com/ I had to insert the string “sql_mode=ANSI” in my

my.ini file. That works OK. However now Access can't connect to the base.

Does anybody have a solution or a workaround ?

Mysql 5.0, lastest ODBC, Access2000.

[peterlaursen]

“group by” .. and “order by”

oh I found out myself .. problems with a combination of the “group by” .. and “order by” .. clause

It wasn't perfectly true what I wrote, actually Access could connect, but not execcute sql that it could before the change in my.ini . But I noticed it could do a “select *”. Esperimenting with omiting parts of the sql I found this solution

Before the change the sql should look like:

SELECT [mp3_filea].[Album], [mp3_files].[Artist], [mp3_filer].[Genre]

FROM [music].mp3_filer

WHERE [mp3_filer].[Artis] Is Not Null And [mp3_filer].[Album] Is Not Null

GROUP BY [Album], [Genre], [Artist]

ORDER BY [Albuml], [Genre], [Artist];

After the change it must me:

SELECT [mp3_filer].[Genre], [mp3_filer].[Artist], [mp3_filer].[Album]

FROM [musik].mp3_filer

WHERE [mp3_filer].[Artist] Is Not Null And [mp3_filer].[Album] Is Not Null

GROUP BY [genre], [Artist], [album];

(the point is to have an album-artist-genre combination listed only once no matter how many tracks there are og no matter whether track no.s are available!)

It can be quite tricky to write sql that is accepted and interpreted identically by Access, NyODBC and MySQL ….

But thanks for viewing 😆

[peterlaursen]

of course I messed it up!

Before the change the sql should look like:

SELECT [mp3_files].[Album], [mp3_files].[Artist], [mp3_filer].[Genre]

FROM [music].mp3_filer

WHERE [mp3_filer].[Artist] Is Not Null And [mp3_filer].[Album] Is Not Null

GROUP BY [Album], [Genre], [Artist]

ORDER BY [Albuml], [Genre], [Artist];

After the change it must me:

SELECT [mp3_files].[Album], [mp3_files].[Artist], [mp3_filer].[Genre]

FROM [musik].mp3_filer

WHERE [mp3_filer].[Artist] Is Not Null And [mp3_filer].[Album] Is Not Null

GROUP BY [Album], [Genre], [Artist];

[Author]

Posts