String comparison error 1

[David Higgs]

The following Code Queries my MySQL database for Contacts made on 29/04/2019

log_option = "left(Time_on,10) = '2019-04-18' " 
MyLog_Selection = "SELECT * FROM &TBL_Logbook where " + log_option

SQLEXEC(SQLCONN,MyLog_Selection ,'cTempLogbook')

I would like to replace '2019-04-18' with a “variable” so that the MySQL will return a list of Contacts made on the day of querying the Database. To replace '2019-04-18' I used the following Code:

LEFT(TTOC(DateTime(),3),10)

which returns ‘todays date’ in the following format 2019-04-18

MyLog_Selection = "SELECT * FROM &TBL_Logbook where Left(Time_on,10) = (LEFT(TTOC(DateTime(),3),10)) "

SQLEXEC(SQLCONN,MyLog_Selection ,'cTempLogbook')

The above code produces an “Alias 'cTempLogbook' is not found” error, presumably due to a failed connection.

I have tried numerous options to no avail. Any help would be much appreciated.

Regards,

David.

Recreational user of VFP.

 

[Olaf Doschke]

The MySQL database doesn't know VFP functions. So you have to execute VFP code on the VFP side and only send the resulting SQL to MySQL.

What can be done is using parameterization, that requires a question mark, brackets around an expression don't suffice.

That you're using Left(Time_on,10) in your MySQL code is a sign your MySQL database stores dates as strings, that's not what you do, you keep data in the data types they are best stored in.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[David Higgs]

That you're using Left(Time_on,10) in your MySQL code is a sign your MySQL database stores dates as strings, that's not what you do, you keep data in the data types they are best stored in.

Strange thing is "Time_On" is a DATETIME Field yet the following Code works.

log_option = "left(Time_on,10) = '2019-04-18' " 
MyLog_Selection = "SELECT * FROM &TBL_Logbook where " + log_option

SQLEXEC(SQLCONN,MyLog_Selection ,'cTempLogbook')

Regards,

David.

Recreational user of VFP.

 

[David Higgs]

Experimenting with PHP MyAdmin I found the following code works.

MyLog_Selection ="SELECT *  FROM `Logbook` WHERE `Time_On` LIKE '%2019-04-18%'"

I'll continue with my VFP Code and see how it works.

Regards,

David.

Recreational user of VFP.

 

[Olaf Doschke]

Indeed strange, as MySQL reference tells LEFT() needs a string and a numeric parameter. There is no such thing mentioned as implicit conversions.

Bye, Olaf.




Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

Nevertheless, you didn't react and understood completely.

This expression doesn't work in MySQL:

(LEFT(TTOC(DateTime(),3),10))

MySQL has the datetime type, but no DATETIME() function. And no TTOC(). So as said:

you have to execute VFP code on the VFP side and only send the resulting SQL to MySQL.

LOCAL lcDate
lcDate = LEFT(TTOC(DateTime(),3),10)
log_option = "left(Time_on,10) = '"+lcDate+"'"
MyLog_Selection = "SELECT * FROM &TBL_Logbook where " + log_option

And it could even be more elegant without string conversion using parameters. But take that aside, this is straight using your recipe and methodology you introduced yourself, simply stay with it.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

And now some much more general things to learn:

Inspect what you're executing when you first do, so always debug your first run. You put together a query MySQL can't execute as it has VFP code in it.
In the simplest case here? MyLog_Selection to display the query on screen or set _cliptext=MyLog_Selection to have it in the clipboard and then paste it into phpmyadmin query window or MySQLWorkbench or HeidiSQL or whatever administration tool you use for MySQL.

Then SQLEXEC has a return value indicating three different things:

<0: an error occurred
=0: code still executing (only happens when you choose to allow asynchronous execution - see SQLSETPROP setting 'Asynchronous').
>0: OK, number of result sets (notice again SQLSETPROP offers options to allow multiple queries to return multiple results, then also see SQLMORERESULTS()

At this point notice, you should always at least peek into the help for further functions and commands in the same context when you learn something. SQLStringConnect, SQLExec, and SQLDisconnect is just the minimum set of functions. There's much more to it and to know.

I'll just dive into one more feature regarding that result value <0. When an error occurs in a remote database, there will be nothing like a message box there nor in your VFP process, a server is meant to continue to serve data, so it'll just return the info about an error, stop execution and be available for the next request, it can serve more connections, so it can't wait with a modal error message for something, it has to go on.

So, in this case, it's up to you to see whether there is an error and then get information about it. One way of letting Foxpro itself react automatic (the server still just ignores, stops your script and falls back into processing next requests) is using SQLSetProp(SQLCONN, "DispWarnings",.t.) after making the connection (SQLCONN=SQLSTRINGCONNECT...or SQLCONNECT).

A general way of doing SQLEXEC is:

LOCAL lnReturnValue && somwhere in the variable declaration section of your code
....
lnReturnValue = SQLEXEC(....your parameterization, can vary a lot...)
If lnReturnValue<0
   AERROR(paError)
   SET STEP ON
Endif

This is just a simple way of handling errors during tests you do within the IDE. SET STEP ON starts the debugger in single step mode and also the paError array AERROR() generates will be visible in a treeview inside the Locals Window of the debugger. The only unfortunate thing is messages are quite long and the local windows won't word wrap array elements. So another thing you can also do is log AERROR information into an error log file, something you likely already have for any error, also all VFP specific errors.

And notice one thing about the error messages: No matter if MySQL or MSSQL they all begin stating a "connection-level error". That doesn't mean a connection problem or instability, it's just an error that happened via the connection, but doesn't mean the connection itself, it means the SQL. The message itself will tell more. In your case it'll likely point to the TTOC() call MySQL doesn't know.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[David Higgs]

Hello Olaf,

Thank you very much for your help, patience and understanding, it is much appreciated.

I tried your code and it work perfectly as expected. Looking back at my code, I was so near yet so far from achieving what I had set out to do; I was just getting in a muddle with my String concatenations.

In the past I’ve often tried things in phpMyAdmin and cut & pasted the code from phpMyAdmin into VFP to try to resolve code issues but have never tried doing it in the opposite direction! I can see that “_cliptext” would be very useful in this respect. In fact I’ve just tried “_cliptext=MyLog_Selection” and pasted it into “phpMyAdmin” SQL and appreciate how useful it is for de-bugging code.

I use “phpMyAdmin” and” MySQL – Front” on the MySQL side and make use of the “VFP Code References Utility” and also “GoFish Master” to help whilst programming.

Ok on broadening my horizon by taking a deeper look into MySQL Functions and Commands. As I pick up advice and new ideas, I do try to improve my existing code by implementing them. VFP is a fascinating Program and it never ceases to amaze me on what it can do, I’ve barely scraped the surface.

Error trapping is one area that I really do need to concentrate on and in particular I need to look into and understand the use of AERROR. (I’ve said that before!)

Olaf, great advice as always. I can’t guarantee I’ll understand it all but I do look back at the threads I started at Tek-Tips from time to time to jog my (poor) memory. I re-call when I first started using the Forum that I made constant use of BROWSE and had never tried using or had any understanding of GRIDS. Now I barely use BROWSE except when testing code and always find a use for GRIDS. Incidentally, I found your reply to this thread

https://www.tek-tips.com/viewthread.cfm?qid=1794184

very informative. We have discussed “Blank Grids” in previous threads and I have followed some of the principles you discuss in my applications.




Regards,

David.

Recreational user of VFP.

 

[David Higgs]

The main reason for using a MySQL Server was so to have my Databases accessible from any Computer attached to my Home Network. At that time I deemed the setting up of the MySQL Server to be beyond my capabilities so my Son did all of the work for me. I've gradually started to do more and more with MySQL although my emphasis had always been on VFP.

Having taken your advice, I did a quick search on "Selecting MySQL records for current Date" which revealed the MySQL CURDATE() Function. So, I now use the following code in my program.

Where DATE(`Time_on`) = CURDATE()

Regards,

David.

Recreational user of VFP.

 

[Olaf Doschke]

CURDATE() returns a string again, that needs implicit conversion to date. Use DATE(NOW()).

Converting a string to a date is more expensive than taking the date part of a datetime, therefore DATE(NOW()) faster than CURDATE(), but the main point is to always compare same types and don't depend on any implicit conversions.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[David Higgs]

Olaf,

All duly noted.

I searched for MySQl "Current Date" which came up with CURDATE(), although it met my requirements, I can see now that I need to study MySQL Commands in greater depth.

I will also take a look at my application to see if my use of DTOC() & CTOD() and any other "conversions" can be avoided.

Regards,

David.

Recreational user of VFP.

 

[Mike Lewis]

David,

I'm coming late to this discussion, so my apologies if the following is something you have already considered.

When I started using MySQL, I stored dates in the default format (which is in fact a character string), and used the UNIX_TIMESTAMP() function to convert them to datetimes for comparison and arithmetic purposes. But I found I was getting errors. I don't recall the details (this was about ten years ago), but I think it had something to do with the dates not having a time element.

I solved the problem by using the int datatype for my dates, representing the number of seconds since the Unix base date. I don't recall having any further problems in that area.

This might not be relevant to your immediate issue, but you might like to keep it in mind if you are storing dates in MySQL.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Unlike VFPs date and datetime literals delimited with curly brackets and an additional ^character, i.e. {^yyyy-mm-dd}, other databases like MSSQL and MySQL indeed take a date string, as you have to have some literal representation of dates in queries, too. Dates are also displayed that way in workbench query results. But that doesn't mean dates and datetimes are stored as strings. You can only do LEFT() on string values and DATE_ADD()) etc. functions only work with date or datetime columns, not with string columns and I don't have to tell the choice of the right type is the only way to make use of corresponding type-dependent function and functionalities. For example, dates stored in char fields wouldn't sort correctly unless the string format is putting the year, month, and day in that order, which they usually don't.

MySQL is very "forgiving" or let's say versatile interpreting date strings as the correct date, but like in VFP you depend very much on server settings and internationally date strings differ in the order of day and month. Another reason to have date and datetime columns.

From the original question post I still can't imagine something like LEFT(datetimefield,10) works, as the LEFT() function is like VFPs LEFT() function needs a string. MYSQLs DATE() function is something FoxPro also has, but it's not FoxPRos DATE(), it is the equivalent to FoxPro's TTOD().

Yes, CURDATE() is the current date, but it's in string format. If you compute with it as in CURDATE()+2 it returns a number instead, so that's another problem you may only stumble upon later, if you try to calculate with it. I dislike such functions alone because of their overloaded and incoherent nature. I'm not against overloading, that can make sense, but not in this case.

I had a similar experience like you Mike, on the subject of currency and decided it is easier to store amounts as ints in cents/pennies instead of coping with conversion problems. But indeed it was for a quick fix and I later also learned to use connection options to use the currency data type, as it still makes sense to put data into the data types invented for them to have the full feature set of functionalities thought of around them.

Implicit conversions are a very usual topic and sometimes can help very much, but in MSSQL I also only like straight forward or setting independent conversions and regarding dates, there is an ISO string format 'YYYYMMDD' you can use in MSSQL, that's not working in MYSQL, though.

You can't transport VFP dates directly in a query, you have to parameterize a query, and with VFP that's done using SQLEXECs parameterization, not MYSQL, VFP converts that to ODBC compliant language the MySQL ODBC driver can convert to MySQL's way of parameterized queries and that's the principle with which VFPs parameterization can be used with any backend, so you can also do this:

Where DATE(`Time_on`) = ?(DATE())

What still speaks for MySQL's CURDATE or DATE(Now()) is that you likely also store CURDATE or NOW() into fields as default value and so even if the server is in another time zone than clients you can rely on this working while there is a shift in a few hours when the VFP client is in another time zone than the server. In the other hand you might want the day from the client perspective, then use VFPs DATE(), but then you can't use server-side CURDATE() or NOW() as default values as they can potentially put data one day off, then you will also need insert values with VFPs DATE() overriding the default value of the field.

There are always more things to think about, once you go from local DBFs to a server. It's easy to overlook some of these things, as you begin with localhost as a developer and have local time = server time by definition. And then I didn't even touch the aspect of daylight saving time and such things.

Bye, Olaf.






Olaf Doschke Software Engineering

https://www.doschke.name

 

[David Higgs]

Hello Mike,

Thank you for your input, all duly noted. Although I have achieved what I set out to do in my original post, I will no doubt revisit this thread from time to time to refresh my aged memory!

Olaf has gone to great lengths to educate me on the use of "Strings" which has encouraged me to look more at MySQL than I have done in the past. I have also started to look at my Applications to see if I can improve the efficiency of my code as I glean more information on VFP / MySQl. I know I have only touched the surface of VFP / MySQL but I have gained a lot of satisfaction from seeing my applications progress.

And then I didn't even touch the aspect of daylight saving time and such things.

Fortunately my "Logbook Application" has Times in UTC and you provided me with a nice little routine that looks after UTC.




Regards,

David.

Recreational user of VFP.