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:

Quote (dylim)

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

vs

Quote (dylim)

[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

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?

Quote (dylim)

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

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:

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.

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 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 https://www.w3schools.com/sql/sql_ref_as.asp


Regards

Griff
Keep 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:

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 

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

How is this?!

Quote (myself)

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

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.

Quote:

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

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

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

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.

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

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

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.

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

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.

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

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.

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

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 

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.

Quote (dylim)

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

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):

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 
 

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

Thanks and best regards.

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

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".

Guys,

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

Thanks!

Love live the Fox!

Cheers!

Quote (dylim)

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

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

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

Chriss,

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

 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]
 

Which works.

Thanks a lot sir!

It won't surprise you I suggest


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:
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

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!

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
You write
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.

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

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

Try this, it is just a wild guess:

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