Creating MySQL Databases

[Peter Reid]

Hi Mark

Thanks for the detailed response and explanation, it makes things a 
lot clearer.  However, I can't see how I can get non-query results 
back from the MySQL server.  In particular, I want to get a list of 
current databases, but when I use (from Sarah's suggestion):

   put "root" into dbUser
   put "mysql" into dbName
   put "localhost" into dbAddr
   put "" into dbPass  -- no password for root as yet

   put revOpenDatabase("MySQL",dbAddr,dbName,dbUser,dbPass) into dbresult
   if dbResult is not a number then
     answer error dbResult as sheet
     exit to top
   end if
   put dbResult into dbID

   put "SHOW DATABASES" into dbSQL
   revExecuteSQL dbID, dbSQL
   put the result into sqlResult

the result is empty even though there are 4 databases and I establish 
the connection on localhost using the root account. (I can confirm 
that the 4 databases exist and can be seen using root with no 
password by using phpMyAdmin).  I'm doing this early development on 
the same system as the MySQL server and so your comment about remote 
use of the root account isn't relevant as yet.

Cheers

Peter

>The client/server aspect of MySQL and similar databases is all
>encapsulated in the revOpenDatabase function - and there is no
>difference utilising the database functions whether you specify
>'localhost' as the address or an IP address of a remote machine - the
>underlying database libraries take care of all the nitty-gritty of
>sending commands and receiving data.
>
>The only requirement that Revolution puts upon you when connecting is
>that you have to connect to an existing database (you can't have a
>database-less connection) - but this isn't an issue because all MySQL
>databases have a 'mysql' database in which all the meta-data is stored.
>(Or you can just create a dummy database to connect to)
>
>Once you have connected, you can issue any SQL command you require -
>assuming the user you connected to the database with has appropriate
>privileges (MySQL has a reasonable fine-grained set of these).
>
>In particular, Sarah's suggestion is probably the best way to create
>databases - however, as I recall, the default setup of MySQL prohibits
>access to the root user from anywhere but 'localhost' - so you will
>probably want to set up another user, usable more widely and endowed
>with 'create database' permissions.
>
>The thing to remember about SQL is that it is 'closed' in the sense that
>everything that the databases allow you to do can be done in SQL and
>uses the standard database abstractions of cursors, tables and columns.
>
>For example, MySQL has a 'SHOW DATABASES' command that will return a
>table with one column ('Databases') containing the list of databases
>visible to the current user. Analogously, there is a 'SHOW TABLES'
>command that returns a list of tables in the current database.
>
>Similarly there are commands such as 'GRANT' and 'REVOKE' (with suitable
>unrememberable subsequent syntax) for granting and revoking access
>permissions for users.
>
>Warmest Regards,
>
>Mark.

-- 
Peter Reid
Reid-IT Limited, Loughborough, Leics., UK
E-mail: preid at reidit.co.uk