Display sqlite Data

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: 102
Joined: Mon Mar 20, 2017 5:13 am

Display sqlite Data

Post by Quinton B. » Mon May 01, 2017 8:15 pm

Hello, I'm having trouble displaying the password of an account. I have just made the insert statement to put new account information into the database. Now once you have an account and you forget the password, I'm trying to search the database for the password of a row where the username, security question, and security question answer.

Here is the following code:

global gDatabaseID
on mouseUp
put the text of field "field_Forgot_Password_UserName" into tForgotPasswordUserName
answer tForgotPasswordUserName
put the label of button "button_Forgot_Password_SecurityQuestion" into tForgotPasswordSecurityQuestion
answer tForgotPasswordSecurityQuestion
put the text of field "field_Forgot_Password_SecurityQuestion" into tForgotPasswordSecurityQuestionAnswer
answer tForgotPasswordSecurityQuestionAnswer
##revExecuteSQL gDatabaseID, "SELECT Password FROM MentorAccount Where UserName = tForgotPasswordUserName, SecurityQuestion = tForgotPasswordSecurityQuestion, SecurityQuestionAnswer = tForgotPasswordSecurityQuestionAnswer"
put "SELECT Password FROM MentorAccount Where UserName = tForgotPasswordUserName, SecurityQuestion = tForgotPasswordSecurityQuestion, SecurityQuestionAnswer = tForgotPasswordSecurityQuestionAnswer" into tSQL
put revDataFromQuery(tab,return,gDatabaseID,tSQL) into field "field_Forgot_Password_Password"
end mouseUp

Klaus
Posts: 11009
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Display sqlite Data

Post by Klaus » Mon May 01, 2017 9:15 pm

Hi Quinton,

you are passing the e.g. STRING tForgotPasswordUserName and not its content to the SQL command and also do not use AND to "connect" the conditions!
...
put "SELECT Password FROM MentorAccount Where UserName =" && "'" & tForgotPasswordUserName & "'" && "AND SecurityQuestion = " & "'" & tForgotPasswordSecurityQuestion & "'" && AND SecurityQuestionAnswer =" && "'" & tForgotPasswordSecurityQuestionAnswer & "'" into tSQL
...
I recommend this page to learn some more SQL, I always visit that page when I need to script database stuff :D
https://www.w3schools.com/sql/default.asp

Best

Klaus

SparkOut
Posts: 1948
Joined: Sun Sep 23, 2007 4:58 pm

Re: Display sqlite Data

Post by SparkOut » Tue May 02, 2017 6:46 am

The "password" is really the hashed and salted digest when you store and compare, isn't it? Please say it is. Please!
Storing passwords in the real in the database is really a bad idea.

Klaus
Posts: 11009
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Display sqlite Data

Post by Klaus » Tue May 02, 2017 11:44 am

Yes, sure, but one step after another! 8)

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

Re: Display sqlite Data

Post by Quinton B. » Tue May 02, 2017 3:31 pm

Hey, Klaus. That didn't work.

Klaus
Posts: 11009
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Display sqlite Data

Post by Klaus » Tue May 02, 2017 3:50 pm

Yep, sorry, wrote that out of my head and forgot a QUOTE before the last AND, this one will work:

Code: Select all

...
## In one line of course!
put "SELECT Password FROM MentorAccount Where UserName =" && "'" & tForgotPasswordUserName & "'" && "AND SecurityQuestion = " & "'" & tForgotPasswordSecurityQuestion & "'" && "AND SecurityQuestionAnswer =" && "'" & tForgotPasswordSecurityQuestionAnswer & "'"  into tSQL
...
Hint: To avoid this inconvenience, I ususally use two little functions:

Code: Select all

function q tString
  return QUOTE & tString & QUOTE
end q

function q2 tString
  return "'" & tString & "'"
end q2
Saves a lot of typing and the neccessity to count all closing quotes and single quotes :D

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

Re: Display sqlite Data

Post by Quinton B. » Tue May 02, 2017 9:32 pm

Okay, it doesn't post any errors. But how do I display the search result in a text box?
Attachments
Screen Shot 2017-05-02 at 1.31.55 PM.png

Klaus
Posts: 11009
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Display sqlite Data

Post by Klaus » Tue May 02, 2017 9:53 pm

Quinton B. wrote:Okay, it doesn't post any errors.
Sure? Please take a look at field "Password" in the screenshot.
What exactly does the error say?

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

Re: Display sqlite Data

Post by Quinton B. » Wed May 03, 2017 7:58 pm

Yeah, I'm sure, I forgot to clear that field.

Klaus
Posts: 11009
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Display sqlite Data

Post by Klaus » Wed May 03, 2017 9:15 pm

Hi Quinton,

ah, I see, OK, fine then. :D

When using this:
...
put revDataFromQuery(tab,return,gDatabaseID,tSQL) into tData
## Means: return all found data one line per record (cr), database fields of one record separated by tab
...
The returned data from the database will be one line with three items separated by TAB and you can e.g.:
---
set itemdel to TAB
piut item 1 of tDAta into fld "username"
put item 2 of tData into fld "security question"
put item 3 of tData into fld "security question answer"
...
You get the picture :D


Best

Klaus

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

Re: Display sqlite Data

Post by Quinton B. » Thu May 04, 2017 9:57 pm

I fixed some of your grammar errors, but it still didn't work, so I tried what another form had:

put revQueryDatabase(gDatabaseID, "SELECT * FROM MentorAccount Where UserName="& field "field_Forgot_Password_UserName" & ";") into field "field_Forgot_Password_Password"

But I get the following error
Attachments
Screen Shot 2017-05-04 at 2.00.59 PM.png

Klaus
Posts: 11009
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Display sqlite Data

Post by Klaus » Fri May 05, 2017 10:38 am

Hi Quinton,

not sure, but since you did not use single quotes, the database obviously interprets the content of
field "field_Forgot_Password_UserName" as a column name, which in fact does not exist.

Code: Select all

Try this, using my function q2:
...
put revQueryDatabase(gDatabaseID, "SELECT * FROM MentorAccount Where UserName="& q2(field "field_Forgot_Password_UserName") & ";") into field "field_Forgot_Password_Password"
...
Best

Klaus

AxWald
Posts: 356
Joined: Thu Mar 06, 2014 2:57 pm

Re: Display sqlite Data

Post by AxWald » Fri May 05, 2017 11:25 am

Hi,

UserName is quite sure VARCHAR or TEXT, so you need single quotes.
And "revQueryDatabase" only returns a recordset ID, which is not what you want here.

Besides, single quoting table names has proven helpful, too. And error checking is worth its weight in gold!

I wrote a quick demo with lots of comments for you. Try it!
You'll see that I use the "revDB_*" functions, this is just a matter of taste - they're easy to check, and easy to search in code. You could as well use "revOpenDataBase" and "revDataFromQuery".
(Hint: I do professional work with databases, and all I ever need (of all the rev DB stuff) are: "revDB_connect", "revDB_disconnect", revDB_queryList" and "revDB_execute" ...)

Code: Select all

on mouseUp
   --  Preparing:  --
   put field "UserName_fld" into myUserName
   put "SELECT * FROM 'MentorAccount' WHERE UserName = '" & myUserName & "';" into StrSQL
   put StrSQL  --  for development only, so that we see what we did!
   --> SELECT * FROM 'MentorAccount' WHERE UserName = 'Tate.Tech';
   --  UserName is TEXT or VARCHAR, so single quotes are required!
   
   --  The DB stuff: __
   put revDB_connect("SQLite","D:/Databases/myDB.sqlite") into myDBId  --  open DB
   if myDBId is not a number then
      answer error "DB connect failed!" & CR & myDBId
      exit mouseUp
   end if
   
   put revDB_QueryList("tab","return",myDBId,StrSQL) into myData  --  get Data
   get revDB_disconnect(myDBId)  --  close DB before processing. No need to check result.
   
   set itemdel to comma --  just to be sure ...
   if item 1 of myData = "revdberr" then
      answer error "DB query failed: " & item 1 of myData & CR & StrSQL
      --  having the SQL in the errStr is priceless!
      exit mouseUp
   end if
   
   --  Data processing: --
   put myData  --  for development only, so that we see what we did!
   --> a record, tab-delimited, easy to check in a spread sheet or good text editor
   
   set itemdel to tab --  since now we work with tabbed items
   if the number of lines of myData <> 1 then           --  always carefully check the results of your queries!
      if the number of lines of myData = 0 then
         answer error "No data found!"
      else
         answer error "Multiple records found!"
      end if
   else
      put item 1 of myData into field "FieldForItem_1"   --  you got the idea?
      put item 2 of myData into field "FieldForItem_2"
      --  ...
   end if
end mouseUp


Hope this helps, have fun!
Livecode programming until the cat hits the fan ...

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

Re: Display sqlite Data

Post by Quinton B. » Sun May 14, 2017 11:14 pm

So now I'm trying to update my database with a new password after I have validated that it is their. I have done the validation part but cannot update the password.
Attachments
Capture.PNG

Post Reply

Return to “Getting Started with LiveCode - Complete Beginners”