MAN page from RedHat Other MsqlPerl-1.17-1.i386.rpm

LIB/MSQL

Section: User Contributed Perl Documentation (1)
Updated: perl 5.003 with
Index

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);          @arr = $dbh->listdbs;  @arr = $dbh->listtables;          $quoted_string = $dbh->quote($unquoted_string);  $error_message = $dbh->errmsg;

  $sth = $dbh->listfields($table);  $sth = $dbh->query($sql_statement);          @arr = $sth->fetchrow;  %hash = $sth->fetchhash;          $sth->dataseek($row_number);

  $sth->as_string;

  @indices = $sth->listindices                   # only in mSQL 2.0  @arr = $dbh->listindex($table,$index)          # only in mSQL 2.0  ($step,$value) = $dbh->getsequenceinfo($table) # only in mSQL 2.0

DESCRIPTION

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

Internally you are dealing with the two classes Msql andMsql::Statement. You will never see the latter, because you reachit through a statement handle returned by a query or a listfieldsstatement. The only class you name explicitly is Msql. It offers youthe 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 orwith an empty string as the first argument it connects to the UNIXsocket (usually /dev/msql), which has a much better performance thanthe TCP counterpart. A database name as the second argument selectsthe chosen database within the connection. The return value is adatabase handle if the connect succeeds, otherwise the return value isundef.

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 ifyou want to change the connection to a different database using adatabase handle you have got from a previous connect, then useselectdb.

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

These two work rather similar as descibed in the mSQL manual. Theyreturn a statement handle which lets you further explore what theserver has to tell you. On error the return value is undef. The objectreturned by listfields will not know about the size of the table, so anumrows() on it will return the string ``N/A'';

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

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

  @arr = $sth->fetchrow;

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

  %hash = $sth->fetchhash;

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

  $sth->dataseek($row_number);

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

No close statement

Whenever the scalar that holds a database or statement handle losesits value, Msql chooses the appropriate action (frees the result orcloses the database connection). So if you want to free the result orclose 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.

Error messages

A static method in the Msql class is ->errmsg(), which returns thecurrent value of the msqlErrMsg variable that is provided by the CAPI. There's also a global variable $Msql::db_errstr, which alwaysholds the last error message. The former is reset with the nextexecuted command, the latter not.

The -w switch

With Msql the -w switch is your friend! If you call your perlprogram with the -w switch you get the warnings from ->errmsg onSTDERR. This is a handy method to get the error messages from the msqlserver without coding it into your program.

If you want to know in greater detail what's going on, set theenvironment variables that are described in David's manual. David'sdebugging aid is excellent, there's nothing to be added.

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

->quote($str [, $length])

returns the argument enclosed in single ticks ('') with any specialcharacter escaped according to the needs of the API. Currently thismeans, any single tick within the string is escaped with a backslashand backslashes are doubled. Currently (as of msql-1.0.16) the APIdoes not allow to insert binary nulls into tables. The quote methoddoes not fix this deficiency.

If you pass undefined values to the quote method, it returns thestring NULL.

If a second parameter is passed to quote, the result is truncatedto that many characters.

NULL fields

NULL fields in tables are returned to perl as undefined values.

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 databaseit 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 oneargument.

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::CHAR_TYPE,                             &Msql::INT_TYPE, &Msql::REAL_TYPE,  @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 arrayreference (see the perlref manpage and the perlldsc manpage for details) when called ina scalar context. The scalar context is useful, if you need only thename 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];

New in mSQL 2.0

The query() function in the API returns the number of rows affected bya query. To cite the mSQL API manual, this means...

  If the return code is greater than 0, not only does it imply  success, it also indicates the number of rows "touched" by the query  (i.e. the number of rows returned by a SELECT, the number of rows  modified by an update, or the number of rows removed by a delete).

As we are returning a statement handle on selects, we can easily checkthe number of rows returned. For non-selects we behave just the sameas mSQL-2.

To find all indices associated with a table you can call thelistindices() method on a statement handle. To find out the columnsincluded 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 valuewith the these functions defined in the Msql package: IDENT_TYPE,NULL_TYPE, TEXT_TYPE, DATE_TYPE, UINT_TYPE, MONEY_TYPE, TIME_TYPE,IDX_TYPE, SYSVAR_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 at the sametime. So you may want to build two different Msql modules at a time,one for 1.0, another for 2.0, and load whichever you need. Check outwhat the -I switch in perl is for.

Everything else seems to remain backwards compatible.

@EXPORT

For historical reasons the constants CHAR_TYPE, INT_TYPE, andREAL_TYPE are in @EXPORT instead of @EXPORT_OK. This means, that youalways have them imported into your namespace. I consider it a bug,but not such a serious one, that I intend to break old programs bymoving them into EXPORT_OK.

Connecting to a different port

The mSQL API allows you to interface to a different port than thedefault that is compiled into your copy. To use this feature you haveto set the environment variable MSQL_TCP_PORT. You can do so at anytime in your program with the command

    $ENV{'MSQL_TCP_PORT'} = 1234; # or 1112 or 1113 or 4333 or 4334

Any subsequent connect() will establish a connection to the specifiedport.

For connect()s to the UNIX socket of the local machine useMSQL_UNIX_PORT instead.

Displaying whole tables in one go

A handy method to show the complete contents of a statement handle isthe as_string method. This works similar to the msql monitor with afew exceptions:

the width of a column: is calculated by examining the width of all entries in that column
control characters: are mapped into their backslashed octal representation
backslashes: are doubled (\\ instead of \)
numeric values: are adjusted right (both integer and floating point values)

The differences are illustrated by the following table:

Input to msql (a real carriage return here replaced with ^M):

    CREATE TABLE demo (      first_field CHAR(10),      second_field INT    ) \g

    INSERT INTO demo VALUES ('new    line',2)\g    INSERT INTO demo VALUES ('back\\slash',1)\g    INSERT INTO demo VALUES ('cr^Mcrnl    nl',3)\g

Output of msql:

     +-------------+--------------+     | first_field | second_field |     +-------------+--------------+     | new    line    | 2            |     | back\slash  | 1            |    crnlr    nl  | 3            |     +-------------+--------------+

Output of pmsql:

    +----------------+------------+    |first_field     |second_field|    +----------------+------------+    |new\012line     |           2|    |back\\slash     |           1|    |cr\015crnl\012nl|           3|    +----------------+------------+

Version information

The version of MsqlPerl is always stored in $Msql::VERSION as it isperl standard.

The mSQL API implements methods to access some internal configurationparameters: gethostinfo, getserverinfo, and getprotoinfo. All threeare available both as class methods or via a database handle. Butunder no circumstances they are associated with a database handle. Allthree return global variables that reflect the last connect()command within the current program. This means, that all three returnempty strings or zero before the first call to connect().

Administration

shutdown, createdb, dropdb, reloadacls are all accessible via adatabase handle and implement the corresponding methods to whatmsqladmin does.

The mSQL engine does not permit that these commands are invoked byother users than the administrator ofthe database. So please make sureto check the return and error code when you issue one of them.

StudlyCaps

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

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

PREREQUISITES

mSQL is a database server and an API library written by DavidHughes. To use the adaptor you definitely have to install these first.

AUTHOR

andreas koenig koenigAATTfranz.ww.TU-Berlin.DE

This document was created byman2html,using the manual pages.