SQLite revQueryDatabase "where" param won't detect invalid target

[Jan Schenkel]

--- Paul Murray <mr.prmurray at gmail.com> wrote:
> When trying to detect that an entry in a table is
> missing, revQueryDatabase
> "SELECT... WHERE..." is returning an integer
> (cursor) result when the table
> is actually empty or the search value has never been
> inserted into the
> table:
> 
> The following code is preceded by the creation of
> the table before any
> records are inserted:
> 
> put "SELECT * FROM players WHERE playerID=:1" into
> doSQL
> put "abc1234" into anonID
> put revQuerydatabase(wgdbConID,doSQL,"anonID") into
> playersCursor
> answer doSQL & ":" & anonID &" result = " &
> playersCursor  -- shows the
> result (playersCursor value is always an integer)
> 
> The result (playersCursor) is ALWAYS an integer, no
> matter if there are any
> entries in the table or not, and no matter what
> value is given to the anonID
> variable.
> 
> I need to be able to determine if the record with
> the specified "playerID"
> exists or not.
> Does anyone have any idea where the problem is?
> Any help would be greatly appreciated.  Thanks.
> 

Hi Paul,

Let me assure you: nothing is wrong, and you're not
going crazy either :-)

A 'cursor' or 'record set' that you get from
revQueryDatabase is an integer number, if the query
was successful, and this number is nothing more than
an identifier that you can use for calls to
revMoveToNextRecord, revNumberOfRecords of
revDatabaseColumnCount.

In this case, if you want to know if a record exists,
use the revDataFromQuery function with a slightly
different SQL query.
##
put "SELECT COUNT(*) FROM players WHERE playerID=:1"
into doSQL
put "abc1234" into anonID
put revDataFromQuery(wgdbConID,doSQL,"anonID") into
tRecordCount
answer doSQL & ":" & anonID &" result = " &
tRecordCount
##

If the database can't find said record, the result of
the COUNT(*) function is 0 (zero), otherwise it's 1
(assuming that playerID is your primary, unique key
for that table)

HTH,

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)