[OT] Problem with MySQL Insert

[Paul Foraker]

Now that I think about this, is this a 'feature' of revExecuteSQL ?  A
string that looks like a time gets coerced by the command into a time and
adjusted for time zone without regard to the field type of the destination
database field. Can that be what's happening?

On Tue, Mar 24, 2015 at 9:48 PM, Paul Foraker <paul at whitefeather.com> 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?
>



-- 

White Feather Software
www.whitefeather.com
Cell: 408-391-1480