LiveCode Forums.

Posted: **Thu Jan 21, 2016 7:15 pm**

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?

Posted: **Thu Jan 21, 2016 9:54 pm**

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

Posted: **Thu Jan 21, 2016 10:08 pm**

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

Posted: **Thu Jan 21, 2016 11:20 pm**

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

Posted: **Fri Jan 22, 2016 6:33 pm**

quailcreek wrote: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!

Posted: **Fri Jan 22, 2016 7:13 pm**

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

Posted: **Fri Jan 22, 2016 9:49 pm**

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

Posted: **Fri Jan 22, 2016 9:57 pm**

My bad... put a ) after clientCompany

Code: Select all

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

Posted: **Sat Jan 23, 2016 1:15 am**

I knew it was something silly :p
Thanks man!

LiveCode Forums.

Error when Creating New Record

Error when Creating New Record

Re: Error when Creating New Record

Re: Error when Creating New Record

Re: Error when Creating New Record

Re: Error when Creating New Record

Re: Error when Creating New Record

Re: Error when Creating New Record

Re: Error when Creating New Record

Re: Error when Creating New Record