DBPerl User Interface Specification Version 4.2b2 (4/21/93) Kurt Andersen (kurt@hpsdid.sdd.hp.com) ============================================================ 0. INTRODUCTION ============================================================ 0.1 Purpose of DBperl 0.1.1 Background 0.1.2 Current Work 0.1.3 DBperl Availability 0.2 Contributors To This Specification 0.3 Structure Of The Specification Distribution 0.4 For More Information Or To Contribute Constructive Feedback ------------------------------------------------------------ 0.1 Purpose of DBperl The purpose of the DBperl API (application PERL-script interface) is to create a common set of extensions (usubs) to PERL to handle interacting with various database engines. This will allow the creation of DB-manipulation scripts without regard for the engine that is being used to service the SQL requests. 0.1.1 Background A number of independent efforts have been made to connect database engines to PERL. Interfaces currently exist for Oracle, Sybase, Interbase and perhaps others that we are not aware of. All of these interfaces have APIs that are atuned to the peculiarities of the engine back-end and thus a PERL script must be changed to work with different engines. 0.1.2 Current Work Since around October 1992, a group of interested parties, including the authors of the interfaces named above, have been working (thrashing) out an engine-independent specification for what we are calling DBperl. The specification is currently at the 4.2 revision level and is being distributed to comp.lang.perl and other interested parties who have corresponded with us. Pointer references to the specification are also being placed in various of the comp.databases.* newsgroups. DBperl (yes this is the officially recognized and approved capitalization and spelling) is currently conceived of as follows: +------+ +----------+ +-------+ +------+ |DBperl|_~API~_| DBperl |_~MPI~_|Xyzperl|_~XCI~_| XYZ | |script| ~ ~ | switch | ~|||~ |module | ~ \ ~ |engine| +------+ +----------+ ||| +-------+ \ +------+ ||| \ ||+---- Sybperl \ / Xyz |+----- Interperl +-< Call +------ whateverperl \ Interface This document covers only the API specification and will be converted into the DBperl man page at sometime in the future. 0.1.3 DBperl Availability Our collective plan is that all of the DBperl materials (specifications, switch and modules) will be distributed under the same terms and mechanisms as PERL itself. 0.2 Contributors To This Specification & Database Engine Of Interest Kurt Andersen Informix Kevin Stock Oracle Buzz Moschetti Interbase Michael Peppler Sybase Tim Bunce DBperl switch & futures Ted Lemon Ingres and a whole lot more people who have put up with the discussions and contributed from time to time. 0.3 Structure Of The Specification Distribution Because the specification is fairly long it is divided into 5 parts (trying to keep each part < 250 lines for maximum mailer compatibility): 0) Introduction to DBperl (what you're reading now). 1) DBperl preliminaries (conventions, open issues, script library, variables and utility functions) 2) DBperl database interaction API functions 3) Examples of common (?) activities 4) Future issues/directions and implementation notes If you do not receive a particular part of the distribution, you can request copies from a mail server at ***TBD*** 0.4 For More Information Or To Contribute Constructive Feedback Please contact one of the people listed above if you have specific questions about the handling of database engine peculiarities. 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 following 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 6 months and over 200 messages of more than 500kB have transpired, so the succinct version you receive will not preserve the nuances of the give-and-take that has happened. For feedback on this specification, please send that to: perldb-interest@vix.com from whence it will be echoed to the mailing list of DBperl interested parties. If you would like to be added to (or removed from) the list of interested parties, please send mail to: perldb-interest-request@vix.com to that effect. --End of DBperl API 4.2/0-- --DBperl API 4.2/1-- ============================================================ 1. CONVENTIONS & STRUCTURE ============================================================ 1.1 Document Layout: 1. Conventions & Structure 2. Open issues not addressed/resolved in this version 3. DBPerl Library Contents 4. Variables 4.1 PERL variables 4.2 Environment Variables 5. Functions 5.1 Simple (obviously a relative term) 5.2 Utility 5.3 Datatype Functions 5.4 DB Interaction 6. Examples 7. Future Issues & Directions 8. Implementation Notes 9. Other Concluding Notes 1.2 Each function is documented with the following structure: a. Call Structure b. Parameter Definition c. Action d. Return Value(s) e. Event Types Generated f. Named Attributes g. Other Notes (as applicable) 1.3 Terms The terms defined below refer to the following diagram: +------+ +----------+ +--------+ +------+ |DBperl|_~API~_| DBperl |_~MPI~_| xyz |_~XCI~_| xyz | |script| ~ ~ | switch | ~|||~ | module | ~ \ ~ |engine| +------+ +----------+ ||| +--------+ \ +------+ ||| \ ||+---- Sybmodule \ / Xyz |+----- Intermodule +-< Call +------ whatevermodule \ Interface Library A package (in PERL terms) of utility functions to aid the DBperl user in performing common activities. API -- application PERL-script interface The call interface and variables provided by DBperl to PERL scripts via the usub mechanism. MPI -- module programmer's interface A de-PERLized interface to modules provided by the DBperl switch. This interface is specified in a separate document at a C-call level. Module programmers should not need to know much if anything about PERL usub programming to implement a module. module Modules handle the mediation between the DBperl switch and engine call interfaces. engine The actual RDBMS "engine" that is being communicated with by a module. (This definition does not strictly hold in some of the future possibilities that have been envisioned, cf section 7 below.) switch An interface handler that converts between the PERL stack and the MPI to enforce interpretation and consistency of this API and to handle dynamic loading/linking of modules in a seamless manner. Also provides independence from the PERL usub mechanism for module programmers (especially important if it changes with perl 5.0!). bundle The actual collection of DBperl switch and DBperl modules which are built and installed on the system where the PERL script is being executed. handle Referred to below, a handle should be treated as an opaque data structure pointer that is used for identification to the API between calls to it. 1.4 Notation 1.4.1 Return values noted as $rc or a handle evaluate (in PERL) to boolean false on an error condition to facilitate short-cut chaining. 1.4.2 Return values noted as $rv do not necessarily evaluate as false on an error condition. 1.4.3 Return values noted as @ary return an array of values. 1.4.4 "Handles", if defined, are assumed to be active. 1.4.4.1 $modh designates a handle to a database module and is interchangeable (execpt in exotic cases) with the name of the engine(s) to which it connects. 1.4.4.2 $dbh designates a handle to a database connection and presumes a module designation. 1.4.4.3 $sqh designates a handle to an SQL statement (multi- statements tied to a single handle are not supported) and presumes a $dbh to which it is connected. 1.4.4.4 $hh designates either a $sqh, $dbh or $modh. 1.5 General Interface Rules & Caveats 1.5.1 Data is returned at to the perl script as character strings. This allows arbitrary precision representations within the scope of the engine/module to be handled without loss of accuracy. Beware that PERL conversions and other engines/modules may not preserve the same accuracy. 1.5.2 Dates and times are returned as character strings representing the number of seconds since 1 January 1970. Timezone effects are engine/module dependent and can be deduced (and in some cases affected) from (by) parameters available from &db_getopt(), q.v. 1.5.3 Multiple SQL statements may not be combined in a single execution bundle (on a single $sqh). 1.5.4 Binary data types are not supported by DBperl. 1.5.5 Handle overloading is allowed in that any function specified to accept a $modh parameter could also accept a $hh in that position to indirectly specify the engine. Also, any function that calls for a $dbh parameter can accept a $sqh to specify the $dbh indirectly. 1.5.6 The name space '[$%&]db_*' is reserved for DBperl functions and variables. Scripts using identifiers in this name space may experience compatibility problems with future versions of the DBperl API. All environment variables used by the DBperl switch or modules match 'DBPERL_*'. ============================================================ 2. OPEN ISSUES ============================================================ 2.1 The contents of the DBPerl Library are subject to expansion. Please let me know what other common patterns of SQL execution you think would be valuable to include for users who pick up the DBPerl package and API. 2.2 Other suggested values to be accessible via functions 5.2.2 and 5.2.3 are requested. 2.3 How to handle intervals in a distinct manner from dates and times is yet to be defined. ============================================================ 3. DBPERL LIBRARY CONTENTS ============================================================ 3.0 Description The DBPERL library is a proposed set of PERL libary functions which combine basic usubs functions into common patterns. 3.1 Functions 3.1.1 Single line (row) SELECT 3.1.1a) @ary = &db_lookup($dbh, $statement [, @bind_vars]); 3.1.1b) ***TBD*** 3.1.1c) ***TBD*** 3.1.1d) ***TBD*** 3.1.1e) ***TBD*** 3.1.1f) ***TBD*** 3.1.2 Return all data from a SELECT, losing NULL distinctions 3.1.2a) @ary = &db_fetchall($sqh, $separator); 3.1.2b) ***TBD*** 3.1.2c) ***TBD*** 3.1.2d) ***TBD*** 3.1.2e) ***TBD*** 3.1.2f) ***TBD*** 3.1.3 Lookup all tables in the database with attributes 3.1.3a) $rc = &db_info_table_all($dbh, $funct); b) $dbh -- see 1.3.4.1 $funct -- a user function to be called with the attributes of each table found in the $dbh. sub build_table_catalog { local(%table_attribs) = @_; ... } 3.1.3c) Calls the user supplied function with the attributes of each table in turn. This allows a portable method of building a data dictionary. 3.1.3d) ***TBD*** 3.1.3e) ***TBD*** 3.1.3f) ***TBD*** 3.1.3g) See also, section 5.5 below. 3.1.4 Lookup all columns in a table with attributes 3.1.4a) $rc = &db_info_col_all($dbh, $tab_name, $funct); b) $dbh -- see 1.3.4.1 $tab_name -- the name of the table for which columns will be returned. $funct -- a user function to be called with the attributes of each column found in the specified $dbh and $tab_name. sub build_col_catalog { local(%col_attribs) = @_; ... } 3.1.4c) Calls the user supplied function with the attributes of each column in turn. This allows a portable method of building a data dictionary. 3.1.4d) ***TBD*** 3.1.4e) ***TBD*** 3.1.4f) ***TBD*** 3.1.4g) See also, section 5.5 below. ============================================================ 4. VARIABLES ============================================================ 4.1 Perl Variables ------------------------------------------------------- 4.1.1 $db_errno -- integer, the error code returned by the last database access operation (functions defined in section 5.4). 4.1.2 $db_errstr -- string, the status message associated with the last database access operation, c.f. 4.1.1. This value will be set upon variable access only. 4.2 Environment Variables ------------------------------------------------------- 4.2.1 DBPERL_MODULE -- defines the default module to use 4.2.2 DBPERL_DBASE -- default database name to connect to 4.2.3 DBPERL_PATH -- the path used by the DBperl switch in looking up modules to dynamically link/load (cf, section 7). 4.2.4 DBPERL_AUTOLOAD -- defines dynamically linked modules to be loaded by the DBperl switch and precedence for activation (cf, section 7). ============================================================ 5. FUNCTIONS ============================================================ 5.1 Simple Functions ------------------------------------------------------- 5.1.1a) $rc = &db_version([$db_modeng]); b) $db_modeng -- an optional quoted string with the following legal values (case insensitive): 'dbperl' or any supported engine or module name. c) Returns the title and version number of the requested item. If $db_modeng is omitted, the default is 'dbperl' and refers to the interface specification of the DBperl API. d) String. Example: "DBperl Version 4.2". Format should match m/(\w+) Version ([\d\.]+)/ 5.1.2a) @ary = &db_engines(); b) No parameters. c) Returns an array of db_engine names which can be accessed by the DBperl bundle. d) Array of strings. Example: ('Oracle', 'Informix'). 5.1.3a) @ary = &db_modules(); b) No parameters. c) Returns an array of db_module names which are presently loaded in the DBperl bundle. d) Array of strings. Example: ('oramodule', 'infomodule'). 5.1.4a) @ary = &db_attribution([$db_modeng]); b) $db_modeng -- an optional quoted string with the following legal values (case insensitive): 'dbperl' or any supported engine or module name. c) Returns the attribution information about a particular item. If $db_modeng is omitted, the default is 'dbperl' and refers to the interface specification of the DBperl engine. By attribution, I mean the type of information returned by "perl -v". d) Array of strings. Example: see "perl -v". 5.1.5a) $rv = &db_status([$dbh]); b) $dbh -- see 1.3.4.1 c) Returns the status of the last operation performed on the designated handle. If no handle is designated, reports the status of the last operation. d) 'SUCCESS' | 'WARN' | 'NODATA' | engine dependent error number Note that only error evaluates (&db_status($dbh) != 0) true. e) Return values follow X/OPEN guideline (***quotation***). "Operation"s are limited to the functions listed in section 5.4. 5.1.6a) $rc = &db_rtncode([$dbh]); b) $dbh -- see 1.3.4.1 c) Returns the result code of the last operation performed on the designated handle. If no handle is designated, returns the result code of the last operation. d) Integer: engine dependent error/result code e) "Operation"s are limited to the functions listed in section 5.4. 5.2 Utililty Functions ------------------------------------------------------- 5.2.1a) $rc = &db_errormsg([$rc [, $dbh]]); b) $rc is an optional result code integer (cf 5.1.6). $dbh -- see 1.3.4.1 c) Returns the message string associated with the result code for the selected $dbh. If no $dbh is specified, the $dbh referenced by the last operation is used. If no result code is specified, the message for the last operation performed on the designated handle is returned. d) String: result code message e) "Operation"s are limited to the functions listed in section 5.4. 5.2.2a) $rv | @ary = &db_getvalue($dbh, $attribute_name); b) $dbh -- see 1.3.4.1 $attribute_name -- a quoted string designating the name of the attribute value to set. Note that the case used for $attribute_name denotes who specified the semantics of the value: UPPERCASE -- X/Open, SQL92 etc defined semantics Mixedcase -- DBperl defined semantics lowercase -- db_engine or module specific semantics c) Returns the requested value. If the requested value is not supported, returns undefined. d) Depending on the requested value, may be a single value or an array, see below. e) Note: Some "values" may be tied to a db_module rather than to the $dbh. In those cases, the engine is implied by the $dbh. Note that a db_module is not obliged to implement all of these, but where the required semantics match a defined attribute name, that name should be used. Suggested standard values: 'IDENTIFIER_CASE' -- string, either 'UPPER', 'LOWER' or 'MIXED'. 'IDENTIFIER_LENGTH' -- integer, maximum number of characters for a user defined name. 'ROW_LENGTH' -- integer, maximum byte width of a row. 'TXN_ISOLATION' -- integer, one of: 0, 1, 2, 3. Suggested DBperl values: 'Nrows' -- integer, number of rows affected by the last operation on $dbh. 'Debug' -- string, debug level of the DBperl switch. 'Databases' -- array of strings: names of accessible databases. The presence of a db in the returned list implies (barring network failure) that a connection can currently be made to it with an appropriate user/passwd combination. 'Savepoint' -- defined true if savepoints are supported. 'FetchRandom' -- defined true if &db_fetch() optional parameters are supported. 'ModuleName' -- name of module affiliated with the handle. 'ModuleHandle' -- a handle to the module affiliated with the handle. 'DatabaseName' -- name of database affiliated with the handle. 'DatabaseHandle' -- a handle to the database affiliated with the handle. 'StatementType' -- the type of SQL statement: 0 indicates a SELECT statement, other values are engine dependent. 'EngineName' -- name of engine affiliated with the handle. 'TimeZoneType' -- returns one of the following values to indicate the handling of date and time information: 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. Example db_module values: 'sqlca' -- (Informix) array 'debug' -- (all) string, debug level of the db_module. 'cache' -- (Oracle) ? 'txtype' -- (Interbase) ? 5.2.3a) $rv | @ary = &db_setvalue($dbh, $attribute_name, $value | @ary); b) $dbh -- see 1.3.4.1 $attribute_name -- a quoted string designating the name of the attribute value to set. See 5.2.2b $value | @ary -- the value to be assigned. c) Sets the requested value. If the requested value is not supported, returns undefined. d) Returns the previous value to allow later resetting. Dependent on the requested value, may be a single value or an array. e) Note: Some "values" may be tied to a db_engine rather than to the $dbh. In those cases, the engine is implied by the $dbh. Not all values supported by 5.2.2 may be 'settable'. Suggested settable values: 'Debug' -- (all) string, debug level of the switch. 'debug' -- (all) string, debug level of the module. 'cache' -- (Oracle) ? 'txtype' -- (Interbase) ? 5.2.4a) $rv | @ary = &db_runfunc($modh, $funct [, @params]); b) $modh -- see 1.3.4.1 $funct -- a module specific function name @parms -- function specific parameters c) Executes the requested function (passing it $hh and @params) and returns the result thereof. d) Dependent on the function specified. e) This is the recommended route for module-specific functions to be accessed via the DBperl specification. 5.2.7a) $qtstr = &db_quote($hh, $str); b) $dbh -- see 1.3.4.1 $str -- string to be quoted as an entity c) Returns the input string suitably quoted for use as a string constant in an SQL statement for the engine implied by $hh. This function adds the required type of surrounding quotes and will also escape/quote any internal characters as required. d) String. 5.2.8) -- deleted -- 5.2.9a) $rv = &db_handler($db_module, $handler_function); b) $db_engine -- an installed db_module $handler_function -- a function to be called on certain events/conditions in DB interaction functions for this engine. See 5.2.10 for specification of handler_function interface. c) Installs the requested PERL function as an event handler. d) String. Returns the name of the previous handler function. This allows handlers to be pushed and popped on a stack. Also a new handler could call the previous handler to deal with any event types it did not wish to handle. e) Being an event handler (rather than just an error handler) and passing an $event_type allows events such as messages or alerts etc to be supported within the DBperl specification. The actual circumstances of invocation and the semantics of any return values still need to be defined. 5.2.10a) sub example_handler { local($event_name, $function_name, $hh, %params) = @_; ... } b) $event_name -- Event which triggered this call (eg, ERROR, WARN, SUCCESS). Possible events are listed in subsection 'e' of each function definition in section 5.4. $function_name -- DB access function (section 5.4) being called when the event occurred. $hh -- The handle passed to the function. %params -- An associative array, the contents of which are defined in conjunction with each relevant function. 5.3 Date & Time Functions ------------------------------------------------------- 5.3.0 Notation & Nomenclature Unix date/time seconds since 1 January 1970 with timezone issues identified via &db_getvalue(...'Timezonetype'). Native date/time native datetime of a given database (indicated by $hh in functions) Precision is expressed as a string with one of the following values: 'year','month','day','hour','minute','second','millisecond' 5.3.1 Functions a)1) $udt = &db_ndt2udt($hh, $ndt [, $low [, $high]]); 2) $ndt = &db_udt2ndt($hh, $udt [, $low [, $high]]); b) $hh -- see 1.3.4.3 $udt -- a Unix date/time value $ndt -- a native date/time value $low -- the smallest degree of precision desired in the output $high -- the highest degree of precision desired in the output c) Converts a date/time value to the desired notation. d) The date/time in the new notation. e) No timezone or locale adaptations are performed. --End of DBperl API 4.2/1-- --DBperl API 4.2/2-- 5.4 DB Interaction Functions (Operations) ------------------------------------------------------- Note that the last $dbh used by these functions is remembered and used as the default $dbh for some of the utility functions described above. Where an $sqh is used that implies a $dbh. 5.4.1a) $dbh = &db_connect([$database [,$name [,$passwd [,$db_module [,%attr]]]]]); b) $database -- a database name $name -- name by which to connect to the database $passwd -- password by which to connect $db_module -- module to use in connecting %attr -- associative array of additional attributes that can be setup with this connection c) Establishes a database connection to the requested database. If a database is not specified, uses the environment variable value (4.2.3). If $db_module is not specified uses the environment variable value (4.2.1). If that variable is not set and the dbperl supports more that one module then undef is returned. The current username is used as the default value for $user. d) A database connection handle or undef on error. e) ERROR WARN f) 'RetryN+' -- set the number (N+) of retries to be attempted by the event handler. The value element of this pair is either a keyword ('ALL', 'ERROR', 'WARN') or a database specific list of error codes (comma separated string) on which retries will be attempted. 'Handler' -- establish the event handler for this connection. 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. 5.4.2a) $rc = &db_do($dbh, $statement [,%attr]); b) $dbh -- a database handle $statement -- a non-SELECT statement with no BIND parameters %attr -- associative array of additional attributes that can be set on this statement c) Immediately executes $statement. d) Boolean e) ERROR WARN f) 'RetryN+' -- set the number (N+) of retries to be attempted by the event handler. The value element of this pair is either a keyword ('ALL', 'ERROR', 'WARN') or a database specific list of error codes (comma separated string) on which retries will be attempted. 5.4.3a) $sqh = &db_prepare($dbh, $statement [,%attr]); b) $dbh -- a database handle $statement -- a SQL statement %attr -- associative array of additional attributes that can be set on this statement c) Create a handle and pre-process the SQL statement as needed prior to execution. d) A SQL handle e) ERROR WARN f) 'cache' -- set Oracle cache size 'RetryN+' -- set the number (N+) of retries to be attempted by the event handler. The value element of this pair is either a keyword ('ALL', 'ERROR', 'WARN') or a database specific list of error codes (comma separated string) on which retries will be attempted. g) The SQL statement may be any single statement with or without bind variables. 5.4.4a) $rc = &db_bind($sqh, $parm1 [, ... $varn]); b) $sqh -- a SQL handle $parm1..n -- bind parameters NULL values are indicated by undefined values (undef). c) Perform whatever processing is necessary to connect the bind variables to the referenced SQL statement. d) Interpretation of return code values: '0' = Error, use &db_rtncode() and/or &db_errormsg(); 'SUCCESS' = No rows (or an unknown number) affected.; 'WARN' = Warning with an unknown number of rows affected. integer>0 = Number of rows affected (success) e) ERROR WARN f) N/A g) Note that it is not possible to identify the number of rows affected if there was a warning, use &db_getvalue(). If the statement is not a SELECT statement, &db_bind() will cause the execution of the statement. Otherwise, a SELECT statement will be in a state to run &db_fetch() after &db_bind(). 5.4.5a) @ary = &db_fetch($sqh [,%attr]); b) $sqh -- a SQL handle %attr -- associative array of additional attributes that can be set on this statement c) If KEY is not specified, return the next row of data. If KEY [and POSITION] are specified and random fetches are supported return the specified row of data. If KEY [and POSITION] are specified and random fetches are not supported, return undefined. d) Array, NULL values are indicated by undefined values (undef) in the returned array. e) ERROR WARN NO_MORE_ROWS f) KEY -- 'first' | 'next' | 'last' | 'relative' POSITION -- integer, required if KEY is 'relative' ignored otherwise 'RetryN+' -- set the number (N+) of retries to be attempted by the event handler. The value element of this pair is either a keyword ('ALL', 'ERROR', 'WARN') or a database specific list of error codes (comma separated string) on which retries will be attempted. 5.4.6 -- collapsed into 5.4.5 -- 5.4.7a) $rc = &db_finish($sqh [,%attr]); b) $sqh -- a SQL handle %attr -- associative array of additional attributes that can be set on this statement c) Release all resources (and locks etc) being used by $sqh. After this call, $sqh will not be valid. d) Boolean e) ERROR WARN f) 'RetryN+' -- set the number (N+) of retries to be attempted by the event handler. The value element of this pair is either a keyword ('ALL', 'ERROR', 'WARN') or a database specific list of error codes (comma separated string) on which retries will be attempted. g) This is equivalant to closing a cursor in many database types. 5.4.8a) $rc = &db_disconnect([$dbh | $db_modeng [,%attr]]); b) $dbh -- an optional database connection handle $db_modeng -- an optional installed db_module or engine %attr -- associative array of additional attributes that can be set on this statement c) Release all resources being used in conjunction with the specified handle, module or engine. If a module is specified, all connections via that module will be closed and, if dynamic loading is implemented, the module will be released. If an engine is specified, all connections with that type of engine will be closed. If no parameter is specified, all modules will be detached. d) Boolean e) ERROR WARN f) 'RetryN+' -- set the number (N+) of retries to be attempted by the event handler. The value element of this pair is either a keyword ('ALL', 'ERROR', 'WARN') or a database specific list of error codes (comma separated string) on which retries will be attempted. 5.4.9a) $rc = &db_starttx($dbh [,%attr]); b) $dbh -- a database connection handle %attr -- associative array of additional attributes that can be set on this statement c) Perform any processing needed to mark the start of a transaction. If transactions are not supported by the engine, returns undefined. d) Boolean e) This function only need be called for the few database types which do not automatically start transactions (Informix etc). e) ERROR WARN f) 'RetryN+' -- set the number (N+) of retries to be attempted by the event handler. The value element of this pair is either a keyword ('ALL', 'ERROR', 'WARN') or a database specific list of error codes (comma separated string) on which retries will be attempted. 5.4.10a) $rc = &db_savepoint($dbh, $savepoint_name [,%attr]); b) $dbh -- a database connection handle %attr -- associative array of additional attributes that can be set on this statement c) Perform any processing needed to mark a savepoint within a transaction. If savepoints are not supported by the engine, returns undefined. d) Boolean e) ERROR WARN f) 'RetryN+' -- set the number (N+) of retries to be attempted by the event handler. The value element of this pair is either a keyword ('ALL', 'ERROR', 'WARN') or a database specific list of error codes (comma separated string) on which retries will be attempted. 5.4.11a) $rc = &db_commit($dbh [,%attr]); b) $dbh -- a database connection handle %attr -- associative array of additional attributes that can be set on this statement c) Perform any processing needed to commit a transaction. If transactions are not supported by the engine, returns undefined. d) Boolean e) ERROR WARN f) 'RetryN+' -- set the number (N+) of retries to be attempted by the event handler. The value element of this pair is either a keyword ('ALL', 'ERROR', 'WARN') or a database specific list of error codes (comma separated string) on which retries will be attempted. 5.4.12a) $rc = &db_rollback($dbh [,%attr]); b) $dbh -- a database connection handle $savepoint_name -- the name of a savepoint %attr -- associative array of additional attributes that can be set on this statement c) Perform any processing needed to rollback a transaction If transactions are not supported by the engine, returns undefined. If a savepoint is specified, but not supported by the engine, the entire transaction is rolled back and the function returns undefined. d) Boolean e) ERROR WARN f) SAVEPOINT -- savepoint to be rolled back to 'RetryN+' -- set the number (N+) of retries to be attempted by the event handler. The value element of this pair is either a keyword ('ALL', 'ERROR', 'WARN') or a database specific list of error codes (comma separated string) on which retries will be attempted. 5.4.13a) @ary = &db_titles($sqh [,%attr]); b) $sqh -- a SQL handle %attr -- associative array of additional attributes that can be set on this statement c) Lookup column names d) Array of strings: the names of the data being returned e) ERROR WARN f) 'RetryN+' -- set the number (N+) of retries to be attempted by the event handler. The value element of this pair is either a keyword ('ALL', 'ERROR', 'WARN') or a database specific list of error codes (comma separated string) on which retries will be attempted. g) If the engine does not provide this information then the implementation will return ('col1','col2','col3') etc. 5.5 Data Dictionary Functions (Operations) ------------------------------------------------------- Note that the functions defined in this section are also considered to be DB interaction functions for the purposes of error handling. 5.5.1a) %attr = &db_info_table($dbh, $table_name); b) $dbh -- a database connection handle $table_name -- the name of a table in the database to be reported upon. c) Look up schema information about the specified table. d) Associative array, keys will include TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE and REMARKS. For definitions of these attributes, consult the X/Open standard. e) ERROR WARN f) N/A 5.5.1a) %attr = &db_info_col($dbh, $table_name, $col_name); b) $dbh -- a database connection handle $table_name -- the name of a table in the database in which the column of interest exists. $col_name -- the name of a column in the table to be reported upon. c) Look up schema information about the specified column. d) Associative array, keys will include TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHAR_MAX_LENGTH, NUMERIC_PRECISION, NUMERIC_PREC_RADIX, NUMERIC_SCALE, NULLABLE and REMARKS. For definitions of these attributes, consult the X/Open standard. e) ERROR WARN f) N/A --End of DBperl API 4.2/2-- --DBperl API 4.2/3-- ============================================================ 6. EXAMPLES ============================================================ 6.1 Simple SELECT ------------------------------------------------------- ... $dbh = &db_connect($database, $name, $password) || die "couldn't"; $sqlh = &db_prepare($dbh, "select A, B, C from table"); while(@ary = &db_fetch(&sqlh)) { print @ary; } &db_finish($sqlh); # optional, will be performed upon disconnect &db_disconnect($dbh); 6.2 Simple INSERT ------------------------------------------------------- The return handle 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"; ... 6.3 Iterative insert from flatfile ------------------------------------------------------- ... $sqlh = &db_prepare($dbh, "insert into table $a(col1, col2, col3) values (?, ?, ?)"); open(INFILE, "<$infile"); # Incoming data file is regular ol' bar-separated ASCII: while() { &db_bind(&sqlh, split(/\|/)); } &db_finish($sqlh); ... 6.4 Creating a temporary table ------------------------------------------------------- The return handle may be checked for errors. ... &db_do($dbh, "create table TMP (SecId char(12), Coupon double)") || warn "TMP table creation failed: $db_error\n$db_errstr\n"; ... 6.5 Error handling ------------------------------------------------------- Instead of doing something like: $state = $closed; $dbh = &db_connect(..) || &dbexit("Didn't open."); $state = $open; $rc = &db_starttx($dbh); $sqlh = &db_do($dbh, "select ...") || &dbexit("Didn't open.") ... $rc = &db_commit($dbh) || &dbexit("Couldn't commit.") ... sub dbexit { 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'); $rc = &db_starttx($dbh); $sqlh = &db_execute($dbh, "select ..."); ... $sqlh = &db_commit($dbh); ... sub errhandler { local($event_name, $function_name, $hh, %params) = @_; ... } 6.6 Bind parameter handling ------------------------------------------------------- ... $sqlh = &db_prepare("select a from x where y = ?"); while(..) { &db_bind($sqlh, $conditional_value_1); while(@_ = &db_fetch()) { ... } &db_close($sqlh); } 6.7 Multi-table computations w/ cursor manipulation ------------------------------------------------------- Scenario: 2 database tables, one of which contains computed information derived from the second table (let's just say the the computations are sufficiently involved to only want to do them once, for a simple example I'll use an average and standard deviation). The Goal: A DBperl program that will lookup the records in table 1 that need to have the average and std. dev. computed; for each record, retrieve the appropriate rows from table 2, compute the desired values, and update the record in table 1 with the computed values. Assume the two tables have the following fields: Table 1: rec_type average std_dev Table 2: rec_type value Here's a possible solution in DBperl (less error checking): $dbh = &db_connect('database', 'username', 'password'); $sql1 = &db_prepare($dbh, <<, 'UPDATE', 'cursor1'); select rec_type from table_1 for update of (average, std_dev) $sql2 = &db_prepare($dbh, <<); select value from table_2 where rec_type = ? $sql3 = &db_prepare($dbh, <<); update table_1(average, std_dev) values (?, ?) while (($rec_type) = &db_fetch($sql1)) { &db_bind($sql2, $rec_type); while (($value) = &db_fetch($sql2)) { ++$nvalues; $total += $value; # don't know how to calculate std_dev; sorry! } $nvalues = 1 if $nvalues == 0; # avoid div by 0 &db_bind($sql3, $total / $nvalues, $std_dev, 'CURRENT OF', 'cursor1'); } &db_close($sql1); &db_close($sql2); &db_close($sql3); &db_disconnect($dbh); *** Not massaged past this point *** 8. Date manipulations Some examples, to make this clearer: 1) Display the current date in native and standard format, and in the native format of all currently supported database types: # Is this method of getting the date standard? I don't know. $sqlh = &db_open($dbh, 'select sysdate from dual'); ($date) = &db_fetch($sqlh); &db_close($sqlh); $std_date = &db_todate($date); # get standard date print 'native format is', $date; print 'standard format is', $std_date; foreach $db (&db_getvalue('databases')) { print $db, 'format is', &db_fromdate($std_date, $db); } 2) Copy records from one table to another, within a single engine: $get = &db_open($dbh, 'select date_field, num_field from table'); $put = &db_open($dbh, 'insert into othertable values(?, ?)'); while (($date, $num) = &db_fetch($get)) { &db_bind($put, $date, $num); } &db_close($get); &db_close($put); 3) As (2), but different engines: $get = &db_open($dbh_i, 'select date_field, num_field from table'); $put = &db_open($dbh_o, 'insert into othertable values(?, ?)'); while (($date, $num) = &db_fetch($get)) { &db_bind($put, &db_convdate($date, $dbh_i, $dbh_o), $num); } &db_close($get); &db_close($put); &db_convdate() is obviously defined as: sub db_convdate { local($date, $infmt, $outfmt) = @_; &db_fromdate(&db_todate($date, $infmt), $outfmt); } --End of DBperl API 4.2/3-- --DBperl API 4.2/4-- Futures... ** What Is Possible... It's still a little early to go into too much detail but I thought this might be of interest... Without any changes to the DBperl spec (other than the addition of an optional &db_install() function) a great deal is now possible. Ignoring facilities like auto-dynamic loading on demand, the following examples only consider what can be done with callbacks into perl packages (a favourite of mine): Provide stubs that feed dummy/test data --------------------------------------- _____________ ____________ Perl | Application || Perl Code | ---- --v--------------^----------- C | \............/ | --------------------------- Provide remote network access to a non-networked database --------------------------------------------------------- _____________ _________ _______________ Perl | Application || Package | <== socket ==> | Slave Client | ---- --v--------------^-------- --v-------------- C | `... DBperl ...^ | | `. DBperl .. | |________________________| |____________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 | ------------- Wild ideas: Being able to implement database interfaces in perl should encourage many people to implement new (non-sql) databases using the DBperl interface specification. Consider NIS, DNS, X.500, SNMP etc. 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 might be possible to have a generic SQL interpreter in one layer that could use other (non-sql) layers to gather the raw data. Cute! :-) Of course, as the database side gathers momentum, developers of application utilities such as forms generators are more likely to add direct support for DBperl. I can see Perl/4GL on the horizon! Regards, Tim. For reference here's the relevant part of my previous definition: ! * A new (and much improved ;-) definition of modules and handles: ! ! Firstly, there will always be a need for an opaque module handle that ! provides a rapid and unambiguous reference to a specific installed module. ! Few people will ever have a need to deal directly with more than one module ! in an application. Those applications that require explicit control can use: ! ! $modh = &db_install($module_name [, %install_options ] ); ! ! to install a named module and make it available for use: ! $module_name is the name of the module to install, and the optional ! %install_options provides a means to control the installation and ! supply parameters to the module as it initialises. ! ! The module name and module parameter names are always lowercase (important). ! If dynamic loading is supported and the module is not already installed ! then the DBperl Switch will search for and load either an object file ! or perl implementation of the module. Module names are unique (important). ! Note that most applications will NEVER NEED to call this function directly. ! ! The good stuff: ! ! A module name can be used _wherever_ any type of handle is required, ! e.g. $attribution = &db_getvalue('oracle', 'Attribution'); ! The DBperl Switch will *automatically* convert the name into a module ! handle (including installing the module if required). ! ! The DBperl Switch can also use a 'database type name' (dbtype for short). ! A dbtype name can be used wherever a module name or a handle is required. ! A dbtype name always begins with a capital letter (important). ! 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 dbtype, 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. ! ! An empty string is equivalent to $ENV{'DBPERL_MODULE'}. Note that ! this could be defined to be a specific module name (e.g. 'oracle') ! or a dbtype name (e.g., 'Oracle'). ! ! The dbtype mechanism enables some advanced uses of DBperl such as the ! ability to 'layer' modules by having one module 'intercept' and 'accept' ! a &db_attatch() request that would normally be dealt with by another module. ! ! 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'. ! > I think that the loading/access of modules by the switch needs to be > something more that just alphabetic (sort of implied by some of Tim's > statements last week). If multiple modules can handle access to (let's > say) an XBase database, some reasonable mechanism needs to be in place > to provide the "expected" module, not just the first one that says, "I > can do it." > If you DO care which module you use then you'd ask for it by MODULE name. If you DON'T care, you would ask for any 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'. > I have been mulling over several possible ideas on how to handle this > situation (module defined priority tags, installation designated priorities, > environmentally defined priorities) a satisfactory approach is still > eluding me. > This is where it get more interesting. I've tried to avoid addressing these peripheral issues that don't impact on the core spec, preferring to leave these till we could get some implementation experience. I'll try to outline my thoughts below. > Consider the following scenarios in thinking about the issue: > > 1) The user wishes to access an oracle database. The DBperl available > to him has modules for Oracle Rev 2, Oracle Rev 5, Ingres->Oracle, > Sybase->Oracle, Oracle->Ingres, Oracle->Sybase, and a remote engine > handler. What gets loaded when he runs: > &db_attach ('dbfoo',,,'oracle'); ? > A name that is all lowercase is an explicit module name. So, in this case only the module called 'oracle' will be loaded/used. If the example had used a dbtype name, e.g., 'Oracle' then things get more interesting. See below. > 2) How does the user specify the Oracle Rev 2 module if that is what > he specifically wants? > As above, he would refer to it by name (lowercase), say 'oraclev2'. > See the problem? Any suggestions? > My current thoughts on some significant points: 1) Modules are queried (for &db_attach) in a defined order. By default newly installed modules would be added to the end of the query list. 2) Generally 'fancy' modules would not be statically linked. I see most 'fancy' modules, such as translators, being implemented in perl. 3) 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. 4) File names for modules: dbp*.pl for modules implemented in perl and dbp*.o (maybe .so as well) for dynamically linkable object code. 5) It must be possible to preload modules in a specific order and with specific installation parameters. 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 (~/.dbperl :-) 6) 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. The key point here is that the dbtype mechanism only works for modules that are *already* installed. If no installed module accepts that dbtype then the dbtype name is converted to lowercase and treated as a module name. So 'Oracle' becomes 'oracle' and all is (hopefully) well. But, lets consider the 'Xbase' dbtype. 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 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). Does that make sense? Please note that none of this really affects the DBperl spec as currently defined. It's all an extension. For now you could even just state that module names are lowercase and leave it at that. Feel free to put this stuff in the Futures section but I really don't want to start a major debate on the details that might hold up the spec. --End of DBperl API 4.2/4-- --End of DBperl API 4.2-- Function Summary: Notes: .