No data in sqlite datbase after insert statement.

[Quinton B.]

Hello, I'm able to insert data into my database, but when I go to check it through the sqlite manager on Firefox I cannot see any data. Yet when I run my insert statement through the execute sql tab it shows up in the table.

Small portion of my problem:

-- This is in the stack script.
on preOpenStack
global gConnectionID

put specialFolderPath("desktop" & "/FIRST.sqlite") into tDatabaseAddress

put revOpenDatabase("sqlite", tDatabaseAddress) into gConnectionID
end preOpenStack


-- This is the script of a button.
on mouseUp
put the text of field "field_UserName_NewAccount" into tUserName
put the text of field "field_Password_NewAccount" into tPassword
put the text of field "field_ConfirmPassword_NewAccount" into tConfirmPassword

put "INSERT INTO MentorAccount VALUES ('tUserName','tPassword','tConfirmPassword');" into tSQL

revExecuteSQL, gConnectionID, tSQL
revCloseDatabase gConnectionID
end mouseUp


To conclude the insert statement works fine in sqlite manager but no data is actually inserted into the database.

[AxWald]

Hi,

Hello, I'm able to insert data into my database, but when I go to check it through the sqlite manager on Firefox I cannot see any data. Yet when I run my insert statement through the execute sql tab it shows up in the table.

You do a refresh in SQLite Manager after the insert (Shift-F5)? *1)

Else you should always do this:

Code: Select all

...
   revExecuteSQL, gConnectionID, tSQL
   if the result is not a number then
      answer error "Ooops:" & CR & the result
   end if
...

This will tell you in time when a query fails, often including the reason.

Further:

Code: Select all

put the text of field "field_Password_NewAccount" into tPassword

Noooo! At least use:

Code: Select all

put SHAencode(the text of field "field_Password_NewAccount") into tPassword

function SHAencode MyString
   get sha1digest(MyString)
   put empty into MyVar
   get binarydecode("H*",it,MyVar)
   return myvar
end SHAencode

Have fun!

*1): Don't say "Axwald, you moron, do you think I'm stupid?" - I don't know how often I fooled myself forgetting this ...

[MaxV]

Hello, I'm able to insert data into my database, but when I go to check it through the sqlite manager on Firefox I cannot see any data. Yet when I run my insert statement through the execute sql tab it shows up in the table.

Small portion of my problem:

Code: Select all

-- This is in the stack script.
on preOpenStack
global gConnectionID

put specialFolderPath("desktop" & "/FIRST.sqlite") into tDatabaseAddress

This is an error, correct code is code:

Code: Select all

put (specialFolderPath("desktop") & "/FIRST.sqlite") into tDatabaseAddress

[Quinton B.]

I have already tried refreshing the database earlier and it doesn't do anything.

[jacque]

Every handler that uses a global variable must declare the global. The preopenstack handler does that but the button mouseUp does not. That means that the button doesn't know what gConnectionID is, and the command will not run correctly.

[Quinton B.]

Actually, it was, I just forgot to put that in my first question, because I was typing it from my phone, but here is all the code from my mac.

--------------------------------------------------

global gConnectionID
on mouseUp
-- check the global connection ID to make sure we have a database connection
if gConnectionID is not a number then
answer error "Please connect to the database first."
exit to top
end if
-- edit these variables to match your database & table
-- this assumes a table called Table1 with 3 fields
put the text of field "field_UserName_NewAccount" into
put SHAencode(the text of field "field_Password_NewAccount") into tPassword
put the text of field "field_ConfirmPassword_NewAccount" into tConfirmPassword
put tConfirmPassword into tCP
put the text of field "field_TeamNumber_NewAccount" into tTeamNumber
put tTeamNumber into tTN
put the text of field "field_Email_NewAccount" into tEmail
put tEmail into tE
put the text of field "field_ConfirmEmail_NewAccount" into tConfirmEmail
put tConfirmEmail into tCE
put the text of field "field_PhoneNumber_NewAccount" into tPhoneNumber
put tPhoneNumber into tPN
put the text of field "field_ConfirmPhoneNumber_NewAccount" into tConfirmPhoneNumber
put tConfirmPhoneNumber into tCPN
put the text of button "button_Country_NewAccount" into tCountry
put tCountry into tC
put the text of field "field_Street_NewAccount" into tStreet
put tStreet into tS
put the text of field "field_State_NewAccount" into tState
put tState into tST
put the text of field "field_City_NewAccount" into tCity
put tCity into tCI
put the text of field "field_ZipCode_NewAccount" into tZipCode
put tZipCode into tZC
put the text of field "field_StateIdNumber_NewAccount" into tSateIdNumber
put tStateIdNumber into tSIN
put the text of field "field_FirstName_NewAccount" into tFirstName
put tFirstName into tFN
put the text of field "field_MiddleName_NewAccount" into tMiddleName
put tMiddleName into tMN
put the text of field "field_LastName_NewAccount" into tLastName
put tLastName into tLN
put the text of field "field_Age_NewAccount" into tAge
put tAge into tA
put the text of field "field_TIMSNumber_NewAccount" into tTIMSNumber
put tTIMSNumber into tTIMSN
put the text of button "button_SecurityQuestion_NewAccount" into tSecurityQuestion
put tSecurityQuestion into tSQ
put the text of field "field_SecurityQuestion_NewAccount" into tSecurityQuestionAnswer
put tSecurityQuestionAnswer into tSQA
-- Table Name
-- construct the SQL - the :1, :2 & :3 placeholders in the SQL will be filled by variables in the revExecuteSQL line
//put tUN, tP, tCP, tTN, tE, tCE, tPN, tCPN, tC, tS, tST, tCI, tZC, tSIN, tFN, tMN, tLN, tA, tTIMSN, tSQ, tSQA into tValues
put "INSERT INTO MentorAccount VALUES ('tUserName','tPassword','tCP','tTN','tE','tCE','tPN','tCPN','tC','tS','tST','tCI','tZC','tSIN','tFN','tMN','tLN','tA','tTIMSN','tSQ','tSQA');" into tSQL
-----(UserName,Password,ConfirmPassword,TeamNumber,Email,ConfirmEmail,PhoneNumber,ConfirmPhoneNumber,Country,Street,State,City,ZipCode,StateIdNumber,FirstName,MiddleName,LastName,Age,TIMSNumber,SecurityQuestion,SecurityQuestionAnswer)
/*put "INSERT INTO MentorAccount (UserName,Password,ConfirmPassword,TeamNumber,Email,CoqnfirmEmail,PhoneNumber,ConfirmPhoneNumber,Country,Street,State,City,ZipCode,StateIdNumber,FirstName,MiddleName,LastName,Age,TIMSNumber,SecurityQuestion,SecurityQuestionAnswer) " & \
merge("VALUES ('[[tUN]]', '[[tP]]', '[[tCP]]', '[[tTN]]', '[[tE]]', '[[tCE]]', '[[tPN]]', '[[tCPN]]', '[[tC]]', '[[tS]]', '[[tST]]', '[[tCI]]', '[[tZC]]', '[[tSIN]]', '[[tFN]]', '[[tMN]]', '[[tLN]]', '[[tA]]', '[[tTIMSN]]', '[[tSQ]]', '[[tSQA]]')") into tSQL*/
-- send the SQL to the database, filling in the placeholders with data from variables
revExecuteSQL gConnectionID, tSQL -- check the result and display the data or an error message
if tSQL is not empty then
answer info "New record added."
else
answer error "There was a problem adding the record to the database:" & cr & the result
end if
revCloseDatabase gConnectionID
end mouseUp

-------------------------------------------------

function SHAencode MyString
get sha1digest(MyString)
put empty into MyVar
get binarydecode("H*",it,MyVar)
return myvar
end SHAencode

[AxWald]

Hi,

Code: Select all

   revExecuteSQL gConnectionID, tSQL   -- check the result and display the data or an error message
   if tSQL is not empty then
      answer info "New record added."
   else
      answer error "There was a problem adding the record to the database:" & cr & the result
   end if

Beside possible other quirks you don't check the result. tSQL should contain the SQL string (that would be useful see to determine if it's correct), but what comes back from the query is in "the result"!

So try this:

Code: Select all

...
   revExecuteSQL, gConnectionID, tSQL
   if the result is not a number then  --  the result is a number, or an error description
      put the result & CR & gConnectionID & CR & tSQL into myErr  --  we construct a variable
      set the clipboardData["text"] to myErr  --  and copy it for further pasting
      answer error "Ooops:" & CR & myErr  --  and thow an error
   end if
...

This way you'll get a warning if it fails, and have any useful error data ready to paste here ;-) Try it, and let us know what happened!

Have fun!

[MaxV]

put the text of field "field_UserName_NewAccount" into

into ???

[MaxV]

However use:
########CODE to copy and paste#######
put revDataFromQuery(tab,return,gConnectionID,tSQL) into tRecords
if tRecords is not empty then
answer info "New record added."
else
answer error "There was a problem adding the record to the database:" & cr & the tRecords
end if
#####END OF CODE generated by http://tinyurl.com/j8xf3xq with livecode 9.0.0-dp-6#####

[Quinton B.]

Thanks, Guys, I figured it out.
Here is the correct statement.

revExecuteSQL gDatabaseID, "INSERT INTO MentorAccount VALUES (:1,:2,:3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21)","tUserName","tPassword","tConfirmPassword","tTeamNumber","tEmail","tConfirmEmail","tPhoneNumber","tConfirmPhoneNumber","tCountry","tStreet","tState","tCity","tZipCode","tStateIdNumber","tFirstName","tMiddleName","tLastName","tAge","tTIMSNumber","tSecurityQuestion","tSecurityQuestionAnswer"