button script for accessing an sqlite database

[KMGriffiths]

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?

[Philhold]

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

[SparkOut]

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.

[KMGriffiths]

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

[sturgis]

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.

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

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.

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

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

[chris9610]

If you list the exact script we can look at it and fix it.

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