use DBI;
$driver = "mSQL"; # or "mSQL1";
$dsn = "DBI:$driver:database=$database;host=$hostname";
$dbh = DBI->connect($dsn, undef, undef);
or
$driver = "mysql";
$dsn = "DBI:$driver:database=$database;$options";
$dbh = DBI->connect($dsn, $user, $password);
$drh = DBI->install_driver("mysql");
@databases = $drh->func($host, $port, '_ListDBs');
@tables = $dbh->func( '_ListTables' );
$sth = $dbh->prepare("SELECT * FROM foo WHERE bla");
or
$sth = $dbh->prepare("LISTFIELDS $table");
or
$sth = $dbh->prepare("LISTINDEX $table $index");
$sth->execute;
$numRows = $sth->rows;
$numFields = $sth->{'NUM_OF_FIELDS'};
$sth->finish;
$rc = $drh->func('createdb', $database, $host, $user, $password, 'admin');
$rc = $drh->func('dropdb', $database, $host, $user, $password, 'admin');
$rc = $drh->func('shutdown', $host, $user, $password, 'admin');
$rc = $drh->func('reload', $host, $user, $password, 'admin');
$rc = $dbh->func('createdb', $database, 'admin');
$rc = $dbh->func('dropdb', $database, 'admin');
$rc = $dbh->func('shutdown', 'admin');
$rc = $dbh->func('reload', 'admin');
use DBI;
$driver = "mSQL"; # or "mSQL1"
$dsn = "DBI:$driver:$database";
$dsn = "DBI:$driver:database=$database;$options";
$dbh = DBI->connect($dsn, undef, undef);
or
$dsn = "DBI:mysql:$database";
$dsn = "DBI:mysql:database=$database;$options";
$dbh = DBI->connect($dsn, $user, $password);
A database must always be specified.
Possible options are, separated by semicolon:
Should the mysql or mSQL daemon be running on a non-standard port number,
you may explicitly state the port number to connect to in the hostname
argument, by concatenating the hostname and port number together separated by a colon ( : ) character or by using the port argument. This doesn't work for mSQL 2: You have to create an alternative
config file and load it using the msql_configfile attribute, see below.
DBI->connect("DBI:mSQL:test;msql_configfile=msql_test.conf");
If the filename is not absolute, mSQL will search in certain other
locations, see the documentation of the msqlLoadConfigFile()
function in the mSQL manual for details.
$dsn = "DBI:mysql:test;mysql_read_default_file=/home/joe/my.cnf";
$dbh = DBI->connect($dsn, $user, $password)
The option mysql_read_default_group can be used to specify the default group in the config file: Usually this is the client group, but see the following example:
[perl]
host=perlhost
[client]
host=localhost
If you read this config file, then you'll be typically connected to localhost. However, by using
$dsn = "DBI:mysql:test;mysql_read_default_group=perl;"
. "mysql_read_default_file=/home/joe/my.cnf";
$dbh = DBI->connect($dsn, $user, $password);
you'll be connected to perlhost. See the (missing :-) documentation of the C function
mysql_options() for details.
mysql_socket=/dev/mysql
Usually there's no need for this option, unless you are using another location for the socket than that built into the client.
$drh = DBI->install_driver("~~");
@dbs = $drh->func("$hostname:$port", "_ListDBs");
@dbs = $drh->func($hostname, $port, "_ListDBs");
@dbs = $dbh->func('_ListDBs');
Returns a list of all databases managed by the mysql daemon or mSQL daemon
running on $hostname, port $port. This method is rarely needed for databases running on localhost: You should use the portable method
@dbs = DBI->data_sources("mysql");
or
@dbs = DBI->data_sources("mSQL");
whenever possible. It is a design problem of this method, that there's no
way of supplying a host name or port number to data_sources, that's the only reason why we still support ListDBs. :-(
@tables = $dbh->func('_ListTables');
Once connected to the desired database on the desired mysql or mSQL mSQL
daemon with the DBI-connect()> method, we may extract a list of the tables that
have been created within that database.
ListTables returns an array containing the names of all the tables present within the selected database. If no tables have been created, an empty list is returned.
@tables = $dbh->func( '_ListTables' );
foreach $table ( @tables ) {
print "Table: $table\n";
}
$sth = $dbh->prepare("LISTFIELDS $table");
$sth->execute;
See SQL EXTENSIONS below.
$rc = $drh->func("createdb", $dbname, [host, user, password,], 'admin');
$rc = $drh->func("dropdb", $dbname, [host, user, password,], 'admin');
$rc = $drh->func("shutdown", [host, user, password,], 'admin');
$rc = $drh->func("reload", [host, user, password,], 'admin');
or
$rc = $dbh->func("createdb", $dbname, 'admin');
$rc = $dbh->func("dropdb", $dbname, 'admin');
$rc = $dbh->func("shutdown", 'admin');
$rc = $dbh->func("reload", 'admin');
For server administration you need a server connection. For obtaining this connection you have two options: Either use a driver handle (drh) and supply the appropriate arguments (host, defaults localhost, user, defaults to '' and password, defaults to ''). A driver handle can be obtained with
$drh = DBI->install_driver('mSQL');
Otherwise reuse the existing connection of a database handle (dbh).
There's only one function available for administrative purposes, comparable
to the m(y)sqladmin programs. The command being execute
depends on the first argument:
It should be noted that database deletion is not prompted for in any way. Nor is it undo-able from DBI.
Once you issue the dropDB() method, the database will be gone!
These method should be used at your own risk.
$rc = $drh->func( $database, '_CreateDB' );
$rc = $drh->func( $database, '_DropDB' );
or
$rc = $drh->func( $host, $database, '_CreateDB' );
$rc = $drh->func( $host, $database, '_DropDB' );
These methods are equivalent to the admin method with ``createdb'' or ``dropdb'' commands, respectively. In particular note the warnings concerning the missing prompt for dropping a database!
$infoString = $dbh->{'info'};
$threadId = $dbh->{'thread_id'};
These correspond to mysql_info() and
mysql_tread_id(), respectively.
my $numFields = $sth->{'NUM_OF_FIELDS'};
Note, that most attributes are valid only after a successfull execute. An undef value will returned in that case. The most important exception is the mysql_use_result attribute: This forces the driver to use mysql_use_result rather than
mysql_store_result. The former is faster and less memory consuming, but
tends to block other processes. (That's why mysql_store_result is the
default.)
To set the mysql_use_result attribute, use either of the following:
my $sth = $dbh->prepare("QUERY", { "mysql_use_result" => 1});
or
my $sth = $dbh->prepare("QUERY");
$sth->{"mysql_use_result"} = 1;
Of course it doesn't make sense to set this attribute before calling the
execute method.
Column dependent attributes, for example NAME, the column names, are returned as a reference to an array. The array indices are corresponding to the indices of the arrays returned by fetchrow and similar methods. For example the following code will print a header of table names together with all rows:
my $sth = $dbh->prepare("SELECT * FROM $table");
if (!$sth) {
die "Error:" . $dbh->errstr . "\n";
}
if (!$sth->execute) {
die "Error:" . $sth->errstr . "\n";
}
my $names = $sth->{'NAME'};
my $numFields = $sth->{'NUM_OF_FIELDS'};
for (my $i = 0; $i < $numFields; $i++) {
printf("%s%s", $$names[$i], $i ? "," : "");
}
print "\n";
while (my $ref = $sth->fetchrow_arrayref) {
for (my $i = 0; $i < $numFields; $i++) {
printf("%s%s", $$ref[$i], $i ? "," : "");
}
print "\n";
}
For portable applications you should restrict yourself to attributes with capitalized or mixed case names. Lower case attribute names are private to DBD::mSQL and DBD::mysql. The attribute list includes:
$index of table $table. See the docs of msqlListIndex for
details.
Certain attributes methods have been declared obsolete or deprecated, partially because there names are agains DBI's naming conventions, partially because they are just superfluous or obsoleted by other methods.
Obsoleted attributes and methods will be explicitly listed below. You cannot expect them to work in future versions, but they have not yet been scheduled for removal and currently they should be usable without any code modifications.
Deprecated attributes and methods will currently issue a warning unless you set the variable $DBD::mSQL::QUIET to a true value. This will be the same for Msql-Mysql-modules 1.19xx and 1.20xx. They will be silently removed in 1.21xx.
Here is a list of obsoleted attributes and/or methods:
$drh->func("createdb", $dbname, $host, "admin")
$drh->func("dropdb", $dbname, $host, "admin")
$sth = $dbh->prepare("LISTFIELDS $table")
$sth->execute;
$numRows = $sth->rows;
The obvious questions is: Are the C libraries thread safe? In the case of mSQL the answer is definitely ``no''. The C library has a concept of one single active connection at a time and that is not what threads like.
In the case of MySQL the answer is ``mostly'' and, in theory, you should be able to get a ``yes'', if the C library is compiled for being thread safe (By default it isn't.) by passing the option -with-thread-safe-client to configure. See the section on How to make a threadsafe client in the manual.
Msql-2.0.4 and 2.0.4.1 contain a bug that makes ORDER BY and hence the test
script t/40bindparam fail. To verify, if this is the case for you, do a
cd Msql
perl -w -I../blib/lib -I../blib/arch t/40bindparam.t
If something is wrong, the script ought to print a number of id's and names. If the id's aren't in order, it is likely, that your mSQL has a bug. See the INSTALL file for a patch.
This document is Copyright (c)1997 Alligator Descartes. All rights reserved. Permission to distribute this document, in full or in part, via email, Usenet, ftp archives or http is granted providing that no charges are involved, reasonable attempt is made to use the most current version and all credits and copyright notices are retained ( the AUTHOR and COPYRIGHT sections ). Requests for other distribution rights, including incorporation into commercial products, such as books, magazine articles or CD-ROMs should be made to Alligator Descartes <[email protected]>.
[email protected]
To subscribe to this list, send a mail with the words
subscribe msql-mysql-modules
or
subscribe msql-mysql-modules-digest
in the first line of the body to [email protected]. A mailing list archive is in preparation.
Additionally you might try the dbi-user mailing list for questions about DBI and its modules in general. Subscribe via
http://www.fugue.com/dbi
Mailing list archives are at
http://www.rosat.mpe-garching.mpg.de/mailing-lists/PerlDB-Interest/
http://outside.organic.com/mail-archives/dbi-users/
http://www.coe.missouri.edu/~faq/lists/dbi.html
http://www.arcana.co.uk/technologia/perl/DBI
where documentation, pointers to the mailing lists and mailing list archives and pointers to the most current versions of the modules can be used.
Information on the DBI interface itself can be gained by typing:
perldoc DBI
right now!