MySQL DBE Driver

DOCUMENTATION

• NAME
• SYNOPSIS
• EXAMPLE
• DESCRIPTION
• Connection Parameters
• Connection Attributes
• Large Objects
• Reading from LOB and TEXT fields
• Writing to LOB and TEXT fields by using a LOB stream
• Writing to LOB and TEXT fields by reading from IO handles
• Writing to LOB and TEXT fields by reading from a class
• LIMITATIONS
• KNOWN ERRORS
• AUTHORS
• COPYRIGHT

NAME

SYNOPSIS

  use DBE;
  
  $dsn = "Driver=MySQL;Host=localhost;User=root;Db=test;Charset=utf8";
  $con = DBE->connect( $dsn );
  
  $res = $con->query( 'SELECT * FROM table' );
  
  $con->prepare( 'SELECT * FROM table' );
  $res = $con->execute();

EXAMPLE

  use DBE;
  
  # make a connection
  $con = DBE->connect(
      'driver' => 'MySQL',
      'socket' => '/tmp/mysql.sock',
      'user' => 'db_user',
      'password' => 'db_pass',
      'db' => 'test',
      'charset' => 'utf8',
      'reconnect' => 1,
  );
  
  # create a table
  $con->do(
      qq/
      CREATE TABLE CFG_Config (
          CFG_Index INT PRIMARY KEY auto_increment,
          CFG_Name VARCHAR(64),
          CFG_Value VARCHAR(64),
          UNIQUE (CFG_Name)
      )
      /
  );
  
  # insert an entry
  $con->do( "INSERT INTO CFG_Config (CFG_Name,CFG_Value) VALUES('foo','bar')" );
  # or with placeholders
  # $con->do(
  #     "INSERT INTO CFG_Config (CFG_Name,CFG_Value) VALUES(?,?)",
  #     'foo', 'bar'
  # );
  
  # select the entry
  $res = $con->query(
      "SELECT CFG_Value FROM CFG_Config WHERE CFG_Name = 'foo'"
  );
  # or with placeholders
  # $res = $con->query(
  #     "SELECT CFG_Value FROM CFG_Config WHERE CFG_Name = ?",
  #     'foo'
  # );
  
  ($foo) = $res->fetch_row();
  print "\$foo = '$foo'\n";
  
  # output:
  # $foo = 'bar'

DESCRIPTION

Connection Parameters

  use DBE;
  
  DBE->connect( 'Provider=MySQL;Database=test;User=root;Password=' );

• DRIVER | PROVIDER [MySQL]
  Driver must be "MySQL".

• DB | DATABASE [dbname]
  Name of the database to connect to.

• HOST [hostname]
  HOST may be either a hostname or an IP address. If host is empty or the string "localhost", a connection to the local host is assumed: For Windows, the client connects using a shared-memory connection, if the server has shared-memory connections enabled. Otherwise, TCP/IP is used. For Unix, the client connects using a Unix socket file.

• USER | UID [user]
  Username for authentication.

• AUTH | PWD | PASSWORD [pwd]
  Password for authentication.

• SOCKET [path]
  Specifies the socket or named pipe that should be used. Note that the HOST parameter determines the type of the connection.

• COMPRESS [1|0|Yes|No|True|False]
  Use compression protocol. Defaults to FALSE.

• INTERACTIVE [1|0|Yes|No|True|False]
  Allow interactive_timeout seconds (instead of wait_timeout seconds) of inactivity before closing the connection. The client session wait_timeout variable is set to the value of the session interactive_timeout variable.

• IGNORE_SPACE | IGNORESPACE [1|0|Yes|No|True|False]
  Allow spaces after function names. Makes all functions names reserved words.

• LOCAL_FILES | LOCALFILES [1|0|Yes|No|True|False]
  Enable LOAD DATA LOCAL handling.

• CONNECT_TIMEOUT | CONNECTTIMEOUT [int]
  Connect timeout in seconds.

• FOUND_ROWS | FOUNDROWS [1|0|Yes|No|True|False]
  Use found rows instead of affected rows. Defaults to False.

• MULTI_STATEMENTS | MULTISTATEMENTS [1|0|Yes|No|True|False]
  Tell the server that the client may send multiple statements in a single string (separated by ";"). Defaults to False.

• READ_DEFAULT_FILE | READDEFAULTFILE [path]
  Read options from the named option file instead of from my.cnf.

• READ_DEFAULT_GROUP | READDEFAULTGROUP [name]
  Read options from the named group from my.cnf or the file specified with READ_DEFAULT_FILE.

• USE_SSL | USESSL [1|0|Yes|No|True|False]
  Use SSL (encrypted protocol). Defaults to No.

• SSL_KEY | SSLKEY [path]
  The pathname to the key file.

• SSL_CERT | SSLCERT [path]
  The pathname to the certificate file.

• SSL_CA | SSLCA [path]
  The pathname to the certificate authority file.

• SSL_CAPATH | SSLCAPATH [path]
  The pathname to a directory that contains trusted SSL CA certificates in pem format.

• SSL_CIPHER | SSLCIPHER [string]
  A list of allowable ciphers to use for SSL encryption.

Connection Attributes

• CHARSET | CHARACTERSET [string]
  A connection character set.

• LOB_MAX_SIZE | LOBMAXSIZE [int]
  The maximum size of data that will be fetched directly from large objects. Objects larger then LOB_MAX_SIZE are returned as DBE LOB class when using statements and LOB_TRUNCATE is disabled. In other case large objects are silently truncated. Default value is 4000 bytes.

• LOB_TRUNCATE | LOBTRUNCATE [1|0|Yes|No|True|False]
  On TRUE value large objects are truncated at LOB_MAX_SIZE. On FALSE value and while using statements objects larger then LOB_MAX_SIZE are returned as DBE LOB class, which is the default setting.

• LOB_BUFFER_SIZE | LOBBUFFERSIZE [int]
  The buffer size in bytes when writing to LOBs from a PerlIO handle or a class. Default value is 4096 bytes.

Large Objects

Reading from LOB and TEXT fields

  $stmt = $con->prepare( "SELECT lob FROM table" );
  $res = $stmt->execute();
  
  while( ($lob) = $res->fetchrow_array )
  {
      if( ref( $lob ) )
      {
          print "lob size is ", $lob->size, " bytes\n";
          while( $got = $lob->read( $buf, 1024 ) )
          {
              print "lob read $got bytes\n";
          }
          $lob->close; # close the LOB handle
      }
      else
      {
          # LOB data can be accessed directly.
          print "lob size is ", length( $lob ), " bytes\n";
      }
  }
  
  # close the statement
  $stmt->close;

Writing to LOB and TEXT fields by using a LOB stream

  $stmt = $con->prepare( 'INSERT INTO table (lob) VALUES(?)' );
  # bind parameter 1 as binary type without data
  $stmt->bind_param( 1, '', 'b' );
  # open a lob stream for parameter 1
  $lob = $stmt->lob_open_write( 1 );
  # write data to the lob
  $lob->write( 'writing data to the lob....' );
  # close the lob stream
  $lob->close;
  # execute the statement
  $stmt->execute();

Writing to LOB and TEXT fields by reading from IO handles

  $stmt = $con->prepare( 'INSERT INTO table (lob) VALUES(?)' );
  # open a file
  open $fh, '< lob.file';
  # bind a Perl IO handle to the lob parameter
  # use 'b' for binary data and 's' for text.
  $stmt->bind_param( 1, $fh, 'b' );
  # execute the statement and write the contents of 'lob.file' to MySQL
  $stmt->execute();
  # close the file
  close $fh;

Writing to LOB and TEXT fields by reading from a class

  package MyInput;
  
  sub new {
      my $pkg = shift;
      my $this = {
          'counter' => 0,
          'max' => shift || 1,
      };
      bless $this, $pkg;
  }
  
  # the class must implement a READ method, like a tied HANDLE does
  
  sub READ {
      my ($this, undef, $length) = @_;
      # read up to 'max' times
      $this->{'counter'} ++ == $this->{'max'} and return -1; # EOF
      # we need to write directly into the second parameter ($_[1])
      $_[1] = 'TEST';
      # was LOB_BUFFER_SIZE set to less then 4 bytes?
      $length < 4 and $_[1] = substr( $_[1], 0, $length );
      # return the length of bytes read
      return length( $_[1] );
  }
  
  package main;
  
  $stmt = $con->prepare( 'INSERT INTO table (lob) VALUES(?)' );
  # make the class
  $in = MyInput->new( 3 );
  # bind the class to parameter 1
  $stmt->bind_param( 1, $in, 'b' );
  # execute the statement and read from our class
  $stmt->execute();
  # destroy the class
  undef $in;

LIMITATIONS

KNOWN ERRORS

Write LOB failed: Can`t send long data for non-string/non-binary data types

Either you tried to send long data on non-string/non-binary type, or you didn`t bind the parameter.

Solution: Bind the parameter as string or binary type before sending long data.

  $stmt = $con->prepare( 'INSERT INTO table (text_column) VALUES(?)' );
  
  $lob = $stmt->lob_open_write( 1 );
  $lob->write( 'text value' ); # error
  
  # bind the parameter as string
  $stmt->bind_param( 1, '', 's' );
  $lob = $stmt->lob_open_write( 1 );
  $lob->write( 'text value' ); # ok

Execute failed: Can`t execute statement while another statement is active on the server

MySQL allows only one executed statement per connection when the results are not buffered on the client. Client buffering is disabled when statements return LOB or TEXT fields with a create size that is larger then LOB_MAX_SIZE.

Solution: Close the unbuffered statement before executing another statement.

  $stmt = $con->prepare( 'SELECT lob FROM table' );
  $res = $stmt->execute;
  while( $res->fetch_row( \%row ) ) {
      # process results
  }
  
  # close the statement to prevent errors
  $stmt->close;
  
  $stmt2 = $con->prepare( 'SELECT ...' );
  $res = $stmt2->execute; # ok

Prepare failed: Commands out of sync; you can`t run this command now

Since MySQL allows only one statement with unbuffered results per connection, you cannot prepare another statement until all rows where fetched from the unbuffered statement or it has been closed.

Solution: Fetch all rows from the unbuffered statement or close it before preparing another statement.

  $stmt = $con->prepare( 'SELECT lob FROM table' );
  $res = $stmt->execute;
  
  # close the statement to prevent errors
  $stmt->close;
  
  $stmt = $con->prepare( 'SELECT ...' ); # ok

Prepare failed: Can`t use row limit together with unbuffered statements

Unbuffered statements can not be used together with row limit because MySQL does not allow "SELECT FOUND_ROWS()" to determine the number of rows found.

Solution: Set LOB_MAX_SIZE to the create size of the largest field in the result set in the statement, or avoid using statements.

AUTHORS

COPYRIGHT