Unix/Linux Go Back    

RedHat 9 (Linux i386) - man page for mysql (redhat section 3)

Linux & Unix Commands - Search Man Pages
Man Page or Keyword Search:   man
Select Man Page Set:       apropos Keyword Search (sections above)

Mysql(3)		       User Contributed Perl Documentation			 Mysql(3)

       Msql / Mysql - Perl interfaces to the mSQL and mysql databases

	 use Msql;

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


	 use Mysql;

	 $dbh = Mysql->connect(undef, $database, $user, $password);
	 $dbh = Mysql->connect($host, $database, $user, $password);


	 $dbh = Msql1->connect($host);
	 $dbh = Msql1->connect($host, $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;



	 @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 Msql-Mysql-modules 1.19_10 M(y)sqlPerl is no longer a separate module.  Instead it
       is emulated using the DBI drivers. You are strongly encouraged to implement new code with
       DBI directly. See "COMPATIBILITY NOTES" below.

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

       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);


	 $dbh = Mysql->connect($host, undef, $user, $password);
	 $dbh = Mysql->connect($host, $database, $user, $password);


	 $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 data-
       base 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.

	 $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 val-
       ues 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.


       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.

       Error messages

       Both drivers, Msql and Mysql implement a method ->errmsg(), which returns a textual error
       message. Mysql additionally supports a method ->errno returning the corresponding error

       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();
	   $errmsg = Mysql->errmsg();
	   $errmsg = Msql1->errmsg();

       The "-w" switch

       With Msql and Mysql the "-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

       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;

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

       returns the argument enclosed in single ticks ('') with any special character escaped
       according to the needs of the API.

       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 charac-

       NULL fields

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


       Now lets reconsider the above methods with regard to metadata.

       Database Handle

       As said above you get a database handle with the connect() method.  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;

       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.

       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 or
				    &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];

       New in mSQL 2.0

       The query() function in the API returns the number of rows affected by a 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 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 "listin-
       dex($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,

       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 which-
       ever you need. Check out what the "-I" switch in perl is for.

       Everything else seems to remain backwards compatible.


       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.

       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 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

	 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
	   INSERT INTO demo VALUES ('back\\slash',1)\g
	   INSERT INTO demo VALUES ('cr^Mcrnl

       Output of msql:

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

       Output of pmsql:

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

       Version information

       The version of Msql and Mysql is always stored in $Msql::VERSION or $Mysql::VERSION as it
       is perl standard.

       The mSQL API implements methods to access some internal configuration parameters: geth-
       ostinfo, 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 data-
       base handle.


       shutdown, createdb, dropdb, reloadacls are all accessible via a database handle and imple-
       ment the corresponding methods to what msqladmin does.

       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.


       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.

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

       MySQL is a libmysqlclient.a library written by Michael Widenius This was originally
       inspired by MySQL.

       M(y)sql used to be a separate module written in C. This is no longer the case, instead the
       old modules are emulated by their corresponding DBI drivers. I did my best to remove any
       incompatibilities, but the following problems are known to remain:

       Static methods
	   For whatever reason, mSQL implements some functions independent from the respective
	   database connection that really depend on it. This made it possible to implement




	   as static methods. This is no longer the case, it never was for MysqlPerl. Instead you
	   have to use




	   This variable used to turn off the printing of error messages. Unfortunately DBI uses
	   a completely different mechanism for that: The "PrintError" attribute of the database
	   and/or statement handles. We try to emulate the old behaviour by setting the "PrintEr-
	   ror" attribute to the current value of $M(Y)SQL::QUIET when a handle is created, that
	   is when M(y)sql->connect or $dbh->query() are called.

	   You can overwrite this by using something like

	       $dbh->{'dbh'}->{'PrintError'} = 1;


	       $sth->{'PrintError'} = 0;

       Andreas Koenig "koenig@franz.ww.TU-Berlin.DE" wrote the original MsqlPerl. Jochen Wiedmann
       "joe@ispsoft.de" wrote the M(y)sqlPerl emulation using DBI.

       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 sup-
       port and more functionality. Alligator maintains an interesting page on the DBI develop-


perl v5.8.0				    2002-11-18					 Mysql(3)
Unix & Linux Commands & Man Pages : ©2000 - 2018 Unix and Linux Forums

All times are GMT -4. The time now is 12:10 AM.