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
Using Wildcards in Variables in SQLITE queries
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
-
- Posts: 77
- Joined: Thu May 21, 2015 2:41 am
Re: Using Wildcards in Variables in SQLITE queries
Hi Not a lot of thought,
the wildcard character in SQLite is %!
First hit on Google after entering "wildcard sqlite"
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
the wildcard character in SQLite is %!
First hit on Google after entering "wildcard sqlite"
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
-
- Posts: 77
- Joined: Thu May 21, 2015 2:41 am
Re: Using Wildcards in Variables in SQLITE queries
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.
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.
-
- Posts: 77
- Joined: Thu May 21, 2015 2:41 am
Re: Using Wildcards in Variables in SQLITE queries
Yep...that was the problem...I'm a moron. Thanks for the help Klaus. Sorry, for being the most annoying member.
Thanks
Chris
Thanks
Chris
Re: Using Wildcards in Variables in SQLITE queries
Hi Chris,
* is the wildcard character in Livecode, that's why!
Best
Klaus
* is the wildcard character in Livecode, that's why!
No need to get vain, you are NOT!Sorry, for being the most annoying member.
Best
Klaus