Error when Creating New Record

Creating desktop or client-server database solutions?

Moderators: Klaus, FourthWorld, heatherlaine, kevinmiller, robinmiller

Post Reply
smith8867
Posts: 57
Joined: Tue Nov 18, 2014 5:36 pm

Error when Creating New Record

Post by smith8867 » 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:
Image

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

quailcreek
Posts: 746
Joined: Sun Feb 04, 2007 11:01 pm
Location: McKenna, WA

Re: Error when Creating New Record

Post by quailcreek » Thu Jan 21, 2016 9:54 pm

Hi,
I think it would help if you share the code you've written.
Tom
MacBook Pro OS Mojave 10.14

smith8867
Posts: 57
Joined: Tue Nov 18, 2014 5:36 pm

Re: Error when Creating New Record

Post by smith8867 » Thu Jan 21, 2016 10:08 pm

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

Image

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
Posts: 746
Joined: Sun Feb 04, 2007 11:01 pm
Location: McKenna, WA

Re: Error when Creating New Record

Post by quailcreek » 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
Tom
MacBook Pro OS Mojave 10.14

smith8867
Posts: 57
Joined: Tue Nov 18, 2014 5:36 pm

Re: Error when Creating New Record

Post by smith8867 » 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!

quailcreek
Posts: 746
Joined: Sun Feb 04, 2007 11:01 pm
Location: McKenna, WA

Re: Error when Creating New Record

Post by quailcreek » 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
Tom
MacBook Pro OS Mojave 10.14

smith8867
Posts: 57
Joined: Tue Nov 18, 2014 5:36 pm

Re: Error when Creating New Record

Post by smith8867 » 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

quailcreek
Posts: 746
Joined: Sun Feb 04, 2007 11:01 pm
Location: McKenna, WA

Re: Error when Creating New Record

Post by quailcreek » 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) "&\
Tom
MacBook Pro OS Mojave 10.14

smith8867
Posts: 57
Joined: Tue Nov 18, 2014 5:36 pm

Re: Error when Creating New Record

Post by smith8867 » Sat Jan 23, 2016 1:15 am

I knew it was something silly :p
Thanks man!

Post Reply

Return to “Databases”