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]

If you still don't understand it, simply take that for your future of programming.
Using the right tool for the right job helps, in this case for long multiline texts TEXT..ENDTEXT.

Prepare your queries this way, they are transferred to the variable of property in the first place, and then to MySQL. Nothing is cut off, shortened, incomplete and as long as you transfer what you tested in the WorkBench to a TEXT..ENDTEXT statement, that will also work from within VFP. Just enjoy that it works, no need for a deeper understanding about the string literal limits, you just don't use them anymore for your queries.

Notice, you cannot only set variables with TEXT TO. The TO target of TEXT TO could also be a property, like TEXT TO YourCA.SelectCmd NOSHOW .... ENDTEXT.

Chriss

 

[dylim]

Hi Chriss,

What is puzzling to me is that this particular cursoradapter's SelectCmd statement that produces the error is 268 chars.

I have other cursoradapters which have SelectCmds which are more than 400 chars even.

This is what bothers me.

Thanks and best regards.

 

[Chris Miller]

Well, there must have been some error in that query, anyway.

One source of error can be, that Workbench will support Unicode, when you copy over the query, you might lose something due to codepage conversion to ANSI Windows 1252 codepage, no matter if you paste into a PRG or method or into a property.
I would recommend logging, then you know what exactly arrives in MySQL and can find out why it fails. Query length is not the only factor but surely was the reason in the first case you posted.

By the way, I just checked that a property can store longer queries, too. I would have bet on that only working if you use the [...] button to pop up a memo box for setting longer strings to a property, but you can simply copy&paste a long query into a property in the property Window and when instanciating it it's not cut off to 255 chars.

For sure MySQL doesn't fail when receiving a working query. The error can come in at the point you transfer it from the Workbench into the CA SelectCmd, the transfer from VFP via the ODBC connection could also have a loss due to codepage translations.

I saw from your posted error messages that ou use the ANSI driver, but that's a minimum requirement for VFP to work properly, you can still set up connections that then require the query to be in Uniccode, UTF8 or other codepages.
Logging what arrives in MySQL server will show you more than just looking into the VFP code.

Chriss

 

[Gerrit Broekhuis]

Hi Dennis,

In fact there is NO reason NOT to use TEXT…ENDTEXT for every SQL query. I’m doing that for more than 10 years now and I never had errors with (very) long queries.

Regards, Gerrit

 

[dylim]

Chriss,

This alone means that it is happening at the VFP side of things:

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']

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

* Do take note that the above code is in actuality one single line. I purposely inserted CRs for readability.

 

[mjcmkrsr]

Hi Dylim,

Are datefrom/dateto dates? If yes they have to be put between curly braces, best with the caret ^ to avoid misunderstandings - like {^2023-01-01}

hth

MarK

 

[dylim]

mjcmkrsr

The data values are in single quotes as this is what MySQL handles date values.

Nevertheless, I did what you suggested. Same error still.

 

[Tore Bleken]

Please show your complete and exact code, where it fails and what error message you see.

 

[dylim]

Hi Tore Bleken,

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

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'

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

Error message is as follows:

[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

So, I tried using SQLEXEC as well. I assigned the string to var cSQL to be pass as a parameter to SQLEXEC():

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']

* the above code is in actuality one single line. I purposely inserted CRs for readability.

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

 

[Tore Bleken]

Chris, and I believe others as well, has shown you how to use a text-Endtext construct instead of cSQL=... And you have also been told why this works, and your code doesn't!

From Help: "Maximum length of a string literal: 255". And a String Literal is the string you create by a single var = 'xxxxxx'.

[pre]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[/pre]

Notice that this will actually work, and it also makes it much more readable. Why you insist on using your code that doesn't, really puzzles me.

When we give help for free, we expect people to pay attention to what we suggest.

 

[Chris Miller]

Command contains unrecognized phrase/keyword.

We were through with this, it's the error that points out a string literal - this query string in the assignment to cSL - is too long.

It doesn't explain, how you could execute it, though, so you must have done something else that was at least forwarding something to MySQL, so you could get back the error message from MySQL, which you posted. So that's codee not failing with the SQL error, that's code ffailing before you even get to the point of doing SQLEXEC. Or did you ignore that error and just continued? Did you inspect whaat cSQL is, what you actually sent to MySQL? If you have no further code previous to that, the failing assignment would mean cSQL is either empty or .F. or the variable isn't even defined.

That code you last posted fails, but as it fails even before the SQLExec it also won't give you an SQL error message, it simply won't get to the point of sending cSQL to MySQL Server at all, as cSSQL isn't set if the assignment of cSQL=[tooo long string] doesn't work.

So you did use CA, you said, and you put in exactly that query in the SelectCmd property, right?
What is your connection string, dylim?

Chriss

 

[dylim]

Tore,

From Help: "Maximum length of a string literal: 255". And a String Literal is the string you create by a single var = 'xxxxxx'.

I do acknowledge that using TEXT.. ENDTEXT does work.

However, I have used the SelectCmd property exceeding the 255 maximum length sir. Some are even up to 500 chars long. The SelectCmd below is 378 chars (and it works):

[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]
I simply wonder why this particular string, which is approximately "only" 260+ chars, produces the error.

Thanks and best regards.

 

[dylim]

Chriss,

So you did use CA, you said, and you put in exactly that query in the SelectCmd property, right? --- YES

My connection string is:
DRIVER=MySQL ODBC 8.0 ANSI Driver;PORT=1686;SERVER=localhost;DATABASE=payprox;UID=root;PWD=p@55w0rd;SSLMODE=DISABLED

 

[Tore Bleken]

Dylim,

"I simply wonder why this particular string, which is approximately "only" 260+ chars, produces the error."

OK, I'llk explain. Try the following in the command window:
x='a'
No error. Then you try
x='aa'
Again, no error. Then you try
x='aaa'
Again no error.
Now continue, and add one more 'a' each time. All of a sudden you will get an error, and that's when you have entered a string which has 256
a's in it. 255 works, 256 doesn't. That's why 255 is the maximum length of a string you can enter in ONE line of code, aka "string literal".

 

[dylim]

Guys,

Anyways, thanks a lot for spending some of your very precious time in answering my query.

Thanks!

Love live the Fox!

Cheers!

 

[Chris Miller]

I simply wonder why this particular string, which is approximately "only" 260+ chars, produces the error.

Apply Sherlock Holmes: When you have eliminated the impossible, whatever remains, however improbable, must be the truth.

So that query is in the CA at designtime. But is it actually what finally is in SelectCmd at the time the CA executes the cursorfill? You have to have something that changes this from the working query to something that errors.

So, as I already sketched previously, there is query logging available, to find out what actually was received by MySQL and errors. The error message, as you know, will not tell you the full query that executed, does it? MySQL reports a syntax problem "near" part of the query MySQL Server received.

To find out what actually executes you can't just look at the SelectCmd at designtime, this isn't a reaadonly query, the form init or load, several data environment methods or events and the CA init and other code (beforecursorfill event, for example) could modify SelectCmd before you get to the actual cursorfill.

On top of all that, the query as is and seems right to the eye, can have a codepage conversion problem when sending it over to MySQL, even though you use the ANSI driver. Your connectionstring doesn't set OPTIONS=flags, and then you use defaults for several options. Look into that what it means in general.

In short, be sensible and use debugging. Have a breakpoint at the important moment. Just looking at what the query is at designtime doesn't tell you that this is executed as is, it's not a readonly property, it's not a property you can only set at designtime and there can be a modification through transposing from VFPs ANSI codepage on the way to MySQL. To finally see what actually executes in MySQL the best thing is to use query logging and see what MySQL receives, then you can find the error.

Something has to happen that turns the working query to a syntactically wrong, and you have to investigate. Gazing in unbelief at the working query set to SelectCmd at designtime won't tell you what MySQL actually receives.

Chriss

 

[Chris Miller]

Let me summarize what you did to debug your query:

You took the query as it's in the CA SelectCmd property, put it into a TEXT ENDTEXT, and executed that with SQLExec(). That worked and now you think you're done with debugging and you're puzzled why it doesn't work in the CA?

Look back at what I already posted, there are lots of reasons your test isn't replicating what happens through using the CA. There are many locations in which the SelectCmd may be modified, the error from MySQL shows you a part of the query that's familiar to you, so it seems the query wasn't altered. If it wasn't, you wouldn't have a problem, though. So something must change from the outset until the cursorfill, or it's altered within the transfer to MySQL.

The optimal point to know what MySQL executed is if you could set a breakpoint in MySQL server at the point it receives a command before it executes. The best alternative you have is query logging. And the next best is setting a breakpoint in VFP just before the execution of the CA.SelectCmd. So set a breakpoint in the CA.beforecursorfill event and then inspect SelectCmd. Notice, if there is no code in that event, simply add in something like LOCAL Dummyvariable and you can set a breakpoint there.

Because, all you could verify so far is that this query string works, which you already knew from Workbench, where you created and tested it first. You only eliminated the connection being the culprit, but is the CA using that same connection or does it make one itself? What else runs in the context of using the CA? Those questions should be on your mind and debugging what actually runs.

Chriss

 

[Chris Miller]

Did you resolve the issue?

Your debugging wasn't covering all aspects of how things should turn out because you made some too simple assumptions:
Your debugging thought was, if you make a connection and test the same query with SQLEXEC that would prove the CA should also work as there's nothing different about it.

The SelectCmd set at design time in the CA could be modified or overridden by CA code, including inherited code. I pointed out there is the BeforeCursorfill method. Thats empty in the CA? Well, think about inheritance, too. It's a native CA, not based on another CA class? Ok, then there still is Init(). In short, just single step through all that happens.

The connection the CA ses may be different from what you use in your SQLExec replacement experiment. There are a lot of options with MySQL connections that influence the behavior of automatic conversions and much more. That can render the same query non working, in some cases. Again this can be set up different than what you see at design time and you'll only see what really is going on with debugging.

So I talk about debugging, that should start at the point in code before you DO FORM or CreateObject("formclass"), then you can also single step through whatever code is run in the DE of the form.

There is a query error happening, that just points out something differs from your SQLEXEC experiment, it's insufficient to prove the CA works, it's obvious, but you seem reluctant to see that this replacement experiment isn't all you can and should do.

Chriss

 

[dylim]

Chriss,

I will be revisiting this weird problem once I finish this ongoing project. For the meantime, I 'reformulated' the problematic query to:

[pre] oLeaves.SelectCmd = [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=?vpemployeeid and b.datefrom>=?vpdate1 and b.dateto<=?vpdate2]
[/pre]

Which works.

Thanks a lot sir!

 

[Chris Miller]

It won't surprise you I suggest

 Text To oLeaves.SelectCmd NoShow
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=?vpemployeeid and b.datefrom>=?vpdate1 and b.dateto<=?vpdate2
ENDTEXT

Also, for such a paramterized query to work, you have to ensure the variables vpemployeeid, vpdate1 and vpdaate2 exist and are accessible (by means of their variable scope) at the time of execution. For a CA that means when cursorfill happens.
I suggest you instead make these parameters properties of the CA object and then reference them like this:

Text To oLeaves.SelectCmd NoShow
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=?this.vpemployeeid and b.datefrom>=?this.vpdate1 and b.dateto<=?this.vpdate2
ENDTEXT

This will refer to the CA object.

As properties of the CA there's no failure possible. Using mere variables could easily be the reason your query fails as a missing variable would lead to some mishap. I'm not even sure without testing what exactly happens, but in a normal view you'd get an interactive inputbox for the value, you didn't get this, so maybe it's just kept literally in the query.

Why didn't you mention them before? Your original query had just literal values for these columns. If you have local variables in the code setting SelectCmd, that fails, even if you then call cursorfill yourself, the local scope is, well, local only. If you use private variables they can be accessible at cursorfill, if you actively call it. But if the CA is put into a forms data environment, the cursorfill isn't called, it's rather happening as an event when the DE iterates all its cursor objects to open the tables and views and CAs. Now that's not even necessarily seeing private variables. Private variables are seen locally and in called methods down the callstack hierarchy, but events happening are not calls. So the easiest most secure way is to put those parameter values into properties.



Chriss