Problems trying to use SQLite functions in a SELECT statement

[Jan Schenkel]

Hi Peter,

Which version of rev are you using when you experience this problem? Rev 4.0 ships with the latest driver for SQLite3 - but previous versions were lagging a bit.

Also, do you have an example to upload somewhere so we cant take a look at your database structure and code?

Jan Schenkel
=====
Quartam Reports & PDF Library for Revolution
<http://www.quartam.com>

=====
"As we grow older, we grow both wiser and more foolish at the same time."  (La Rochefoucauld)


--- On Tue, 11/10/09, Peter Haworth <pete at mollysrevenge.com> wrote:

> From: Peter Haworth <pete at mollysrevenge.com>
> Subject: Problems trying to use SQLite functions in a SELECT statement
> To: use-revolution at lists.runrev.com
> Date: Tuesday, November 10, 2009, 11:11 AM
> 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?
> 
> Pete Haworth
> 
> 
> 
> 
> 
> 
> 
> 
> _______________________________________________
> use-revolution mailing list
> use-revolution at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage
> your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-revolution
>