MySQL 5.7 ODBC Error 1064

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

The onl thing I see that may play a role:

dylim said:

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

Click to expand...

vs

dylim said:

[mysqld-5.7.40-log]

Click to expand...

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

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?

dylim said:

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

Click to expand...

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

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?!

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:


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

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


Regards

Griff
Keep [Smile]ing

GriffMg,

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

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?

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

** Edit **

From

Regards

Griff
Keep [Smile]ing

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

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?

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

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?!

myself said:

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

Click to expand...

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

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.

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

Click to expand...

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

Chriss

Chris,

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

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