arghh. Neither postgres nor mySQL fully supported, even in 7.

[Mark Waddingham]

> Livecode can't handle  multi-line mySQL transactions in a single 
> livecode
> transaction!  You need to send the commands individually, and then 
> commit
> (A non-starter for me, as the enemy is network latency, not bandwidth 
> or
> load on the SQL server).

The current revDB API isn't really set up to handle the way MySQL 
implements multiple statements per query - however, we could make it 
work like Postgres... i.e. return either the last result set, or an 
error if one of the steps failed.

> Whereas this is not a problem in postgres--but livecode hasn't 
> implemented
> ssl for postgres (which I suspect is only a few lines of code).

Indeed, it might well be only a 'few lines of code', but the number of 
lines of code is never representative of the amount of work it might 
take to write them in the first place and ensure everything they need to 
work is in place.

> So until I have my server written, access will remain only through VPN 
> . . .

To be fair, your server solution is a much better idea both 
architecturally and security-wise.

 From a security perspective you really don't want to have a way for a 
client to send arbitrary SQL commands against your database - that's a 
huge surface area for attack. Architecturally speaking, it means you 
have no overall control over what the clients can and can't do, and also 
ties the front-end implementation (the client) to a specific design of 
database.

The custom server-based approach you are clearly adopting means that you 
can raise the level of the protocol with which the clients talk to the 
server. Rather than allowing the clients to send arbitrary SQL commands 
you would be better to enumerate the operations the client needs to be 
able to do (parameterized as appropriate) and wrap those up in specific 
actions the server accepts.

For example, let's say your client needs to fetch a list of documents 
available to it keyed on (say) an author field. Then the client request 
could be:

   fetchDocumentsWithAuthor theAuthor

(so it just sends 'fetchDocumentsWithAuthor,theAuthor' to the server)

And then the server would create the query and execute it against the 
database.

This approach separates the front-end view (the client), from the 
back-end service and data storage (your custom server and SQL database) 
- it simplifies the design of both by ensuring a consistent and easily 
maintainable interface between the two concerns.

Mark.

-- 
Mark Waddingham ~ mark at livecode.com ~ http://www.livecode.com/
LiveCode: Everyone can create apps