Using database linked controls with MySQL

[Jan Schenkel]

--- Sarah Reichelt <sarahr at genesearch.com.au> wrote:
> On 28 Jul 2004, at 8:23 am, Bill wrote:
> 
> > But this time I'm hoping someone knows how to
> easily format dates in 
> > the
> > manner that MySQL expects them. Dates have to be
> yyyy-mm-dd.
> >
> > The format runrev gives me is mm/dd/yy. I could
> write a complex thing 
> > that
> > would convert it every time but maybe there is a
> built in format or 
> > someone
> > has a nice conversion function I could use.
> >
> It's only the sort of thing you would have written
> for yourself, but 
> here are my functions for SQL date conversions:
> 
> -- converts a system short date into an SQL format
> date
> -- uses current date if none supplied
> --
> function SQLDate pDate
>    if pDate is empty then put the short system date
> into pDate
>    convert pDate from short system date to dateItems
>    return item 1 of pDate & "-" & item 2 of pDate &
> "-" & item 3 of pDate
> end SQLDate
> 
> 
> -- reverse function to convert SQL date to short
> system date
> --
> function SystemDate pDate
>    if pDate is empty then return the short system
> date
>    replace "-" with comma in pDate
>    put ",0,0,0,0" after pDate
>    convert pDate from dateItems to short system date
>    return pDate
> end SystemDate
> 
> 
> Cheers,
> Sarah
> 

Allow me to add the suggestion that you could use the
Database Query linked fields by means of a little
trick :

- first make a field "Linked date" and link that to
the database query and field

- then make another field "Input date" which will hold
the input, and set its script to :
##
on closeField
  -- copy the date in the right format to the linked
field
  put SQLDate(the text of me) into field "Linked date"
  -- then update the database
  send "closeField" to field "Linked data"
end closeField
##

- also add a handler to your card script that you can
call whenever you refresh the database query or
navigate the result set :
##
on RefreshDateField
  put SystemDate(field "Linked date") into field
"Input date"
end RefreshDateField
##

Once you're sure everything works, you can hide the
field "Linked date" and nobody will ever know you used
this trick ;-)

Hope this helped,

Jan Schenkel.

=====
"As we grow older, we grow both wiser and more foolish at the same time."  (La Rochefoucauld)


		
__________________________________
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail