[OT] Problem with MySQL Insert

[Mark Schonewille]

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 at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>