No data in sqlite datbase after insert statement.

Got a LiveCode personal license? Are you a beginner, hobbyist or educator that's new to LiveCode? This forum is the place to go for help getting started. Welcome!

Moderators: Klaus, FourthWorld, heatherlaine, kevinmiller

Post Reply
Quinton B.
Posts: 108
Joined: Mon Mar 20, 2017 5:13 am

No data in sqlite datbase after insert statement.

Post by Quinton B. » Wed Apr 26, 2017 4:56 am

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
Posts: 368
Joined: Thu Mar 06, 2014 2:57 pm

Re: No data in sqlite datbase after insert statement.

Post by AxWald » Wed Apr 26, 2017 8:37 am

Hi,
Quinton B. wrote: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:
Quinton B. wrote:

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 ...
Livecode programming until the cat hits the fan ...

MaxV
Posts: 1574
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: No data in sqlite datbase after insert statement.

Post by MaxV » Wed Apr 26, 2017 2:51 pm

Quinton B. wrote: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
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

Quinton B.
Posts: 108
Joined: Mon Mar 20, 2017 5:13 am

Re: No data in sqlite datbase after insert statement.

Post by Quinton B. » Wed Apr 26, 2017 3:32 pm

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

jacque
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 5114
Joined: Sat Apr 08, 2006 8:31 pm
Location: Minneapolis MN
Contact:

Re: No data in sqlite datbase after insert statement.

Post by jacque » Wed Apr 26, 2017 4:38 pm

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.
Jacqueline Landman Gay | jacque at hyperactivesw dot com
HyperActive Software | http://www.hyperactivesw.com

Quinton B.
Posts: 108
Joined: Mon Mar 20, 2017 5:13 am

Re: No data in sqlite datbase after insert statement.

Post by Quinton B. » Wed Apr 26, 2017 8:31 pm

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
Posts: 368
Joined: Thu Mar 06, 2014 2:57 pm

Re: No data in sqlite datbase after insert statement.

Post by AxWald » Thu Apr 27, 2017 10:00 am

Hi,
Quinton B. wrote:

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!
Livecode programming until the cat hits the fan ...

MaxV
Posts: 1574
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: No data in sqlite datbase after insert statement.

Post by MaxV » Fri Apr 28, 2017 1:50 pm

put the text of field "field_UserName_NewAccount" into
into ???
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

MaxV
Posts: 1574
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: No data in sqlite datbase after insert statement.

Post by MaxV » Fri Apr 28, 2017 1:53 pm

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#####
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

Quinton B.
Posts: 108
Joined: Mon Mar 20, 2017 5:13 am

Re: No data in sqlite datbase after insert statement.

Post by Quinton B. » Mon May 01, 2017 4:47 pm

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"

Post Reply

Return to “Getting Started with LiveCode - Complete Beginners”