SUMMARY:
use Mysql; $dbh = Mysql->Connect; $dbh = Mysql->Connect($host); $dbh = Mysql->Connect($host,$database); $dbh = Mysql->Connect($host,$database,$password); $dbh = Mysql->Connect($host,$database,$password,$user); $dbh->SelectDB($database); $sth = $dbh->ListFields($table); @arr = @{$sth->name}; @arr = @{$sth->length}; $value = $sth->numfields; @arr = @{$sth->type}; @arr = @{$sth->is_num}; @arr = @{$sth->is_blob}; @arr = @{$sth->is_not_null}; $sth = $dbh->Query($sql_statement); @arr = $dbh->ListDBs; @arr = $dbh->ListTables; @arr = $sth->FetchRow; %hash = FetchHash $sth; $sth->DataSeek($row_number); $scalar = $dbh->sock; $scalar = $dbh->host; $scalar = $dbh->database; $scalar = $dbh->quote($binary_string);
This package is designed to be as close as possible to its C API counterpart.
Internally you are dealing with the two classes Mysql
and
Mysql::Statement
. You will never see the latter, as you reach
it through a statement handle returned by a Query or a ListFields
statement. The only class you name explicitly is Mysql. It offers you
the Connect command.
Because of limitations in how Perl deals with numeric values you will have problems using numbers larger than the size of a signed LONG. (2147483647). This can occur when using MySQL's unsigned LONG (DOUBLE) or LONGLONG (BIGINT) data types. Perl stores returned values as strings, but will automatically convert them to numbers when you use the values in a numeric context. This will cause them to be "truncated" to 2147483647 since Perl uses a signed LONG value to store such numbers.
You can use the following work arounds. First of all, always treat values that may be large as strings. As long as you do this they can be displayed and re-inserted into a database without incident. The same goes for inserting new values into tables. If you set a variable called $tmpvar to be equal to "4147483647" and then INSERT it into your database you should be fine. Note that the ""'s are important here, since they force Perl to treat the value as a string.
If you need to do calculations involving large numbers you should do them via the initial SELECT.
$dbh = Mysql->Connect;
$dbh = Mysql->Connect($host);
$dbh = Mysql->Connect($host,$database);
$dbh = Mysql->Connect($host,$database,$password);
$dbh = Mysql->Connect($host,$database,$password,$user);
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 /dev/mysql, which is a big performance gain. A database name in 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. If you are using a mysqld compiled with MIT threads you will be unable to use sockets. In this case specify your systems hostname for the host variable.
You may also optionally provide a username and password. If no user name is provided then the current login will be used. If no password is provided the connection will fail if the user has a password.
You will need this handle to gain further access to the
database. You may issue multiple Connect
statements, but
be sure to use different variable names ($dbh1,$dbh2, $dbh3, etc.)
You can find out what socket, host and database a given handle is associated with by using doing the following.
$scalar = $dbh->sock;
$scalar = $dbh->host;
$scalar = $dbh->database;
The database will be undef if you specified only a host during the Connect.
$ENV{"MYSQL_TCP_PORT"}=3334;
$ENV{"MYSQL_UNIX_PORT"}="/tmp/mysql_new.sock";
use Mysql;
$dbh = Mysql->Connect($host);
SYNOPSIS:
$dbh->SelectDB($database);
DESCRIPTION:
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 acquired from a previous Connect
,
then use SelectDB.
SYNOPSIS:
$sth = ListFields $dbh $table;
The MySQL ListFields does not work quite the same as its mSQL counterpart. In MySQL you use the following commands to get information after making a ListFields call.
You must have made a successful call to ListFields before using the following functions.
@arr = @{$sth->name};
| Returns a array of the column names |
@arr = @{$sth->length};
| Returns a array of column lengths |
$value = $sth->numfields;
| Returns number of columns in table |
@arr = @{$sth->type};
| Array of MySQL types |
@arr = @{$sth->is_num};
| Array of 0 and 1 where 1 indicates that the column is numerical |
@arr = @{$sth->is_blob};
| Array of 0 and 1 where 1 indicates that the column is a blob |
@arr = @{$sth->is_not_null};
| Array of 0 and 1 where 1 indicates that the column is NOT NULL |
SYNOPSIS:
$sth = $dbh->Query($sql_statement);
$sth = $dbh->Query("SELECT * FROM Widget_Table WHERE widget_id = 1") or die $Mysql::db_errstr;
while(@record = $sth->FetchRow) {
$foo = $record[0]; $bar = $record[1];
}
It's a good idea to always check for errors. In the example above this is done by the "or die..." portion of the Query line.
SYNOPSIS:
@arr = $dbh->ListDBs;
@dbs = $dbh->ListDBs; # Returns an array of DB names
$count = $#dbs; # Figure out how many elements.
for($i=0;$i<=$count;$i++) {
print(" ".$dbs[$i]."\n");
}
SYNOPSIS:
@arr = $dbh->ListTables;
@tables = $dbh->ListTables
; # Assumes that $dbh points # to a valid database$count = $#tables;
# Figure out how many elements.for($i=0;$i<=$count;$i++) {
# Print out the table names.print(" ".$tables[$i]."\n");
}
SYNOPSIS:
@arr = $sth->FetchCol($col);
Note that this function will reset the current row pointer to the end of the table; you'll need to use DataSeek(0) to reset it.
SYNOPSIS:
%hash = $sth->FetchHash;
SYNOPSIS:
@arr = $sth->FetchRow;
SYNOPSIS:
DataSeek $sth $row_number;
$scaler = $dbh->quote($binary_string)
This will escape all ' and \ characters and convert \0 and \n's as well.
$dbh = Connect Mysql $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 with no arguments, or with only one argument.
$sth = ListFields $dbh $table;
$sth = Query $dbh $sql_statement;
$sth knows about all metadata that are provided by the API:
$scalar = $sth->affected_rows; |
Number of inserted/changed rows. |
$scalar = $sth->info; |
Report stats information on previous ALTER TABLE or LOAD DATA FROM INFILE query. |
$arrref = $sth->is_blob; |
An array of boolean specifying whether a given field is a BLOB. |
$arrref = $sth->is_not_null; |
An array of boolean values specifying whether a given field is NULL or not. |
$arrref = $sth->is_pri_key; |
An array of boolean specifying whether a given field is a primary key. |
$arrref = $sth->is_num; |
An array of boolean specifying whether a given field is a number. |
$scalar = $sth->insert_id; |
Value assigned to column with AUTO_INCREMENT on last INSERT. |
$arrref = $sth->length; |
An array of the length of each field in bytes. |
$arrref = $sth->name; |
The names of the columns. |
$scalar = $sth->numrows; |
Number of rows returned. |
$scalar = $sth->numfields; |
Number of fields returned. |
$arrref = $sth->table; |
The names of the tables of each column. |
$arrref = $sth->type; |
The type of each column, defined in mysql.h.
and accessible via &Mysql::CHAR_TYPE,
&Mysql::INT_TYPE, &Mysql::REAL_TYPE |
-w
switchIf you want to use the -w switch but do not want to see the error messages from the MySQL daemon, you can set the variable $Mysql::QUIET to some non zero value, and the error messages will be suppressed.
To use the adaptor you have to install this library first.