A few strange behaviors accessing DBs

[Pete]

Thought I would pass on a few weirdnesses I've come across recently while
accessing databases from LC.  Some of these are outside the day-to-day db
access sphere but may be of interest to anyone developing db tools with LC.
 I came across all these while accessing SQLite databases.

The revDatabaseTableNames function returns a list of all the table names in
a database.  Unfortunately, it also includes the names of any views defined
in the database with no indication as to which are tables and which are
views.  IMHO, this is incorrect, especially since the documentation is
silent on it.  If I ask for table names, I want table names not view names.
 I've written my own handler to return a list of table names to get round
this issue.

Next issue concerns the use of the special column name "rowid".  Calling
revQueryDatabase with a SELECT statement that includes rowid as a column to
be returned creates a db cursor, no problem.  However, if I then call
revDatabaseColumnNames to get a list of the columns in the cursor, rowid is
not in the list.  Instead, it is replaced by the name of the primary key
field of the table being queried.  This in turn causes an error to be
returned if I call revDatabaseColumnNamed, requesting the value of the
rowid column.  "rowid" is sometimes a synonym for the primary key column of
a table but not always.  Even when it is a synonym, I don't believe LC
should return a column name that wasn't in the SELECT statement that
created the cursor.

The final problem involves the use of revDatabaseColumnNamed, which returns
the value of a named column from the current row in a cursor.  The
dictionary says you can include a variable or an array element  parameter
in the call to this function and the returned value will be placed into the
specified variable/array element.  What it doesn't mention is that the
variable/array must already exist in order for this to work.  If it
doesn't, the variable/array is not created and no error is reported.  Not a
big deal as long as you know about it.  Second problem concerns the use of
multilevel array keys.  Assuming myArray exists and I specify
"myArray[1][id]" as the destination for the returned data, what ends up in
myArray is a key of "1][id" with the returned value.  Arrays with a single
level of keys work fine.

As mentioned, these are pretty esoteric situations but wanted to pass them
on in the hope that it might save someone some debugging time.


-- 
Pete
Molly's Revenge <http://www.mollysrevenge.com>