MySQL 5.7 ODBC Error 1064

[dylim]

Hi Experts,

When I run the following query in MySQL Workbench, it works:

[pre]select a.employeeid, a.leaveid, b.datefrom, b.dateto, b.approved from lvreqhdr a inner join lvreqdtl b on a.id=b.lvreqhdrid where a.posted=1 and a.cancelled=0 and b.approved>0 and a.employeeid='11EE2D00AAC9C336A758CCB8ADFBA6C1' and b.datefrom>='2023-01-01' and b.dateto <='2023-10-10'[/pre]

But when I run the same code in a CursorAdapter, I get this error 1064.

Error message is as follows:

[pre][MySQL][ODBC] 8.0(a) Driver][mysqld-5.7.40-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' b.dateto, b.approved from lvreqhdr a inner join lvregdtl b on a.id=b.lvreqhdrid' at line 1
[/pre]

Any help or lead is highly appreciated.

Thanks in advance. Long live the Fox!

 

[Chris Miller]

Hi dylim,

it's nice you make use of form features like PRE, but notice the text does not wrap. That makes it hard to help with it.

Chriss

 

[Chris Miller]

The onl thing I see that may play a role:

[MySQL][ODBC] 8.0(a) Driver]

vs

[mysqld-5.7.40-log]

Using the MySQL 8.0(a) driver to access a 5.7.40 database may not work out fine, no matter if you only use SQL that MySQL 5.7 also understands.

Chriss

 

[dylim]

Hi Chriss,

What do you mean specifically? That MySQL ODBC 8.0 cannot be used to access MySQL server 5.7?

Funny though, this has worked for me a few years already. Although I have to say I encountered quite a few weird behaviors such as this current issue.

Re using PRE, should I do away using these you mean?

 

[Chris Miller]

That MySQL ODBC 8.0 cannot be used to access MySQL server 5.7?

That's all I can see that would cause this.

You can use PRE, but you have to wordwrap manually PRE is not doing wordwrap. I don't know if you're using a 2560 width screen, but on HD (1920) he pre text exceeds the width of a browser even in full screen mode.



Chriss

 

[dylim]

Chriss,

The MySQL documentation 'recommends' ODBC 8.0 for MySQL Server 5.7 and up.

Now, I tried using SQLEXEC( nDataSource, cSQL ).

This is really getting weird. At the command prompt, I was trying the load the SQL statement to variable cSQL, like so:

[pre]
cSQL = [select a.employeeid, a.leaveid, b.datefrom, b.dateto, b.approved
from lvreqhdr a inner join lvreqdtl b on a.id=b.lvreqhdrid
where a.posted=1 and a.cancelled=0 and b.approved>0 and
a.employeeid='11EE2D00AAC9C336A758CCB8ADFBA6C1' and b.datefrom>='2023-01-01' and b.dateto<='2023-10-10']
[/pre]

It shows an error: Command contains unrecognized phrase/keyword.

Is there some reserved word I should know?!

 

[Chris Miller]

Do you know how to write multiline statements in VFP?

Line continuation has to be done with semicolons.
But doing that within string delimiters can be problematic, as a semicolon is allowed to be part of a string and then line continuation can work or you get a semicolon in the midst of the SQL string and maybe just a partial string.

So finally look what you have in cSQL or in the cursoradapter SelectCmd property. If it's just half of the SQL you want to execute it's no wonder it fails, is it?

There's a simpler way to deal with setting long and/or multiline text to character type variables, use TEXT TO..ENDTEXT syntax:

TEXT TO cSQL NOSHOW
select a.employeeid, a.leaveid, b.datefrom, b.dateto, b.approved 
  from lvreqhdr a inner join lvreqdtl b on a.id=b.lvreqhdrid 
  where a.posted=1 and a.cancelled=0 and b.approved>0 
    and a.employeeid='11EE2D00AAC9C336A758CCB8ADFBA6C1' 
    and b.datefrom>='2023-01-01' and b.dateto<='2023-10-10'
ENDTEXT

That'll actually include CHR(10) and CHR(13) in the cSQL and you might want to remove those before SQLEXEC or before setting this into a cursoradapter SelectCmd property.

Chriss

 

[GriffMG]

I am wondering if you need to be a bit more explicit using AS:

cSQL = [select a.employeeid, a.leaveid, b.datefrom, b.dateto, b.approved 
        from lvreqhdr AS a inner join lvreqdtl AS b on a.id=b.lvreqhdrid 
        where a.posted=1 and a.cancelled=0 and b.approved>0 and 
        a.employeeid='11EE2D00AAC9C336A758CCB8ADFBA6C1' and b.datefrom>='2023-01-01' and b.dateto<='2023-10-10']

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[dylim]

GriffMg,

I have never used AS as part of the FROM clause.

 

[dylim]

Hi Chris,

The use of TEXT... ENDTEXT worked, either using the actual table names as prefixes or using a or b.

What also worked was when I did not use the list of fields but used:

select a.*, b.* from lvreqhdr a inner join lvreqdtl b on a.id=b.lvreqhdrid
where a.posted=1 and a.cancelled=0 and b.approved>0 and
a.employeeid='11EE2D00AAC9C336A758CCB8ADFBA6C1' and b.datefrom>='2023-01-01' and b.dateto<='2023-10-10'


Any idea why this behavior is such?

 

[GriffMG]

My SQL is very weak Dylim, I do use AS makes it clearer for me.

** Edit **

From

https://www.w3schools.com/sql/sql_ref_as.asp

SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerName="Around the Horn" AND c.CustomerID=o.CustomerID;

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[Chris Miller]

dylim, a VFP string literal, that is a string in delimiters in source code, can only reach 255 characters, longer strings cause the error "unrecognized phrase/keyword".

In short, always look into cSQL or CA.SelectCmd what actually is in it and what you therefore send to MySQL. An incomplete string obviously causes query errors.

If ou always use TEXT TO .. ENDTEXT even for shorter single line queries, you can't go wrong, and you could also make use of textmerge features this command offers. Take a deeper look into the help topic of it.

Chriss

 

[dylim]

Chris,

Hi. You are right. The SQL statement was over 255 chars long. How come though, that when you use TEXT.. ENDTEXT then issue the same SQL statement, works?

 

[Chris Miller]

It's a limitation of string literals, not of string variables. See a help chapter called system capacities.

String variables can be as long as 16MB, there surely is a shorter limitation of MySQL for queries. TEXT...ENDTEXT overcomes this 255 bytes limitation of source code.
I don't know why this is, maybe the compiler only uses one byte to store the offset from a starting delimiter to the next, but strings in code have to be kept short unless you use TEXT ENDTEXT. VFP is not alone in that aspect, in PHP you have the heredoc/nowdoc mechanism that's very similar, in C# you have @ (verbatim string), in Java you have text blocks delimited by """ (triple double quotes), and so on. I don't know any programming language without any concept for longer strings, and queries are usually that. So how do you live without it, anyway?

Chriss

 

[dylim]

Chris,

Here is the crazy thing about this. Check this out this SQL statement:

[pre]
select a.*, b.descriptor as paygrpname, b.frequency, c.descriptor as deptname, d.descriptor as position, e.descriptor as bankname,
f.descriptor as shiftname from employee a inner join paygroup b on a.paygroupid=b.id inner join department c on a.deptid=c.id
inner join position d on a.positionid=d.id left join bank e on a.bankid=e.id left join shift f on a.shiftid=f.id[/pre]

The above is way longer than the other one. But this one does NOT produce any error.

How is this?!

 

[Chris Miller]

It's a limitation of string literals, not of string variables.

I guess you didn't understand this. But I thereby already told you longer strings are not a problem for string variables. You can have strings up to 16MB length, or was it 32MB? Clearly not the length of that query you wonder about, though.

It's only string literals, strings in source code delimited by quotes, double quotes or square backets, that can't be longer. Strings within TEXT ENDTEXT are not limited that way in source code and in variables you also could put in longer strings by using 'part1'+'part2' etc. - but TEXT..ENDTEXT is much more convenient, isn't it?

Why did I mention how other programming languages also have constructs for multiline strings? It's normal to use them and only these constructs allow you to format queries readably and maintainable anyway, so forget about this limit and go. Be professional and use TEXT..ENDTEXT to prepare statments in VFP, just like you would use other multiline string constructs in other programming languages. It's a norm, anyway.

The next higher limit you'll encounter, maybe, is of MySQL. IIRC older MS SQL Server vvrsions had a limit of 8192 charcters for queries, MYSQL similarly, I guess. Today this also can be longer.

Chriss

 

[dylim]

Chris,

I use the SelectCmd property to put in all my SQL statements. It puzzles me why the shorter SQL statement produces an error message while the longer one does not.

How do I implement TEXT.. ENDTEXT to put in the SelectCmd property?

Thanks as always.

 

[Chris Miller]

It puzzles me why the shorter SQL statement produces an error message while the longer one does not.

Really, what do you expect as MySQL reacton to a short and incomplete query. What's puzzzling about this?

Chriss

 

[dylim]

Chris,

You lost me sir. What do you mean by 'incomplete query'?

 

[Chris Miller]

If you have a query longer than 255 characters, that works, due to VFPs limit of strings in code, then the query of your source code does get cut off and you send over the cut off sql query to MySQL which of course fails.

You solved the problem by using TEXT..ENDTEXT, have the complete query in the VFP variable or the SelectCmd propertyy of the CA, details don't matter, and now the complete query sent to MySQL does not fail. Simple. What is still puzzling you?

Chriss