Problems trying to use SQLite functions in a SELECT statement
pete at mollysrevenge.com
Tue Nov 10 13:11:23 CST 2009
Some time ago, I experienced problems trying to get revQueryDatabase
to process a SELECT statement that included the SQLite substr function
- rev QueryDatabase failed with the ubiquitous "Missing database ot
SQL error" message. The same statement, cut and pasted into either
the Sqlite3 command line tool or the Firefox SQLite Manager extension
worked perfectly. At the time I was able to work around the problem
within my rev code.
I'm now running into the same problem trying to use the SQLite
group_concat function - revQueryDatabase fails, same statement works
fine in sqlite3/Firefox extension. Also tried revDataFromQuery which
also failed. This time, it will be a major hassle to write code within
my application to work around this.
It would appear that the rev database handlers cannot deal with
certain SQlite functions. Some of the core functions such as max,
min, round seem to work (haven't tried them all), The date and Time
functions also seem to work OK. Even some of the aggregate functions
(which is where group_concat is listed) such as sum, count, avg work
OK. But substr and group_concat definitely cause problems. If that
is the case it's a major oversight/bug/problem in my opinion.
Has anyone else run into this issue and perhaps found a workaround?
Would it be possible, for example, to run sqlite3 from within rev,
pass it the SELECT statement and somehow get the results back?
More information about the use-livecode