Creating MySQL Databases

Mark Waddingham 36degrees at runrev.com
Mon Mar 21 05:54:25 EST 2005


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

> Hi Sarah & Mark
> 
> Thank you both for your code fragments which are very helpful. 
> However, the problem I have is how to address the MySQL server on a 
> different system, not the one the Rev program is running on.
> 
> If my Rev program were running on the same system as the MySQL server 
> then I might send "mysql < createDB.sql" as a shell command. 
> However, the "mysql" command only exists on a different system on my 
> local network (IP 192.168.0.99, say) whilst I'm running the Rev 
> program on my local system (IP 192.168.0.3, say). So the problem is 
> that "mysql" is actually "/usr/local/mysql/bin/mysql" on 192.168.0.99 
> whilst "createDB.sql" is actually "/Users/fred/Documents/creatdb.sql" 
> on 192.168.0.3.  How do I construct the shell command in this 
> situation?
> 
> Just a thought here, but if my Rev program is actually on the server 
> system (in "/User/dbuser/Documents/" say) and the user executes it 
> directly from there whilst remote-connected to the system as 
> "dbuser", will this all work as if the MySQL server and the rev 
> program were all running locally?  (My understanding is that if you 
> execute a program on a remote system, then it's actually running in 
> the memory of your system, not the remote one??).
> 
> By the way, all this cross-system addressing messiness is hidden for 
> the SQL commands that Rev supports, which is why it would have been 
> nice if it supported database creation and status listing (so you can 
> list existing databases, etc.).  The main reason I'm using MySQL is 
> for a client-server application, which is why this sort of detail 
> would be better encapsulated within the Rev SQL database support!
> 
> Cheers
> Peter



More information about the use-livecode mailing list