button script for accessing an sqlite database
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
-
- Posts: 7
- Joined: Thu Aug 06, 2009 7:32 am
button script for accessing an sqlite database
Have created 2 databases, PROJECTS and DOORS. My d/base runs correctly in sqlite. I have tried to execute the following button script in Revolution:
select * from projects, doors where projects.projectID = doors.projectID and projects.name = 'a non literal' which will be input on a card
This will not compile and I note that SELECT is showing in red script.
Can anyone advise please?
select * from projects, doors where projects.projectID = doors.projectID and projects.name = 'a non literal' which will be input on a card
This will not compile and I note that SELECT is showing in red script.
Can anyone advise please?
Michael Griffiths
Hi Michael,
Have you tried to trouble shoot your SQL by simplifying it. ie does
select * from projects where projects.projectID = doors.projectID
work?
Does
select * from projects, doors where projects.projectID = doors.projectID
work?
Also I couldn't understand what this was all about. "and projects.name = 'a non literal' which will be input on a card "
Cheers
Phil
Have you tried to trouble shoot your SQL by simplifying it. ie does
select * from projects where projects.projectID = doors.projectID
work?
Does
select * from projects, doors where projects.projectID = doors.projectID
work?
Also I couldn't understand what this was all about. "and projects.name = 'a non literal' which will be input on a card "
Cheers
Phil
Could you post a little bit more of your script maybe, and show how are you calling the SQL select statement? Have you already got the DB id, from the connection statement, and using it with revExecuteSQL, revDataFromQuery or revQueryDatabase? There are slightly different circumstances for the use of each of these.
Also are you using the Database Query Builder? That will be entirely different in the way you use it.
Also are you using the Database Query Builder? That will be entirely different in the way you use it.
-
- Posts: 7
- Joined: Thu Aug 06, 2009 7:32 am
Button script for sqlite database
Hi Phil, Thanks for your reply. I must say I am new to databases but I bought a program called SQLITE EXPERT which I used to build the query, the script of which I sent. This did work in the SQLITE EXPERT software in that using ... AND PROJECTS.NAME = 'LINCOLN' extracted all the projects for LINCOLN. Iwas hoping to transfer this script to a Revolution card and set up a text field to input a variable rather than an actual name.
If this is not any clearer, I apologise. Michael
If this is not any clearer, I apologise. Michael
Michael Griffiths
Re: Button script for sqlite database
Here, i'll post some scripts i'm using now while working on a program so you can compare and see where yours might need adjustment.
Heres the code (in a button) where I connect to the database and generate the id.
A simple way to select data. tPass contains the table name. The selection string including tPass is put into tSQl which is then used as the full select string in revdb_queryList. comma and carriage return are column and row delimiters that are put into the receiving container so in this case tList ends up with a list like
column1,column2,colum3,column4<cr>
column1,column2...
You get the idea i'm sure.
Heres the code (in a button) where I connect to the database and generate the id.
Code: Select all
on mouseUp
global gConID
if gConID is empty then
set the defaultFolder to specialFolderPath("desktop")
put revOpenDatabase("sqlite","budget.db",,,,,,) into tConID
if tConID = "" then
answer warning "Problem creating or accessing databse!"
else
answer information "Budget DB Connected! Your connection ID is: " & tConID
put tConID into gConID
end if
else
answer information "Budget DB already connected"
end if
end mouseUp
column1,column2,colum3,column4<cr>
column1,column2...
You get the idea i'm sure.
Code: Select all
on getAll tPass
put "select * from" && tPass into tSQL
put empty into field "outPut"
put revdb_querylist(comma,cr,gConID, tSQL) into tList
put tList into field "outPut"
put tList
end getAll
KMGriffiths wrote:Hi Phil, Thanks for your reply. I must say I am new to databases but I bought a program called SQLITE EXPERT which I used to build the query, the script of which I sent. This did work in the SQLITE EXPERT software in that using ... AND PROJECTS.NAME = 'LINCOLN' extracted all the projects for LINCOLN. Iwas hoping to transfer this script to a Revolution card and set up a text field to input a variable rather than an actual name.
If this is not any clearer, I apologise. Michael
Re: button script for accessing an sqlite database
If you list the exact script we can look at it and fix it.
Example:
Example:
Code: Select all
put fld "efLastName" into qcname
put "'"&qcname&"%'" into qcname
put "select * from customer where cust_lastname like "&qcname&" order by cust_lastname limit 100" into dosql
put revDataFromQuery(tab, return, mydbId, dosql) into shcust
Developing with Windows XP & Revolution 4.5