DATABASE FUNCTIONS

[Stephen Quinn Barncard]

Hi, Good to be back. I've been away from this forum for over a year.

I'm messing around with the DATABASE functions and MYSQL in the 
newest version of REV: 2.5. I've read most of the docs on the 
subject, and I've tried the feature earlier this year in my version 
of 2.2 - to a point.

I've previously created a working database which now interfaced on 
the web with PHP and Golive Dynamic techniques, which I learned 
recently. Permissions are all good, and COCOMYSQL and Navicat work 
perfectly with the database. (SQL has come a long way since I 
struggled with Butler on the Mac in the 90's.)

Database is bush_quotes with three fields: id (INT), quote (TEXT) and 
attributed (TEXT)

Creating a dynamic application was a breeze in Rev, except for a  few 
serious glitches which I'll describe later.

I set up the Database Query Builder (which is similar to Golive's 
Content source editor), then created fields and bound them to fields 
in the Query that I created earlier. Simple SELECT statement.

Everything just hooked up fine; real text appeared immediately after 
field creation , and first, last, next and previous buttons worked 
flawlessly and FAST.

Where I run into trouble is with UPDATING.

First of all, the changes don't usually work. Randomly they seem to 
happen, but I'm not sure what Rev is doing 'behind the scenes'.

scenario:

I use auto update, change a field and click in another field to update.

I go to previous record, then back to the one I changed. It appears 
that my change was not accepted. Or was it?

Then I use 'refresh' button. The cursor reverts to the first record.

Either I can see the update (if I then step up to the changed record) 
or usually I get an error dialog:

Update Failed on the SQL command: UPDATE bush_quotes SET quote =:1 
WHERE id = '000000000003' RevDB error:

I noted that there was no way to display the id in a field without 
REV changing the value from the database to a displayformat with 10 
leading zeroes, as shown above. I should be able to display the id 
field without the formatting, or at least have a choice of number 
formatting. Is this then passed back into the database as a TEXT 
string instead of an INTEGER?

QUESTIONS:

Would this mis-typing of variables be what is sending SQL into error?

If I want to do more things like searches, do I have to go to custom code?

how do I obtain the session id for the query?

How could I fix the formatting of a SQL field displayed in a field 
before it is displayed? I mean where would the script be? - I can 
handle the formatting.

thanks in advance!