=========================================================================== 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 (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 Interbase - Michael Peppler Sybase - Tim Bunce DBI Switch - Ted Lemon 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 # # 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() { &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.