Using a text field with date formated content in a WHERE clause of a SELECT statement

[Lonnie]

Hi everyone,

This is sort of related to the topic submitted by lemodizon's
"How to search between two dates in LiveCode" on Nov. 12,2020.

I'm trying to create the SQLite SELECT statement below, to include
a text field with date formatted content in the WHERE clause.

Using the suggestions by Xero, even though SQLite evidently
doesn't have have date type fields, I created the functions below,
using other's contributions, to increment the gamedate field in
my app and then convert the date format from MM/DD/YYYY to YYYY-MM-DD.

I used these functions to recreate my softball Schedule table with the new
date format and then tried to create my tRecords list in the answer
command below from the new table. No luck using either date format.

It does work if you remove the line referencing the date as noted below.

I love this forum and have gotten so much great help from it.
I've spent hours trying to resolve this one ... any suggestions?

Thanks again,

Lonnie

---------- My Code ------------
put "SELECT * FROM Schedule" && \
"WHERE orgs_id='" & gOrgs_id & "'" && \
"AND eventno='" & gEventNo & "'" && \
"AND gametype='" & gITrnGameType & "'" && \
"AND gamedate='" & gtGameDayDate & "'" && \ -- REMOVE THIS LINE AND IT WORKS
"ORDER BY orgs_id, events_id, setno, gametype, gameno" & ";" into tSQL
---------------------------------------------------------
put revDataFromQuery(comma,return,gConID,tSQL) into tRecords
answer "Display List of Schedule tRecords = " & cr & tRecords with "OK"

--- Excuse the several "answer" commands I throw in for testing
function IncrementDate pDate
-- answer "In IncrementDate with " & pDate with "Cancel" or "OK"
-- if it is "Cancel" then
-- exit to top
-- end if

# year, month number, day of the month, hour in 24-hour time,
# minute, second, numeric day of the week
# 2019,9,19,0,0,0,5 ... Sept 19, 2019 after converted to dateitems

put pDate into tDate
convert tDate to dateItems
-- answer "tDate converted to dateItems format = " & tDate with "Cancel" or "OK"
-- if it is "Cancel" then
-- exit to top
-- end if

# Increment Game Date 1 Day
add 1 to the item 3 of tDate

# Convert tDate back to date format
convert tDate to date
-- answer "tDate converted back to date format = " & tDate with "Cancel" or "OK"
-- if it is "Cancel" then
-- exit to top
-- end if

# Preceed single digit month and day values with 0's
set the itemDelimiter to "/"
# Convert single value month date to 2 digit
if the number of chars of item 1 of tdate = 1 then
put 0 before item 1 of tdate
end if

# Convert single value day date to 2 digit
if the number of chars of item 2 of tdate = 1 then
put 0 before item 2 of tdate
end if

put item 1 of tdate into tMonth
put item 2 of tdate into tDay

put the internet date into tInternetDate
convert tInternetDate from internet date to dateItems
set itemdelimiter to ","
put item 1 of tInternetDate into tYear
put tMonth & "/" & tDay & "/" & tYear into tDate

--answer "New pDate = " & pDate
return tDate
end IncrementDate


function MySQLDate pDate
# CONVERT A DATE IN THE FORMAT MM/DD/YYYY TO YYYY-MM-DD
put pDate into tDate
-- answer "Date passed to MySQLDate = " & tDate with "Cancel" or "OK"
-- if it is "Cancel" then
-- exit to top
-- end if

set the itemDelimiter to "/"
put item 1 of tdate into tMonth
put item 2 of tdate into tDay
put item 3 of tdate into tYear
put tYear & "-" & tMonth & "-" & tDay into tDate
return tDate
end MySQLDate


function IncrementTime pTime -- NOT RELATED BUT MIGHT BE HANDY SOMETIME
global giSetsTimeAllowed
put pTime into tTime
convert tTime to short time
convert tTime to dateItems
put item 5 of tTime + giSetsTimeAllowed into item 5 of tTime
convert tTime to short time
return tTime
end IncrementTime

[bangkok]

Very confusing.

Thank you to give us the content of... tSQL

That's the only way to see the issue (in your code, we see nothing... for instance, what do you put into gtGameDayDate ?)

We need to see the SQL query, created by your code.

[dunbarx]

Lonnie.

Always, please, place your code samples within the code tags "</>". So much easier to read.

Craig

[AxWald]

Hi,

why so convoluted? Try this:

Code: Select all

function makeSQLDate theDate, theTime, dayToAdd, theFormat
   /* Converts a dateTime to SQL format, adds days if needed.
   # "theDate": a date formatted according to your system settings
   # "theTime": a time formatted according to your system settings (default: "0:00")
   # "dayToAdd": number of days to add (subtracts if negative, default: 0)
   # "theFormat: "SQLD" = SQL date; "SQLT" = SQL dateTime (default: SQLT)
   Returns the desired value, or an error beginning with "error"   */
   
   if dayToAdd is empty then put 0 into dayToAdd
   if theFormat is empty then put "SQLT" into theFormat
   if theTime is empty then put "0:00" into theTime
   put " " & theTime after theDate
   
   convert theDate from system date and system time to dateItems
   if the result is not empty then
      return "Error: Not a date: '" & theDate & "'; Reason: '" & the result & "'"
   end if
   
   add dayToAdd to item 3 of theDate
   convert theDate to dateItems

   switch theFormat
      case "SQLD"
         put item 1 of theDate & \
               "-" & format("%02s", item 2 of theDate) & \
               "-" & format("%02s", item 3 of theDate) into theDate
         break
      case "SQLT"
         put item 1 of theDate & \
               "-" & format("%02s", item 2 of theDate) & \
               "-" & format("%02s", item 3 of theDate) & \
               " " & format("%02s", item 4 of theDate) & \
               ":" & format("%02s", item 5 of theDate) into theDate
         break
   end switch
   return theDate
end makeSQLDate

Call it then:

Code: Select all

   put makeSQLDate(the system date,,,"SQLD") into mySQLDate
   --  or:
   put makeSQLDate(the system date, the system time) into mySQLDateTime
   --  or:
   put makeSQLDate(the system date,,-1,"SQLD") into mySQLDate_YesterDay

Have fun!

[Lonnie]

Hi All,

Thanks for your help.
bangkok ... sorry for the confusion, I'll try to reword it a simpler way.

Craig ... thanks ... I found the code button ... I'll have to check out the other buttons up there!!

AxWald ... I hadn't run across the format() feature, it looks like a powerful tool.

Here's, hopefully, a more to the point version of the above question.

My Schedules table might someday have thousands of records. There could be a dozen organizations each hosting 100 events a year. Each event has multiple sets of tournament schedules, one for each grouping made up of gender, age and skill level. And each schedule in a SET runs for 3 days.

So what I'm trying to do is isolate the records for one particular date in one these listings of games. When I include the code, line 5 of the SELECT statement above, attempting to specify a particular game date, now in the format mm/dd/yyyy in my table, tRecords is blank. If I remove that line I get a list of records which includes all 3 days of records for the "Setno" specified.

If I change the format of the gamedate field to yyyy-mm-dd, I still get nothing in tRecords.

I was able to cut the SELECT statement down to the following and have a populated list of records. It just doesn't contain what I need.

Code: Select all

put 3 into giGameType   
put "11/22/2020" into gtGameDayDate 
put "SELECT * FROM schedule" && \
      "WHERE gametype='" & giGameType & "'" && \
        "AND gamedate='" & gtGameDayDate & "'" && \
      "ORDER BY orgs_id, eventno, setno, gametype, \
        gamedate, gameno" into tSQLput revDataFromQuery(comma,return,gConID,tSQL) into tRecords      answer "tRecords = " & cr & tRecords

If I reverse the order of the WHERE conditions it still works, tRecord is populated with a list of records.

But if I add one more WHERE condition OR change the gametype condition to a condition for any other field it doesn't work, tRecords is blank.

The database is a SQLite and the gamedate field in the schedules table is a TEXT field since SQLite, to my knowledge, doesn't have date type fields.

I hope this makes more sense.

Thanks again for all your help,

Lonnie

[bangkok]

I hope this makes more sense.

No it doesn't.


Anyway, the error now is obvious

Code: Select all

put "11/22/2020" into gtGameDayDate

You use gtGameDayDate in a SQL query.

It can't work.

SQL use YYYY-MM-DD format.

Code: Select all

put "11/22/2020" into gtGameDayDate

should be

Code: Select all

put "2020-11-22" into gtGameDayDate

But you're still not out of the woods yet.

Because as I told you : you can not spot a SQL error/mistake until you SEE the SQL query...

Hence me asking you to provide us the content of the SQL query.

Just put

Code: Select all

answer tSQLput 

SQL queries can be broken in many ways : the content of a variable, syntax, a single quote missing etc.

By looking at the SQL query in its final form, then you can fix the code that creates the SQL query.

[Lonnie]

Ok ... I have a feeling your going to tell me how to copy and paste the results of an -- answer tSQLput -- command into the forum but until you do the best I could come up with is screen shots. If you look on the right side you can see most of the code that generated the SQL statement. I had to recreate the table, now called TrnTEMP, with the SQL acceptable date format, YYYY-MM-DD. Then I had to build the web page to display everything. https://t-mgr.com/scrnshots

Hope this gives you what you need.

Lonnie

[AxWald]

Hi,

omitting error handling isn't the best of ideas:

Code: Select all

   put revDataFromQuery(x,y,z,tSQL) into whatEver
   if whatEver begins with "revdberr" then
      set the ClipBoardData["text"] to "Error: " & \
            whatEver & CR & "tSQL:" & CR & tSQL
      answer error whatEver & CR & \
            "Error message copied & ready to paste." titled "Eeeek!"
      breakpoint
   end if	

Further, when working with databases it's mandatory to have some kind of database manager software. Using such you can test your SQL queries, or, better, create them there & only then insert them into LC code.

Last, using breakpoints (soft = red dot; hard = see above) and the variable tab in the script editor shows you all your variables at any time :)

Have fun!

[bangkok]

Hope this gives you what you need.

Actually... no.

Why do you have a Datagrid which is populated behind ? How do you get such a listing ?
I see that you have 2 columns : game date and game time.

Futhermore what means "doesn't work" ?

Do you receive an error message ? Or do you receive an empty result ?

Last question what's the datatype of your column "gamedate" ?

What does it store ?

A simple date ?

Or a datetime like "2020-11-22 08:02:03" ?

[Lonnie]

Thanks again gang,

AxWald: I still have the error handler code, I moved it down and commented it out so more of the code I used and tried is visible. I wanted to show how the var tRecords was populated ... or not. At this point I'm not getting an error but tRecords is blank when I say it doesn't work.

I'm using DB Browser to view and edit the database structure and browse the content. I haven't tried inserting SQL code to test it. I'll check that out. If there is a better database manager program let me know.

What I find interesting, I've used Visual Foxpro and before that dBaseII for nearly 40 years to run my flower business, Proteas of Hawaii. And to view and edit data in a table requires 3 words:

SELECT Schedule
BROWSE

WITH SQL AND LC you write a SELECT statement, convert it to an array, which is quite a process, and either put this data into a datagrid or you save each element in the array to a memory variable, change the value of the elements needing editing, and finally create and execute a SQL UPDATE or INSERT statement and you got it! Not sure this is progress. I'm sure it's my newbee status with LC ... I'm hopeful to learn a better approach.
Thanks again for your help!

bangkok:
Going through your questions:

Why do you have a Datagrid which is populated behind ? How do you get such a listing ?
I see that you have 2 columns : game date and game time.

I have 9 tables in this project, and each has a separate card and on each card there is a datagrid to display that table's content. There's a login card to start and your access rights based on the login information determines the next card that is opened.

There is a similar set of buttons at the bottom of each card that either moves you to a different card or performs some function or process.

The card displayed in the screen shots is a test page for the schedule generation process. The button in the upper right labeled "Test List Display" is a selectin of a drop down menu. The other menu options are Enter Day 1 Scores, another for Day 2 and 3. It's purpose is to insert test scores into the schedule so I can test the "Standings", "Update Names" and the "Create HTML" features. When I couldn't separate Day 2 and Day 3 entries in the schedule I made the "Test List Display" menu option to see what the SELECT statement was selecting ... and it is nothing if I incorporate the gamedate in either format! So here we are!

Futhermore what means "doesn't work" ?
Do you receive an error message ? Or do you receive an empty result ?

As I mentioned above to AxWald, "Doesn't Work" means there is no content generated by the SELECT statement and therefore the tRecord's list is blank. There is no ERROR generated. Sorry for the confusion. The last image of the 5 screen shots on https://t-mgr.com/scrnshots was supposed to indicate this. Again, sorry for the lack of clarity.

Last question what's the datatype of your column "gamedate" ?
What does it store ?
A simple date ?
Or a datetime like "2020-11-22 08:02:03" ?

As mentioned in the heading of this post, the field gamedate is a text field, containing date looking content, that to me represents a date. The gametime field, also is a text field indicating the time a game is to start. I used text type fields for these since SQLite doesn't offer date or time type fields.

I have a boolean variable that if changed switches the data source from SQLite on my local machine to a MySQL database on a LiveCode server. So eventually I could convert the date and time fields to a more precise data type and the more obvious benefit is anyone could access and modify data by way of a web connection. But while building this I'm using a SQLite database on my laptop so I'd still like to figure out how to deal with date type data locally.

The content of my gamedate field is a simple date which was in the format of 11/22/2020. At your suggestion, I changed it to a simple date with the SQL required format of 2020-11-20.

Also, of possible interest, when in the MM/DD/YYYY I was able to populate tRecords using both the gamedate and gametype fields in the WHERE clause. If I added any other fields to the WHERE, tRecords is blank. When I changed over to the YYYY-MM-DD format, there was no error but tRecords was blank, even if I only referenced the gamedate field in the WHERE clause of the SELECT statement.

I did come up with a work around but I still need to figure out how to deal with dates in a SQLite database.

You might have noticed the CRUD button. This is only on my test card and stores my collection of "How To's" once I figure out some process. So I need to fill in dealing with dates!

I actually thought there might be a market for this but I find the one prospect I had has come up with another solution. So if anyone is interested in the stack as a learning tool you are welcome to it. You just have to share what you add and learn! I'm sure you probably won't be building tournament schedules but the concepts could probably be applied to a number of other business models.

The link to the screen shots, https://t-mgr.com/scrnshots will have my contact information at the bottom of the home page so I can get your information to send it to you.

Thanks again,

Lonnie

[AxWald]

Hi,

At this point I'm not getting an error but tRecords is blank when I say it doesn't work.

If "revDataFromQuery" returns empty then your query resulted in an empty data set.

If I reverse the order of the WHERE conditions it still works ...

There is a mistake somewhere in the SQL string - just reversing AND-arguments mustn't make any difference!

I'm using DB Browser to view and edit the database structure and browse the content. I haven't tried inserting SQL code to test it. I'll check that out. If there is a better database manager program let me know.

I prefer "SQLite Manager", but this may not be an option (it requires an old firefox or similar to run). Anyways, use the "execute SQL" function there with your tSQL! This is the exact equivalent to LC's "revDataFromQuery/ revdb_queryList" and "revExecuteSQL/ revdb_execute" (actually all they do is to send your tSQL to the DB ...).
Perfectly suited to test SQL strings and isolate where the error hides - in LC or in SQL.

What I find interesting, I've used Visual Foxpro and before that dBaseII for nearly 40 years to run my flower business, Proteas of Hawaii. And to view and edit data in a table requires 3 words:

SELECT Schedule
BROWSE

Never used these, but to my knowledge FoxPro should be similar to MS Access which I used for years before switching to LC - but Access is a specialized database frontend, while LC is a full application development environment that can handle databases, but doesn't put much emphasis on it.
And even in Access I had to go lengths to properly display my data via multiple queries in forms and reports. And then still often had to code the writing back, because "this isn't an updatable query" (or changing the query so that Access thought it's "updatable" ...). Plus having value evaluations in each single field, all over the place. After making the switch it took me some time to re-learn, but meanwhile I'm much faster in LC. And the resulting code is multitudes faster.

WITH SQL AND LC you write a SELECT statement, convert it to an array, which is quite a process, and either put this data into a datagrid or you save each element in the array to a memory variable, change the value of the elements needing editing, and finally create and execute a SQL UPDATE or INSERT statement and you got it! Not sure this is progress.

I usually fetch my data as tab-CR CSVs and keep/ use them as such. Arrays in LC are simple key-value pairs, not really suitable when it comes to tabular data. Sure, we can put arrays within an array as workaround, but this blows up any sort order, and requires lots of coding. They are very useful for a lot of things - but when working with DB output they often aren't first choice.
I don't use DataGrids, too. These are nice things for beginners to display small snippets of data, but used with what I need it for they're ways too slow, cumbersome & inflexible. Thus I use classic text fields with "tabStops" set. Not the "cRevTable" stuff, this wasn't ever really finished.
Lacking both a native data structure and a native graphic representation for tabular data may be among the most crippling handicaps of LC - by my view.

But about the handling of data:
I usually grab my data set, and store it in a custom Property of the card/ stack. Then I make a copy of it, and display an ordered/ formatted/ filtered version of it in my field(s).
When the user changes the data, it is applied to the copy & displayed in the field. When done ("Accept!"), I compare the copy to the original, build a VALUES structure of the differences, and send it back to the DB for a UPDATE/ REPLACE. No big deal.

Compared to Access this is multitudes faster, because I have full control of what goes on with the DB - I talk to it directly, in SQL, without having a front end trying to do it: I use TEMP TABLES when it makes sense, I use a lot of VIEWS and STORED PROCEDURES - I do my best to have as much of the "heavy work" done on the "big iron".
I don't have the assistants or the prefab stuff of Access - but in this trade I get a whole other dimension of freedom in designing my UI - using a basic card with common controls as background, and adding the actual layout of a form in code, for instance.
Then, manipulating even huge pieces of tab-CR CSV is a breeze in LC - its excellent capabilities in chunk handling come to shine here. LC allows me to do things that had my users snoring when done in Access ;-))


Some more:

As mentioned in the heading of this post, the field gamedate is a text field, containing date looking content, that to me represents a date. The gametime field, also is a text field indicating the time a game is to start. I used text type fields for these since SQLite doesn't offer date or time type fields.

Don't confuse the internal workings & the SQL implementation of SQLite! This works perfectly:

Code: Select all

CREATE TABLE 't_tablestats' ('TName' CHAR PRIMARY KEY  NOT NULL  UNIQUE , 'LUPdate' DATETIME)

Using the proper data types will help you to detect mistakes in time, for instance spaces attached to a number, or misspelled dates. Further you'll have proper ORDER BY results.

I have a boolean variable that if changed switches the data source from SQLite on my local machine to a MySQL database on a LiveCode server. So eventually I could convert the date and time fields to a more precise data type and the more obvious benefit is anyone could access and modify data by way of a web connection.

There are some differences in the SQL dialects of SQLite & mySQL/ MariaDB, but there are no differences in the data types (that come to my mind easily ...). I regard SQLite SQL as kind of a subset of mySQL's dialect - what works in SQLite often works as well in mySQL, but not the other way around.

Well, hope some pieces of help are hidden in this wall of text. Have fun!

[Lonnie]

Hi AxWald,

This is amazing ... what a lesson. Thank you so much for all the information and taking the time to put it all out. I'm going to try all of this out.

I usually grab my data set, and store it in a custom Property of the card/ stack. Then I make a copy of it, and display an ordered/ formatted/ filtered version of it in my field(s).
When the user changes the data, it is applied to the copy & displayed in the field. When done ("Accept!"), I compare the copy to the original, build a VALUES structure of the differences, and send it back to the DB for a UPDATE/ REPLACE. No big deal.

I came close to this but not quite ... I'd love to see a sample of this. Is there a way to send stack and databases back and forth through the forum?
I have a card the game schedule in a datagrid listing each game as shown in the screen shots. To post game scores you can modify the datagrid, which would take some training for the user, (insert score, tab out, click save row) or you can hilite a row and click on a "Post Scores" button that loads a new card with the hilited data inserted into fields like we used with Access and FoxPro. User puts in the scores for both teams and clicks on a "Save Scores" button and you return to the card with the data grid and scores are updated. A bit clumsy but it works.

You mention using CSV's rather than arrays ... that's high on my list to figure out.

Thanks again,

Lonnie