A few strange behaviors accessing DBs

[Mark Schonewille]

Hi Pete,

I believe all those separate database-related functions are just confusing and shouldn't exist in the first place. One simple command to execute all MySQL commands should me sufficient. There is no reason why there would be any distinction between revExecuteSQL and revDatabaseQuery for example.

I do all my SQL in PHP and connect from LiveCode to PHP. Saves me quite a few headaches.

--
Best regards,

Mark Schonewille

Economy-x-Talk Consulting and Software Engineering
Homepage: http://economy-x-talk.com
Twitter: http://twitter.com/xtalkprogrammer
KvK: 50277553

Download the Installer Maker Plugin 1.7 for LiveCode here http://qery.us/za

On 26 jan 2012, at 19:01, Pete wrote:

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