(Resolved) Confused (as usual)

[NigelS]

Code: Select all

command saveMessageBookDetails
   
   local tPos
   local tSermonCode, tSermonYear, tSermonTitle, tSermonText
   
   put getDatabaseID() into tDatabaseID
   
   if tDatabaseID <> 0 then
      
      get the selectedText of field "Result" 
      put it into tMessageTitle
      
      -- extract Sermon code
      put offSet(" ",tMessageTitle) into tPos
      get char 1 to tPos of tMessageTitle
      put it into tSermonCode
      
      -- extract Sermon title
      get char tPos+1 to len(tMessageTitle) of tMessageTitle
      put it into tSermonTitle
      
      -- extract message year
      put char 1 to 2 of tMessageTitle into tYear
      put "19"&tYear into tSermonYear
      
      -- extract the suffix 
      put offSet(".rtf",tSermonTitle) into tPos
      get char 1 to tPos-1 of tSermonTitle
      put it into tSermonTitle
      
      put the field "TheMessage" into tSermonText
      
      put "INSERT INTO messageBookDetails " \ 
            & " ( SermonCode, YearOfSermon, SermonTitle, SermonText ) " \
            & " values ( tSermonCode, tSermonYear, tSermonTitle, tSermonText)" into tSQL
      
      revExecuteSQL tDatabaseID, tSQL
      if the result is not 1 then
         answer warning the result
         exit saveMessageBookDetails 
      end if
      
      
   else
      answer information "Connect to Database First"
   end if
   
end saveMessageBookDetails

When this code snippet is run I get the following error.
I've been struggerling for awhile now trying to understand the error. Any help would be appriciated.

[NigelS]

I've tried this as well.

[sturgis]

As you apparently figured out based on your second method, " values ( tSermonCode, tSermonYear, tSermonTitle, tSermonText)" is a string, the variables are not evaluated. Your second method that looks like
" values (" & tSermonCode & "," & tSermonYear & "," & tSermonTitle & "," & "," & tSermonText & ")"
should be closer to what you need. However, for char/string based columns in your database you must quote the values so the preceeding should include single tick quotes around the string values. It might be easier to do this either with merge, or with variable substitution.
With merge...
put merge("INSERT INTO messageBookDetails ( SermonCode, YearOfSermon, SermonTitle, SermonText ) values ( '[[tSermonCode]]','[[tSermonYear]]', '[[tSermonTitle]]', '[[tSermonText]]')") into tSQL -- notice the single tick quotes around the string values
I wasn't sure if you had year as a string or whatever so put the ticks there too. This way merge will.. er.. Merge the current variable values into the string at the placeholders [[variablename]]
The substitution method is probably LOTS easier.

put "INSERT INTO messageBookDetails (SermonCode,YearOfSermon,SerminTitle,SermonText) values (:1,:2,:3,:4)"
revExecuteSQL tDatabaseID, tSQL,"tSermonCode","tSermonYear","tSermonTitle","tSermonText" -- the :1,:2,:3,:4 are replaced with the variables. You can do this with a numerically keyed array too where arraykey[1] replaces :1 etc.

Code: Select all

command saveMessageBookDetails
   
   local tPos
   local tSermonCode, tSermonYear, tSermonTitle, tSermonText
   
   put getDatabaseID() into tDatabaseID
   
   if tDatabaseID <> 0 then
      
      get the selectedText of field "Result" 
      put it into tMessageTitle
      
      -- extract Sermon code
      put offSet(" ",tMessageTitle) into tPos
      get char 1 to tPos of tMessageTitle
      put it into tSermonCode
      
      -- extract Sermon title
      get char tPos+1 to len(tMessageTitle) of tMessageTitle
      put it into tSermonTitle
      
      -- extract message year
      put char 1 to 2 of tMessageTitle into tYear
      put "19"&tYear into tSermonYear
      
      -- extract the suffix 
      put offSet(".rtf",tSermonTitle) into tPos
      get char 1 to tPos-1 of tSermonTitle
      put it into tSermonTitle
      
      put the field "TheMessage" into tSermonText
      
      put "INSERT INTO messageBookDetails " \ 
            & " ( SermonCode, YearOfSermon, SermonTitle, SermonText ) " \
            & " values ( tSermonCode, tSermonYear, tSermonTitle, tSermonText)" into tSQL
      
      revExecuteSQL tDatabaseID, tSQL
      if the result is not 1 then
         answer warning the result
         exit saveMessageBookDetails 
      end if
      
      
   else
      answer information "Connect to Database First"
   end if
   
end saveMessageBookDetails

When this code snippet is run I get the following error.
I've been struggerling for awhile now trying to understand the error. Any help would be appriciated.

[NigelS]

I tried many options as shown below.

Code: Select all

put "INSERT INTO messageBookDetails " \ 
            & " ( SermonCode, YearOfSermon, SermonTitle, SermonText ) " \
            & " values ( '"&tSermonCode&"', '"&tSermonYear&"', '"&tSermonTitle&"', '"&tSermonText&"')" into tSQL

Code: Select all

put "INSERT INTO messageBookDetails " \ 
            & " ( SermonCode, YearOfSermon, SermonTitle, SermonText ) " \
            & " values ( '&tSermonCode&', '&tSermonYear&', '&tSermonTitle&', '&tSermonText&')" into tSQL

Code: Select all

put merge("INSERT INTO messageBookDetails " \
      & "( SermonCode, YearOfSermon, SermonTitle, SermonText ) "\
      & " values ( '[[tSermonCode]]','[[tSermonYear]]', '[[tSermonTitle]]', '[[tSermonText]]')") into tSQL
 

None of which worked.

However, this did

Code: Select all

      put "INSERT INTO messageBookDetails (SermonCode,YearOfSermon,SermonTitle,SermonText) values (:1,:2,:3,:4)" into tSQL
      revExecuteSQL tDatabaseID, tSQL,"tSermonCode","tSermonYear","tSermonTitle","tSermonText"

One day when I've a little more experience with Live Code I'll figure it out, but at this stage I'm only to happy to have been pointed in a direction that yielded a result. So, I thank you for that.

[sturgis]

If you decide to try the merge method again I suspect that it doesnt handle the multiline method. So if you try it again it might work better like so:

Code: Select all

put "INSERT INTO messageBookDetails " \
      & "( SermonCode, YearOfSermon, SermonTitle, SermonText ) "\
      & merge(" values ( '[[tSermonCode]]','[[tSermonYear]]', '[[tSermonTitle]]', '[[tSermonText]]')") into tSQL

Also, since I don't know if tSermonYear is a number or string in your db, the ' around [[tSermonYear]]' might be an issue. (passing a string to an integer db field)

As you noted though, the other method works SO much better, plus there are security benefits from doing it with the placeholder method. (makes it harder for someone to do sql injection type attacks, and makes it so you don't really have to worry about data types, strings with quotes etc... At least as far as I know)

[phaworth]

As you noted though, the other method works SO much better, plus there are security benefits from doing it with the placeholder method. (makes it harder for someone to do sql injection type attacks, and makes it so you don't really have to worry about data types, strings with quotes etc... At least as far as I know)

The placeholder method is definitely the best way to go about all this for sure. Personally, I've gone the route of using an array instead of multiple variables. The array keys are the numbers you use for the placeholders, so tArray[1] goes into :1, etc. When your typing skills aren't that great (like mine), it's less error prone!

Pete

[NigelS]

Thank you gents. I will be experamenting during the weekend and well post the outcomes as soon as I've reached a satifactory conclusion.

I thank you for your comments and I must convey that it if was not for your comments I would still be on the road of frustration.