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!

 

[dylim]

Hi Chriss,

I created a custom LoadCursor() method in lieu of CursorFill. In LoadCursor, it accepts a object which has as its properties the needed parameters. In this particular case, vpEmployeeID, vpDate1 and vpDate2. I then created the local variables with the exact same names before I call CursorFill().

This has worked like a charm for me for years already, only to hit this weird behavior a few days ago.

Cheers!

 

[Tore Bleken]

Weird behavior? It's not weird, it's well documented as a limitation in VFP. Please re-read my explanation in an earlier message.

Again, I strongly recommend that you use the text-endtext construct whenever strings can be long. Also text-endtext tends to be much easier to read.

So instead of

 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]

You write

Text to oLeaves.SelectCmd noshow Pretext 15 && 1+2+4+8
  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

The Pretext clause makes sure that the line feeds and other "empty" characters are removed, check Help for details.

We spend much time in providing free help, we expect people to read what we write.

 

[dylim]

Hi Tore,

The CAs are "pre-created" - I have already put the SQL statement in the SelectCmd property in the Properties page.

So, for caEmployee, I have something like [select * from employee order by lastname, firstname, middlename] in the SelectCmd property. For say, caDepartment, I have something like [select * from department order by code]. The square brackets are not included.

How do I use TEXT.. ENDTEXT.. to this end sir? In the Init()?

Thanks and Best Regards

 

[Tore Bleken]

I don't use CA's for several reasons.

Try this, it is just a wild guess:

Text to oLeaves.SelectCmd noshow Pretext 15 && 1+2+4+8
  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 
oLeaves.Cursorfill()

 

[Chris Miller]

If you have already set the SelectCmd, then you don'T have to set it in code.

On 1 Oct 23 11:46 you posted an assignemnt to SelectCmd, that would have better be done with TEXT..ENDTEXT. Never mind, if you just wanted to state this is the value of the SelectCmd. Then don''t post it in the form of an assignment. I can't read your mind, sorry.

Tha major point is that your query has parameters ?vpemployeeid, ?vpdate1, ?vpdate2

Thos variables have to exist and have values of proper data type when the CA executes. And if you put this CA in a forms data environment, it would, just for example, be too late to create and set those parameter variables in the form init.

Whreever you create those variables, it would also be the question whether the variable scope enables the CA to see them.

Tore has given you a short sample code that would actively call the cursorfill. If you have the CA in a form data environment that method fires as an event, or you could also say it is raised or called from the data envirnment. You might have created the CA with builder, we don't know nothing about that, but since we now at least know it has parameters, that's the most likely source of your error. You have to be aware of how to handle these parameters so the CA can work. Also in any other circumstance, whether the CA is in a form dataenvironment or you create and use it in code.

Well, one major point is that to ensure the CA has access to the query parameters, you make them properties of the CA. That's the main point. It then also becomes a good question where to set these properties to the values needed at runtime. If that was already known at design time there wouldn't be the need for parameters.

I don't even know if you just inherited a project or made all this yourself. The latter doesn't seem to be the case, or you'd already know better. It could be your last change to the code that introduced the error.

You still focus on showing us the query and seem to think we find an error in it. Well, there is a 100% guarantee a query with parameters fails in some way, if its parameters are missing or - that was my major point, even when they all exist and are set, but not accessible from the CA.

Again, going back to one of my earlier advice, if you start debugging before the error happens, for example before starting the form that has this erroring CA, you could find out what is the exact situation when the CA errors. In the first debug session you'd likely get to the error and for the next session would know the last step before the error occurs. And if you are there, it would be interesting whether variables vpemployeeid, vpdate1, and, vpdate2 exist, what is their scope (local, private, global), and what are their values? If they don't even exist it's no wonder your CA fails if their scope is local, they are only visible to the code that declared them, if they exist and are visible to the CA by their scope, then the query could still fail on wrong data types. A date should, for example, actually be a date and not the string of a date.

I hope this all makes your situation clearer, also all we still don't know about your setup, yet. But showing the query is surely not enough to see what's not working with a CA. There's more to it than just knowing the SelectCmd. it surely is the most important aspect, but I already told you multiple times the connection and thus driver options usd can play a role, the parameters are also now an important part of it. Likely the one that actually make all this break and that leads to the MySQL error.

Chriss