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

[Paul Murray]

Jan,
Thanks very much for your help!

btw... got a syntax error on:
put revDataFromQuery(wgdbConID,doSQL,"anonID") into tRecordCount

so I checked the documentation, and although it implies the first two
parameters are optional,
I had to add a couple of leading commas to get it to work:
put revDataFromQuery(,,wgdbConID,doSQL,"anonID") into tRecordCount

Very minor compared to much research time you saved me!
Thanks again.
Paul

On Wed, Nov 19, 2008 at 4:23 AM, Jan Schenkel <janschenkel at yahoo.com> wrote:

> --- 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)
>
>
>
> _______________________________________________
> 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
>