===========================================================================
Perl 5 Database Interface (DBI) API Specification
-------------------------------------------------
Version 0.6
* D R A F T *
============================================================
! This version of the document is still under construction !
*** You are reading a (possibly inconsistent) snapshot. ***
! Please read version 0.5 for a stable view of the standard!
============================================================
Abstract
--------
The Perl DBI is a database access Application Programming Interface
(API) for the Perl Language. This specification defines a set of
functions, variables and conventions that provide a consistent
database interface independant of the actual database being used.
---------------------------------------------------------------------------
Mailing list: perldb-interest@vix.com
Subscribe to: perldb-interest-REQUEST@vix.com
FTP Archive: ftp.demon.co.uk:/pub/perl/db (read the README file)
Current editor: Tim Bunce <timbo@ig.co.uk> (Version 0.5 onwards)
Editors notes: lines of text that begin with an exclamation:
! are editors notes to the reviewers and not part of the spec.
---------------------------------------------------------------------------
Copyright (c) Tim Bunce 1994 England.
Permission is hereby granted to make and distribute verbatim
(complete and unedited) copies of this document. Permission is also
granted to translate this document into other languages and formats
provided that all text is included.
===========================================================================
TABLE OF CONTENTS
1. INTRODUCTION TO THE DBI
1.1 Purpose of the DBI
1.2 Background
1.3 Current Work
1.4 Guiding Principles
1.5 Specification Originators And Contributors
1.6 Intellectual Property and Copyright
1.7 For More Information Or To Contribute Constructive Feedback
1.8 Structure Of The Specification
2. CONVENTIONS, STRUCTURE and OPEN ISSUES
2.1 Architecture of a DBI Application
2.2 Terms
2.3 General Interface Rules & Caveats
2.4 Naming Conventions
2.5 Notation
2.6 Document Conventions
2.7 Open Issues
3. DATABASE INTERACTION FUNCTIONS
3.1 Session Management
3.1.1 Introduction
3.1.2 connect
3.1.3 disconnect
3.2 Data Query Functions
3.2.1 Introduction
3.2.2 prepare
3.2.3 execute
3.2.4 titles
3.2.5 fetch
3.2.6 finish
3.2.7 do (Library Function)
3.2.8 lookup (Library Function)
3.2.9 proc
3.3 Transaction Management
3.3.1 Introduction
3.3.2 commit
3.3.3 rollback
3.3.4 savepoint
3.4 Navigational Data Query Functions
4. ERROR AND EVENT HANDLING
4.1 Error Status Functions
4.1.1 errno
4.1.2 errstr
4.1.3 errstate
4.1.4 errmsg
4.2 Event Handler Functions
4.2.1 Introduction
4.2.2 handler
4.2.3 example_handler
4.3 Event Types
4.3.1 Introduction
4.3.2 Standard Event Types
4.3.3 DBperl Defined Event Types
4.3.4 Example Module Specific Event Types
4.4 Using An Event Handler
4.4.1 Introduction
4.4.2 The Retry Mechanism
4.4 DBperl Generic Event Handler Function
5. THE DBPERL SWITCH AND MODULES
5.1 Introduction
5.2 Switch Variables
5.2.1 $db'version
5.2.2 $db'attribution
5.2.3 $db'last_func
5.2.4 $db'last_handle
5.2.5 $db'last_modh
5.2.6 $ENV{DBPERL_PATH}
5.2.7 $ENV{DBPERL_AUTOLOAD}
5.2.8 $ENV{DBPERL_MODULE}
5.3 Switch Functions
5.3.1 modules
5.3.2 install
5.3.3 call_func
5.4 Switch Basics
5.4.1 DBperl Modules
5.4.2 DBperl Handles
5.4.3 Function Call Routing
5.4.4 Automatic Handle Promotions
5.5 Module Selection
5.5.1 Introduction
5.5.2 Using Module Names
5.5.3 Using Database Type Names
5.6 Module Dynamic Loading
5.6.1 Introduction
5.6.2 Search Method
5.6.3 Automatic Loading on Startup
5.6.4 Example
5.7 Module Layering
5.7.1 Introduction
5.7.2 Examples
6. ATTRIBUTE NAMES AND VALUES
6.1 Introduction
6.2 Attribute Functions
6.2.1 getvalue
6.2.2 setvalue
6.2.3 Using Attributes With Other Functions
6.3 Attribute Names
6.3.2 MixedcaseNames
6.3.3 lowercase_names
6.3.4 Attribute Scope
6.3.5 Attribute Values
6.3.6 Attribute Listing Legend
6.4 Module Attributes ($modh, $dbh, $sh)
6.4.1 ModuleName S
6.4.2 ModuleAttribution S
6.4.3 ModuleHandle H
6.5 Database Attributes ($dbh, $sh)
6.5.1 IDENTIFIER_LENGTH I
6.5.2 IDENTIFIER_CASE S
6.5.3 ROW_LENGTH I
6.5.4 DbTimeZoneType S
6.5.5 DbReadCache I,M
6.5.6 DbNumRows I
6.5.7 DbHandle H
6.6 Statement Attributes ($sh)
6.7 Module Specific Attributes
6.8 Switch Specific Attributes
6.8.1 DBperlPortable B,M
6.8.2 DBperlTrace S,M
6.8.3 DBperlLog S,M
7. DATA TYPE CONVERSION FUNCTIONS
7.1 Introduction
7.2 String Formatting Functions
7.2.1 quote
7.3 Date & Time Functions
7.3.1 Notation & Nomenclature
7.3.2 ndt2udt
7.3.3 udt2ndt
8. DATA DICTIONARY FUNCTIONS
8.1 Introduction
8.2 Data Dictionary Core Functions
8.2.1 info_table_names
8.2.2 info_table
8.2.3 info_col_names
8.2.4 info_col
9. EXAMPLES
9.1 Simple SELECT
9.2 Simple INSERT
9.3 Iterative insert from flatfile
9.4 Creating a temporary table
9.5 Error handling
9.6 Bind parameter handling
10 APPENDICES
10.1 Function and Variable Summary
10.2 Portability
10.2.1 Detecting and Declaring Non-Portability
10.3 Ideas for Utilities and Packages
10.5 Module Implementors Template
10.5.1 Example for Oracle Module
10.5.2 Example for Ingres Module
===========================================================================
1. INTRODUCTION TO THE DBI
===========================================================================
(This section was last modified on $Date: 1996/11/14 23:34:52 $
is currently at $Revision: 6.6 $ and is NOT stable.)
1.1 Purpose of the DBI
---------------------------------------------------------------------------
The purpose of the DBI API (Application Programming Interface) is to
define and implement a common interface to enable interaction between
applications and various database engines. The DBI will allow the
creation of database-manipulation scripts without regard for the engine
being used to service the requests.
It is important to remember that the DBI is just an interface. A thin
layer of 'glue' between an application and one or more Database Drivers.
It is the drivers which do the real work. The DBI provides a standard
interface and framework for the drivers to operate within.
1.2 Background
---------------------------------------------------------------------------
A number of independent efforts have been made to connect database
engines to perl. Interfaces currently exist for Oracle, Sybase,
Ingres, Interbase, Informix, Unify, Postgres and perhaps others.
All of these interfaces have APIs that are atuned to the peculiarities
of their engine back-end and thus a perl script must be changed to work
with different engines.
Since September 29th 1992, a group of interested parties, including the
authors of some of the interfaces named above, have been working on
(thrashing out) an engine-independent interface specification. This
was known as DBperl for almost two years before being renamed (after
much debate) to 'The Database Interface' or DBI for short.
After approximately 18 months the specification was quite stable and
implementation was about to start. However, at this time Larry was
starting to release Perl 5 alpha versions. It soon became apparent that
the object-oriented features of Perl 5 could be used to implement a
dramatically improved database interface.
1.3 Current Work
---------------------------------------------------------------------------
The last formally released version of the specification was at the 0.5
revision level. This was frozen at the end of the Perl 4 based design
work. It's around 3000 lines and over 100Kb long. Details of the DBI
mailing list and ftp archive are given in Section 1.7.
After a period of discussion and experimentation on the mailing list an
object-oriented version of the specification for Perl 5 was informally
developed.
I am currently implementing the DBI hand-in-hand with the ongoing
development of Perl 5 by Larry Wall and the members of the perl5-porters
mailing list. The first DBI alpha was released to the mailing-list on
the 12th Oct 1994. I'm now working on the Oracle Driver, which is in
it's early stages.
Larry has very kindly agreed to ship the DBI as a standard part of Perl 5.
As implementation progresses I'll be (slowly) working on the next draft
of this specification to bring it into line with the evolving implementation.
1.4 Guiding Principles
---------------------------------------------------------------------------
It is hoped that DBI will:
o Provide an interface with sufficient capability to be useful.
o Be simple to use for simple applications.
o Have sufficient flexibility to accommodate unusual or proprietary
functionality (events etc) and even non-sql databases.
o Conform to or anticipate applicable standards where practical.
Especially the X/Open & SQL Access Group SQL and CLI standards.
o Enable the creation of database-independent perl scripts
but not limit you to the lowest common functionality.
o Support dynamic loading of database drivers.
o Support concurrent access to multiple database engines.
o Be freely available. See below.
1.5 Specification Originators And Contributors
---------------------------------------------------------------------------
- Kurt Andersen <*> Informix
- Kevin Stock <*> Oracle
- Buzz Moschetti <buzz@bear.com> Interbase
- Michael Peppler <mpeppler@itf.ch> Sybase
- Tim Bunce <tbunce@ig.co.uk> DBI Switch
- Ted Lemon <mellon@hemlock.ncd.com> Ingres
<*> Unfortunately Kevin (a major contributor) and Kurt (the original
specification editor upto and including v0.4) have both lost their
access to the net.
List of Top Contributors (automatically generated):
Tim Bunce, Kevin Stock, Buzz Moschetti, Kurt Andersen, Ted Lemon,
William Hails, Garth Kennedy, Michael Peppler, Neil S. Briscoe,
David J. Hughes, Jeff Stander, Forrest D Whitcher, Larry Wall,
Jeff Fried, Roy Johnson, Paul Hudson, Georg Rehfeld, Steve Sizemore,
Ron Pool, Jon Meek, Tom Christiansen, Steve Baumgarten,
Randal L. Schwartz, ... and a whole lot more people (100+) who have
put up with the discussions and contributed from time to time on the
mailing list (see below).
We always need more active contributors. Please join in this effort.
1.6 Intellectual Property and Copyright
---------------------------------------------------------------------------
Our collective intention is that all of the DBI materials (DBI API
specifications, switch and drivers) will be distributed under the same
terms and mechanisms as perl itself (e.g., can be used under the GNU
_or_ Artistic License at the users choice).
Thus individual Driver implementors and DBI application developers
are free to exploit their work commercially. They are not required to
release their work or source code, but we hope that they will.
This DBI API Specification is Copyright (c) Tim Bunce 1994 England.
Permission is hereby granted to make and distribute verbatim (complete
and unedited) copies of this document. Permission is also granted to
translate this document into other languages and formats provided that
all text is included.
1.7 For More Information Or To Contribute Constructive Feedback
---------------------------------------------------------------------------
Very little of this specification has been arbitrarily chosen. If you
are interested in the reasoning behind any particular portion of the
API, please send your queries to the address below and you will
probably get one (or more) versions of the discussions that have led up
to this current formulation. Be warned that more than 24 months and
over 600 messages totaling more than 2Mb have transpired, so the
succinct version you receive will not preserve the nuances of the
give-and-take that has happened.
Please send feedback on this specification to the DBI mailing list
from whence it will be echoed to the interested parties:
perldb-interest@vix.com
If you would like to be added to (or removed from) the list of
interested parties, please send your request to:
perldb-interest-REQUEST@vix.com
Archives of the perldb-interest mail and copies of the DBI API
specification are kept at ftp.demon.co.uk:/pub/perl/db by kind
permission of demon.net (Demon Internet Services Limited, UK). This
archive also holds other database/perl related software (oraperl,
sybperl, sqlperl, uniperl, rdb, shql etc).
Please contact one of the people listed above if you have specific
questions about the handling of database engine peculiarities.
1.8 Structure Of The Specification
---------------------------------------------------------------------------
Because the specification is long it is divided into several parts:
1) Introduction to the DBI (this section)
2) Structure, Conventions and Open Issues
3) Database Interaction Functions
4) Error and Event Handling
5) The Switch and Drivers
6) Attribute Names and Values
7) Data Type Conversion Functions
8) Data Dictionary Functions
9) Examples
10) Appendix
Eventually this specification will become a Perl pod file for the DBI.
Each Driver implementor will also supply a man page for their driver in
a standard format and style.
End of DBI API Section 1.
===========================================================================
2. CONVENTIONS, STRUCTURE and OPEN ISSUES
===========================================================================
(This section was last modified on $Date: 1996/11/14 23:34:52 $
is currently at $Revision: 6.5 $ and is NOT stable.)
2.1 Architecture of a DBI Application
---------------------------------------------------------------------------
|<- Scope of DBI ->|
.-. .--------------. .-------------.
.------. | |---| XYZ Driver |---| XYZ Engine |
|Perl | |S| `--------------' `-------------'
|script| |A| |w| .--------------. .-------------.
|using |--|P|--|i|---|Oracle Driver |---|Oracle Engine|
|DBperl| |I| |t| `--------------' `-------------'
| API | |c|...
|funcs | |h|... Other drivers
`------' | |...
`-'
2.2 Terms
---------------------------------------------------------------------------
API -- Application Perl-script Interface
The call interface and variables provided by DBI to perl
scripts. The API is implemented as a Perl extension.
Switch
The code that provides the DBI API and dispatches the DBI function
calls to the appropriate Driver for actual execution. The Switch
is also responsible for the dynamic loading of Drivers, error
checking/handling and other general duties.
Driver
A Driver implements support for a given type of Engine (database).
Drivers contain implementations of the DBI functions written using
the private interface functions of the corresponding Engine. Only
authors of sophisticated/multi-database applications or generic
library functions need be concerned with Drivers. The Switch
itself has a Driver interface and can be conveniently treated as an
Driver by applications.
Engine
The actual "engine" that is being communicated with via a Driver,
e.g., Oracle, Ingres, Sybase etc. Note that Engines need not be
databases, consider DNS, X.500, SNMP for example.
DRI -- Driver Interface
This is the interface provided by the Switch which is used to
communicate with Drivers. Only Driver implementors need be
concerned with this interface. The Switch and DRI will take most of
the work out of writing Drivers for DBperl. Hopefully this will
encourage people to implement Drivers for their favourite database.
! reference
! object
Handle
Handles are Perl 5 blessed references returned by some DBI
functions which can be passed back to DBI functions to refer to
objects such as a Driver, a database connection or a prepared SQL
statement.
Library
A perl package of utility functions to aid the DBI user in
performing common activities. Within the DBI spec these functions
are not separated out but are located in their appropriate sections
and identified by the label 'Library Function'.
Bundle
The actual collection of DBI and Drivers which are built and/or
installed on the system where the perl script is being executed.
TBD
To Be Decided. Parts of the specification that have not yet been
completed. These are often just section cross references.
!Class
!Method
!Module (DBperl.pm)
! DBperl packages are henceforth 'classes' because they provide 'methods'.
! DBperl functions are 'methods' because they all expect a blessed
reference ("virtual") or a class name ("static") as their first argument.
! DBperl handles are 'objects'. (anyone care to explain why in one simple
sentence? Something about being a blessed reference to a class)
! The term 'DBperl object' will be used to refer to any object created
by DBperl including all types of handles.
! DBperl will be implemented as a Perl 5 'module' with a .pm file and
an import method. Application code will say: use DBperl;
! The terms 'derived class' and 'base class' will be used to describe
inheritance relationships.
Other terms used in this document but not directly related to DBperl
are defined in the Appendix.
2.3 General Interface Rules & Caveats
---------------------------------------------------------------------------
a) Most data is returned to the perl script as perl strings. This
allows arbitrary precision representations within the scope of the
Engine/Adaptor to be handled without loss of accuracy. Beware that
perl conversions and other Engines/Adaptors may not preserve the
same accuracy.
b) Dates and times are returned as character strings in the native
format of the corresponding Engine. Functions are provided to
convert the native format values into integers representing the
number of seconds since 1 January 1970, e.g., 'unix time'. Other
conversion functions may be defined later. Time Zone effects are
Engine/Adaptor dependent, see Section TBD.
c) Perl supports binary data in perl strings and the Switch will pass
binary data to and from the Adaptors without change. It is up to
the Adaptor implementors to decide how they wish to handle such
binary data.
d) Multiple SQL statements may not be combined in a single statement
handle, e.g., a single $sh. This restriction has most impact upon
Sybase users. An alternative approach may be possible later.
e) Non-sequential record reads are not supported in this version of the
DBperl API. E.g., records can only be fetched in the order that the
database returned them and once fetched they are forgotten.
f) Positioned updates and deletes are not currently supported by
DBperl. E.g., it is not possible to execute a select query, fetch a
number of rows and then update or delete the 'current row' before
fetching more rows. All cursors are assumed to be read-only and
unnamed. This may change in the future.
g) Individual Adaptor implementors are free to provide any private
mechanisms that they feel are useful (e.g., for items d, e and f
above). These private functions can be invoked using the DBperl::
exec() method, but they are not part of this specification.
2.4 Naming Conventions
---------------------------------------------------------------------------
a) All DBperl functions and variables exist in packages with names
which begin with 'DBperl'. These package name are reserved for use
by DBperl.
! changes:
b) The package name space 'db_*' is reserved for the private use of
DBperl Adaptor packages. E.g., the 'oracle' DBperl Adaptor has
private use of the db_oracle package namespace. DBperl does not
currently define any use for this namespace, this will change.
Scripts using packages with names which begin with db_ may
experience compatibility problems with future versions of the
DBperl API or specific DBperl Adaptors.
c) The DBperl Adaptor names 'switch' and 'dbperl' are reserved.
d) All environment variables used by the DBperl Switch or Adaptors
begin with 'DBPERL_'.
e) Attribute name-value pairs are used in many places throughout the
DBperl specification. They provide a very simple and flexible way
to determine or alter the behaviour of an Adaptor or a particular
database query.
Attribute names and values are described in detail in section 6.
For now it is only important to note that the case of the attribute
name is used to signify who defined the meaning of that name and
its values.
Case of name Meaning defined by
------------ ------------------
UPPER_CASE Standards, e.g., X/Open, SQL92 etc (portable)
MixedCase DBperl API (portable), underscores are not used.
lower_case Adaptor or Engine specific (non-portable)
2.5 Notation
---------------------------------------------------------------------------
a) "Handles", if defined, are assumed to be active:
$adh - designates a handle for a database Adaptor and is
generally interchangeable with the name of the Adaptor
to which it connects. Typically users need not be
concerned with Adaptors or Adaptor handles.
$dbh - designates a handle for an open database connection and
presumes an Adaptor designation.
$sh - designates a statement handle for a single prepared SQL
statement and presumes a $dbh to which it is connected.
There is no separate cursor handle for active queries in
DBperl. The $sh serves as a handle for both the prepared
statement and its cursor. Multi-statements tied to a
single handle are not supported.
$handle - designates that a handle is required but no specific
type is most appropriate. The accompanying documentation
will describe which types of handles may be used.
b) Return values are noted as:
$rc - return condition (or a handle), evaluates (in Perl) to
boolean false on an error condition to facilitate short-cut
chaining.
Typical values: 1=success, 0=error, undef=unsupported (error).
E.g., $handle->method() || die "method failed"
$rv - return value, does not necessarily evaluate as false on an
error condition.
@ary - returns an array of values. Typically an error condition
will result in an empty array being returned.
2.6 Document Conventions
---------------------------------------------------------------------------
Each function is documented with the following general structure:
5.4.3 something
$rv = $handle->method($arg [,%attr]);
$arg -- description of argument
%attr -- associative array of additional attributes that can
be passed to this function to affect its behaviour
=> $rv -- description of returned value
Several sentences describing the purpose and behaviour of the method.
Events: description of events/errors that may occur (if any)
EVENT_NAME -- description of this event and its meaning
...
Attributes: description of optional attributes (where appropriate)
name, value -- description of this attribute and its possible values
...
Portability:
Any aspects of this functions behaviour that might not be supported
by some Adaptors. (Also see the Portability section of the appendix.)
Example:
...
Events and errors are described in detail in section 4.
Standard events and errors such as ERROR and WARN are also
described in that section. Only events that are specific to a
function are listed in that functions description.
2.7 Open Issues
---------------------------------------------------------------------------
a) The contents of the DBperl Library are open to suggestion.
Please let me know what other common patterns of SQL execution you
think would be useful to DBperl application developers.
b) Suggestions for additional attribute names and values are
requested.
c) How to handle date and time intervals in a distinct manner from
absolute dates and times is yet to be defined. It may be ignored.
d) Data typing issues in general have been put to one side for now
(other than the absolute vs interval date/time issue noted above).
It seems best to ignore them until implementations of the spec
actually exist.
e) Do we need functions to control:
autocommit -- on / off
locking -- level=row/page/table, readlocks, timeouts etc
etc
f) Do we need functions to:
call database procedures
return space left in/for the database
return list of available databases
etc
End of DBperl API Section 2.
===========================================================================
3. DATABASE INTERACTION FUNCTIONS
===========================================================================
(This section was last modified on $Date: 1996/11/14 23:34:52 $
is currently at $Revision: 6.3 $ and is NOT stable.)
3.1 Session Management
---------------------------------------------------------------------------
3.1.1 Introduction
Before an application can interact with a database engine it must
establish a connection to that engine using the &db'connect function.
A database connection is also known as a database session.
DBperl does not have a concept of a `current session'. Every session
has a handle (e.g., a $dbh) returned from the &db'connect function, and
that handle (or a handle derived from it) must be passed to every
DBperl function.
3.1.2 connect
$dbh = &db'connect([$database [, $username [, $password
[, $module [, %attr]]]]]);
$database -- a database name
$username -- user name by which to connect to the database
$password -- password by which to connect as $username
$module -- name or handle of module to use
%attr -- attributes for this connection
$dbh -- returns a database connection handle or undef on error.
Establishes a database connection (session) to the requested database.
The $database, $username and $password arguments are passed to the
module for processing. The DBperl API does not define ANY
interpretation for the contents of these fields. As a convenience, if
the $database field is undefined or empty the Switch will substitute
the value of the environment variable DBPERL_DBNAME if any.
If $module is not specified, the environment variable DBPERL_MODULE
is used. If that variable is not set and the Switch has more than one
Module loaded then the connect fails and undef is returned. Modules
are defined in Section 5.
The module is free to interpret the database, username and password
fields in any way and supply whatever defaults are appropriate for
the engine being accessed.
The &db'connect() function automatically starts a transaction for
those database types which require explicit control over
transactions. There is no need for the user to invoke an explicit
'start transaction' function. Whenever a transaction is commited or
rolled-back a new transaction is started automatically. This emulates
the SQL-92 standard behaviour.
Each session ($dbh) is independent from the transactions in other
sessions. This is useful where you need to hold cursors open across
transactions, e.g., use one session for your long lifespan cursors
(typically read-only) and another for your short update transactions.
Portability:
Portable applications should not assume that a single module will
be able to support multiple simultaneous sessions. For example:
$dbh_1 = &db'connect(@args);
$dbh_2 = &db'connect(@args); # may not be portable
Attributes:
Handler, function -- establish the event handler for this connection
(see Section 4: Error and Event Handlers). This is an alternative
to calling &db'handler and allows the handler to process events in
the midst of establishing a connection whereas &db'handler
requires a preexisting $dbh.
See Section 6 and "Modules" in the Appendix for details of general
and Module specific attributes.
3.1.3 disconnect
$rc = &db'disconnect([$handle]);
$handle -- an optional handle: $modh or $dbh
Release all resources being used in conjunction with the specified
handle (typically a $dbh) and disconnect the session from the
engine. Note that any changes associated with the session
will be rolled back. To commit work use &db'commit before calling
&db'disconnect.
If a module handle is specified, all connections via that module will
be &db'disconnect()'d and, if dynamic loading is implemented, the
module will be unloaded. If no parameter is specified, all modules
will be detached.
3.2 Data Query Functions
------------------------------------------------------------
3.2.1 Introduction
DBperl allows the application to `prepare' a statement for later
execution. A prepared statement is identified by a statement handle,
e.g., $sh. A statement handle can be in one of three states:
Prepared -- after &db'prepare() or &db'finish() succeeded.
Open -- after &db'execute() if the statement is a select.
Invalid -- after &db'disconnect() (or &db'prepare() failed).
Typical function call sequence (for select statement):
prepare,
execute, fetch, fetch, ... finish,
execute, fetch, fetch, ... finish,
execute, fetch, fetch, ... finish.
Typical function call sequence (for non-select statement):
prepare,
execute,
execute,
execute.
Some database engines invalidate prepared statements after a commit or
rollback. DBperl Modules for these engines will automatically and
transparently re-prepare any statements whose handles are used after
they become invalid in this way.
Portability restrictions (also see "Portability" in the Appendix):
Portable applications should not rely on a single module being able to
support multiple simultaneous open selects. For example:
$sh_1 = &db'prepare($dbh, $select_statement_1);
$sh_2 = &db'prepare($dbh, $select_statement_2);
&db'execute($sh_1);
&db'execute($sh_2); # $sh_1 still open, may not be portable
Changes from DBperl v0.4 (and oraperl):
a) $qh handles (previously $sqlh) are now called statement handles ($sh)
b) &db'prepare NEVER executes a statement.
c) &db'bind is now called &db'execute to better describe its function
and conform more closely with the SQL CLI standard.
d) &db'execute and &db'do are the only (non-library) functions that
execute statements.
3.2.2 prepare
$sh = &db'prepare($dbh, $statement [, %attr]);
$dbh -- a database handle
$statement -- a database language statement (e.g. SQL)
%attr -- additional attributes
$sh -- returned statement handle
Prepare a single statement for execution by the database and return a
handle for it.
The statement can include 'placeholders' for values to be supplied
when the prepared statement is executed. Placeholders are identified
with a question mark in the statement (as per the X/Open standard).
The &db'execute function (see below) is used to associate values with
any placeholders in a prepared statement before executing it.
The &db'getvalue($sh, 'IsSelect') function can be used to determine
if the prepared statement is a select.
There is no mechanism to unprepare or forget a prepared statement.
TBD: a disconnect will. What about commit etc?
Attributes
ReadAheadCache, n -- number of rows to read ahead (if supported)
CursorLife, HOLD -- cursor should span transactions (if supported)
Events/Errors:
InvalidAttribute --
3.2.3 execute
$rc = &db'execute($sh [, @bind_values]);
$sh -- a statement handle from &db'prepare
@bind_values -- values to be bound to placeholders in $sh.
$rc -- return code, '' (false) on error else a true value.
Perform whatever processing is necessary to execute the prepared
statement.
If the prepared statement contained placeholders then the correct
number of values for those placeholders must be supplied as arguments
to &db'execute. NULL values are indicated by undefined values
(undef) in the @bind_values array.
* Execution of non-select statements:
There is no need to call &db'finish after the execution of non-select
statements. Note that it is not possible to identify the number of
rows affected by a non-select statement if there was a warning; use
&db'getvalue($sh,'RowCount') instead.
$rc = 'SUCCESS' -- No rows (or an unknown number) affected
$rc = 'WARN' -- Warning, with an unknown number of rows affected
$rc = integer>0 -- Number of rows affected (success)
* Execution of select statements:
The &db'execute function for a select statement is similar to the
Open Cursor function provided by many database interfaces.
If the statement handle was already open then &db'finish is called
automatically to complete the previous select.
The &db'fetch function can be used to fetch result rows. The
&db'finish function should be called to complete the processing of
the select statement (free read locks, etc) and return the statement
handle to its prepared state.
$rc = 'SUCCESS' -- Select started without any problems
$rc = 'WARN' -- Select started with warnings
3.2.4 titles
@ary | $rv = &db'titles($sh);
$sh -- a statement handle
@ary | $rv -- returns array of column names or number of columns
In an array context &db'titles returns an array of column names. In a
scalar context it returns the number of columns. Returns () or 0 if
$sh is not a select statement.
If the engine does not provide column names then the module will return
('col1','col2','col3') etc.
3.2.5 fetch
@ary = &db'fetch($sh);
$sh -- a statement handle
@ary -- returns fetched record as an array of field values
Fetches and returns a row of data from the statement handle $sh.
NULL values are indicated by undefined values (undef) in the returned
array or by the value of the current setting of the ReturnNullAs
attribute.
There is currently no DBperl function that supports non-sequential
(and non-standard) record read orders, e.g, First, Last, Next,
Previous etc. This may be addressed in a future version of the DBperl
spec. Meanwhile Module implementors can provide a private function
for users to invoke via the &db'call_func() function (see Section 5).
Calling &db'fetch in a scalar context will generate a warning if the
query has more than one field and perl was invoked with the -w flag.
Errors:
NonSelectHandle -- $sh is not a handle you can fetch from.
add &fetchall to return ref to [ [@], [@], ... ]
3.2.6 finish
$rc = &db'finish($sh);
$sh -- a statement handle
$rc -- return code
Release all resources (and locks, etc) being used by an Open $sh.
This is equivalent to closing a cursor in many database types. After
this call, the $sh statement handle will no longer be Open. The
statement remains Prepared, the statement handle can be passed to
&db'execute again if required. This function may be called for
non-select statements but it does nothing.
Note that there is no function to forget or delete a prepared
statement handle. Prepared statements live until &db'disconnect.
3.2.7 do (Library Function)
$rc = &db'do($dbh, $statement [, @bind_values]);
$dbh -- database handle
$statement -- a non-select statement
@bind_values -- optional bind values
$rc -- return code
Immediately executes $statement. This function provides a simple way
to execute self-contained statements such as deletes, updates and
inserts. It avoids the need to use &db'prepare, &db'execute and
possibly &db'finish.
Example
&db'do($dbh, "delete from $table where key_1=?", $key_1);
Library Implementation:
local($sh) = &db'prepare($dbh, $statement) || return undef;
&db'execute($sh, @bind_values) || return undef;
local($err) = $db'errno; # save the errno from execute
&db'finish($sh); # just in case it was a select
$err;
3.2.8 lookup (Library Function)
@ary = &db'lookup($dbh, $statement [, @bind_values]);
$dbh -- database handle
$statement -- a statement
@bind_values -- optional bind values
@ary -- returned first record as array of field values
Immediately executes $statement, fetching and returning the
first row of data. This function provides a simple way
to execute occasional select statements that need only
return one row of data.
Library Implementation:
local($sh) = &db'prepare($dbh, $statement) || return undef;
&db'execute($sh, @bind_values) || return undef;
local(@row) = &db'fetch($sh);
&db'finish($sh);
@row;
3.2.9 proc
This version of DBperl does not define a standard function for
invoking stored procedures. Too many differences exist between
the major database vendors implementations for it to be practical
to define a standard interface for DBperl.
Module implementors are encouraged to provide a module private
function called _proc for invoking stored procedures for their engine
type. Applications can call this function using the DBperl
&db'call_func() function described in Section 5.
For example:
&db'call_func($handle, '_proc', $procedure_name, @args);
3.3 Transaction Management
------------------------------------------------------------
3.3.1 Introduction
Most DBperl modules for relational database engines will support
transaction processing with commit and rollback.
The &db'connect, &db'commit and &db'rollback functions automatically
start a new transaction for those database engines which require
explicit control over transactions. There is no need for the user to
invoke an explicit 'start transaction' function. Whenever a
transaction is commited or rolled-back a new transaction is started
automatically. This emulates the SQL-92 standard behaviour.
3.3.2 commit
$rc = &db'commit($dbh);
$dbh -- database handle
$rc -- return code
Perform any processing needed to commit a transaction. If
transactions are not supported by the engine, the function does
nothing and returns success.
By default any open statement handles associated with the $dbh
session will be returned to the prepared state.
As stated in 3.3.1, the &db'commit() function automatically starts a
new transaction for those database types which require explicit
control over transactions. There is no need for the user to invoke an
explicit 'start transaction' function. Whenever a transaction is
commited or rolled-back a new transaction is started automatically.
This emulates the SQL-92 standard behaviour.
3.3.3 rollback
$rc = &db'rollback($dbh, [$savepoint_name]);
$dbh -- database handle
$savepoint_name -- optional name of a transaction savepoint
$rc -- return code
Perform any processing needed to rollback a transaction. If
transactions are not supported by the engine, the function returns an
error.
* Partial rollbacks (to named savepoints):
If savepoints are supported then the transaction is rolled back
to the named savepoint. Behaviour if the savepoint is not known
is undefined. Savepoints are defined using the &db'savepoint
function (section 3.3.3).
If savepoints are not supported by the engine, the entire transaction
is rolled back (see below) and the function raises an error.
&db'getvalue($dbh, 'Savepoints') can be used to determine if a
database supports transaction savepoints.
* Complete rollbacks (no savepoint or savepoint error):
By default any open statement handles associated with the $dbh
session will be returned to the prepared state.
As stated in 3.3.1, after an entire transaction has been rolled back,
&db'rollback($dbh) automatically starts a new transaction for those
database types which require explicit control over transactions.
There is no need for the user to invoke an explicit 'start
transaction' function. Whenever a transaction is commited or
rolled-back a new transaction is started automatically. This emulates
the SQL-92 standard behaviour.
3.3.4 savepoint
$rc = &db'savepoint($dbh, $savepoint_name);
$dbh -- database handle
$rc -- return code
Mark this point within a transaction as a named savepoint.
If savepoints are not supported by the engine, returns undefined.
3.4 Navigational Data Query Functions
------------------------------------------------------------
It is intended that future versions of the DBperl specification be
extended to include a simple non query language interface.
This `navigational' style interface would allow DBperl to act as a
single standard Perl interface for both SQL and flat-file databases
(including the whole range of xBase .DBF databases and unix system
files). This work is likely to be based loosely on the Borland IDAPI
specification.
When using these interface functions against an SQL database the DBperl
Switch could automatically translate the query into SQL before passing
it to the Module for processing. In this way simple applications that
don't require the facilities of a query language can be made portable
across a wider range of databases.
Note that non-query language `databases' could include systems like NIS,
DNS, SNMP, X.500 etc.
End of DBperl API Section 3.
===========================================================================
4. ERROR AND EVENT HANDLING
===========================================================================
(This section was last modified on $Date: 1996/11/14 23:34:52 $
is currently at $Revision: 6.3 $ and is NOT stable.)
4.1 Error Status Functions
---------------------------------------------------------------------------
4.1.1 errno
$db'errno
Holds the native error code returned by the last DBperl function.
Note that this variable has a *very* short lifespan. It is reset
to '' (false) by the Switch before *every* DBperl function call.
See also the &db'errstate($dbh) function below.
Users should not assume that this variable will hold an integer
value, engines that use the newly standardised 5 character SQLSTATE
variable will store that value in $db'errno instead.
4.1.2 errstr
$db'errstr
Holds the error text associated with the current value of $db'errno.
The text does not have a trailing newline character.
This is equivalent to join("\n", &db'errmsg($db'last_handle)).
See &db'errmsg below and Section 5 "Switch and Modules".
4.1.3 errstate
$rv = &db'errstate($dbh);
$dbh -- database handle
$rv -- returns last native error code for this handle
Returns the last native error code associated with this $dbh. The
lifespan of this value is determined by the module but it should be
valid for longer than $db'errno. Most modules will only reset this
value when another error occurs (like unix errno).
Portability:
Note that although a $dbh is indicated here, some modules will only
be able to store one errstate value for all their sessions.
4.1.4 errmsg
@ary = &db'errmsg($dbh);
$dbh -- database handle
@ary -- returns diagnostic message strings
Returns one or more diagnostic text strings associated with the last
operation performed on the designated handle.
The X/Open standard provides for multiple diagnostic messages to
be generated and stored by a database engine during the processing
of a function. The individual diagnostic messages strings do not have
trailing newlines.
As a convenience, if the function is called in a scalar context it will
return a single string containing all the available messages joined
by newlines (but still without a trailing newline).
4.2 Event Handler Functions
---------------------------------------------------------------------------
4.2.1 Introduction
DBperl provides a very general ``event'' handling mechanism. This
mechanism can easily support a wide variety of event types in a simple
and consistent manner. DBperl treats errors and warnings as types of
event.
Databases that support events/alerters/messages etc can use the DBperl
event handler mechanism to provide a clean and consistent interface for
them.
When a module raises an event/error it defines a default behaviour to
be followed if no event handler is registered or the handler returns
undef.
There is no default handler installed but a simple generic handler
is supplied in the DBperl library.
4.2.2 handler
$rv = &db'handler($dbh, $handler_function);
$dbh -- a database handle
$handler_function -- name of function to be called
$rv -- returns the name of previous handler function or ''.
Installs the named perl function as an event handler for the
specified $dbh. The handler function will be called by the Switch
when certain events/conditions occur in DBperl functions acting for
this $dbh, including errors and warnings.
An example handler function is defined below.
Because &db'handler() returns the name of the previously installed
handler (if any) the application developer can chain handlers
together. For example, a new handler could call the previous handler
to deal with any event types it did not wish to handle itself.
Example:
$prev_handler = &db'handler($dbh, 'example_handler');
4.2.3 example_handler
sub example_handler {
local($event_type, $function_name, $handle, %params) = @_;
... code ...
return undef if (< default behaviour is ok >);
... code ...
return ($action, @values); # alter behaviour to $action
}
Parameters:
$event_type -- Event type which triggered this call (See 4.3).
$function_name -- DBperl function being called when the event occurred.
$handle -- The handle passed to the DBperl function.
%params -- An associative array, the contents of which are
defined in conjunction with the $event_type and
$function_name. This will often be empty.
Results:
undef -- handler does not want to handle event
($action, @values) -- where $action is one of: 'Ok', 'Fail', 'Retry',
'Abort' (See 4.4)
The first three parameters provide 'core' information (information
that is independent of any given event type). The $handle can be used
to provide other useful information (such as the module name) via the
&db'getvalue($handle,...) function.
The $event_type names follow the DBperl naming convention whereby the
case of the name identifies who defined the meaning of that event
type. See section 2.4.a for more details. If the event is an error
then $db'errno (etc) will be set correctly by the module before the
handler is called.
The contents of %params will vary according to the function name,
event name, the functions arguments and the module type.
Note that very few fixed parameters are passed (e.g., just the 3) in
order that event handling can be as fast as possible. Future uses of
event handlers might require much higher 'bandwidth' than the
occasional error event.
The handler can return undef to indicate that it does not wish to
alter the default behaviour for this event.
Returned Action Values:
Ok -- The calling function should return success using @values
Fail -- The calling function should return failure using @values
Abort -- DBperl will rollback and abort the entire application
Retry -- Attempt to retry the action that generated the event
A handler is free to return any $action value that the module
supports (by DBperl convention such $action values would be lowercase
names). In this way modules can easily extend the functionality of
event handlers to suit their needs. Action values that are unknown
to the Module cause a warning to be printed to stderr and the action
is treated as 'Fail'.
4.3 Event Types
---------------------------------------------------------------------------
4.3.1 Introduction
Events are identified by Event Type Names. Event type names are defined
below in a similar manner to functions:
EventTypeName ([param_name_1 [, ...]])
The EventTypeName is the value passed to the handler function as its first
argument ($event_type in the example handler shown above).
The optional names listed in parentheses are the names of key value
pairs passed to the handler in the %params argument. The key names
follow the DBperl letter case convention defined in Section 2.4.d.
Where possible, modules should use DBperl defined event types and not
define their own. Modules are free to supply extra module-specific key
value pairs for DBperl defined events in order to convey any extra
information that is available for the event.
4.3.2 Standard Event Types
These three events are the `standard' events supported by most
engines. No extra parameters (%params) are supplied to the handler for
these event types.
ERROR ()
An operation failed.
WARN ()
An operation succeeded but with a warning.
NO_MORE_DATA ()
A &db'fetch reached the end of result set.
4.3.3 DBperl Defined Event Types
These events cover both DBperl internal issues (such as
AttributeUnknown) and attempt to provide a standard interface for a
range of engine related events (e.g., DbMessage).
AttributeUnknown (Name)
An attempt was made to refer to an unknown attribute name (Name) by a
function with a %attr parameter or by a call to &db'setvalue or
&db'getvalue.
AttributeValueBad (Name, Value)
An attempt was made to set an known attribute (Name) to an invalid
value (Value). The attribute value remains unchanged.
DbMessage (Text [, Id])
A message string (Text), with an optional identifier (Id), has been
received from the engine. Typically these messages originate from
the execution of stored procedures.
DbEvent (Name)
An event (identified by Name) has occurred within the engine.
Debug (Level, Text)
The Switch and/or Modules may choose to use the event mechanism to produce
and filter debugging information. This event type name is a placeholder for
that purpose which may be more fully defined in the future.
4.3.4 Example Module Specific Event Types
All module specific (private) event types have lowercase names that
begin with the name of the module.
oracle_?
... any suggestions ?
4.4 Using An Event Handler
---------------------------------------------------------------------------
4.4.1 Introduction
The event handler mechanism is very flexible. It is unlikely that a
full definition of its behaviour will be available until the DBperl
Switch and one or more Modules have been implemented.
Initially the handler function will only be able to return undef (to
signify default behaviour) and hence will not be able to control the
further processing of the event.
The notes below attempt to outline the current thinking.
4.4.2 The Retry Mechanism
For certain types of event it is useful to be able to retry the action
that triggered the event. Typical examples are Deadlock and LockTimeout.
It is likely that only a few events and only some modules will support
the retry mechanism.
Sequence of actions:
a) A retryable event occurs. The module prepares to call the handler.
b) The module indicates to the handler that a Retry is possible by
supplying a RetryCount (with the value 0) in the %params array.
c) The handler requests a retry by returning 'Retry' to the module.
d) The module retries the action.
e) If the retry fails then the handler is called again with an
incremented RetryCount.
This activity can be viewed as a dialogue between Module and handler.
If a RetryCount is NOT passed to the handler then the Module is saying
"I cannot retry this so don't bother asking for a Retry". If a RetryCount
IS passed to the handler then the Module is saying "I can retry this for
you if you want me to". The value of the RetryCount gives the number of
failed retries so far attempted (initially zero).
A call tree might look like:
--> DBperl Function called
Switch
Module [Module detects retryable event]
Event_Handler(..., 'RetryCount', 0) [responds 'Retry']
Module [Module retries but fails again]
Event_Handler(..., 'RetryCount', 1) [responds 'Retry']
Module [succeeds and returns via Switch]
Switch
<-- DBperl Function returns
The application has no (direct) visibility of this activity.
Note that there is no mechanism to define a maximum retry count because
it's the users own event handler that is controlling the retries. It can
stop retrying whenever it likes and for whatever reasons it likes (for
example, "retry as many times as possible within one minute").
Example of handler function return code:
return ('Abort', 1) if ($params{'RetryCount'} > 3);
return ('Retry'); if (defined($params{'RetryCount'});
return undef; # else default behaviour if I can't retry
4.4 DBperl Generic Event Handler Function
---------------------------------------------------------------------------
This subsection lists the source code for the DBperl generic event handler
function supplied in the DBperl library. This function is not installed as
a handler by default. It must be explicitly installed by an application.
! this should be expanded...
sub db'generic_event_handler{
local($event_type, $function_name, $handle, %params) = @_;
local(@p_list, $p_name, $p_value)=(''); # for processing %params
# <do lots of &db'getvalue calls to gather info about $handle>
# build list of name=value strings for each item in %params
while(($p_name, $p_value) = each(%params)){
push(@p_list, "$p_name='$p_value'");
}
# print a reasonably generic event message
print "$event_type event in $function_name",
join(', ','',@p_list),"\n";
# retry twice if possible else request default behaviour
return ('Retry') if (defined($params{'RetryCount'})
&& $params{'RetryCount'} < 2);
return undef; # request default behaviour for this event
}
End of DBperl API Section 4.
===========================================================================
5. THE DBPERL SWITCH AND MODULES
===========================================================================
(This section was last modified on $Date: 1996/11/14 23:34:52 $
is currently at $Revision: 6.3 $ and is NOT stable.)
5.1 Introduction
---------------------------------------------------------------------------
Recall the diagram from section 2.1:
|<-- Scope of DBperl --->|
.-. .-------------. .-------------.
.------. | |---| XYZ Module |---| XYZ Engine |
|Perl | |S| `-------------' `-------------'
|script| |A| |w| .-------------. .-------------.
|using |--|P|--|i|---|Oracle Module|---|Oracle Engine|
|DBperl| |I| |t| `-------------' `-------------'
| API | |c|...
|funcs | |h|... Other modules
`------' | |...
`-'
The Switch is at the heart of DBperl. It provides the DBperl API and
re-directs the DBperl function calls to the appropriate Module for
actual execution. The Switch is also responsible for the dynamic
loading of Modules, error checking/handling and other general duties.
Note that it is not necessary to understand the contents of this
section of the DBperl API in order to be able to write DBperl
applications. This section deals with deeper issues that should only
be of interest to advanced users and the curious.
5.2 Switch Variables
---------------------------------------------------------------------------
5.2.1 $db'version
The version number of the DBperl interface specification (e.g., 1.1)
Note that this value is *not* the version of any software actually
being used. It only defines the version of the DBperl *specification*
that the software claims to implement.
This value can be used to ensure that the DBperl being used to execute
an application is recent enough to have all the required features. This
is similar to the perl $] special variable.
The &db'getvalue($modh, 'ModuleVersion') function can be used to find
the version of a particular Module. To find the version of the Switch
software use &db'getvalue('switch', 'ModuleVersion').
Also see the $db'attribution variable below.
5.2.2 $db'attribution
The attribution strings for DBperl, the Switch and all the loaded
Modules.
Attributions are similar to the output of perl -v and would typically
include: author, copyright notice, compile time options and revision
information (e.g., version number, patch level, release date etc).
Would typically be used as: DBperl -e "print \$db'attribution".
5.2.3 $db'last_func
The name of the last DBperl function called. Also see $db'last_modh
below.
5.2.4 $db'last_handle
The handle passed to the last DBperl function called. Also see
$db'last_modh below.
5.2.5 $db'last_modh
The handle of the last module invoked by the Switch to implement the last
function called.
If $db'errno is set, then $db'last_modh corresponds to the module that
recorded the error in $db'errno. Note that errors detected by the
Switch (e.g. invalid handle) result in $db'last_modh being set the
handle of the notional Switch Module and $db'last_handle being set to
undef.
These last_* variables would typically only be of use in sophisticated
multi-database applications or generic library functions.
5.2.6 $ENV{DBPERL_PATH}
The colon separated path used by the DBperl Switch when searching for
modules to dynamically link/load. Defaults to the value of @INC.
5.2.7 $ENV{DBPERL_AUTOLOAD}
Defines dynamically linked modules to be pre-loaded by the DBperl
Switch on startup and precedence for activation.
The full specification for DBPERL_AUTOLOAD will not be defined until
a DBperl switch which supports autoloading is being implemented.
5.2.8 $ENV{DBPERL_MODULE}
The default Module name to use if no module parameter supplied to the
&db'connect function. (Also used if an empty $modh is passed to any
function. See below.)
5.3 Switch Functions
---------------------------------------------------------------------------
These functions only exist in the Switch and not in any Modules.
5.3.1 modules
@ary = &db'modules();
Returns an array of names of all the installed modules.
E.g., ('oracle', 'xbase', 'informix'). The notional 'switch'
module is not included in the list.
DBperl does not have a function that lists modules which could be
dynamically loaded (if supported) from external files. Such a
function may be added later.
5.3.2 install
$modh = &db'install($module_name [, %install_attributes ] );
$module_name -- the name of the module to install
%install_attributes -- optional installation attributes
$modh -- returned module handles or undef
Install and initialize a named module and make it available for use.
This function is normally never called directly by a DBperl
application. Typically all module loading and initialization is
performed automatically and transparently by the Switch either on
start-up (DBPERL_AUTOLOAD) or when the application calls &db'connect.
5.3.3 call_func
$rv | @ary = &db'call_func($handle, $function [, @params]);
$handle -- $modh, $dbh or $sh
$function -- a module specific function name
@params -- function specific parameters
$rv|@ary -- Result dependent on the function specified.
Executes the named function of the module identified by $any_handle
(passing it $any_handle and @params) and returns the result thereof.
This is the recommended method for calling private module functions
via the DBperl specification.
In order to prevent name space clashes with future versions of the
DBperl API, non-DBperl standard function names (private functions)
must begin with an underscore.
Examples
# call module private function to invoke stored procedure
$rv = &db'call_func($dbh, '_proc', $proc_name, @proc_args);
# call module private function to fetch previous row
@ary = &db'call_func($sh, '_fetch_prev');
5.4 Switch Basics
---------------------------------------------------------------------------
5.4.1 DBperl Modules
A DBperl Module typically enables access to one or more types of
database, e.g., the 'oracle' module implements an interface to Oracle
databases.
The DBperl Switch manages one or more modules and provides Perl
applications with a single consistent interface to them. The Switch
ensures that the correct module handles each request.
Future versions of the Switch are planned to support dynamic loading of
Modules automatically on demand. This specification assumes that such
functionality already exists.
Module names are always lowercase and globally unique. To avoid clashes
the DBperl API appendix will list (register) all known module names.
5.4.2 DBperl Handles
A DBperl handle is an opaque scalar value. The actual contents of the
handle are private to the Switch and the Module that provided the
handle. Applications should not attempt to examine or print the
contents of a DBperl handle.
Recall that DBperl defines three types of handle:
$modh -- a handle for an instance of a loaded Module
$dbh -- a handle for an instance of a database session
$sh -- a handle for a prepared statement within a session
Module handles exist to provide an unambiguous reference to a specific
installed module. Few applications will ever need to deal with module
handles directly, applications typically deal only with database and
statement handles.
In Perl 5, DBperl handles are likely to be blessed with Object Oriented
magic to give them special powers. Full details must await a Perl 5
implementation of DBperl.
5.4.3 Function Call Routing
In order for the Switch to `route' a function call to the correct
module it must be able to determine which module should handle the call.
Nearly all DBperl functions take a handle as their first parameter.
The Switch is always able to instantly determine the Module that owns
any given type of handle and uses this ability to rapidly invoke the
appropriate function in the correct Module.
5.4.4 Automatic Handle Promotions
Each Module function has a range of handles that it's willing to accept
from the Switch, e.g., &db'disconnect() will accept $dbh or $modh.
If the handle supplied by the application is too specific, e.g., a $sh
where a $dbh is required, the Switch will `promote' the handle upwards
(from $sh -> $dbh -> $modh) until within the range the function will
accept.
Handle promotions generate warnings if the perl -w flag is in effect.
5.5 Module Selection
---------------------------------------------------------------------------
5.5.1 Introduction
This section describes how the Switch selects which module to use when
an application calls the &db'connect function.
Whenever the switch requires a handle it will accept a name. This can
either be the name of a module or the name of a type of database. The
Switch will automatically try to convert the name into a corresponding
module handle using the methods described below.
Names that begin with a lowercase letter are Module names (see 5.5.2
below). Names that begin with a capital letter are treated as Database
Type names (see 5.5.3 below). Names that do not begin with a letter
generate an error.
An empty name is equivalent to the value of $ENV{'DBPERL_MODULE'}.
5.5.2 Using Module Names
If the supplied name begins with a lowercase letter then the Switch
assumes that it is an explicit module name.
The Switch checks to see if it already has a module with that name
loaded. If it does then its handle is used.
If no module with that name is already loaded then the Switch will
attempt to dynamically load a module with that name (if dynamic loading
is supported). See section 5.6 below.
The names of loaded Modules are kept on a list and checked in order. By
default newly installed modules are added to the end of the list.
Note that module selection by name is much slower than using module
handles. Also, a module name cannot identify a single instance of a
module if that module has been (dynamically) loaded more than once
(very unusual but possible). The Switch will simply pick the first
module on its list with that name. For these reasons widespread use of
module names is discouraged.
5.5.3 Using Database Type Names
If the supplied name begins with a capital letter then the Switch
assumes that it is a `database type name' (dbtype for short).
As with a module name the DBperl Switch will automatically convert the
name into a module handle, however the method it uses is different.
The Switch will `ask' each installed module in turn if it wants to
`support' this database type, the handle of the first module to accept
is returned. If no installed module accepts then the dbtype name is
converted to lowercase and treated as a module name. See 5.5.1 above.
Database type names are a simple but powerful addition to DBperl. They
open up the association between applications and modules. For example:
When you look a little into the future (say late 1994) you can
imagine a single DBperl module 'sqlcli' implemented using the ISO
Standard SQL Call Level Interface specification. This module would be
able to support a *multitude* of database types. It would accept
requests to connect to DbTypes of 'Oracle', 'Ingres', 'Informix' etc.
The dbtype mechanism allows special purpose modules to be `layered'
in between the application and the module that actually does the
work. Consider a debugging or logging module layered above a
database module. In this case the layered module would pass all
parameters and return values to and fro but would write trace
information to a log file.
See section 5.7 for further examples of layered modules.
A dbtype name can be used wherever a module name or a handle is
required. DbType names should always begin with a capital letter,
this distinguishes them from Module names which are always lowercase.
In order to gain maximum benefit from this scheme the recommended
practice is that modules be called 'ingres', 'oracle', 'sybase' etc and
that application code be written (or $DBPERL_MODULE defined) to use the
corresponding dbtype name, e.g., 'Ingres', 'Oracle', 'Sybase'.
If you DO care which specific module you use then you ask for it by
MODULE name. If you DON'T care, you ask for any suitable module by
using a DBTYPE name. E.g., If you specifically want "Dbase III" you
would ask for the module by name (say) 'dbase3'. If you don't really
care (and want to be more portable) you would ask for a module by
dbtype (say) 'Xbase'.
5.6 Module Dynamic Loading
---------------------------------------------------------------------------
5.6.1 Introduction
It is anticipated that the DBperl Switch will support the dynamic
loading of Modules. These modules may be implemented either as Perl
scripts or as dynamically linkable binary object files.
Any attempt to refer to a Module name which is not loaded will cause
the Switch to automatically attempt to find, load and initialize that
module.
5.6.2 Search Method
When searching for a external module to dynamically load the Switch
will search along a defined path which defaults to @INC (PERLLIB).
This path could be set via an environment variable DBPERL_PATH.
File names for modules: dbp*.pl for modules implemented in perl and
dbp*.o (maybe .so as well) for dynamically linkable object code.
5.6.3 Automatic Loading on Startup
DBperl provides a mechanism to automatically load modules on
startup.
To do this the user can define the DBPERL_AUTOLOAD environment variable
to either be a string that specifies what to load or is the name of a
file that contains a specification (perhaps ~/.dbperl). This will be
fully defined in a later version of this specification.
Remember that the dbtype mechanism only works for modules that are
already installed and queries them in a specific order. Using the
DBPERL_AUTOLOAD mechanism the user can force desired modules to appear
earlier in the list and hence be used in preference to others.
5.6.4 Example
Consider an application calling &db'connect and using the database type
name 'Xbase' for the module parameter. (Xbase is the name of the flat-file
database standard based on Dbase and supported by Paradox, FoxPro etc.)
Assume that you don't have any modules that accept the Xbase dbtype
statically linked into your DBperl. In this case the 'Xbase' dbtype
will fail and be converted to the module name 'xbase'. The Switch will
then try to load a module called 'xbase' and fail if one is not found.
Now, lets assume that you don't have a loadable module called xbase but
you do have one called 'dbase3' that will accept requests for the
'Xbase' dbtype. You have two choices, either copy the dbase3 module
file and call it dbpxbase.o or force the dbase3 module to be loaded
before the request for 'Xbase' is executed (using DBPERL_AUTOLOAD for
example).
5.7 Module Layering
---------------------------------------------------------------------------
5.7.1 Introduction
It is possible for one module to `bounce' calls on to another module.
In this way modules can be `layered' one on top of another.
For example, a `logging' module could pass all DBperl calls on to another
module unchanged but keep a log file of all parameters and return values.
It is also possible for a layered module to modify the apparent
behaviour of the underlying module. For example, if an ingres-to-oracle
translation module existed it could be layered above the oracle module
to allow an Ingres SQL specific application to run unaltered on an
Oracle database.
5.7.2 Examples
The following examples consider what can be done with callbacks
into perl packages.
Provide stubs that feed dummy/test data
---------------------------------------
_____________ ____________
Perl | Application || Perl Code |
---- --v--------------^-----------
C | \...Switch.../ |
---------------------------
Provide remote network access to a non-networked database
---------------------------------------------------------
_____________ _________ _______________
Perl | Application || Package | <== socket ==> | Slave Client |
---- --v--------------^-------- --v--------------
C | `... Switch ...^ | | `. Switch .. |
|________________________| |____________v__|
------------v--
| Database I/F |
---------------
Provide Perl Layer(s) for a multitude of uses
---------------------------------------------
_____________ ____________
Perl | Application || Perl Layer |
---- --v--------------^--------v--
C | \............/ | |
| ......................./ |
-v-------------------------
-v-------------
| Database I/F |
---------------
The possible uses of this sort of layering are endless, here's one:
Provide SQL Portability through SQL (etc) Translation
-----------------------------------------------------
_____________ _____________________
| Ingres || Ingres to Oracle |
Perl | Application || Translation Package |
---- --v--------------^----------------v---
C | `..............^ | |
| ................................' |
-v----------------------------------
-v-----------
| Oracle I/F |
-------------
Implement SQL Interface to Non-SQL Databases
--------------------------------------------
Recently the source code to an RDBMS with SQL support was posted to the
net, it was written entirely using /bin/sh and the unix utilities cut,
paste and join! Given that Perl database layers can be stacked upon one
another it should be possible to have a generic SQL interpreter in one layer
that could use other (non-sql) layers to gather the raw data.
_____________ _________________
| SQL || SQL Interpreter |
Perl | Application || in perl |
---- --v--------------^------------v---
C | `..............^ | |
| ............................' |
-v------------------------------
-v--------------------------------------
| Non-Query Language Module (e.g. xbase) |
----------------------------------------
End of DBperl API Section 5.
===========================================================================
6. ATTRIBUTE NAMES AND VALUES
===========================================================================
(This section was last modified on $Date: 1996/11/14 23:34:52 $
is currently at $Revision: 6.2 $ and is NOT stable.)
6.1 Introduction
---------------------------------------------------------------------------
Attributes provide an extra means of communication between an
application and DBperl modules.
They are typically used by an application to get extra information
about a DBperl entity such as a database handle or cursor. They can
also be used by an application to alter the default behaviour of DBperl
modules or database engines.
6.2 Attribute Functions
---------------------------------------------------------------------------
6.2.1 getvalue
$rv | @ary = &db'getvalue($handle, $attribute_name);
$handle -- any handle
$attribute_name -- the name of the attribute
Returns the current value of the named attribute.
The return value may be a scalar or an array depending on the type of
the attribute. Returns undefined if the attribute is not known or
not valid for the handle type given (e.g., a statement level
attribute with a module handle).
6.2.2 setvalue
$rv | @ary = &db'setvalue($handle, $attribute_name, @values);
$handle -- any handle
$attribute_name -- the name of the attribute
@values -- scalar or array to be assigned to the attribute
Sets the value of the named attribute.
Returns the previous value of the attribute to allow later
resetting. The return value may be a scalar or an array depending on
the type of the attribute. Returns undefined if the attribute is not
known or not valid for the handle type given (e.g., a statement level
attribute with a module handle).
If an invalid value is given then an error event is raised (see
section 4.2.2), the value remains unchanged and the current value is
returned.
6.2.3 Using Attributes With Other Functions
A few DBperl functions allow attribute-value pairs to be passed as optional
parameters. These functions include connect(), prepare() and install().
Generally the attributes are applied to the handle being created by the
function.
It is important to note that attributes not known by the module being
used may be ignored. If you wish to be told whenever an attribute is
being ignored you can install an event handler to either tell you or to
force an error. See section 4.
6.3 Attribute Names
---------------------------------------------------------------------------
Attribute names share a single namespace so the DBperl standard defines
how attribute names must be created so as to indicate their meaning and
scope of effect.
The letter case of attribute names is used to denote who specified the
semantics (meaning) of the attribute and it's values:
6.3.1 UPPERCASE_NAMES
These attributes have the meaning and values that are assigned to them
by various standards. The standards include X/Open and SQL92.
6.3.2 MixedcaseNames
These attributes have the meaning and values that are assigned to them
by this DBperl specification. New 'DBperl standard' attributes will be
defined by the 'DBperl standard committee' :-) via the perldb-interest
mailing list.
6.3.3 lowercase_names
These attributes have the meaning and values that are assigned to them
by the implementor of the module being used. A module implementor is
free to add any lowercase attribute names at any time. Module specific
attribute names must begin with the name of the module, e.g., oracle_*,
ingres_*.
6.3.4 Attribute Scope
An attribute has a scope of effect: some attributes apply to a module
as a whole and not to individual database sessions, other attributes
apply to a particular database session while others apply to an
individual cursor.
To reduce the risk of confusion, DBperl attribute names generally
consist of at least two words with the first word indicating the scope
of the attribute. For example: ModuleVersion, DbName, CursorName etc.
Attributes such as ROW_LENGTH, which are defined by external
standards, do not follow this naming scheme. However their meaning is
well defined and should never be ambiguous.
Not many module attributes exist. Many attributes that you may think of
as module attributes are only available for a database session handle.
This means that you will need to connect to a database before being
able to determine many attributes of the engine being used.
This is an important design decision. With time the DBperl modules will
become more generic. Eventually a single X/Open standards based module
will be able to connect to many types of database server (ingres, oracle,
sybase etc). In this case the module itself will not have any knowledge
about a particular type of server until it has connected to it.
6.3.5 Attribute Values
Boolean values should be set to 0 for false and 1 for true. Other
non-zero values may work but should not be used in order to allow for
future use.
6.3.6 Attribute Listing Legend
The following sections list the attributes currently defined by DBperl.
Each section lists the attributes for a given scope (Module, Database etc)
and the handle types that can be used ($modh, $dbh etc).
Each attribute name is followed by a series of one or more flags that
indicate the data type of the attribute value and whether the attribute is
modifiable:
S -- String type
I -- Integer type
B -- Boolean type (0 or 1)
H -- Handle type
M -- Modifyable via &db'setvalue()
6.4 Module Attributes ($modh, $dbh, $sh)
---------------------------------------------------------------------------
6.4.1 ModuleName S
Name of module that created the supplied handle.
6.4.2 ModuleAttribution S
Attribution string for the module that created the supplied handle.
6.4.3 ModuleHandle H
A handle to the module that created the supplied handle (typically a
database or statement handle).
6.5 Database Attributes ($dbh, $sh)
---------------------------------------------------------------------------
6.5.1 IDENTIFIER_LENGTH I
Returns the maximum number of characters for a user defined name.
6.5.2 IDENTIFIER_CASE S
Returns either 'UPPER', 'LOWER' or 'MIXED'.
6.5.3 ROW_LENGTH I
Returns maximum byte width of a row.
6.5.4 DbTimeZoneType S
This is a read-only attribute that attempts to describe how the
database server deals with timezones. DBperl does not use the value
itself. Returns one of the following values:
GMT = always returns the time as true GMT/UTC.
LocalServer = always returns the time as local time at the server.
LocalClient = always returns the time as local time at the client.
Transparent = does not consider timezones at all, e.g. the time value
returned is exactly the value originally inserted.
6.5.5 DbReadCache I,M
The number of rows that the module will pre-fetch and cache when
fetching from read-only cursors.
6.5.6 DbNumRows I
Returns the number of rows affected by the last operation on $dbh.
6.5.7 DbHandle H
A handle to the database associated with the supplied handle (typically
a statement handle).
! Others will be defined here in the light of experience.
6.6 Statement Attributes ($sh)
---------------------------------------------------------------------------
! To be defined in the light of experience.
6.7 Module Specific Attributes
---------------------------------------------------------------------------
Module attributes will be defined in the documentation that accompanies
the modules being used.
6.8 Switch Specific Attributes
---------------------------------------------------------------------------
These attributes only apply to the internal Switch pseudo-module. They
provide a means of communicating with DBperl directly.
When using these attributes you can avoid the need to get a handle by
using the module name (see section 5.5) , e.g., &db'getvalue('switch',...).
The name 'dbperl' may be used as an alias for 'switch'.
Generally these attributes are related to services that the Switch
provides for the other modules. These include holding global attributes
and providing debugging trace files etc.
6.8.1 DBperlPortable B,M
A global flag used by the switch and other modules to warn about
non-portable DBperl usage. See the appendix for portability issues.
6.8.2 DBperlTrace S,M
Defines DBperl debug trace level. Specification to be defined.
6.8.3 DBperlLog S,M
Specifies a filename to which DBperlTrace information should be written.
End of DBperl API Section 6.
===========================================================================
7. DATA TYPE CONVERSION FUNCTIONS
===========================================================================
(This section was last modified on $Date: 1996/11/14 23:34:52 $
is currently at $Revision: 6.2 $ and is NOT stable.)
7.1 Introduction
---------------------------------------------------------------------------
Currently DBperl does not try to address a wide range of data type issues.
Only sql string formatting and very basic date/time issues are covered.
7.2 String Formatting Functions
---------------------------------------------------------------------------
7.2.1 quote
$sqlstr = &db'quote($handle, $str);
$handle
$str -- string to be quoted as an entity
$sqlstr -- returned fully quoted sql string literal
Returns the input string suitably quoted for use as a string constant
in an SQL statement for the engine implied by $handle. This function
adds the required type of surrounding quotes and will also
escape/quote any internal characters as required.
Using literal string constants may allow a query optimizer to
generate a better query plan than would be possible if a placeholder
was used. It can also be used to reduce the number of parameters that
need to be bound using &db'execute() thus improving efficiency.
Examples:
Input string: don't
Output (oracle): 'don''t'
Output (ingres): 'don'+X'27+'t'
7.3 Date & Time Functions
---------------------------------------------------------------------------
7.3.1 Notation & Nomenclature
udt -- Unix date/time
Expressed as seconds since 1 January 1970. ALWAYS GMT/UTC.
ndt -- Native date/time
The natural datetime format of a given database (indicated by
$handle in functions). This is generally the format used by the
database when a datetime field is selected without any formatting
applied to it.
For example:
Ingres "select date('now')" => "22-Apr-1994 15:45:02"
Oracle "select ?" => "22-Apr-94 15:45:02"
The time component is optional and defaults to 00:00:00.
7.3.2 ndt2udt
$udt = &db'ndt2udt($handle, $ndt [, $local]);
$handle
$ndt -- a native datetime string
$local -- boolean, 0=ntd is GMT, 1=ntd is in local timezone
$udt -- a Unix datetime integer (GMT)
Returns a Unix datetime integer corresponding to the native date
time string value supplied.
If $local is 0 then $ntd is assumed to be in GMT timezone. If $local
is 1 then $ntd is assumed to be in the local timezone. If $local is
undefined then it is assumed to have whatever value is appropriate
for default format ntd's in the module being used, e.g., 1 for Ingres.
7.3.3 udt2ndt
$ndt = &db'udt2ndt($handle, $udt [, $local]);
$handle
$udt -- a Unix datetime integer (GMT)
$local -- boolean, 0=return GMT ntd, 1=return localtime ntd
$ndt -- a native datetime string
Returns a native datetime format string corresponding to the unix
datetime value supplied.
If $local is 0 then $ntd is returned as a GMT timezone value (also,
if the database allows it, ' GMT' will be appended to the value).
If $local is 1 then $ntd is returned as a local timezone value. If
$local is undefined then it is assumed to have whatever value is
appropriate for default format ntd's in the module being used, e.g.,
1 for Ingres.
Examples:
&db'udt2ndt($h, time, 0) => "22-Apr-1994 14:45:02 GMT"
&db'udt2ndt($h, time, 1) => "22-Apr-1994 15:45:02"
End of DBperl API Section 7.
===========================================================================
8. DATA DICTIONARY FUNCTIONS
===========================================================================
(This section was last modified on $Date: 1996/11/14 23:34:52 $
is currently at $Revision: 6.2 $ and is NOT stable.)
8.1 Introduction
---------------------------------------------------------------------------
A data dictionary holds detailed information about objects within a
database including the tables and views it contains and the columns
within those tables and views.
The DBperl specification defines three core data dictionary functions
that provide a simple but effective way to query the data dictionary of
a given database. Other utility functions may be added later.
These data dictionary functions and the values they return are based on
the X/Open SQL standard description of the INFORMATION_SCHEMA. This
defines a standard and hence portable way of representing data
dictionary information. Generally DBperl Module implementors will be
able to implement these functions by executing an SQL query on that
databases own proprietary data dictionary tables.
The functions are defined in such a way that all available attributes
can be returned. This allows for both portable applications that will
only use the standard attribute names (in uppercase and mixed case) and
non-portable applications that can use proprietary module-specific
attribute names (in lowercase).
8.2 Data Dictionary Core Functions
---------------------------------------------------------------------------
8.2.1 info_table_names
@ary = &db'info_table_names($dbh);
$dbh
@ary -- returned list of names of table and view names.
Return a list of table and view names. This will contain the names of
all the tables and views in the database to which the user has access.
It may also contain some table and view names to which the user does
not have access.
For databases that use schema names each returned table name will be
prefixed by its schema name, e.g., "SCHEMA.TABLE".
8.2.2 info_table
%attr = &db'info_table($dbh, $table_name);
$dbh
$table_name -- name of table in database to be reported upon.
%attr -- returned associative array of table attributes.
Look up data dictionary information about the specified table. For
databases that require a schema name it should be included in
$table_name, e.g., $table_name = "$schema.$table"
Associative array keys may include:
TABLE_SCHEMA -- The name of the schema containing TABLE_NAME.
TABLE_NAME -- The name of this table of view.
TABLE_TYPE -- Either 'BASE TABLE' or 'VIEW'.
REMARKS -- Descriptive information about the table.
For full definitions of these attributes, consult the X/Open standard.
See appendix for details of Module specific attributes.
8.2.3 info_col_names
@ary = &db'info_col_names($dbh, $table_name);
$dbh
@ary -- returned list of column names.
Return a list of column names for the specified table or view. For
databases that require a schema name it should be included in
$table_name, e.g., $table_name = "$schema.$table"
8.2.4 info_col
%attr = &db'info_col($dbh, $table_name, $col_name);
$dbh
$table_name -- name of table in database containing $col_name.
$col_name -- name of column in table $table_name.
%attr -- returned associative array of column attributes.
Look up data dictionary information about the specified column.
For databases that require a schema name it should be included
in $table_name, e.g., $table_name = "$schema.$table"
Associative array keys may include
TABLE_SCHEMA -- The name of the schema containing TABLE_NAME.
TABLE_NAME -- The name of this table of view.
COLUMN_NAME -- The name of the column.
DATA_TYPE -- Identifies the type (string, see below).
CHAR_MAX_LENGTH -- Max length if col is a character DATA_TYPE.
NUMERIC_PRECISION -- ...
NUMERIC_PREC_RADIX -- ...
NUMERIC_SCALE -- ...
NULLABLE -- Either 'NO' or 'YES'.
REMARKS -- Descriptive information about the column.
For full definitions of these attributes, consult the X/Open standard.
See appendix for details of Module specific attributes.
Standard DATA_TYPE values:
'CHARACTER', 'CHARACTER VARYING', 'DECIMAL', 'FLOAT',
'DOUBLE PRECISION', 'INTEGER', 'NUMERIC', 'REAL', 'SMALLINT',
'BIT', 'BIT VARYING', 'DATE', 'TIME', 'TIMESTAMP'.
End of DBperl API Section 8.
===========================================================================
9. EXAMPLES
===========================================================================
(This section was last modified on $Date: 1996/11/14 23:34:52 $
is currently at $Revision: 6.2 $ and is out of date and NOT STABLE)
This section is very incomplete and inaccurate. It will not be completed
for issue 0.5 because issue 0.6 will change much of what would be
written here.
9.1 Simple SELECT
---------------------------------------------------------------------------
$dbh = &db_connect($database, $name, $password) || &Abort;
$sh = &db_prepare($dbh, "select A, B, C from tablename");
while(@ary = &db_fetch($sh)) {
print @ary;
}
&db_finish($sh);
&db_disconnect($dbh);
9.2 Simple INSERT
---------------------------------------------------------------------------
The return value may be checked for errors.
&db_execute($dbh, "insert into table (A, B, C) values $a, $b, $c")
|| warn "INSERT failed: $db_error\n$db_errstr\n";
9.3 Iterative insert from flatfile
---------------------------------------------------------------------------
$sh = &db_prepare($dbh,
"insert into table tablename(col1, col2, col3) values (?, ?, ?)");
open(INFILE, "<$infile"); # data file of comma delimited ASCII
while(<INFILE>) {
&db_execute(&sqlh, split(/,/)) || &Abort;
}
&db_finish($sh);
9.4 Creating a temporary table
---------------------------------------------------------------------------
&db_do($dbh, "create table TMP (col1 char(12), col2 double)")
|| &Abort;
9.5 Error handling
---------------------------------------------------------------------------
Instead of doing something like:
$state = $closed;
$dbh = &db_connect(..) || &Abort("Didn't open.");
$state = $open;
$sh = &db_do($dbh, "select ...") || &Abort("Didn't open.")
...
$rc = &db_commit($dbh) || &Abort("Couldn't commit.")
...
sub Abort {
print "various warnings...";
if($state == $open) {
&db_rollback($dbh);
&db_disconnect($dbh);
}
}
The event handler allows you to do:
...
$dbh = &db_connect('foo', 'bar',, 'Handler','errhandler');
$sh = &db_execute($dbh, "select ...");
...
$sh = &db_commit($dbh);
...
sub errhandler {
local($event_name, $function_name, $handle, %params) = @_;
...
}
9.6 Bind parameter handling
---------------------------------------------------------------------------
$sh = &db_prepare("select a from x where y = ?");
while(...) {
&db_execute($sh, $conditional_value_1);
while(@_ = &db_fetch()) {
...
}
}
&db_close($sh);
End of DBperl API Section 9.
===========================================================================
10 APPENDICES
===========================================================================
(This section was last modified on $Date: 1996/11/14 23:34:52 $
is currently at $Revision: 6.2 $ and is NOT STABLE.)
10.1 Function and Variable Summary
---------------------------------------------------------------------------
3. DATABASE INTERACTION FUNCTIONS
connect $dbh = &db'connect([$database [, $username [, $password
[, $module [, %attr]]]]]);
disconnect $rc = &db'disconnect([$handle]);
prepare $sh = &db'prepare($dbh, $statement [, %attr]);
execute $rc = &db'execute($sh [, @bind_values]);
titles @ary | $rv = &db'titles($sh);
fetch @ary = &db'fetch($sh);
finish $rc = &db'finish($sh);
commit $rc = &db'commit($dbh);
rollback $rc = &db'rollback($dbh, [$savepoint_name]);
savepoint $rc = &db'savepoint($dbh, $savepoint_name);
4. ERROR AND EVENT HANDLING
errno $db'errno
errstr $db'errstr
errstate $rv = &db'errstate($dbh);
errmsg @ary = &db'errmsg($dbh);
handler $rv = &db'handler($dbh, $handler_function);
5. THE DBPERL SWITCH AND MODULES
modules @ary = &db'modules();
install $modh = &db'install($module_name [, %install_attributes ] );
call_func $rv | @ary = &db'call_func($any_handle, $function [, @params]);
6. ATTRIBUTE NAMES AND VALUES
getvalue $rv | @ary = &db'getvalue($handle, $attribute_name);
setvalue $rv | @ary = &db'setvalue($handle, $attribute_name, @values);
7. DATA TYPE CONVERSION FUNCTIONS
quote $sqlstr = &db'quote($handle, $str);
ndt2udt $udt = &db'ndt2udt($handle, $ndt [, $local]);
udt2ndt $ndt = &db'udt2ndt($handle, $udt [, $local]);
8. DATA DICTIONARY FUNCTIONS
info_table_names @ary = &db'info_table_names($dbh);
info_table %attr = &db'info_table($dbh, $table_name);
info_col_names @ary = &db'info_col_names($dbh, $table_name);
info_col %attr = &db'info_col($dbh, $table_name, $col_name);
10.2 Portability
---------------------------------------------------------------------------
! This section will be expanded and refined in the light of experience.
We have to strike a balance between focusing on known engines,
available standards (SQL-92 etc) and unknown or future systems.
If you know that feature X is supported on, say, Ingres, Oracle and
Sybase and you're only going to support your DBperl application on
those platforms then that's fine.
I guess what many of the portability statements in the DBperl
specification are saying is "A DBperl module is not REQUIRED to support
this feature, therefore it may not be supported in all modules".
This is an incomplete list of what may not be portable:
1) Interpretation of error codes/messages etc.
2) Any 'all lowercase' attribute name for &db'[gs]etvalue() etc.
3) All but the most trivial SQL strings should be regarded as suspect.
4) Any non-trivial data type related issues (money, dates, intervals).
5) Any use of &db'exec()
6) Savepoints
7) ... ?
10.2.1 Detecting and Declaring Non-Portability
Using the 'Portable' module attribute:
- The module attribute 'Portable' defaults to true for all modules.
- If the module attribute 'Portable' is true and the application
invokes a non portable action etc then the module will issue
a warning message.
- The application developer can either choose to modify the code
to make it portable or to disable the warnings by adding the
code: &db'setvalue($dbh, 'Portable', 0);
- By explicitly setting 'Portable' to false the application
developer is admitting and documenting that it's not portable.
- Anyone getting an application from the network can tell that
it's NOT portable if it has that code in it.
- Of course, if it does not have that code then it's not actually
guaranteed to be portable but you have a better chance.
Boolean Attribute Values
As for the values, I'd have to agree that '0' is not ideal.
In another message I've recommended that 1 and 0 be used for now to
allow for meanings to be attached to other values later. This is still
a valid point but I think the "TRUE" and "FALSE" would also be valuable.
TRUE and FALSE would also be handy for setting other boolean attributes.
But consider:
&db_setvalue($dbh, $boolean_attrib, 'TRUE');
print $value if ($value = &db_getvalue($dbh, $boolean_attrib));
&db_setvalue($dbh, $boolean_attrib, 'FALSE');
print $value unless ($value = &db_getvalue($dbh, $boolean_attrib));
What should/would be printed?
We must translate FALSE to 0 but should we also translate TRUE to 1?
I'd say yes. 0 and 1 are the natural perl boolean values and it
simplifies module implementation (use of ints and bit fields etc).
One last issue, what about applications that try to use strings/numbers
other than 0/1/TRUE/FALSE to set a boolean attribute?
10.3 Ideas for Utilities and Packages
---------------------------------------------------------------------
Lee McLoughlin and Tim Bunce
> } 5. Is there anything in one of the other database interfaces that you
> } would like to have in yours?
> An automatic i/o generator. Something that given a table name generates
> a format statement for output and a read routine for input.
An interesting thought. That would make a good utility library function.
> } 6. Is there any additional functionality that you would like to have?
> I'd like to be able to say something like:
> lookup( table, select_statement, assoc_arrays )
> and have the assoc arrays filled in for me with the result of the select
> statement.
So far an @ary=&db_fetchall($sqh, $separator); is as close as we have got.
This would do a push(@ary, join($separator, @field_values)) for each row
and return the array. I'd like to see a similar function for associative
arrays, but at this stage it might have just be penciled in for version 2.
A key issue (excuse the pun) is how to identify the key(s) to be
used for the array, perhaps:
@keys= (1, 2);
%ary = &db_fetch_assoc($sqh, $separator, @keys);
1) @keys indicates which fields are to be used to form the assoc key
2) Values of composite key fields are joined using $,
3) $separator is used to join the data field values
4) Assumes keys are unique else earlier rows are lost.
5) Are the key field values also stored in the data?
&db_fetch_assoc() could join &db_fetchall() as a library function.
Query-By-Forms
Something that takes a table or view name, turns it into a query page on
screen, the user fills in the blanks (by answering questions I guess) and
then returns a select statement.
A very interesting project and one I could find a lot of use for. I
might have a shot at it myself once DBperl is real and stable.
Size of: an object, tablespace (or equivalent), etc
Amount of free space left to work with (oracle in tablespace)
Privileges of the user
Efficiency of table storage (size of data stored vs disk space used)
Depth of B-Tree index
Database configuration parameters
e) Do we need functions to control:
autocommit -- on / off
locking -- level=row/page/table, readlocks, timeouts etc
f) Do we need functions to:
call database procedures
return space left in/for the database
---
=============
Apendices
-------------
10.5 Module Implementors Template
10.5.1 Example for Oracle Module
oracle_cache
10.5.2 Example for Ingres Module
-------------
End of DBperl API Section 10.