Error when Creating New Record

[smith8867]

I have been following this guide: http://lessons.livecode.com/m/4071/l/70 ... l-database

Connecting works, I get the connectionID and so on, its just that I get this error:


I was wondering how I can resolve this to enter the data to the database?

[quailcreek]

Hi,
I think it would help if you share the code you've written.

[smith8867]

I fixed that error, realised it was a quotes missing, however I'm getting this now:

Code: Select all

on insertRecord
   //Setup colums that the data will be inserted to
   put "clientName, clientEmail, clientNumber, jobDesc, postedBy, clientCompany" into dbCollumn // not variable names, just the collumns of db
   //
   //Setup the SQL command
   put "INSERT INTO " & dbTable &  " (" & dbCollumn & ") VALUES (:1, :2, :3, :4, :5, :6)" into dbSQL //dbSQL stores this command
   //
   //Send the SQL command
   revExecuteSQL connectionID, dbSQL, "clientName", "clientEmail", "clientNumber", " jobDesc", "poster", "clientCompany"
   //
   if the result is a number then
        answer info "New record added."
    else
        answer error "There was a problem adding the record to the database:" & cr & the result
    end if
end insertRecord

I'm kind of new to the SQL with LiveCode, any help would be much appreciated.
Thanks

[quailcreek]

I've learned from some of the best here on the forum. I suggest using this kind of set up. The values that are put into the array can be variables. I'm more of an SQLite user but this should work for MYSQL too.

Code: Select all

on insertRecord
   put "Fred Flintstone" into SQLArray[1]
   put "myemail@fred.com" into SQLArray[2] 
   put "123" into SQLArray[3]
   put "Programmer" into SQLArray[4]
   put "Bettey Flintstone" into SQLArray[5]
   put "Big Business" into SQLArray[6]
   
   get executeSQL("BEGIN")
   if it is not an integer then
      -- error handling code
      answer "An error occured BEGIN"
      exit insertRecord
   end if
   
   put "INSERT INTO " & dbTable & "(clientName, clientEmail, clientNumber, jobDesc, postedBy, clientCompany "&\
   "VALUES (:1,:2,:3,:4,:5,:6)" into dbSQL
   revExecuteSQL connectionID, dbSQL, "SQLArray"
   
   if the result is not an integer then
      get executeSQL("ROLLBACK")
      -- error handling here
      answer "An error occured ROLLBACK"
      exit insertRecord
   end if
   
   get executeSQL("COMMIT")
   if the result is a number then
      answer info "New record added."
   else
      answer error "There was a problem adding the record to the database:" & cr & the result
   end if
end insertRecord

This should go into your stack script.

Code: Select all

function executeSQL psql,pparm
   if pparm is empty then
      revExecuteSQL the uDatabaseID of this stack, psql
   else
      revExecuteSQL the uDatabaseID of this stack, psql,"pparm"
   end if
   
   return the result
end executeSQL

[smith8867]

I've learned from some of the best here on the forum. I suggest using this kind of set up. The values that are put into the array can be variables. I'm more of an SQLite user but this should work for MYSQL too.

Code: Select all

on insertRecord
   put "Fred Flintstone" into SQLArray[1]
   put "myemail@fred.com" into SQLArray[2] 
   put "123" into SQLArray[3]
   put "Programmer" into SQLArray[4]
   put "Bettey Flintstone" into SQLArray[5]
   put "Big Business" into SQLArray[6]
   
   get executeSQL("BEGIN")
   if it is not an integer then
      -- error handling code
      answer "An error occured BEGIN"
      exit insertRecord
   end if
   
   put "INSERT INTO " & dbTable & "(clientName, clientEmail, clientNumber, jobDesc, postedBy, clientCompany "&\
   "VALUES (:1,:2,:3,:4,:5,:6)" into dbSQL
   revExecuteSQL connectionID, dbSQL, "SQLArray"
   
   if the result is not an integer then
      get executeSQL("ROLLBACK")
      -- error handling here
      answer "An error occured ROLLBACK"
      exit insertRecord
   end if
   
   get executeSQL("COMMIT")
   if the result is a number then
      answer info "New record added."
   else
      answer error "There was a problem adding the record to the database:" & cr & the result
   end if
end insertRecord

This should go into your stack script.

Code: Select all

function executeSQL psql,pparm
   if pparm is empty then
      revExecuteSQL the uDatabaseID of this stack, psql
   else
      revExecuteSQL the uDatabaseID of this stack, psql,"pparm"
   end if
   
   return the result
end executeSQL

Thanks for replying. I think I understand the first piece of code. Could you explain the second piece that goes in the Stack script? I'm a little confused at that.
Thanks again man!

[quailcreek]

Good question. BEGIN, ROLLBACK and COMMIT are used by SQL. They should be used when you modify the Db. You can look them up. Also, I changed the function to match your code. I store the connection ID in a stack property (the uDatabaseID of this stack). Yours. I believe, is stored in a global. This might help you understand the function a little better.

get executeSQL("BEGIN")
get executeSQL("ROLLBACK")
get executeSQL("COMMIT")

Code: Select all

    function executeSQL psql,pparm
       if pparm is empty then
          revExecuteSQL connectionID, psql
       else
          revExecuteSQL connectionID, psql,"pparm"
       end if
       
       return the result
    end executeSQL

[smith8867]

Thanks qualcreek. I have updated the stack script code, however I keep getting a rollback error message. I can't seem to locate the error in the code for adding a record:

Code: Select all

on insertRecord
   -- Put the contents of the fields into SQLarray
   put field "clientName" into SQLarray[1]
   put field "clientEmail" into SQLarray[2]
   put field "clientNumber" into SQLarray[3]
   put field "jobDesc" into SQLarray[4]
   put field "poster" into SQLarray[5]
   put field "clientCompany" into SQLarray[6]
   //
   get executeSQL("BEGIN")
   if it is not an integer then
      answer "An error occured BEGIN"
      exit insertRecord
   end if
   //
   //
   put "INSERT INTO " & dbTable & " (clientName, clientEmail, clientNumber, jobDesc, postedBy, clientCompany "&\
   "VALUES (:1,:2,:3,:4,:5,:6)" into dbSQL
   revExecuteSQL connectionID, dbSQL, "SQLarray"
   //
   if the result is not an integer then
      get executeSQL("ROLLBACK")
      answer "An error occured ROLLBACK"
      exit insertRecord
   end if
   //
   //
   get executeSQL("COMMIT")
   if the result is a number then
      answer info "New record added."
   else
      answer error "There was a problem adding the record to the database:" & cr & the result
   end if
end insertRecord

[quailcreek]

My bad... put a ) after clientCompany

Code: Select all

 put "INSERT INTO " & dbTable & "(clientName, clientEmail, clientNumber, jobDesc, postedBy, clientCompany) "&\

[smith8867]

I knew it was something silly :p
Thanks man!