Re: DBD SQL::Statement change in Maintainership
jeff wrote: Hi friends in the DBI community, After many years of maintaining SQL::Statement and various pure-Perl DBDs (RAM, AnyData, File, CSV) I've now finally admitted that I am far too busy to do an adequate job with the modules. Although I'll stay on as co-maintainer and help out when I can, the active development will pass into the capable hands of H.Merijn Brand (DBD::CSV and DBD::File) and Jens Rehsack (all the rest). I'd like to thank both of them for stepping in and especially Jens who has tackled the thankless task of straightening out some of the messes I created in SQL::Statement. I'd also like to thank the many people over the years who sent bug reports and patches and jumped in to help when I needed it, especially Tim Bunce and Dean Arnold. Thanks for all your help w/ S::S (and your patience w/ me) I've also been afflicted with too busy to keep up w/ my CPAN goods syndrome and may need to seek co-maintainers soon. I hope your new endeavors are as fun as Perl hacking (I wish mine were 8^/) - Dean Arnold
[Slight OT] DBD/DBIx::Chart need patent protection, know any contacts ?
(Sorry if this is OT, but I'm hoping someone can point me in the right direction) Someone has just alerted me to the following patent attempt by our friends at IBM: Data Plotting Extension for Structured Query Language (http://www.freepatentsonline.com/y2008/0215554.html) Given that DBD::Chart predates the filing by at least 7 years (in fact, you can still get a version from CPAN from 2002, and I think BackPAN may have stuff from 2000/2001), I'd like to make sure this thing gets killed. Does anyone know an open source patent holding group, or someplace I can submit prior art claims ? TIA, Dean Arnold Presicient Corp.
Re: ODBC 3.5 spec ?
[EMAIL PROTECTED] wrote: On Mar 23, 3:49 pm, [EMAIL PROTECTED] (Dean Arnold) wrote: Anyone know where the spec might be hiding ? Last clue I had was some MSDN CD circa 2000. Hi, Dean -- The ODBC info is still on the Microsoft site, albeit at a new URL. Try here for the ODBC Programmer's Reference -- http://msdn2.microsoft.com/en-us/library/ms714177.aspx The left-hand navigation drill-downs will take you to lots of other data access information, among other things. Yes, I'm aware of that, but I could swear there used to be a more formal spec/SDK...but I'm progressing w/ those docs and a nice DDJ article from some years ago. I'm curious to know why you would need to build an ODBC driver for DBI data sources -- and what would consume the results! The notion has been kicked around here a few times in the past. The primary motivation is to expose exotic DBD's (DBD::iPod, DBD::Amazon, DBD::Google, DBD::Gofer, etc.) or subclasses (eg, DBIx::Chart) some of us have written to a larger audience (eg, the mass of Excel jockeys). Which might have the pleasant side effect of - exposing more folks to Perl solutions - motivating more people to write more exotic DBDs (eg, take a random walk around programmableweb.com and pick some services to map to DBDs) - encouraging DBD authors to do a better job of conformance (yes, I'm guilty) (in some sense, the resulting ODBC wrapper can act as a conformance tester...*if* I can find a freely available conformance test suite for ODBC) There may be an existing solution for you, which may save a world of headache, as this is not the easiest of specifications to implement. Hehe. I recall a flight home from a SQLAccess meeting in SillyCon Valley many years ago, reading the 1st draft SQL CLI spec MSFT had dropped in our collective laps, and thinking to myself, No one in their right mind will code to this interface Which shows how feeble my prescient skills are. However, using the aforementioned DDJ template helps leapfrog much of the required ODBC internal yak-shaving. Ultimately, my hope is to provide a liberally licensed FOSS solution. - Dean
Re: ODBC 3.5 spec ?
Tim Bunce wrote: On Sun, Mar 23, 2008 at 12:49:22PM -0700, Dean Arnold wrote: I've finally have a reason to write an ODBC wrapper around Perl/DBI (not DBD::ODBC, but the other way around). Strange what some people have to do for a living! :) Do you mean a Perl-level wrapper (ala Win32::ODBC API) or a C-level ODBC emulation? If the former, then the Win32::DBIODBC hack may be s start: http://search.cpan.org/src/TIMB/DBI-1.603/lib/Win32/DBIODBC.pm If the later then I suspect you're in for a bumpy ride. Grafting DBI into the guts of an exiting open source ODBC driver may be the best approach here. Its the latter. I've found a decent resource from an old DDJ article to bootstrap things. I've already built a couple embedded Perl projects, so much of the process will hopefully be cut/paste code. The hard part will be finding DBD's that provide sufficient metadata at the right steps in the process...esp. the sort of DBDs I'm trying to support (eg, DBD::Amazon, DBD::iPod, DBIx::Chart, maybe even DBD::Gofer ?) But I'm having no luck finding the formal ODBC 3.5 spec. It appears our friends in Redmond have deep-6'd it from their websites, and a lengthy googling session hasn't surfaced anything. Anyone know where the spec might be hiding ? archive.org may help. Last clue I had was some MSDN CD circa 2000. Sounds plausible. You may be able to find a copy of the ODBC 3.5 Developers Guide book by Roger Sanders (pub Mc Graw Hill). Have fun! Tim. Thanks for the pointers, Dean
ODBC 3.5 spec ?
I've finally have a reason to write an ODBC wrapper around Perl/DBI (not DBD::ODBC, but the other way around). But I'm having no luck finding the formal ODBC 3.5 spec. It appears our friends in Redmond have deep-6'd it from their websites, and a lengthy googling session hasn't surfaced anything. Anyone know where the spec might be hiding ? Last clue I had was some MSDN CD circa 2000. TIA, Dean Arnold Presicient Corp.
Re: Raising the dead Scrolling Cursors
Jonathan Leffler wrote:
On Fri, Mar 14, 2008 at 5:53 AM, [EMAIL PROTECTED] wrote:
I have been playing about with scrollable result sets (Cursors)
in Oracle/OCI and have noticed that there has been some discussion about
it before on the list
You can find the discussion here
http://perl.markmail.org/search/?q=list%3Aorg.perl.dbi-dev+fetch_scroll#query:list%3Aorg.perl.dbi-dev%20fetch_scroll+page:1+mid:xqwmgau3zesiyo7n+state:results
and here
http://perl.markmail.org/search/?q=from%3A%22Tim+Bunce%22+scroll+cursor#query:from%3A%22Tim%20Bunce%22%20scroll%20cursor+page:1+mid:t3xvtu3rcqarszsx+state:results
Reading through these I see that basically each Database has its own
flavour or scrollable results set from a select i.e.
Informix Scrollable and updateable
MySQL semi Scrollable
Oracle Scrollable and read only
SQLServer Scrollable and read only
Scrollable and updateable
So my proposal is to stub in
fetch_scroll
in DBI
and let the DBD drivers write their own implementation
This would be the same as bind_param_inout_array which is stubbed in but
not implemented in DBI
The proposed sub would be like this
bind_param_inout_array = { U =[1,2,'[, \%attr]'] }
so there will be an attribute param to pass in any commands/setting that
are needed for the fetch.
The advantage of this is we get something that would be customized for
each DBD implementation of course the disadvantage is we do not have a
pure Perl implementation of his functionality.
Any thoughts,comments or even insults;) are welcome
Sounds good to me. Will there be a standard set of 'scroll actions'?
FETCH_NEXT
FETCH_PREV
FETCH_FIRST
FETCH_LAST
FETCH_CURRENT
FETCH_RELATIVE
FETCH_ABSOLUTE
(Any others? That's the set for Informix.)
$sth-fetch_scroll(FETCH_xxx [, $offset]);
Or do we need a more general hash to pass values in?
In DBI's spirit of ODBC/SQL CLI conformance, presumably the
attributes should be
SQL_FETCH_NEXT
SQL_FETCH_PRIOR
SQL_FETCH_FIRST
SQL_FETCH_LAST
SQL_FETCH_ABSOLUTE
SQL_FETCH_RELATIVE
SQL_FETCH_BOOKMARK
with values as defined somewhere in ODBC's header files.
Gruesome details of ODBC's SQLFetchScroll are here:
http://msdn2.microsoft.com/en-us/library/ms714682(VS.85).aspx
Dean Arnold
Presicient Corp.
AJAX meets SQL?
First, Tim gave us DBD::Gofer with an HTTP transport... Then, Google gave us Gears with a nice little local SQLite store (And Adobe has since done the same with AIR). Then, someone with the SQL madness wrote a SQL parser for Javascript: http://www.trentrichardson.com/jsonsql/ Today, Ajaxian informs me of server side Javascript database access: http://ajaxian.com/archives/server-side-javascript-databases-access So the obvious destination for all this: has anyone yet built a Javascript package to leverage DBD::Gofer::Transport::http directly ? I know there are sandbox issues, but assuming the script was delivered from the same domain as the Gofer::Xport::http server, might this be possible ? (at least in stripped down form) ? (I suppose some DBI functionality might get left behind...but I reckon it'd still be useful. And might dovetail nicely with some things I'm trying to do with DBIx::Chart...) OTOH, I may be having one of my less coherent moments... Dean Arnold Presicient Corp.
Re: DBI interface for Amazon SimpleDB?
Tim Bunce wrote: So, who's going to be first to mash up Amazon::SimpleDB::* and perhaps DBI::SQL::Nano into a DBI driver for Amazon's new SimpleDB service? http://www.amazon.com/gp/browse.html?node=342335011 Tim. Do you have driver prefix for it yet ? Maybe simpdb_ ? BTW: it isn't free, altho its pretty cheap. I'll see what I can morph from DBD::Amazon. Dean Arnold Presicient Corp.
Re: DBI interface for Amazon SimpleDB?
Dean Arnold wrote: Tim Bunce wrote: So, who's going to be first to mash up Amazon::SimpleDB::* and perhaps DBI::SQL::Nano into a DBI driver for Amazon's new SimpleDB service? http://www.amazon.com/gp/browse.html?node=342335011 Tim. Do you have driver prefix for it yet ? Maybe simpdb_ ? After a bit of investigation, it appears the preferred acronym is 'sdb', so perhaps 'amznsdb_' is a better prefix ? FWIW: it appears the Amazon announcement is a bit premature; while the docs/website are available, the sample files and actual access appears to be limited to invited guests at this point. Also, the key piece of the name is Simple. Its really more like a version of memcached with some simple predicate and set operators. However, DBD::Amazon's infrastructure looks to be a good fit to sortof turn it into a semi-RDBMS. (Esp. the DNF engine to separate out predicates that can be executed on the server from those that can be executed in the client) - Dean
Re: new DBD driver: DBD::MVS_FTPSQL
Clemente Biondo wrote: ROTFL This is not exactly the kind of feedback I was hoping for but thanks anyway :-) Seriously, this driver can be useful to people whose work involves (like me) data reporting from mainframe systems. Imagine this scenario: your boss asks you to produce a report that require manipulation of data from different sources, one of which is a DB2 database located on a mainframe.This scenario is very common in banking, finance and insurance environment. Now, the traditional way of doing this require: -) writing a JCL and one or more Cobol programs on the mainframe side to extract the raw information needed from the mainframe. -) writing a program on the pc side to acquire the data from the mainframe ,combine it with other data sources, and finally format the report. Based on my experience, using this driver and some other perl modules like Spreadsheet::WriteExcel you can dramatically reduce development time from weeks to days. Don't feel lonesome...DBD::Chart gets lots of funny looks, too, but also still seems to attract a lot of positive attention. FWIW, many years ago I used a similar (albeit inverted) solution for file transfers (upload/download files to/from a database for which I had drivers when ftp wasn't an option). E.g., DBD::iPod is still a personal favorite. Perl is great, but sometimes good ol' SQL rules! To reiterate (with apologies to the late Chairman of the Board), The more I do DBD's, the more DBD's I do. (I spose I should throw in a plug for SQL::Preproc...it may make your solution even easier to use) Dean Arnold Presicient Corp.
RE: Another set of DBI docs
Followup: I've added more links to the TOC (including FAQ, Driver Writers Guide, and links to most DBDs). It may a take a while to load, due to the Javascript, but its more comprehensive now. For the curious: The classdocs are rendered w/ Pod::Classdoc and the TOC is generated with HTML::ListToTree. - Dean
Re: [OSCON] Informal BOF @ OBF
Tim Bunce wrote: On Fri, Jul 20, 2007 at 07:26:28AM -0700, Dean Arnold wrote: For those attending OSCON next week: I've talked Mssr. Bunce into an informal BOF at the Oregon Brewers Festival (http://www.oregonbrewfest.com/) across the bridge sometime Thursday afternoon. Let me know if you're interested so I can run logistics. My guess is we'll head over around 4-5 PM. That's not going to work for me. I'm giving at least two, maybe three, lightnings talks around that time. Then the lightning talks are followed by Larry's State of the Onion. 12:30pm thru 3pm looks like a good slot for the BoF. (That way I can be suitably lubricated before my talks...) Tim. Allright then. Shall we meet outside the Starbucks in the OCC @ 12:30 ? Not certain how much food/brew will be setup yet, but hopefully enough to get you into lightning frame of mind. - Dean
[OSCON] Informal BOF @ OBF
For those attending OSCON next week: I've talked Mssr. Bunce into an informal BOF at the Oregon Brewers Festival (http://www.oregonbrewfest.com/) across the bridge sometime Thursday afternoon. Let me know if you're interested so I can run logistics. My guess is we'll head over around 4-5 PM. Dean Arnold Presicient Corp.
Re: Announce: New DBI FAQ - please try it out
Tim Bunce wrote: Bob Hicks has helped get this setup and has kindly offered to act as editor and help coordinate contributions. Thanks Bob. Take a look at http://dbi.tiddlyspot.com and let us know what you think. Tim. 1. Any chance the driver descriptions can be initially populated from CPAN using the NAME one-liner + DESCRIPTION section ? 2. Perhaps a DBIx section is needed ? Dean Arnold Presicient Corp.
Re: Announce: New DBI FAQ - please try it out
Hicks, Robert wrote: 1. Any chance the driver descriptions can be initially populated from CPAN using the NAME one-liner + DESCRIPTION section ? Something like (or with NAME/DESC on a different line): NAME: DBD::ADO - A DBI driver for Microsoft ADO (Active Data Objects) DESCRIPTION: The DBD::ADO module supports ADO access on a Win32 machine. DBD::ADO is written to support the standard DBI interface to data sources. I don't have a problem doing that. While I'm being demanding...what about a simple hyperlink to the CPAN page (via the usual http://search.cpan.org/perldoc?DBD::Xyz) ? - Dean
Re: RFC: Adding Metadata to PurePerl DBDs
Jeff Zucker wrote: Dean Arnold wrote: Jeff Zucker wrote: I am preparing to add the ability to attach metadata to DBDs that subclass DBD::File. Will there be a std. i/f to get at that info from within SQL::Statement (and its subclasses) ? Yes definitely. though probably to start you'll just have access to the SQL::Translator object, there won't be specific S:S methods to access it for now. Thats fine (maybe preferable, since S::S won't need to keep up w/ every little SQL::Xlator change). Which leads to the question: will there be any hooks to enforce referential integrity if PK/FK support is provided ? That's certainly the long term plan. I'm not going to try to implement that at the same time because um (insert some good reason other than that I plain just don't have the time now). This will be some nice juicy hacking for interested parties, hint, hint. :-) Ouch, stop poking me! OW, quit it! Seriously, its on my priority list. Unfortunately, its at No. 6, and items 1 and 5 are hefty projects. BTW: Ref Integrity inevitably leads to the topic of triggers. Does S::X support trigger syntax, and will you store same in your YAML ? Can I assume the YAML files are persistent ? If so, is there any mechanism to assure they get deleted when a table is dropped ? Sigh, yeah I was planning that. Will you be kicking around OSCON again this year ? Perhaps we can chat about this there... - Dean
Re: RFC: Adding Metadata to PurePerl DBDs
Jeff Zucker wrote: I am preparing to add the ability to attach metadata to DBDs that subclass DBD::File. This will mean that DBI metadata methods such as primary_key() and column_info() will be available and also that other modules like DBIx::Class and Class::DBI will have access to the metadata. I've outlined my proposed interface below and would appreciate comments. The short version is that if the user sets the f_use_metadata database handle attribute, SQL::Translator will be used to parse CREATE TABLE statements and the resulting object will be stored serialized as YAML. When DBI metadata methods are called (and only then), the YAML file will be queried. snip/ Will there be a std. i/f to get at that info from within SQL::Statement (and its subclasses) ? (One of these days) I hope to finish up an extension that wants to get at column type info for better type conversion and esp. datetime support. And presumably PK/FK info might be exploited for improved JOIN processing. Which leads to the question: will there be any hooks to enforce referential integrity if PK/FK support is provided ? Can I assume the YAML files are persistent ? If so, is there any mechanism to assure they get deleted when a table is dropped ? Dean Arnold Presicient Corp.
Losing error info on return from failed $dbh-prepare
I've tested this on both 1.54 and 1.56; don't know if it exists prior to that, tho I'm fairly certain that it worked properly at some earlier release. I have an app the reuses the $sth variable. Having prepared/executed/fetched for one statement, it proceeds to prepare a new (erroneous) statement, for which the DBD internally properly reports the error and calls $dbh-set_err() (all of which is reported in the traces). As $dbh-prepare returns undef in that instance, it causes the existing $sth variable to invoke its DESTROY logic, which in turn invokes the finish() logic on the $sth. In the traces, I see the error info being cleared before I can step into my finish() method, so I assume DBI's finish() is somehow intercepting the call and clearing the error info at that point. Why ? The error information was stored on the connection, *not* the statement handle. Shouldn't the error info be left alone, regardless whether the statement handle is being destroyed ? Is there some additional step I need to apply to assure the error info is retained ? Or is this a bug ? I'm currently working around it by re-invoking $dbh-set_err within $sth-DESTROY after everything's cleaned up (only if there's an outstanding error), but that seems a bit dangerous e.g., if $sth-DESTROY is being called in response to a $dbh-DESTROY. Dean Arnold Presicient Corp.
Re: How to set $@ upon failure of eval
Patrick Galbraith wrote: Hi all! Quick question - how do I ensure from C something that I eval on the perl side, if it fails sets [EMAIL PROTECTED] Say for instance, I have something that checks something in C, I return in a way that eval/$@ is set. Thanks in advance! Patrick If I understand your issue, see the section on Warning and Dieing in perlapi (http://perldoc.perl.org/perlapi.html) In brief, use either croak() (or Perl_croak()), or set errsv directly (from the perlapi page): errsv = get_sv(@, TRUE); sv_setsv(errsv, exception_object); croak(Nullch); If OTOH you're trying to validate $@ from C, then you need to run under eval_pv/sv(). See Evaluating a Perl statement from your C program in perlembed (http://perldoc.perl.org/perlembed.html) HTH, Dean Arnold Presicient Corp.
Re: Where is DBI::Pool ?
Tim Bunce wrote:
On Sun, Apr 29, 2007 at 09:53:29AM -0700, Dean Arnold wrote:
It doesn't appear in CPAN search, and the link from
DBI's POD returns not found. I found an old email
pointing to Stas Beckman's personal website, but
its no longer there either.
I've attached the latest copy I have from Stas, which I'm sure is the
latest as I know neither Stas nor I have done any work on it recently.
OK, thnx.
FWIW: I'm trying to better understand how the brain surgery works
I'll happily answer any questions not answered by the take_imp_data() docs.
with an eye toward (a) how to make it work w/ pure Perl DBDs, and
well, as a first step I've just done this:
--- lib/DBI/PurePerl.pm (revision 9465)
+++ lib/DBI/PurePerl.pm (working copy)
@@ -844,7 +844,16 @@
warn private_data @_;
}
sub take_imp_data {
-undef;
+my $dbh = shift;
+# A reasonable default implementation. Typically a pure-perl driver would
+# have their own take_imp_data method that would delete all but the
+# essential items in the hash before einding with:
+# return $dbh-SUPER::take_imp_data();
+# Of course it's useless if the driver doesn't also implement support for
+# the dbi_imp_data attribute to the connect() method.
+require Storable;
+delete $dbh-{$_} for (keys %is_valid_attribute);
+return Storable::freeze($dbh);
}
sub rows {
return -1; # always returns -1 here, see DBD::_::st::rows below
All the really matters is that the driver's take_imp_data() method
produces a simple scalar value that the driver's dbi_imp_data attribute
can use to restore a working connection.
(b) what (if anything) I might do to accommodate it
in a threads::shared replacement I'm developing.
Could you give some more details?
Tim.
See http://www.presicient.com/sociable (very preliminary).
In brief, it provides:
- faster threads::shared (current benchmarks 2x faster for simple
scalar reads,
about 3x faster for writes on single core system; multicore
should be significantly faster)
- integrated thread queue for faster inter-thread (esp. duplex)
communication
- ...which provides for faster apartment threading
- interthread tie() (ie, tie() a variable in 1 or more threads,
access from an external thread activates the tie()'s in the
threads)
- transactional memory aka STM
As to the DBI::Pool interest, I want to make sure handles can
be pooled/cached in a Sociable container.
When updated to use Thread::Sociable, DBIx::Threaded should be
quicker than the current implementation, support the tied object i/f,
and retain the benefit of not requiring any DBD changes.
Alas, unless/until iCOW is implemented, the footprint of a single thread
per connection is too expensive for more than a few
connections. (however, a std. async DBI API would permit multiple connections
per apartment - but that would require DBD updates.)
Wrt take_imp_data() for pure Perl drivers: Your patch is similar to
what I thought, tho I'm unclear on the behavior of driver-specific vs. DBI
attributes ? Is your patch deleting the DBI attributes before
marshalling the remainder into a scalar (via Storable) ?
I've vague notions about avoiding the Storable step
if the connection context is stored in a Sociable (or threads::shared)
variable/object (tho threads::shared access overhead may be worse than
Storable marshalling).
Of course, there's the little matter of migrating filehandles
between threads...ideally, that could be solved by providing
Sociable (or threads::shared) handles (currently on Sociable's
TO DO list).
Dean Arnold
Presicient Corp.
Re: problems with perlcc not compiling modules into shared objects
Sean McMahon wrote: When I use perlcc to compile something like module.pm perlcc says compiling of shared objects is disabled. Does anyone know how to fix this? Anyone have tips on how to get perl to compile into a binary executable? thanks Sean Not sure what perlcc has to do with DBI, but I'll bite. As it says in the POD, perlcc is *very* experimental. IIRC, there was/is some discussion of removing it from the upcoming 5.10 release. To my knowledge, it has never worked reliably except on the most trivial of scripts. And unless you're prepared to roll up your sleeves and muck in, you're pretty much out of luck wrt support. If you really need to create a standalone executable, I'd suggest you look into PAR/pp, or any of the commercial alternatives (my personal fav is perl2exe). While they're not really compiled, they generally accomplish the same net effect. Dean Arnold Presicient Corp.
Re: ParamArrays and default execute_array() issue ?
Tim Bunce wrote:
On Fri, Sep 22, 2006 at 05:40:53PM -0700, Dean Arnold wrote:
Just so I'm clear:
DBI's default execute_array()/execute_for_fetch() requires the use of
positional (i.e., '?') placeholders. Drivers which Brequire named
placeholders must implement their own execute_array()/execute_for_fetch()
methods to properly sequence bound parameter arrays.
... or they can emulate positional placeholders (DBD::Oracle does this).
I'd guess that emulating positional placeholders would be both simpler
and more useful for general script portability.
Could you update the docs? (If you don't have write access to the dbi
repository yet, send me your auth.perl.org username and I'll give it to
you.)
Time to get this out of my TODO queue...
I noticed that DBI::DBD was silent on the whole area of
array binding, so here's what I've added.
- Dean
=head4 The execute_array(), execute_for_fetch() and bind_param_array() methods
In general, DBD's only need to implement Cexecute_for_fetch() and
Cbind_param_array. DBI's default Cexecute_array() will invoke the
DBD's Cexecute_for_fetch() as needed.
The following sequence describes the interaction between
DBI Cexecute_array and a DBD's Cexecute_for_fetch:
=over
=item 1
App calls C$sth-Egtexecute_array(\%attrs, @array_of_arrays)
=item 2
If C@array_of_arrays was specified, DBI processes C@array_of_arrays by
calling
DBD's Cbind_param_array(). Alternately, App may have directly called
Cbind_param_array()
=item 3
DBD validates and binds each array
=item 4
DBI retrieves the validated param arrays from DBD's ParamArray attribute
=item 5
DBI calls DBD's Cexecute_for_fetch($fetch_tuple_sub, [EMAIL PROTECTED]),
where C$fetch_tuple_sub is a closure to iterate over the
returned ParamArray values, and C[EMAIL PROTECTED] is an array to receive
the disposition status of each tuple.
=item 6
DBD iteratively calls C$fetch_tuple_sub to retrieve parameter tuples
to be added to its bulk database operation/request.
=item 7
when DBD reaches the limit of tuples it can handle in a single database
operation/request, or the C$fetch_tuple_sub indicates no more
tuples by returning undef, the DBD executes the bulk operation, and
reports the disposition of each tuple in [EMAIL PROTECTED]
=item 8
DBD repeats steps 6 and 7 until all tuples are processed.
=back
E.g., here's the essence of LDBD::Oracle's execute_for_fetch:
while (1) {
my @tuple_batch;
for (my $i = 0; $i $batch_size; $i++) {
push @tuple_batch, [ @{$fetch_tuple_sub-() || last} ];
}
last unless @tuple_batch;
my $res = ora_execute_array($sth, [EMAIL PROTECTED],
scalar(@tuple_batch), $tuple_batch_status);
push @$tuple_status, @$tuple_batch_status;
}
Note that DBI's default execute_array()/execute_for_fetch() implementation
requires the use of positional (i.e., '?') placeholders. Drivers
which Brequire named placeholders must either emulate positional
placeholders (e.g., see LDBD::Oracle), or must implement their own
execute_array()/execute_for_fetch() methods to properly sequence bound
parameter arrays.
Re: coderef instead of filename for trace() ?
Tim Bunce wrote:
On Fri, Dec 15, 2006 at 03:26:46PM -0800, Dean Arnold wrote:
One item: I'm currently using a separate 19fhtrace.t test script;
should I merge it into 09trace.t ? Or leave it separate ?
(I'll vote for separate, since its easier to test the feature
standalone that way)
I agree.
OK. And your 2 examples worked (w/ a little tweaking), so I've added
them to the test script. Revised POD for Tracing To Layered Filehandles
below. I'll checkin once you're OK w/ the POD.
- Dean
=head2 Tracing to Layered Filehandles
BNOTE:
=over 4
=item *
Tied filehandles are not currently supported, as
tie operations are not available to the PerlIO
methods used by the DBI.
=item *
PerlIO layer support requires Perl version 5.8 or higher.
=back
As of version 5.8, Perl provides the ability to layer various
disciplines on an open filehandle via the LPerlIO module.
A simple example of using PerlIO layers is to use a scalar as the output:
my $scalar = '';
open( my $fh, +:scalar, \$scalar );
$dbh-trace( 2, $fh );
Now all trace output is simply appended to $scalar.
A more complex application of tracing to a layered filehandle is the
use of a custom layer (IRefer to LPerlio::via Ifor details
on creating custom PerlIO layers.). Consider an application with the
following logger module:
package MyFancyLogger;
sub new
{
my $self = {};
my $fh;
open $fh, '', 'fancylog.log';
$self-{_fh} = $fh;
$self-{_buf} = '';
return bless $self, shift;
}
sub log
{
my $self = shift;
return unless exists $self-{_fh};
my $fh = $self-{_fh};
$self-{_buf} .= shift;
#
# DBI feeds us pieces at a time, so accumulate a complete line
# before outputing
#
print $fh At , scalar localtime(), ':', $self-{_buf}, \n and
$self-{_buf} = ''
if $self-{_buf}=~tr/\n//;
}
sub close {
my $self = shift;
return unless exists $self-{_fh};
my $fh = $self-{_fh};
print $fh At , scalar localtime(), ':', $self-{_buf}, \n and
$self-{_buf} = ''
if $self-{_buf};
close $fh;
delete $self-{_fh};
}
1;
To redirect DBI traces to this logger requires creating
a package for the layer:
package PerlIO::via::MyFancyLogLayer;
sub PUSHED
{
my ($class,$mode,$fh) = @_;
my $logger;
return bless \$logger,$class;
}
sub OPEN {
my ($self, $path, $mode, $fh) = @_;
#
# $path is actually our logger object
#
$$self = $path;
return 1;
}
sub WRITE
{
my ($self, $buf, $fh) = @_;
$$self-log($buf);
return length($buf);
}
sub CLOSE {
my $self = shift;
$$self-close();
return 0;
}
1;
The application can then cause DBI traces to be routed to the
logger using
use PerlIO::via::MyFancyLogLayer;
open my $fh, ':via(MyFancyLogLayer)', MyFancyLogger-new();
$dbh-trace('SQL', $fh);
Now all trace output will be processed by MyFancyLogger's
log() method.
Re: coderef instead of filename for trace() ?
Tim Bunce wrote:
sv_2io never returns false, it croaks. But since you're only calling it
if SvROK is true, and the croak error is meaningful (Bad filehandle ...)
I think that's fine. Also, IoOFP(io) might be false in some odd cases.
So I'd suggest a slight tweak:
if (SvROK(file)) {/* assume it's a filehandle */
io = sv_2io(file); /* will croak if not */
if (!io || !(fp = IoOFP(io))) {
warn(DBI trace filehandle is not valid);
return 0;
}
}
else { ... }
OK, thnx. BTW: Does something need to be refcnt'd here ? It occurs
to me that an app could code something odd and pass a handle
that would go out of scope, leaving DBI with a bogus handle,
unless we refcnt it.
Question is, what's refcntable ? io ? If so, we need an add'l
slot in DBIS to save it (so it can be decremented/discarded,
rather than PerlIO_close, when a close would normally occur).
BTW: I also swiped the U16 spare_pad variable from DBIS to
make a logfp_is_ours flag which gets set when DBI PerlIO_open's
the logfile, and cleared when it gets set to STDERR/STDOUT, or
the input is a filehandle, so DBI doesn't attempt to close
a filehandle it didn't open.
Will need some tests of course. Both for writing to a plain filehandle
and a tied one.
Did that last night, along w/ some POD updates.
- Dean
coderef instead of filename for trace() ?
(Nothing surfaced in DBI.pm or the TODO list, nor the usual
net searches, so...)
Has there been any thought to extending the target for
trace() beyond simple filenames ? I'm looking for a means
to supply either a coderef, or maybe an object implementing
a simple print/write interface. My need is to be able to direct
the traces to a centrally managed logging facility, and be able
to turn them off/on as needed, e.g:
my $logger = MyFancyLogger-new();
DBI-trace(2, sub { $logger-logInfo(DBI trace: . $_[0]); });
I realize the tracing is currently buried in XS code, but
it appears the HandleError invokation code could be copied
to provide the ability.
Has this been discussed before, or have I overlooked something that already
exists ? It seems like a capability that would be useful.
Dean Arnold
Presicient Corp.
Re: coderef instead of filename for trace() ?
H.Merijn Brand wrote: On Wed, 13 Dec 2006 11:31:00 -0800, Dean Arnold [EMAIL PROTECTED] wrote: Has there been any thought to extending the target for trace() beyond simple filenames ? I'm looking for a means to supply either a coderef, or maybe an object implementing a simple print/write interface. My need is to be able to direct the traces to a centrally managed logging facility, and be able to turn them off/on as needed, e.g: Instead of a coderef, I would suggest accepting file handles. As the simple open my $handle, , \$scalar; is a way to get your trace in a string if that would work, passing that string to a sub would solve your case too. I've just done something similar in a module that ties filehandles to functions, so if trace () were to accept file handles in all allowed formats (also IO::Handle), that would be generic enough to cover all needs. To clarify, I presume you mean trace() is modified to support (possibly tied) filehandles in addition to simple filenames ? And then the app can supply a tied filehandle ? - Dean
Re: coderef instead of filename for trace() ?
Tim Bunce wrote:
On Wed, Dec 13, 2006 at 11:31:00AM -0800, Dean Arnold wrote:
I realize the tracing is currently buried in XS code, but
it appears the HandleError invokation code could be copied
to provide the ability.
Only for errors, not for general tracing.
Sorry, I meant in a general sense, the plumbing could be copy/paste/edit'd.
Has this been discussed before, or have I overlooked something that already
exists ? It seems like a capability that would be useful.
It has been raised before. The only viable approach is to use a tied
filehandle - but I've never got round to doing the plumbing.
I'd imagine that some simple changes to set_trace_file() in DBI.xs would
suffice.
Are you saying that just changing set_trace_file() to accept a filehandle
would suffice ? Then the app can supply its own tie'd filehandle to
trace() ? The tied filehandle occured to me, but as the trace stuff is XS code,
I was concerned about (1) whether the PerlIO XS functions were smart about tie'd
handles, and (2) the caveat in perltie ie This is partially implemented now..
So, if I were to add just this bit of code to set_trace_file:
if (SvROK(file)) {
/* DAA must be a filehandle...we hope */
/* but how do we tell, and how do we get the PerlIO object from an SV ? */
fp = (PerlIO *)file;
}
else { /* the current filename stuff */ }
..would it magically work ? Any idea how I get the PerlIO* object from the
SV* input ? I don't see anything in perlapio, perlapi, or perlguts to guide me.
TIA, Dean
Re: coderef instead of filename for trace() ?
After a bit more research, I think I've figured out
how to get the PerlIO object from an SV:
IO *io;
if (!file) /* no arg == no change */
return 0;
/* XXX need to support file being a filehandle object */
if (SvROK(file)) {
/* DAA must be a filehandle...we hope */
/* but how do we tell, and how do we get the PerlIO object from an SV ? */
io = sv_2io(file);
if (io) {
fp = IoOFP(io);
}
else {
warn(Input trace filehandle is not valid);
return 0;
}
}
else { /* do things the old way */ }
Does that make sense ? (I kyped the code from some Perl/Tk internals)
- Dean
Re: Fwd: [Pdl-porters] PDL - RDBMS linkage
Jonathan Scott Duff wrote: I'm kind of looking for a way to get perl out from in between the DBMS and PDL. Sure, I can pull all of the data from the DBMS into perl-space and then create a piddle from that, but it would be nicer if there were a way to do something like this: use PDL; use DBI; use DBI2PDL; # okay, this name sucks, but work with me here :) # ... my $p = $sth-fetchrow_pdl;# or ... my $q = $sth-fetchall_pdl; # or whatever ... Basically I'm thinking that this hypothetical module would add *_pdl routines that would complement the *_array routines of DBI. Of course there will have to be some smarts to deal with non-numeric columns and such, but that's a detail best left for an actual implementation. :-) I don't know enough about PDL's insides to know just how possible this is and I just realized that I probably don't know enough about DBI's insides either. -Scott Disclaimer: I'm blissfully ignorant of PDL other than knowing its a number crunching tool. 1. Consider a DBI subclass (as a DBIx extension, e.g., DBIx::PDL). Presumably, adding a couple functions such as you're after should be straightforward, while preserving all the other DBI semantics/syntax. 2. And (as an SQL fanboy) I'd consider the ultimate disintermediation: a DBI subclass to make it appear that PDL was running in the DBMS. See DBIx::Chart for a similar concept applied to charting. It would be interesting to see a SQL extension with matrix operators (perhaps as some sort of exotic JOIN ?): my $dbh = DBIx::PDL-connect($dsn, $user, $passwd); my $results = $dbh-selectall_arrayref( 'SELECT * from (SELECT * from TABLE1) a X (SELECT * FROM TABLE2) b'); (Yes, my claims to sanity are often disputed). Dean Arnold Presicient Corp.
Re: Question regarding the naming of a MySQL server-side protocol module
Philip Stoev wrote: Hello, I am currently developing a Perl module that will handle the MySQL protocol server-side, allowing the creation of mysql-like servers that will answer queries from external data (e.g. another database via DBI, etc.) I would like to ask how do you think this module should be named. The person that has previously worked on that called his code Net::MySQLd (not released on CPAN), however I am aiming for a module that does more than read() and write() so I thought something in the DBIx namespace would be appropriate. After all, it would be possible to construct a replacement of DBI::ProxyServer using this module and the mysql protocol rather than RPC. At the same time, I wanted to avoid any run-ins with MySQL's patent department, that is why I though DBIx::OurPerl would be appropriate and at the same time will unfortunately be completely non-descriptive. Thank you in advance for any suggestions. Philip Stoev First, let me offer kudos on this, I'd love to see it. What version of the MySQL protocol will you be implementing ? E.g., plugging DBIx::Chart into this would let MySQL JDBC/ODBC clients render charts directly from SQL. (Any volunteers to do a Pg version ? ;^). That said, I'd vote against using a DBIx toplevel namespace as this doesn't strictly have a data mgmt purpose (unless the add'l functions you hint about will be doing that). The original Net::MySQLd is probably a good start, as there's already a Net::MySQL to implement the client side. Or maybe Net::MySQLServer to better disambiguate. OTOH, there's an equivalent module for Sybase/TDS called Sybase::TdsServer, and there's at least one MySQL toplevel module, so MySQL::ServerSide might be reasonable as well. Dean Arnold Presicient Corp.
Re: Announce: DBI-1.53 release candidate 1
Tim Bunce wrote:
Also, FYI: on AS 5.8.6, WinXP:
t\40profile..ok 24/45
# Failed test in t\40profile.t at line 240.
# Structures begin differing at:
t\40profile..NOK 32# $got-{t\40profile.t} = Does not
exist
# $expected-{t\40profile.t} = HASH(0x1b7b4e0)
All else passed OK.
Looks like a windows / vs \ issue. Could you look into it for me as I
don't use windows at all. It's probably trivial.
Tim.
Yep. Here's my hacked patch:
--- 40profile.t Wed Oct 25 04:12:31 2006
+++ 40profile.t.new Wed Oct 25 13:32:41 2006
@@ -229,7 +229,13 @@
$dbh-{Profile}-{Data} = undef;
my ($file, $line1, $line2) = (__FILE__, undef, undef);
+
+if ($^O eq 'MSWin32') {
+ $file =~ s:.*\\::;
+}
+else {
$file =~ s:.*/::;
+}
sub a_sub {
$sth = $dbh-prepare(select name from .); $line2 = __LINE__;
}
Re: DBI + Math::BigInt + Fedora causes $drh-connect() to silently die
Dean Arnold wrote:
Basically, my driver was causing DBI to silently die on return from
the dr::connect() routine, even tho the connection had been
completely successfully setup in the driver. DBI-trace(15)
didn't show anything, other than DBI::END was called immediately
on return from $drh-connect() (inside DBI::connect()).
I spose I should grab a simple Pure Perl DBD, and hack in some Math::BigInt
calls to see if I can induce it in a stripped down driver
(DBD::DBM ? DBD::CSV ? suggestions ?)
Followup to my OP:
1. I tried hacking some Math::BigInt::bmod()s into DBD::File::dr::connect() and
DBD::CSV::dr::connect(), but couldn't repro the issue.
2. After some tinkering, I tried wrapping eval {}'s
around the Math::BigInt calls in my driver ...and that
does the trick. While it doesn't explain the problem,
it lets me GOWI.
- Dean
execute_array() progress reporting ?
While testing my execute_array() implementation, I've added
something that might be generally useful: a progress-report
callback. I realize that ArrayTupleFetch might be adapted
to provide the concept with a bit more coding in the app, but
if all the app is doing is either dumping a file to the DBMS,
or using a $sth to supply the data, it might be useful to have
a ArrayProgress parameter that gets called occasionally.
My driver specific version is a stmt handle attribute that
supplies an arrayref of [ $tupleincr, $callback ], where
$tupleincr is the minimum number of tuples between invoking $callback,
and $callback is just s CODE ref that gets called with the
current number of tuples sent.
E.g.,
$sth-execute_array({
ArrayTupleStatus = [EMAIL PROTECTED],
ArrayTupleFetch = $sth,
ArrayProgress = [ 100, \report_progress ]
});
sub report_progress {
my $tuples = shift;
print \r Sent $tuples...;
}
Would this be a useful addition to the API, or just more code clutter ?
- Dean
Re: execute_array() progress reporting ?
Tim Bunce wrote:
On Tue, Sep 26, 2006 at 11:02:00AM -0700, Dean Arnold wrote:
My driver specific version is a stmt handle attribute that
supplies an arrayref of [ $tupleincr, $callback ], where
$tupleincr is the minimum number of tuples between invoking $callback,
and $callback is just s CODE ref that gets called with the
current number of tuples sent.
E.g.,
$sth-execute_array({
ArrayTupleStatus = [EMAIL PROTECTED],
ArrayTupleFetch = $sth,
ArrayProgress = [ 100, \report_progress ]
});
sub report_progress {
my $tuples = shift;
print \r Sent $tuples...;
}
Would this be a useful addition to the API, or just more code clutter ?
Umm. Doesn't really seem worth it when it's easy to do via
ArrayTupleFetch. Something like:
$sth-execute_array({
ArrayTupleStatus = [EMAIL PROTECTED],
ArrayTupleFetch = sub {
report_progress($fetch_count) if ++$fetch_count % 100 == 0;
return $sth-fetchrow_arrayref;
},
});
(If you pass ArrayTupleFetch as an sth then execute_array just turns it
into a closure anyway, so there's no extra cost there.)
Tim.
OK. I was on the fence about it myself, but thought it worth mentioning.
- Dean
Re: ParamArrays and default execute_array() issue ?
Tim Bunce wrote:
On Tue, Sep 19, 2006 at 01:41:22PM -0700, Dean Arnold wrote:
I stumbled on an oddity in execute_array() I'm hoping can
be clarified (it maybe a bug, or just an undoc'd requirement
of driver authors)
The following bit of code appears to require that the hash returned by
ParamArrays use monotonically increasing integers as keys:
my %hash_of_arrays = %{$sth-{ParamArrays}};
some code here
my @bind_ids = 1..keys(%hash_of_arrays);
my $tuple_idx = 0;
$fetch_tuple_sub = sub {
return if $tuple_idx = $maxlen;
my @tuple = map {
my $a = $hash_of_arrays{$_};
ref($a) ? $a-[$tuple_idx] : $a
} @bind_ids;
++$tuple_idx;
return [EMAIL PROTECTED];
};
But the ParamArrays docs indicate that the returned hash can
use something other than integers as keys.
Umm. Good point.
It's related to the fact that if you use 'named' placeholders with a
non-array statement then you have to use bind_param() and not supply
any bind values to execute().
The problem with execute_array is that it's built on execute_for_fetch
and that requires an ordered set of bind values.
I've worked around this by implementing an internal mapping
so that named parameters have a matching positional value,
which is used when ParamArrays/ParamValues are assigned
within bind_param{_array]().
snip
I think I'm going to take the view that all drivers should support
'?' style placeholders and that those should be used for execute_array
and execute_for_fetch.
For database that don't support '?' style placeholders it's pretty
trivial for the driver to rewrite them as 'p1', 'p2' etc etc.
That's exactly what DBD::Oracle as always done.
Just so I'm clear:
DBI's default execute_array()/execute_for_fetch() requires the use of
positional (i.e., '?') placeholders. Drivers which Brequire named
placeholders must implement their own execute_array()/execute_for_fetch()
methods to properly sequence bound parameter arrays.
Could you update the docs? (If you don't have write access to the dbi
repository yet, send me your auth.perl.org username and I'll give it to you.)
Tim.
OK, after I finish up my release to shake out anything else. I've got a few
other items I'd like to note in the DBI::DBD pod wrt ParamValues/ParamArrays...
actually, I'm going to try and a create a detailed list of attributes
that do or don't get forwarded to a driver's STORE/FETCH methods...ParamValues/
ParamArrays gave me fits until I figured out DBI didn't route them thru
my FETCH(). Plus the strangeness of _new_sth() wrt which attributes it will
save and which it won't.
- Dean
ParamArrays and default execute_array() issue ?
I stumbled on an oddity in execute_array() I'm hoping can
be clarified (it maybe a bug, or just an undoc'd requirement
of driver authors)
The following bit of code appears to require that the hash returned by
ParamArrays use monotonically increasing integers as keys:
my %hash_of_arrays = %{$sth-{ParamArrays}};
some code here
my @bind_ids = 1..keys(%hash_of_arrays);
my $tuple_idx = 0;
$fetch_tuple_sub = sub {
return if $tuple_idx = $maxlen;
my @tuple = map {
my $a = $hash_of_arrays{$_};
ref($a) ? $a-[$tuple_idx] : $a
} @bind_ids;
++$tuple_idx;
return [EMAIL PROTECTED];
};
But the ParamArrays docs indicate that the returned hash can
use something other than integers as keys.
I've worked around this by implementing an internal mapping
so that named parameters have a matching positional value,
which is used when ParamArrays/ParamValues are assigned
within bind_param{_array]().
So...bug, or requirement ? I don't know if anything can be
done to correct it without some new DBI attribute to provide
the parameter name-to-position mapping, so I'm guessing
its a requirement, and maybe ParamArrays (and ParamValues and ParamTypes)
need doc updates to remove the 'flexible' definition ? Or is a new
attribute preferable ? or maybe just an extension to ParamTypes to include
a POSITION attribute ? Or maybe the requirement is that drivers w/ named
placeholders have to implement their own execute_array() ?
Dean Arnold
Presicient Corp.
Re: Safely timing out DBI queries
Tim Bunce wrote: Which brings me back to the notion of non-blocking requests. Assuming many/most client libs do support an async capability, and a OOB cancel, then it should be possible to standardize the behavior externally. Attempting to standardize, let alone implement, non-blocking requests for the current DBI is a far bigger task than the above. On the other hand, I'd be *delighted* if you, or anyone else, would like to champion the work. Tim. In case anyone's interested, here's the dialog from the last time async support came up: http://www.mail-archive.com/dbi-dev@perl.org/msg03407.html - Dean
Re: Safely timing out DBI queries
(I've added dbi-dev as this seems relevant)
Tim Bunce wrote:
On Sat, Sep 16, 2006 at 08:31:54PM -0400, Sam Tregar wrote:
On Sat, 16 Sep 2006, Dean Arnold wrote:
I think your best bet might be to work with the DBD::mysql maintainers
to implement some driver-specific nonblocking versions of
execute/prepare (and maybe fetch), as well as support for
'out of band' cancel.
That's an interesting idea.
For any driver that uses a network socket to connect you could close()
the socket in the signal handler to (relatively) safely timeout.
Should be fairly clean/safe for the db client library state, though
unsafe signals means there's still a chance perl's internal state could
be corrupted.
On the server-side the query may be left running on databases that
don't detect disconnects (which I think still includes mysql).
A minor problem with this approach is how to determine the socket file
descriptor. For drivers that don't/can't make the socket fd available,
it requires checking which fd's are open before and after the connect.
Kludgy but effective.
Yow...how many client libs surface the socket descriptor ? Not too many
I'm aware of. And the 'sift and kill' approach of finding fd's is likewise
an opportunity for disaster, since you don't know how many fd's may have
been opened in the process of setting up a connection, and some platforms
don't always update fd status as quickly as one might like (I've been down
this particular road to hell for similar purposes). And just dumping the
connection is a pretty severe reaction to something most DBMS's actually
have i/f's to handle gracefully.
Right now I'm putting together
DBIx::Timeout which implements my fork()-based timeout in a reusable
package. Seems to work, although I've learned to expect forking code
to have unexpected bugs which take time to shake out.
It's mysql specific currently, using $id=$dbh-{thread_id} and do(KILL $id).
(And shouldn't thread_id be mysql_thread_id?)
Would be nice to generalise it. There was talk sometime ago (perhaps on
dbi-dev) of extending the DBI api along these lines:
$id = $dbh-{SessionId};
$dbh-kill_session($id);
Would certainly be simple for me to add to the DBI. Then it's just a
simple matter of getting the drivers to implement it :)
Er, but how ? Unless/until the DBI is threadsafe, the only way
for kill_session() to work is by breaking the DBD out of the
current blocking request. Which I assume is to be accomplished
by throwing signals around ?
Keep in mind that the behavior of various DB client libs
wrt signals on any given platform is certainly not standardized;
some blow up, some require extra API calls to handle things, others
just swallow the signal.
(I'll note that signals are conclusive proof that UNIX
was developed in an era when recreational narcotics were readily available
and inexpensive.)
Oh, and then there's the little matter of non-POSIX platforms. Trying to
reliably
use Perl + fork + signals + some external library on Win32 will certainly
keep a developer busy for a few days.
Once an app (or DBD) is down the rabbit hole of a DBMS client library,
any attempt to standardize such behavior is likely to meet with limited success.
Which brings me back to the notion of non-blocking requests.
Assuming many/most client libs do support an async capability, and a OOB cancel,
then it should be possible to standardize the behavior externally.
Dean Arnold
Presicient Corp.
Re: Whats the deal with NUM_OF_FIELDS ?
Tim Bunce wrote: Probably no good reason, or at least none that I can remember. Patches welcome! Send me your auth.perl.org username and I'll give you commit rights... I don't have a patch, but here's what appears to be going on: DBI::_new_sth() calls DBI::_new_handle(), which creates both the inner tied hash with the supplied attributes hash, then creates the outer blessed version of said hash. Then it calls _setup_handle() to finish things up. _setup_handle() ends up in dbih_setup_handle(), which populates the hash with attributes inherited from the parent object, and then *explicitly sets NUM_OF_FIELDS to -1* without ever verifying if NUM_OF_FIELDS is already defined. Furthermore, none of the attributes to _new_sth/_new_handle ever get processed by dbih_set_attr_k() (ie, DBI's STORE()), so any attribute processing performed there is not applied to constructor-provided attributes. So it appears that _new_sth()/_new_dbh() constructor attributes only support driver-specific attributes and/or inocuous DBI-level attributes (prior to my recent efforts, I've only ever set Statement and CursorName), and the driver must apply any other DBI-level attributes *after* the handle is created...which is a minor PITA. Might dbih_setup_handle() be able to call dbih_set_attr_k() with each supplied attribute to provide that processing ? Or am I overlooking something ? OTOH, this horse is out of the barn and 2 miles down the road, so just doc'ing the situation may be best for now. - Dean
Whats the deal with NUM_OF_FIELDS ?
This has been a puzzler for me for some time, so hopefully someone can edify me. I have a pure Perl driver on which I'm doing some serious refactoring (removing 6 years of accumulated cruft), and added a simple function to build $sth's from an input argument hash. Since most of the arguments are just $sth attributes, I simply called $sth = DBI::_new_sth($dbh, \%args); which works fine...until I later attempt to call $sth-_set_fbav() to stash a row, where it chokes with NUM_OF_FIELDS probably isn't set right. After verifying everything is where I expect it to be, I run with DBI::PurePerl...and it runs just fine. So after a lot of trial error messing about with _new_sth(), I discover I can pass in all of my argument hash *except* NUM_OF_FIELDS; it has to be set *after* I create the $sth...even tho I've set all of NUM_OF_PARAMS/NAME/TYPE/PRECISION/etc. in the call to _new_sth(). Is this a bug, or is there a reason for this ? If the latter, is it documented anywhere ? As the author of several pure Perl DBD's, (and hopefully a few more in the near future), I've always worked around this with lots of explicit per-field assignment code that could've/should've just been passed to the constructor. Even if I have to leave that code in place, it would be nice to understand why... - Dean
Re: execute_array enhancement to DBI
While we're kicking array binding around, I've got a couple ideas/issues to address... 1) I'm wondering about the desirability of an add'l status code to indicate a parameter tuple could not be sent to the server ? E.g., in my case, there's a limit to the size of message I can send to the server, so if an app binds more tuples than can be sent, the driver must either A) retain some state so it can pick up where the prior request left off (which may open the window to hell if things go wrong) B) flag the whole execution as failed (which is kindof a pain for the app) C) send as many tuples as possible, then mark the remainder as unsent and the app can then resubmit as needed, thereby alleviating the burden on the driver. I'll be doing (C) for DBD::Teradata using a status code of -2, but thought it might be generally useful to reserve a status code for that purpose ? 2) I've got a version issue: newer versions of Teradata support bulk operations, but older versions don't, and I need to support both. If I implement execute_array() in my driver, but discover the DBMS is an older version, how can I safely throw the call back up to the DBI default implementation ? (Its probably something very simple and obvious, but its got me stumped...) Dean Arnold Presicient Corp.
Re: execute_array enhancement to DBI
Tim Bunce wrote: The default execute_array method calls execute_for_fetch. So drivers only have to implement execute_for_fetch - and execute_for_fetch is designed to allow drivers to decide batch sizes for themselves. Please excuse my ignorance, I've had severe cognitive turbulence wrt exec_for_fetch for some time now, so hopefully you can confirm or correct the following ad-hoc sequence diagram of the process ? (This is for the simplest case, no app-provided fetch_tuple_sub) App calls $sth-execute_array(\%attrs, @array_of_arrays); = DBI processes @array_of_arrays by calling DBD's bind_param_array() = DBD validates and binds each array = DBI retrieves the validated param arrays from DBD's ParamArray attribute = DBI creates $fetch_tuple_sub to simply iterate over the returned ParamArray values = DBI calls DBD's execute_for_fetch = DBD calls $fetch_tuple_sub to retrieve each parameter tuple = when DBD reaches batch limit, or no more tuples, DBD sends to DBMS, and reports status in [EMAIL PROTECTED] However, that still leaves open the issue of unsent tuples, e.g., a connection failure occurs after 10,000 tuples are sent and committed, and 10,000 tuples remain. How do we inform the app of the unsent tuples (for recovery purposes) ? (Which is one use of the suggested -2 status code). Using the above scenario, the DBD would just keep consuming tuples and marking their status as -2 (which might be a rather lengthy process unless the app limits itself to reasonable chunks of tuples per execute_array() call). E.g., since the default DBI exec_for_fetch() doesn't have a way to bust out of its loop until all tuples are consumed, the default execute() return code may pile up a lot of error msgs to the effect No connection available (short of some HandleError closure, I guess ?). Whereas, using the simple status code, the app can just submit a batch of tuples via execute_array(), then scan the @tuple_status to identify any unsent tuples (presumably immediately preceded by a status with an error code). Once the connection is recovered, the app can just resubmit the unsent tuples. (This may be a difficult issue for $sth based fetch_tuple_sub unless some known ordering were applied in the source statement). That said, I guess I'm free to return the -2 status and note it as driver-specific behavior. In perl: $h-SUPER::execute_array(...) (but I'd recommend just implementing execute_for_fetch instead). Which would presumably just route each tuple to regular execute() for the legacy case ? - Dean
Re: Creating an ODBC driver; ODBC ::SOAP
Karjala wrote: How would one go about creating his own ODBC driver in Windows? While that probably isn't terribly relevant to this list, I'll humor your suggestion... Is it possible to do so with perl? Theoretically, yes. See http://perldoc.perl.org/perlembed.html. However, from a practical perspective, I'd suggest its likely to be a very painful process. In my less lucid moments (which some believe is most of them), I considered a similar albeit more general solution to create an ODBC driver which could load/encapsulate *any* DBI driver. Why ? Because some of the more exotic DBI drivers might be very useful to the general, Perl-ignorant data consuming public (primarily for all those Windows apps which rely on ODBC, e.g., Excel). Which would permit things like DBD::iPod, DBD::Google, DBD::Amazon, etc. to be plugged into those tools. While I've since struck upon a more flexible, yet more powerful solution (which must remain stealthy for the present), I suspect the original concept might be a welcome addition to CPAN, and a great way to evangelize Perl to the computing masses. Is there an ODBC driver for Windows that will produce and send SOAP statements to a website instead of SQL statements to a database? Probably somewhere, tho likely tailored to a specific web service. While DBD::Amazon currently relies on REST, it could've been implemented as SOAP. Likewise, the nascent DBD::eBay (which relies on Net::eBay) doesn't truly use SOAP, just the XML API, but could use SOAP. The trick is implementing an SQL syntax and schema for the web service you're trying to map. See SQL::Statement (and maybe DBD::AnyData or DBD::Amazon) for how to do that. So you've basically got 2 tasks before you: an ODBC wrapper for DBI drivers, and a DBD::SOAP or somesuch for your web service. Or you can just start hacking a lot of C code... HTH, and best of luck, Dean Arnold Presicient Corp.
ChildHandles, Scalar::Util, Makefile.PL
I've recently surfaced a bug in a pure Perl DBD that
ChildHandles solves quite nicely...except it appears
to require Scalar::Util, which is not a CORE module
(tho perhaps it should be). While DBI gracefully degrades
if Scalar::Util isn't found - and ActiveState appears to include
it in its releases -, I'd suggest an addition to
Makefile.PL to test for its presence, and provide
a warning ala the RPC::PlServer test ? And perhaps
it should be included in Bundle::DBI ?
Here's my proposed addition at line 110 of Makefile.PL
for DBI-1.50 (the min version may need tweaking):
eval use Scalar::Util 1.14 ();;
if ($@) {
push @missing, 'Scalar::Util';
print \a,'MSG';
*** Note:
The optional Scalar::Util module is not installed.
If you want to use the ChildHandles attribute,
you'll need to install Scalar::Util.
You can install it any time after installing the DBI.
You do *not* need this module for typical DBI usage.
MSG
sleep 2;
}
Dean Arnold
Presicient Corp.
Re: ChildHandles, Scalar::Util, Makefile.PL
Dean Arnold wrote: I've recently surfaced a bug in a pure Perl DBD that ChildHandles solves quite nicely...except it appears to require Scalar::Util, which is not a CORE module (tho perhaps it should be). While DBI gracefully degrades if Scalar::Util isn't found - and ActiveState appears to include it in its releases -, I'd suggest an addition to Makefile.PL to test for its presence, and provide a warning ala the RPC::PlServer test ? And perhaps it should be included in Bundle::DBI ? (Forgive my self-response...) Upon further review, it seems the DBI code implements its own weakening in C ? Yet still performs the HAS_WEAKEN test in DBI.pm ? Can anyone clear up my confusion ? Is Scalar::Util required or not ? It appears to still be required for DBI::PurePerl... Dean Arnold Presicient Corp.
Re: ChildHandles, Scalar::Util, Makefile.PL
Please disregard. I've located the module in CORE (just in a non-obvious location). Sorry for the interruption. Dean Arnold Presicient Corp.
Re: Status of Callbacks
David Wheeler wrote: Hi All, I just noticed that, while the Callbacks attribute has been implemented in SVN, it still hasn't made it into a release. I could have sworn that it was a long time ago that I worked on them. And didn't I write some docs for them? So, what's the status on Callbacks? Are they ready to go into a release? Thanks, David Er, care to refresh our collective memories w/ the executive overview of what callbacks is ? My DBI inbox doesn't surface anything, and Google ain't much help either...I see a blurb in the CPAN roadmap doc, but thats it. Dean Arnold Presicient Corp.
Re: Status of Callbacks
David Wheeler wrote: On Nov 1, 2005, at 12:11 PM, David Wheeler wrote: So, what's the status on Callbacks? Are they ready to go into a release? Ah, I see from this post: http://www.mail-archive.com/dbi-dev@perl.org/msg03793.html That Steven Schubiger was going to pen some docs for Callbacks. Since that didn't happen, perhaps I should have a crack at it. Yeah, the thread you pointed to requires a bit more than casual knowledge of what is being discussed/implemented :^/. It'd be nice to see some actual docs. Also, the reason I'm following up on this is that I've found a perfect use for callbacks. DBD::SQLite lets you declare database functions and aggregates _in Perl_! This is a slick feature of which I plan to take full advantage. I have a quick example here: http://www.justatheory.com/computers/databases/sqlite/ custom_perl_aggregates.html Any chance these would work in Pg's PL/perl, extproc_perl, or the (whatever MySQL does) ? While I have many other things on my plate, this might be a nice addition to DBIx::Threaded as well (once I've got proxied closures working in Thread::Apartment). Dispatching into another thread might have some interesting applications... Dean Arnold Presicient Corp.
[ANNOUNCE] DBIx-Threaded-0.10-RC1
I'm pleased to announce the availability of the first release candidate of DBIx-Threaded, an apartment threaded DBI subclass to permit DBI objects to be shared between Perl threads. The docs are at http://www.presicient.com/dbixthrd The tarball is at http://www.presicient.com/dbixthrd/DBIx-Threaded-0.10-RC1.tar.gz (You'll need Thread::Queue::Duplex 0.12 from CPAN, too.) I've tested with DBD::CSV, DBD::SQLite, DBD::Teradata, and DBD::ODBC on a number of platforms (see the Test Notes section of the docs). While I have encountered some issues with various Perl versions, 5.8.4 and 5.8.6 seem the most thread-friendly at this point. I'd appreciate test reports for other drivers, perls, or platforms. I'll CPAN it after its had a week or 2 to ripen. Regards, Dean Arnold Presicient Corp.
Strangeness w/ Fedora Core 4 and Perl 5.8.7
Strap in, this is a bit of a saga...
For DBIx::Threaded testing, I've updated a machine
to Fedora Core 4. Given RedHat's notoriety wrt
the Perl they deliver, after installing, I immediately
pull down AS 5.8.7 for Linux and begin testing.
Normal cases work OK, but using threads leads to
bizarre behavior at various times. Since DBIx::Threaded
is behaving nicely on OS X/AS5.8.7 and WinXP/AS5.8.3
(tho WinXP+5.8.3 seem to still have some thread rundown
issues on exit), I decide to build perl 5.8.7 on Fedora 4
from scratch. It builds and installs wo/ problems.
I test DBIx::Threaded w/ DBD::CSV and DBD::SQLite
wo/ any issues. When I start testing DBD::Teradata
(same version which is working very nicely on WinXP and OS X),
it dies very quietly just after connecting. Eventually,
I backtrack to a minimal DBI-only script to debug the issue.
Except there's nothing to debug. On return from
DBD::Teradata::dr::connect, it immediately dies. I trace(),
but there's nothing of note in the logs. I eval{} and
test $@, still nothing.
When I switch to DBI::PurePerl, everything works fine.
So I have to assume that whatever I'm breaking is somewhere in
DBI's connect() finalize code.
Has anyone built perl 5.8.7 on Core 4 and run DBI yet ?
Note that Core 4 uses gcc 4.01; are there any known issues
with that combo I should know about ?
Regards,
Dean Arnold
Presicient Corp.
Async rears its head again...
Since I'm going to need to implement some support for it in DBIx::Threaded soon, I thought I'd stir up the hornets nest (tho only briefly). After reviewing this thread: http://www.mail-archive.com/dbi-dev@perl.org/msg03408.html and skimming thru the more common DBDs on CPAN, can I conclude that only DBD::ODBC and DBD::Teradata provides any driver level support for async execution at this point ? (is odbc_async_exec() really async ? Looks more like an intermediate msg dispatcher to me...) When I implement support for *true* async capability in DBIx::Threaded (primarily so I can externally abort in-progress executions), I'd hope to make it usable by any other drivers that wish to implement async operation. And while I've got your attention, here's my current list of drivers that support multistatement requests with a more_results() type i/f: DBD::ODBC DBD::DB2 DBD::Sybase DBD::Teradata Are there others ? TIA, Dean Arnold Presicient Corp.
Re: Subclassing DBI and multiple inheritance
Tim Bunce wrote:
On Wed, Aug 03, 2005 at 05:29:28PM -0700, Dean Arnold wrote:
Since I've forgotten about this only a few dozen times now,
I'll offer this bit of text for future inclusion in the
DBI POD (where to put it, and the exact text, I'll leave
to the keepers):
BNote that DBI subclasses are not strictly inherited
subclasses in the classic OO sense. Your subclassed driver,
connection, and statement objects Bmust be derived
through the DBI at some point in order to acquire some
neccesary magic needed for any methods not explicitly
overridden in your subclass, or for any methods invoked on
the SUPER class. If you see errors like the following:
SV = PVMG(0x39ca0a4) at 0x1d1d8f0
REFCNT = 1
FLAGS = (ROK)
IV = 0
NV = 0
RV = 0x24b24b8
PV = 0x24b24b8
CUR = 0
LEN = 0
dbih_getcom handle MyDBI::db=HASH(0x24b24b8) is not a DBI handle
(has no magic) at
your object probably hasn't been derived through the appropriate
base class.
Could you make that a little more explicit? Including how to fix it.
Expressing it as a clarification/extension of the Subclassing the DBI
section of the docs would be good:
http://search.cpan.org/~timb/DBI/DBI.pm#Subclassing_the_DBI
Tim.
p.s. I'm not making any suggestions here to avoid distorting your thinking :)
Distort away!
As to a fix: it would probably help if there were some API provided
so I could conjure my objects such that the above error didn't
occur when base class methods were invoked - assuming the magic is not too
heavyweight. My particular problem is that DBIx::Threaded objects need
to be curse()'d and redeem()'d between threads, so they need to be
fairly lightweight. Plus, I never call a SUPER::connect or SUPER::prepare
when manufacturing objects. If I can just $self-SUPER::conjure()
at new() and redeem() time to recover the magic (this is starting to sound like
a scene from LOTR...), that would be great.
As to the docs: Not certain what to say. My example of classic OO
inheritance is
package BaseClass;
sub new {
return bless {}, shift;
}
sub over_ride {
my $self = shift;
return $self-do_something();
}
package SubClass;
use base qw(BaseClass);
sub new {
return bless (), shift;
}
sub over_ride {
my $self = shift;
$self-SUPER::over_ride();
return $self-do_something_else();
}
package main;
my $subclass = SubClass-new();
$subclass-over_ride();
__END__
Note that SubClass's constructor never has to explicitly
touch SUPER. For DBI subclasses, that is not currently the case,
since the objects need to acquire DBI magic. So when I
applied the usual subclassing for DBIx::Threaded, using
my own constructors for dr/db/st, I ran into the aforementioned
problems when invoking SUPER methods.
So I guess maybe the following exemplar would work:
WRONG SUBCLASS:
package MyDBI::dr;
use base qw(DBI::dr);
sub new {
#
# doesn't acquire DBI magic!
#
my $class = shift;
return bless { }, $class;
}
sub connect {
my $dr = shift;
return MyDBI::db-new(@_);
}
package MyDBI::db;
use base qw(DBI::dr);
sub new {
my ($class, $dsn, $user, $pass, $attrs) = @_;
#
# doesn't acquire DBI magic!
#
my $dbh = {};
bless $dbh, $class;
...do connect() stuff...
return $dbh;
}
RIGHT SUBCLASS:
insert existing POD example here
Subclassing DBI and multiple inheritance
(FYI: AS perl 5.8.3, DBI 1.48, WinXP)
Does DBI subclassing permit/play nice with multiple
inheritance ?
I'm running into an issue attempting to inherit the
set_err/err/errstr/state methods on DBIx::Threaded handles. I initially
attempted to just default to the base class, but that didn't work,
so I've tried overloading to explicitly call the base class methods:
DBIx::Threaded::db::errstr(C:/Perl/site/lib/DBIx/Threaded.pm:903):
903:return shift-DBI::db::errstr();
DB2 s
SV = PVMG(0x39ca0a4) at 0x1d1d8f0
REFCNT = 1
FLAGS = (ROK)
IV = 0
NV = 0
RV = 0x24b24b8
PV = 0x24b24b8
CUR = 0
LEN = 0
dbih_getcom handle DBIx::Threaded::db=HASH(0x24b24b8) is not a DBI handle
(has no magic) at C:/Perl/site/lib/DBIx/Threaded.pm line 903.
DBIx::Threaded::db::errstr('DBIx::Threaded::db=HASH(0x1d766bc)') called
at test.pl line 171
I vaguely recall having a similar issue with DBIx::Chart which was
eventually fixed by just using SUPER::err()/etc. However, that doesn't
seem to be working w/ DBIx::Threaded, and one big difference is
that DBIx::Chart only inherits from DBI). Here's the inheritance
for DBIx::Threaded (this is for the db handle):
use base qw(DBIx::Threaded::common Thread::Queue::Queueable DBI::db);
(I've also tried @ISA = ...)
Is there something else I need to do ?
Dean Arnold
Presicient Corp.
Re: Subclassing DBI and multiple inheritance
Dean Arnold wrote: (FYI: AS perl 5.8.3, DBI 1.48, WinXP) Does DBI subclassing permit/play nice with multiple inheritance ? Nevermind. I forgot that the subclassing root starts at DBI::dr w/ the connect(), not DBI...which creates its own set of issues for me, but hopefully will let DBI base methods behave properly. - Dean
Re: Subclassing DBI and multiple inheritance
Dean Arnold wrote: Dean Arnold wrote: (FYI: AS perl 5.8.3, DBI 1.48, WinXP) Does DBI subclassing permit/play nice with multiple inheritance ? Nevermind. I forgot that the subclassing root starts at DBI::dr w/ the connect(), not DBI...which creates its own set of issues for me, but hopefully will let DBI base methods behave properly. - Dean Since I've forgotten about this only a few dozen times now, I'll offer this bit of text for future inclusion in the DBI POD (where to put it, and the exact text, I'll leave to the keepers): BNote that DBI subclasses are not strictly inherited subclasses in the classic OO sense. Your subclassed driver, connection, and statement objects Bmust be derived through the DBI at some point in order to acquire some neccesary magic needed for any methods not explicitly overridden in your subclass, or for any methods invoked on the SUPER class. If you see errors like the following: SV = PVMG(0x39ca0a4) at 0x1d1d8f0 REFCNT = 1 FLAGS = (ROK) IV = 0 NV = 0 RV = 0x24b24b8 PV = 0x24b24b8 CUR = 0 LEN = 0 dbih_getcom handle MyDBI::db=HASH(0x24b24b8) is not a DBI handle (has no magic) at your object probably hasn't been derived through the appropriate base class. - Dean
Re: DBIx::Threaded feedback
David Nicol wrote:
After reading http://www.presicient.com/dbixthrd
I don't like all the explicit waiting, although I suppose it fits the explicit
nature of DBI better than something more abstract. It would be
possible to create a wrapper around DBIx::Threaded that would provide
self-promoting result objects (see asynchronous::universal::ready) instead
of necessitating all the checking. Also a way to stack up calls against
an unfinished call for background completion (see
asynchronous::universal::set_callback)
would be good in my book.
Note that the various wait()'s are only relevant for start()'ed calls;
the usual DBI suspects behave as before (ie, blocking).
As for self-promoting objects, I'm not certain how DBIx::Threaded
can enforce that; the things going into and coming out of DBIx::Threaded
are data elements, rather than objects (w/ the exception of connections
manufacturing
statement objects), ie, DBIx::Threaded is a resource,
the users of DBIx::Threaded are consumers. Any promotion they need
should be their own implementation...at which point they call
DBIx::Threaded-wait() or TQD-wait() or whatever.
As for callbacks, keep in mind that you can't pass CODErefs between
threads; the DBIx::Threaded apt. thread has no knowledge of the
appl. thread (tho I spose one could pass entire code nuggets as scalar strings
and eval on the server side...but I'm not inclined to do that).
So the only way to enforce that would be an extension
to Thread::Queue::Duplex to register a private callback association
w/ the enqueue'd $id...but that means someone somewhere has to call into
TQD to catch the cond_signal/cond_broadcast event...which kinda defeats
the purpose of registered callbacks. The only other solution is some sort of
MainLoop implicit event catcher/dispatcher, and I don't see DBI/DBIx::Threaded
as the proper place to provide that.
But it looks good.
Another thing is the fallback when invoked in a non-threaded perl. Have
you considered using the forks module instead of threads instead of falling
back to fully synchronous operation?
Not quite that simple. TQD would also need to be updated to use
sockets, and then there's the whole mess of translating
locks/cond_wait/cond_signal to signal events.
I guess my reaction is, if you don't have threads, don't use DBIx::Threaded.
If Perl's thread model is heavyweight, and DBIx::Threaded is a
middleweight solution, then using forks and sockets is neutron star class
heavyweight, and the application probably needs a new architecture.
concerning the doubts about last_insert_id(), I think a
last_insert_id() statement
in a callback block would have to use the same connection and would execute
immediately after a threaded insert, in the same thread ---
connections each have
their own threads, do they not? A callback queue could guarantee that an action
would happen immediately after the blocking event completed, thus solving the
stale last_insert_id problem.
So, I'd like to see something like
$id = $dbh-dbix_threaded_start($InsertStatement,{});
DBIx::Threaded-dbix_threaded_set_callback($id, sub{
$LastInsertedID = $dbh-last_inserted_id()
});
as a way to be sure that the code will run immediately after outstanding
request $id completes, in the same thread, with the same connection.
See my note wrt callbacks above. In order for that sequence to be safe, a whole
new set of locking sequences would be needed. Maybe TQD will provide async
notification in future, and/or grouped operations, but for now I'll leave it
to applications to sequence such things themselves.
- Dean
Re: DBIx::Threaded feedback
Tim Bunce wrote: On Tue, Jul 26, 2005 at 01:20:44PM -0700, Dean Arnold wrote: Dean, check the discussion on take_imp_data and DBI::Pool[1] in this list's archives. [1] http://stason.org/tmp/DBI-Pool-0.02.tar.gz You can't really use Storable to snatch the underlaying datastructs from a random DBD. Tim has started working on take_imp_data and DBD::mysql is the first one to have a support for it. But there are issues with it and I was waiting for Tim to fix those in DBI before moving on with DBI::Pool. The advantage of DBI::Pool is that it'll work transparently for the users (like Apache::DBI does), and require no special coding. Doesn't it require special coding in the DBDs ? (i.e., each driver has to implement take_imp_data() and possibly other stuff ?) Few will need to override take_imp_data(), but even for those that do it will only require a little house-keeping and then calling SUPER::take_imp_data(). The change that drivers will need to implement is to support being passed a dbi_imp_data attribute at connect time. But even that is pretty trivial: basically if given dbi_imp_data then skip the call to the underlying DB APIs connect because the imp_data already contains a valid connection. Could either you or Stas provide the executive summary (aka Passing Resources Between Perl Threads for Dummies) of how this works ? The tarball Stas pointed me at doesn't have any real POD (just a template README), and trying to glean the info from the mail archives is (at best) challenging. I'm having a serious bout of cognitive dissonance trying to reconcile what I know about - Perl's thread model (esp. wrt sharing data) - DBI - writing DBD's with what you're telling me wrt DBI::Pool. Does DBI::Pool require that the thread being passed the connection already have the DBI connection context as a result of being spawned out of the thread in which the connection was created ? Will it work for pure-Perl DBDs (seems like there'd be lots of driver-specific context copying required...) ? Will it permit the DBI-derived objects to be passed between threads (i.e., will it automagically marshal/unmarshal the objects into something that can be passed, e.g., on a queue) ? (General, Perl case, not Apache-specific) It 'only' enables a connection made in one thread to be 'loaned out' to another thread. Which is perfect for DBI::Pool. (In theory it could also enable a statement handle made in one thread to be 'loaned out' to another, but I'll wait till it's working for connections before exploring that further.) DBIx::Threaded derived objects are automagically marshalled/unmarshalled (assuming the app uses Thread::Queue::Duplex to pass things around), due to the apartment threading model used (apps just get thin veneer client objects, all the real work goes on inside the apartment thread) All that does add overhead of course. DBIx::Threaded will have higher method call cost to be traded against the greater functionality. Agreed. Hopefully, I'll have some empirical numbers by the weekend. However, the flexibility of DBIx::Threaded may actually provide some performance improvement at the macro level (for certain types of apps, anyway), at the expense of performance at the micro (ie, individual statement) level. - Dean
Re: DBIx::Threaded feedback
Tim Bunce wrote:
On Wed, Jul 27, 2005 at 10:21:10AM -0700, Dean Arnold wrote:
Will it work for
pure-Perl DBDs (seems like there'd be lots of driver-specific
context copying required...) ?
It can be made to work for pure-Perl DBDs.
Let's start with the docs for take_imp_data() (added in DBI 1.36):
---snip---
snip/
The key point is that $imp_data is a plain simple string.
It just happens to contain (typically) a chunk of binary data.
The only useful thing you can do with that data is pass it to
a connect call for the same driver:
DBI-connect(..., { dbi_imp_data = $imp_data })
Already read that, which is what kinda got me concerned
in the 1st place. In TQD terms, you're curse()ing the underlying
connection object into a frozen scalar, then
thawing and redeem()ing the scalar back to the underlying
connection object in the borrowing thread.
But there could be a *lot* of context stuff, and the
take_imp_data() comments from DBI.xs don't give me a warm
fuzzy feeling either.
I spose for XS-based DBD's, assuming they've been carefully
crafted to not stray from those rules, its not a big deal (few or no
SV/AV/etc's to manage to recover context).
I think Stas mentioned he'd only tested with DBD::mysql; have
any other DBD's been tested with this ?
Pure perl drivers are free to return any chunk of data, as a string, so
long as it can be used to recover the connection information. So for a
pure perl driver using a socket, $imp_data could be the integer file
descriptor of the socket. Note that it can't (shouldn't) be a reference
to a filehandle object since references can't easily be passed between
threads.
That definitely bothers me (having written a number of PP drivers).
Not impossible, but certainly an added burden for DBD authors, and
certainly an opportunity for things to go awry in very strange ways.
And statement handles could be even worse...
And only serves to convince me that apt. threading, tho a bit on the beefy side,
is a better solution for my needs.
Thanks for the clarification; I may not like the answer, but at least
I'm less inclined to be shaking my head and mumbling to myself trying
to figure out how it works (I had concluded you were employing some
extra magic under Perl's covers that I might exploit).
- Dean
Re: DBIx::Threaded feedback
Stas Bekman wrote: Dean Arnold wrote: I'd appreciate any reviews of my current DBIx::Threaded design as outlined at http://www.presicient.com/dbixthrd I've already implemented most of it, and have begun testing, but I'd like a bit more feedback before I rollout RC1. If you see something thats broken, or if something is missing, please let me know. Dean, check the discussion on take_imp_data and DBI::Pool[1] in this list's archives. [1] http://stason.org/tmp/DBI-Pool-0.02.tar.gz You can't really use Storable to snatch the underlaying datastructs from a random DBD. Tim has started working on take_imp_data and DBD::mysql is the first one to have a support for it. But there are issues with it and I was waiting for Tim to fix those in DBI before moving on with DBI::Pool. The advantage of DBI::Pool is that it'll work transparently for the users (like Apache::DBI does), and require no special coding. Doesn't it require special coding in the DBDs ? (i.e., each driver has to implement take_imp_data() and possibly other stuff ?) Will it permit the DBI-derived objects to be passed between threads (i.e., will it automagically marshal/unmarshal the objects into something that can be passed, e.g., on a queue) ? (General, Perl case, not Apache-specific) DBIx::Threaded requires *no* changes to any DBDs or the DBI (at least, it hasn't thus far). It also doesn't require any changes to apps, other than to use use DBIx::Threaded; $dbh = DBIx::Threaded-connect(...); ...and maybe observe some of the other limitations (most of which appear to be very minor corner cases). I spose if an app gets clever about ref $sth looking for DBI::st, and gets DBIx::Threaded::st instead, it might break...but thats a general subclass issue. DBIx::Threaded derived objects are automagically marshalled/unmarshalled (assuming the app uses Thread::Queue::Duplex to pass things around), due to the apartment threading model used (apps just get thin veneer client objects, all the real work goes on inside the apartment thread) I realize that DBI::Pool solves some problems, but it appears to rely on DBD authors to make some changes; unfortunately, I have an immediate need, and can't rely on thatunless I missed something ? OTOH, I'd certainly like DBIx::Threaded to play nice w/ Apache, but I'm not certain what/how/when to do about that. Dean Arnold Presicient Corp.
Re: bind_col() issues for DBIx::Threaded
Tim Bunce wrote: On Sat, Jul 23, 2005 at 10:13:05AM -0700, Dean Arnold wrote: The more I think about this, the more I'm convinced I should just POD a caveat that fetchall_XXX with bind_col() isn't supported (much like binding multiple variables to a single column). Why would anyone want to do that anyway? I very much doubt anyone depends on the interaction of bind_col() with fetchall_XXX(). I'm not even sure what that interaction is. I'd guess the bound variable is left with the value from the last row fetched. It's certainly not documented. I'd be happy to consider a patch that documents that it's undefined. Probably belongs in finish() as fetching beyond the last row is defined to implicitly/effectively call finish. Tim. Er, may have just found a purpose for bind_col() on fetchall(): supplying return type specification. Guess I'll support that aspect, but not the actual loading of the bind variables for fetchall(). - Dean
Re: DBIx::Threaded feedback
Dean Arnold wrote:
It also doesn't require any changes to apps, other than to use
use DBIx::Threaded;
$dbh = DBIx::Threaded-connect(...);
...and maybe observe some of the other limitations (most of
which appear to be very minor corner cases). I spose if an
app gets clever about ref $sth looking for DBI::st, and
gets DBIx::Threaded::st instead, it might break...but thats
a general subclass issue.
Forgot to mention that DBIx::Threaded also provides various
async interfaces (start(), wait(), etc.),
tho apps don't need to use them.
Plus, when used with other Thread::Queue::Duplex'd threads,
apps can have a generalized async capability (ie, wait on any
arbitrary TQD'd request to complete).
And finally, FWIW, if Mssr. Bunce decided to accomodate
DBIx::Threaded in DBI, its as simple as the DBI::PurePerl
solution, ie, just check that threads are available, that
DBIx::Threaded is available, and that an environment variable
is set:
if ($ENV{DBI_THREADED} $Config{useithreads}) {
eval {
require DBIx::Threaded;
}
return DBIx::Threaded-connect(@_)
unless $@;
}
But take_imp_data() does make me a bit curious...I'm wondering
if DBIx::Threaded could use it to return its small underlying
context (rather than requiring every driver to be updated), and
thus let any DBD (running inside an apt.
thread, of course) play nice w/ DBI::Pool ?
- Dean
Re: bind_col() issues for DBIx::Threaded
Tim Bunce wrote: On Sat, Jul 23, 2005 at 10:13:05AM -0700, Dean Arnold wrote: The more I think about this, the more I'm convinced I should just POD a caveat that fetchall_XXX with bind_col() isn't supported (much like binding multiple variables to a single column). Why would anyone want to do that anyway? I very much doubt anyone depends on the interaction of bind_col() with fetchall_XXX(). I'm not even sure what that interaction is. I'd guess the bound variable is left with the value from the last row fetched. It's certainly not documented. Exactly (at least per my read of the DBI and DBI::PP code). Assuming each fetch out of a DBD is getting loaded into the _fbav, the last row fetched would be in any bound variables. I'd be happy to consider a patch that documents that it's undefined. Probably belongs in finish() as fetching beyond the last row is defined to implicitly/effectively call finish. Tim. Don't know that undefined is the right term...based on my coderead, I think its defined behavior...maybe discouraged or deprecated is a better description. DBIx::Threaded will just document it as unsupported. - Dean
DBIx::Threaded feedback
I'd appreciate any reviews of my current DBIx::Threaded design as outlined at http://www.presicient.com/dbixthrd I've already implemented most of it, and have begun testing, but I'd like a bit more feedback before I rollout RC1. If you see something thats broken, or if something is missing, please let me know. TIA, Dean Arnold Presicient Corp.
Re: bind_col() issues for DBIx::Threaded
Tim Bunce wrote: On Fri, Jul 22, 2005 at 01:18:07PM -0700, Dean Arnold wrote: In the DBI 1.48 POD, it says... Multiple variables can be bound to a single column, but there's rarely any point. Is that true ? Yes. Does that mean that I have to explicitly $sth-bind_col($colnum, undef); before binding a new variable (assuming I don't want the old variable to get updated on the next fetch) ? If it does work that way, is that desirable behavior ? Actually there's no way to unbind a column, and no one has noticed :) Hmm, thats a bit of a pickle for DBIx::Threaded to emulate. At the moment I'm permitting multiple threads to use the same stmt handle concurrently (at least, in appearance). Which means one thread can bind one set of column variables, while another thread can bind another set. My assumption is that the first thread certainly won't want to see the results of a fetch() on the 2nd thread (and vice versa), so I'm attempting to isolate the bind variables. I think my current implementation will be OK, tho it may carry a bit more baggage than I expected (the real issue is the behavior of bound variables on a fetchall_XXX() call, otherwise a simple copy would work OK). Wish me luck... Dean Arnold Presicient Corp.
Seeking *non* thread-friendly DBD
Hoping someone can point me at a DBD thats known to be non-thread-friendly (ie, hasn't implemented the clone() methods). However, its underlying client libs and XS subs (if any) do need to be thread-capable (ie, no writing to process-global variables wo/ locking). I'm trying to test out a theory that DBIx::Threaded will make non-thread-friendly DBD's not only friendly, but thread-safe. TIA, Dean Arnold Presicient Corp.
Re: DBI v2 - The Plan and How You Can Help
Jonathan Leffler wrote:
I've dropped perl6-language off the addressee list - this is pretty much
internals of DBI or DBD::WhatNot and not Perl language per se.
On 7/12/05, Sam Vilain [EMAIL PROTECTED] wrote:
Dean Arnold wrote:
RE: LOBs and SQL Parse Trees: having recently implemented
LOB support for a JDBC driver (and soon for a DBD), I can assure
you that SQL parse trees are unneeded to support them. For databases
Great!
Perhaps you can shed some light on how to do it for this, then.
SQL command;
INSERT INTO FOO (?, ?, ?, ?);
Column 3 is a BYTEA column in Pg and needs special peppering to work.
What sort of peppering ? DBI provides SQL_BLOB, and SQL_CLOB
type descriptors (as well as SQL_BLOB_LOCATOR and SQL_CLOB_LOCATOR), so
presumably DBD::Pg (or any other DBD supporting LOBs) provides the
logic to map from
$sth-bind_param(3, $somelob, SQL_CLOB);
to whatever it needs to send on the wire. No different than, e.g.,
binding an integer or decimal(15,4). If some drivers don't support that,
thats a driver conformance issue, not a requirement for a new interface
mechanism.
or this;
SELECT
*
FROM
FOO
WHERE
SOME_DATE_COLUMN ?
SOME_DATE_COLUMN is the database native date type. On Oracle you'll
need to convert the ? to a 'TO_DATE(?)'.
Er, why ? I haven't used DBD::Oracle lately, but assuming you
$sth-bind_param(1, '2005-07-13', SQL_DATE),
I'd assume DBD::Oracle would be smart enough to communicate that
to Oracle (either by munging the query text, or providing type codes
in the client request structure). I certainly handle that sort of
thing in DBD::Teradata, and I suspect DBD::ODBC would as well.
DBD::Informix deals with both of these correctly in a variety of ways. The
DATE column is the easier - Informix Dynamic Server (IDS) is very good about
converting strings to DATE values - and to most other types. Also, since
Informix describes the types of the columns of the INSERT statement - and
can describe the input parameters of the SELECT statement (using DESCRIBE
INPUT) in the more recent versions of IDS - it can arrange the necessary
conversion.
The BYTEA example - corresponding to BYTE in IDS - is trickier. The string
you supply is converted into the relevant C structure - it happens to be a
loc_t in Informix ESQL/C - and then passed to the database. For INSERT, this
is easy because the types are described and the code in DBD::Informix can
tell that it needs to treat that properly. In other places, you have to use
the Informix type codes to convey the information to DBD::Informix. From
'perldoc DBD::Informix':
$upd = 'UPDATE SomeTable SET TextCol = ? WHERE Pkey = ?';
$sth = $dbh-prepare($upd);
$sth-bind_param(1, $blob_val, { ix_type = IX_TEXT });
$sth-bind_param(2, $pkey);
$sth-execute;
Internally, DBD::Informix knows that it must do the Perl string to Informix
loc_t mapping when this is specified.
Yes, it is a bit of work for the driver - but, for at least some drivers, it
is doable.
Have you considered updating DBD::Informix to use the SQL_BLOB/CLOB type codes ?
Regards,
Dean Arnold
Presicient Corp.
Re: DBI v2 - The Plan and How You Can Help
RE: Placeholders: since DBIv1 already supports both forms of PH's, I see no reason to deprecate or abandon either form. Furthermore, to my knowledge, none of (ODBC, JDBC, ADO.NET) has abandonded or deprecated the ? form, so I don't see the need for DBI to. RE: LOBs and SQL Parse Trees: having recently implemented LOB support for a JDBC driver (and soon for a DBD), I can assure you that SQL parse trees are unneeded to support them. For databases robust enough to support LOBs, they'll almost always provide sufficient metadata info and/or SQL syntax to manipulate them; only databases which don't support LOBs have that difficulty. Furthermore, a quick review of the current DBI will indicate that Mssr. Bunce has already implemented some stub methods for generalized support. RE: SQL Parse Trees (or other non-SQL query input) Since none of (ODBC, JDBC, ADO.NET) seems compelled to impose this concept on driver writers, I don't see why DBI should be the vanguard. However, implementing a subclass of DBI to support it seems technically feasible, so I'd suggest that those of you championing this requirement implement one on DBI v1. Feel free to use DBIx::Chart to bootstrap your project. As the proponents of this notion are so generous with their requirements for those of us who develop DBI drivers and/or contribute development efforts to the DBI itself, I'm sure they won't object if I provide a few requirements: 1. For DBI drivers which support them, your subclass must support - arbitrary numbers and levels of JOINs (including various outer, and non-equijoins) - arbitrarily nested subqueries (including correlated) - HAVING and GROUP BY clauses - ORDER and LIMIT clauses - updatable cursors - database-specific SQL extensions 2. It must function with current versions of 40% of DBD's created or updated on CPAN since Jan. 1, 2003. Said 40% must include - DBD::ODBC - DBD::Oracle - DBD::Pg - DBD::MySQL - DBD::CSV - one 'exotic' driver (e.g., DBD::iPod or DBD::Amazon, but excluding DBD::Google, whose syntax is too simplistic for a meaningful test) (FWIW: Past experience (e.g., execute_array()) leads me to believe Mssr. Bunce's requirements are likely much higher than 40%, so choose wisely, grasshopper) BTW: If you need a list of DBD's meeting said requirement, let me know, I just pulled one down. 3. It cannot require any changes to either DBI or the selected DBD's. 4. It must produce a database-independent conforming set of error codes (feel free to use SQLSTATE aka $h-state) 5. It must be uploaded to CPAN, and list, and demonstrably function against, the DBD's selected in requirement (2) above. Once you've implemented the subclass, you'll have empirical proof of the feasibility, and, more importantly, you'll be able to port the subclass to DBIv2, without any additional burden on DBI developers. Regards, Dean Arnold Presicient Corp.
Re: DBI v2 - The Plan and How You Can Help
BTW: If you need a list of DBD's meeting said requirement, let me know, I just pulled one down. Sure, send it over. [ ] DBD-ADO-2.94.tar.gz 31-Jan-2005 02:4041k GZIP compressed docume [ ] DBD-ASAny-1.13.tar.gz 31-Oct-2003 15:0030k GZIP compressed docume [ ] DBD-Amazon-0.10.tar.gz 23-May-2005 15:4158k GZIP compressed docume [ ] DBD-AnyData-0.08.tar.gz 19-Apr-2004 03:1620k GZIP compressed docume [ ] DBD-CSV-0.22.tar.gz 31-Mar-2005 18:0636k GZIP compressed docume [ ] DBD-Chart-0.81.tar.gz 26-Jan-2005 19:59 212k GZIP compressed docume [ ] DBD-DB2-0.78.tar.gz 19-Sep-2004 10:3475k GZIP compressed docume [ ] DBD-File-0.34.tar.gz21-Jun-2005 01:14 8k GZIP compressed docume [ ] DBD-Google-0.11.tar.gz 04-Mar-2004 18:5120k GZIP compressed docume [ ] DBD-Informix-2005.01.. 14-Mar-2005 19:01 267k GZIP compressed docume [ ] DBD-Ingres-0.51.tar.gz 12-Jan-2004 06:1846k GZIP compressed docume [ ] DBD-InterBase-0.43.t.. 25-Feb-2004 04:3078k GZIP compressed docume [ ] DBD-LDAP-0.06.tar.gz12-Mar-2004 21:4825k GZIP compressed docume [ ] DBD-Log-0.22.tar.gz 27-May-2005 06:5114k GZIP compressed docume [ ] DBD-MaxDB-7_5_00_26... 18-Apr-2005 08:3879k GZIP compressed docume [ ] DBD-Mimer-1.00.tar.gz 25-Nov-2003 15:5171k GZIP compressed docume [ ] DBD-Mock-0.27.tar.gz11-Jul-2005 11:3634k GZIP compressed docume [ ] DBD-Multiplex-1.96.t.. 25-Jan-2005 17:30 9k GZIP compressed docume [ ] DBD-ODBC-1.13.tar.gz08-Nov-2004 10:1595k GZIP compressed docume [ ] DBD-Oracle-1.16.tar.gz 22-Oct-2004 05:17 230k GZIP compressed docume [ ] DBD-Pg-1.43.tar.gz 23-Jun-2005 08:09 128k GZIP compressed docume [ ] DBD-PgPP-0.05.readme09-May-2004 08:06 3k [ ] DBD-PgPP-0.05.tar.gz13-May-2004 12:5616k GZIP compressed docume [ ] DBD-PgSPI-0.02.tar.gz 06-Dec-2004 00:3021k GZIP compressed docume [ ] DBD-Redbase-0.22.tar.gz 21-Oct-2003 22:5128k GZIP compressed docume [ ] DBD-SQLite-1.09.tar.gz 20-Jun-2005 11:42 464k GZIP compressed docume [ ] DBD-SQLite2-0.33.tar.gz 10-Sep-2004 11:50 355k GZIP compressed docume [ ] DBD-Sprite-0.56.tar.gz 12-Jun-2005 21:5286k GZIP compressed docume [ ] DBD-Sybase-1.05.tar.gz 19-Dec-2004 05:01 183k GZIP compressed docume [ ] DBD-TSM-0.04.readme 22-Mar-2005 16:05 2k [ ] DBD-TSM-0.04.tar.gz 23-Jun-2005 16:32 9k GZIP compressed docume [ ] DBD-Teradata-1.20.ta.. 17-Sep-2004 19:2736k GZIP compressed docume [ ] DBD-Trini-0.01.tar.gz 15-Jul-2003 03:1821k GZIP compressed docume [ ] DBD-Unify-0.31.tgz 16-Mar-2004 11:0731k GZIP compressed tar ar [ ] DBD-XBase-0.241.tar.gz 21-Nov-2003 09:25 109k GZIP compressed docume [ ] DBD-Yaswi-0.01.tar.gz 21-Feb-2005 19:46 4k GZIP compressed docume [ ] DBD-iPod-0.01.tar.gz06-Jan-2005 02:4113k GZIP compressed docume [ ] DBD-mysql-3.0002.tar.gz 11-Jul-2005 12:49 127k GZIP compressed docume [ ] DBD-mysql-AutoTypes-.. 02-Mar-2004 06:03 3k GZIP compressed docume [ ] DBD-mysql-SimpleMySQ.. 28-Apr-2004 16:39 4k GZIP compressed docume [ ] DBD-mysqlPP-0.04.tar.gz 24-Jan-2003 06:14 7k GZIP compressed docume - Dean
Re: DBI v2 - The Plan and How You Can Help [DRAFT]
David Nicol wrote: On 7/2/05, Dean Arnold [EMAIL PROTECTED] wrote: - Asynchronous queries (coroutines? threads?) Threads. If you've ever done much Java/JDBC work, you'll realize how much simpler a solution to async it is. (Ignoring the rest of Java/JDBC's undesirable traits) A couple quarters ago I submitted a proposal to write a general pragma delivering sugar to simply wrap any module with message-passing, including worker process/thread pooling and testing against DBI to the TPF, I guess the proposal has expired by now. Anyway this can be solved in a general way that is larger than DBI, and get it solved in DBI for free. I'm already implementing such a wrapper for DBI (DBIx::Threaded); not a pragma, and very specific to DBIv1, but hopefully it solves at least 85-90% of the problem. (tho async cancel/abort isn't solvable at this point) BTW: the Pots::* modules already do what I *think* you're proposing (again, not as pragmas, and Perl5 based) Dean
Re: DBI v2 - The Plan and How You Can Help [DRAFT]
Sam Tregar wrote: On Sat, 2 Jul 2005, Dean Arnold wrote: Er, marketting in what way ? I see the email as an attempt to attract new developers to the project. Hence it is, in some respects, an attempt to market DBI v2 to developers. My rant redacted. Relax. I'm just suggesting that Tim not talk about a fund drive that more-or-less failed when trying to drum up volunteers for the new project. It will be obvious to everyone who reads this that $500 isn't enough to help in a significant way. That may serve to subtract from the otherwise positive tone of his plea. -sam [ I'll take the role of bad cop here... I don't intend this as personal attack. Fortunately, Tim's on vacation, so he can dress me down after he gets back. ] These forums are intended to ask for, and provide assistance to, users and developers of the Perl DBI. One way (a very important way) to provide that assistance is for those who profit the most from the Perl DBI, to provide funding and/or other resources. If it's unseemly for Mssr. Bunce to ask for community assistance here (esp. when so many ask for assistance from *him*), then where should he ask ? If the issue is the perceived shaming, I can only posit that asking nicely obviously didn't work. This issue needs to be addressed. Those who profit from DBI need to be made aware that resources are needed to continue to provide a quality product, if only so the websites that provide their profits don't start succumbing to code rot. My rant is intended more as incitement to the community at large (the silent majority). If you work for an org that relies on DBI to keep the customers coming thru the doors (or URLs, as the case may be), you need to elevate this issue to your management and get some commitment from them to help out, if only in a Googlesque manner (i.e., letting some staff contribute 10-20% of their paid time to the effort). Its all well and good for us to give Tim a honey-do list for DBIv2. Whats needed is some serious commitment from developers, and esp. users who apparently feel entitled to the fruits of Tim's (and others) labors. After all, this isn't about maintaining yet another module that parses zipcodes. We're talking about the maintenance and development of a fundamental component of enterprise infrastructure. Now I'll take my lithium and go watch fireworks. Dean
Re: ParamTypes attribute ?
Tim wrote: Could you send me a doc patch based on my emails in that thread? Tim. First draft derived mainly from the ParamValues pod (see embedded [NOTES TO TIM]): =item CParamTypes (hash ref, read-only) Returns a reference to a hash containing the type information currently bound to placeholders. The keys of the hash are the 'names' of the placeholders: either integers starting at 1, or, for drivers that support named placeholders, the actual parameter name string. The hash values are hashrefs of type information in the same form as that provided to the various bind_param() methods (See L/Data Types for Placeholders for the format and values), plus anything else that was passed as the third argument to bind_param(). Note that type information that is not relevant to a given TYPE may be omitted, e.g., SCALE for SQL_CHAR. [ NOTE TO TIM: Your note in the roadmap shows both the scalar and hashref form of type info...in the interest of simplicity, I'd prefer a single form (hashrefs); do you have a strong opinion one way or the other ? ] Returns undef if not supported by the driver. [ NOTE TO TIM: Do you envision adding this info to the ShowErrorStatement output ?] See L/ShowErrorStatement for an example of how this is used. If the driver supports CParamTypes, but no values have been bound yet, then the driver should return a hash with the placeholder name keys, but all the values undef; however, some drivers may return a ref to an empty hash, or, alternately, may provide full type information (e.g., if the prepare()'d SQL statement provides placeholder type information). It is possible that the values in the hash returned by CParamTypes are not Iexactly the same as those passed to bind_param() or execute(). The driver may have modified the type information in some way based on the bound values, other hints provided by the prepare()'d SQL statement, or alternate type mappings required by the driver or target database system. It is also possible that the keys in the hash returned by CParamTypes are not exactly the same as those implied by the prepared statement. For example, DBD::Oracle translates 'C?' placeholders into 'C:pN' where N is a sequence number starting at 1. The CParamTypes attribute was added in DBI X.XX. Implementation is the responsibility of individual drivers; the DBI layer default implementation simply returns undef.
Re: ParamTypes attribute ?
Tim wrote:
[ NOTE TO TIM: Your note in the roadmap shows both the scalar and
hashref form of type info...in the interest of simplicity, I'd prefer
a single form (hashrefs); do you have a strong opinion one way
or the other ? ]
The goal is to keep bind_param cheap. But drivers can defer the cost
of converting the scalar to a hash ref till the ParamTypes attribute
is accessed.
Er, does that mean you prefer to support both scalar hashref,
or just hashref ?
My reasoning is from the POV of an application; if drivers
can return either scalars *or* hashrefs, then the app
has to be wired to handle both, vs. just handling hashrefs.
I realize its not a big chunk of code, but it is an opportunity
for confusion:
my $paramtypes = $sth-{ParamTypes};
print Type of $_ is .
(ref $paramtypes-{$_} ?
$paramtypes-{$_}{TYPE} :
$paramtypes-{$_})
foreach (keys %$paramtypes);
vs.
my $paramtypes = $sth-{ParamTypes};
print Type of $_ is $paramtypes-{$_}{TYPE}\n
foreach (keys %$paramtypes);
All seems fine. You can send the patch direct to me and I'll apply it.
Or, if you give me your perl.org userid I'll give you commit rights[*]
Tim.
[*] I've decided to be more liberal with commit access than I have been
in the past. I'll give commit access now to anyone with a track record
of sane patches.
I'll just let you patch it for now.
- Dean
Re: ParamTypes attribute ?
Tim Bunce wrote: Okay. You send, I'll apply. Thanks Dean! Tim. Here 'tis. (diff'd against DBI 1.48) - Dean --- DBI.pm Thu May 12 12:40:53 2005 +++ ../DBI-1.48/DBI.pm Mon Mar 14 07:45:38 2005 @@ -6055,44 +6055,6 @@ The CParamValues attribute was added in DBI 1.28. -=item CParamTypes (hash ref, read-only) - -Returns a reference to a hash containing the type information -currently bound to placeholders. The keys of the hash are the -'names' of the placeholders: either integers starting at 1, or, -for drivers that support named placeholders, the actual parameter -name string. The hash values are hashrefs of type information in -the same form as that provided to the various bind_param() methods -(See L/Data Types for Placeholders for the format and values), -plus anything else that was passed as the third argument to bind_param(). -Note that type information that is not relevant to a given -TYPE may be omitted, e.g., SCALE for SQL_CHAR. -Returns undef if not supported by the driver. - -If the driver supports CParamTypes, but no values have been bound -yet, then the driver should return a hash with the placeholder name -keys, but all the values undef; however, some drivers may return -a ref to an empty hash, or, alternately, may provide full type -information (e.g., if the prepare()'d SQL statement provides placeholder -type information). - -It is possible that the values in the hash returned by CParamTypes -are not Iexactly the same as those passed to bind_param() or execute(). -The driver may have modified the type information in some way based -on the bound values, other hints provided by the prepare()'d -SQL statement, or alternate type mappings required by the driver or target -database system. - -It is also possible that the keys in the hash returned by CParamTypes -are not exactly the same as those implied by the prepared statement. -For example, DBD::Oracle translates 'C?' placeholders into 'C:pN' -where N is a sequence number starting at 1. - -The CParamTypes attribute was added in DBI X.XX. Implementation -is the responsibility of individual drivers; the DBI layer default -implementation simply returns undef. - - =item CStatement (string, read-only) Returns the statement string passed to the L/prepare method.
ParamTypes attribute ?
I may have missed this in the archives, but google
didn't shed any light, so here goes...
DBI currently defines a readonly ParamValues $sth attribute;
is there a need for an equivalent ParamTypes attribute ?
I'm currently dealing with an app that needs to retrieve
parameter type info, and will be adding a driver-specific
attribute for it, but thought it might be something that DBI
in general might need.
FWIW: my implementation will return a hashref of
parameter type info keyed by parameter name (or number),
with values consisting of hashrefs of
{ TYPE, PRECISION, SCALE, IN_OR_OUT }
ala the returned fields type info, plus an IN/OUT/INOUT indicator
for stored procedure calls (string based: 'IN', 'OUT', 'INOUT').
If no type info is available for a
parameter, its TYPE will be SQL_UNKNOWN_TYPE; if a piece of the
type info is missing or not relevant, (e.g., VARCHAR wo/ a
defined PRECISION, or the PRECISION/SCALE for an INTEGER),
then that piece of info is omitted.
Thoughts ? Did I overlook an existing attribute or i/f ?
Regards,
Dean Arnold
Presicient Corp.
Re: ParamTypes attribute ?
Tim wrote: On Wed, May 11, 2005 at 09:36:26AM -0700, Dean Arnold wrote: I may have missed this in the archives, but google didn't shed any light, so here goes... DBI currently defines a readonly ParamValues $sth attribute; is there a need for an equivalent ParamTypes attribute ? Yeap: http://groups-beta.google.com/groups?q=ParamTypes%20bunce Tim. Thought I saw this somewhere before (my googlewhacking has been terrible lately). Details: Is this something that drivers are expected to implement, or will DBI pick up the info from the bindXXX() calls ? If the latter, will it be overridable ? My target DBMS has the ability to explicitly specify param type info in the SQL, so in some cases, the info would be available after prepare() but before anything was bound. Thnx, Dean
Re: DBI/DBD Wikis
Jeff Zucker wrote: Jochen Wiedmann wrote: on the DBD::mysql mailing list came up the suggestion for installing a Wiki somewhere. I like the idea. We should think carefully about what already exists and clearly differentiate what we are doing from the other efforts. The similar projects that I know about which already exist are PerlMonks, cpan.forum, and rt.cpan.org. Where is cpan.forum located ? I can't find any links at cpan.org... Perlmonks (as I assume most of you know) is a longstanding Perl online community with a huge amount of DBI-related information (and some DBI-related misinformation :-)). Personally, I (aka jZed on PerlMonks) find PerlMonks to be a real community - although there is some flaming, trolling, insulting, it is very much minimized compared to say the average usenet forum. The questions and answers on PerlMonks run the gamut from complete beginners to people like Perrin and Michael Peppler who provide real depth. Cpan.forum is fairly new and I haven't used it much but it appears to have the infrastructure to deal with many modules. Rt.cpan is not quite the same thing, but I mention it because we, as authors, need to already keep our eyes on rt.cpan, dbi-users, PerlMonks and the idea of yet another place is somewhat daunting. I think the differentiator is the Wiki-ized content, which tends to be more structured, and would hopefully become a Wikipedia for the DBI (and DBD's and DBIx's). PerlMonks content tends to be scattered; archives of this maillist are difficult to search, and pretty unstructured (Googled or otherwise); rt.cpan is for bug reports, and I've never seen cpan.forum. BTW: should dbi-users be included in this discussion ? Dean Arnold Presicient Corp.
Re: [dbi] Re: Savepoint support proposal
Dan Scott wrote: On Wed, 9 Mar 2005 13:29:40 -0800, Darren Duncan [EMAIL PROTECTED] wrote: At 8:55 AM + 3/9/05, Martin J. Evans wrote: Just a small point. I could be wrong (I cannot look it up right now) but I think in ODBC SQLEndTran can issue a rollback or commit on ALL connections. Effectively, it issues the rollback/commit on each connection in the environment. That would be a lot harder to do with $dbh-do since you'd have to do it once per connection. If you wanted a global action in DBI, then reasonable syntax could be to issue the rollback/commit on the DBI class name rather than on a connection handle object. Either that, or restructure DBI so that the first thing a user does is create an environment handle object, off of which they call connect(), which incidentally saves us having to use any package globals. -- Darren Duncan Can someone provide a scenario where you would want to issue a global rollback or commit? Perhaps (probably) I'm suffering from a limited imagination here, but it seems like you would want to rollback or commit on a connection by connection basis. Dan 2PC. But DBI doesn't support that (yet). However, some of these arguments are starting to bring me around to the original notion. If DBI ever gets a full 2PC capability, such capability may be desirable (or at least, the creation of commit groups, as an earlier poster intimated.) Dean Arnold Presicient Corp.
Re: Savepoint support proposal
Greg Sabino Mullane wrote: We are working on implementing savepoint support in DBD::Pg, and someone pointed out (quite rightly) that perhaps there should be DBI method support for savepoints, as they are a standard SQL construct used by more than just PostgreSQL. So I would like to propose that DBI implement a savepoint, release, and rollbackto method, similar to the existing begin, commit, and rollback methods for database handles. In short, these would be simple wrappers, with the actual implementation left to the DBDs. I can whip up a prototype and more detailed docs, but wanted to see if there was any input or objections before I did. Thanks, - -- Greg Sabino Mullane [EMAIL PROTECTED] While I don't have any particular objections, doesn't Pg (and most other DBMSs supporting savepoints) have SQL syntax to implement them ? If so, isn't just $dbh-do(savepoint-sql) sufficient ? my 2 cents, Dean Arnold Presicient Corp.
Re: what does it take to make DBI:: DBD::* thread-safe?
Stas Bekman wrote: Hi, As mod_perl 2 is going to be released shortly the issue with DBI and friends not being thread-safe (I believe DBI is fine, but DBDs are not) becomes a problem. Since Apache 2 has a bunch of threaded MPM implementations and while Unix users could always use prefork MPM (no threads) windows users can't afford this luxary, they need the modules to be thread-safe in order to use those. So how hard will it be to bring the DBI world to the thread-safety? What are the issues that prevent us from getting there (besides the ever lacking tuits). Thanks. Probably the biggest issue is the thread safety of the underlying DBMS client API (for non-pure perl implementations), and the XS wrapper. For those of us with Pure Perl drivers, its a bit simpler: implement the CLONE method in the driver, and make sure any global variables that might exist are either shared or converted to instance (rather than package/class) level variables. For real databases, MySQL and Pg both have pure perl implementations, tho I'm not certain how current they are, nor whether they've been tested for thread safety at this point. plug type='shameless' DBD::Teradata (pure perl, commercial version) has been thread safe for about a year now. (Last tested with Perl 5.8.5 on Fedora Core 2) /plug FWIW: Threaded perl performance seems to have improved in Perl 5.8.5; in 5.8.3/4, it seemed mighty slow, both on Windows and Linux. I haven't had a chance to test 5.8.6 yet. However, Since $dbh's can't be shared between threads, I wonder how Apache connection pools will behave in such an environment (if at all) ? Dean Arnold Presicient Corp.
Re: DBI 2.0 interface change request
John Siracusa wrote: On Tue, 03 Aug 2004 09:35:25 +0200, Jochen Wiedmann That gains absolutely *no* functionality It's a heck of a lot easier to subclass... for the burden of loosing compatibility. There's no reason the old interface couldn't continue to exist. OK, I'll bite Hashrefs are declarativeSQL is declarative... Methods are procedural. I can imagine (even postulate ..see recent mad rantings RE embedded SQL) a fully declarative perl-to-database I/F. I realize many perl users seem hell bent on turning the language into Java-yuk-foo. Please don't. If you're inclined for the same old I'll tell you what I want and how I want it and how to get it i/f, there are plenty of (IMHO misguided) DBIx's to help you get over your jones. rant Why do so many of you hate SQL so much ? I've seen some amazingly intractable perl code (worse still C/C++/Java code) that purports to simplify databaase access. Unless you're dealing with the parts explosion problem, SQL does a pretty good job, and solves most problems, if folks would take a moment to consider the declarative solution. /rant As for me, give me a tie, and let declaration guide me (I promise, this is not a pseudonym for Fabian Pascal), BTW: This probably belongs on dbi-users at this point.. A MAd DEclAratiVe, Dean
Re: RFC: SQL::Preprocessor - a SQL filter for Perl
Understood. Sorry. - Dean
RFC: SQL::Preprocessor - a SQL filter for Perl
caveat
while not strictly a DBI issue, I'm going to
kick this off here, since its pretty relevant
(ala SQL::Statement).
If I'm out of line, or should restrict this
to one or the other lists, please advise
/caveat
A couple years ago, someone posted an RFC and some
questions about building an Inline::SQL module. Alas,
while I was pretty enthused about it, it never
seemed to get off the ground.
Of late, that particular scratch has needed some
serious itchin' on my part, esp. since I've
been playing with Inline::C, and some Acme
modules. So, I've sketched out some POD
to get the ball rolling. I'm looking for some feedback,
esp related to
- embedded SQL syntax for various databases
- possible feature requests/needs for optimal
Perl integration
- alternate namespace suggestions
- possible problems with the syntax I've sketched
out in the POD (its certainly possible that
pluggable database-specific syntax parsing
may need to be accomodated, like DBD::AnyData)
While I won't be able to do serious development for
some time, its the sort of thing I can work on to clear
my head when other projects fog my brain.
And of course, anyone wanting to pitch is welcome!
=head1 NAME
SQL::Preprocessor - Embed SQL in your Perl (ala SQL preprocessors)
SQL::Preprocessor::Chart - use DBIx::Chart instead of DBI
=head1 SYNOPSIS
use SQL::Preprocessor;
use DBI;
use DBI qw(:sql_types);
...some code...
EXEC SQL DECLARE SQLDA $sqlda;
EXEC SQL CONNECT TO 'mysql:database=$database;host=$hostname;port=$port'
AS userid IDENTIFIED BY $password AS myconn;
EXEC SQL DECLARE CURSOR mycursor AS
SELECT * FROM mytable FOR UPDATE;
EXEC SQL OPEN mycursor;
while ($SQLSTATE eq '0') {
EXEC SQL FETCH mycursor INTO $col1, $col2, $col3, $col4;
if ($col1 $col4) {
EXEC SQL UPDATE mytable SET col4 = col4 + 100
WHERE CURRENT OF mycursor;
}
}
EXEC SQL CLOSE mycursor;
#
# and if you used SQL::Preprocessor::Chart:
#
EXEC SQL
SELECT * FROM mytable
RETURNING LINEGRAPH(*), IMAGEMAP
WHERE WIDTH=500
AND HEIGHT=500
AND FORMAT='PNG'
AND LOGO='myimage.png'
AND X_AXIS='Date'
AND Y_AXIS='Stock Price'
AND MAPNAME='stockmap'
AND SIGNATURE='GOWI Systems, Inc.'
AND SHOWPOINTS=1
AND POINT='opencircle';
open(OUTF, 'mychart.png') || die $!;
binmode OUTF;
print OUTF, $_-[0];
close OUTF;
open(OUTF, 'mychart.map') || die $!;
print OUTF, $_-[1];
close OUTF;
EXEC SQL DISCONNECT myconn;
=head1 PREREQUISITE MODULES
=over 4
=item
DBI 1.43
=item
Filter::Simple
=item
DBIx::Chart (only for SQL::Preprocessor::Chart)
=item
DBI Driver of the target database system
=back
=head1 DESCRIPTION
A SQL preprocessor for Perl. Via Filter::Simple, EXEC SQL
statements are converted into DBI calls to perform the specified
SQL operation.
=head2 Background
For the unfamiliar, embedded SQL first appeared shortly after
relational databases, primarily for use in COBOL programs.
In recent years, it is most frequently used with C or C++,
and even more recently, with Java, via SQLJ. And, now, with Perl!
=head2 Behaviors
A few usage notes...
=head3 Supported SQL Syntax
Pretty much whatever DBI can consume, plus the following
extensions:
=over 4
=item BEGIN DECLARE SECTION
Begins a section of declarations to be used as either
placeholder values, or as values to receive results.
=item CLOSE cursorname
Closes the specified cursor.
=item CONNECT TO dsn [ USER user [ IDENTIFIED BY password ] [ WITH attributes ] [ AS
name ]
Connects to the specified DSN as user, password, with an optional
attributes hash, and optionally assign the given name.
DSN's are the usual DBI suspects, although the 'dbi:'
prefix is optional.
=item DECLARE CURSOR name AS select-statement [ FOR UPDATE ]
Declares a cursor, possibly updatable.
=item DECLARE SQLDA
Specifies a variable to be used to receive DESCRIBE information
for static executions.
=item DESCRIBE statement-name INTO hash
Deposits the DESCRIBE information for the named PREPARE'd
statement.
=item DISCONNECT [ name ]
Disconnects either the current connection, or the specified
connection.
=item END DECLARE SECTION
Terminates the DECLARE SECTION started by BEGIN DECLARE SECTION
=item EXECUTE name [ USING variable-list ] | [ USING DESCRIPTOR sqlda ]
Executes the PREPARE'd statement, optionally supplying a variable-list
or a SQLDA for input placeholder data
=item EXECUTE IMMEDIATE statement
Immediately executes the specified statement, which is supplied as
either a string expression
=item FETCH
Re: RFC: SQL::Preprocessor - a SQL filter for Perl
Matthew O. Persico wrote:
My 0.02USD is this implementation detail: you have to tell Filter::Simple not to
replace the embedded SQL but to comment it out and add the filtered code or else
debugging will be a royal PITA.
Yep. And presumably, sprinkle with #line pragmas so line numbers of reported
errors make sense.
To be honest, given a native API, I have never understood the desire for embedded SQL. But TIMTOWTDI, so good luck.
Yes no. Consider a quick little script to pull some data. Yes, DBI is reasonably
compact, but maybe
use SQL::Preprocessor;
EXEC SQL CONNECT TO 'mydsn' USER userid IDENTIFIED BY password;
EXEC SQL SELECT * FROM mytable;
foreach my $row (@$_) {
print join(', ', @$row), \n;
}
EXEC SQL DISCONNECT;
__END__
...is a bit more convenient/familiar to us aging SQL hackers.
And maybe for SQL hackers who don't know the DBI yet
(if there are any ;^)
And think of it...by running perl interactively, it may even possible
to use perl instead of sqlplus/isql/etc. (Not too sure if Filter
would handle that, tho)
--
Matthew O. Persico
Thanks,
Dean
Re: Minimum dependencies for DBI v2
Darren Duncan wrote: At 12:33 AM +0200 7/9/04, H.Merijn Brand wrote: You won't be leaving them behind. They still have DBI-1.xx FWIW as per customer request, I am depending on 5.8.4 and up since two weeks. You can imagine that's a little bit earlier than I planned. And it's not UTF-8 that makes me need 5.8.4 That's largely what I was getting at. Besides Unicode, 5.8.x is a lot more thoroughly tested and bug free, plus thread support is a lot more mature, plus there's PerlIO to work with, plus various other advantages. And, there is a different and/or newer set of modules included with it. (Forgive my ignorance, but starting with which version of Perl was Test::More bundled?) The fact is, if not now, then before too long, 5.6.x will be the new 5.005 as far as the general developer community is concerned. Alas, for much of the installing public, that won't be the case. In my case, I've bitten the bullet and started a fork for my driver: one that supports 5.6.x+, and another that requires 5.8.3+. Its a pain, but getting some users to even consider moving to 5.6.x has proven difficult, esp. in enterprise class environments, where the Prime Directive is If it ain't broke, don't fix it. Yes, I'm aware 5.005 might well be considered broken in places, but convincing a CIO of that can be a challenge if your website has been happliy dishing up webpages on 5.005 for several years. While I only provide essential updates for the 5.6.x fork, there may be sites that want DBI v2 features (whatever that ends up being), but won't be willing to update Perl versions, at least not to 5.8+ in the near future. Hence, the test suite might be very valuable for pre-5.8 sites. On the other hand, retrofitting DBI v2 features might be a profit center for us DBD authors ;^), and a better impetus to the laggard users. Perhaps as a middle ground, I can suggest that DBI v2 will support Perl 5.6 initially, but that support will be considered deprecated. This way, 5.6 people can move to DBI v2 to get some of the new features, but with the fore-knowledge that they should upgrade to 5.8 before too long afterwards. That would be a gentle forced migration if you will. If the core Perl developers can't crowbar users off 5.005, I doubt us poor DBI developers have much chance of getting them to move from 5.6 to 5.8, much less off 5.005. Perhaps we're victims of our own success 8^/ And they still have the older DBI versions to use in perpetuity, regardless. One last thing: Have any surveys been done concerning what for production versions of Perl are being used by what fraction of users, both in general and those that also use DBI? For example, how many have migrated to 5.8 already? Solely based on the sites I support, I'd say few, at least for production use. How many others are on 5.6? Many more than 5.8, but not as many as you might think (or prefer). And how many others are on older versions of 5? You'd probably be surprised/disheartened by the number still running 5.005. I recently installed an enterprise class application that still bundles ActiveState's 5.004 perl as part of their install procedures. Or, heavens forbid, 4.x or older? Probably not too much of a concern; IIRC, Perl 5's release pretty much coincided with the internet boom, so most people hadn't even heard of Perl before 5.0 was released. Where can I lookup such info, *other* than on Google? Maybe perl.org or perlmonks ? Point of reference: consider how many sites still run Oracle 7, or Win NT4 ? A LOT! And Oracle and MSFT have armies of people paid to push sites off those versions. -- Darren Duncan I'd never argue against migrating sites to perl 5.8 (in fact, I actively proselytize for that move), but I think we need to be pragmatic and cautious about forcing sites to migrate. DBI has become a mature tech (thanks to Tim and the collective). We can choose to dictate upgrades to the user community, or take the kinder, gentler approach, albeit at greater effort on our part. In the interest of greater adoption, I'd suggest the latter. IMO, I'd vote for 5.6 as an acceptable baseline for the test suite. I won't suggest a baseline for DBI v2, since its features and timeframe aren't yet well defined, and may require DBD authors to fork anyway. (Well, maybe not require, since many DBD authors are working gratis, so users will get only what the authors choose to provide) Discretely drags soapbox offstage Dean Arnold Presicient Corp.
Re: Bulk insert/load API access from DBI
Michael Peppler wrote: I've been asked to investigate the feasibility of adding access to Sybase's bulk-load API in DBD::Sybase. This is an API that allows you to load table rows in a minimally logged manner, and is of course much faster than normal INSERT statements. snip I've built bulk load/unload facilities into DBD::Teradata pretty much from the outset. Thus far, I use mainly driver-specific APIs (just to make it easier for end-users, if interested see www.presicient.com/tdatdbd/tdatdbdfl.html). In Teradata's case, special connections are required for bulk load/unload, so there are a few connect() driver-sepcific attributes required. In addition, Teradata load utilities are intended to handle multiple terabyte loads, so there are some extra facilities available to the app (checkpointing, logging, etc.). However, it is possible for app writers to use the DBI directly to do the load, so long as they know the right way to connect(), the right SQL to use, and the proper sequence to execute. Not certain how useful this is to your case, but figured I'd pitch my $0.02 in if we're talking about a standard DBI interface for such things. For what its worth, I'd love to see broader DBD support for such facilities (which is why I kept pounding the table for array binding), as perl makes a *great* ECTL tool. Regards, Dean Arnold Presicient Corp. www.presicient.com
Re: Any interest in a $sth-{TYPE_STRING} ?
Umm, OK, I think I figured part of the answer...for PP DBI, FETCH() is in DBI::PurePerl, but for mainstream DBI, its in XS code, yes ? That latter throws in some wrinkles... Ignore that for now. Just write it as a perl sub and we can look at integrating it if/when we're all happy with what it does. Also, can anyone point me to a copy of ISO/ANSI SQL std. that describes the type characteristics of some of the newer/more complicated types, esp. REF, ARRAY, MULTISET, UDT, LOCATOR, ROW, and the INTERVAL items as well (I know how my DBMS flavor handles INTERVAL declarations, but am not certain what the standard is). Specifically, I'm looking for which types have precision and scale, and where those items would be applied eg, DECIMAL(precision, scale) vs. INTERVAL HOUR(precision) TO SECOND(scale) (Is that how other drivers use PRECISION and SCALE with INTERVAL types ?) I was wondering how soon you'd start facing the more tricky cases :) Tim. I found an ISO SQL spec (for SQL-99 anyway... has DBI declared any conformance level at this point ?) And my interpretations of type string formats seem pretty conformant. However, the exotics, e.g. ROW, GUID, UDT, etc. will likely have only generic defaults for obvious reasons (questions? just read the spec), but INTERVAL defaults will behave pretty much as I described. Quick poll: how many drivers supply a PRECISION value for INTEGER, REAL, FLOAT, DOUBLE PRECISION ? The std. indicates they can have precision... Dean Arnold Presicient Corp. www.presicient.com
Re: Any interest in a $sth-{TYPE_STRING} ?
- Original Message -
From: Tim Bunce [EMAIL PROTECTED]
To: Dean Arnold [EMAIL PROTECTED]
Cc: Tim Bunce [EMAIL PROTECTED]; DBI developers [EMAIL PROTECTED]
Sent: Thursday, April 29, 2004 1:49 AM
Subject: Re: Any interest in a $sth-{TYPE_STRING} ?
On Wed, Apr 28, 2004 at 11:17:30PM -0700, Dean Arnold wrote:
I found an ISO SQL spec (for SQL-99 anyway...
has DBI declared any conformance level at this point ?)
No. Though basically it tried to track the latest standards.
And my interpretations of type string formats seem pretty
conformant. However, the exotics, e.g. ROW, GUID, UDT,
etc. will likely have only generic defaults for obvious reasons
(questions? just read the spec), but INTERVAL defaults
will behave pretty much as I described.
Quick poll: how many drivers supply a PRECISION value for
INTEGER, REAL, FLOAT, DOUBLE PRECISION ? The std.
indicates they can have precision...
How many drivers provide _accurate_ PRECISION for all those types?
And how many databases accept having a PRECISION specified for those types?
And for FLOAT types is PRECISION in the same units as the FLOAT(n)
parameter? (For float types some databases specify PRECISION in
decimal digits and some in binary digits, and some change between
versions :)
Originally you said either for display purposes, or to generate
additional queries. Display purposes is fine, you can do anything
'reasonable' but generate additional queries is a far more demanding
requirement. It's important to specify *exactly* what the goal is.
And what about modifiers like UNSIGNED, or even AUTO_UNIQUE_VALUE.
Tim.
Actually I would hope that *both* display and query generation would be
possible; in fact, the former should be satisfied by the latter.
My question/efforts are related to default implementation to support
drivers which don't implement a driver specific version.
So should a default implemenation even be provided if it can't
safely meet the 2nd requirement (query generation) ? I'm not certain
DBI specifies enough metadata to completely generate defaults
that can be safely used in query generation (eg, CASESPECIFIC,
CHARACTER SET, in addition to those listed by Tim)
And the exotic types (ROW, UDT, etc) certainly couldn't be fully generated
from the existing metadata.
Dean Arnold
Presicient Corp.
www.presicient.com
Re: Any interest in a $sth-{TYPE_STRING} ?
I'd consider a patch, with documentation and tests. Tim. I started coding such a patch, then started looking around in DBI as to where to inject it, and can't seem to find a nice/clean place to put it. AFAIK, the only place DBI has a chance to 'meddle' with the driver's prepare() is in the _new_sth() call, but the driver may not have all its TYPE/NAME/etc. attributes available at that point. So either (a) I have to force every prepare() to go thru the DBI layer to call the driver prepare so I can validate TYPE_STRING exists when the driver prepare() is done, or (b) I have to create a tie for it somehow ? Or is it safe to assume that drivers are well behaved enough to call SUPER::FETCH() inside DBD::xxx::st::FETCH() if the requested attribute isn't recognized ? And, BTW, where the heck is the FETCH code for the DBD::_::st ? If anyone can provide some pointers on how to patch into that bit of code, I'd be much obliged... Umm, OK, I think I figured part of the answer...for PP DBI, FETCH() is in DBI::PurePerl, but for mainstream DBI, its in XS code, yes ? That latter throws in some wrinkles... Also, can anyone point me to a copy of ISO/ANSI SQL std. that describes the type characteristics of some of the newer/more complicated types, esp. REF, ARRAY, MULTISET, UDT, LOCATOR, ROW, and the INTERVAL items as well (I know how my DBMS flavor handles INTERVAL declarations, but am not certain what the standard is). Specifically, I'm looking for which types have precision and scale, and where those items would be applied eg, DECIMAL(precision, scale) vs. INTERVAL HOUR(precision) TO SECOND(scale) (Is that how other drivers use PRECISION and SCALE with INTERVAL types ?) TIA, Dean Arnold Presicient Corp. www.presicient.com
Re: Any interest in a $sth-{TYPE_STRING} ?
On Sat 24 Apr 2004 21:38, Dean Arnold [EMAIL PROTECTED] wrote:
I'm finding I frequently need to convert the DBI type metadata
from a result set into a string representation (either for display purposes,
or to generate additional queries).
Is there a need/desire to add a new $sth metadata item
TYPE_STRING that would contain that info, eg,
$sth-{TYPE}-[0] == SQL_INTEGER = $sth-{TYPE_STRING}-[0] = 'INTEGER'
$sth-{TYPE}-[0] == SQL_DECIMAL,
$sth-{PRECISION}-[0] == 10,
$sth-{SCALE}-[0] == 2= $sth-{TYPE_STRING}-[0] =
'DECIMAL(10,2)'
etc.
This appears to be one of those things that could have a default
implemention at the DBI layer, but overridden by the driver. This is a bit
more convenient than reconstructing things from type_info(), esp. since
precision/scale would already be supplied.
Since I've coded this translation about a dozen times
now, I could probably whip up a patch to DBI for the default.
(Probably based on type_info(), if driver provides it, else just
a best guess)
Opinions ? Have I overlooked something like this thats already in the DBI ?
1. I like it, and I would use it
2. If used to (re)create tables, it would be nice to be able to customize the
words used in advance, so no changes would be needed afterwords
DECIMAL = NUMBER = NUMERIC = INT2 = BLARGHY = ...
CHAR = VARCHAR = STRING = HUMBERDREW_4 = ...
DOUBLE = FLOAT = NUMBER = FLURBLE32 = ...
DATE = TIME = DATETIME = TIMESTAMP = FIRAGO =
I've seen too many to be able to stop fantasize about what database designers
come up with in the future. If I could pass such a table on connect, or assign
it to a dbh just after, I could use centralized tables. The keys to such table
could be the current set of SQL_... types, but with fallback enablement for
generic types (double/float = numeric) optional
--
H.Merijn BrandAmsterdam Perl Mongers (http://amsterdam.pm.org/)
using perl-5.6.1, 5.8.0 633 on HP-UX 10.20 11.00, AIX 4.2, AIX 4.3,
WinNT 4, Win2K pro WinCE 2.11 often with Tk800.024 /| DBD-Unify
ftp://ftp.funet.fi/pub/languages/perl/CPAN/authors/id/H/HM/HMBRAND/
I'm not entirely certain I understand your request ? My thoughts were as follows:
1) Drivers should provide their own TYPE_STRING values, presumably in their
native platforms' format
2) For any driver that doesn't provide TYPE_STRING, but *does* provide type_info_all(),
DBI layer would generate TYPE_STRING from the information from type_info_all(),
{TYPE}, {PRECISION}, {SCALE}
3) For drivers that provide neither TYPE_STRING nor type_info(), DBI generates
TYPE_STRING from 'standard' type names and {TYPE}/{PRECISION}/{SCALE}
Are you requesting that some master type string thesaurus be maintained so
you can do a quick type synonym lookup from one DBMS to another ? As nice
as that might be, its a bit more than I envisioned for this feature, and sortof
exists by just using each DBMS's type_info_all() and translating between them.
Dean Arnold
Presicient Corp.
www.presicient.com
Any interest in a $sth-{TYPE_STRING} ?
I'm finding I frequently need to convert the DBI type metadata
from a result set into a string representation (either for display purposes,
or to generate additional queries).
Is there a need/desire to add a new $sth metadata item
TYPE_STRING that would contain that info, eg,
$sth-{TYPE}-[0] == SQL_INTEGER = $sth-{TYPE_STRING}-[0] = 'INTEGER'
$sth-{TYPE}-[0] == SQL_DECIMAL,
$sth-{PRECISION}-[0] == 10,
$sth-{SCALE}-[0] == 2= $sth-{TYPE_STRING}-[0] = 'DECIMAL(10,2)'
etc.
This appears to be one of those things that could have a default
implemention at the DBI layer, but overridden by the driver. This is a bit
more convenient than reconstructing things from type_info(), esp. since
precision/scale would already be supplied.
Since I've coded this translation about a dozen times
now, I could probably whip up a patch to DBI for the default.
(Probably based on type_info(), if driver provides it, else just
a best guess)
Opinions ? Have I overlooked something like this thats already in the DBI ?
Dean Arnold
Presicient Corp.
www.presicient.com
Re: proposed perform_transaction() method
sub perform_transaction {
my ($dbh, $coderef, $allow_non_transaction) = @_;
my $use_transaction = 1;
my $orig_AutoCommit = $dbh-{AutoCommit};
if ($orig_AutoCommit) {
unless (eval { $dbh-{AutoCommit} = 0; 1 }) {
die unless $allow_non_transaction;
$use_transaction = 0;
}
}
eval {
@result = (wantarray) ? $coderef-() : scalar $coderef-();
$dbh-commit if $use_transaction;
};
if ($@ $use_transaction) {
local $@; # protect original error
eval { $dbh-rollback };
}
die if $@; # propagate original error
$dbh-{AutoCommit} = 1 if $orig_AutoCommit;
return $result[0] unless wantarray;
return @result;
}
Hopefully it's self-explanatory. Comments welcome.
Tim
p.s. I've not actually tested this yet.
Er, is this giving users enough rope to shoot themselves in the foot ?
Since $coderef can contain *any* code, might users be (mis)led to believe
that anything they do in the coderef will get rolled back on any error ?
While it would be neat to have such a capability (ie, adding transaction
monitor capability to DBI), in this present form, the docs will need to be very
explicit about
what its capabilities really are (Bold and UPPERCASE THE POD DESCRIPTION).
OTOH, might a partial xaction monitor behavior be possible by the DBI
layer flagging that its acting as xaction monitor, and then tracking every
database handle that gets used within the $coderef, and applying the
rollback/commit to *all* such handles ? It ain't 2PC, but its a start.
E.g., a execute_for_fetch() pulling data from $srcdbh into $tgtdbh;
apps might want both to operate within a xaction, so if
$tgtdbh fails and is rolled back, $srcdbh is likewise rolledback in order to
release locks. (Probably lots of complications if apps start threading within
the $coderef)
And for that matter, maybe support an add'l $coderef, e.g, $recoverref,
the app can provide to perform any app level rollback operation ?
Or maybe just add class level begin_transaction(), commit(), and rollback()
to DBI, eg,
DBI-begin_transaction();
$dbh1-do(...);
$dbh2-do(...);
...some code...
DBI-commit();
with maybe some args to begin_transaction() to provide a rollback exit label
and/or a rollback coderef ? Or maybe borrowing some try/catch code from
http://www.perl.com/lpt/a/2002/11/14/exception.html ?
BTW: has there ever been an attempt to build a xaction monitor in Perl ?
Overthinking again,
Dean Arnold
Presicient Corp.
www.presicient.com
Re: proposed perform_transaction() method
While it would be neat to have such a capability (ie, adding transaction monitor capability to DBI), in this present form, the docs will need to be very explicit about what its capabilities really are (Bold and UPPERCASE THE POD DESCRIPTION). OTOH, might a partial xaction monitor behavior be possible by the DBI layer flagging that its acting as xaction monitor, and then tracking every database handle that gets used within the $coderef, and applying the rollback/commit to *all* such handles ? It ain't 2PC, but its a start. That's a whole different can of works I don't even want to think about right now as I'm up to my eyes in worms already. But my gut feel is I don't want to go there at all without doing 2PC properly via XA APIs. Overthinking again, Yes :) Tim. 'kay...but, XA issues aside, might a try...catch... type of construct be more elegant ? Maybe once I clear my own teetering pile of works, I'll take a stab at a DBIx that implements that behavior. Shouldn't be any later than 2007 or so... Dean Arnold Presicient Corp. www.presicient.com
Driver capabilities metadata ?
I don't see anything in the PODs, so... Is there any std. mechanism for querying a driver regarding its capabilities ? E.g., similar to get_info(), but related to driver behavior, rather than supported DBMS behavior. My need is primarily for a) character set support b) thread support c) array binding support Item (a) seems to be a bit nebulous at the moment, but in theory DBI could determine both items (b) and (c). There are probably other capabilities that could be queried (maybe this ties into the conformance testing efforts ?) Eg, if I'm writing an app to ETL between Oracle and DB2, it would be useful to know if both drivers support threaded operation, and native array binding, in order formulate an optimal execution environment/strategy. Probably on the bleeding edge again, but hopefully food for thought, Dean Arnold Presicient Corp. www.presicient.com
Re: Trace Topics
CHANGES in DBI 1.42 will say:
Added $h-parse_trace_flags(foo|SQL|7) to map a group of
trace flags into the corresponding trace flag bits.
Added automatic calling of parse_trace_flags() if
setting the trace level to a non-numeric value:
$h-{TraceLevel}=foo|SQL|7; $h-trace(foo|SQL|7);
DBI-connect(dbi:Driver(TraceLevel=SQL|foo):..., ...);
The DBI has 16 topic bits to play with. Drivers have 8. I expect
the DBI to assign some of its bits to common 'driver topics' so the
drivers own bits can be reserved for topics specific to each driver.
As you may expect, DBI topics names will begin with a capital letter
and driver specific topics with a lowercase letter.
For drivers written in C there'll be a macro like this:
if (DBIc_TRACE_TLL( topic_flags, topic_flag_level, trace_level )) {
print(...);
}
Where topic_flags are one or more bit flags related to the message,
topic_flag_level is the minimum trace_level at which the topic_flags
should be tested (ie topic_flags must match _and_ the handle trace
level must be at least topic_flag_level) and trace_level is the
handle trace level at which to print regardless of topic_flags matching.
DBIc_TRACE_TLL stands for trace topic-level-level.
if (DBIc_TRACE_TLL( DBItf_FOO|DBItf_BAR, 2, 5 )) { print(...) }
will always print if the handle trace level is = 9 otherwise it'll
print if the FOO or BAR topic is set and the handle trace level is = 2.
What I'm after now is a bit of brainstorming on the topic of topics.
The 'SQL' topic I plan to use to generate a trace of SQL statements
executed. The output will be *just* the raw SQL statement, possibly
with trailing whitespace replaced with a newline. If ParamValues
is available then an SQL comment will be appended with the ParamValues.
A topic flag:
may enable new trace messages regardless of level
may enable new trace messages if level some threshold
may add detail to existing trace messages
do anything else that seems like a good idea :)
Here are some ideas for other topics to get you started:
HndCre - handle creation detail (any type)
HndDest - handle destruction detail (any type)
MethInst - method install detail
MethDisp - method dispatch detail
SetErrI - set_err called to set info state
SetErrW - set_err called to set warn state
SetErrE - set_err called to set error state
SetErr - set_err called to set any (SetI|SetW|SetE)
Prof - Profiling detail
Pid - Show process id in (some) trace messages
As you can see, I'd like to keep the names short but reasonably mnemonic.
A topic can turn on more than one flag, such as SetErr above, which
will make it easy to split a topic into subtopics later if we want to
There'll also be an 'ALL' to turn on all flag bits, handy for testing
all the tracing code, or for masochists :)
Part of the reason for doing all this is so that high trace levels
can provide greater detail on a topic without being drowned in other
trace output. The first four topics above are all targeted a removing
'obscure details' from the 'normal' trace. I think that's one of the
best uses for topics: Let the normal trace levels be less cluttered.
Tim.
p.s. A reminder: a driver should output no trace information at all
at trace levels 1 and 2 (unless a specific topic is enabled).
How about a 'Protocol' topic that permits dumping client-server
protocol traces (ie bits on the wire) ?
(useful for pure perl drivers,
tho maybe not possible for drivers requiring external libraries,
eg ODBC)
Dean Arnold
Presicient Corp.
www.presicient.com
Re: More than one type of prepare
PostgreSQL has recently added a true server-side prepare, which raises some issues in regards to the prepare/execute DBI model. Our initial thought was simply to use the new prepare/execute when it was available on the backend, and keep everything transparent to the user. However, there are cases where a local (or non server-prepared) prepare is preferred, so we need to allow both. One idea I had, which Rudy Lippan also thought of for DBD::mysql, is to have a global and a local flag to toggle between the two. The global can be set at connect and resides at the $dbh level. The local is an extra attrib send to the prepare() method, which would override the global method. Toggling between the two *after* the prepare would even be possible, especially if you have an intelligent app that realizes that it may need to mix and match them. Has anyone else run into this? Does the above sound like a good solution? (The 'local' prepare for DBD::Pg just means that we build the complete SQL statement ourselves and send that off to the server). - -- Greg Sabino Mullane [EMAIL PROTECTED] Not certain about your needs for client side prepare, but I've a similar issue with Teradata, but solely for performance purposes. In essence, there are lots of operations for which prepare doesn't provide much except a pass/fail syntax check (INSERT, UPDATE, DELETE, various DDL), which will get handled at execute time anyway. Performing these PREPARE operations on a multiTerabyte DBMS with quite probably 1000's of concurrent users is probably not a good idea. So I trap those queries in the driver, do a simple syntax check, and dummy up the statement metadata (usually, none). However, there are times where a full prepare of these items is needed (e.g., during script compile in my IDE, TeraForge). In that case, I've provided a 'tdat_compatibility' attribute that specifies a maximum compatibility level of the DBD version that the app expects. This has the advantage that it can be applied not only to this particular issue, but to other behaviors as well. HTH, Dean Arnold Presicient Corp. www.presicient.com
Re: DBI, threads, install_method()
Just a followup: ActiveState now has Perl 5.8.3 available, it appears to eliminate the threading warning issue I reported earlier. Dean Arnold Presicient Corp. www.presicient.com
DBI 1.40, Perl 5.8.3, and fork()
I'm getting an odd behavior that appears to be different than Perl 5.6, hope you can shed some light. I have an app that opens a connection, then forks some processes, which also open connections, do some stuff, then exit. Thing is, when the children exit and their driver handle gets DESTROY'ed, disconnect_all() is called to cleanup. But since the children inherit the list of open connections from the parent process, they destroy the parent's connection. The behavior occurs on both Linux (Fedora 1) and OS X 10.3. This code has been used with Perl 5.6 and DBI 1.36 without an issue, presumably because the driver handle got recreated, but I don't see the driver() method get called by the child processes, so the connection list exists as inherited from the parent. Has something changed ? Can DBI see the change and invoke the CLONE as for threads, or is this something I need to handle internally by tracking PID's ? Dazed confused, Dean Arnold Presicient Corp. www.presicient.com
Re: DBI 1.40, Perl 5.8.3, and fork()
Nevermind...my bad, I dropped a bit of code during the latest updates. Sorry, Dean Arnold Presicient Corp. www.presicient.com
Re: DBI, threads, install_method()
I've managed to recreate the threading issue
with DBD::Sponge (see script below) ( I also
modified DBD::Sponge - not included below -
to add an $installed flag for its one
installed method)
The secret seems to be spawning a few threads that
connect(), then disconnect and exit, then
spawning some more threads that connect().
The 2nd set of threads generate the warning msg.
The interesting thing is that the scalar it complains about
is the same in all cases.
I'll try this on 5.8.3 when I get a chance.
###
#!/usr/local/bin/perl -w
use DBI;
use DBI qw(:sql_types);
use Config;
use threads;
use threads::shared; # so we can share data
use Thread::Queue; # a thread-safe shared queue!
#
# verify threads are available
#
die 'Perl not built with threads'
unless $Config{useithreads};
#
# open a connection in main thread
#
my $maindbh = DBI-connect('dbi:Sponge:', '','', {
PrintError = 1,
RaiseError = 0,
AutoCommit = 1
}) || die DBI-errstr;
#
# now spawn a thread that opens a connection
#
my $queue = Thread::Queue-new();
push @thrds, threads-create(\child_thread, $queue)
foreach (1..2);
$queue-enqueue('go') foreach (1..2);
#
# wait for completion
#
$_-join foreach (@thrds);
#
# now spawn again
#
@thrds = ();
print Spawning again\n;
push @thrds, threads-create(\child_thread, $queue)
foreach (1..2);
$queue-enqueue('go') foreach (1..2);
#
# wait for completion
#
$_-join foreach (@thrds);
$maindbh-disconnect;
print Test completed\n;
sub child_thread {
my ($queue) = @_;
$queue-dequeue();
my $dbh = DBI-connect('dbi:Sponge:', '', '', {
PrintError = 1,
RaiseError = 0,
AutoCommit = 1
}) || die DBI-errstr;
print Child: connected and exitting\n;
$dbh-disconnect;
}
Dean Arnold
Presicient Corp.
www.presicient.com
Re: DBI, threads, install_method()
Guess I should mention: When I don't open any connections in the main thread before spawning threads, the problem doesnt occur (ie, the threads don't walk on each other with install_method). The problem only happens when the main thread opens a connection, and then spawns some threads that also open connections. Dean Arnold Presicient Corp. www.presicient.com
