DBE - Database (Express) Engine for Perl

The DBE module permits a secure and fast way to access database systems. DBE was developed for high performance and low usage of resources. The chart below illustrates the functionality of DBE.

DOCUMENTATION

• NAME
• SYNOPSIS
• DESCRIPTION
• Uniform SQL
• Parameter Names in Placeholders for All
• Functions by Category
• Examples
• Simple Query
• Query with statements
• METHODS
• Connection Control Methods
• Command Execution Methods
• Retrieving Query Result Information
• Accessing Result Data
• Accessing Fields in a Result Set
• Transaction Methods
• Simple Data Fetching
• Information and Catalog Functions
• Other Functions
• Large Objects
• THREADSAFETY
• AUTHORS
• COPYRIGHT

NAME

SYNOPSIS

  use DBE;
  
  $con = DBE->connect( %arg );
  $con = DBE->connect( $dsn );
  
  $res = $con->query( $sql );
  $res = $con->query( $sql, @bind_values );
  
  $rv = $con->do( $sql );
  $rv = $con->do( $sql, @bind_values );
  
  $con->prepare( $sql );
  $res = $con->execute( ... );
  
  $stmt = $con->prepare( $sql );
  $stmt->bind_param( $p_num, $value );
  $stmt->bind_param( $p_num, $value, $type );
  
  $res = $stmt->execute();
  $res = $stmt->execute( @bind_values );
  
  @row = $res->fetch_row();
  $res->fetch_row( \@row );
  $res->fetch_row( \%row );
  
  @row = $res->fetchrow_array();
  $row = $res->fetchrow_arrayref();
  $row = $res->fetchrow_hashref();
  
  $array = $res->fetchall_arrayref();
  $hash = $res->fetchall_hashref( $key );
  
  @name = $res->fetch_names();
  $num_rows = $res->num_rows();
  $num_fields = $res->num_fields();
  
  $rv = $con->auto_commit( $mode );
  $rv = $con->begin_work();
  $rv = $con->commit();
  $rv = $con->rollback();
  
  $str   = $con->error();
  $errno = $con->errno();
  
  $rv = $con->set_charset( $charset );
  $charset = $con->get_charset();
  
  $quoted = $db->quote( $arg );
  $quoted = $db->quote_id( ... );

DESCRIPTION

Uniform SQL

  # uniform
  $con->query("% SELECT * FROM [table@db] WHERE [field] = '1'");
  # native
  $con->query("SELECT * FROM `db`.`table` WHERE `field` = '1'");

  # uniform
  $con->query("% SELECT * FROM [schema.table] AS [t1] WHERE [t1.id] = 1");
  # native
  $con->query('SELECT * FROM "schema"."table" AS "t1" WHERE "t1"."id" = 1');

  # uniform
  $con->query('% SELECT * FROM [table#csv] WHERE [table.field] = 1');
  # native
  $con->query('SELECT * FROM "table.csv" WHERE "table"."field" = 1');

Parameter Names in Placeholders for All

  # prepare, execute
  $stmt = $con->prepare( '% SELECT * FROM [table] WHERE [id] > :id' );
  $stmt->bind_param( ':id', 1000, 'i' );
  $res = $stmt->execute();
  
  # or as query
  $res = $con->query(
      '% SELECT * FROM [table] WHERE [id] > :id',
      { 'id' => 1000 }
  );

Functions by Category

affected_rows, bind_param, connect, disconnect, do, execute, get_charset, insert_id, param_count, prepare, query, set_charset

bind_column, dump, fetch, fetch_col, fetch_row, fetchall_arrayref, fetchall_hashref, fetchrow_arrayref, fetchrow_hashref, num_rows, row_seek, row_tell

fetch_field, fetch_names, field_seek, field_tell, names, num_fields

auto_commit, begin_work, commit, rollback

selectall_arrayref, selectall_hashref, selectrow_array, selectrow_arrayref, selectrow_hashref

columns, data_sources, foreign_keys, getinfo, primary_keys, installed_drivers, open_drivers, special_columns, tables, type_info

attr_get, attr_set, driver_has, escape_pattern, name_convert, row_limit, quote, quote_id, ping

errno, error, set_error_handler, trace

close, eof, getc, read, print, seek, size, tell, write

Examples

Simple Query

  use DBE;
  
  $con = DBE->connect(
      'provider' => 'MySQL',
      'socket' => '/tmp/mysql.sock',
      'user' => 'root',
      'auth' => '',
      'db' => 'test',
      'charset' => 'utf8',
  );
  
  $res = $con->query( "SELECT * FROM table WHERE field = 'foo'" );
  
  print join( '|', $res->fetch_names() ), "\n";
  while( @row = $res->fetch_row() ) {
      print join( '|', @row ), "\n";
  }

Query with statements

  use DBE;
  
  $dsn = "Provider=MySQL;Host=localhost;User=root;Password=;Database=test";
  $con = DBE->connect( $dsn );
  $con->set_charset( 'utf8' );
  
  $stmt = $con->prepare( 'SELECT * FROM table WHERE field = ?' );
  # bind "foo" to parameter 1 and execute
  $res = $stmt->execute( 'foo' );
  
  print join( '|', $res->fetch_names() ), "\n";
  while( $row = $res->fetchrow_arrayref() ) {
      print join( '|', @$row ), "\n";
  }

METHODS

Connection Control Methods

$con = DBE -> connect ( %hash )

$con = DBE -> connect ( $dsn )

Opens a connection to a database.

Parameters

%hash

Following parameters are support by DBE:

• PROVIDER | DRIVER [string]
  Name of provider to connect with. Defaults to "Text".

• WARN [1|0|Yes|No|True|False]
  Warn on error. Defaults to $^W.

• CROAK [1|0|Yes|No|True|False]
  Croak on error. Defaults to TRUE.

• RECONNECT [int]
  Number of attempts to reconnect with a delay of 1 sec. Default value "0" disables reconnection.

  # parameters as hash
  $con = DBE->connect(
      'provider' => 'Text',
      'dbq' => '/path/to/csv-files/',
  );
  
  # parameters as dsn
  $dsn = 'Provider=Text;DBQ=.;Format="Delimited(;)";QuoteBy=""""';
  $con = DBE->connect( $dsn );

  # global error
  $con = DBE->connect( ... )
      or die 'Connect failed (' . DBE->errno . ') ' . DBE->error;
  
  # connection error
  $con->query( ... )
      or die 'Query failed (' . $con->errno . ') ' . $con->error;
  

  # global error
  $con = DBE->connect( ... )
      or die 'Connect failed ' . DBE->error;
  
  # connection error
  $con->query( ... )
      or die 'Query failed ' . $con->error;
  

$code

The error code.

$msg

The error message.

$provider

The description string of the provider.

$action

Performed action during the error occurred.

$obj_id

Either a value of "con", "res" or "stmt".

$obj

Depending on $obj_id, it can be a connection object, a result object or a statement object.

  $con = DBE->connect ('Provider=Text');
  
  # set error handler to a function
  $con->set_error_handler (\&error_handler);
  
  # raise a syntax error
  $con->do ("SELECT * FROM");
  
  sub error_handler {
      # does allmost the same like internal croak
      my ($code, $msg, $provider, $action, $obj_id, $obj) = @_;
      my ($pkg, $file, $line) = caller ();
      print STDERR "[$provider] $action: $msg at $file line $line\n";
      exit;
  }

  $con = DBE->connect ('Provider=Text');
  
  $obj = MY->new ();
  
  # set error handler to an object
  $con->set_error_handler ('error_handler', $obj);
  # or
  # $con->set_error_handler ('MY::error_handler', $obj);
  # or
  # $con->set_error_handler (\&MY::error_handler, $obj);
  
  # raise a syntax error
  $con->do ("SELECT * FROM");
  
  1;
  
  package MY;
  
  require Carp;
  
  sub new {
      bless {}, shift;
  }
  
  sub error_handler {
      # does allmost the same like internal croak
      my ($this, $code, $msg, $provider, $action, $obj_id, $obj) = @_;
      &Carp::croak ("[$provider] $action: $msg");
  }

  0 - DBE_TRACE_NONE - disable tracing
  1 - DBE_TRACE_SQL - trace sql statements only
  2 - DBE_TRACE_SQLFULL - trace sql statements inlcuding bind
      and execute calls
  3 - DBE_TRACE_ALL - trace all messages

  # global tracing of all messages
  DBE->trace( 3 );
  
  # trace into a file
  open( $fh, "> trace.log" ) or die "can't open file: $!";
  DBE->trace( 3, $fh );
  
  # just trace the sql statements within a connection object
  $con->trace( 1 );
  
  # disable global tracing
  DBE->trace( 0 );

Command Execution Methods

$res = $con -> query ( $statement )

$res = $con -> query ( $statement, @bind_values )

$res = $con -> do ( $statement )

$res = $con -> do ( $statement, @bind_values )

Sends a SQL statement to the currently active database on the server. do() is a synonym for query().

Parameters

$statement

This parameter can include one or more parameter markers in the SQL statement by embedding question mark (?) characters at the appropriate positions.

@bind_values

An array of values to bind to the statement. Values bound in this way are usually treated as "string" types unless the driver can determine the correct type, or unless bind_param() has already been used to specify the type.

  $stmt = $con->prepare( 'UPDATE table WHERE field = ?' );
  # bind 'foo' to 'field' as type 'string'
  $stmt->bind_param( 1, 'foo', 's' );
  # execute
  $res = $stmt->execute();

  $con->prepare( 'UPDATE table WHERE field = ?' );
  # bind 'foo' to 'field' as type 'string'
  $con->bind_param( 1, 'foo', 's' );
  # execute
  $res = $con->execute();

  Character Description
  ---------------------
  i   corresponding value has type integer 
  d   corresponding value has type double 
  s   corresponding value has type string 
  b   corresponding value has type binary 

  $stmt = $con->prepare(
      'UPDATE table WHERE field1 = ? AND field2 = ?' );
  # bind '100' to 'field1' as type 'integer'
  $stmt->bind_param( 1, 100, 'i' );
  # bind 'foo' to 'field2'
  $stmt->bind_param( 2, 'foo' );

  $stmt = $con->prepare( 'UPDATE table WHERE field = ?' );
  # bind 'foo' to 'field' and execute
  $res = $stmt->execute( 'foo' );

Retrieving Query Result Information

$con -> affected_rows ()

Gets the number of affected rows in a previous SQL operation After executing a statement with query() or execute(), returns the number of rows changed (for UPDATE), deleted (for DELETE), or inserted (for INSERT). For SELECT statements, affected_rows() works like num_rows().

Return Values

An integer greater than zero indicates the number of rows affected or retrieved. Zero indicates that no records where updated for an UPDATE statement, no rows matched the WHERE clause in the query or that no query has yet been executed.

$con -> insert_id ()

$con -> insert_id ( $catalog )

$con -> insert_id ( $catalog, $schema )

$con -> insert_id ( $catalog, $schema, $table )

$con -> insert_id ( $catalog, $schema, $table, $column )

Returns the auto generated id used in the last query or statement.

Parameters

$catalog

The catalog where the table is located.

$schema

The schema where the table is located.

$table

The table where the column is located.

$column

The column to retrieve the generated id from.

Accessing Result Data

$res -> dump ( [$maxlen [, $lsep [, $fsep [, $fh [, $header]]]]] )

Fetches all the rows from the result set and prints the results to a handle. NULL fields are printed as undef. Fields with utf8 data are enclosed in double quotes. Other fields are enclosed in single quotes.

Parameters

$maxlen

The maximum length of a field value. Defaults to 35

$lsep

Line separator, printed after each row. Defaults to "\n"

$fsep

Field separator. Defaults to ", "

$fh

File handle to print the results there. Defaults to STDOUT

$header

Print the column names on the first line. Defaults to TRUE.

  $res = $con->query( "SELECT prename, name FROM person" );
  
  $res->fetch_row( \%row );
  print "First person is $row{'prename'} $row{'name'}\n";
  
  $res->fetch_row( \@row );
  print "Second person is $row[0] $row[1]\n";
  
  @row = $res->fetch_row;
  print "Third person is $row[0] $row[1]\n";

  $res = $con->query( "SELECT name FROM color" );
  
  @colors = $res->fetch_col();
      or
  $res->fetch_col( \@colors );
  
  print "Available colors: ", join( ', ', @colors ), "\n";

  $res = $con->query( "SELECT id, name FROM color" );
  $res->fetch_col( \%color );
  
  $res = $con->query( "SELECT color_id, description FROM articles" );
  $res->bind( $color_id, $desc );
  while( $res->fetch ) {
      print "Article $desc has color ", $color{$color_id}, "\n";
  }

  $res = $con->query( "SELECT name, age FROM person" );
  
  $a_per = $res->fetchall_arrayref();
  foreach $row( @$a_per ) {
      print $row->[0], " is ", $row->[1], " years old\n";
  }
  
  # fetch as hashref
  $a_per = $res->fetchall_arrayref( {} );
  foreach $row( @$a_per ) {
      print $row->{'name'}, " is ", $row->{'age'}, " years old\n";
  }

  $res = $con->query( 'SELECT ID, Name FROM Table' );
  $data = $res->fetchall_hashref( 'ID' );
  # print name of ID = 2
  print $data->{2}->{'Name'};
  
  $res = $con->query( 'SELECT ID1, ID2, Name FROM Table' );
  $data = $res->fetchall_hashref( 'ID1', 'ID2' );
  # print name of ID1 = 2 and ID2 = 10
  print $data->{2}->{10}->{'Name'};

  my ($article_id, $article_name);
  $res = $con->query( "SELECT ID, Name FROM Article" );
  $res->bind_column( 1, $article_id );
  $res->bind_column( 2, $article_name );
  
  while( $res->fetch ) {
      print "article $article_id: $article_name\n";
  }

  my ($prename, $name, $age);
  $res = $con->query( "SELECT Prename, Name, Age FROM Person" );
  $res->bind( $prename, $name, $age );
  while( $res->fetch ) {
      print "$name, $prename is $age years old\n";
  }

  my %row;
  $res = $con->query( "SELECT Prename, Name, Age FROM Person" );
  $res->bind( @row{$res->names('lc')} );
  while( $res->fetch ) {
      print "$row{'name'}, $row{'prename'} is $row{'age'} years old\n";
  }

Accessing Fields in a Result Set

$res -> names ()

$res -> names ( $name )

$res -> fetch_names ()

$res -> fetch_names ( $name )

Returns an array of field names representing in a result set. names is a synonym for fetch_names.

Paramters

$name

Name conversation. A value of "lc" convert names to lowercase and a value of "uc" convert names to uppercase.

Transaction Methods

$con -> auto_commit ( $mode )

Turns on or off auto-commit mode on queries for the database connection.

Parameters

$mode

Whether to turn on auto-commit or not.

Simple Data Fetching

$con -> selectrow_array ( $statement )

$con -> selectrow_array ( $statement, @bind_values )

Combines various operations into a single call and returns an array with values of the first row, or undef on error. See fetchrow_array() for further information.

$con -> selectrow_arrayref ( $statement )

$con -> selectrow_arrayref ( $statement, @bind_values )

Combines various operations into a single call and returns a reference to an array with values of the first row, or undef on error. See fetchrow_arrayref() for further information.

$con -> selectrow_hashref ( $statement )

$con -> selectrow_hashref ( $statement, @bind_values )

Combines various operations into a single call and returns a reference to a hash with names and values of the first row, or undef on error. See fetchrow_hashref() for further information.

$db -> selectall_arrayref ( $statement )

$db -> selectall_arrayref ( $statement, {} )

$db -> selectall_arrayref ( $statement, undef, @bind_values )

$db -> selectall_arrayref ( $statement, {}, @bind_values )

Combines various operations into a single call. See fetchall_hashref() for a description. Returns undef on error.

$db -> selectall_hashref ( $statement, $key )

$db -> selectall_hashref ( $statement, $key, @bind_values )

Combines various operations into a single call. See fetchall_hashref() for a description. Returns undef on error.

$con -> select_col ( $statement, @bind_values )

$con -> select_col ( $statement, [$num], @bind_values )

$con -> select_col ( $statement, [\@col], @bind_values )

$con -> select_col ( $statement, [\@col, $num], @bind_values )

$con -> select_col ( $statement, [\%col], @bind_values )

$con -> select_col ( $statement, [\%col, $num_key, $num_val], @bind_values )

Combines various operations into a single call and returns an array with values of the first row, or undef on error. The @bind_values are optional. See fetch_col() for further information.

Information and Catalog Functions

DBE -> open_drivers ()

Returns a hash of open (loaded) drivers. The key field in the hash contains the name of the driver and the value field contains a description of the driver.

Example

  %drivers = DBE->open_drivers();

DBE -> installed_drivers ()

Returns a hash of installed drivers. The key field in the hash contains the name of the driver and the value field contains a description of the driver.

Example

  %drivers = DBE->installed_drivers();

$con -> data_sources ()

Returns a list of data sources (databases) available.

$con -> getinfo ( $id )

Returns general information, (including supported data conversions) about the DBMS that the application is currently connected to.

Example

  $pattern_escape = $con->getinfo( 14 );

$con -> type_info ()

$con -> type_info ( $sql_type )

Returns a result set that can be used to fetch information about the data types that are supported by the DBMS.

Parameters

$sql_type

The SQL data type being queried as string or number. Supported types are: (numeric values stay in parenthesis)

• SQL_ALL_TYPES (0)
• SQL_BIGINT (-5)
• SQL_BINARY (-2)
• SQL_VARBINARY (-3)
• SQL_CHAR (1)
• SQL_DATE (9)
• SQL_DECIMAL (3)
• SQL_DOUBLE (8)
• SQL_FLOAT (6)
• SQL_INTEGER (4)
• SQL_NUMERIC (2)
• SQL_REAL (7)
• SQL_SMALLINT (5)
• SQL_TIME (10)
• SQL_TIMESTAMP (11)
• SQL_VARCHAR (12)

1. TYPE_NAME
   String representation of the SQL data type name.

2. DATA_TYPE
   SQL data type code.

3. COLUMN_SIZE
   If the data type is a character or binary string, then this column contains the maximum length in bytes.
   For date, time, timestamp data types, this is the total number of characters required to display the value when converted to character.
   For numeric data types, this is the total number of digits.

4. LITERAL_PREFIX
   Character or characters used to prefix a literal; for example, a single quotation mark (') for character data types or 0x for binary data types; undef is returned for data types where a literal prefix is not applicable.

5. LITERAL_SUFFIX
   Character or characters used to terminate a literal; for example, a single quotation mark (') for character data types; undef is returned for data types where a literal suffix is not applicable.

6. CREATE_PARAMS
   A list of keywords, separated by commas, corresponding to each parameter that the application may specify in parentheses when using the name that is returned in the TYPE_NAME field. The keywords in the list can be any of the following: length, precision, or scale. They appear in the order that the syntax requires them to be used. For example, CREATE_PARAMS for DECIMAL would be "precision,scale"; CREATE_PARAMS for VARCHAR would equal "length." undef is returned if there are no parameters for the data type definition; for example, INTEGER.

7. NULLABLE
   Whether the data type accepts a NULL value:
   0 - if the data type does not accept NULL values.
   1 - if the data type accepts NULL values.
   2 - if it is not known whether the column accepts NULL values.

8. CASE_SENSITIVE
   Indicates whether the data type can be treated as case sensitive for collation purposes; valid values are TRUE and FALSE.

9. SEARCHABLE
   Indicates how the data type is used in a WHERE clause. Valid values are:

   SQL_UNSEARCHABLE (0)

   The data type cannot be used in a WHERE clause.

   SQL_LIKE_ONLY (1)

   The data type can be used in a WHERE clause only with the LIKE predicate.

   SQL_ALL_EXCEPT_LIKE (2)

   The data type can be used in a WHERE clause with all comparison operators except LIKE.

   SQL_SEARCHABLE (3)

   The data type can be used in a WHERE clause with any comparison operator.

10. UNSIGNED_ATTRIBUTE
    Indicates where the data type is unsigned. The valid values are: TRUE, FALSE or undef. A undef indicator is returned if this attribute is not applicable to the data type.

11. FIXED_PREC_SCALE
    Contains a TRUE value if the data type is exact numeric and always has the same precision and scale; otherwise, it contains FALSE.

12. AUTO_UNIQUE_VALUE
    Contains TRUE if a column of this data type is automatically set to a unique value when a row is inserted; otherwise, contains FALSE.

13. LOCAL_TYPE_NAME
    Localized version of the data source–dependent name of the data type. undef is returned if a localized name is not supported by the data source. This name is intended for display only.

14. MINIMUM_SCALE
    The minimum scale of the SQL data type. If a data type has a fixed scale, the MINIMUM_SCALE and MAXIMUM_SCALE columns both contain the same value. undef is returned where scale is not applicable.

15. MAXIMUM_SCALE
    The maximum scale of the SQL data type. undef is returned where scale is not applicable. If the maximum scale is not defined separately in the DBMS, but is defined instead to be the same as the maximum length of the column, then this column contains the same value as the COLUMN_SIZE column.

16. SQL_DATA_TYPE
    The value of the SQL data type as it appears in the SQL_DESC_TYPE field of the descriptor. This column is the same as the DATA_TYPE column, except for interval and datetime data types.
    For interval and datetime data types, the SQL_DATA_TYPE field in the result set will return SQL_INTERVAL or SQL_DATETIME, and the SQL_DATETIME_SUB field will return the subcode for the specific interval or datetime data type.

17. SQL_DATETIME_SUB
    When the value of SQL_DATA_TYPE is SQL_DATETIME or SQL_INTERVAL, this column contains the datetime/interval subcode. For data types other than datetime and interval, this field is undef.
    For interval or datetime data types, the SQL_DATA_TYPE field in the result set will return SQL_INTERVAL or SQL_DATETIME, and the SQL_DATETIME_SUB field will return the subcode for the specific interval or datetime data type.

18. NUM_PREC_RADIX
    If the data type is an approximate numeric type, this column contains the value 2 to indicate that COLUMN_SIZE specifies a number of bits. For exact numeric types, this column contains the value 10 to indicate that COLUMN_SIZE specifies a number of decimal digits. Otherwise, this column is NULL.

19. INTERVAL_PRECISION
    If the data type is an interval data type, then this column contains the value of the interval leading precision. Otherwise, this column is NULL.

1. TABLE_CAT
   The catalog identifier.

2. TABLE_SCHEM
   The name of the schema containing TABLE_NAME.

3. TABLE_NAME
   The name of the table, or view, or alias, or synonym.

4. TABLE_TYPE
   Identifies the type given by the name in the TABLE_NAME column. It can have the string values 'TABLE', 'VIEW', 'BASE TABLE', or 'SYSTEM TABLE'.

5. REMARKS
   Contains the descriptive information about the table.

1. TABLE_CAT
   The catalog identifier.

2. TABLE_SCHEM
   The name of the schema containing TABLE_NAME.

3. TABLE_NAME
   The name of the table or view.

4. COLUMN_NAME
   Column identifier. Name of the column of the specified table or view.

5. DATA_TYPE
   Identifies the SQL data type of the column.

6. TYPE_NAME
   String representing the name of the data type corresponding to DATA_TYPE.

7. COLUMN_SIZE
   Defines the maximum length in characters for character data types, the number of digits for numeric data types or the length in the representation of temporal types.

8. BUFFER_LENGTH
   The length in bytes of transferred data.

9. DECIMAL_DIGITS
   The total number of significant digits to the right of the decimal point.

10. NUM_PREC_RADIX
    The radix for numeric precision. The value is 10 or 2 for numeric data types and NULL (undef) if not applicable.

11. NULLABLE
    FALSE if the column does not accept NULL values, or TRUE if the column accepts NULL values, or undef if unknown.

12. REMARKS
    May contain descriptive information about the column.

13. COLUMN_DEF
    The default value of the column. The value in this column should be interpreted as a string if it is enclosed in quotation marks.

14. SQL_DATA_TYPE
    This column is the same as the DATA_TYPE column, except for datetime and interval data types.

15. SQL_DATETIME_SUB
    The subtype code for datetime and interval data types. For other data types, this column returns a NULL.

16. CHAR_OCTET_LENGTH
    The maximum length in bytes of a character or binary data type column. For all other data types, this column returns a NULL.

17. ORDINAL_POSITION
    The ordinal position of the column in the table. The first column in the table is number 1.

18. IS_NULLABLE
    "NO" if the column does not include NULLs.
    "YES" if the column could include NULLs.
    This column returns a zero-length string if nullability is unknown.

1. TABLE_CAT
   The catalog identifier.

2. TABLE_SCHEM
   The name of the schema containing TABLE_NAME.

3. TABLE_NAME
   Name of the specified table.

4. COLUMN_NAME
   Primary Key column name.

5. KEY_SEQ
   Column sequence number in the primary key, starting with 1.

6. PK_NAME
   Primary key identifier. NULL if not applicable to the data source.

1. PKTABLE_CAT
   Primary key table catalog name; NULL if not applicable to the data source.

2. PKTABLE_SCHEM
   Primary key table schema name; NULL if not applicable to the data source.

3. PKTABLE_NAME
   Primary key table name.

4. PKCOLUMN_NAME
   Primary key column name. The driver returns an empty string for a column that does not have a name.

5. FKTABLE_CAT
   Foreign key table catalog name; NULL if not applicable to the data source.

6. FKTABLE_SCHEM
   Foreign key table schema name; NULL if not applicable to the data source.

7. FKTABLE_NAME
   Foreign key table name.

8. FKCOLUMN_NAME
   Foreign key column name. The driver returns an empty string for a column that does not have a name.

9. KEY_SEQ
   The ordinal position of the column in the key, starting at 1.

10. UPDATE_RULE
    Action to be applied to the foreign key when the SQL operation is UPDATE:

      0  -  CASCADE
      1  -  RESTRICT
      2  -  SET NULL
      3  -  NO ACTION
      4  -  SET DEFAULT
    

11. DELETE_RULE
    Action to be applied to the foreign key when the SQL operation is DELETE:

      0  -  CASCADE
      1  -  RESTRICT
      2  -  SET NULL
      3  -  NO ACTION
      4  -  SET DEFAULT
    

12. FK_NAME
    Foreign key name. NULL if not applicable to the data source.

13. PK_NAME
    Primary key name. NULL if not applicable to the data source.

1. TABLE_CAT
   Catalog name of the table to which the statistic or index applies; NULL if not applicable to the data source.

2. TABLE_SCHEM
   Schema name of the table to which the statistic or index applies; NULL if not applicable to the data source.

3. TABLE_NAME
   Table name of the table to which the statistic or index applies.

4. NON_UNIQUE
   Indicates whether the index does not allow duplicate values:
   TRUE if the index values can be nonunique.
   FALSE if the index values must be unique.
   NULL is returned if TYPE is 'table'.

5. INDEX_QUALIFIER
   The identifier that is used to qualify the index name doing a DROP INDEX; NULL is returned if an index qualifier is not supported by the data source or if TYPE is 'table'.

6. INDEX_NAME
   Index name; NULL is returned if TYPE is 'table'.

7. TYPE
   Type of information being returned:

     'table'      indicates a statistic for the table (in the CARDINALITY
                  or PAGES column). 
     'btree'      indicates a B-Tree index.
     'clustered'  indicates a clustered index. 
     'content'    indicates a content index.
     'hashed'     indicates a hashed index.
     'other'      indicates another type of index.
   

8. ORDINAL_POSITION
   Column sequence number in index (starting with 1); NULL is returned if TYPE is 'table'.

9. COLUMN_NAME
   Column name. If the column is based on an expression, such as SALARY + BENEFITS, the expression is returned; if the expression cannot be determined, an empty string is returned.

10. ASC_OR_DESC
    Sort sequence for the column: "A" for ascending; "D" for descending; NULL is returned if column sort sequence is not supported by the data source or if TYPE is 'table'.

11. CARDINALITY
    Cardinality of table or index; number of rows in table if TYPE is 'table'; number of unique values in the index if TYPE is not 'table'; NULL is returned if the value is not available from the data source.

12. PAGES
    Number of pages used to store the index or table; number of pages for the table if TYPE is 'table'; number of pages for the index if TYPE is not 'table'; NULL is returned if the value is not available from the data source or if not applicable to the data source.

• The optimal set of columns that uniquely identifies a row in the table.
• Columns that are automatically updated when any value in the row is updated by a transaction.

$type (integer)

Type of column to return. Must be one of the following values:

SQL_BEST_ROWID (1)

Returns the optimal column or set of columns that, by retrieving values from the column or columns, allows any row in the specified table to be uniquely identified. A column can be either a pseudo-column specifically designed for this purpose (as in Oracle ROWID or Ingres TID) or the column or columns of any unique index for the table.

SQL_ROWVER (2)

Returns the column or columns in the specified table, if any, that are automatically updated by the data source when any value in the row is updated by any transaction (as in SQLBase ROWID or Sybase TIMESTAMP).

1. SCOPE
   Actual scope of the rowid. Contains one of the following values:
   SQL_SCOPE_CURROW (0), SQL_SCOPE_TRANSACTION (1), SQL_SCOPE_SESSION (2)
   NULL is returned when $type is SQL_ROWVER (2). For a description of each value, see the description of Scope in "Parameters," earlier in this section.

2. COLUMN_NAME
   Column name. The driver returns an empty string for a column that does not have a name.

3. DATA_TYPE
   SQL data type of the column.

4. TYPE_NAME
   DBMS character string represented of the name associated with DATA_TYPE column value.

5. COLUMN_SIZE
   The size of the column on the data source.

6. BUFFER_LENGTH
   The length, in bytes, of the data returned in the default C type.

7. DECIMAL_DIGITS
   The decimal digits of the column on the data source. NULL is returned for data types where decimal digits are not applicable.

8. PSEUDO_COLUMN
   Indicates whether the column is a pseudo-column, such as Oracle ROWID:
   SQL_PC_UNKNOWN (0), SQL_PC_NOT_PSEUDO (1), SQL_PC_PSEUDO (2)

Other Functions

$con -> escape_pattern ( $string )

Escape pattern matching characters for use in tables() and columns().

Example

  # since "_" matches any single character,
  # it must be escaped to match it as "_"
  $res = $con->tables( $con->escape_pattern( 'Database_Name' ) );

$con -> quote ( $value )

Quote a value for use as a literal value in an SQL statement, by escaping any special characters (such as quotation marks) contained within the string and adding the required type of outer quotation marks.

The quote() method should not be used with placeholders.

Parameters

$value

Value to be quoted.

  print $con->quote( "Don't clash with quote chars" );
  # output: 'Don''t clash with quote chars'

  print $con->quote_bin( "DBE" );
  
  # SQLite3:      X'444245'
  # MySQL:        0x444245
  # PostgreSQL:   '\104\102\105'

  # MySQL driver:
  
  $s = $con->quote_id( 'table' );
  # returns: `table`
  
  $s = $con->quote_id( 'table', 'field' );
  # returns: `table`.`field`
  
  $s = $con->quote_id( 'table', '*' );
  # returns: `table`.*

  # read the actual state
  print "current name conversion: ", $con->name_convert() || "none", "\n";
  
  # convert names to uppercase
  $con->name_convert( "NAME_uc" );
  # run a query
  $res = $con->query( "SELECT ..." );
  $res->names; # uppercase
  
  # convert names to lowercase
  $con->name_convert( "lc" );
  # run a query
  $res = $con->query( "SELECT ..." );
  $res->fetchrow_hashref; # keys are in lowercase
  
  # disable name conversion
  $con->name_convert( "" );

  # set limit to 1 row
  $con->row_limit( 1 );
  
  # the driver holds one row in the result set
  $res = $con->query( "SELECT * FROM t1" );
  
  # num_rows returns the total number of rows available
  print $res->num_rows, "\n";
  
  # fetch the first row
  @row = $res->fetch_row;
  
  # fetch the second row
  # the driver now makes a new SELECT call to the dbms
  @row = $res->fetch_row;
  
  # disable row limit for further queries
  $con->row_limit( 0 );

  $limit = 10;
  $offset = 0;
  
  $con->row_limit( $limit, $offset );
  $res->query( "SELECT * FROM t1" );
  
  $first_row = $offset + 1;
  $last_row = $res->num_rows - $offset;
  $last_row > $limit and $last_row = $limit;
  
  print "print rows $first_row to $last_row\n";
  for $row_pos( $first_row .. $last_row ) {
      $res->fetch_row( \@row ) or last;
      print "row $row_pos ", join( '|', @row ), "\n";
  }

  croak, warn, reconnect

  $prv = $con->get_attr( 'croak' );
  # enable croak on error
  $con->set_attr( 'croak' => 1 );
  eval {
      # raise an error
      $con->do( "SELECT" );
  };
  # restore previous attribute
  $con->set_attr( 'croak' => $prv );
  if( $@ ) {
      print "SELECT failed: $@\n";
  }

  if( ! $con->driver_has( 'prepare' ) ) {
      print "statements are not available!\n";
  }

Large Objects

$lob -> read ( $buffer, $length )

Reads a maximum of length bytes from the large object.

Parameters

$buffer

A variable to write the read bytes into.

$length

The maximum number of bytes read is specified by the length parameter.

  0 - Beginning of large object. 
  1 - Current position of large object. 
  2 - End of large object. 

  $lob->print( 'hello lob', "\n" );

THREADSAFETY

AUTHORS

COPYRIGHT