Using Wildcards in Variables in SQLITE queries

Creating desktop or client-server database solutions?

Moderators: Klaus, FourthWorld, heatherlaine, kevinmiller, robinmiller

Post Reply
Not a lot of thought
Posts: 77
Joined: Thu May 21, 2015 2:41 am

Using Wildcards in Variables in SQLITE queries

Post by Not a lot of thought » Mon Dec 07, 2015 9:22 pm

I am creating a programming in which I would like the UI to be almost entirely interactive. So if you click on something it sends the ID of that item to a variable to be included in the WHERE clause of the SQL statements updating the card. For example: you might have several items in different classes of category all of which may be interdependent or dependent upon another. Employee, Area, sales, department, Country.

Put "*" into tArea
put "*" into temployeeID
Put "*" into tDepartment
Put "*" into tCountry
Assuming we're already connected to the DB
Put "SELECT sales FROM employee WHERE Area is "&tArea&" AND employeeID is "&tEmployeeID" AND department is "&tDepartment&" AND Country is "&tCountry&";" into tSQL
put revDataFromQuery(tab,return,tDatabaseID,tSQL) into field "X"

I think Livecode is escaping out the variables, but I'm not sure. SQL returns an error when running the query at "*".
Essentially what I would like for it to do is when the user selects something it sends the ID up to the variable changes just that variable in the query to return everything for that ID. So click on an employeeID and it returns all the information for that employee. Click on a department then employee it returns everything for that employee in that department, etc. I'm not exactly sure what I'm doing wrong and if anyone has an easier way please feel free to make a suggestion. Typed on my phone so code may have a typo or two, please excuse I've already checked for that on the program.

Thanks

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

Re: Using Wildcards in Variables in SQLITE queries

Post by Klaus » Mon Dec 07, 2015 11:52 pm

Hi Not a lot of thought,

the wildcard character in SQLite is %!
First hit on Google after entering "wildcard sqlite" 8)

Not sure I understand correctly, but this concatenated SQL command, presumig * would be correct syntax:
...
Put "*" into tArea
put "*" into temployeeID
Put "*" into tDepartment
Put "*" into tCountry
Put "SELECT sales FROM employee WHERE Area is "&tArea&" AND employeeID is "&tEmployeeID" AND department is "&tDepartment&" AND Country is "&tCountry&";" into tSQL
...
will return the same as this:
...
Put "SELECT sales FROM employee
...
No? Yes?


Best

Klaus

Not a lot of thought
Posts: 77
Joined: Thu May 21, 2015 2:41 am

Re: Using Wildcards in Variables in SQLITE queries

Post by Not a lot of thought » Tue Dec 08, 2015 12:27 am

Initiatally of course, but the above would allow for me to override the wildcard variable when the user clicked on any given portion of the UI with a more specific query. I suppose I wasn't overly clear, but I'd like whenever the person clicks on a section of the UI associated with a specific department I want to override the tDepartment variable with that department's ID, rerun the query and update the UI with info for just that department. But I want to leave the query versatile. So the user clicks on a department and sales for that department show up then using the same query click on an employee get info on that employee's sales in that department.

Thanks

Chris

PS I'll try the %. It was late when I was trying to figure it out. I'm not sure why I was under the impression that * was the wildcard. That's probably my problem.

Not a lot of thought
Posts: 77
Joined: Thu May 21, 2015 2:41 am

Re: Using Wildcards in Variables in SQLITE queries

Post by Not a lot of thought » Tue Dec 08, 2015 1:44 am

Yep...that was the problem...I'm a moron. Thanks for the help Klaus. Sorry, for being the most annoying member. :oops:


Thanks
Chris

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

Re: Using Wildcards in Variables in SQLITE queries

Post by Klaus » Tue Dec 08, 2015 12:05 pm

Hi Chris,

* is the wildcard character in Livecode, that's why!
Sorry, for being the most annoying member.
No need to get vain, you are NOT! :D


Best

Klaus

Post Reply

Return to “Databases”