/ 
MsqlPerl-1.08
/ Msql

NAME

Msql - Perl interface to the mSQL database

SYNOPSIS

  use Msql;
	
  $dbh = Msql->connect;
  $dbh = Msql->connect($host);
  $dbh = Msql->connect($host, $database);
	
  $dbh->selectdb($database);
	
  $sth = $dbh->listfields($table);
  $sth = $dbh->query($sql_statement);
	
  @arr = $dbh->listdbs;
  @arr = $dbh->listtables;
	
  @arr = $sth->fetchrow;
  %hash = $sth->fetchhash;
	
  $sth->dataseek($row_number);

DESCRIPTION

This package is designed as close as possible to its C API counterpart. The manual that comes with mSQL describes most things you need. Due to popular demand it was decided though, that this interface does not use StudlyCaps (see below).

Internally you are dealing with the two classes Msql and Msql::Statement. You will never see the latter, because you reach it through a statement handle returned by a Query or a ListFields statement. The only class you name explicitly is Msql. It offers you the connect command:

$dbh = Msql->connect;
$dbh = Msql->connect($host);
$dbh = Msql->connect($host, $database);

This connects you with the desired host/database. With no argument or with an empty string as the first argument it connects to the UNIX socket (usually /dev/msql), which has a much better performance than the TCP counterpart. A database name as the second argument selects the chosen database within the connection. The return value is a database handle if the connect succeeds, otherwise the return value is undef.

You will need this handle to gain further access to the database.

$dbh->selectdb($database);

If you have not chosen a database with the connect command, or if you want to change the connection to a different database using a database handle you have got from a previous connect, then use selectdb.

$sth = $dbh->listfields($table);
$sth = $dbh->query($sql_statement);

These two work rather similar as descibed in the mSQL manual. They return a statement handle which lets you further explore what the server has to tell you. On error the return value is undef.

@arr = $dbh->listdbs();
@arr = $dbh->listtables;

An array is returned that contains the requested names without any further information.

@arr = $sth->fetchrow;

returns an array of the values of the next row fetched from the server. Similar does

%hash = $sth->fetchhash;

return a complete hash. The keys in this hash are the column names of the table, the values are the table values. Be aware, that when you have a table with two identical column names, you will not be able to use this method without trashing one column. In such a case, you should use the fetchrow method.

$sth->dataseek($row_number);

lets you specify a certain offset of the data associated with the statement handle. The next fetchrow will then return the appropriate row (first row being 0).

No close statement

Whenever the scalar that holds a database or statement handle loses its value, Msql chooses the appropriate action (frees the result or closes the database connection). So if you want to free the result or close the connection, choose to do one of the following:

undef the handle
use the handle for another purpose
let the handle run out of scope
exit the program.

Metadata

Now lets reconsider the above methods with regard to metadata.

Database Handle

As said above you get a database handle with

$dbh = Msql->connect($host, $database);

The database handle knows about the socket, the host, and the database it is connected to.

You get at the three values with the methods

$scalar = $dbh->sock;
$scalar = $dbh->host;
$scalar = $dbh->database;

database returns undef, if you have connected without or with only one argument.

Statement Handle

Two constructor methods return a statement handle:

$sth = $dbh->listfields($table);
$sth = $dbh->query($sql_statement);

$sth knows about all metadata that are provided by the API:

  $scalar = $sth->numrows;    
  $scalar = $sth->numfields;  

  @arr  = $sth->table;       the names of the tables of each column
  @arr  = $sth->name;        the names of the columns
  @arr  = $sth->type;        the type of each column, defined in msql.h
	                     and accessible via &Msql::chartype,
	                     &Msql::inttype, &Msql::realtype,
  @arr  = $sth->isnotnull;   array of boolean
  @arr  = $sth->isprikey;    array of boolean
  @arr  = $sth->length;      array of the length of each field in bytes

The six last methods return an array in array context and an array reference (see perlref and perlldsc for details) when called in a scalar context. The scalar context is useful, if you need only the name of one column, e.g.

$name_of_third_column = $sth->name->[2]

which is equivalent to

@all_column_names = $sth->name;
$name_of_third_column = $all_column_names[2];

The -w switch

With Msql the -w switch is your friend! If you call your perl program with the -w switch you get the warnings that normally are stored in $Msql::db_errstr on STDERR. This is a handy method to get the error messages from the msql server without coding it into your program. If you want to know in greater detail what's going on, set the environment variables that are described in David's manual. David's debugging aid is excellent, there's nothing to be added.

If you want to use the -w switch but do not want to see the error messages from the msql daemon, you can set the variable $Msql::QUIET to some true value, and they will be suppressed.

StudlyCaps

Real Perl Programmers (C) usually don't like to type ListTables but prefer list_tables or listtables. The mSQL API uses StudlyCaps everywhere and so did early versions of MsqlPerl. Beginning with $VERSION 1.06 all methods are internally in lowercase, but may be written however you please. Case is ignored and you may use the underline to improve readability.

The price for using different method names is neglectible. Any method name you use that can be transformed into a known one, will only be defined once within a program and will remain an alias until the program terminates. So feel free to run fetch_row or connecT or ListDBs as in your old programs. These, of course, will continue to work.

PREREQUISITES

mSQL is a libmsql.a library written by David Hughes "URL: mailto:bambi@Bond.edu.au". You get that at "/Bond.edu.au/pub/Minerva/msql" in URL: ftp:.

To use the adaptor you definitely have to install this library first.

AUTHOR

andreas koenig koenig@franz.ww.TU-Berlin.DE

SEE ALSO

Alligator Descartes wrote a database driver for Tim Bunce's DBI. I recommend anybody to carefully watch the development of this module (DBI::mSQL). Msql is a simple, stable, and fast module, and it will be supported for a long time. But it's a dead end. I expect in the medium term, that the DBI efforts result in a richer module family with better support and more functionality. Alligator maintains an interesting page on the DBI development: http://www.hermetica.com/