"Convert" Command

[NigelS]

I've been getting a lot of help from various places and people. But I have now come to the conclusion the the command "convert' is buggy.
Here is a brief description of whats happening.

I have an array with various pits and pieces of data in them.

Code: Select all

global PLNListingDetailArray
   
   put "ListDateField" into PLNListingDetailArray[1][1] -- Field Name
   put true into PLNListingDetailArray[1][2] -- Validation Required
   put "dateListed" into PLNListingDetailArray[1][3] -- Database Field Name
   put "INTEGER" into PLNListingDetailArray[1][4] -- Data Type
   put "DateEntryField" into PLNListingDetailArray[1][5] -- Control Type
   
   put "ListingExpiryDateField" into PLNListingDetailArray[2][1]
   put true into PLNListingDetailArray[2][2]
   put "expiryDate" into PLNListingDetailArray[2][3] 
   put "INTEGER" into PLNListingDetailArray[2][4] 
   put "DateEntryField" into PLNListingDetailArray[2][5] 

   more puts......

I have three objects on a card that capture a date.

For each of those input fields that requires a date a button is provided in which a calendar is presented and the user can select the date. Notice the 'callback' as I insure that the "short" date in used.

Code: Select all

on mouseUp
   DPpopup 
   if the result is not empty then answer error the result 
end mouseUp

on DPdateClick aDates,pMode,pSourceObj
   put aDates["Short"] into field "ListingExpiryDateField"
end DPdateClick

Once the user has completed entering the data the "Save" button is used to save the date. But before the data can be saved there's a 'command' that builds a SQL INSERT string containing the required details.

In building this string I do a call to a 'command' to build the structure that is required syntax for the SQL INSERT.

Code: Select all

command buildFieldNameAndValuesVariable
   
   global PLNListingDetailArray, gPLNColumnNames, gPLNFieldValues
   local tArrayElement
   local tValue, tEnteredDate
   local tItemName
   
   repeat for each element tArrayElement in PLNListingDetailArray 
      put gPLNColumnNames & tArrayElement[3] & "," into gPLNColumnNames
      put empty into tValue
      switch tArrayElement[5]
         case "OptionMenu" -- OptionMenu datatype
            
            if tArrayElement[1] = "PropertyTypeOptionMenu" then
               put the label of button "PropertyTypeOptionMenu" into tItemName
               put char 1 of tItemName into tValue 
               break
            end if
            
            if tArrayElement[1] = "PropertySubtypeOptionMenu" then
               put the label of button "PropertySubtypeOptionMenu" into tItemName
               put char 1 of tItemName into tValue
               break
            end if
            
            if tArrayElement[1] = "AreaOptionMenu" then
               put the label of button "AreaOptionMenu" into tItemName 
               put char 1 of tItemName into tValue
               break
            end if
            
         case "CheckBox" -- CheckBox datatype
            
            if GetAction() = "Add" then 
               put 0 into tValue
            end if 
            
            if tArrayElement[1] = "NoOpenHourCheck" then
               if the hilite of button "NoOpenHourCheck" is true then
                  put 1 into tValue
               end if 
               break
            end if
            
            if tArrayElement[1] = "IsRentalCheck" then
               if the hilite of button "IsRentalCheck" is true then 
                  put 1 into tValue
               end if
               break
            end if
            
            if tArrayElement[1] = "DontContactOwnerCheck" then
               if the hilite of button "DontContactOwnerCheck" is true then 
                  put 1 into tValue
               end if
               break
            end if
            
         case "DateEntryField"
            put 0 into tEnteredDate
            switch tArrayElement[1]
               case "ListDateField"
                  put myConvertDateToSeconds("ListDateField") into tEnteredDate
                  put tEnteredDate into tValue
                  break
               case "ListingExpiryDateField"
                  put myConvertDateToSeconds("ListingExpiryDateField") into tEnteredDate
                  put tEnteredDate into tValue
                  break
               case "OpenHourDateField"
                  put myConvertDateToSeconds("OpenHourDateField") into tEnteredDate
                  put tEnteredDate into tValue
                  break
            end switch
      end switch
      
      put gPLNFieldValues & tValue & "," into gPLNFieldValues
      
   end repeat
   
   delete last character of gPLNColumnNames
   
end buildFieldNameAndValuesVariable

the portion of interest is the "Case "DateEntryFied" the date entered has to be converted to seconds. So while looping through the array I can see which of the fields has a date entered.

When a date is identified then this portion of the code is executed

Code: Select all

case "DateEntryField"
            put 0 into tEnteredDate
            switch tArrayElement[1]
               case "ListDateField"
                  put myConvertDateToSeconds("ListDateField") into tEnteredDate
                  put tEnteredDate into tValue
                  break
               case "ListingExpiryDateField"
                  put myConvertDateToSeconds("ListingExpiryDateField") into tEnteredDate
                  put tEnteredDate into tValue
                  break
               case "OpenHourDateField"
                  put myConvertDateToSeconds("OpenHourDateField") into tEnteredDate
                  put tEnteredDate into tValue
                  break
            end switch
      end switch
      

which has a call to this function

Code: Select all

function myConvertDateToSeconds pFieldName
   get field pFieldName
   convert it to seconds
end myConvertDateToSeconds

On the first iteration of the loop, the code when it sees that a date needs to be converted into seconds the code works correctly and I get the correct conversion into seconds. On the second iteration of the loop the conversion returns back the date in the same format as it was sent across.

However I have just noticed something while typing this out. If I use the CURRENT date then 'convert' works but if I use a another date, say a week ahead then it fails.

[Klaus]

Hi Nigel,

I never had any problems with "convert", but some genereal hints:

1. your function is missing the "RETURN XX" part!
2. relying on IT in situations like this is asking for trouble!
3. adding "from... to" to the function may also help.

Do this:

Code: Select all

function myConvertDateToSeconds pFieldName
   put field pFieldName into tDate
   convert tDate from short date to seconds
   return tDate
end myConvertDateToSeconds

and see if this helps...

Best

Klaus

[NigelS]

Hi Klaus

Thank you for your input. I did notice I had left it out after I had posted this. I did add it in but it made no difference. In the end I have just used the date in its default state and let SQLITE deal with the conversion as it contains a method that does the same sort of thing. I suppose in about a years time I'll come back to this and well properly see my mistake as I'm fairly new to LiveCode.

[jacque]

Did you try changing the location of the "break" statements in your script? I posted that in another thread where you mentioned the problem. The "break" must be the last thing before each new "case" statement, it shouldn't be embedded inside an "if" clause. The script may be erroring and aborting before it has a chance to do any conversions.