Партнёры:
Хостинг:
Msql / Mysql - Perl interfaces to the mSQL and mysql databases
use Msql;
$dbh = Msql->connect($host); $dbh = Msql->connect($host, $database);
or
use Mysql;
$dbh = Mysql->connect(undef, $database, $user, $password); $dbh = Mysql->connect($host, $database, $user, $password);
or
$dbh = Msql1->connect($host); $dbh = Msql1->connect($host, $database);
$dbh->selectdb($database);
@arr = $dbh->listdbs; @arr = $dbh->listtables;
$quoted_string = $dbh->quote($unquoted_string); $error_message = $dbh->errmsg; $error_number = $dbh->errno; # MySQL only
$sth = $dbh->listfields($table); $sth = $dbh->query($sql_statement);
@arr = $sth->fetchrow; # Array context $firstcol = $sth->fetchrow; # Scalar context @arr = $sth->fetchcol($col_number); %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
$rc = $dbh->shutdown(); $rc = $dbh->createdb($database); $rc = $dbh->dropdb($database);
As of March 1998, the Msql and Mysql modules are obsoleted by the DBI drivers DBD::mSQL and DBD::mysql, respectively. You are strongly encouraged to implement new code with the DBI drivers. In fact, Msql and Mysql are currently implemented as emulations on top of the DBI drivers.
Internally you are dealing with the two classes "Msql" and "Msql::Statement" or "Mysql" and "Mysql::Statement", respectively. 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 or Mysql. They offer you the connect command:
$dbh = Msql->connect($host); $dbh = Msql->connect($host, $database);
or
$dbh = Mysql->connect($host, undef, $user, $password); $dbh = Mysql->connect($host, $database, $user, $password);
or
$dbh = Msql1->connect($host); $dbh = Msql1->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, 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 or MySQL 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'';
@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. Be carefull with context here! In scalar context the method behaves different than expected and returns the first column:
$firstcol = $sth->fetchrow; # Scalar context!
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.
@arr = $sth->fetchcol($colnum);
returns an array of the values of each row for column $colnum. Note that this reads the entire table and leaves the row offset at the end of the table; be sure to use $sth->dataseek() to reset it if you want to re-examine the table.
$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).
Usually you do fetch error messages with
$errmsg = $dbh->errmsg();
In situations where a $dbh is not available (for example when connect() failed) you may instead do a
$errmsg = Msql->errmsg();
or
$errmsg = Mysql->errmsg();
or
$errmsg = Msql1->errmsg();
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.
By default errors are printed as warnings. You can suppress this behaviour by using the PrintError attribute of the respective handles:
$dbh->{'dbh'}->{'PrintError'} = 0;
For mSQL this means, any single tick within the string is escaped with a backslash and backslashes are doubled. Currently (as of msql-1.0.16) the API does not allow to insert NUL's (ASCII 0) into tables. The quote method does not fix this deficiency.
MySQL allows NUL's or any other kind of binary data in strings. Thus the quote method will additionally escape NUL's as \0.
If you pass undefined values to the quote method, it returns the string "NULL".
If a second parameter is passed to "quote", the result is truncated to that many characters.
You get at the three values with the methods
$scalar = $dbh->sock; $scalar = $dbh->host; $scalar = $dbh->database;
Mysql additionally supports
$scalar = $dbh->user; $scalar = $dbh->sockfd;
where the latter is the file descriptor of the socket used by the database connection. This is the same as $dbh->sock for mSQL.
$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 or
&Mysql::FIELD_TYPE_STRING,
&Mysql::FIELD_TYPE_LONG, ...
@arr = $sth->isnotnull; array of boolean
@arr = $sth->isprikey; array of boolean
@arr = $sth->isnum; array of boolean
@arr = $sth->length; array of the possibble maximum length of each
field in bytes
@arr = $sth->maxlength; array of the actual maximum length of each field
in bytes. Be careful when using this attribute
under MsqlPerl: The server doesn't offer this
attribute, thus it is calculated by fetching
all rows. This might take a long time and you
might need to call $sth->dataseek.
Mysql additionally supports
$scalar = $sth->affectedrows number of rows in database affected by query $scalar = $sth->insertid the unique id given to a auto_increment field. $string = $sth->info() more info from some queries (ALTER TABLE...) $arrref = $sth->isblob; array of boolean
The array methods (table, name, type, is_not_null, is_pri_key, length, affected_rows, is_num and blob) 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];
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 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. You can access their numeric value with 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 same time. 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 out what the "-I" switch in perl is for.
Everything else seems to remain backwards compatible.
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|
+----------------+------------+
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().
This situation is better with MySQL: The methods are valid only in connection with a database handle.
The mSQL and MySQL engines do not permit that these commands are invoked by users without sufficient privileges. So please make sure to check the return and error code when you issue one of them.
$rc = $dbh->shutdown();
$rc = $dbh->createdb($database);
$rc = $dbh->dropdb($database);
It should be noted that database deletion is not prompted for in any way. Nor is it undo-able from within Perl.
B<Once you issue the dropdb() method, the database will be gone!>
These methods should be used at your own risk.
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.
MySQL is a libmysqlclient.a library written by Michael Widenius This was originally inspired by MySQL.
Msql->errmsg
or
Msql->getserverinfo
as static methods. This is no longer the case, it never was for MysqlPerl. Instead you have to use
$dbh->errmsg
or
$dbh->getserverinfo
You can overwrite this by using something like
$dbh->{'dbh'}->{'PrintError'} = 1;
or
$sth->{'PrintError'} = 0;
http://www.symbolstone.org/technology/perl/DBI
|
Закладки на сайте Проследить за страницей |
Created 1996-2024 by Maxim Chirkov Добавить, Поддержать, Вебмастеру |