SQLite - Selecting multiple columns

[Stitch_Fan]

I have a SQLite database that keeps track of the serial ports my rev app is talking to:
Table: SerialPorts

Code: Select all

PortID   PortName    PortStatus
1          COM1:      Open
2          COM2:      Closed

When I try to select data from a single column...

Code: Select all

put "SELECT PortID FROM SerialPorts WHERE PortStatus='Open'" into SQLquery
put revDataFromQuery(,,ConnectionID,SQLquery) into QueryResults

...it works fine. But when I try to grab more than one column...

Code: Select all

put "SELECT PortID, PortName FROM SerialPorts WHERE PortStatus='Open'" into SQLquery
put revDataFromQuery(,,ConnectionID,SQLquery) into QueryResults

...it fails with a revDB error. Seems like the comma between the fields is where the problem starts because the error message says the entire query was "SELECT PortID".

UPDATE: I am passing the SQL as a parameter to another handler that actually calls the database. The comma caused the SQL to get interpreted as two parameters. So I changed the , to ~ and do a quick replace before executing the query and this works.

Code: Select all

put "SELECT PortID~PortName FROM SerialPorts WHERE PortStatus='Open'" into SQLquery
send "DBsearch" && SQLquery to stack "TCS"
put the result into QueryResults

on DBsearch SQLquery
...
put revDataFromQuery(,,ConnectionID,replaceText(SQLquery,"~",",")) into QueryResults
...
return QueryResults
end DBsearch

Can anyone think of a better way to do this?

thanks,
Mike
Stitch_Fan

[Janschenkel]

If you can live with fetching all the columns, you could just use '*' instead of specifying the individual columns.

Code: Select all

put "SELECT * FROM SerialPorts WHERE PortStatus='Open'" into SQLquery

Also, have you tried stringing all the column names togethert without a space after the comma?

Code: Select all

put "SELECT PortID,PortName FROM SerialPorts WHERE PortStatus='Open'" into SQLquery

Jan Schenkel.

[trevordevore]

When using a variable with "send" the engine treats this:

Code: Select all

send "DBsearch" && SQLquery to stack "TCS"

differently than this:

Code: Select all

send "DBsearch SQLquery" to stack "TCS" 

In the later the variable is treated as a single parameter to the function DBsearch. If you want your send statements to always behave how you would expect then use variables and enclose the entire statement in quotes. If you use this approach your comma problems will go away.

If you are using Rev 3.5 you could use dispatch instead:

Code: Select all

dispatch "DBsearch" to stack "TCS" with SQLquery

[Stitch_Fan]

Thanks for your help Jan and Trevor. The learning curve to move from doing things "the hard way" i.e. a bunch of flat files to SQLite has been a lot easier than I thought it would be. The only problem is the rev ide releases are outpacing my glacial development speed. I think using the dispatch command will be the perfect solution. Thanks again, Mike aka Stitch Fan