Hi Paul,
MySQL definitely doesn't guess that your string consists of two times
and definitely doesn't guess that it should be converted depending on
time zone.
MySQL doesn't interprete a string as a date if the field for that string
isn't defined as a date. Your field is defined as VARCHAR and thus the
string will be stored without modification. Something else is going on here.
First of all, VARCHAR(10) is wrong. This should be VARCHAR(14) if I
counted correctly.
Perhaps you need to add the line
put tSQL
after the constuction of the MySQL command in your syntax and see what
appears in the message box. Can you confirm that the MySQL syntax you
see in the message box is the same as what you entered in Sequel Pro?
What are the collations of the database and the time field?
--
Best regards,
Mark Schonewille
Economy-x-Talk Consulting and Software Engineering
Homepage: http://economy-x-talk.com
Twitter: http://twitter.com/xtalkprogrammer
KvK: 50277553
Installer Maker for LiveCode:
http://qery.us/468
Buy my new book "Programming LiveCode for the Real Beginner"
http://qery.us/3fi
LiveCode on Facebook:
https://www.facebook.com/groups/runrev/
On 3/25/2015 05:48, Paul Foraker wrote:
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
_______________________________________________
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
