Difference in SQL Syntax between Access and MySQL (from VB6 project using ADO)

[Ian Davies]

Hello
Apologies for posting to so many groups but this one is difficult to
catagorize precisely

The following code worked fine with a connection to an Access database.
However, I have changed over to MySQL and am getting syntax error messages
with my code.

adoSetRS.Open "select [SetIndex], [Set] from tSet WHERE StatusID = 1 Order
by Set", db, adOpenStatic, adLockOptimistic

I cannot see why there would be any differences in the syntax to query an
Access as opposed to a MySQL database. After all its all SQL.
Or does anyone know different
or have experienced similar

PS The errors are not occuring on any particular part of the statment it
seems quite random
Help greatly appreciated
Ian

 

[Albert D.Kallal]

I bet the square brackets are a problem.

Why don't you try putting the sql directly in the MySql?

Apologies for posting to so many groups but this one is difficult to
catagorize precisely

Ah, gee, you would think you try a MySql newsgroup first with the raw
sql...would you not?

(so, yea..apology accepted..but you got make a bit better efforts on your
part...)..

Did you try the actual sql as a query in MySql?
Did you ask any of the MySql people first?

 

[Ian Davies]

Hi
The square brackets werent in initially. I put them in thinking it was the
lack of them causing the errors. Alas NOT.
I will try some of your other suggestions

Ian

 

[Ian Davies]

Ha
Seems that in my first example 'set' is a reserved word but cannot be
protected by enclosing in []. Does anyone know how I can include a field
called 'set' in my statment without the error?

That one solved I now got this one giving the same problem

******************************************************************
strPupilSQL = "SELECT tPupilsSubject.PupSubIndex, tPupilsSubject.SubjectNo,
tPupilsSubject.TeacherID, tPupilsSubject.YrIndex, tPupilsSubject.BandIndex,
tPupilsSubject.SetIndex, [Surname] & ' ' & [Firstname] AS PupilName"
strPupilSQL = strPupilSQL & " FROM tPupils INNER JOIN tPupilsSubject ON
tPupils.PupilNo = tPupilsSubject.PupilNo"
strPupilSQL = strPupilSQL & " Where (((tPupilsSubject.SubjectNo) " & ParSub
& ") And ((tPupilsSubject.TeacherID) " & ParTeacher & ") And
((tPupilsSubject.YrIndex) " & ParYr & ") And ((tPupilsSubject.BandIndex) " &
ParBand & ") And ((tPupilsSubject.SetIndex) " & ParSet & ") And
((tPupilsSubject.statusID) = 1) And ((tPupils.statusID) = 1))"
strPupilSQL = strPupilSQL & " ORDER BY tPupils.Surname"
***********************************************************

The error is on the first line. I cant see it
The ParSub, ParYr etc comes from

******************************
If cbdSubject.BoundText = "" Then
ParSub = " Like '%'"
Else
ParSub = "=" & cbdSubject.BoundText
End If
*******************************

 

[Albert D.Kallal]

Again, do a

debug.Print strPupilSQL

Now, cut and past the above into a query, ...does it work?

 

[Michael Cole]

Hello
Apologies for posting to so many groups but this one is difficult to
catagorize precisely

The following code worked fine with a connection to an Access
database. However, I have changed over to MySQL and am getting syntax
error messages with my code.

adoSetRS.Open "select [SetIndex], [Set] from tSet WHERE StatusID = 1
Order by Set", db, adOpenStatic, adLockOptimistic

I cannot see why there would be any differences in the syntax to
query an Access as opposed to a MySQL database. After all its all SQL.
Or does anyone know different
or have experienced similar

PS The errors are not occuring on any particular part of the statment
it seems quite random
Help greatly appreciated
Ian

All versions of SQL do differ slightly. To get the most out of reusable
SQL, stick to using ANSI SQL, but even then, there can be issues.

Basically, you cannot guarentee that SQL from one DB will work as is in
another.

 

[Bob Butler]

Hello
Apologies for posting to so many groups but this one is difficult to
catagorize precisely

The following code worked fine with a connection to an Access
database. However, I have changed over to MySQL and am getting syntax
error messages with my code.

adoSetRS.Open "select [SetIndex], [Set] from tSet WHERE StatusID = 1
Order by Set", db, adOpenStatic, adLockOptimistic

I'm not familiar with MySql but did you try escaping the other Set in the
Order By clause?
select SetIndex, [Set] from tSet WHERE StatusID = 1 Order by [Set]

 

[Donius]

In mysql ((and ask me why, i won't know, but)) the way to indicate a
table name, database name, or field name is to put backticks (`, not ')
around the table name, db name, or field name. I don't know if it will
solve your reserved word as a fieldname issue, but it's worth a shot.
The backtick is usually just above the tab button on a keyboard, in the
case that you're not familiar with it.

Hope that helps,
-Brendan

 

[Paul Clement]

¤ Ha
¤ Seems that in my first example 'set' is a reserved word but cannot be
¤ protected by enclosing in []. Does anyone know how I can include a field
¤ called 'set' in my statment without the error?
¤

I believe the reserved word must be quoted, otherwise it cannot be used.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Michael B. Johnson]

Hello
Apologies for posting to so many groups but this one is difficult to
catagorize precisely

The following code worked fine with a connection to an Access
database. However, I have changed over to MySQL and am getting syntax
error messages with my code.

adoSetRS.Open "select [SetIndex], [Set] from tSet WHERE StatusID = 1
Order by Set", db, adOpenStatic, adLockOptimistic

My guess is that because of the reserved word, the query just isn't going to
work, period.

Could you /work around/ it by creating a view in mySQL? Something like...

CREATE VIEW xyz
AS
SELECT [SetIndex], [Set] as QSet, StatusId
FROM tSet
WHERE StatusID = 1

then, in your code,

rs.Open "SELECT [SetIndex], QSet
FROM tSet
WHERE StatusId = 1", db, etc...

I haven't tried it myself, yet; might this work?
_______________________
Michael B. Johnson

 

[KwikOne]

Actually the problem is the field name 'Set' since that is a Reserved
word. In MySQL you would need to ensure the field name was enclosed in
back-ticks such as `Set`.

 

[KwikOne]

Michael,
Unless he was using MySQL 5.0 and up there is no such thing as Views
and even then it would not be downwards compatible.
And, even though the "Set" field is a reserved word, it should work
when enclosed in backticks.

Also, he has not indicated how he is connecting to the MySQL server -
OleDB, ODBC, or what (and versions of them plus the MySQL server). I
mention this since I know of a couple issues with the MySQL server
versions 3.23, and 4.0 with things like this. Plus the level of ODBC,
or OLEDb can be a factor also.

Kerry

 

[Ian Davies]

Thanks the back tick solved it.
I had problems with most of the SQLs I had previously used with Access.
Fortunately by a bit of trial and error I found that MySQL doesnt like
square brackets. So with the problem I had in an earlier post changing the
following
*****************************************************
SELECT
tPupilsSubject.PupSubIndex,tPupilsSubject.SubjectNo,tPupilsSubject.YrIndex,
[tPupils].[Surname] & ' ' & [tPupils].[FirstName] & ' - ' &
[tStaff].[NameCode] AS PupilName
FROM tStaff INNER JOIN (tPupils INNER JOIN tPupilsSubject ON tPupils.PupilNo
= tPupilsSubject.PupilNo) ON tStaff.TeacherID = tPupilsSubject.TeacherId
Where(((tStaff.StatusID) = 1) And ((tPupils.StatusID) = 1) And
((tPupilsSubject.StatusID) = 1) And ((tPupilsSubject.YrIndex) " & ParYr & ")
AND ((tPupilsSubject.SubjectNo) " & ParSub & "))
ORDER BY tPupils.Surname
*******************************************************
to
*******************************************************
SELECT
tPupilsSubject.PupSubIndex,tPupilsSubject.SubjectNo,tPupilsSubject.YrIndex,
tPupils.Surname & ' ' & tPupils.FirstName & ' - ' & tStaff.NameCode AS
PupilName
FROM tStaff INNER JOIN (tPupils INNER JOIN tPupilsSubject ON tPupils.PupilNo
= tPupilsSubject.PupilNo) ON tStaff.TeacherID = tPupilsSubject.TeacherId
Where(((tStaff.StatusID) = 1) And ((tPupils.StatusID) = 1) And
((tPupilsSubject.StatusID) = 1) And ((tPupilsSubject.YrIndex) " & ParYr & ")
AND ((tPupilsSubject.SubjectNo) " & ParSub & "))
ORDER BY tPupils.Surname
*******************************************************
Hope this helps someone else
Thanks Albert I just realised you did suggest that too in your earlier post
Ian

 

[Jeff Johnson [MVP: VB]]

Hope this helps someone else

I hope it helps someone else learn not to use reserved words as column
names....

 

[KwikOne]

Hopefully ...
But, I wont hold my breath that someone else wont come along with the
same type of problem (I too learned the hard way when I upsized my
Access DB to MySQL). I also learned that for MySQL it is safer to
ALWAYS use the backticks for both table and field names.

Kerry

 

[Michael Cole]

Actually the problem is the field name 'Set' since that is a Reserved
word. In MySQL you would need to ensure the field name was enclosed in
back-ticks such as `Set`.

Backticks! Now that is _ugly_. Brackets, parenthesis, braces, quotes,
double-quotes - fine. Confusing maybe, but fine. Who the hell came up with
the MySQL idea. I'm glad I've never had to work with it, if that is an
example of its syntax.

 

[Bill Karwin]

Backticks! Now that is _ugly_. Brackets, parenthesis, braces, quotes,
double-quotes - fine. Confusing maybe, but fine. Who the hell came up with
the MySQL idea.

Yes, it is unfortunate. The ANSI SQL 92 standard for delimited
identifiers is double-quotes. But if one has implemented a DBMS that
uses double-quotes for string delimiters, then you risk breaking
everyone's existing code if you change the meaning of those symbols. So
the solution is to use some other symbol.

InterBase, for example, solved this problem in a different way when they
implemented delimited identifiers. They created a connection-level
property called "sql dialect" that allows applications to switch between
the old behavior of double-quotes (as string delimiters) and the new
behavior (as identifier delimiters); the default was the old behavior,
so their customers' existing apps wouldn't break.

Regards,
Bill K.

 

[Bill Karwin]

the solution is to use some other symbol.
InterBase, for example,

Sorry -- that wasn't clear, because InterBase is not an example of using
a different symbol like MySQL did. InterBase's solution is an
alternative to that. Just want to be clear.

Regards,
Bill K.

 

[Ralph]

Sorry -- that wasn't clear, because InterBase is not an example of using
a different symbol like MySQL did. InterBase's solution is an
alternative to that. Just want to be clear.

Regards,
Bill K.

This is OT but I vaguely remember some non-ANSI SQL that used nested single
and double quotes, a la JavaScript, that you could alternate to get even
deeper...
"Outer stuff 'medium stuff "lower stuff 'innerstuff'"'". The escape was to
double up on the quote. It was a nightmare. <g>

Do you or anyone remember what database that was?

Just curious.

-ralph