In a table in a MySQL database, I need a field displaying a time period
corresponding to the half hour period during which a record was posted. The
period is in the form "HH:MM-HH:MM AM|PM". I have set the field type to
VARCHAR(10) and LiveCode is passing the string to the query handler. MySQL
is interpreting the incoming text as a date and converting it to my local
offset from server time.
In the Variable Watcher, before the revExecuteSQL, the pPeriod has the
correct time period; e.g., 11:00-11:30 PM. In the database, Sequel Pro
shows the period field with a value of 6:00-6:30 PM when Jacque in
Minnesota posts the record at 11:00 PM Minnesota time, and 4:00-4:30 PM
when I do it in California.
I'm surprised that MySQL is doing this interpretation of a character string
to a time value. How do I prevent that from happening?
In Sequel Pro, I entered this command:
INSERT INTO report
(`shortdate`,`period`,`dsp_id`, `dsp_name`, `client_id`,`client_name`,
`behavior_label`, `behavior_desc`, `observed`)
VALUES ("3/15/2014","12:30-1:00
PM",22,"fred",22,"lucy","jumping","high",1)
and the period field correctly displayed "12:30-1:00 PM".
Here's the LiveCode handler:
on sendReport pShortDate,pPeriod,pDSPid,pDSPname,pClientID,pClientName,pRows
getConnected ## establishes the database connection
set the itemDel to tab
put "INSERT INTO report (`shortdate`,`period`,`dsp_id`, `dsp_name`,
`client_id`,`client_name`, `behavior_label`, `behavior_desc`, `observed`)"
&& \
"VALUES " into tSQL
repeat for each line thisRow in pRows
put "('" & pShortDate & "', '" & pPeriod & "', '" & pDSPid & "', '" &
pDSPname &"', '" & pClientID &"', '" & pClientName &"', '" & \
item 1 of thisRow & "', '" & item 2 of thisRow & "', '" & item 3
of thisRow & "')," after tSQL
end repeat
put ";" into last char of tSQL
revExecuteSQL gBTdbID,tSQL
...
Any ideas how to do this?
_______________________________________________
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
