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:
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.
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.
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. The object returned by listfields will not know about the size of the table, so a numrows() on it will return the string ``N/A'';
An array is returned that contains the requested names without any further information.
returns an array of the values of the next row fetched from the server. Similar does
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.
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).
-w
switch-w
switch is your friend! If you call your perl
program with the -w
switch you get the warnings from ->errmsg 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 supressed.
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
database returns undef, if you have connected without or with only one argument.
$sth knows about all metadata that are provided by the API:
The six last methods return an array in array context and an array reference (see the perlref manpage and the perlldsc manpage for details) when called in a scalar context. The scalar context is useful, if you need only the name of one column, e.g.
which is equivalent to
As we are returning a statement handle on selects, we can easily check the number of rows returned. For non-selects we behave just the same as mSQL-2.
To find all indices associated with a table you can call the
listindices()
method on a statement handle. To find out the columns
included in an index, you can call the listindex($table,$index)
method on a database handle.
There are a few new column types in mSQL 2. Access their numeric value with the functions Msql::IDENT_TYPE, Msql::IDX_TYPE, and Msql::TEXT_TYPE.
You cannot talk to a 1.0 server with a 2.0 client.
You cannot link to a 1.0 library and to a 2.0 library I Everything else seems to remain backwards compatible.
Any subsequent connect() will establish a connection to the specified
port.
For connect()s to the UNIX socket of the local machine use
MSQL_UNIX_PORT instead.
Output of msql:
Output of pmsql:
The mSQL API implements methods to access some internal configuration
parameters: gethostinfo, getserverinfo, and getprotoinfo. All three
are available both as class methods or via a database handle. But
under no circumstances they are associated with a database handle. All
three return global variables that reflect the last connect()
command within the current program. This means, that all three return
empty strings or zero before the first call to connect().
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.
@EXPORT
For historical reasons the constants CHAR_TYPE, INT_TYPE, and
REAL_TYPE are in @EXPORT instead of @EXPORT_OK. This means, that you
always have them imported into your namespace. I consider it a bug,
but not such a serious one, that I intend to break old programs by
moving them into EXPORT_OK.
Connecting to a different port
The mSQL API allows you to interface to a different port than the
default that is compiled into your copy. To use this feature you have
to set the environment variable MSQL_TCP_PORT. You can do so at any
time in your program with the command
Displaying whole tables in one go
A handy method to show the complete contents of a statement handle is
the as_string method. This works similar to the msql monitor with a
few exceptions:
The differences are illustrated by the following table:.
Input to msql (a real carriage return here replaced with ^M):
\\ instead of \
)
Version information
The version of MsqlPerl is always stored in $Msql::VERSION as it is
perl standard.
Administration
shutdown, creatdb, dropdb, reloadacls are all accessible via a
database handle and implement the corresponding methods to what
msqladmin does.
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.
PREREQUISITES
mSQL is a database server and an API library written by David
Hughes. To use the adaptor you definitely have to install these 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
(DBD::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/