This is Info file pm.info, produced by Makeinfo version 1.68 from the input file bigpm.texi.  File: pm.info, Node: DBD/Ovrimos, Next: DBD/Recall, Prev: DBD/JDBC, Up: Module List DBI Driver for Ovrimos (formerly Altera SQL Server) *************************************************** NAME ==== DBD::Ovrimos - DBI Driver for Ovrimos (formerly Altera SQL Server) SYNOPSIS ======== use DBI; my $dbh=DBI->connect( "dbi:Ovrimos:some.host.com:2500", "user", "passwd") or die "Cannot connect\n"; # more DBI calls... DESCRIPTION =========== DBI driver for Ovrimos (See `DBI(3)' in this node for details). This driver is essentially a rename of DBD::Altera. Since DBI is a moving target at the time of this writing, this driver should only be assumed to work with DBI 0.93. A standard notice in DBD drivers' man pages is that, since the DBI is not yet stable, any DBD driver should be considered ALPHA software. So be it. We will try to keep up with the changes, stay tuned at which is the primary download site for this driver. CURRENT VERSION =============== Release 0.12 Name change... Previous release were: Release 0.11 Essentially a bug-fix. Release 0.10 (one hair short of 1.00). Main difference from previous version 0.09 is minor alterations to permit use for AGI (Another Gateway Interface). In other words, how can one use the same module to write both DBI programs and stored procedures for Ovrimos. Also, stored procedures are now supported, using the pseudo-SQL "call xxx ..." statement. See the documentation of Ovrimos for details. DRIVER-SPECIFIC BEHAVIOR ======================== DATA-SOURCE NAME ---------------- The dsn string passed to DBI->connect must be of the following form: dbi:Ovrimos:host:port where host is a TCP/IP address in human-readable or dotted-decimal format, and port is the TCP/IP port number to use (Ovrimos SQLPORT configuration parameter). CONNECTIONS, SESSIONS AND TRANSACTIONS -------------------------------------- One can have multiple connections to an Ovrimos database, up to the limit specified by one's User License. Keep in mind that what the License calls 'sessions' amount to what are called separate statements in DBI. Underlying the DBI is a protocol using the ODBC-equivalent 'connections' and 'statements'. Sessions are kept live until commit/rollback, and that can result in denial of service if you reach the License limit. The database handle will reuse an inactive statement handle, so finish() often. Commit/rollback finish()'es implicitly all open cursors (that's the answer one asks ODBC with SQL_CURSOR_COMMIT_BEHAVIOR and SQL_CURSOR_ROLLBACK_BEHAVIOR). Cached statements are not available. In the near future it is planned to cache SQL statements internally at the SQL Server, so preparing the same SQL statement as some time before will return a new $sth but without the cost associated with preparing from scratch. DATA TYPES ---------- All ODBC 2.0 data types are supported. The format of time/date values is as per the SQL/2 Standard, i.e.: 'DATE YYYY-MM-DD', 'TIME HH:MM:SS' and 'TIMESTAMP YYYY-MM-DD HH:MM:SS'. Ovrimos supports some additional types that are given below alongside their numerical value: UNSIGNED SMALLINT = 20 UNSIGNED INTEGER = 21 UNSIGNED TINYINT = 22 UNSIGNED BIGINT = 23 ERROR HANDLING -------------- As it stands, the DBI does not support the notion of warnings. Consequently, there are no diagnostics for successful calls. There is no obstacle in adding this, but since perl code using DBI will not check $h->errstr for successful operations, there is not much incentive to actually do it. Diagnostics for failed calls are inspected with the usual DBI calls. Do not pay any attention to $h->err; it is dummy. Ovrimos returns Standard SQL SQLSTATES and assorted messages, modelled principally after ODBC use. Since many diagnostics can be accumulated by one call, the diagnostics are merged, separated with newline. In that way, only the first SQLSTATE in the queue is visible using $h->state. One has to parse $h->errstr to find out the rest. BLOBS ----- BLOBs are supported via the SQL2 types *LONG VARCHAR* and *LONG VARBINARY*. These are not fetched with SQL queries and the LongReadLen and LongTruncOk attributes are not honored. Instead, Ovrimos presents a HTTP interface for retrieving BLOBS. Every BLOB has a Uniform Resource Identifier that can be found using the built-in *URI* function. This makes for easy retrieval of BLOBs in CGI scripts, where the URI can be embedded in HTML constructs like this: my ($name,$uri1,$uri2); $sth->bind_columns(undef,\($name,$uri1,$uri2)); $sth->prepare('select name,uri(blob1),uri(blob2) from blobtest'); $sth->execute; while($sth->fetch) { print 'Click here! '; print 'Image
', "\n"; } BLOBs are MIME-typed so the HTTP browser knows how to handle them. If one needs to retrieve a BLOB in an arbitrary script, one can use HTTP facilities like those in the libwww bundle (see CPAN, ). Or, one can just lead a simple life and do require 5.002; use strict; use IO::Socket; my $host; my $file; my $port=80; if($uri =~ m[^http://(.*):(\d*)/(.*)]) { ($host,$port,$file)=($1,$2,$3); } elsif($uri =~ m[^http://(.*)/(.*)]) { ($host,$file)=($1,$2); } else { die "horribly"; } my $so=IO::Socket::INET->new( Proto=>"tcp", PeerAddr=>$host, PeerPort=>$port) or die "in pain"; print $so "GET /$file HTTP/1.0\r\n\r\n"; $so->flush() or die "in agony"; One can then proceed to read from $so after skipping the reply header. If the MIME type is required, it can be found in the 'Content-type:' attribute of the reply header. Maybe in a later release this functionality will be included in the driver. DRIVER-SPECIFIC ATTRIBUTES -------------------------- There are some additional attributes that the user can query a $sth for: TYPE (also ovrimos_column_type) Reference to an array of column types as per ODBC, plus the Ovrimos extended types. TYPE is in capitals because the values returned conform to approved standards (ODBC, X/Open). ovrimos_column_precision Reference to an array of column precisions. Has meaning only for vector types (*CHAR, *BINARY) and NUMERIC/DECIMAL ovrimos_column_scale Reference to an array of column scales. Has meaning only for NUMERIC/DECIMAL. ovrimos_execution_plan It is a high-level explanation of the execution plan for the statement. The format is highly version-dependent and not to be dependent upon, but a human reader should be able to understand the access path for every range variable used, the order of range variables, the indices used, which temporary tables have been created et.c. ovrimos_native_query The query submitted, but in the form retained by the SQL Server. The SQL Server applies transformations to the SQL source and disambiguates certain constructs. The modified source can also be found in the execution plan (see above). LOW-LEVEL LIBRARY ----------------- The entire low-level library that implements the Ovrimos protocol is included. The DBI driver is based on this library, but one could conceivably use the library on its own. It is the only way, for the time being, to use scrollable cursors and bookmarks, since the DBI does not support them (yet?). See the package `DBD::Ovrimos::lowlevel' in `Ovrimos.pm'. No documentation is provided in this version about the low-level library. COMFORMANCE =========== There is a particularity concerning transactions: see `' in this node. Cached statements don't exist. Not even the function prepare_cached exists. Do not use it! You won't find any relevant attribute either. KNOWN BUGS ========== There are no known bugs in the DBD Driver. ACKNOWLEDGEMENTS ================ I would like to thank all the people on the DBI-DEV mailing list that helped clear some misunderstandings. SEE ALSO ======== DBI(3) AUTHOR ====== Dimitrios Souflis dsouflis@altera.gr, COPYRIGHT ========= (c) Altera Ltd, Greece http://www.altera.gr Permission is granted to use this software library according to the GNU Library General Public License (see ).  File: pm.info, Node: DBD/Recall, Next: DBD/XBase, Prev: DBD/Ovrimos, Up: Module List Database fault tolerance through replication. ********************************************* NAME ==== DBD::Recall - Database fault tolerance through replication. SYNOPSIS ======== use DBI; my $replicas = '192.168.1.1:7000,192.168.1.2:7000,192.168.1.3:7000'; my $dsn = "DBI:Recall:database=$replicas"; my $drh = DBI->install_driver ($driver, { Replicas => $replicas }); my @dbs = $drh->func( "_ListDBs" ); print (join "\n",@dbs,"\n"); my $dbh = DBI->connect($dsn); my @tables = $dbh->tables(); print (join "\n",@tables,"\n"); DESCRIPTION =========== This module interfaces to Recall, a data replication library written by Eric Newton, to provide transparent fault tolerance for database applications. Recall is based on a data replication algorithm developed at DEC's SRC for the Echo filesystem. It implements a fast protocol with low network overhead and guranteed fault tolerance as long as n of 2n-1 replica nodes are up. The DBD::Recall interface allows you to add fault tolerance to your database applications by a trivial change in your code. Simply use this module instead of the DBD you are currently using. To achieve replicated functionality you'll also need to set up a few pieces of external infrastucture, such as the replica servers, and rsync access between replicas. This is all described in greater detail in `Replication::Recall::DBServer' in this node. WARNING ======= DBD::Recall is a hack that attempts to accomplish something (fault-tolerance through replication) at the perl DBD driver level that would be better implemented by database servers. It works, but it is not pretty. Some commercial servers, such as Oracle, do implement replication. If speed and reliability are critical to your application, you will probably be better off with one of the commercial databases that implement replication within the database engine. I've only tried DBD::Recall with MySQL so far on Debian GNU/Linux. If you get it to work with another database engine or on another operating system, please email me about your experiences so I can include information about your platform in future releases. BUGS ==== * Transparency is accomplished through a remote delegation hack which might break under certain circumstances. If this happens to you, please let me know. * There must be loads more. Let me know if you find some. AUTHOR ====== DBD::Recall is Copyright (c) 2000 Ashish Gulhati . All Rights Reserved. ACKNOWLEDGEMENTS ================ Thanks to Barkha for inspiration, laughs and all 'round good times; and to Eric Newton, Gurusamy Sarathy, Larry Wall, Richard Stallman and Linus Torvalds for all the great software. LICENSE ======= This code is free software; you can redistribute it and/or modify it under the same terms as Perl itself. DISCLAIMER ========== This is free software. If it breaks, you own both parts.  File: pm.info, Node: DBD/XBase, Next: DBD/mSQL, Prev: DBD/Recall, Up: Module List DBI driver for XBase compatible database files ********************************************** NAME ==== DBD::XBase - DBI driver for XBase compatible database files SYNOPSIS ======== use DBI; my $dbh = DBI->connect("DBI:XBase:/directory/subdir") or die $DBI::errstr; my $sth = $dbh->prepare("select MSG from test where ID != 1") or die $dbh->errstr(); $sth->execute() or die $sth->errstr(); my @data; while (@data = $sth->fetchrow_array()) { ## further processing } $dbh->do('update table set name = ? where id = 45', {}, 'krtek'); DESCRIPTION =========== DBI compliant driver for module XBase. Please refer to DBI(3) documentation for how to actually use the module. In the connect call, specify the directory containing the dbf files (and other, memo, etc.) as the third part of the connect string. It defaults to the current directory. Note that with dbf, there is no database server that the driver would talk to. This DBD::XBase calls methods from XBase.pm module to read and write the files on the disk directly, so any limitations and features of XBase.pm apply to DBD::XBase as well. DBD::XBase basically adds SQL, DBI compliant interface to XBase.pm. The DBD::XBase doesn't make use of index files at the moment. If you really need indexed access, check XBase(3) for notes about support for variour index types. SUPPORTED SQL COMMANDS ====================== The SQL commands currently supported by DBD::XBase's prepare are: select ------ select fields_or_expressions from table [ where condition ] [ order by field ] Fields_or_expressions is a comma separated list of fields or arithmetic expressions, or a * for all fields from the table. The where condition specifies which rows will be returned, you can have arbitrary arithmetic and boolean expression here, compare fields and constants and use and and or. Match using `like' is also supported. Examples: select * from salaries where name = "Smith" select first,last from people where login = "ftp" or uid = 1324 select id,first_name,last_name from employ where last_name like 'Ki%' order by last_name select id + 1, substr(name, 1, 10) from employ where age > 65 select id, name from employ where id = ? You can use bind parameters in the where clause, as the last example shows. The actual value has to be supplied via bind_param or in the call to execute or do, see DBI(3) for details. To check for NULL values in the where expression, use `id is null' and `id is not null', not `id == null'. Please note that you can only select from one table, joins are not supported and are not planned to be supported. If you need them, get a real RDBMS (or send me a patch). In the arithmetic expressions you can use a couple of SQL functions - currently supported are concat, substr (and substring), trim, ltrim and rtrim, length. I do not have an exact idea of which and how many functions I want to support. It's easy to write them in a couple of minutes now the interface is there (check the XBase::SQL module if you want to send a patch containing support for more), it's just that I do not really need them and sometimes it's hard to tell what is usefull and what is SQL92 compatible. Comment welcome. The select command may contain and order by clause. Only one column is supported for sorting at the moment, patches are welcome. The group by clause is not supported (and I do not plan them), nor are the aggregate functions. delete ------ delete from table [ where condition ] The where condition is the same as for select. Examples: delete from jobs ## emties the table delete from jobs where companyid = "ISW" delete from jobs where id < ? insert ------ insert into table [ ( fields ) ] values ( list of values ) Here fields is a (optional) comma separated list of fields to set, list of values is a list of constants to assign. If the fields are not specified, sets the fields in the natural order of the table. You can use bind parameters in the list of values. Examples: insert into accounts (login, uid) values ("guest", 65534) insert into accounts (login, uid) values (?, ?) insert into passwd values ("user","*",4523,100,"Nice user", "/home/user","/bin/bash") update ------ update table set field = new value [ , set more fields ] [ where condition ] Example: update passwd set uid = 65534 where login = "guest" update zvirata set name = "Jezek", age = 4 where id = 17 Again, the value can also be specified as bind parameter. update zvirata set name = ?, age = ? where id = ? create table ------------ create table table name ( columns specification ) Columns specification is a comma separated list of column names and types. Example: create table rooms ( roomid int, cat char(10), balcony boolean ) The allowed types are char num numeric int integer float boolean blob memo date time datetime Some of them are synonyms. They are of course converted to appropriate XBase types. drop table ---------- drop table table name Example: drop table passwd VERSION ======= 0.155 AUTHOR ====== (c) 1997-1999 Jan Pazdziora, adelton@fi.muni.cz, http://www.fi.muni.cz/~adelton/ at Faculty of Informatics, Masaryk University in Brno, Czech Republic SEE ALSO ======== perl(1); DBI(3), XBase(3); dbish(1) Translation into Japanese (older version) at http://member.nifty.ne.jp/hippo2000/perltips/DBD/XBase.htm by Kawai Takanori.  File: pm.info, Node: DBD/mSQL, Next: DBD/mysql, Prev: DBD/XBase, Up: Module List mSQL and mysql drivers for the Perl5 Database Interface (DBI) ************************************************************* NAME ==== DBD::mSQL / DBD::mysql - mSQL and mysql drivers for the Perl5 Database Interface (DBI) SYNOPSIS ======== use DBI; $dbh = DBI->connect("DBI:mSQL:$database:$hostname:$port", undef, undef); or $dsn = "DBI:mysql:$database:$hostname:$port"; if ($compression) { $dsn .= ";mysql_compression=1"; } $dbh = DBI->connect($dsn, $user, $password); @databases = DBD::mysql::dr->func( $hostname, '_ListDBs' ); @tables = $dbh->func( '_ListTables' ); $sth = $dbh->prepare("LISTFIELDS $table"); $sth->execute; $sth->finish; $sth = $dbh->prepare("SELECT * FROM foo WHERE bla"); $sth->execute; $numRows = $sth->rows; $numFields = $sth->{'NUM_OF_FIELDS'}; $sth->finish; $rc = $drh->func( $database, '_CreateDB' ); $rc = $drh->func( $host, $database, '_CreateDB' ); $rc = $drh->func( $database, '_DropDB' ); $rc = $drh->func( $host, $database, '_DropDB' ); DESCRIPTION =========== and are the Perl5 Database Interface drivers for the mysql, mSQL 1.x and mSQL 2.x databases. The drivers are part of the *mysql-modules* and *Msql-modules* packages, respectively. Class Methods ------------- connect use DBI; $dbh = DBI->connect("DBI:mSQL:$database", undef, undef); $dbh = DBI->connect("DBI:mSQL:$database:$hostname", undef, undef); $dbh = DBI->connect("DBI:mSQL:$database:$hostname:$port", undef, undef); or use DBI; $dsn = "DBI:mysql:$database;$options"; $dsn = "DBI:mysql:$database;$hostname;$options"; $dsn = "DBI:mysql:$database;$hostname:$port;$options"; $dsn = "DBI:mysql:$database;$hostname:$port;$options"; $dbh = DBI->connect($dsn, $user, $password); A database must always be specified. The hostname, if not specified or specified as ", will default to an mysql or mSQL daemon running on the local machine on the default port for the UNIX socket. Should the mysql or mSQL daemon be running on a non-standard port number, you may explicitly state the port number to connect to in the hostname argument, by concatenating the hostname and *port number* together separated by a colon ( : ) character. Other options, possibly separated by semicolons, are: mysql_compression As of MySQL 3.22.3, a new feature is supported: If you use the option mysql_compression=1 then the communication between client and server will be compressed. mysql_socket As of MySQL 3.21.15, it is possible to choose the Unix socket that is used for connecting to the server. This is done, for example, with mysql_socket=/dev/mysql Usually there's no need for this option, unless you are using another location for the socket than that built into the client. Private MetaData Methods ------------------------ ListDBs @dbs = $dbh->func("$hostname:$port", '_ListDBs'); Returns a list of all databases managed by the mysql daemon or mSQL daemon running on `$hostname', port $port. This method is rarely needed for databases running on `localhost': You should use the portable method @dbs = DBI->data_sources("mysql"); or @dbs = DBI->data_sources("mSQL"); whenever possible. It is a design problem of this method, that there's no way of supplying a host name or port number to data_sources, that's the only reason why we still support ListDBs. :-( ListTables @tables = $dbh->func('_ListTables'); Once connected to the desired database on the desired mysql or mSQL mSQL daemon with the `DBI-'connect()> method, we may extract a list of the tables that have been created within that database. ListTables returns an array containing the names of all the tables present within the selected database. If no tables have been created, an empty list is returned. @tables = $dbh->func( '_ListTables' ); foreach $table ( @tables ) { print "Table: $table\n"; } ListFields Deprecated, see `' in this node below. ListSelectedFields Deprecated, see `' in this node below. Database Manipulation --------------------- CreateDB DropDB $rc = $drh->func( $database, '_CreateDB' ); $rc = $drh->func( $database, '_DropDB' ); or $rc = $drh->func( $host, $database, '_CreateDB' ); $rc = $drh->func( $host, $database, '_DropDB' ); These two methods allow programmers to create and drop databases from DBI scripts. Since mSQL disallows the creation and deletion of databases over the network, these methods explicitly connect to the mSQL daemon running on the machine `localhost' and execute these operations there. It should be noted that database deletion is *not prompted for* in any way. Nor is it undo-able from DBI. Once you issue the dropDB() method, the database will be gone! These methods should be used at your own risk. DATABASE HANDLES ================ The DBD::mysql driver supports the following attributes of database handles (read only): $infoString = $dbh->{'info'}; $threadId = $dbh->{'thread_id'}; These correspond to mysql_info() and mysql_tread_id(), respectively. STATEMENT HANDLES ================= The statement handles of DBD::mysql and DBD::mSQL support a number of attributes. You access these by using, for example, my $numFields = $sth->{'NUM_OF_FIELDS'}; Note, that most attributes are valid only after a successfull execute. An undef value will returned in that case. The most important exception is the `mysql_use_result' attribute: This forces the driver to use mysql_use_result rather than mysql_store_result. The former is faster and less memory consuming, but tends to block other processes. (That's why mysql_store_result is the default.) To set the `mysql_use_result' attribute, use either of the following: my $sth = $dbh->prepare("QUERY", { "mysql_use_result" => 1}); or my $sth = $dbh->prepare("QUERY"); $sth->{"mysql_use_result"} = 1; Of course it doesn't make sense to set this attribute before calling the execute method. Column dependent attributes, for example NAME, the column names, are returned as a reference to an array. The array indices are corresponding to the indices of the arrays returned by fetchrow and similar methods. For example the following code will print a header of table names together with all rows: my $sth = $dbh->prepare("SELECT * FROM $table"); if (!$sth) { die "Error:" . $dbh->errstr . "\n"; } if (!$sth->execute) { die "Error:" . $sth->errstr . "\n"; } my $names = $sth->{'NAME'}; my $numFields = $sth->{'NUM_OF_FIELDS'}; for (my $i = 0; $i < $numFields; $i++) { printf("%s%s", $$names[$i], $i ? "," : ""); } print "\n"; while (my $ref = $sth->fetchrow_arrayref) { for (my $i = 0; $i < $numFields; $i++) { printf("%s%s", $$ref[$i], $i ? "," : ""); } print "\n"; } For portable applications you should restrict yourself to attributes with capitalized or mixed case names. Lower case attribute names are private to DBD::mSQL and DBD::mysql. The attribute list includes: ChopBlanks this attribute determines whether a fetchrow will chop preceding and trailing blanks off the column values. Chopping blanks does not have impact on the max_length attribute. insertid MySQL has the ability to choose unique key values automatically. If this happened, the new ID will be stored in this attribute. This attribute is not valid for DBD::mSQL. is_blob Reference to an array of boolean values; TRUE indicates, that the respective column is a blob. This attribute is valid for MySQL only. is_key Reference to an array of boolean values; TRUE indicates, that the respective column is a key. This is valid for MySQL only. is_num Reference to an array of boolean values; TRUE indicates, that the respective column contains numeric values. is_pri_key Reference to an array of boolean values; TRUE indicates, that the respective column is a primary key. This is only valid for MySQL and mSQL 1.0.x: mSQL 2.x uses indices. is_not_null A reference to an array of boolean values; FALSE indicates that this column may contain NULL's. You should better use the NULLABLE attribute above which is a DBI standard. length max_length A reference to an array of maximum column sizes. The max_length is the maximum physically present in the result table, length gives the theoretically possible maximum. max_length is valid for MySQL only. NAME A reference to an array of column names. NULLABLE A reference to an array of boolean values; TRUE indicates that this column may contain NULL's. NUM_OF_FIELDS Number of fields returned by a SELECT or *LISTFIELDS* statement. You may use this for checking whether a statement returned a result: A zero value indicates a non-SELECT statement like INSERT, DELETE or UPDATE. table A reference to an array of table names, useful in a JOIN result. type A reference to an array of column types. It depends on the DBMS, which values are returned, even for identical types. mSQL will return types like &DBD::mSQL::INT_TYPE, &DBD::msql::TEXT_TYPE etc., MySQL uses &DBD::mysql::FIELD_TYPE_SHORT, &DBD::mysql::FIELD_TYPE_STRING etc. COMPATIBILITY ALERT =================== As of version 0.70 DBD::mSQL has a new maintainer. Even more, the sources have been completely rewritten in August 1997, so it seemed apropriate to bump the version number: Incompatibilities are more than likely. Recent changes: --------------- New connect method DBD::mSQL and DBD::mysql now use the new connect method as introduced with DBI 0.83 or so. For compatibility reasons the old method still works, but the driver issues a warning when he detects use of the old version. There's no workaround, you must update your sources. (Sorry, but the change was in DBI, not in DBD::mysql and DBD::mSQL.) _ListFields returning statement handle As of Msql-modules 1.1805, the private functions $dbh->func($table, "_ListFields"); and $sth->func("_ListSelectedFields"); no longer return a simple hash, but a statement handle. (*_ListSelectedFields* is a stub now which just returns $self.) This should usually not be visible, when your statement handle gets out of scope. However, if your database handle ($dbh in the above example) disconnects, either because you explicitly disconnect or because he gets out of scope, and the statement handle is still active, DBI will issue a warning for active cursors being destroyed. The simple workaround is to execute `$sth->finish' or to ensure that $sth gets out of scope before $dbh. Sorry, but it was obvious nonsense to support two different things for accessing the basically same thing: A M(y)SQL result. The drivers do not conform to the current DBI specification in some minor points. For example, the private attributes is_num or is_blob have been written *IS_NUM* and *IS_BLOB*. For historical reasons we continue supporting the capitalized names, although the DBI specification now reserves capitalized names for standard names, mixed case for DBI and lower case for private attributes and methods. We currently consider anything not conforming to the DBI as deprecated. It is quite possible that we remove support of these deprecated names and methods in the future. In particular these includes: `$sth->func($table, '_ListSelectedFields')' highly deprecated, all attributes are directly accessible via the statement handle. For example instead of $ref = $sth->func($table, '_ListSelectedFields') my @names = $ref->{'NAME'} you just do a my @names = @{$sth->{'NAME'}}; Capitalized attribute names Deprecated, should be replaced by the respective lower case names. MULTITHREADING ============== The multithreading capabilities of the Msql-Mysql-modules depend completely on the underlying C libraries: The modules are working with handle data only, no global variables are accessed or (to the best of my knowledge) thread unsafe functions are called. Thus DBD::mSQL and DBD::mysql are completely thread safe, if the C libraries thread safe and you don't share handles among threads. The obvious questions is: Are the C libraries thread safe? In the case of mSQL the answer is definitely "no". The C library has a concept of one single active connection at a time and that is not what threads like. In the case of MySQL the answer is "mostly" and, in theory, you should be able to get a "yes", if the C library is compiled for being thread safe (By default it isn't.) by passing the option -with-thread-safe-client to configure. See the section on *How to make a threadsafe client* in the manual. BUGS ==== The port part of the first argument to the connect call is implemented in an unsafe way when using mSQL. In fact it is just stting the environment variable MSQL_TCP_PORT during the connect call. If another connect call uses another port and the handles are used simultaneously, they will interfere. I doubt that this will ever change. Msql-2.0.4 and 2.0.4.1 contain a bug that makes ORDER BY and hence the test script `t/40bindparam' fail. To verify, if this is the case for you, do a cd Msql perl -w -I../blib/lib -I../blib/arch t/40bindparam.t If something is wrong, the script ought to print a number of id's and names. If the id's aren't in order, it is likely, that your mSQL has a bug. See the INSTALL file for a patch. AUTHOR ====== *DBD::mSQL* has been primarily written by Alligator Descartes (*descarte@arcana.co.uk*), who has been aided and abetted by Gary Shea, Andreas Koenig and Tim Bunce amongst others. Apologies if your name isn't listed, it probably is in the file called 'Acknowledgments'. As of version 0.80 the maintainer is Andreas König. Version 2.00 is an almost complete rewrite by Jochen Wiedmann. COPYRIGHT ========= This module is Copyright (c)1997 Jochen Wiedmann, with code portions Copyright (c)1994-1997 their original authors. This module is released under the 'Artistic' license which you can find in the perl distribution. This document is Copyright (c)1997 Alligator Descartes. All rights reserved. Permission to distribute this document, in full or in part, via email, Usenet, ftp archives or http is granted providing that no charges are involved, reasonable attempt is made to use the most current version and all credits and copyright notices are retained ( the AUTHOR and COPYRIGHT sections ). Requests for other distribution rights, including incorporation into commercial products, such as books, magazine articles or CD-ROMs should be made to Alligator Descartes <*descarte@arcana.co.uk*>. MAILING LIST SUPPORT ==================== This module is maintained and supported on a mailing list, msql-mysql-modules@tcx.se To subscribe to this list, send a mail with the words subscribe msql-mysql-modules or subscribe msql-mysql-modules-digest in the first line of the body to mdomo@tcx.se. A mailing list archive is in preparation. Additionally you might try the dbi-user mailing list for questions about DBI and its modules in general. Subscribe via http://www.fugue.com/dbi Mailing list archives are at http://www.rosat.mpe-garching.mpg.de/mailing-lists/PerlDB-Interest/ http://outside.organic.com/mail-archives/dbi-users/ http://www.coe.missouri.edu/~faq/lists/dbi.html ADDITIONAL DBI INFORMATION ========================== Additional information on the DBI project can be found on the World Wide Web at the following URL: http://www.arcana.co.uk/technologia/perl/DBI where documentation, pointers to the mailing lists and mailing list archives and pointers to the most current versions of the modules can be used. Information on the DBI interface itself can be gained by typing: perldoc DBI right now!  File: pm.info, Node: DBD/mysql, Next: DBI/FAQ, Prev: DBD/mSQL, Up: Module List mSQL and mysql drivers for the Perl5 Database Interface (DBI) ************************************************************* NAME ==== DBD::mSQL / DBD::mysql - mSQL and mysql drivers for the Perl5 Database Interface (DBI) SYNOPSIS ======== use DBI; $dbh = DBI->connect("DBI:mSQL:$database:$hostname:$port", undef, undef); or $dsn = "DBI:mysql:$database:$hostname:$port"; if ($compression) { $dsn .= ";mysql_compression=1"; } $dbh = DBI->connect($dsn, $user, $password); @databases = DBD::mysql::dr->func( $hostname, '_ListDBs' ); @tables = $dbh->func( '_ListTables' ); $sth = $dbh->prepare("LISTFIELDS $table"); $sth->execute; $sth->finish; $sth = $dbh->prepare("SELECT * FROM foo WHERE bla"); $sth->execute; $numRows = $sth->rows; $numFields = $sth->{'NUM_OF_FIELDS'}; $sth->finish; $rc = $drh->func( $database, '_CreateDB' ); $rc = $drh->func( $host, $database, '_CreateDB' ); $rc = $drh->func( $database, '_DropDB' ); $rc = $drh->func( $host, $database, '_DropDB' ); DESCRIPTION =========== and are the Perl5 Database Interface drivers for the mysql, mSQL 1.x and mSQL 2.x databases. The drivers are part of the *mysql-modules* and *Msql-modules* packages, respectively. Class Methods ------------- connect use DBI; $dbh = DBI->connect("DBI:mSQL:$database", undef, undef); $dbh = DBI->connect("DBI:mSQL:$database:$hostname", undef, undef); $dbh = DBI->connect("DBI:mSQL:$database:$hostname:$port", undef, undef); or use DBI; $dsn = "DBI:mysql:$database;$options"; $dsn = "DBI:mysql:$database;$hostname;$options"; $dsn = "DBI:mysql:$database;$hostname:$port;$options"; $dsn = "DBI:mysql:$database;$hostname:$port;$options"; $dbh = DBI->connect($dsn, $user, $password); A database must always be specified. The hostname, if not specified or specified as ", will default to an mysql or mSQL daemon running on the local machine on the default port for the UNIX socket. Should the mysql or mSQL daemon be running on a non-standard port number, you may explicitly state the port number to connect to in the hostname argument, by concatenating the hostname and *port number* together separated by a colon ( : ) character. Other options, possibly separated by semicolons, are: mysql_compression As of MySQL 3.22.3, a new feature is supported: If you use the option mysql_compression=1 then the communication between client and server will be compressed. mysql_socket As of MySQL 3.21.15, it is possible to choose the Unix socket that is used for connecting to the server. This is done, for example, with mysql_socket=/dev/mysql Usually there's no need for this option, unless you are using another location for the socket than that built into the client. Private MetaData Methods ------------------------ ListDBs @dbs = $dbh->func("$hostname:$port", '_ListDBs'); Returns a list of all databases managed by the mysql daemon or mSQL daemon running on `$hostname', port $port. This method is rarely needed for databases running on `localhost': You should use the portable method @dbs = DBI->data_sources("mysql"); or @dbs = DBI->data_sources("mSQL"); whenever possible. It is a design problem of this method, that there's no way of supplying a host name or port number to data_sources, that's the only reason why we still support ListDBs. :-( ListTables @tables = $dbh->func('_ListTables'); Once connected to the desired database on the desired mysql or mSQL mSQL daemon with the `DBI-'connect()> method, we may extract a list of the tables that have been created within that database. ListTables returns an array containing the names of all the tables present within the selected database. If no tables have been created, an empty list is returned. @tables = $dbh->func( '_ListTables' ); foreach $table ( @tables ) { print "Table: $table\n"; } ListFields Deprecated, see `' in this node below. ListSelectedFields Deprecated, see `' in this node below. Database Manipulation --------------------- CreateDB DropDB $rc = $drh->func( $database, '_CreateDB' ); $rc = $drh->func( $database, '_DropDB' ); or $rc = $drh->func( $host, $database, '_CreateDB' ); $rc = $drh->func( $host, $database, '_DropDB' ); These two methods allow programmers to create and drop databases from DBI scripts. Since mSQL disallows the creation and deletion of databases over the network, these methods explicitly connect to the mSQL daemon running on the machine `localhost' and execute these operations there. It should be noted that database deletion is *not prompted for* in any way. Nor is it undo-able from DBI. Once you issue the dropDB() method, the database will be gone! These methods should be used at your own risk. DATABASE HANDLES ================ The DBD::mysql driver supports the following attributes of database handles (read only): $infoString = $dbh->{'info'}; $threadId = $dbh->{'thread_id'}; These correspond to mysql_info() and mysql_tread_id(), respectively. STATEMENT HANDLES ================= The statement handles of DBD::mysql and DBD::mSQL support a number of attributes. You access these by using, for example, my $numFields = $sth->{'NUM_OF_FIELDS'}; Note, that most attributes are valid only after a successfull execute. An undef value will returned in that case. The most important exception is the `mysql_use_result' attribute: This forces the driver to use mysql_use_result rather than mysql_store_result. The former is faster and less memory consuming, but tends to block other processes. (That's why mysql_store_result is the default.) To set the `mysql_use_result' attribute, use either of the following: my $sth = $dbh->prepare("QUERY", { "mysql_use_result" => 1}); or my $sth = $dbh->prepare("QUERY"); $sth->{"mysql_use_result"} = 1; Of course it doesn't make sense to set this attribute before calling the execute method. Column dependent attributes, for example NAME, the column names, are returned as a reference to an array. The array indices are corresponding to the indices of the arrays returned by fetchrow and similar methods. For example the following code will print a header of table names together with all rows: my $sth = $dbh->prepare("SELECT * FROM $table"); if (!$sth) { die "Error:" . $dbh->errstr . "\n"; } if (!$sth->execute) { die "Error:" . $sth->errstr . "\n"; } my $names = $sth->{'NAME'}; my $numFields = $sth->{'NUM_OF_FIELDS'}; for (my $i = 0; $i < $numFields; $i++) { printf("%s%s", $$names[$i], $i ? "," : ""); } print "\n"; while (my $ref = $sth->fetchrow_arrayref) { for (my $i = 0; $i < $numFields; $i++) { printf("%s%s", $$ref[$i], $i ? "," : ""); } print "\n"; } For portable applications you should restrict yourself to attributes with capitalized or mixed case names. Lower case attribute names are private to DBD::mSQL and DBD::mysql. The attribute list includes: ChopBlanks this attribute determines whether a fetchrow will chop preceding and trailing blanks off the column values. Chopping blanks does not have impact on the max_length attribute. insertid MySQL has the ability to choose unique key values automatically. If this happened, the new ID will be stored in this attribute. This attribute is not valid for DBD::mSQL. is_blob Reference to an array of boolean values; TRUE indicates, that the respective column is a blob. This attribute is valid for MySQL only. is_key Reference to an array of boolean values; TRUE indicates, that the respective column is a key. This is valid for MySQL only. is_num Reference to an array of boolean values; TRUE indicates, that the respective column contains numeric values. is_pri_key Reference to an array of boolean values; TRUE indicates, that the respective column is a primary key. This is only valid for MySQL and mSQL 1.0.x: mSQL 2.x uses indices. is_not_null A reference to an array of boolean values; FALSE indicates that this column may contain NULL's. You should better use the NULLABLE attribute above which is a DBI standard. length max_length A reference to an array of maximum column sizes. The max_length is the maximum physically present in the result table, length gives the theoretically possible maximum. max_length is valid for MySQL only. NAME A reference to an array of column names. NULLABLE A reference to an array of boolean values; TRUE indicates that this column may contain NULL's. NUM_OF_FIELDS Number of fields returned by a SELECT or *LISTFIELDS* statement. You may use this for checking whether a statement returned a result: A zero value indicates a non-SELECT statement like INSERT, DELETE or UPDATE. table A reference to an array of table names, useful in a JOIN result. type A reference to an array of column types. It depends on the DBMS, which values are returned, even for identical types. mSQL will return types like &DBD::mSQL::INT_TYPE, &DBD::msql::TEXT_TYPE etc., MySQL uses &DBD::mysql::FIELD_TYPE_SHORT, &DBD::mysql::FIELD_TYPE_STRING etc. COMPATIBILITY ALERT =================== As of version 0.70 DBD::mSQL has a new maintainer. Even more, the sources have been completely rewritten in August 1997, so it seemed apropriate to bump the version number: Incompatibilities are more than likely. Recent changes: --------------- New connect method DBD::mSQL and DBD::mysql now use the new connect method as introduced with DBI 0.83 or so. For compatibility reasons the old method still works, but the driver issues a warning when he detects use of the old version. There's no workaround, you must update your sources. (Sorry, but the change was in DBI, not in DBD::mysql and DBD::mSQL.) _ListFields returning statement handle As of Msql-modules 1.1805, the private functions $dbh->func($table, "_ListFields"); and $sth->func("_ListSelectedFields"); no longer return a simple hash, but a statement handle. (*_ListSelectedFields* is a stub now which just returns $self.) This should usually not be visible, when your statement handle gets out of scope. However, if your database handle ($dbh in the above example) disconnects, either because you explicitly disconnect or because he gets out of scope, and the statement handle is still active, DBI will issue a warning for active cursors being destroyed. The simple workaround is to execute `$sth->finish' or to ensure that $sth gets out of scope before $dbh. Sorry, but it was obvious nonsense to support two different things for accessing the basically same thing: A M(y)SQL result. The drivers do not conform to the current DBI specification in some minor points. For example, the private attributes is_num or is_blob have been written *IS_NUM* and *IS_BLOB*. For historical reasons we continue supporting the capitalized names, although the DBI specification now reserves capitalized names for standard names, mixed case for DBI and lower case for private attributes and methods. We currently consider anything not conforming to the DBI as deprecated. It is quite possible that we remove support of these deprecated names and methods in the future. In particular these includes: `$sth->func($table, '_ListSelectedFields')' highly deprecated, all attributes are directly accessible via the statement handle. For example instead of $ref = $sth->func($table, '_ListSelectedFields') my @names = $ref->{'NAME'} you just do a my @names = @{$sth->{'NAME'}}; Capitalized attribute names Deprecated, should be replaced by the respective lower case names. MULTITHREADING ============== The multithreading capabilities of the Msql-Mysql-modules depend completely on the underlying C libraries: The modules are working with handle data only, no global variables are accessed or (to the best of my knowledge) thread unsafe functions are called. Thus DBD::mSQL and DBD::mysql are completely thread safe, if the C libraries thread safe and you don't share handles among threads. The obvious questions is: Are the C libraries thread safe? In the case of mSQL the answer is definitely "no". The C library has a concept of one single active connection at a time and that is not what threads like. In the case of MySQL the answer is "mostly" and, in theory, you should be able to get a "yes", if the C library is compiled for being thread safe (By default it isn't.) by passing the option -with-thread-safe-client to configure. See the section on *How to make a threadsafe client* in the manual. BUGS ==== The port part of the first argument to the connect call is implemented in an unsafe way when using mSQL. In fact it is just stting the environment variable MSQL_TCP_PORT during the connect call. If another connect call uses another port and the handles are used simultaneously, they will interfere. I doubt that this will ever change. Msql-2.0.4 and 2.0.4.1 contain a bug that makes ORDER BY and hence the test script `t/40bindparam' fail. To verify, if this is the case for you, do a cd Msql perl -w -I../blib/lib -I../blib/arch t/40bindparam.t If something is wrong, the script ought to print a number of id's and names. If the id's aren't in order, it is likely, that your mSQL has a bug. See the INSTALL file for a patch. AUTHOR ====== *DBD::mSQL* has been primarily written by Alligator Descartes (*descarte@arcana.co.uk*), who has been aided and abetted by Gary Shea, Andreas Koenig and Tim Bunce amongst others. Apologies if your name isn't listed, it probably is in the file called 'Acknowledgments'. As of version 0.80 the maintainer is Andreas König. Version 2.00 is an almost complete rewrite by Jochen Wiedmann. COPYRIGHT ========= This module is Copyright (c)1997 Jochen Wiedmann, with code portions Copyright (c)1994-1997 their original authors. This module is released under the 'Artistic' license which you can find in the perl distribution. This document is Copyright (c)1997 Alligator Descartes. All rights reserved. Permission to distribute this document, in full or in part, via email, Usenet, ftp archives or http is granted providing that no charges are involved, reasonable attempt is made to use the most current version and all credits and copyright notices are retained ( the AUTHOR and COPYRIGHT sections ). Requests for other distribution rights, including incorporation into commercial products, such as books, magazine articles or CD-ROMs should be made to Alligator Descartes <*descarte@arcana.co.uk*>. MAILING LIST SUPPORT ==================== This module is maintained and supported on a mailing list, msql-mysql-modules@tcx.se To subscribe to this list, send a mail with the words subscribe msql-mysql-modules or subscribe msql-mysql-modules-digest in the first line of the body to mdomo@tcx.se. A mailing list archive is in preparation. Additionally you might try the dbi-user mailing list for questions about DBI and its modules in general. Subscribe via http://www.fugue.com/dbi Mailing list archives are at http://www.rosat.mpe-garching.mpg.de/mailing-lists/PerlDB-Interest/ http://outside.organic.com/mail-archives/dbi-users/ http://www.coe.missouri.edu/~faq/lists/dbi.html ADDITIONAL DBI INFORMATION ========================== Additional information on the DBI project can be found on the World Wide Web at the following URL: http://www.arcana.co.uk/technologia/perl/DBI where documentation, pointers to the mailing lists and mailing list archives and pointers to the most current versions of the modules can be used. Information on the DBI interface itself can be gained by typing: perldoc DBI right now!