Fwd: please help with DBD-Oracle-1.76

[Tim Bunce]

> Begin forwarded message:
> 
> From: Daniel Chmielewski 
> Subject: Fwd: please help with DBD-Oracle-1.76
> Date: 5 September 2022 at 10:03:15 IST
> To: t...@cpan.org
> 
> Tim, please help. 
> Regards,
> Daniel
> 
> 
> -- Forwarded message -
> Od: Daniel Chmielewski  <mailto:daniel.chmielew...@gmail.com>>
> Date: śr., 31 sie 2022 o 15:10
> Subject: please help with DBD-Oracle-1.76
> To: mailto:t...@cpan.org>>
> 
> 
> Hi TIm,
> Can you help me. 
> I have problem with installing DBD-Oracle-1.76. 
> 
> At all it works (make install), but tests fail (make tests). It seems that 
> the software does not recognized oracle instant client version 21.7. This is 
> a 'portable' version with only requires unpack in place. 
> Have you ever made any test with this ?  Please help. Do you have any 
> suggestions?
> Regards,
> Daniel
> 
> 
> Short description:
> 1.
> DBD-Oracle-1.76 requires DBI, and all the tests of DBI works fine. 
> 
> 2.
> DBI-1.63 (all tests are successful, make, make test, make install => no error 
> at all).
> 
> 3.
> I also install instant client from oracle:
> https://www.oracle.com/pl/database/technologies/instant-client/linux-x86-64-downloads.html
>  
> <https://www.oracle.com/pl/database/technologies/instant-client/linux-x86-64-downloads.html>
> 
> export ORACLE_HOME=/root/ora2pg/instantclient_21_7
> 
> export LD_LIBRARY_PATH=/root/ora2pg/instantclient_21_7
> 
> export PATH=$ORACLE_HOME/bin:$PATH
> 
> 
> 4.
> and set env. variable (all at root users), database is located on other 
> server, not on this on which is perl installed. 
> 
> 
> But with DBD-Oracle-1.76 installed on it I get the following error:
> 
> root@stlx DBD-Oracle-1.76]# perl Makefile.PL
> 
> Using DBI 1.643 (for perl 5.026003 on x86_64-linux-thread-multi) installed in 
> /usr/local/lib64/perl5/auto/DBI/
> 
> Configuring DBD::Oracle for perl 5.026003 on linux (x86_64-linux-thread-multi)
> 
>  
> 
> If you encounter any problem, a collection of troubleshooting
> 
> guides are available under lib/DBD/Oracle/Troubleshooting.
> 
> 'DBD::Oracle::Troubleshooting' is the general troubleshooting
> 
> guide, while platform-specific troubleshooting hints
> 
> live in their labelled sub-document (e.g., Win32
> 
> hints are gathered in 'lib/DBD/Oracle/Troubleshooting/Win32.pod').
> 
>  
> 
> Installing on a linux, Ver#4.18
> 
> Using Oracle in /root/ora2pg/instantclient_21_7
> 
> Can't find sqlplus. Pity, it would have helped.
> 
> I'm having trouble finding your Oracle version number... trying harder
> 
>  
> 
> WARNING: Could not determine Oracle client version, defaulting to
> 
> version 9.2.0.4.0. Some features of DBD::Oracle may not work.
> 
> Oracle version-based logic in Makefile.PL may produce erroneous
> 
> results. You can use "perl Makefile.PL -V X.Y.Z" to specify your
> 
> client version.
> 
>  
> 
> Oracle Version 9.2.0.4.0 (9.2)
> 
> Looks like an Instant Client installation, okay
> 
> Your LD_LIBRARY_PATH env var is set to '/root/ora2pg/instantclient_21_7'
> 
> Oracle sysliblist:
> 
> Found header files in /root/ora2pg/instantclient_21_7/sdk/include.
> 
>  
> 
> client_version=9.2
> 
>  
> 
>  
> 
> DEFINE= -Wall -Wno-comment -DUTF8_SUPPORT -DORA_OCI_VERSION=\"9.2.0.4.0\"
> 
>  
> 
>  
> 
> Checking for functioning wait.ph <http://wait.ph/>
> _FORTIFY_SOURCE requires compiling with optimization (-O) at 
> /usr/lib64/perl5/features.ph <http://features.ph/> line 207.
> 
>  
> 
>  
> 
> System: perl5.026003 linux x86-038.build.eng.bos.redhat.com 
> <http://x86-038.build.eng.bos.redhat.com/> 4.18.0-305.17.1.el8_4.x86_64 #1 
> smp mon aug 30 07:26:31 edt 2021 x86_64 x86_64 x86_64 gnulinux
> 
> Compiler:   gcc   -g -D_REENTRANT -D_GNU_SOURCE -O2 -g -pipe -Wall 
> -Werror=format-security -Wp,-D_FORTIFY_SOURCE=2 -Wp,-D_GLIBCXX_ASSERTIONS 
> -fexceptions -fstack-protector-strong -grecord-gcc-switches 
> -specs=/usr/lib/rpm/redhat/redhat-hardened-cc1 
> -specs=/usr/lib/rpm/redhat/redhat-annobin-cc1 -m64 -mtune=generic 
> -fasynchronous-unwind-tables -fstack-clash-protection -fcf-protection -fwrapv 
> -fno-strict-aliasing -I/usr/local/include -D_LARGEFILE_SOURCE 
> -D_FILE_OFFSET_BITS=64
> 
> Linker: /usr/bin/ld
> 
> Sysliblist:
> 
> Linking with -lclntsh.
> 
>  
> 
> LD_RUN_PATH=/root/ora2pg/instantclient_21_7
> 
> Using DBD::Oracle 1.76.
> 
> Using DBD::Oracle 1.76.
> 
> =
> 
> Please help,
> Daniel
> 
> 
> -- 
> Daniel Chmielewski
> 
> 
> -- 
> Daniel Chmielewski

Oracle DBD with 19c - forked child hangs on exit

[Tim Wolfe]

We just upgraded one of our database servers from Oracle 12.2.0.1 to
19.15.0 and one our perl programs is behaving differently. We are running
with RedHat 8.4 using Perl v5.26.3 and the latest DBD::Oracle and DBI from
cpan.

Under 19c, a forked child hangs on exit if the parent has (or had) an
oracle connection where the connection handle is a global (using 'our').
This does not happen if the connection handle is a local (declared with
'my'). When running against a 12c database, with DBD::Oracle using 12c
libraries, the child process exits normally regardless of how the variable
is 'declared'.

Note also that this issue does not happen if the child exec's another
program.

The block of code below exhibits the issue in our environment. This is just
a simplification. The real code uses packages and so the connection handle
needs to be a global.

Any help appreciated.

use strict;
use warnings;

use DBI;

# If $dbh declared with my, this works witn 19c
# otherwise, exit in child below never returns
our $dbh = DBI->connect('dbi:Oracle:ORCL', 'scott', 'tiger',
   { RaiseError => 1, AutoCommit => 0, PrintError
=> 0, InactiveDestroy  => 1 });

print "Connected to db\n";
$dbh->disconnect;
#undef $dbh;   # Child process exits normally if this uncommented

my $pid;
$pid = fork;

if( $pid == 0 ) {
print "This is child process\n";
print "Child process exiting now\n";
exit 0; # Never returns if $dbh is a global
}

print "This is parent process and child ID is $pid\n";
print "Parent Waiting on child\n";
my $chldPid = wait;
print "Parent done. Child pid $chldPid has completed\n";

exit 0;

(Fwd) DBD:Oracle

[Tim Bunce]

- Forwarded message from Ajay Kumar  -

Date: Mon, 13 Jul 2020 15:32:55 +
From: Ajay Kumar 
To: Tim Bunce 
Subject: DBD:Oracle

   Hi Tim,
   Hope, you are sound and safe with your loved ones.
   I am trying to install the ORA2PG  tool which migrates Oracle data into 
PostgreSQL database.
   I have installed Strawberry Perl, ActiveState perl on Oracle server box 
where Oracle client is not
   installed but Oracle 12c server is there. I have also installed PostgreSQL 
database on the same server.
   The server is Windows 2012R2 64 bit.
   One of the prerequisite of ora2pg is to install DBD::Oracle. How can I 
install this one on this server.
   If you can help me that would be great and I really appreciate it or if you 
can point me to someone.
   Thanks,
   Ajay

   Ajay Kumar

   Applications Developer

   Prince William County Public Schools

- End forwarded message -

Announce: DBI 1.643

[Tim Bunce]

=head2 Changes in DBI 1.643 - 31st January 2020

Fix memory corruption in XS functions when Perl stack is reallocated
thanks to Pali
Fix calling dbd_db_do6 API function
thanks to Pali
Fix potentially calling newSV(0) in malloc_using_sv()
thanks to Pali
Fix order of XS preparse() ps_accept and ps_return argument names
thanks to Petr Písař
Fix a potential NULL profile dereference in dbi_profile()
thanks to Petr Písař
Fix a buffer overflow on an overlong DBD class name
thanks to Petr Písař

Remove remnants of support for perl <= v5.8.0
thanks to Pali and H.Merijn Brand
Update Devel::PPPort and remove redundant compatibility macros
thanks to Pali and H.Merijn Brand

Correct minor typo in documentation
thanks to Mohammad Anwar
Correct documentation introducing $dbh->selectall_array()
thanks to Pali
Introduce select and do wrappers earlier in the documentation
thanks to Dan Book
Mark as deprecated old API functions which overflow or are affected by
Unicode issues, thanks to Pali

Add new attribute RaiseWarn, similar to RaiseError,
thanks to Pali

=cut

Many thanks to those who contributed to this somewhat overdue release.

Enjoy!

Tim.

Announce: DBI 1.642

[Tim Bunce]

  file: $CPAN/authors/id/T/TI/TIMB/DBI-1.642.tar.gz
  size: 604581 bytes
   md5: f2ba18b5cea1c8cb322a62be0a847f3d
  sha1: 4838da411896707eb89acf63ae2d35c88ff841c7

=head2 Changes in DBI 1.642 - 28th October 2018

Fix '.' in @INC for proxy test under parallel load
thanks to H.Merijn Brand.
Fix driver-related croak() in DBI->connect to report the original DSN
thanks to maxatome #67

Introduce a new statement DBI method $sth->last_insert_id()
thanks to pali #64
Allow to call $dbh->last_insert_id() method without arguments
thanks to pali #64
Added a new XS API function variant dbd_db_do6()
thanks to Pali #61

Fix misprints in doc of selectall_hashref
thanks to Perlover #69
Remove outdated links to DBI related training resources. RT#125999

=cut

Many thanks to those who contributed to this release.

Enjoy!

Tim.

p.s. During testing it was discovered that the recent Clone-0.40 release
causes DBI test failures. There's already a Clone-0.41 release that works.
This is very unlikely to impact you. Many thanks to Slaven Rezić for his
testing and investigation https://rt.cpan.org/Ticket/Display.html?id=127501

Re: Extend API for last_insert_id

[Tim Bunce]

On Fri, May 04, 2018 at 02:10:18PM +0200, p...@cpan.org wrote:
> Hello, do you have any opinion or comments?
> 
> Tim, you as a DBI maintainer, what do you think about those ideas?
> 
> On Friday 27 April 2018 16:03:59 p...@cpan.org wrote:
> > 
> > So I'm proposing change that caller would be allowed to call
> > $dbh->last_insert_id() without any argument

Ok.

> > Second change: Add a new statement method $sth->last_insert_id().

That seems fine. I'd write the fallback code like this:

sub last_insert_id { return shift->{Database}->last_insert_id(@_) }

Thanks Pali.

Tim.

Re: debugging and stepping into ->do

[Tim Bunce]

On Wed, Apr 25, 2018 at 06:34:20PM +, Jeff Macdonald wrote:
>Thanks Brian!
>I'm familiar with C. My goal with digging into DBI was to programmatically 
> cause a SQL error in a test
>case. I needed somehow to create a wrapper around do (or execute) in which 
> I'd examine the Statement and
>"die" accordingly. This would allow me to validate database logic that 
> rolled back a transaction.

https://metacpan.org/pod/DBI#Callbacks may help.

Tim.

Announce DBI-1.640

[Tim Bunce]

  file: $CPAN/authors/id/T/TI/TIMB/DBI-1.640.tar.gz
  size: 603787 bytes
   md5: 47d37079ba164908a65fb86f8179cb74
  sha1: 2446ea4e139cd56c66f1dd99c58536fa3f4e97cc

=head2 Changes in DBI 1.640 - 28th January 2018

Fix test t/91_store_warning.t for perl 5.10.0
thanks to pali #57

Add Perl 5.10.0 and 5.8.1 specific versions to Travis testing
thanks to pali #57
Add registration of mariadb_ prefix for new DBD::MariaDB driver
thanks to pali #56

=cut

Many thanks to pali for these contributions.

Enjoy!

Tim.

Re: Need help with an unexpected behaviour

[Tim Bunce]

On Fri, Jan 26, 2018 at 04:47:35PM +0100, Andreas Mock wrote:
> Hi all,
> 
>   9 my %attr = (
>  10 'AutoCommit' => 1,
>  11 'RaiseError' => 1,

>  35 sub doit {
>  36 my $dbh = shift;
>  37
>  38 local $dbh->{'AutoCommit'} = 1;
>  39
>  40 $dbh->begin_work;
>  41 $dbh->do("insert into mca_rb_test values ('short')");
>  42 $dbh->do("insert into mca_rb_test values
> ('looong')");
>  43 $dbh->commit;

> Whithout line 38 I get what I expect. [...]

> BUT: As soon as I have line 38 in there, which shouldn't change
> the initially set 'AutoCommit', the first insert is commited
> to the database even the exeption is raised in the opened
> transaction.

That seems like a driver bug at first sight.

While "local $dbh->{'AutoCommit'} = 1;" looks like a simple
hash assignment there's a lot going on behind the scenes.
($dbh is a ref to a tied hash so a STORE method gets called
to handle the assignment.)

While it doesn't "change" the AutoCommit setting, since it's already
enabled, the "local" does cause Perl to arrange to execute
$dbh->{'AutoCommit'} = 1 when the scope exits.

Both the initial assignment and the re-setting assignment may have side
effects.

> Can someone explain what is happening behind the scenes or
> give a pointer to some helpful documentation which I have overlooked?

The DBI (and most drivers) have extensive tracing built in.
The trace output is often very helpful to see what's really happening.
See https://metacpan.org/pod/DBI#TRACING

It'll show you the effect of the local AutoCommit assignment and
re-setting assignment at scope-exit.

Tim.

Announce: DBI 1.639

[Tim Bunce]

  file: $CPAN/authors/id/T/TI/TIMB/DBI-1.639.tar.gz
  size: 603697 bytes
   md5: f9bf9775b3dbaabc4630b2b29941aa89
  sha1: d486ad357708054827017a0d7151554704ce6607

=head2 Changes in DBI 1.639 - 28th December 2017

Fix UTF-8 support for warn/croak calls within DBI internals,
thanks to pali #53
Fix dependency on Storable for perl older than 5.8.9,
thanks to H.Merijn Brand.

Add DBD::Mem driver, a pure-perl in-memory driver using DBI::DBD::SqlEngine,
thanks to Jens Rehsack #42

Corrected missing semicolon in example in documentation,
thanks to pali #55

=cut

Many thanks to those who contributed.

Enjoy!

Tim.

p.s. There wasn't a 1.638 release.

Re: Oracle encoding query

[Tim Bunce]

On Wed, Dec 06, 2017 at 04:53:36PM +0100, H.Merijn Brand wrote:
> On Wed, 6 Dec 2017 15:34:17 +0000, Tim Bunce <tim.bu...@pobox.com>
> wrote:
> 
> > A view might be useful. Or perhaps define your own function to wrap the 
> > expression.
> 
> That worked very well!
> 
> CREATE OR REPLACE FUNCTION diac_u (v VARCHAR2) RETURN NCHAR IS
> BEGIN
> RETURN utl_i18n.raw_to_nchar (utl_i18n.string_to_raw (v), 'utf8');
> END;
> /
> 
> select diac_u (land_u) from land where c_land = 7072;

You might want to add DETERMINISTIC before the IS so the function can be
used more efficiently in some cases.

Tim.

Re: Oracle encoding query

[Tim Bunce]

On Wed, Dec 06, 2017 at 04:12:53PM +0100, H.Merijn Brand wrote:
> On Wed, 6 Dec 2017 15:50:11 +0100, "H.Merijn Brand"
> > 
> > I have been playing with several variants of
> > 
> >   select convert (land_u, 'AL16UTF16', 'UTF8') from land where c_land = 
> > 7072;
> > 
> > but I didn't get SQL Developer to show the ë
> 
>  select utl_encode.text_encode (land_u) from land where c_land = 7072;
> 
> =>
> 
>  Zuidrhodesi=C3=AB
> 
> so SQL developer *does* see the individual bytes as they are stored
> 
> I can get the expected display with
> 
>  select utl_i18n.raw_to_nchar (utl_i18n.string_to_raw (land_u), 'utf8') from 
> land where c_land = 7072;
> 
> which experiences as rather overcomplicated, esp if I need to do this
> for all _u fields in the query :(

A view might be useful. Or perhaps define your own function to wrap the 
expression.

Tim.

Announce: DBI 1.637

[Tim Bunce]

  file: $CPAN/authors/id/T/TI/TIMB/DBI-1.637.tar.gz
  size: 596423 bytes
   md5: fdcb1739c923300de7bc5250c1c75337
  sha1: 183ba1542a7499b508568c1765a4a1bcc4b4bbc9

=head2 Changes in DBI 1.637

Fix use of externally controlled format string (CWE-134) thanks to pali #44
This could cause a crash if, for example, a db error contained a %.
https://cwe.mitre.org/data/definitions/134.html
Fix extension detection for DBD::File related drivers
Fix tests for perl without dot in @INC RT#120443
Fix loss of error message on parent handle, thanks to charsbar #34
Fix disappearing $_ inside callbacks, thanks to robschaber #47

Allow objects to be used as passwords without throwing an error, thanks to 
demerphq #40
Allow $sth NAME_* attributes to be set from Perl code, re #45
Added support for DBD::XMLSimple thanks to nigelhorne #38

Documentation updates:
Improve examples using eval to be more correct, thanks to pali #39
Add cautionary note to prepare_cached docs re refs in %attr #46
Small POD changes (Getting Help -> Online) thanks to openstrike #33
Adds links to more module names and fix typo, thanks to oalders #43
Typo fix thanks to bor #37

=cut

Many thanks to those who contributed to this release.

Enjoy!

Tim.

(Fwd) Oracle.pm

[Tim Bunce]

- Forwarded message from Gowtham <shiningstargau...@gmail.com> -

Date: Mon, 6 Mar 2017 15:40:59 -0600
From: Gowtham <shiningstargau...@gmail.com>
To: tim.bu...@pobox.com
Subject: Oracle.pm

Hi Tim,

I feel that you are the right person to check this with.

I am trying to understand the code on Oracle.pm (DBD::Oracle) to debug my perl 
code which tries to make a connection to an Oracle DB. My code exits on the 
below piece of code on debug mode and i am unable to establish the reason for 
it.
   DBD::Oracle::db::_login($dbh,$dbname,$user,$auth,$attr) or return undef;

I am unable to find the definition for the subroutine _login() which will help 
me to understand the reason for returning 'undef'. Could you please kindly 
advice me. 

Correct me if i have reached a wrong email. 

Thanks - Gautam

- End forwarded message -

Re: FetchHashKeyName emits invalid keys for utf8 fieldnames

[Tim Bunce]

Thanks for the report Chris.

Any chance you could write up a small test case for us?
Ideally in a new issue at https://github.com/perl5-dbi/dbi/issues

Thanks.

Tim.

On Fri, Feb 24, 2017 at 12:55:23PM +1100, Chris Hutchinson wrote:
> Using DBI v 1.636
> 
> $dbi->{FetchHashKeyName}=’NAME_lc’ or NAME_uc apparently produces different 
> keys from ‘lc’ and ‘uc’ functions for fieldnames containing non-ascii 
> characters.
> 
> For example, ‘ämne-Abc’  with FetchHashKeyName=NAME_lc results in result key 
> ‘\x{0}\x{0}mne-abc’
> 
> 
> Summary of my perl5 (revision 5 version 24 subversion 0) configuration:
> 
>   Platform:
> osname=linux, osvers=2.6.32-642.6.2.el6.x86_64, archname=x86_64-linux
> uname='linux yonkyo.local 2.6.32-642.6.2.el6.x86_64 #1 smp wed oct 26 
> 06:52:09 utc 2016 x86_64 x86_64 x86_64 gnulinux '
> config_args='-de -Dprefix=/opt/perlbrew/perls/perl-5.24.0 
> -Aeval:scriptdir=/opt/perlbrew/perls/perl-5.24.0/bin'
> hint=recommended, useposix=true, d_sigaction=define
> useithreads=undef, usemultiplicity=undef
> use64bitint=define, use64bitall=define, uselongdouble=undef
> usemymalloc=n, bincompat5005=undef
>   Compiler:
> cc='cc', ccflags ='-fwrapv -fno-strict-aliasing -pipe -fstack-protector 
> -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 
> -D_FORTIFY_SOURCE=2',
> optimize='-O2',
> cppflags='-fwrapv -fno-strict-aliasing -pipe -fstack-protector 
> -I/usr/local/include'
> ccversion='', gccversion='4.4.7 20120313 (Red Hat 4.4.7-17)', 
> gccosandvers=''
> intsize=4, longsize=8, ptrsize=8, doublesize=8, byteorder=12345678, 
> doublekind=3
> d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=16, 
> longdblkind=3
> ivtype='long', ivsize=8, nvtype='double', nvsize=8, Off_t='off_t', 
> lseeksize=8
> alignbytes=8, prototype=define
>   Linker and Libraries:
> ld='cc', ldflags =' -fstack-protector -L/usr/local/lib'
> libpth=/usr/local/lib /usr/lib /lib/../lib64 /usr/lib/../lib64 /lib 
> /lib64 /usr/lib64 /usr/local/lib64
> libs=-lpthread -lnsl -lgdbm -ldb -ldl -lm -lcrypt -lutil -lc
> perllibs=-lpthread -lnsl -ldl -lm -lcrypt -lutil -lc
> libc=libc-2.12.so, so=so, useshrplib=false, libperl=libperl.a
> gnulibc_version='2.12'
>   Dynamic Linking:
> dlsrc=dl_dlopen.xs, dlext=so, d_dlsymun=undef, ccdlflags='-Wl,-E'
> cccdlflags='-fPIC', lddlflags='-shared -O2 -L/usr/local/lib 
> -fstack-protector'
> 
> 
> Characteristics of this binary (from libperl):
>   Compile-time options: HAS_TIMES PERLIO_LAYERS PERL_COPY_ON_WRITE
> PERL_DONT_CREATE_GVSV
> PERL_HASH_FUNC_ONE_AT_A_TIME_HARD PERL_MALLOC_WRAP
> PERL_PRESERVE_IVUV USE_64_BIT_ALL USE_64_BIT_INT
> USE_LARGE_FILES USE_LOCALE USE_LOCALE_COLLATE
> USE_LOCALE_CTYPE USE_LOCALE_NUMERIC USE_LOCALE_TIME
> USE_PERLIO USE_PERL_ATOF
>   Locally applied patches:
> Devel::PatchPerl 1.42
>   Built under linux
>   Compiled at Nov  3 2016 12:25:49
>   %ENV:
> PERLBREW_BASHRC_VERSION="0.75"
> PERLBREW_HOME="/home/chris/.perlbrew"
> PERLBREW_MANPATH="/opt/perlbrew/perls/perl-5.24.0/man"
> PERLBREW_PATH="/opt/perlbrew/bin:/opt/perlbrew/perls/perl-5.24.0/bin"
> PERLBREW_PERL="perl-5.24.0"
> PERLBREW_ROOT="/opt/perlbrew"
> PERLBREW_VERSION="0.75"
>   @INC:
> /opt/perlbrew/perls/perl-5.24.0/lib/site_perl/5.24.0/x86_64-linux
> /opt/perlbrew/perls/perl-5.24.0/lib/site_perl/5.24.0
> /opt/perlbrew/perls/perl-5.24.0/lib/5.24.0/x86_64-linux
> /opt/perlbrew/perls/perl-5.24.0/lib/5.24.0
> .
> ---
> 
> Test case script
> =
> 
> use strict;
> use utf8;
> use Test::More tests => 26;
> use Data::Dumper;
> 
> use DBI;
> 
> my $dbi=DBI->connect(
> 'dbi:Pg:dbname=test_db',
> 'chris',
> '',
> {
> pg_enable_utf8  =>  1,
> }
> );
> 
> my @expect=(
> [ 'NAME', "ABc", "ABc" ],
> [ 'NAME_uc', "ABc", "ABC" ],
> [ 'NAME_lc', "ABc", "abc" ],
> 
> [ 'NAME', "てすと-ABc", "てすと-ABc" ],
> [ 'NAME_uc', "てすと-Abc", "てすと-ABC" ],
> [ 'NAME_uc', "てすと-Abc", "てすと-ABC" ],
> [ 'NAME_lc', "てすと-Abc", "てすと-abc" ],
> [ 'NAME_lc', "てすと-Abc", "てすと-abc" ],
> 
> [ 'NAME', "ÄMNE-Abc", "ÄMN

(Fwd) RE: Issues with Oracle DBD in Cygwin

[Tim Bunce]

- Forwarded message from "Neargarder, Keith" 
 -

Date: Wed, 12 Oct 2016 12:22:00 +
From: "Neargarder, Keith" 
To: "'t...@cpan.org'" , "'byter...@cpan.org'" 
, "'yan...@cpan.org'" , 
"'mjev...@cpan.org'" 
CC: 'Mike Towery' , 'Christopher Jones' 
, "'john...@pharmacy.arizona.edu'" 

Subject: RE: Issues with Oracle DBD in Cygwin

   FYI - follow up.



   I believe the issue was a 32-bit versus 64-bit problem. I believe the Cygwin 
Perl is 64-bit and my
   Oracle (full) client is 32-bit. I have a 3^rd party application on this 
server that requires 32-bit
   Oracle. Although I believe I tried both 32 and 64-bit instant client when 
building DBD-Oracle inside
   Cygwin and neither worked.



   Anyway how I finally got a working version was to download Active State 
32-bit Perl for Windows, use PPM
   to install DBD-Oracle 1.74 and using my 32-bit full Oracle client everything 
is looking pretty good. I
   removed Cygwin's Perl and am using Windows Perl within the Cygwin 
environment. This presents some
   challenges with directory paths - Windows versus Cygwin - but these 
challenges seem easier than getting
   DBD-Oracle to work within the Cygwin/Perl environment.



   Couple of posts that helped in case anyone is interested:

   [1]http://www.cs.unc.edu/~jeffay/dirt/FAQ/cygwin-perl.html



   [2]http://www.nntp.perl.org/group/perl.dbi.users/2014/01/msg36852.html





   From: Neargarder, Keith
   Sent: Friday, September 16, 2016 5:16 PM
   To: 't...@cpan.org' ; 'byter...@cpan.org' 
; 'yan...@cpan.org'
   ; 'mjev...@cpan.org' 
   Subject: Issues with Oracle DBD in Cygwin



   Gentlemen,



   Any assistance is greatly appreciated. I have been working on this for days 
now and am completely stuck.
   I cannot find a way past the error below. I have searched online and tried 
everything I have found and
   no luck. Wasn't sure if I should just create another Cygwin/DBD-Oracle post 
or what???



   Suggestions please???



   Let me know if you need more info. I tried to be as thorough as possible.



   Couple of things I have noticed that seem off to me and might be an issue???

   1.   after unpacking the instant client files into 
c:\oracle\instantclient_12_1 (set as ORACLE_HOME)
   I do NOT have a "lib" or "rdbms/lib" subdirectory - am I missing something? 
I have been using
   LD_LIBRARY_PATH=$ORACLE_HOME/lib and I see it and the rdbms/lib referenced 
in LD_RUN_PATH but those
   directories do not exist. If it were a problem I sure would expect some sort 
of error message but
   nothing jumps out at me???

   2.   I cannot connect to my target database with the sqlplus installed 
under instant client however
   I can connect using another Oracle client installed on the same server. 
Obviously this is an issue but I
   don't believe I am even getting to the point where my Perl script tries to 
connect as it cannot even
   load the Oracle.dll. With sqlplus I get the error: ORA-12154: TNS:could not 
resolve the connect
   identifier specified

   3.   Tried using the other Oracle client I have installed where sqlplus 
does work but still get the
   same error when trying to run my Perl script.

   4.   when running "perl Makefile.pl -V 12.1.0" one thing/difference that 
caught my eye is in my
   output it has a line "Generating a Unix-style Makefile" whereas I have seen 
on another Gygwin/DBD-Oracle
   post "Generating a GNU-style Makefile" - could this be an issue??? The make 
file seems to "work", runs
   to completion and creates all the targets, so I guess it is OK.



   Info:

   . Windows Server 2008 R2 SP1 64-bit OS

   . Cygwin Version 2.5.1

   . Perl 5.22.2 (Cygwin's Perl build, see output of "perl -V" below)

   . Oracle 12.1.0 (database on a Linux server if that matters; I am 
able to connect to it from
   this Windows server with sqlplus

   . Instant Client 12.1.0.2.0 - first I tried 64-bit, did not work 
then tried 32-bit when I
   remembered another 3^rd party software required 32-bit Oracle client; I 
downloaded the Instant client
   basic, SDK and sqlplus zip files and extracted them all into 
c:\oracle\instantclient_12_1

   . DBD-Oracle 1.74



   Environment variables:

   . ORACLE_DSN="DBI:Oracle:host=$my_host;sid=$my_sid"

   . ORACLE_USERID=$my_user/$my_pass

   . ORACLE_HOME=/cygdrive/c/oracle/instantclient_12_1

   . LD_LIBRARY_PATH=$ORACLE_HOME/lib



   Steps taken (as outlined in Cygwin.pod):

   5.   extracted the DBD-Oracle 1.74 package into my personal work 
directory
   /home/svb28/perl5/DBD-Oracle-1.74

   6.   cd /home/svb28/perl5/DBD-Oracle-1.74

   7. 

Re: sth->prepare() setting Active?

[Tim Bunce]

Looks good. Thanks David!

Tim.

On Fri, Sep 30, 2016 at 04:03:35PM -0500, David Nicol wrote:
> sorry, this one is better
> 
>"Active"
> 
>Type: boolean, read-only
> 
>The "Active" attribute is true if the handle object is "active". This
>is rarely used in applications. The exact meaning of active depends on
>the database driver, but some aspects of the semantics are defined for
>interoperability. These include:
> 
>o   For a database handle, active typically means that the handle is
>connected to a database ("$dbh->disconnect" sets "Active" off).
> 
>o   For a statement handle it typically means that the handle is a
>"SELECT" that may have more data to fetch. (Fetching all the data
>or calling "$sth->finish" sets "Active" off.)
> 
>o   Prepared statement handles that must have "execute" called on them
>before they will return data should not be active until that
>happens.

> --- DBI.pm_orig   2016-09-30 15:28:20.0 -0500
> +++ DBI.pm2016-09-30 15:59:26.0 -0500
> @@ -3595,13 +3595,29 @@
>  Type: boolean, read-only
>  
>  The C attribute is true if the handle object is "active". This is 
> rarely used in
> -applications. The exact meaning of active is somewhat vague at the
> -moment. For a database handle it typically means that the handle is
> -connected to a database (C<$dbh-Edisconnect> sets C off).  For
> -a statement handle it typically means that the handle is a C
> +applications. The exact meaning of active depends on the database driver, 
> but some aspects
> +of the semantics are defined for interoperability. These include:
> +
> +=over
> +
> +=item
> +
> +For a database handle, active typically means that the handle is
> +connected to a database (C<$dbh-Edisconnect> sets C off).
> +
> +=item
> +
> +For a statement handle it typically means that the handle is a C
>  that may have more data to fetch. (Fetching all the data or calling 
> C<$sth-Efinish>
>  sets C off.)
>  
> +=item
> +
> +Prepared statement handles that must have C called on them before 
> they will return data
> +should not be active until that happens.
> +
> +=back
> +
>  =head3 C
>  
>  Type: boolean

Re: sth->prepare() setting Active?

[Tim Bunce]

On Fri, Sep 30, 2016 at 12:40:02AM +0100, Russell Howe via dbi-users wrote:
> On Thu, Sep 29, 2016 at 11:30:08PM -, Greg Sabino Mullane wrote:
> > 
> > > Should a call to prepare() return an Active statement? (i.e. 
> > > $sth->{Active} == 1)
> > >
> > > This appears to be the behaviour of DBD::Sybase, but not DBD::Pg
> > 
> > I don't think there is a canonical answer to that, but I can say that 
> > DBD::Pg in most cases will not even talk to the server until the first 
> > execute() after the prepare(), so it not being Active seems a sane 
> > interpretation.

> > The docs for DBD::Pg:
> > 
> >Indicates if a handle is active or not. For database handles, this 
> >indicates if the database has been disconnected or not. For statement 
> >handles, it indicates if all the data has been fetched yet or not. 
> >Use of this attribute is not encouraged.
> > 
> > As far as I can tell, DBD::Sybase makes not effort to do anything special 
> > regarding that attribute.
> 
> DBD::Sybase has this
> 
> /* Re-enable the active flag here (in 1.05_03) to fix bug with
>finish not getting called correctly */
> DBIc_ACTIVE_on(imp_sth);
> 
> at the end of syb_st_prepare() in dbdimp.c
> 
> So, it explictly sets Active to deal with some issue that I haven't
> fully delved into.
> 
> > In short, I would not rely upon it, especially across DBDs.
> 
> That's unfortunate, because Class::DBI does.
> 
> $ grep -r Active .
> ./lib/Class/DBI.pm:   $sth->execute(@$args) unless $sth->{Active};
> 
> (from sth_to_objects)
> 
> As far as I can see, this is to work out whether the sth that's been
> passed in has already had execute called on it (e.g. part of a multiple
> result loop). Removing the $sth->{Active} check (and ensuring Ima::DBI
> always calls prepare and not prepare_cached (I haven't figured out what's
> going on there yet) gets Class::DBI working with DBD::Sybase.
> 
> Our current code overrides db_Main which seems like an unnecessary hack
> to me, and confuses Class::DBI somewhat, triggering warnings.
> 
> I'm halfway down this rabbit hole and not really sure which turning to
> take now!

I'd take the view that $sth->{Active} shouldn't be true until after a
successful execute().

I'd happily take a doc patch that tightens up the docs in that direction.

Tim.

(Fwd) Issues with Oracle DBD in Cygwin

[Tim Bunce]

- Forwarded message from "Neargarder, Keith" 
 -

Date: Fri, 16 Sep 2016 22:16:11 +
From: "Neargarder, Keith" 
To: "'t...@cpan.org'" , "'byter...@cpan.org'" 
, "'yan...@cpan.org'" , 
"'mjev...@cpan.org'" 
Subject: Issues with Oracle DBD in Cygwin

   Gentlemen,



   Any assistance is greatly appreciated. I have been working on this for days 
now and am completely stuck.
   I cannot find a way past the error below. I have searched online and tried 
everything I have found and
   no luck. Wasn't sure if I should just create another Cygwin/DBD-Oracle post 
or what???



   Suggestions please???



   Let me know if you need more info. I tried to be as thorough as possible.



   Couple of things I have noticed that seem off to me and might be an issue???

   1.   after unpacking the instant client files into 
c:\oracle\instantclient_12_1 (set as ORACLE_HOME)
   I do NOT have a "lib" or "rdbms/lib" subdirectory - am I missing something? 
I have been using
   LD_LIBRARY_PATH=$ORACLE_HOME/lib and I see it and the rdbms/lib referenced 
in LD_RUN_PATH but those
   directories do not exist. If it were a problem I sure would expect some sort 
of error message but
   nothing jumps out at me???

   2.   I cannot connect to my target database with the sqlplus installed 
under instant client however
   I can connect using another Oracle client installed on the same server. 
Obviously this is an issue but I
   don't believe I am even getting to the point where my Perl script tries to 
connect as it cannot even
   load the Oracle.dll. With sqlplus I get the error: ORA-12154: TNS:could not 
resolve the connect
   identifier specified

   3.   Tried using the other Oracle client I have installed where sqlplus 
does work but still get the
   same error when trying to run my Perl script.

   4.   when running "perl Makefile.pl -V 12.1.0" one thing/difference that 
caught my eye is in my
   output it has a line "Generating a Unix-style Makefile" whereas I have seen 
on another Gygwin/DBD-Oracle
   post "Generating a GNU-style Makefile" - could this be an issue??? The make 
file seems to "work", runs
   to completion and creates all the targets, so I guess it is OK.



   Info:

   . Windows Server 2008 R2 SP1 64-bit OS

   . Cygwin Version 2.5.1

   . Perl 5.22.2 (Cygwin's Perl build, see output of "perl -V" below)

   . Oracle 12.1.0 (database on a Linux server if that matters; I am 
able to connect to it from
   this Windows server with sqlplus

   . Instant Client 12.1.0.2.0 - first I tried 64-bit, did not work 
then tried 32-bit when I
   remembered another 3^rd party software required 32-bit Oracle client; I 
downloaded the Instant client
   basic, SDK and sqlplus zip files and extracted them all into 
c:\oracle\instantclient_12_1

   . DBD-Oracle 1.74



   Environment variables:

   . ORACLE_DSN="DBI:Oracle:host=$my_host;sid=$my_sid"

   . ORACLE_USERID=$my_user/$my_pass

   . ORACLE_HOME=/cygdrive/c/oracle/instantclient_12_1

   . LD_LIBRARY_PATH=$ORACLE_HOME/lib



   Steps taken (as outlined in Cygwin.pod):

   5.   extracted the DBD-Oracle 1.74 package into my personal work 
directory
   /home/svb28/perl5/DBD-Oracle-1.74

   6.   cd /home/svb28/perl5/DBD-Oracle-1.74

   7.   generated the liboci.a using: dlltool --input-def oci.def 
--output-lib liboci.a

   8.   downloaded instant client packages and unpacked them into the 
c:\oracle\instantclient_12_1
   directory.

   9.   set environment variables (see above), tried ORACLE_USERID with and 
without the trailing
   "@$my_sid" as I have seen it both ways in different posts

   10.   ran: perl Makefile.pl (see output below)

   11.   ran: make (see output below)

   12.   tried make test but all those fail so not much use

   13.   ran: make install (see output below)

   14.   ran my own simple test.pl script to test out DBD-Oracle connection; 
tried variations of connect
   info but nothing works; using line as suggested in Cygwin.pod, sample 
connect line below(from test.pl
   line 152):

   $dbh = DBI->connect(

 "dbi:Oracle:host=$my_host;sid=$my_sid",

 $my_user, $my_pass);



   Error/output from test.pl:



   $ test.pl

   install_driver(Oracle) failed: Can't load
   
'/usr/lib/perl5/site_perl/5.22/x86_64-cygwin-threads/auto/DBD/Oracle/Oracle.dll'
 for module DBD::Oracle:
   Exec format error at /usr/lib/perl5/5.22/x86_64-cygwin-threads/DynaLoader.pm 
line 193.

   at (eval 35) line 3.

   Compilation failed in require at (eval 35) line 3.

   Perhaps a required shared library or dll isn't installed where expected

   at /home/svb28/util/test.pl line 152.



   perl -V output:



   $ perl -V

   Summary of my perl5 (revision 5 version 22 subversion 2) configuration:

Re: DBD::XML

[Tim Bunce]

On Sun, Sep 04, 2016 at 08:33:21AM -0400, Nigel Horne wrote:
> 
> On 4/9/16 05:56, Tim Bunce wrote:
> > > So here's an interesting one.  Any thoughts on this?  I assumed it all got
> > > pulled in magically, but I guess I'm missing something.  But what?
> > > 
> > > http://www.cpantesters.org/cpan/report/f9cbd816-7052-11e6-ab41-c893a58a4b8c
> > I've not listed DBI as a prerequisite, nor DBI::DBD::SqlEngine (which is
> > the first thing the module loads) so that test might have been running
> > with a funky older version.
> Thanks, Tim.  The problem is that seems to be the case with a lot of
> smokers, not just a few, so I think it's something in need of more
> attention.  I've added SQL::Statement to the pre-reqs since I saw that in
> some other code (I forget which now), I'll see if that helps.

Why not also add DBI::DBD::SqlEngine (0.06, I think) as a pre-req?
Seems appropriate since that's what the module 'use's.

Tim.

Re: DBD::XML

[Tim Bunce]

On Thu, Sep 01, 2016 at 01:53:52PM -0400, Nigel Horne wrote:
> So here's an interesting one.  Any thoughts on this?  I assumed it all got
> pulled in magically, but I guess I'm missing something.  But what?
> 
> http://www.cpantesters.org/cpan/report/f9cbd816-7052-11e6-ab41-c893a58a4b8c

I've not listed DBI as a prerequisite, nor DBI::DBD::SqlEngine (which is
the first thing the module loads) so that test might have been running
with a funky older version.

Tim.

Re: DBD::XML

[Tim Bunce]

On Sun, Aug 21, 2016 at 02:29:49PM -0400, Nigel Horne wrote:
> 
> > Anyway, back to the topic of naming... I'd suggest something like
> > DBD::XMLSimpleTable which would have a corresponding prefix of 'xmlst_'.
> 
> Not so sure about Table - how about DBD::XMLSimple?

Yeap. That seems fine. So DBD::XMLSimple and a prefix of xmls_.

Tim.

Re: DBD::XML

[Tim Bunce]

On Fri, Aug 19, 2016 at 10:09:45AM -0400, Nigel Horne wrote:
> On 8/19/16 9:56 AM, Tim Bunce wrote:
> > On Fri, Aug 19, 2016 at 09:30:32AM -0400, Nigel Horne wrote:
> > > 
> > > Apart from one change I need to make in terms of column names, I'm pretty
> > > much ready to start working on a 0.01 CPAN release.  It's read-only, but
> > > that's all I need.  How do I set about requesting driver registration, or 
> > > is
> > > this mentioning enough?
> > Probably :)
> > 
> > But I wonder about the name. "DBD::XML" seems to be a bold name,
> > implying that it's _the_ DBI interface for data stored in XML files.
> > Of course the same kind of issue applies to many other drivers,
> > so it's not a major concern, but does seem worth dicussing.
> 
> I'm more than happy to entertain other names if you have any suggestions.

I've some random questions and observations below...

> So, here's the example I've started with to get the code basic interface
> going and tested.  The code I have works with this trivial example.
> 
> data/person.xml:
> 
> 
> 
> 
> Nigel Horne
> n...@bandsman.co.uk
> 
> 
> A N Other
> nob...@example.com
> 
> 

Does that format ('table', 'row', 'id') correspond with a known XML Schema?

> use DBD::XML;

(Ideally users shouldn't need to use the driver module explicitly.)

> my $dbh = DBI->connect('dbi:XML(RaiseError => 1):');
> $dbh->func('person', 'XML', "$Bin/../data/person.xml", 'ad_import');  # to be 
> replaced with xml_import once the driver has been registered

I presume ad_import comes from DBD::AnyData. Is that 'inspired by',
or 'is a fork of', or 'using under the hood'?

> my $sth = $dbh->prepare("SELECT * FROM person");
> $sth->execute();
> 
> while (my $href = $sth->fetchrow_hashref()) {
> my $d = Data::Dumper->new([$href]);
> print "got data:\n", $d->Dump();
> }

($sth->dump_results can be handy for little example scripts.)

Are any other XML Schema supported, or supportable?

Is the XML and/or the parsed data loaded into memory or does each
$sth->fetch call pull the next chunk from the XML parser?
In other words, can it read files larger than the available memory?
(Not related to the naming, just curious :)

Tim.

Re: DBD::XML

[Tim Bunce]

On Fri, Aug 19, 2016 at 09:30:32AM -0400, Nigel Horne wrote:
> Ron,
> > > I've started working on a DBD::XML driver, and the first pass is looking
> > > good.  I'm doing this because of the demise of DBD::AnyData, and
> > > DBD::AnyData2 isn't ready yet, so until it is I wanted XML support.
> > > 
> > > Before I go any further, is anyone else working on something similar or
> > > is there something already out there?  I don't want to re-invent the
> > > wheel.
> > 
> > It sounds vaguely like my
> > https://metacpan.org/release/DBIx-Admin-BackupRestore.
> 
> Excellent, thanks so much for the pointer.  I'll take a look and see.
> 
> Apart from one change I need to make in terms of column names, I'm pretty
> much ready to start working on a 0.01 CPAN release.  It's read-only, but
> that's all I need.  How do I set about requesting driver registration, or is
> this mentioning enough?

Probably :)

But I wonder about the name. "DBD::XML" seems to be a bold name,
implying that it's _the_ DBI interface for data stored in XML files.
Of course the same kind of issue applies to many other drivers,
so it's not a major concern, but does seem worth dicussing.

Tim.

(Fwd) DBD::Oracle quote_identifier

[Tim Bunce]

- Forwarded message from "LAWS, MICHAEL H"  -

Date: Tue, 16 Aug 2016 21:04:20 +
From: "LAWS, MICHAEL H" 
To: "t...@cpan.org" 
Subject: DBD::Oracle quote_identifier

   Hello,



   I was using quote_identifier for table names on an oracle database.

   My code uses DBI, which implements the specifics in this case via 
DBD::Oracle.

   It appears to be using backticks which oracle throws an error when it sees 
an invalid character.

   Substituting all instances of backtick with the double quote, from oracle 
documentation,

   Causes it to work and is hopefully still more secure than not using quote 
functions at all.



   Am I missing something or would this be an open or previously closed issue?



   Thanks,

   Michael Laws

- End forwarded message -

Re: suppress quoting in prepared sql

[Tim Bunce]

I'd happily take a patch to the DBI docs to mention the common use case
described by this thread.

Tim.

On Tue, Apr 05, 2016 at 05:29:34PM +, Vaughan, Mark wrote:
> This works if the number of elements remains static. You'd have to run the 
> prepare again if the number of elements changes.
> 
> Mark Vaughan
> Neustar, Inc. / Lead Consulting Services Consultant, Professional Services
> 8532 Concord Center Drive, Englewood, CO 80112, USA
> Office: +1.303.802.1308  Fax: +1.303.802.1350  /  mark.vaug...@neustar.biz
> 
> 
> -Original Message-
> From: Paul DuBois [mailto:p...@snake.net] 
> Sent: Tuesday, April 05, 2016 11:25 AM
> To: Bruce Ferrell <bferr...@baywinds.org>
> Cc: dbi-users@perl.org
> Subject: Re: suppress quoting in prepared sql
> 
> 
> > On Apr 5, 2016, at 11:55 AM, Bruce Ferrell <bferr...@baywinds.org> wrote:
> > 
> > Ick!
> > 
> > ok, I have to dynamically build the IN clause of the prepare as a 
> > static sql statement
> 
> Yep. This is how I do it for a given array of values:
> 
> # Create a string of placeholder characters, with one ? character # per 
> element in an array of values.
> 
> my @values = (1, 2, 3, 4, 5);
> 
> my $str = join (",", ("?") x @values);
> 
> Then interpolate $str into your query string.
> 
> > 
> > On 4/5/16 9:32 AM, Vaughan, Mark wrote:
> >> >From the DBI documentation 
> >> >(https://urldefense.proofpoint.com/v2/url?u=https-3A__metacpan.org_p
> >> >od_DBI-23Placeholders-2Dand-2DBind-2DValues-29-3A=CwIF-g=MOptNlV
> >> >tIETeDALC_lULrw=rwT9R07bCzfhX6apOj8NoPX-TbEkSSLuFkjri49xQ-0=QpMl
> >> >4dk0ZSYHx2vhZSJDCeS1tdTQ9Z8GWCyZqgIjc28=2uZZNLLOkgh5xJfTn_SVli361r
> >> >ZOaGOrDxGPv_yVwd8=
> >> 
> >> Also, placeholders can only represent single scalar values. For example, 
> >> the following statement won't work as expected for more than one value:
> >> 
> >> "SELECT name, age FROM people WHERE name IN (?)"# wrong
> >> "SELECT name, age FROM people WHERE name IN (?,?)"  # two names
> >> 
> >> You may have to prepare the query each time unless you have a fixed number 
> >> of elements in the IN clause.
> >> 
> >> HTH,
> >> Mark Vaughan
> >> Neustar, Inc. / Lead Consulting Services Consultant, Professional 
> >> Services
> >> 8532 Concord Center Drive, Englewood, CO 80112, USA
> >> Office: +1.303.802.1308  Fax: +1.303.802.1350  /  
> >> mark.vaug...@neustar.biz
> >> 
> >> 
> >> -Original Message-
> >> From: Bruce Ferrell [mailto:bferr...@baywinds.org]
> >> Sent: Tuesday, April 05, 2016 10:24 AM
> >> To: dbi-users@perl.org
> >> Subject: suppress quoting in prepared sql
> >> 
> >> I'm generating a sql statement like this:
> >> 
> >> sth  = $mysql_dbh->prepare(
> >> "select sum(column) as columnSum from table where value in ( ? ) and 
> >> row_date between cast( ? as date) and cast( ? as date) ");
> >> 
> >> sth->execute( $ValueIDs ,$week_start_date,$week_end_date);
> >> 
> >> $ValueIDs is a series of unquoted values:
> >> 
> >> 01161,01162,01262,01147,01034,01125,01125,01017,01125,01278,01204,011
> >> 64
> >> 
> >> When observed at the mysql server, the sql appears as follows:
> >> 
> >> select sum(column) as columnSum where value in ( 
> >> '01161,01162,01262,01147,01034,01125,01125,01017,01125,01278,01204,01
> >> 164' ) and row_date between cast( '2016-03-29' as date) and cast( 
> >> '2016-04-05' as date)
> >> 
> >> resulting in no data being returned.
> >> 
> >> When the sql is manually entered as follows:
> >> 
> >> select sum(column) as columnSum where value in ( 
> >> 01161,01162,01262,01147,01034,01125,01125,01017,01125,01278,01204,011
> >> 64 ) and row_date between cast( '2016-03-29' as date) and cast( 
> >> '2016-04-05' as date)
> >> 
> >> The correct values are returned.
> >> 
> >> How can I suppress the quoting for the IN clause?
> >> 
> >>  
> > 
> 

(Fwd) perl DBI bug report

[Tim Bunce]

- Forwarded message from Dave Dyer  -

Date: Tue, 01 Sep 2015 12:40:00 -0700
From: Dave Dyer 
To: tim.bu...@pobox.com
Subject: perl DBI bug report


This query produces results with lots of trailing nulls
embedded in the "outcome" value.  I suppose this might
be expected to do something odd at the sql engine level,
but embedding null characters, which do not occur in any
of the elements, can't be correct.


   my $q = "select 
matchgroup.status,player,points,tournament,outcome,played,"
. " 
matchparticipant.comment,tournament_group,matchparticipant.uid"
. " from matchparticipant left join matchgroup "
. " on matchparticipant.tournament = 
matchgroup.uid and matchgroup.name = matchparticipant.tournament_group "
. " where matchid=$qm "
# note that because of the particular structure 
of this join query, $outcome seems to be padded with a lot of nulls 

. " union select 
matchgroup.status,-2,0,tournament,if(admin='winner',admin_winner,admin),played,"
. " matchrecord.comment,tournament_group,'A'"
. " from matchrecord left join matchgroup "
. " on matchrecord.tournament = 
matchgroup.uid and matchgroup.name = matchrecord.tournament_group "
. " where matchid=$qm "
. " order by uid ";
 

- End forwarded message -

Announce: DBI 1.634

[Tim Bunce]

  file: $CPAN/authors/id/T/TI/TIMB/DBI-1.634.tar.gz
  size: 595020 bytes
   md5: 4ad15a9c2cc9b68e3fe1f5cadf9cdb30

=head2 Changes in DBI 1.634 - 3rd August 2015

Enabled strictures on all modules (Jose Luis Perez Diez) #22
Note that this might cause new exceptions in existing code.
Please take time for extra testing before deploying to production.
Improved handling of row counts for compiled drivers and enable them to
return larger row counts (IV type) by defining new *_iv macros.
Fixed quote_identifier that was adding a trailing separator when there
was only a catalog (Martin J. Evans)

Removed redundant keys() call in fetchall_arrayref with hash slice (ilmari) 
#24
Corrected pod xref to Placeholders section (Matthew D. Fuller)
Corrected pod grammar (Nick Tonkin) #25

Added support for tables('', '', '', '%') special case (Martin J. Evans)
Added support for DBD prefixes with numbers (Jens Rehsack) #19
Added extra initializer for DBI::DBD::SqlEngine based DBD's (Jens Rehsack)
Added Memory Leaks section to the DBI docs (Tim)
Added Artistic v1  GPL v1 LICENSE file (Jose Luis Perez Diez) #21

=cut

Enjoy!

Tim.

Please test DBI-1.633_91 - recently uploaded to CPAN

[Tim Bunce]

I've uploaded DBI-1.633_91 to CPAN.

It contains a couple of experimental changes I'd like feedback on:
https://metacpan.org/changes/release/TIMB/DBI-1.633_91

Enabled strictures on all modules (Jose Luis Perez Diez) #22
Note that this might cause new exceptions in existing code.
Please take time for extra testing before deploying to production.

Improve handling of row count on 32bit int systems, add sanity check warn
Relevant if perl -V:'i(nt|v)size' reports different sizes
and either you execute statements that might affect =2,147,483,648 
rows.

Please build it, install it, test it, and let us know how it goes.

cpanm https://cpan.metacpan.org/authors/id/T/TI/TIMB/DBI-1.633_91.tar.gz

Thanks!

Tim.

Re: Potential dbi memory leak.

[Tim Bunce]

On Thu, May 28, 2015 at 03:59:51PM +1200, Duncan McEwan wrote:
 On Tue, 26 May 2015 14:13:05 +0100 Tim Bunce tim.bu...@pobox.com wrote:
 
  I've added this as a note:
  
  Note that the ChildHandles array holds weak references and that 'from
  time to time' the old slots get freed up. This isn't a leak, it just
  appears to be if you're not familiar with the caching that DBI does
  internally. You can rest assured that if the DBI did have a real leak
  a) a great many people would be affected and b) it would get fixed very
  quickly.
 
  I think 'from time to time' is every 120 or so newly created child handles.

 But seeing this response from Tim about the fact that the DBI can cache up to
 120 or so handles made me wonder if this is true for database handles as well
 as statement handles?  Is it possible that our problem was simply the
 correctly working DBI caching misbehaving due to our application running
 persistently in multiple fcgid processes.

Short answer: no.

When a new handle is created the DBI simply pushes a weak reference to
the handle onto the end of the ChildHandles array.

Because it's a weak reference it doesn't interfere with the handle
getting destroyed when the last (non weak) reference is removed.

When the handle is destroyed, the corresponding element in the
ChildHandles array is set to undef by the weak reference mechanism
deep in the perl internals. The DBI isn't involved in that.

The apparent 'leak' is because the element in the array still exists,
albeit as an undef. The DBI clears out the undef elements occasionally to
prevent the array growing indefinitely.

Tim.

Re: Potential dbi memory leak.

[Tim Bunce]

I've added this as a note:

Note that the ChildHandles array holds weak references and that 'from
time to time' the old slots get freed up. This isn't a leak, it just
appears to be if you're not familiar with the caching that DBI does
internally. You can rest assured that if the DBI did have a real leak
a) a great many people would be affected and b) it would get fixed very 
quickly.

I think 'from time to time' is every 120 or so newly created child handles.

Tim.

On Tue, May 26, 2015 at 07:57:53AM -0300, Steve Cookson - gmail wrote:
 It seems to be further documented here, together with a solution:
 
 http://stackoverflow.com/questions/13338308/perl-dbi-memory-leak,
 
 But the solution does not seem to be reliable.  Sometimes it works sometimes
 not.
 
 I'll update you when I know more.
 
 Regards,
 
 Steve.
 
 On 26/05/15 07:07, Steve Cookson - gmail wrote:
 Hi Guys,
 
 You may have seen part of this post on PerlMonks.  If so apologies for the
 duplication.  This started off as a general search for leaks in my code,
 and resulted in a few hits, one of which was attached to every database
 access.
 
 A simple select ATT_RECORD_NAME_TXT from TBL_TEST;  results in the leak
 of one scalar value.  It seems to be attached to the -prepare statement.
 
 At first I assumed it was down to my Firebird driver, which is relatively
 new, so I switched the driver to ODBC::Firebird, with the same result.
 Finally I changed to mysql and again got a memory leak.  The only thing I
 can assume is that either my code is generically wrong (and I hope this is
 the case), or there is a leak in dbi, which I would be surprised by.
 
 I would appreciate some advice.
 
 Test code follows.  Please install Devel::Leak to pick up leaked scalars
 and update the dsn to the dsn of your choice.
 
 Thanks for your help.
 
 Regards,
 
 Steve.
 
 #! /usr/bin/perl
 
 package main;
 use strict;
 use warnings;
 use DBI;
 #use DBD::Firebird;
 use DBD::ODBC;
 use Devel::Leak;
 my $handle;
 my $count_start;
 my $count_stop;
 my $gl_dbh;
 
 # Just do this 5 times to make sure there is no contribution to
 $handle count from Devel::Leak
 for (1..10){
 print Handle init: , Devel::Leak::NoteSV($Launch::handle),\n;
 }
 #my $loc_dsn = DSN;
 #dbi:ODBC:Driver=Firebird;Dbname=/home/image/Documents/Endoscopia/DB/newEndo.fdb;
 
 #ib_dialect=3;
 #DSN
 my $loc_dsn = DSN;
 DBI:mysql:database=new_schema_test;
 host=localhost;
 port=3306;
 DSN
 $Launch::gl_dbh=DBI-connect($loc_dsn,root,password, {
 PrintError = 1,# Report errors via warn
 RaiseError = 1# Report errors via Die
 }
 ) or die;
 
 my @loc_sql_string =();
 $loc_sql_string[0]=CREATE TABLE TBL_TEST_LEAK ( ATTR_RECORD_ID_TXT
 VARCHAR(10) NOT NULL, ATT_RECORD_NAME_TXT VARCHAR(255), CONSTRAINT
 PK_TBL_TEST_LEAK PRIMARY KEY (ATTR_RECORD_ID_TXT) ); ;
 $loc_sql_string[1]=GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON TBL_TEST_LEAK TO  SYSDBA WITH GRANT OPTION;
 $loc_sql_string[2]=INSERT INTO TBL_TEST_LEAK (ATTR_RECORD_ID_TXT,
 ATT_RECORD_NAME_TXT) VALUES ('206', 'Delay Test 1' );
 $loc_sql_string[3]=select ATT_RECORD_NAME_TXT from TBL_TEST_LEAK; ;
 $loc_sql_string[4]= $loc_sql_string[3];
 $loc_sql_string[5]= $loc_sql_string[3];
 $loc_sql_string[6]= $loc_sql_string[3];
 $loc_sql_string[7]= $loc_sql_string[3];
 $loc_sql_string[8]= $loc_sql_string[3];
 $loc_sql_string[9]=drop table TBL_TEST_LEAK; ;
 
 for (my $i=1;$i=9;$i++){
 $count_start=Devel::Leak::NoteSV($Launch::handle);
 print DBD start: , $count_start,\n;
 print $loc_sql_string[$i], \n;
 dbd_select($loc_sql_string[$i]);
 # You can use
 #$count_stop=Devel::Leak::CheckSV($Launch::handle);
 $count_stop=Devel::Leak::NoteSV($Launch::handle);
 print Handle stop: , $count_stop,\n;
 print Count difference: , $count_stop-$count_start,\n;
 }
 $Launch::gl_dbh-disconnect;
 
 sub dbd_select{
 my $loc_sql_string=shift;
 my $loc_sth=$Launch::gl_dbh-prepare($loc_sql_string) or die;
 #$loc_sth-execute() or die;
 #$loc_sth-finish();
 return;
 }
 
 1;
 
 
 

Re: Making DBD::Pg to return Postgres arrays as Perl arrays

[Tim Bunce]

On Thu, May 07, 2015 at 01:30:45PM +0200, Adam Sjøgren wrote:
 I wrote:
 
  When I insert new rows where the field has a Perl-array as the value,
  DBIx::Class+DBD::Pg automatically stores them in the database, as hoped
  - nice!
 
  But when I read the field again, I get the textual Postgres-encoded
  representation back (i.e. a string like '{(a,b,c),(d,e,f)}').
 
 After looking more closely on this:
 
 Simple arrays work out of the box, i.e. text[] I can read/write and
 values are automatically converted from Perl arrays when writing to the
 database, and converted back to Perl arrays when reading.
 
 The problem I have is only when my datatype is a (custom) tuple.
 
 I.e. in Postgres I have:
 
   CREATE TYPE entry AS (tag TEXT, created TIMESTAMP, notes TEXT, initials 
 TEXT);
   ALTER TABLE vehicle ADD COLUMN entrylist entry ARRAY;
 
 I would like to be able to do:
 
   $schema-resultset('Vehicle')-create({ entrylist=[
   [ 'a', '2015-05-07', 
 'info', 'adsj' ],
   [ 'b', '2015-05-07', 
 'more', 'adsj' ],
  ] });

Take a look at https://metacpan.org/pod/PGObject::Type::Composite

Tim.

Re: May CPAN Pull request chalenge

[Tim Bunce]

On Fri, May 01, 2015 at 06:34:58PM +0200, jl...@escomposlinux.org wrote:
 Hi Tim,

Hi!

 I have got as a May a assignment your module DBI.

Congratulations :)

 In Neil's message he sugested 3 areas for the pull request:
 
 Notes on your distribution:
 
  * It has a file 'test.pl' in the top directory.
The modern convention is for all tests to live in a 't/' directory.

test.pl is more of a manual testing tool - you'll see it has a bunch of
options for command line use. It could simply be renamed to tester.pl :)

  * The distribution has CPAN Testers failures, so you could look at those:
http://www.cpantesters.org/distro/D/DBI.html

I find http://matrix.cpantesters.org/?dist=DBI+1.633 easier to read.
Most of the failures are for obscure systems like mirbsd and dragonfly
that, I'd guess, you don't have access to. So those would be tricky.
If you have access to Windows then investigating those failures might be
useful.

  * The distribution has CPANTS warnings - check out its page for details:
http://cpants.cpanauthors.org/dist/DBI

Addressing the 'use strict' and 'use warnings' metrics seems reasonable
at first sight. (I'm less keen on 'consistent version' though.)
Adding a LICENSE file would also be good.

 I would like to take the first suggestion first but do you like me to look
 on other issues?

Feel free to scan the issues in RT and see if any appeal.

 Must I test the changes in all perl releases since 5.008 or using the ones
 available on perlbrew will be enough?

perlbrew versions are fine.

 Thanks for your time

Thank you! Feel free to email or ping me or others on IRC.

Good luck!

Tim.

Re: DESTROY ignored for outer handle

[Tim Bunce]

Your original mail referred to DBI errors yet there are no DBI errors.

I suggest you ignore the DBI trace and focus on reproducing whatever
problem you're having and explore how it's affected by changing things
like the number of backend worker processes. Aim to come up with a clear
and precise statement of the problem behaviour, the circumstances, and
what you've explored changing and what effect it had.

Post again here if you find there is a problem related to the DBI.
Otherwise perhaps a more general web development forum would be more help.

Good luck.

Tim.

On Wed, Apr 29, 2015 at 12:22:46AM +0300, Nikos Vaggalis wrote:
Hi Tim and thanks a lot for replying
I have RaiseError enabled,I don't see anything else.Is it ok if I send you 
 the whole log file just to
take a quick look?
Should Mysql be setup with the with-thread-safe-client switch enabled?
thanks
On Tue, Apr 28, 2015 at 8:09 PM, Tim Bunce [1]tim.bu...@pobox.com wrote:
 
  On Tue, Apr 28, 2015 at 09:16:48AM +0300, Nikos Vaggalis wrote:
  
  Now,when in the same session, the error does not occur no matter 
 how much I
  stress it. When opening another 4-5 sessions,or doing a couple of 
 reloads
  of the same session, the following DBI error comes up :
 
  I don't see any errors in that log. I suggest you enable RaiseError, if
  you haven't already, then any errors will be obvious.
 
  (DESTROY ignored for outer handle is an internal detail that's almost
  certainly irrelevant to whatever problem you're having.)
  Tim.
 
 References
 
Visible links
1. mailto:tim.bu...@pobox.com

Re: DESTROY ignored for outer handle

[Tim Bunce]

On Tue, Apr 28, 2015 at 09:16:48AM +0300, Nikos Vaggalis wrote:
 
Now,when in the same session, the error does not occur no matter how much I
stress it. When opening another 4-5 sessions,or doing a couple of reloads
of the same session, the following DBI error comes up :

I don't see any errors in that log. I suggest you enable RaiseError, if
you haven't already, then any errors will be obvious.

(DESTROY ignored for outer handle is an internal detail that's almost
certainly irrelevant to whatever problem you're having.)

Tim.

Re: DBD::ODBC Destroy bug?

[Tim Bunce]

On Wed, Apr 01, 2015 at 03:20:31PM +0100, Martin J. Evans wrote:
 
 However, having said all that I didn't see you were using freeTDS
 until the last email. I've seen an issue with freeTDS returning and
 error status and then not supplying the reason before.
 
 I have to say at this point I'm pointing a finger at your driver as
 whatever, if it returns SQL_ERROR from SQLFreeHandle it should tell us
 the error when we call SQLError. However, your ODBC log is strange as
 it does not show any SQLFreeHandle call failing (and it does not look
 long enough for what you do in your script).
 
 The only bit I'm not too sure about (I doubt has anything to do with
 your problem) is that DBD::ODBC does the following at the end of
 dbd_st_destroy

 if (imp_dbh-hdbc != SQL_NULL_HDBC  !PL_dirty) {
 
 rc = SQLFreeHandle(SQL_HANDLE_STMT, imp_sth-hstmt);
 
 if (DBIc_TRACE(imp_sth, DBD_TRACING, 0, 5))
 TRACE1(imp_dbh, SQLFreeHandle(stmt)=%d\n, rc);
 
 if (!SQL_SUCCEEDED(rc)) {
 dbd_error(sth, rc, st_destroy/SQLFreeHandle(stmt));
 /* return 0; */
 }
 }
 
 DBIc_IMPSET_off(imp_sth); /* let DBI know we've done it   */
 
 In this case, when no error can be found by dbd_error it does:
 
 DBIh_SET_ERR_CHAR(
 h, imp_xxh, Nullch, 1,
 Unable to fetch information about the error, HY000, 
 Nullch);
 
 but of course then drops into DBIc_IMPSET_off(imp_sth) and returns which 
 might not be ok. I don't know if Tim could comment on this.

DBIc_IMPSET_off is how the driver indicates that it's done all the
cleanup it can. The current code seems reasonable.

Tim.

 So, in summary, I cannot reproduce your error, I've seen freeTDS error and 
 fail to tell us the error before, your code works flawlessly with the 
 Easysoft and MS ODBC drivers and your ODBC log contradicts the DBI/DBD::ODBC 
 log so I'm going to suggest you've updated or changed your freeTDS driver and 
 this one is broken.
 
 Martin
 
 
 On Tue, Mar 31, 2015 at 4:23 AM, Martin J. Evans martin.ev...@easysoft.com 
 mailto:martin.ev...@easysoft.com wrote:
 
 On 31/03/15 06:04, Joel Plotkin wrote:
 
 Hi,
 
 I've attached the sample test8.pl http://test8.pl 
  http://test8.pl script (smallest one possible that creates the problem) 
  and a trace file at level 15.
 
 Thanks for any insight,
 
 Joel
 
 
  -dbd_st_execute(ac3cb0)=-1
  - execute= -1 at test8.pl http://test8.pl line 74 via  at 
  test8.pl http://test8.pl line 53
  - DESTROY for DBD::ODBC::st (DBI::st=HASH(0xac3818)~INNER) 
  thr#974010
  SQLFreeHandle(stmt)=-1
  !!dbd_error2(err_rc=-1, what=st_destroy/SQLFreeHandle(__stmt), 
  handles=(c2abd0,c2b1c0,c802c0)
  ** No error found -1 **
  !! ERROR: 1 'Unable to fetch information about the error' 
  (err#1)
  - DESTROY= undef at test8.pl http://test8.pl line 54 via  at 
  test8.pl http://test8.pl line 54
  !! ERROR: 1 CLEARED by call to fetchall_arrayref method
 
 This is suspicious - SQLFreeHandle failed and then the error was cleared.
 
 I cannot reproduce and we need further clues.
 
 Instead of starting tracing in the script could you rerun with 
  DBI_TRACE=DBD=x.log
 
 e.g.,
 set DBI_TRACE=DBD=x.log
 perl myscript.pl http://myscript.pl
 
 This will put in the x.log file a load of ODBC info for the driver etc - 
  could you send me the whole log file.
 
 Another thing well worth doing is enabling tracing at the ODBC level as 
  then we can try and find out why SQLFreeHandle is failing. You can do this 
  by going to the ODBC administrator (make sure you pick the right one 32 bit 
  or 64 bit depending on what your perl is) and selecting the tracing tab, 
  enter a file and click on start then run your script.
 
 Martin
 
 
 

Re: Perl 6 and DBI

[Tim Bunce]

On Thu, Feb 05, 2015 at 12:23:40AM -, Greg Sabino Mullane wrote:
 
 As you may have heard, Larry Wall gave a speech recently declaring 
 the goal of releasing Perl 6 this year, 2015. Honestly, there 
 is little chance of me using Perl 6 until it has a good, working 
 DBI. Anyone know the state of things with DBI and Perl6? Is the 
 goal still to implement what is basically DBI v2, or perhaps 
 someone is working on a simple port of the existing DBI? Is 
 a working DBI even on their list of blocker features for a release?

On MoarVM the perl5 DBI can be accessed via the Inline::Perl5 module.
That probably counts as a reasonable working DBI :)

Back in 2007 I said this:
http://www.slideshare.net/Tim.Bunce/dbi-for-parrot-and-perl-6-lightning-talk-2007

Then in 2010 I said this:
http://www.slideshare.net/Tim.Bunce/perl6-dbdi-yapceu-201008

I still think that's the right way to go, for all the reasons expressed
in the slides.

What's missing is a team of people with the right skill willing to work
on it.  I've had little time to do more than the tinkering I've already
done and I'm severely hampered by knowing ~zero perl6 or Java.

Volunteers are most welcome to express interest on the dbi-dev mailing list.

Tim.

Re: Escaping placeholders, take 2

[Tim Bunce]

On Fri, Jan 09, 2015 at 02:54:56PM -, Greg Sabino Mullane wrote:
 
 Just a heads up to this list - support for backslash-escaped placeholders 
 is now implemented in the current production version of DBD::Pg, 3.5.0
 
 http://search.cpan.org/dist/DBD-Pg/
 
 http://cpansearch.perl.org/src/TURNSTEP/DBD-Pg-3.5.0/Changes

Many thanks Greg.

Tim.

Re: make test fails installing DBI-1.632 (linux, perl5.8.8)

[Tim Bunce]

On Wed, Jan 07, 2015 at 04:50:52PM -0500, John Wiersba wrote:
 
 I see in META.{json,yaml} that a later version of Test::Simple is
 required, but that doesn't seem to cause a noticable error message or
 abort the Makefile.PL/make/make test process.  Installing the latest
 version of Test::Simple allows make test to succeed.

So there's no problem now? Or did that just fix t/06attr.t?

Tim.

Re: Escaping placeholders, take 2

[Tim Bunce]

On Wed, Dec 31, 2014 at 03:55:42AM -, Greg Sabino Mullane wrote:
 
 Tim Bunce wrote:
  Wherein I attempt to summarize the requirements, the background, the 
  options,
  the risks, and the counter-proposal ...
 
 Thanks for taking the time to write all this out in a coherent manner.

You're welcome. I find the effort usually greatly clarifies my thinking.

 I may once again assume the role of devil's advocate herein.

Thanks.

  In modern application development, however, the DBI is just another
  foundation layer, low down in the stack. Above it are modules like
  SQL::Abstract, and ORMs like DBIx::Class. Companies have large and
  growing investments in these stacks, plus the modules they've built over
  them to provide abstraction and encapsulation of business logic.
 
 Agreed.
 
  For these large applications the attribute workarounds are rarely helpful.
 
 Is that a true generalization? Or is it only because these large 
 applications have not bothered to support the attributes?

Supporting attributes means supporting (non-standard) numbered placeholders.
Supporting numbered placeholders is, as previously described, very far
from trivial for SQL::Abstract and thus DBIx::Class.  (I certainly
wouldn't characterize this as not bothered.)


  +Some drivers allow you to prevent the recognition of a placeholder by 
  placing a
  +single backslash character (C\) immediately before it. The driver will 
  remove
  +the backslash character and ignore the placeholder, passing it unchanged 
  to the
  +backend. If the driver supports this then L/get_info(9000) will return 
  true.
 
 Seems a good start. I don't like the phrase prevent recognition of a 
 placeholder 
 but it's too late at night to propose an alternate right now.

s/prevent/disable/?


  Which likely means that SQL::Abstract and/or DBIx::Class will need to
  know as well. (Unless they expose the DBD directly to the user, which
  ruins the point a bit).
 
  Not true.
 
 How will a user of SQL::Abstract know if escapes are supported or not? 
 Isn't the job of such frameworks to release the user from such worries, 
 or at least present some sort of attribute the user can rely on?

SQL::Abstract isn't (just) about portability. It allows SQL conditions
to be expressed and composed via Perl data structures. That's very
powerful functionality. Users who want to use a postgres ? operator know
they're using DBD::Pg and can simply require the appropriate version.


  Question marks are the international standard for placeholders.
  The DBI should have specified a way to escape them from the start.
  I'm attempting to fix that now.
 
 Taking off the devil's advocate hat now. I can agree with this 
 position and the backslash solution.

Great. Thanks Greg.

  To demonstrate the relative complexity, here's a proof-of-concept patch
  for DBD::Pg to implement support for escaping question mark placeholders:
 
  https://github.com/timbunce/dbdpg/commit/54358c7a7efeeaf2666c5e28c301e47624fb9615
 
 +1, thanks for that. I certainly cannot see any harm in adding escape support 
 to DBD::Pg, and then letting the upstream modules start adapting to it.

Wonderful. I'll work on polishing up the patch.

Tim.

Escaping placeholders, take 2

[Tim Bunce]

would, as always, be free to implement this placeholder escaping feature
if and when they wish.


** Counter-proposal

On Sun, Dec 21, 2014 at 03:17:39PM -, Greg Sabino Mullane (the tenth man) 
wrote:
 
 I'm going to play devil's advocate a bit here. There are some problems with 
 this approach. First, it will require that the user know if the underlying 
 DBD supports backslashes.

Of course. This is no different from the developer requiring to know that
the underlying DBD supports any other feature that the developer relies on.

For most cases the familiar dependency management mechanisms work fine:
use DBD::Foo '3.456';
There's also the get_info call, but I doubt that'll be needed in practice.


 Which likely means that SQL::Abstract and/or DBIx::Class will need to
 know as well. (Unless they expose the DBD directly to the user, which
 ruins the point a bit).

Not true.


 So we will have both escaped and unescaped versions of SQL 
 floating around, subject to the whims of whether or not your particular 
 DBD supports it (and in which version).

There's no such thing as escaped and unescaped versions of SQL.
That's a false premise.  This is a really important point: the syntax
being proposed *is invalid SQL*.

Exactly the same approach has been used to safely extend other langauges.
For example the '(?' sequence was invalid in early Perl 5 regexps which
allowed Larry to adopt it to enable new features, like (?:foo)


 Which leads to my counter-proposal: have SQL::Abstract accept
 dollar-number placeholders.

s/accept/generate/.

I thought this might be workable and had looked at the SQL::Abstract
source code a few times but didn't think it an attractive option.
(Partly because of the complexity and performance cost, and partly
because it just seems like a poor approach to the issue.  Relying on the
fact that some drivers support non-standard placeholder styles seems to
be an inelegant fudge to me.  It would be pushing complexity and cost
into code that simply shouldn't need to care.)

Then ribasushi, a principle maintainer of SQL::Abstract and DBIx::Class,
pointed out the fatal flaw: numbered placeholders aren't directly composable.

It's easy to pass around a fragment of SQL containing question mark
placeholders along with a list of values to bind to those placeholders.
These fragments can be directly composed into a larger SQL statement
with the corresponding bind values simply pushed onto a list.

The as_query() method in DBIx::Class is a good example use-case. It returns

\[ $sql_with_placeholders, @binds ]

and that form of 'SQL fragment' is pervasive in the public API. E.g. see
https://metacpan.org/pod/distribution/DBIx-Class/lib/DBIx/Class/Manual/Cookbook.pod#Subqueries

For example:

$resultset1-search_rs({
foo = { -in = $resultset2-as_query },
bar = { -not_in = $resultset3-as_query },
...
})-all;

The $resultset1/2/3 variables could represent different complex
multi-table joins with many conditions and many bind variables.
This is a very powerful feature of DBIx::Class, and it works well.

Trying to do the same with numbered placeholders (colon or dollar) would
require parsing and rewriting of the SQL.  Clearly non-trivial and slow.
For this reason he wouldn't accept such a patch to SQL::Abstract, and I'd
agree with that position.


Question marks are the international standard for placeholders.
The DBI should have specified a way to escape them from the start.
I'm attempting to fix that now.


To demonstrate the relative complexity, here's a proof-of-concept patch
for DBD::Pg to implement support for escaping question mark placeholders:

  
https://github.com/timbunce/dbdpg/commit/54358c7a7efeeaf2666c5e28c301e47624fb9615

You can see that it's just 8 lines of simple code. (Naturally I'll add tests
and docs etc. to this before sending a pull request.)


Tim

p.s. When replying, please edit your replies to only quote the relevant
parts of this (very long) email.

Re: Escaping placeholders (was: [Dbix-class] Using Postgres JSONB operators in queries)

[Tim Bunce]

Sometime over the holiday period I hope to write up a summary of the
arguments for and against and suggest ways forwards.

Tim.

Re: Escaping placeholders

[Tim Bunce]

On Sat, Dec 20, 2014 at 02:23:43PM -0800, Jonathan Leffler wrote:
 
  INSERT INTO SomeTable(DateTimeCol)
  VALUES(DATETIME[1](2014-12-31 23\:59\:59) YEAR TO SECOND);
 
I really won't want people have to futz with their legitimate Informix SQL 
 in order to pass it through
DBD::Informix.

Whatever is provided, whether by DBI or DBD::Informix, must accept the 
 code without the
backslashes in front of the colons.  It is simply not acceptable to have 
 to modify valid SQL to get it
past the gatekeeper code.

Understood. I wouldn't expect DBD::Informix to enable this by default.

We were only discussing a hypothetical situation where DBD::Informix
could optionally enable use of colon placeholders, if desired.

At the moment, the unescaped code works fine.  It will continue to work 
 fine.  As long as DBI does not
break the currently working code, I will survive �� like I have for the 
 last decade and more.  Just
make sure that whatever you do does not break working valid Informix SQL 
 code.

I have absolutely no intention of breaking anything :)

It'll be up to the individual driver authors to add support for escaping
placeholders, if they want to.

(The DBI has a built-in preparse function that's intended for parsing
placeholders but few, if any, drivers use it. I know DBD::Informix doesn't.)

Tim.

On Sat, Dec 20, 2014 at 2:17 PM, Tim Bunce [2]tim.bu...@pobox.com wrote:
 
  On Sat, Dec 20, 2014 at 01:14:29PM -0800, Jonathan Leffler wrote:
  Many, many years ago, DBD::Informix had to give up on the 
 DBI-provided parsing for placeholders
  because
  there were too many contexts in which it was wrong for Informix.  
 It may have improved since
  then, but:
  
INSERT INTO SomeTable(DateTimeCol)
  VALUES(DATETIME(2014-12-31 23:59:59) YEAR TO SECOND);
 
  I think I raised this as an issue back in the 1996-1998 timeframe 
 (I said 'many years ago' and
  meant
  it).  I'd have to dig through my release notes to be more precise.  
 Informix only supports
  natively the
  `?` placeholders.  It doesn't yet have the complexities introduced 
 by the PostgreSQL operators.
  
  I don't know whether this can be handled at all.  It may be that 
 DBD::Informix has to stay out in
  isolation but it would be nice if it wasn't necessary.
 
  The `?` placeholders are 'standard' (for some definition) so 
 DBD::Informix
  isn't really 'in isolation'. There are quite a few drivers that only
  support `?` placeholders.
 
  In theory, if this proposal goes ahead, and is applied to `:` 
 placeholders
  as seems likely, then you'd be able to write the above as:
  INSERT INTO SomeTable(DateTimeCol)
VALUES(DATETIME[3](2014-12-31 23\:59\:59) YEAR TO SECOND);
 
  Tim.
 
--
Jonathan Leffler [4]jonathan.leff...@gmail.com  #include disclaimer.h
Guardian of DBD::Informix - v2013.0521 - [5]http://dbi.perl.org
Blessed are we who can laugh at ourselves, for we shall never cease to be 
 amused.
 
 References
 
Visible links
1. file:///tmp/tel:%282014-12-31%2023
2. mailto:tim.bu...@pobox.com
3. file:///tmp/tel:%282014-12-31%2023
4. mailto:jonathan.leff...@gmail.com
5. http://dbi.perl.org/

Re: Escaping placeholders

[Tim Bunce]

On Sun, Dec 21, 2014 at 10:27:18AM +0100, pe...@vanroose.be wrote:
 On 20.12.2014 15:38, Tim Bunce wrote:
  [...]
  Can you, or anyone else, think of any situation where a backslash before
  a ? or :foo (or even $1) style placeholder might be valid SQL?
 
 Inside quoted text: of course, yes.
 Outside quoted text: maybe some RDBMS accept a backslash as a valid character
 in the name of a table or column? (Haven't tried this out yet.)
 At least make sure \? will not be handled as placeholder inside column name
 quoting, as in e.g.
 foo \? bar

That's standard identifier quoting so should be ignored by the driver
for the same reason that single quoted strings are.

 or in
 [foo \? bar]
 with SQL Server, or
 `foo \? bar`
 for MySQL.

For databases that support those non-standard identifier quoting styles
the driver should be treating them as strings and so skipping them anyway.

 But my fear is that even
 foo\?bar
 would be accepted as valid column or table name by some RDBMS...

I find that very hard to believe without any evidence.
Even if true, the driver for such a database would, I presume,
treat that ? as a placeholder and so it would already be broken.

Tim.

Re: Escaping placeholders

[Tim Bunce]

Thanks David :)

Tim.

On Fri, Dec 19, 2014 at 12:23:23PM -0600, David Nicol wrote:
Please disregard my previous. After reading Tim Bunce's earlier response 
 and thinking about this some
more, although backslash escaping can be tricky, that is how Perl does 
 these things, and Perl coders are
familiar with the nuances of when they must be doubled to get through 
 quoting. Doubling of syntax
characters is a database thing, but it would have to be revised for every 
 new escapable character.
Backslash escaping can be embraced once and will continue to work in 
 potentialfutures where other
significant characters (aside from colon and question mark) might need to 
 be escaped too.
On Fri, Dec 19, 2014 at 10:27 AM, David Nicol [1]davidni...@gmail.com 
 wrote:
 
  I think the suggestion of making ::(\w+) become :$1 and exempting that 
 from placeholder recognition
  seems like a complete winner and DBD maintainers could do that right 
 away, and by do that I mean
  accepting, applying, and redistributing patches..
 
 References
 
Visible links
1. mailto:davidni...@gmail.com

Re: Escaping placeholders

[Tim Bunce]

On Fri, Dec 19, 2014 at 01:12:16PM +0100, Alexander Foken wrote:
 Hello all,
 
 this reminds me of a similar problem I had in 2000 with DBI,
 DBD::Oracle, and Oracle. See
 http://marc.info/?t=9506395904r=1w=2,
 http://173.79.223.25/?l=dbi-devm=95077716125217w=2.
 
 Problem was using named placeholders (:foo) in DBI and at the same
 time use PL/SQL code containing variables (:bar), DBI considered
 both :foo and :bar to be placeholders instead of leaving :bar
 alone and pass it to Oracle. A set of patches from Michael A. Chase
 allowed disabling parts or all of the placeholder parsing, so using
 unnamed placeholders (?) allowed using PL/SQL variables in SQL
 statements.
 
 But the fundamental problem was not solved, there was and still is
 no way to escape placeholders.

Can you, or anyone else, think of any situation where a backslash before
a ? or :foo (or even $1) style placeholder might be valid SQL?

So far no one has come up with one, so I'm getting more comfortable
with the idea that a backslash before a placeholder is a safe change.
I.e., there's a near-zero risk that upgrading a DBI driver to support
backslashes would cause breakage in existing code.

Tim.

Re: Escaping placeholders

[Tim Bunce]

On Sat, Dec 20, 2014 at 05:35:55PM +0100, Alexander Foken wrote:
 On 20.12.2014 15:38, Tim Bunce wrote:
 Can you, or anyone else, think of any situation where a backslash before
 a ? or :foo (or even $1) style placeholder might be valid SQL?
 
 I found two situations for PostgreSQL:
 
 (1) PostgreSQL allows almost any character as escape character in
 Unicode string constants 
 (http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-UESCAPE).
 With that, I can construct  an expression containing \:foo that is
 valid SQL as understood by PostgreSQL:
 
 U'foo\:bar' UESCAPE ':'
 
 This expression represents the string foo\Xbar, where X is the
 Unicode character U+ (TAI VIET LETTER LOW VO).

I don't think that'll be a problem because the driver code that parses
the statement looking for placeholders will skip over quoted strings.

 (2) PostgreSQL also allows Dollar quoting 
 (http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING).
 With that, I can construct an expression containing \$1 that is
 valid SQL as understood by PostgreSQL:
 
 $1$foo\$1$
 
 This expression represents the string foo\, quoted by dollar signs
 using the character 1 as tag.

I'm not sure if the driver code that parses statements in DBD::Pg
handles dollar quoting. I presume so. In which case this shouldn't be a
problem either for the same reason as above.

 So far no one has come up with one, so I'm getting more comfortable
 with the idea that a backslash before a placeholder is a safe change.
 I.e., there's a near-zero risk that upgrading a DBI driver to support
 backslashes would cause breakage in existing code.
 
 Do you plan to escape the escape character, i.e. use a double
 backslash at DBI level to represent a single backslash at database
 level?

That's a good question. I'm not sure. I think the answer has to be no.
I'd welcome any input on that.

Tim.

Re: Escaping placeholders

[Tim Bunce]

On Sat, Dec 20, 2014 at 01:14:29PM -0800, Jonathan Leffler wrote:
Many, many years ago, DBD::Informix had to give up on the DBI-provided 
 parsing for placeholders because
there were too many contexts in which it was wrong for Informix.  It may 
 have improved since then, but:
 
  INSERT INTO SomeTable(DateTimeCol)
VALUES(DATETIME(2014-12-31 23:59:59) YEAR TO SECOND);

I think I raised this as an issue back in the 1996-1998 timeframe (I said 
 'many years ago' and meant
it).  I'd have to dig through my release notes to be more precise.  
 Informix only supports natively the
`?` placeholders.  It doesn't yet have the complexities introduced by the 
 PostgreSQL operators.
 
I don't know whether this can be handled at all.  It may be that 
 DBD::Informix has to stay out in
isolation but it would be nice if it wasn't necessary.

The `?` placeholders are 'standard' (for some definition) so DBD::Informix
isn't really 'in isolation'. There are quite a few drivers that only
support `?` placeholders.

In theory, if this proposal goes ahead, and is applied to `:` placeholders
as seems likely, then you'd be able to write the above as:

   INSERT INTO SomeTable(DateTimeCol)
 VALUES(DATETIME(2014-12-31 23\:59\:59) YEAR TO SECOND);

Tim.

Escaping placeholders (was: [Dbix-class] Using Postgres JSONB operators in queries)

[Tim Bunce]

Hello Augustus.

On Thu, Dec 04, 2014 at 03:38:14PM -0800, Augustus Saunders wrote:
Hi all, I have been unable to find a way to use some of the new
JSONB operators in Postgres 9.4 with DBIx::Class. A quick search
for JSONB on the email archive site didn't turn up any results, so
I thought I would ask.

In particular, ?, ?, and ?| are now operators, and we run into
problems with the DBI placeholder being ?.

As more people start using Postgres 9.4 and the JSON operators this
is going to become a significant problem.

I read that putting single quotes around the question mark would
allow a literal question mark in DBI, but I can't seem to make this
work from DBIx::Class.

It won't do what you want. Question marks in quotes are ignored by the
DBI driver, but question marks in quotes won't work as JSON operators.

Can anybody tell me whether this is currently possible, if so how,
and if not what might be involved or where in the code to look?

For code not using DBIx::Class the pg_placeholder_dollaronly attribute
might work, see https://metacpan.org/pod/DBD::Pg#Placeholders

For code using DBIx::Class the problem is more tricky. I'm pretty sure
that SQL::Abstract and thus DBIx::Class only support question mark
placeholders. That means it probably impossible to use expressions
containing a question mark operator with SQL::Abstract/DBIx::Class.
(Though I'd be delighted to be proven wrong.)

So I think the DBI spec for placeholders needs to be extended to allow a
way to 'escape' a question mark that the driver would otherwise treat as
a placeholder.

The obvious and natural approach would be to use a backslash before a
question mark. The backslash would be removed by the driver before the
statement is passed to the backend.

  $dbh-selectrow_array(q{ SELECT {a:1, b:2}'::jsonb  ? 'b' }); # breaks
  $dbh-selectrow_array(q{ SELECT {a:1, b:2}'::jsonb \? 'b' }); # would work

The key question is: what is the risk of any existing DBI SQL statements
containing a question mark placeholder that's preceeded by a backslash?

Can anyone think of realistic examples? (For ANY DBI driver or backend.)

Tim.

Re: First time DBI user crashing perl

[Tim Bunce]

Turning on tracing is usually a good idea when odd things are happening.
Read https://metacpan.org/pod/DBI#TRACING
and set the DBI_TRACE environment variable.

Tim.

On Wed, Oct 29, 2014 at 09:56:27PM -0600, Jon wrote:
Hello Peter,
 
Well, that's kinda what I figured but never hurts try for the easy answer.
 
I cleaned up your test script a bit. 
 
Can you give this version a try: 
 [1]https://gist.github.com/three18ti/c37587ef7d5fb9ee5748
 
I'm downloading DB2 as we speak, but I wanted to ask for your DB2_HOME 
 environment variable.  In your
instructions it has you set it, but the instructions are clearly for a 
 linux server, what did you do on
your windows server?
 
Honestly, I have very limited DB2 experience, so it's kinda the blind 
 leading the blind here.  I'm
really just curious myself. you're more than welcome to ignore me and wait 
 for one of the more
experienced guys :)
 
Best Regards,
Jon A
On Wed, Oct 29, 2014 at 8:42 PM, Peter Shabino [2]wi...@hotmail.com 
 wrote:
 
  I am running it from a command prompt and I am getting some of the 
 output so perl is running fine.
  Just looks like any command that trys and uses the DB2 interface causes 
 a crash. 
  Thanks,
  Peter
 
  My projects:
  [3]http://www.wire2wire.org/
 

 
 
  Date: Wed, 29 Oct 2014 20:26:15 -0600
  Subject: RE: First time DBI user crashing perl
  From: [4]three1...@gmail.com
  To: [5]wi...@hotmail.com
  CC: [6]dbi-users@perl.org
 
  Hello Peter,
 
  How are you invoking the script? Do you have a perl script that you 
 double click?
 
  If so, can you try opening a command/powershell window and running your 
 program? You'll likely have to
  specify the full path to the perl binary then pass your script as the 
 first parameter.
 
  E.g.: \strawberry\perl\bin\perl.exe [7]my_script.pl
 
  I have not encountered this particular problem with db2 but this is my 
 first step when trying to get
  one of my scripts to run in Windows (I'm a Linux enginner by the Windows 
 engineers steal a lot of my
  applications), perhaps someone has more specific experience than myself.
 
  Best Regards,
  Jon A
 
  On Oct 29, 2014 5:50 PM, Peter Shabino [8]wi...@hotmail.com wrote:
 
Try this again I apparently found a keyboard shortcut to send the 
 message in hotmail..
 
Trying to set up a connection to a remote DB2 server in perl to query 
 some data. 
Followed the directions here:
[9]http://www-01.ibm.com/support/docview.wss?rs=71uid=swg21297335
Then searching the web I came up with this chunk of code:
use warnings;
use strict;
use DBI;
use DBD::DB2;
print(Operating System = ,$^O,\n);
print(Perl Binary = ,$^X,\n);
print(Perl Version = ,$],\n);
print(DBI Version = ,$DBI::VERSION,\n);
print(DBD::DB2 Version = ,$DBD::DB2::VERSION,\n\n);
print(env = ,$ENV{ DB2INSTANCE},\n);
my @driver_names = DBI-available_drivers;
print(join(\n,@driver_names),\n);
print(I am here\n);
my @dataSources   =   DBI-data_sources('DB2');
print(@dataSources,\n);
print(I am there\n);
exit(0);
When I run this I get a popup that says the Perl interpreter has 
 stopped working and a button to
close the program. 
on the console I get:
Operating System = MSWin32
Perl Binary = c:\strawberry\perl\bin\perl.exe
Perl Version = 5.018001
DBI Version = 1.631
DBD::DB2 Version = 1.85
env = DB2
ADO
DB2
DBM
ExampleP
File
Gofer
ODBC
Pg
Proxy
SQLite
Sponge
mysql
I am here
So the failure is when I try and get the DB2 data sources... 
Just have no idea even where to start looking for the issue since 
 there is no error message. Is
there a log file with more information on what might have died?
Thanks,
Peter
My projects:
[10]http://www.wire2wire.org/
 
 References
 
Visible links
1. https://gist.github.com/three18ti/c37587ef7d5fb9ee5748
2. mailto:wi...@hotmail.com
3. http://www.wire2wire.org/
4. mailto:three1...@gmail.com
5. mailto:wi...@hotmail.com
6. mailto:dbi-users@perl.org
7. http://my_script.pl/
8. mailto:wi...@hotmail.com
9. http://www-01.ibm.com/support/docview.wss?rs=71uid=swg21297335
   10. http://www.wire2wire.org/

Re: First time DBI user crashing perl

[Tim Bunce]

On Thu, Oct 30, 2014 at 03:33:05PM -0500, Peter Shabino wrote:
Here is the environment variables:
env DB2INSTANCE = DB2
env DB2LIB = C:/Program Files/IBM/SQLLIB/lib/Win32
env LD_LIBRARY_PATH = C:/Program Files/IBM/SQLLIB/lib/Win32
env LIBPATH = C:/Program Files/IBM/SQLLIB/lib/Win32
and the output from trace 15
New 'DBI::dr' (for DBD::DB2::dr, parent='', id=undef)
dbih_setup_handle(DBI::dr=HASH(0x2e4c7c0)=DBI::dr=HASH(0x2e4c868), 
 DBD::DB2::dr, 0, Null!)
dbih_make_com(Null!, 0, DBD::DB2::dr, 176, 0) thr#98a6f8
dbih_setup_attrib(DBI::dr=HASH(0x2e4c868), Err, Null!) 
 SCALAR(0x2acff18) (already defined)
dbih_setup_attrib(DBI::dr=HASH(0x2e4c868), State, Null!) 
 SCALAR(0x2e11358) (already defined)
dbih_setup_attrib(DBI::dr=HASH(0x2e4c868), Errstr, Null!) 
 SCALAR(0x2acff60) (already defined)
dbih_setup_attrib(DBI::dr=HASH(0x2e4c868), TraceLevel, Null!) 0 
 (already defined)
dbih_setup_attrib(DBI::dr=HASH(0x2e4c868), FetchHashKeyName, Null!) 
 'NAME' (already defined)
- install_driver= DBI::dr=HASH(0x2e4c7c0)
 data_sources DISPATCH (DBI::dr=HASH(0x2e4c7c0) rc1/3 @1 g3 ima801 
 pid#9476) at
c:/strawberry/perl/site/lib/DBI.pm line 1066 via  at db2.pl line 35
!! The warn '0' was CLEARED by call to data_sources method
- data_sources for DBD::DB2::dr (DBI::dr=HASH(0x2e4c7c0)~0x2e4c868) 
 thr#98a6f8
Any clues in there on what I have set up wrong?

Nope. Sure looks like a crash within the DBD::DB2 driver.
Hopefully someone here can help but you could also try
http://www.software.ibm.com/data/db2/perl

Tim.

Here is my perl version
This is perl 5, version 18, subversion 1 (v5.18.1) built for 
 MSWin32-x64-multi-thread
Thanks!
Peter
 
My projects:
http://www.wire2wire.org/
 Date: Thu, 30 Oct 2014 12:17:19 +
 From: tim.bu...@pobox.com
 To: three1...@gmail.com
 CC: wi...@hotmail.com; dbi-users@perl.org
 Subject: Re: First time DBI user crashing perl

 Turning on tracing is usually a good idea when odd things are happening.
 Read https://metacpan.org/pod/DBI#TRACING
 and set the DBI_TRACE environment variable.

 Tim.

 On Wed, Oct 29, 2014 at 09:56:27PM -0600, Jon wrote:
  Hello Peter,
 
  Well, that's kinda what I figured but never hurts try for the easy 
 answer.
 
  I cleaned up your test script a bit.
 
  Can you give this version a try: 
 [1]https://gist.github.com/three18ti/c37587ef7d5fb9ee5748
 
  I'm downloading DB2 as we speak, but I wanted to ask for your DB2_HOME 
 environment variable. In your
  instructions it has you set it, but the instructions are clearly for a 
 linux server, what did you do
on
  your windows server?
 
  Honestly, I have very limited DB2 experience, so it's kinda the blind 
 leading the blind here. I'm
  really just curious myself. you're more than welcome to ignore me and 
 wait for one of the more
  experienced guys :)
 
  Best Regards,
  Jon A
  On Wed, Oct 29, 2014 at 8:42 PM, Peter Shabino [2]wi...@hotmail.com 
 wrote:
 
  I am running it from a command prompt and I am getting some of the 
 output so perl is running fine.
  Just looks like any command that trys and uses the DB2 interface 
 causes a crash.
  Thanks,
  Peter
 
  My projects:
  [3]http://www.wire2wire.org/
 
 

 
 
  Date: Wed, 29 Oct 2014 20:26:15 -0600
  Subject: RE: First time DBI user crashing perl
  From: [4]three1...@gmail.com
  To: [5]wi...@hotmail.com
  CC: [6]dbi-users@perl.org
 
  Hello Peter,
 
  How are you invoking the script? Do you have a perl script that you 
 double click?
 
  If so, can you try opening a command/powershell window and running 
 your program? You'll likely have
to
  specify the full path to the perl binary then pass your script as the 
 first parameter.
 
  E.g.: \strawberry\perl\bin\perl.exe [7]my_script.pl
 
  I have not encountered this particular problem with db2 but this is my 
 first step when trying to get
  one of my scripts to run in Windows (I'm a Linux enginner by the 
 Windows engineers steal a lot of my
  applications), perhaps someone has more specific experience than 
 myself.
 
  Best Regards,
  Jon A
 
  On Oct 29, 2014 5:50 PM, Peter Shabino [8]wi...@hotmail.com wrote:
 
  Try this again I apparently found a keyboard shortcut to send the 
 message in hotmail..
 
  Trying to set up a connection to a remote DB2 server in perl to query 
 some data.
  Followed the directions here:
  [9]http://www-01.ibm.com/support/docview.wss?rs=71uid=swg21297335
  Then searching the web I came up with this chunk of code:
  use

Re: Using DBI::Profile to count and time database calls

[Tim Bunce]

Sorry for the delay.

On Tue, Oct 07, 2014 at 12:00:31PM -0700, Bill Moseley wrote:
Noting like writing an email to trigger reading the docs again.

:)

On Tue, Oct 7, 2014 at 11:43 AM, Bill Moseley [1]mose...@hank.org wrote:
 
  I'm trying to just count number of database queries and the time spent 
 waiting on the database.
 
Does this look correct?
my $h = DBI::Profile-new( Path = [ sub { return $_[1] =~ 
 /^(?:execute|do|select)/ ? undef : \undef;} ]
);

Yes, though evaluating the regex during data collection will be slow.
If efficiency is a concern then it's better to collect using '!MethodName'
and post-process to merge the stats for the methods you're interested in.

Tim.

 
  I can use this to just gather grand totals: 
  DBI::Profile-new( Path = [ ] );
  The total time is fine even if it includes the small amount of time in 
 DBI instead of directly waiting
  on the database. But, the counts are do not represent just trips to the 
 database, rather apparently
  counts of all methods called (e.g. a prepare and execute counts as 2).
  Using:
  DBI::Profile-new( Path = [ '!MethodName' ] );
  might be ok, but I'd have to then find all the methods that go to the db 
 (e.g. execute, do).
  Can I use DBI::Profile to just gather up counts to the database?
  Thanks,
  --
  Bill Moseley
  [2]mose...@hank.org
 
--
Bill Moseley
[3]mose...@hank.org
 
 References
 
Visible links
1. mailto:mose...@hank.org
2. mailto:mose...@hank.org
3. mailto:mose...@hank.org

Re: DBI Mysql Driver Handle Mysteriously Changes!

[Tim Bunce]

On Thu, Mar 27, 2014 at 04:43:41PM +1300, Duncan McEwan wrote:
 
   I've got 1000's of lines of debugging showing this happening.  Some of it
   is my own, inserted as print statements or cluck calls directly into 
   DBI.pm
   and some of it the standard DBI debugging set to level 9.  There is way 
   too
   much to include in this message, but I've included some small extracts 
   below
   to illustrate what I am seeing.
  
  What you've included doesn't show the drh changing.
 
 Then maybe I'm mis-interpreting the debugging I'm seeing?

No, nevermind, I was. Thanks.

  I suggest you focus on that. Specifically the code path taken by the
  request that notices that the drh has changed, _and_ the code path taken
  by the _previous_ request _in the same process_.
 
 Yes.  I had been looking at that.  Within DBI.pm I've inserted calls to
 cluck so I could see the stack trace both the case when a cached database
 handle is returned and when a new one is created.  There were *sometimes*
 differences in the call stack between those times, but sometimes not.
 
 At the moment I only turn DBI tracing on in the connect_cached() call and
 turn it off again before the call returns.  I did that because I was worried
 about the amount of output that would be produced if I left tracing on.
 But perhaps that is what I will have to do.

Perhaps you don't need to output it. Call $stacktrace = Carp::longmess
and only output it if the stacktrace is different to the last one, or
some similar logic.

Also try turning on $drh-{TraceLevel} after the $dbh is created.
That'll then log just future connect_cached calls *and* handle
destruction, which might be useful.

 A potential further complication that I didn't mention previously is that
 our application (which I *did* mention is written as a foswiki plugin)
 uses the CGI::Application perl package.  From a very quick look at its
 code, it does seem to know about the DBI and could perhaps be doing something
 too clever which is causing us problems.  I'll look more into that as
 well.

I didn't see anything suspicious in CGI::Application
https://metacpan.org/source/MARKSTOS/CGI-Application-4.50/lib/CGI/Application.pm
or CGI::Application::Plugin::DBH
https://metacpan.org/source/FREW/CGI-Application-Plugin-DBH-4.04/lib/CGI/Application/Plugin/DBH.pm

For more areas to dig, note the mention of dbi_connect_method
in https://metacpan.org/pod/DBI#connect and
https://metacpan.org/source/TIMB/DBI-1.631/DBI.pm#L571

Keep focused on why a new drh appears.

See https://metacpan.org/source/TIMB/DBI-1.631/DBI.pm#L652
Perhaps something is altering %DBI::installed_drh

Tim.

Re: DBI Mysql Driver Handle Mysteriously Changes!

[Tim Bunce]

Hello Duncan.

On Tue, Mar 25, 2014 at 04:42:56PM +1300, Duncan McEwan wrote:
 
 First, a brief recap.  We have a web-based application that runs as a FosWiki 
 plugin under Apache/fcgid.

Could you post the fcgid configuration details for us?
https://httpd.apache.org/mod_fcgid/mod/mod_fcgid.html

 The database connection used by this application should be persistent
 due to the application calling DBI-connect_cached() on each invocation.

Persistent within a single backend process, yes.

 The new piece of information that I have discovered recently is that on a
 call to connect_cached() which *doesn't* return the cached database handle,
 the database driver handle reference passed into connect_cached() has changed.
 Since the dbh cache is obtained from $drh-{CachedKids} ||= {} I can now
 understand why the cached handled is not found!
 
 What I can't understand is why the driver handle passed into connect_cached()
 has changed!

Neither can I.

 I've got 1000's of lines of debugging showing this happening.  Some of it
 is my own, inserted as print statements or cluck calls directly into DBI.pm
 and some of it the standard DBI debugging set to level 9.  There is way too
 much to include in this message, but I've included some small extracts below
 to illustrate what I am seeing.

What you've included doesn't show the drh changing.

I suggest you focus on that. Specifically the code path taken by the
request that notices that the drh has changed, _and_ the code path taken
by the _previous_ request _in the same process_.

 One thing I did just notice is that our application calls connect_cached()
 in the way shown in the DBI pod - that is:
 
   my $dbh = DBI-connect_cached(...)
 
 I don't know enough about perl internals to know exactly what this does.
 But I did wonder if something like the following might be better given the
 persistent nature of our application provided by fcgid.
 
   my $dbi = new DBI;
   my $dbh = $dbi-connect_cached(...)

No. Using new DBI (or DBI-new) isn't a valid way to use the DBI.
Just DBI-connect_cached is fine.

Tim.

Re: PostgreSQL driver DBD::Pg releases version 3.0.0

[Tim Bunce]

On Thu, Feb 06, 2014 at 12:30:15PM +1100, Ron Savage wrote:

 Eg: map{($_ = encode('utf-8', $$hashref{$_}) )} keys %$hashref
 $$item{$_} = decode('utf-8', $$item{$_} || '') for keys %$item

Performance tip: using the function form of encode() and decode()
is significantly slower than using the method call form:

$utf8_encoding-decode($$item{$_} || ''):

 And each module tested under V 3.0.0 broke.
 
 But a 1-line change fixes them:
 
 $$attr{pg_enable_utf8}= 0 if ($$config{dsn} =~ /dbi:Pg/i);

Sweet.

 Gotta love utf8.

:)

Tim.

Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)

[Tim Bunce]

On Fri, Jan 31, 2014 at 09:11:28AM -0500, John Scoles wrote:
 A final note on this.
 
Seems there was a very very long unknown bug in DBI which was only fix a 
 few days ago wiht DB 1.6.31

If you mean Callbacks getting an inner handle, that wasn't a bug as such.
More like a design choice that proved non-optimal.


 [1]http://blogs.perl.org/mt/mt.fcgi?__mode=view_type=entryid=5570blog_id=2165

That's http://blogs.perl.org/users/byterock/2014/01/callbacks-ate-my-brain.html
I presume.

The end result of this bug was that when callbacks are used on the
statement handle some attributes will not be there so you
programmer who did this

$sth-FETCH( 'ParamValues' ), # WTF? - returns a reference to an array of 
 hashes
 
was most likely complaing that the
 
$sth-{ParamValues},
 
should return a ref but was just returning undef.
 
So he 'Kludged' the code to get the value directly with the FETCH which 
 works

I'm not sure what you're saying here John. Using $sth-FETCH('ParamValues')
is perfectly reasonable. It was required before 1.631 and optional with
1.631+ now that $h-{ParamValues} works.

sort of, but it does bleed memory every so slighly.

Are you sure? This is the first I've heard of such a leak.

Tim.

The latest version of DBI with the
 
$sth-{ParamValues},
 
Should solve all you problems
 
As a bonus I have another topic for me blog
 
Cheers
John
 

 
 
Date: Wed, 29 Jan 2014 14:21:28 +
Subject: Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
From: hhferre...@gmail.com
To: boh...@ntlworld.com
CC: byter...@hotmail.com
 
You are right Martin.. Shame on me :(
At the time you suggested that we did not know about the callbacks, sorry 
 for that, our fault :(�
Now that we know the root of the problem I'm sure we will be able to 
 implement a solution.�
Thanks a lot for your time, tips and patience :)
I would be more than pleased to offer you a ginginha, porto /portuguese 
 wine accompanied by a special
local cheese or a portuguese egg tart case you pass by here!
Best Regards,
H�lder Hugo Ferreira
 
On Wed, Jan 29, 2014 at 12:04 PM, Martin J. Evans [2]boh...@ntlworld.com 
 wrote:
 
  On 29/01/14 11:02, hhferreira wrote:
 
Hey Guys!
 
John, your tip about the callbacks revealed to be very accurate!!
 
  I seem to remember saying a long time back in this thread:
 
  Have you got some sort of execute callback? I ask because of the 
 following in the trace:
 
  � � {{ execute callback CODE(0xb832be8) being invoked
 
  and it is only present before the error.
 
  hmmm.
  �
 
We managed to isolate the issue into this statement:
 
� debug( Executing SQL on OptiDb database:,
� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � 
 � �sql_executer � � � � =
sprintf( %s line %s (%s), (caller(0))[1,2], 
 $sth-{private_keep_alive_seconds} ? 'active: keeping
connection open' : 'maintenance' ),
� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � 
 � �statement � � � � � �=
$sth-{Statement},
*params � � � � � � � = $sth-FETCH( 'ParamValues' ), # WTF? - 
 returns a reference to an array of
hashes*
� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � 
 � �connected_since � � �=
sprintf( %s (%.3f seconds),
� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � 
 � � � � � � � � � � � � � � �
� � �strftime( %H:%M:%S, localtime( 
 $dbh-{private_connected_at_timestamp}[0] ) ),
� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � 
 � � � � � � � � � � � � � � �
� � �tv_interval( $dbh-{private_connected_at_timestamp} ),
� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � 
 � � � � � � � � � � � � � � �
� � �),
� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � 
 � �keep_alive_timestamp =
$dbh-{private_keep_alive_until_timestamp}
� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � 
 � � � � � � � � � � � � � � �
�? sprintf( %s (%s seconds to live),
� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � 
 � � � � � � � � � � � � � � �
� � � � � � �strftime( %H:%M:%S, localtime( 
 $dbh-{private_keep_alive_until_timestamp} ) ),
� � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � 
 � � � � � � � � � � � � � � �
� � � � � � �( $dbh-{private_keep_alive_until_timestamp} - time

Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)

[Tim Bunce]

On Fri, Jan 31, 2014 at 12:50:36PM -0500, John Scoles wrote:
 Well I did do some testing. The leak was very small (1k over 10 min run) but 
 only when one does
$shift-FETCH( 'ParamValues' ),
 in the child callback.

If it doesn't keep growing with more call then it's not a leak.

Tim what would the impact of the above??  I know before 1.63 this
$shift-{ParamValues'},
gave you undef which is why the WTF comment was there.

Because the inner handle is a plain blessed hash ref, whereas the outer
handle is *tied* blessed hash ref.

There's no 'ParamValues' key in that hash, so you get an undef.

The ParamValues lookup is handled by the FETCH method call.

Why if in the CB we had the outter handle would the FETCH give you the 
 attributes of the Inner handle??

Calling $outer-{ParamValues} in a tied hash ref triggers a call to
$outer-FETCH('ParamValues') which then gets dispatched by the DBI to
$inner-FETCH('ParamValues') which does the work.

For more details see http://perldoc.perl.org/perltie.html

Just a silly question?

No such thing :)

Tim.

Cheers
 
 
 
 Date: Fri, 31 Jan 2014 17:00:20 +
 From: martin.ev...@easysoft.com
 To: tim.bu...@pobox.com; byter...@hotmail.com
 CC: hhferre...@gmail.com; boh...@ntlworld.com; dbi-users@perl.org
 Subject: Re: Issues with DBI Oracle Input Array Binds 
 (ORA_VARCHAR2_TABLE)

 On 31/01/14 16:21, Tim Bunce wrote:
  On Fri, Jan 31, 2014 at 09:11:28AM -0500, John Scoles wrote:
  A final note on this.
 
  Seems there was a very very long unknown bug in DBI which was only 
 fix a few days ago wiht DB
1.6.31
 
  If you mean Callbacks getting an inner handle, that wasn't a bug as 
 such.
  More like a design choice that proved non-optimal.
 
  
 [1]http://blogs.perl.org/mt/mt.fcgi?__mode=view_type=entryid=5570blog_id=2165
 
  That's 
 http://blogs.perl.org/users/byterock/2014/01/callbacks-ate-my-brain.html
  I presume.
 
  The end result of this bug was that when callbacks are used on the
  statement handle some attributes will not be there so you
  programmer who did this
 
  $sth-FETCH( 'ParamValues' ), # WTF? - returns a reference to an 
 array of hashes
 
  was most likely complaing that the
 
  $sth-{ParamValues},
 
  should return a ref but was just returning undef.
 
  So he 'Kludged' the code to get the value directly with the FETCH 
 which works
 
  I'm not sure what you're saying here John. Using 
 $sth-FETCH('ParamValues')
  is perfectly reasonable. It was required before 1.631 and optional with
  1.631+ now that $h-{ParamValues} works.
 
  sort of, but it does bleed memory every so slighly.
 
  Are you sure? This is the first I've heard of such a leak.
 
  Tim.

 I've found no evidence of a memory leak with a simple test calling 
 ParamValues a lot with some
parameters. However, I'm not using ORA_VARCHAR2_TABLE. The code is:

 else if (kl==11  strEQ(key, ParamValues)) {
 HV *pvhv = newHV();
 if (imp_sth-all_params_hv) {
 SV *sv;
 char *key;
 I32 keylen;
 hv_iterinit(imp_sth-all_params_hv);
 while ( (sv = hv_iternextsv(imp_sth-all_params_hv, key, keylen)) ) {
 phs_t *phs = (phs_t*)(void*)SvPVX(sv); /* placeholder struct */
 (void)hv_store(pvhv, key, keylen, newSVsv(phs-sv), 0);
 }
 }
 retsv = newRV_noinc((SV*)pvhv);
 cacheit = FALSE;

 }

 which looks sane to me right now. ORA_VARCHAR2_TABLE seems to do strange 
 things with parameters I
don't quite get right now.

 As I said previously to H�lder and John (some of the discussion was off 
 dbi-users list presumably
because it contained log data), although I accept taking the call to 
 ParamValues out has on this
occasion made the problem go away I don't understand why. I think there is 
 more to this than it so far
looks but without a way of reproducing it myself I won't be spending any 
 more time on it. If it is
reproducible in a standalone script I will happily look again.

 Martin

Announce: DBI 1.631

[Tim Bunce]

  file: $CPAN/authors/id/T/TI/TIMB/DBI-1.631.tar.gz
  size: 589096 bytes
   md5: 444d3c305e86597e11092b517794a840

=head2 Changes in DBI 1.631 - 20th Jan 2014

NOTE: This release changes the handle passed to Callbacks from being an 'inner'
handle to being an 'outer' handle. If you have code that makes use of Callbacks,
ensure that you understand what this change means and review your callback code.

Fixed err_hash handling of integer err RT#92172 [Dagfinn Ilmari]
Fixed use of \Q vs \E in t/70callbacks.t

Changed the handle passed to Callbacks from being an 'inner'
handle to being an 'outer' handle.

Improved reliability of concurrent testing
PR#8 [Peter Rabbitson]
Changed optional dependencies to suggest
PR#9 [Karen Etheridge]
Changed to avoid mg_get in neatsvpv during global destruction
PR#10 [Matt Phillips]

=cut

Many thanks to all the contributors.

Enjoy!

Tim.

Re: DBI: selectrow_array() return first column when called in list context

[Tim Bunce]

On Mon, Dec 23, 2013 at 09:30:22AM +, mimic...@gmail.com wrote:
The SQL query is the same in both cases, and as can be seen, the query is 
 expected to return two columns
(user and perm), but selectrow_array() returns undef for the second column 
 (perm in this case).
 
my ($x, $y) = $dbh-selectrow_array(qq{SELECT user,perm FROM access
WHERE token=\'Tt9VpStL4xADSDJQtd4AkM
c6cVi66Mwmr9pMcCRgO4NVJ\'})|| die
Could not query database: $DBI::errstr\n;

Your code could be summarized as:

my ($x, $y) = foo() || die ...;

Note that the || operator forces the foo() call to be evaluated in
scalar context, so only a single value is returned. It's evaluated
something like this:

my ($x, $y) = ( foo() || die ... );

If you used the or operator instead you'd get the expected results
because the or operator precidence is below that of the = operator
so it's evaluated like this:

(my ($x, $y) = foo() ) or die ...;

Tim.

(Fwd) Problems with DBD::Oracle package: function plsql_errstr

[Tim Bunce]

- Forwarded message from Eljot na Onet.pl eljot_...@poczta.onet.pl -

Date: Sat, 02 Nov 2013 21:11:48 +0100
From: Eljot na Onet.pl eljot_...@poczta.onet.pl
To: t...@cpan.org
Subject: Problems with DBD::Oracle package: function plsql_errstr

Hi,

I have found two problems with your DBD::Oracle package.

(you have all examples in the attachment plsql_errstr.zip, please read
README.txt file)

problems concern sub plsql_errstr described in
http://search.cpan.org/~pythian/DBD-Oracle-1.64/lib/DBD/Oracle.pm

What's the point? You use view *user_errors* to fetch info about
errors in *last* query parsed. But the view returns list of ALL
current user errors - as name of the view means.

So if I execute example code from pod :

EXAMPLE 1: (01-example-1-sub.sh, calls pl/01-example-1-sub.pl)

# Show the errors if CREATE PROCEDURE fails
$dbh-{RaiseError} = 0;
if ( $dbh-do( q{
CREATE OR REPLACE PROCEDURE perl_dbd_oracle_test as
BEGIN
PROCEDURE filltab( stuff OUT TAB ); asdf
END; } ) ) {} # Statement succeeded
}
elsif ( 6550 != $dbh-err ) { die $dbh-errstr; } # Utter failure
else {
my $msg = $dbh-func( 'plsql_errstr' );
die $dbh-errstr if ! defined $msg;
die $msg if $msg;
}

I will get msg as in attachment 01-example-01.log containing the error
info I have expected. But the following code will not work properly:

(please execute 00.example-del-subs.sh first)

EXAMPLE 2: (02-example-2-subs.sh, calls pl/02-example-2-subs.pl)

# Show the errors if CREATE PROCEDURE fails
$dbh-{RaiseError} = 0;
if ( $dbh-do( q{
CREATE OR REPLACE PROCEDURE perl_dbd_oracle_test_1st as
BEGIN
PROCEDURE filltab( stuff OUT TAB ); asdf
END; } ) ) {} # Statement succeeded
}
elsif ( 6550 != $dbh-err ) { die $dbh-errstr; } # Utter failure
else {
my $msg = $dbh-func( 'plsql_errstr' );
warn $dbh-errstr, \n if ! defined $msg  defined $dbh-errstr;
warn $msg, \n if $msg;
}

# but this works not exactly as one should expect...
$dbh-{RaiseError} = 0;
if ( $dbh-do( q{
CREATE OR REPLACE PROCEDURE perl_dbd_oracle_test_2nd as
BEGIN
PROCEDURE filltab( stuff OUT TAB ); asdf
END; } ) ) {} # Statement succeeded
}
elsif ( 6550 != $dbh-err ) { die $dbh-errstr; } # Utter failure
else {
my $msg = $dbh-func( 'plsql_errstr' );
warn $dbh-errstr, \n if ! defined $msg  defined $dbh-errstr;
warn $msg, \n if $msg;
}

ooops - first we've got info about procedure perl_dbd_oracle_test_1st
(that's ok) but then we've got info about *both* procedures:
perl_dbd_oracle_test_1st and perl_dbd_oracle_test_2nd as well...

The second problem is more subtle. I think the common idea due to
security reasons is to have only one database user which has resource
and CREATE USER privilege, and the only user creates other users and
tables, views, etc for the them. Other users can do only DML queries.
In this case the view user_errors will obviously return the empty row
list. So the code:

EXAMPLE 3 (03-example-other-user.sh)
# Show the errors if CREATE PROCEDURE fails
$dbh-{RaiseError} = 0;
if ( $dbh-do( q{
CREATE OR REPLACE PROCEDURE myuser.perl_dbd_oracle_test as
BEGIN
PROCEDURE filltab( stuff OUT TAB ); asdf
END; } ) ) {} # Statement succeeded
}
elsif ( 6550 != $dbh-err ) { die $dbh-errstr; } # Utter failure
else {
my $msg = $dbh-func( 'plsql_errstr' );
die $dbh-errstr if ! defined $msg;
die $msg if $msg;
}

will return no info at all!

I have solved both problems, would You look at my version of
plsql_errstr in file 04-example-new-plsql-errstr.pl, please?

As You can see I have defined *sub* *plsql_errarray* which select
error info from database and returns them in array reference much more
convinient for later use; *plsql_errstr* only converts that array into
a single string. Of course one should provide at least two additional
parameters to new plsql_errstr function.

-- 
Greetings
Jarek Lubczyński

There are 10 kinds of people:
Those who understand binary and those who don't



- End forwarded message -

Please test DBI-1.628_50 re change in PrintWarn

[Tim Bunce]

The PrintWarn attribute used to default to the same value as $^W.
In other words, if you ran perl with -w then would be on.

See https://metacpan.org/module/TIMB/DBI-1.627/DBI.pm#PrintWarn

That was reasonable back in early 2004 when PrintWarn was added to the
DBI.  I'm planning to change PrintWarn to always default on.

There's a small risk that this change will cause extra warnings to be
logged from your application if it's doing something that triggers the
driver to record warning states (via the set_err method).

I've uploaded DBI-1.628_50 as a trial release of DBI-1.629 so you can
test it *with your application code* (not just make test) and report back.

Please report back whether you have extra warnings logged *OR NOT*
and don't forget to mention what driver you're using.

Thanks!

Tim.

p.s. DBI-1.628_50 will install as $DBI::VERSION 1.629.
Changes: 
https://metacpan.org/changes/release/TIMB/DBI-1.628_50#Changes-in-DBI-1.629

Re: Please test DBI-1.628_50 re change in PrintWarn

[Tim Bunce]

NOTE: this trial version of the DBI requires compiled drivers to be recompiled.

So if you install it, you'll break all your compiled drivers untill tou
recompile and reinstall them.

I'm not sure yet if the final DBI-1.629 release will have that requirement.
(If it doesn't, and you've installed this trial version, then you'll
need to recompile your drivers again. Sorry for the inconvenience!)

Tim.

On Fri, Oct 11, 2013 at 01:49:04PM +0100, Tim Bunce wrote:
 The PrintWarn attribute used to default to the same value as $^W.
 In other words, if you ran perl with -w then would be on.
 
 See https://metacpan.org/module/TIMB/DBI-1.627/DBI.pm#PrintWarn
 
 That was reasonable back in early 2004 when PrintWarn was added to the
 DBI.  I'm planning to change PrintWarn to always default on.
 
 There's a small risk that this change will cause extra warnings to be
 logged from your application if it's doing something that triggers the
 driver to record warning states (via the set_err method).
 
 I've uploaded DBI-1.628_50 as a trial release of DBI-1.629 so you can
 test it *with your application code* (not just make test) and report back.
 
 Please report back whether you have extra warnings logged *OR NOT*
 and don't forget to mention what driver you're using.
 
 Thanks!
 
 Tim.
 
 p.s. DBI-1.628_50 will install as $DBI::VERSION 1.629.
 Changes: 
 https://metacpan.org/changes/release/TIMB/DBI-1.628_50#Changes-in-DBI-1.629

(Fwd) Can't use an undefined value as an ARRAY reference at /usr/local/lib/perl/5.14.2/DBI.pm

[Tim Bunce]

- Forwarded message from Yonatan Sisay ysi...@cloudxdpo.com -

Date: Sat, 28 Sep 2013 01:25:04 -0700
From: Yonatan Sisay ysi...@cloudxdpo.com
To: tim.bu...@pobox.com
Subject: 

   Greetings,
   I have been having an issue with DBI. I installed a web system called KOHA, 
and in the middle of it all,
   this library started having some issues. Here is how it shows the error.
   Can't use an undefined value as an ARRAY reference at 
/usr/local/lib/perl/5.14.2/DBI.pm
   I have tried re-installing the library and it doesn't seem to fix that. 
Could it be another library that
   is causing the issue?
   Thank you for your time, and I will be waiting for your reply.
   --
   Yonatan Sisay Gebregiorgis
   CloudX Support and Development Team
   [1]supp...@cloudxdpo.com
   [2]www.cloudxdpo.com
   [3]Inline image 1

References

   Visible links
   1. mailto:supp...@cloudxdpo.com
   2. http://www.cloudxdpo.com/



- End forwarded message -

Re: DBD::mysql 4.024 released, many fixes, especially for Windows

[Tim Bunce]

I'm delighted to see DBD::mysql moving again.

Many thanks to you and all those actively contributing their time,
attention and energy.

Tim.

On Wed, Sep 18, 2013 at 08:52:25AM +0200, Michiel Beijen wrote:
 Patrick Galbraith has released a new version of DBD::mysql with many
 small fixes inside.
 
 Change log:
 2013-09-17 Michiel Beijen, Patrick Galbraith, DBI/DBD community
 * Fix memory leak if mysql_server_prepare is enabled - RT76462 - Masahiro 
 Chiba
 * Undefined $DBI::errstr on execute fail on Windows: Michiel Beijen
 * Better diagnostics for 80procs.t Fixes RT#71199: Alexandr Ciornii
 * Fix #64013: INSTALL.pod is shown with 'man install': Juergen Weigert
 * Added 'testport' to keys in Makefile.PL Fixes RT#83492: Michiel Beijen
 * Fixed test 70takeimp warning. Michiel Beijen
 * Made test t/87async.t not stop on Win32. Michiel Beijen
 * Update github location. Update support information. Michiel Beijen
 * POD Fixes  Patch from RT77043 by Gunnar Wolf, Debian Perl Group
 * Makefile.PL issue on Windows, Patch by Zeeshan, from RT#82768.
 
 Thanks everybody for contributing!
 
 The code has now moved to the perl5-dbi organization on Github, so
 update your forks.
 https://github.com/perl5-dbi/DBD-mysql
 
 The RT queue grew a bit unwieldy with lots of older tickets. We
 managed to close a great deal of them but there are still 80 tickets
 left. I hope to be able to get this number even further down in the
 upcoming period. See
 https://rt.cpan.org/Public/Dist/Display.html?Name=DBD-mysql
 
 If you have a bug still open (or discover a new one!) that you would
 like to see fixed please feel free to ping your RT ticket or to create
 a new one if needed. And; pull requests on Github are welcome!
 
 --
 Mike

Re: using callbacks with connect_cached

[Tim Bunce]

Both merged. Thanks!

Tim.

On Tue, Sep 03, 2013 at 11:07:15AM -0700, David E. Wheeler wrote:
 On Aug 25, 2013, at 12:10 PM, Tim Bunce tim.bu...@pobox.com wrote:
 
  p.s. I'd be delighted to get a doc patch that notes the need to use a
  lexical for the callbacks hashref on connect_cached.
 
 Submitted: https://github.com/perl5-dbi/dbi/pull/2
 
  p.p.s. I'd also consider a patch to add a 'connect_cached.connected'
  callback for new connections.
 
 Good idea! How’s this? https://github.com/perl5-dbi/dbi/pull/3
 
 David
 

(Fwd) DBD::Gofer

[Tim Bunce]

- Forwarded message from reist87 reis...@gmail.com -

Date: Mon, 26 Aug 2013 17:31:11 +0600
From: reist87 reis...@gmail.com
To: tim.bu...@pobox.com
Subject: DBD::Gofer

   Hi, Tim.
   Sorry for my English, I am russian)
   We have program on consumer computer, which connected to MS SQL database. 
But this database came under
   the responsibility of another department now. And we can't connect to this 
database directly, due to
   safety concerns.
   We want to put a third computer(proxy server) in order to client connect to 
database through it.. That
   is, as a client_comp - DBD::Gofer_comp - MSSQL_comp. Will this work?
   Thank you.
   nt

- End forwarded message -

Re: using callbacks with connect_cached

[Tim Bunce]

On Sun, Aug 25, 2013 at 01:38:57PM +0200, David E. Wheeler wrote:
 On Aug 24, 2013, at 9:40 PM, Vincent Veyron vv.li...@wanadoo.fr wrote:
 
 You can also use a private attribute in connect_cached.new to tell the 
 connected callback to do its thing:
 
 my $cb = {
 'connect_cached.new' = sub {
 $_[4]-{private_is_new} = 1;
 return;
 },
 connected = sub {
 my $dbh = shift;
 warn connected\n if delete $dbh-{private_is_new};
 return;
 }
 };
 
 for (1..3) {
 DBI-connect_cached('dbi:SQLite:', '', '', {
 PrintError = 0,
 RaiseError = 1,
 AutoCommit = 1,
 Callbacks  = $cb,
 });
 }
 
 This emits connected only once.

If you're going to use a callback on connected and an attribute then
there's no need to also use connect_cached.new. Just this should do:

  connected = sub {
  my $dbh = shift;
  if (++$dbh-{private_connected_count} == 1) {
  ... # is new connection
  }
  return;
  }

Tim.

p.s. I'd be delighted to get a doc patch that notes the need to use a
lexical for the callbacks hashref on connect_cached.

p.p.s. I'd also consider a patch to add a 'connect_cached.connected'
callback for new connections.

Re: utf8 and mysql

[Tim Bunce]

On Thu, Aug 01, 2013 at 10:28:41AM +0300, Gabor Szabo wrote:
 On Wed, Jul 31, 2013 at 7:39 PM, Meir Guttman m...@guttman.co.il wrote:
  Dear Gabor,
  You must include «mysql_enable_utf8 = 1» hash record in the connection 
  attributes used when you create the DBI handle
  my %conn_attrs = (RaiseError= $RaiseError,
PrintError= $PrintError,
AutoCommit= $AutoCommit,
mysql_enable_utf8 = 1);
  my $dbh = DBI-connect
   ($dsn, $user_name, $password, \%conn_attrs);
 
 That worked. Great. Thank you!
 
 This was entirely unclear to me from the docs that it needs to be a
 separate key as I looked a couple of lines above the documentation of
 mysql_enable_utf8 where it showed another key, mysql_use_result, being
 in the actual connection string.

That seems like a bug.
Either it's a bug that mysql_enable_utf8=1 in the DSN should work,
or it's a bug that unrecognized items in the DSN should warn.

There is another, little-used, DSN style that might help:

DBI:mysql(mysql_enable_utf8=1):database=$attr-{database};host=$attr-{host}

The attributes after the driver name are extracted by the DBI and
applied to the $dbh returned from the drivers' connect method.
(So can't be used for attributes that need to be applied before
the connection is established.)

You can see it documented in https://metacpan.org/module/DBI#parse_dsn
and the connect section below it.

Tim.

Announce DBI 1.628

[Tim Bunce]

  file: $CPAN/authors/id/T/TI/TIMB/DBI-1.628.tar.gz
  size: 586733 bytes
   md5: 4273f8cc6ee3979ce448c7eb3f8a6a5a

=head2 Changes in DBI 1.628 - 22nd July 2013

Fixed missing fields on partial insert via DBI::DBD::SqlEngine
engines (DBD::CSV, DBD::DBM etc.) [H.Merijn Brand, Jens Rehsack]
Fixed stack corruption on callbacks RT#85562 RT#84974 [Aaron Schweiger]
Fixed DBI::SQL::Nano_::Statement handling of 0 [Jens Rehsack]
Fixed exit op precedence in test RT#87029 [Reni Urban]

Added support for finding tables in multiple directories
via new DBD::File f_dir_search attribute [H.Merijn Brand]
Enable compiling by C++ RT#84285 [Kurt Jaeger]

Typo fixes in pod and comment [David Steinbrunner]
Change DBI's docs to refer to git not svn [H.Merijn Brand]
Clarify bind_col TYPE attribute is sticky [Martin J. Evans]
Fixed reference to $sth in selectall_arrayref docs RT#84873
Spelling fixes [Ville Skyttä]
Changed $VERSIONs to hardcoded strings [H.Merijn Brand]

=cut

Enjoy!

Tim.

Re: DBI Driver.xst and DBD::cego

[Tim Bunce]

Hi Kurt.

On Sun, May 26, 2013 at 08:13:58PM +0200, Kurt Jaeger wrote:
 https://rt.cpan.org/Ticket/Display.html?id=84285

error: invalid conversion from 'const char*' to 'char*'

I'm surprised the compiler treats this as an error, it's normally a warning.

-char *plural = (DBIc_ACTIVE_KIDS(imp_dbh)==1) ?  : s;
+char *plural = (DBIc_ACTIVE_KIDS(imp_dbh)==1) ? (char*) : (char*)s;

 Can someone have a look at it ? Is that patch the right way to do it ?

It would be better to put the const on the declaration in this case.  I.e.:

-char *plural = (DBIc_ACTIVE_KIDS(imp_dbh)==1) ?  : s;
+const char *plural = (DBIc_ACTIVE_KIDS(imp_dbh)==1) ?  : s;

but that may trigger other errors/warnings in later code which will need
attending to. (Same goes for the other hunk in the patch.)

Could you give that a go?

Tim.

ANNOUNCE DBI-1.627

[Tim Bunce]

  file: $CPAN/authors/id/T/TI/TIMB/DBI-1.627.tar.gz
  size: 585833 bytes
   md5: aab49be51b0f4867a1894145b023d2c5


The primary DBI repository is now on github under the
ownership of the perl5-dbi team:

https://github.com/perl5-dbi/dbi
https://github.com/perl5-dbi?tab=members


=head2 Changes in DBI 1.627 - 16th May 2013

Fixed VERSION regression in DBI::SQL::Nano [Tim Bunce]

=head2 Changes in DBI 1.626 - 15th May 2013

Fixed pod text/link was reversed in a few cases RT#85168
[H.Merijn Brand]

Handle aliasing of STORE'd attributes in DBI::DBD::SqlEngine
[Jens Rehsack]

Updated repository URI to git [Jens Rehsack]

Fixed skip() count arg in t/48dbi_dbd_sqlengine.t [Tim Bunce]

=head2 Changes in DBI 1.625 (svn r15595) 28th March 2013

  Fixed heap-use-after-free during global destruction RT#75614
thanks to Reini Urban.
  Fixed ignoring RootClass attribute during connect() by
DBI::DBD::SqlEngine reported in RT#84260 by Michael Schout

=head2 Changes in DBI 1.624 (svn r15576) 22nd March 2013

  Fixed Gofer for hash randomization in perl 5.17.10+ RT#84146

  Clarify docs for can() re RT#83207

=cut

Enjoy!

Tim.

Re: Huh? 4=3?

[Tim Bunce]

On Wed, May 08, 2013 at 10:58:02AM -0700, fe...@crowfix.com wrote:
 On Wed, May 08, 2013 at 09:43:27AM -0700, Bill Ward wrote:
  Cool. That whole scalar vs list context thing is one of Perl's biggest
  strengths, but also one of its biggest weaknesses (in that it is a common
  source of bugs like this). When you see head-scratching problems, it's one
  of the first things to look for.
 
 Just guessing here, not familiar with the particular code in question.
 But I have been bitten a few times by code which returns false in the
 'proper' manner
 
 return;
 
 instead of forcing a scalar return
 
 return undef;
 
 or list return
 
 return ();

There is no difference between return; and return ();.
Both return an empty list when called in list context
and an undef when called in scalar context.

The return (); style does serve as a reminder to the reader.

 Is that what's going on here -- the original code imparted a list
 context, which triggered another perl gotcha, whereby missing list
 values simply disappear:
 
 scalar(1,2,,4,,6) --- 4, not 6

That returns 6, or rather, it returns whatever happens to be the last value.

Tim.

Re: Relevance of ChildHandles

[Tim Bunce]

On Tue, May 07, 2013 at 10:28:29PM +0100, Alberto Simões wrote:
Hello
 
Any kind of handle on DBI has a $h-{ChildHandles} that store weakrefs to 
 all child handles created. For
instance, on a $dbh, you will get a list of weakrefs to $sth you create.
 
When one of those $sth gets out of scope, its count get to 0. What Perl 
 does to the weakref, is to
change it to an undef value (Perl isn't able to remove that element from 
 the array).
 
Although a single undef takes less memory than a complete $sth object, it 
 takes memory space. And if we
get a lot of these undef values, things get worse.

The DBI purges undef entries from ChildHandles from time to time.
(Specifically whenever the number of entries is a multiple of 120.)

At this stage in the life of the DBI I think it's reasonable to assume
that there isn't a leak in the DBI itself. If there was then a lot of
people would be affected and complaining about it.

Tim.

Prepared statements- variable number of placeholders

[Tim Bowden]

Is it possible, and if so, how can I set the number of placeholders in a
prepared statement at runtime?

IE, given something like:

my $sth = $dbh-prepare(INSERT INTO $table ($fields) VALUES (?,?,?));

which is fine if I know how many fields I'm inserting into, but what if
I don't know till runtime how many fields?  How can I put the (?,?,?)
into a variable and have it recognised as placeholders?

Thanks,
Tim Bowden

Re: Prepared statements- variable number of placeholders- SOLVED

[Tim Bowden]

Thanks Henri.  Much appreciated.

On Mon, 2012-10-22 at 09:21 +0200, Henri Asseily wrote:
 The $sth is a mutable string… Which means you can change it at runtime!
 
 Here's an example of a modified $sth at runtime:
 
   my $up_sql = defined($updated_at) ? 'updated_at  ? AND ' : undef;
   my $sql = SELECT * FROM mytable WHERE $up_sql type_id = ? AND 
 serial_number IN (
   SELECT serial_number FROM anothertable WHERE device_id = ?);
   my $sth = $dbh-prepare($sql);
   my $i = 1;
   if (defined($up_sql)) {
 $sth-bind_param($i, $updated_at, SQL_INTEGER); $i++;
   }
   $sth-bind_param($i, $pass_type_id, SQL_VARCHAR); $i++;
   $sth-bind_param($i, $device_id, SQL_VARCHAR); $i++;
   $sth-execute;
 
 
 
 On Oct 22, 2012, at 9:16 AM, Tim Bowden tim.bow...@mapforge.com.au wrote:
 
  Is it possible, and if so, how can I set the number of placeholders in a
  prepared statement at runtime?
  
  IE, given something like:
  
  my $sth = $dbh-prepare(INSERT INTO $table ($fields) VALUES (?,?,?));
  
  which is fine if I know how many fields I'm inserting into, but what if
  I don't know till runtime how many fields?  How can I put the (?,?,?)
  into a variable and have it recognised as placeholders?
  
  Thanks,
  Tim Bowden
  
 

Re: DBI Threaded use access?

[Tim Bunce]

On Wed, Oct 10, 2012 at 06:32:54PM -0700, Chris Buxton wrote:
 On Oct 10, 2012, at 1:21 PM, Jack Craig wrote:
 
  Hi DBI-Users,
  
  I have a simple Perl program to open a file, db, serially read file, write
  file records to db, close db, close file.
  
  if, between the db open  close, given a single dbh, i create multiple
  threads, each
  reading a segment of the file and asynchronously writing to db using the
  same dbh,
  is that ok?
 
 No. Each thread needs its own dbh. Your DBD may not support this.

Specifically, 
http://search.cpan.org/~timb/DBI-1.622/DBI.pm#Threads_and_Thread_Safety

Tim.

(Fwd) DBD::Oracle Continuous Query Notification

[Tim Bunce]

- Forwarded message from Rune Henssel rune.hens...@trapezegroup.eu -

Date: Tue, 28 Aug 2012 13:03:08 +0200
From: Rune Henssel rune.hens...@trapezegroup.eu
To: t...@cpan.org
Subject: DBD::Oracle Continuous Query Notification

   Hi Tim

   I hope that you might be able to help me with a problem that I have.

   I am part of a team developing an application that needs to be notified 
whenever data is changed in
   certain tables in a Oracle database. I know that this is possible in .NET 
using ODP.NET but is it also
   possible in Perl and if so how?

   Yours
   Rune Henssel
   Systemdeveloper

   Trapeze Group Europe A/S
   Hersted�stervej 27-29
   Bygning A
   DK-2620 Albertslund

   www.trapezegroup.com
   r...@trapezegroup.eu

- End forwarded message -

Re: make test hangs when re-installing DBI under 64-bit

[Tim Bunce]

On Mon, Aug 20, 2012 at 08:41:45AM -0600, Harry Jamieson wrote:
 Environment:
 IBM AIX 5.3.
 Perl 5.8.8 now linked to its 64-bit libraries.
 64-bit compiled test simple 0.98
 64-bit compiled MakeMaker 6.48.
 
 We didn't discover that IBM had shipped our AIX with Perl pointed to
 the 32-bit version until after we had already installed DBI, and now
 that we find that we must use DB2 instead of MySQL, we are forced to
 try to re-install everything under 64-bit.  We have re-linked Perl
 to 64-bit and we were successful at re-installing test simple and
 makemaker under 64-bit.

Did you use perlbrew (http://perlbrew.pl/) or use the defaults?

Do you have to use a perl version that's over six years old?

http://matrix.cpantesters.org/?dist=DBI%201.622;maxver=1
shows the last report for DBI from a cpan tester on AIX was for 1.609

And http://matrix.cpantesters.org/?dist=DBI%201.609;reports=1;os=aix
shows only 1 pass and two fails, and those were for perl 5.10.1
(the pass report looks like it's for a 64-bit system).

Tim.

Re: make test hangs when re-installing DBI under 64-bit

[Tim Bunce]

On Mon, Aug 27, 2012 at 01:42:20PM -0600, Harry Jamieson wrote:
 On 8/27/2012 12:07 PM, Tim Bunce wrote:
 On Mon, Aug 20, 2012 at 08:41:45AM -0600, Harry Jamieson wrote:
 Environment:
 IBM AIX 5.3.
 Perl 5.8.8 now linked to its 64-bit libraries.
 64-bit compiled test simple 0.98
 64-bit compiled MakeMaker 6.48.
 
 We didn't discover that IBM had shipped our AIX with Perl pointed to
 the 32-bit version until after we had already installed DBI, and now
 that we find that we must use DB2 instead of MySQL, we are forced to
 try to re-install everything under 64-bit.  We have re-linked Perl
 to 64-bit and we were successful at re-installing test simple and
 makemaker under 64-bit.
 Did you use perlbrew (http://perlbrew.pl/) or use the defaults?
 
 Do you have to use a perl version that's over six years old?
 
 http://matrix.cpantesters.org/?dist=DBI%201.622;maxver=1
 shows the last report for DBI from a cpan tester on AIX was for 1.609
 
 And http://matrix.cpantesters.org/?dist=DBI%201.609;reports=1;os=aix
 shows only 1 pass and two fails, and those were for perl 5.10.1
 (the pass report looks like it's for a 64-bit system).

 Environment:
 IBM AIX 5.3.
 Perl 5.8.8 now linked to its 32-bit libraries.
 32-bit compiled test simple 0.98
 32-bit compiled MakeMaker 6.48.
 
 Tim, thank you for your reply.   We have Perl 5.8.8 installed on the
 machine right now, and it has been pointed back to 32-bit.  The
 matrix that you gave me seems to show that I should update that to
 at least 5.10.1 and then try to install 1.609 of DBI.

You can't really draw firm conclusions from a single data point.

 What version of DBD::DB2 would go along with that?

I've no idea. The cpantesters matrix doesn't look helpful
http://matrix.cpantesters.org/?dist=DBD-DB2;maxver=1

 The cpantest with the one pass and two fails was under AIX 5.1, and
 we have AXI 5.3.  I'm hoping that that will make a positive
 difference.
 
 I am new to perl and had never heard of perlbrew before.  I will
 download it once the machine has finished TRYING to bring my DB2 up
 to 8.2 so that I can load DBD::DB2.  Right now, I'm zcating the DB2
 fix pack and getting lots and lots of garbage on my screen. :(  And
 yes, I FTP'd it direct from IBM in binary.  I'll keep you up to date
 on my progress.

I can't help much beyond perl and DBI. (It's been many many years
since I've even seen an AIX machine and I've never used DB2.)

http://www-01.ibm.com/support/docview.wss?uid=swg21297335

Perhaps open...@us.ibm.com can help.

Good luck.

Tim.

ANNOUNCE: DBI 1.622

[Tim Bunce]

  file: $CPAN/authors/id/T/TI/TIMB/DBI-1.622.tar.gz
  size: 585651 bytes
   md5: 9836bcf1b9acc842089aa10b16736909

=head2 Changes in DBI 1.622 (svn r15327) 6th June 2012

  Fixed lack of =encoding in non-ASCII pod docs. RT#77588

  Corrected typo in DBI::ProfileDumper thanks to Finn Hakansson.

=cut

Enjoy!

Tim.

Re: Odd error using bind_param_inout

[Tim Bunce]

I wonder if it would be possible (and useful) for DBD::Oracle to warn if
there are unbound params.

Tim.

On Mon, Jun 04, 2012 at 02:12:53PM -0400, Jeffrey Seger wrote:
 $dbh-do() executes your sql.  You are binding the parameter after this.
  Use $dbh-prepare instead.
 
 On Mon, Jun 4, 2012 at 2:01 PM, Bruce Johnson
 john...@pharmacy.arizona.eduwrote:
 
  I'm getting the following error:
 
  [Mon Jun 04 09:14:49 2012] [error] [client 128.196.45.237]
  DBD::Oracle::db do failed: ORA-01008: not all variables bound (DBD ERROR:
  OCIStmtExecute) [for Statement insert into resources (
  short_name,long_name,building_id,room_desc,isaroom,numseats,numtables,hour_open,hour_close,available,computer,enet_num,approved_text)values(
  'B340','SP Training Room','1062','','1','','','7','20','A','','','')
  returning resource_id into :new_id] at /home/allwebfiles/perl/
  resource_mgmt2.pl line 67., referer:
  https://resource-scheduler.pharmacy.arizona.edu/calendar/resource_mgmt.pl
 
 
  The relevant perl code is:
 
  my $new_resource_id = 0;
  my $csr_insert = $lda-do($sq_insert);
  $csr_insert-bind_param_inout(':new_id', \$new_resource_id, 25);
  $csr_insert-execute();
 
  There's only one variable, and as far as I can see it's correct.
 
  $sq_insert is the statement listed in the logged error. resource_id is
  created via an 'on insert' trigger.
 
 
  --
  Bruce Johnson
  University of Arizona
  College of Pharmacy
  Information Technology Group
 
  Institutions do not have opinions, merely customs
 
 
 
 
 
 -- 
 Champions do not become champions when they win the event, but in the
 hours, weeks, months and years they spend preparing for it. The victorious
 performance itself is merely the demonstration of their championship
 character. -T. Alan Armstrong
 
 The Ow that can be expressed is not the true Ow. - Ao Tzu

Re: Installing 32 bit DBI.pm

[Tim Bunce]

On Fri, May 25, 2012 at 03:52:05PM +, chee.yuen...@accenture.com wrote:
 Hi,
 
 We are using a 32 bit perl 5.8.0 = ELF 32-bit LSB executable, Intel 80386, 
 version 1 (SYSV), for GNU/Linux 2.2.5, dynamically linked (uses shared libs), 
 for GNU/Linux 2.2.5, not stripped
 
 As per your support matrix -  
 http://matrix.cpantesters.org/?dist=DBI%201.621;maxver=1 , I downloaded DBI 
 1.605 and installed it.
 
 I got errors when installing and it seems to be due to DBI.pm is ELF 64-bit 
 LSB shared object, AMD x86-64, version 1 (SYSV), not stripped

You're more likely to get help if you show the actual error message.

You're much more likely to get help if you include a url to a complete
log of all you commands and responses. Perhaps paste them into a service
like http://pastebin.com/

Tim.

p.s. http://mikeash.com/getting_answers.html

ANNOUNCE: DBI 1.621

[Tim Bunce]

  file: $CPAN/authors/id/T/TI/TIMB/DBI-1.621.tar.gz
  size: 585080 bytes
   md5: f44d84f8bdbabc14839eae0bd37e5509

=head2 Changes in DBI 1.621 (svn r15315) 21st May 2012

  Fixed segmentation fault when a thread is created from
within another thread RT#77137, thanks to Dave Mitchell.
  Updated previous Changes to credit Booking.com for sponsoring
Dave Mitchell's recent DBI optimization work.

=cut

Enjoy!

Tim.

Re: Segfault in threaded program after upgrade to DBI 1.620

[Tim Bunce]

Ah, great. Thanks Matt.

[Note to self: It's worth checking the bug tracker for updates from users.]

DBI 1.621 is on it's way.

Tim.

On Mon, May 21, 2012 at 10:23:44AM -0500, Matthew Musgrove wrote:
 Tim,
 Yes, I updated that bug report last week to report that the patch worked
 for me.
 
 Thanks,
 Matt
 
 On Mon, May 21, 2012 at 7:53 AM, Tim timbo@timac.local wrote:
 
  Hello Matthew.
 
  It's worth checking the bug tracker for known problems.
  Your problem sounds like
  https://rt.cpan.org/Public/Bug/Display.html?id=77137
  for which we have a patch attached to the ticket.
 
  I'm about to make a trial release of 1.621 with that patch included.
  I'd be grateful if you could test the patch ASAP, or else test the trial
  release.
 
  Thanks.
 
  Tim.
 

Re: Segfault in threaded program after upgrade to DBI 1.620

[Tim]

Hello Matthew.

It's worth checking the bug tracker for known problems.
Your problem sounds like https://rt.cpan.org/Public/Bug/Display.html?id=77137
for which we have a patch attached to the ticket.

I'm about to make a trial release of 1.621 with that patch included.
I'd be grateful if you could test the patch ASAP, or else test the trial
release.

Thanks.

Tim.

ANNOUNCE DBI 1.620

[Tim Bunce]

  file: $CPAN/authors/id/T/TI/TIMB/DBI-1.620.tar.gz
  size: 584922 bytes
   md5: 2dfcff5b3b7afc53be402ef5f133da00

=head2 Changes in DBI 1.620 (svn r15300) 25th April 2012
   
  Modified column renaming in fetchall_arrayref, added in 1.619,
to work on column index numbers not names (an incompatible change).
  Reworked the fetchall_arrayref documentation.
  Hash slices in fetchall_arrayref now detect invalid column names.

=head2 Changes in DBI 1.619 (svn r15294) 23rd April 2012

  Fixed the connected method to stop showing the password in
trace file (Martin J. Evans).
  Fixed _install_method to set CvFILE correctly
thanks to sprout RT#76296
  Fixed SqlEngine list_tables thanks to David McMath
and Norbert Gruener. RT#67223 RT#69260

  Optimized DBI method dispatch thanks to Dave Mitchell.
  Optimized driver access to DBI internal state thanks to Dave Mitchell.
  Optimized driver access to handle data thanks to Dave Mitchell.
  Optimized fetchall_arrayref with hash slice thanks
to Dagfinn Ilmari Mannsåker. RT#76520
  Allow renaming columns in fetchall_arrayref hash slices
thanks to Dagfinn Ilmari Mannsåker. RT#76572
  Reserved snmp_ and tree_ for DBD::SNMP and DBD::TreeData

=cut

Many thanks to the contributors, especially Dave Mitchell
(via Booking.com) and Ilmari.

Dave's work has yielded a significant reduction in some of the DBI
overheads. For users of perls configured with thread support
it is recommended, but not required, that you recompile your drivers
after installing this release in order to get full benefit.

Ilmari's changes implement ideas that came from discussions I'd had with
Peter Rabbitson on IRC about fetch performance in DBIx::Class. Hopefully
a future release of DBIx::Class will make full use of these changes.

Enjoy!

Tim.

ANNOUNCE DBI 1.618

[Tim Bunce]

  file: $CPAN/authors/id/T/TI/TIMB/DBI-1.618.tar.gz
  size: 583067 bytes
   md5: 5ad29b56f7457f22bd1ca1c871b30719

=head2 Changes in DBI 1.618 (svn r15170) 25rd February 2012

  Fixed compiler warnings in Driver_xst.h (Martin J. Evans)
  Fixed compiler warning in DBI.xs (H.Merijn Brand)
  Fixed Gofer tests failing on Windows RT74975 (Manoj Kumar)
  Fixed my_ctx compile errors on Windows (Dave Mitchell)

  Significantly optimized method dispatch via cache (Dave Mitchell)
  Significantly optimized DBI internals for threads (Dave Mitchell)
  Xsub to xsub calling optimization now enabled for threaded perls.
  Corrected typo in example in docs (David Precious)
  Added note that calling clone() without an arg may warn in future.
  Minor changes to the install_method() docs in DBI::DBD.
  Updated dbipport.h from Devel::PPPort 3.20 (Martin J. Evans)

=cut

Many thanks to all who contributed to this release, especially Dave
Mitchell for his optimization work.

Enjoy!

Tim.

Re: New DBD::ODBC 1.34_3 development release

[Tim Bunce]

On Fri, Feb 03, 2012 at 08:51:10PM +, Martin J. Evans wrote:
 
 You should also be aware that calling odbc_getdiag* will clear DBI's
 err, errstr and state so if you want DBI's methods to return
 something meaningful you may need to call them first - I'm unsure if
 I can stop this behaviour.

You can. You need to pass some extra args to install_method().

Something like install_method(odbc_getdiagrec, { O=0x0004 });

That's the value of the IMA_KEEP_ERR flag.  See the #define IMA_*'s in
DBI.xs and the O=... in DBI.pm

Tim.

Re: DBI-Users RE: DBD-Oracle - obtaining OCI handles from $dbh

[Tim Bunce]

I'm late to this thread (it went into a different mail folder).
Lyle, have you see the Oracle::OCI module?
It provides deep integration between Oracle::OCI and DBD::Oracle.

To make that possible I added a get_oci_handle function pointer
to DBD::Oracle's handles. It lets you ask for any kind of OCI handle
from any kind of DBI handle (drh, dbh, sth).

Oracle::OCI calls it like this:

void *
get_oci_handle(SV *h, int handle_type, int flags) {
STRLEN lna;
typedef void *(*hook_type)_((imp_xxh_t *imp_xxh, int handle_type, int 
flags));
hook_type hook;
/* D_imp_xxh(h); */
imp_xxh_t *imp_xxh;
if (flags  1)
warn(get_oci_handle(%s,%d,%d), SvPV(h,lna), handle_type, 
flags);
imp_xxh = (imp_xxh_t*)(DBIh_COM(h));
if (DBIc_TYPE(imp_xxh) == DBIt_ST)
hook = (hook_type)((imp_sth_t*)imp_xxh)-get_oci_handle;
else if (DBIc_TYPE(imp_xxh) == DBIt_DB)
hook = (hook_type)((imp_dbh_t*)imp_xxh)-get_oci_handle;
else croak(Can't get oci handle type %d from %s. Unsupported DBI 
handle type,
handle_type, SvPV(h,lna));
return hook(imp_xxh, handle_type, flags);
}

Then calls it like this:

get_oci_handle(arg, OCI_HTYPE_ERROR, 0);
get_oci_handle(arg, OCI_HTYPE_SVCCTX, 0);
get_oci_handle(arg, OCI_HTYPE_ENV, 0);
get_oci_handle(arg, OCI_HTYPE_SERVER, 0);
get_oci_handle(arg, OCI_HTYPE_SESSION, 0);
get_oci_handle(arg, OCI_HTYPE_STMT, 0);

I'd have no objection to adding a perl method to DBD::Oracle to access
the get_oci_handle function. Perhaps ora_get_oci_handle($handle_type).

Tim [still sad that Oracle::OCI doesn't get more love]


On Thu, Oct 27, 2011 at 02:39:15PM -0400, Lyle Brooks wrote:
 Ok, so with the following addition to Oracle.pm
 
 DBD::Oracle::db-install_method(ora_oci_handles);
 
 my little test program worked.  By worked, I mean I did
 a connect to the database, then did
 
 my @h = $dbh-ora_oci_handles();
 
 and it returned 4 integers (ie. the value of the pointers), which
 is what I expected/wanted.
 
 I haven't yet tested that I can now pass these pointer values to
 the C++ libraries and have them digest it properly...but that would
 be next.
 
 As for how much anyone else might find use for thisprobably not
 a wide audience.  But it is a nice hack!
 
 Thanks for the pointers.
 
 Quoting John Scoles (byter...@hotmail.com):
  
  
   Date: Thu, 27 Oct 2011 14:14:03 -0400
   From: bro...@deseret.com
   To: martin.ev...@easysoft.com
   CC: dbi-users@perl.org
   Subject: Re: DBD-Oracle - obtaining OCI handles from $dbh
   
   
   Thanks for those pointers.
   
   I do agree with what Martin points out. My Perl script using DBI
   and some XS bindings to the legacy C++ libraries would share the
   same address space (not using threads in my application). This
   is why I thought I could return the handles/pointers as scalars.
   
   Taking John's suggestions, here is a quick code hack that I made
   to Oracle.xs (I haven't tested this ...other than it compiles).
   Looks like it should work. ;-)
   
   void
   ora_oci_handles(dbh)
   SV *dbh
   PREINIT:
   D_imp_dbh(dbh);
   PPCODE:
   
   /* Verify what is passed in is a $dbh object */
   if ( ! sv_derived_from(ST(0), DBI::db)) {
   Perl_croak(aTHX_ dbh is not of type DBI::db);
   }
   
   mXPUSHi( (IV) imp_dbh-envhp ); /* Environment handle */
   mXPUSHi( (IV) imp_dbh-svchp ); /* Service Context handle */
   mXPUSHi( (IV) imp_dbh-srvhp ); /* Server handle */
   mXPUSHi( (IV) imp_dbh-authp ); /* Session handle */
   
   XSRETURN(4);
   
   
   Then my idea is to use this in Perl space...
   
   my($envhp, $svchp, $srvhp, $authp) = $dbh-ora_oci_handles();
   
   
   # Now share the OCI handles from DBI with the custom
   # C++ libraries.
   
   my $cpp_dbh = MyCppOracleClass-new(); # creates custom C++ object
   
   $cpp_dbh-envhp($envhp);
   $cpp_dbh-svchp($svchp);
   $cpp_dbh-srvhp($srvhp);
   $cpp_dbh-authp($authp);
   
   # Do something interesting with the C++ object
   
   $cpp_dbh-make_legacy_call_to_db();
   
   
   
   
  Yup that should work I didn't put two and two together and figure you 
  already had XS for the C++. 
   
   It could be something we could add to DBD::Oracle but I would return all 
  of the handles not just the few you need.
   
  I wonder how much it would be used though??
   
   
  Cheers 
   
   
   
   Quoting Martin J. Evans (martin.ev...@easysoft.com):
On 27/10/2011 17:43, John Scoles wrote:
Hmm!!

Well yes could be done but not as part of any release of DBD::Oracle 
it 
would have to be you own hacked version

Why is that John? What is the problem with returning a C pointer via a 
DBD::Oracle attribute? It is just a pointer to some memory and loads of 
XS modules do this. There is an Oracle OCI module I played with for a 
short time but it is problematic to build. I looked at it as I could 
implement OCI calls

Re: DBD-Oracle - obtaining OCI handles from $dbh

[Tim Bunce]

On Fri, Oct 28, 2011 at 05:19:33PM +0100, Martin J. Evans wrote:
 On 28/10/2011 16:34, Charles Jardine wrote:
 I still use Oracle::OCI. There is a patch below which may
 allow you to build it under Oracle 11.2.
 
 As you will see, I have kept it going by botching things.
 Whenever a new feature of OCI causes trouble, I add it to the
 list of features to omit. I would like to do a better job, but
 I simply don't have the time. All I can do is to offer this
 patch on an as-seen basis, in the hope that it might inspire
 some else to do a proper job of rescuing Oracle::OCI.
 
 Aha, I thought it was you Charles who helped me last time I tried to
 build Oracle::OCI.
 
 Perhaps I'll find time to give it another go.

That would be awesome.

Tim.

Re: DBD-Oracle - obtaining OCI handles from $dbh

[Tim Bunce]

On Fri, Oct 28, 2011 at 11:09:20AM -0400, Lyle Brooks wrote:
 
 The only other comment I have on that approach is that the�
 interface comes out looking very C-like...meaning, the Perl
 interface looks almost like the OCI C interface, which is
 not too surprising.  I found myself wishing that Oracle::OCI
 would look more Perl-ish.  Of course, I understand the�
 argument that by mirroring the OCI C interface, then the Perl
 interface is consistent with the OCI C documentation and hence
 there would not be a need to learn two OCI interfaces.

I'd hope people would develop higher-level interfaces for various
aspects of Oracle functionality using Oracle::OCI as an underlying API.
An Oracle::AQ module for example.

 My work uses a good deal of the Oracle AQ functionality, and
 so I've found myself repeated wishing that I could find a�
 way to Oracle::OCI work...but it has left me mostly frustrated.

Hopefully between Charles, Martin, and yourself, we can breath some new
life into Oracle::OCI.

Tim.

Re: Oracle and Two Phase commit with Perl?

[Tim Bunce]

On Thu, Oct 06, 2011 at 03:20:08PM -0700, Eirik Toft wrote:
 On Sep 13, 11:16 am, mark.bo...@proquest.com (Bobak, Mark) wrote:
  Does anyone have any experience w/ doing two-phase commit across
  connections to two different databases from the same Perl program?
   (To guarantee that either both or neither transaction is committed,
  for consistency.)
 
 Well, assuming you have AutoCommit turned off, why not

 unless ($sth1-execute(this,that)  $sth2-execute(this,that)) {
   $dbh1-rollback;
   $dbh2-rollback;
 } else {
   $dbh1-commit;
   $dbh2-commit;
 }

Because if the second commit fails (for any of countless reasons) the
first commit can't be rolled back.

Mark, there's no explicit support for two-phase commit in the DBI, but
drivers are free to implement support via private methods. I don't know
off hand if any do. After a very quick skim of the OCI docs at
http://www.tacsoft.cn/1110/appdev.111/b28395/oci17msc006.htm
it might be fairly simple to add to DBD::Oracle.

Something like:

  ...as above...
  else {
try {
$dbh1-ora_trans_prepare();  # OCITransPrepare()
$dbh2-ora_trans_prepare();
$dbh1-ora_trans_commit(OCI_TRANS_TWOPHASE); # OCITransCommit()
$dbh2-ora_trans_commit(OCI_TRANS_TWOPHASE);
}
catch {
$dbh1-ora_trans_forget();   # OCITransForget()
$dbh2-ora_trans_forget();
}
  }

[assuming RaiseError is enabled and Try::Tiny has been use'd.]
Would be nice to add the rest of the OCITrans*() calls as well.

Tim.

Re: Quoting of values in arrays in broken in DBD::Pg, can you help fix it? ( CPAN RT#58552 ) [SumsaultRT #9386]

[Tim Bunce]

On Fri, Sep 30, 2011 at 08:40:33AM -0400, Mark Stosberg wrote:
 
 Hello,
 
 If you use DBD::Pg and also use PostgreSQL arrays, you should be aware
 there is an open bug with quoting works. A bug report about the issue is
 here, including some related test cases and work on some patches:
 
 https://rt.cpan.org/Public/Bug/Display.html?id=58552

I'm swamped so, tl;dr, sadly.

 However, the proposed fix is not passing all the test cases. The
 solution will need someone with XS skills, who also has confidence about
 exactly what the correct quoting behavior should be.

I'm probably missing something but I'd say the 'correct quoting' is
whatever works to get any random data from the app to the database.

 I'm affected by the issue and would personally appreciate the help.

I'd appreciate a summary of the key issues.

Tim.

Re: Tail Module + DBI Module, can\'t keep up!

[Tim Bunce]

This is all fine advice for performance tuning DBI app and worth doing.
But there's always a limit to what can be achieved on the client.
So it's worth exploring what can be done on the server side, beyond
standard tuning practices.

I wrote a high volume log insertion app using mysql many years ago.
I can't remember the performance figures, but it was fast.

A major cost of insertion is maintaining indices. So for maximum
insertion speed you want to have no indices. But to query the log
records you'll probably want indices.

The way I approached this was to have per-hour tables: log_MMDDHH
The loader streams the records into the table as fast as it can.
(From memory I used a multi-row INSERT statement, no placeholders.)
Every time it switches to a new hour it triggered the building of
indices on the previous hour's table. It also triggered recreating
per-day views (log_MMDD) that abstracted the hourly tables.

The same technique could be applied at whatever time granularity meets
your needs, from table-per day to table-per minute.

If you can delay the loading of the log data by whatever period you're
using (eg hourly) then you have the option of preparing the log data as
hourly text files and then, when each is complete, using a proprietary
bulk-loading tool to load it.

Tim.

Re: Fwd: Idea for a Gofer transport for translating SQL - primarily intended for testing

[Tim Bunce]

On Sun, Sep 11, 2011 at 11:25:20PM +0100, Andrew Ford wrote:
 I suggest that we move this discussion to dbi-dev.

[dbi-users dropped] 

 On 11/09/11 18:14, Tim Bunce wrote:
 On Fri, Sep 09, 2011 at 03:50:53PM +0100, Andrew Ford wrote:
 
   my $next_middleware = $go_transport-middleware;
   $go_transport-middleware( sub { return $middleware_class-handler(shift, 
 $next_middleware) } );
 
 [...]

 Each middleware handler would take a request object and a reference
 to the next handler in the chain.
 
 Calling $go_transport-middleware within the sub isn't right if we go
 with a chain of closures, which I'd prefer.  I'd expect that last line
 to be something like:
 
  $go_transport-middleware( $middleware_class-new_handler( 
  $go_transport-middleware ) );
 
 where new_handler would look something like:
 
  sub new_handler {
  my ($class, $existing_handler) = @_;
 
  my $new_handler = sub {
  my ($self, $request) = @_;
  ...do something with $request...
  my $response = $existing_handler-($self, $request);
  ...do something with $response...
  return $response;
  } );
 
  return $new_handler;
  }
 
 So at setup-time a chain of closures is created.

 [...] Your approach
 is probably cleaner, but it does put the onus on middleware writers
 to build the closures properly (although that can be mitigated
 against with good documentation that provides example code for
 writing new_hander).

It's pretty trivial really.


 dbi:Gofer:transport=null;middleware=delay(5),module2(a=1,b=2);dsn=dbi:XXX...

I'd rather not require the middlewares to do argument parsing.
It seems inapprpriate. The transport base class can do the typical
incantation of:

%args = map { split(/=/, $_, 2) } for split(/,/, $args_string);

which for '5' would yield %args = (5,undef). The middleware could detect
that kind of case (single arg with an undef value) and deal with if it
really wanted to.


 There is still the issue that the middleware gets invoked before the
 transport... sort of -  actually it is the transport that is
 invoking the chain of closures that make up the middleware.

The transport is split into two parts, one handles the caching, retry
and timeout logic (in the base class) and the other does the get this
request from A to B logic (in the various sub classes).

The middleware wraps the latter part, and so gets the caching, retry and
timeout logic for free.


 Anyway,
 linguistically with the DSN I am saying that I want the null
 transport to be used... and by the way I want it to pass the request
 through these items of middleware - so I think that outermost-first
 would be what I at least would expect.

If we go that way (I'm undecided) then the examples should probably
have the via=... before the transport=...


 Of course one could model it as filters, rather than layers, and have
 pre=... and post=...  components but that would be more tricky to implement

I think YAGNI applies.


 Other server-specific stuff in DBI::Gofer::Execute might need to move out
 (perhaps to a DBI::Gofer::ServerExecute subclass) so it can be used as a
 base class for middlewares that want to appear to 'execute' some requests.

s/as a base class for/by/;

 Certainly middlewares ought to inherit from a base class supplied by the
 DBI to enable safer evolution.

 Should the middleware inherit from DBD::GoferMiddleware::Base or
 DBD::Gofer::Middleware::Base

DBD::Gofer::Middleware::Base. Though the class would be practically empty.

 And what class should we use as the default class prefix?  Are we
 still saying that unqualified middleware classes are assumed to have a
 prefix of DBDx::GoferMiddleware?

Yes, I think so.

 I would like to have some methods that can be called without having
 to understand the layout of the request and response objects.

Yeap. They need better encapsulation and convenience methods. But let's
leave that to one side for now and revisit it once there's working code.

 is coming up with some new unit tests that thoroughly exercise the
 new code, in combination with other transports as well as the new
 passthrough transport.

 (are we keeping the final class name component for the middleware
 class in lower case, as it is with the transport classes?).
 I think I'd prefer a capitalized name for these.

 How does one cope with capitalized class names and lower case
 component names - or are you saying that we suggest that middleware
 writers use capitalized class names and thus the names listed with
 via= would be in upper case (to distinguish them from transport
 names?)?

Perhaps a better scheme is that DBI supplied things (transports and
middleware) are lowercase but externally supplied things are capitalized.

 If we have a DBD::Gofer middleware feature then it would be helpful to
 add a new passthrough (or passthru) transport.  This would be based
 on the null transport, but without the serialization/deserialization

Re: Fwd: Idea for a Gofer transport for translating SQL - primarily intended for testing

[Tim Bunce]

 the relevant server classes can be used safely on the client side.
Adding some docs to http://search.cpan.org/perldoc?DBI::Gofer::Response
would be a good start.

Some light refactoring might be needed. Specifically new_response_with_err
in DBI::Gofer::Execute should probably move into DBI::Gofer::Response.

Other server-specific stuff in DBI::Gofer::Execute might need to move out
(perhaps to a DBI::Gofer::ServerExecute subclass) so it can be used as a
base class for middlewares that want to appear to 'execute' some requests.
Certainly middlewares ought to inherit from a base class supplied by the
DBI to enable safer evolution.

 I'll have a go at getting this coded over the next few days, but it
 looks as if the changes required to implement middleware are
 relatively trivial.

Yeah! Many thanks for doing this Andrew. It's a great addition to Gofer.

 Your module could then use this mechanism. If you wanted to release it
 to CPAN then a name like DBDx::GoferMiddleware::FOO would be good.
 (Note the DBDx not DBIx since this is client-side. We may well end up
 with server-side gofer middlewares as well.)

 I think I would take the name DBDx::GoferMiddleware::adaptor for my
 transformation middleware, unless anyone has a better idea

Umm, adaptor seems too generic. Has almost no meaning out of context.
You'd have to make the logic *much* more generic for the name to fit.
A name like RewriteSPcalls would be more descriptive of the current logic.

 (are we keeping the final class name component for the middleware
 class in lower case, as it is with the transport classes?).

I think I'd prefer a capitalized name for these.

 If we have a DBD::Gofer middleware feature then it would be helpful to
 add a new passthrough (or passthru) transport.  This would be based
 on the null transport, but without the serialization/deserialization,
 i.e. something as simple as:

For efficiency reasons the DBI::Gofer::Execute class assumes it can
modify elements of the request object in-place. The serialization is
needed to avoid subtle bugs that that would otherwise cause. I looked
into 'fixing' that some years ago but there wasn't a good use case then
- the null transport was only ever used for testing. It's probably not
worth trying to fix though. The overhead of serialization/deserialization
is probably (I'm guessing) a relatively small part of the overall
overhead of using Gofer.

Feel free to try, but I wouldn't make it a priority :)

Tim.

Re: Fwd: Idea for a Gofer transport for translating SQL - primarily intended for testing

[Tim Bunce]

On Thu, Sep 08, 2011 at 01:19:17PM +0100, Andrew Ford wrote:
Hi Tim
 
Sorry for mailing you directly, but I don't know whether my emails
are getting through to the dbi-users mailing list,

[CC'd to dbi-users]

and I would quite like to get this module onto CPAN and would like some 
 feedback as to
whether the expert thinks it is a bad idea.  I think it would be a 
 useful addition to the testing
armoury, but then I am obviously biased.

The general idea is certainly useful. I'd like to generalise it though.

There's a conceptual similarity with PSGI/Plack middlewares that I'd
like to build on.  Specifically, rather than creating a new transport,
I'd like to see DBD/Gofer/Transport/Base.pm extended to call one or more
'Gofer client middlewares' just before it calls transmit_request_by_transport().

The calling code could be something like:

+   my $mw = $self-middleware;
my $transmit_sub = sub {
my $response = eval {
...
-  $self-transmit_request_by_transport($request)
+  $mw-($self, $request);
}
...
}

The middleware attribute would default to:

sub { shift-transmit_request_by_transport(@_) }

new middleware layer would be added by doing:

my $mw = $self-middleware;
$self-middleware( sub {
my ($self, $request) = @_;
...do something with $request...
my $response = $mw-($self, $request);
...do something with $response...
return $response;
} );

[This is all off-the-top-of-my-head, and I'm not very familar with Plack
internals so I may well be missing important issues.]

Then we just need a way to add middlewares via the environment.

I'd be delighted if you would work up a patch to add that to the DBI.

Your module could then use this mechanism. If you wanted to release it
to CPAN then a name like DBDx::GoferMiddleware::FOO would be good.
(Note the DBDx not DBIx since this is client-side. We may well end up
with server-side gofer middlewares as well.)

Tim.

I have attached the current version of the module file with some 
 documentation from my intial post
included below (I've changed my mind about the attribute naming and 
 currently am going for
transform=modulename and method=methodname for the module/method that 
 implements the transformation).  I
will of course add unit tests and everything that goes with a proper CPAN 
 release, but I would be
grateful for some quick feedback.
 
Regards
Andrew
 
 Original Message 
 
Subject: Idea for a Gofer transport for translating SQL - primarily 
 intended for testing
   Date: Thu, 08 Sep 2011 11:16:41 +0100
   From: Andrew Ford [1]a.f...@ford-mason.co.uk
 To: [2]dbi-users@perl.org
 
I have a software system that I have to test that uses a very large 
 database accessed exclusively with
stored procedures.  It is a pain setting up a test database for simple 
 unit tests of the Perl code (I am
not trying to test the stored procedures).
 
What I want is to be able to do is set up a simple database (SQLite) 
 quickly to use instead, but of
course there is the issue of the stored procedures being database-specific 
 - but then for testing
purposes most of the stored procedures could be represented by simple SQL 
 statements.
 
What I have done as a proof of concept is to take the 
 DBD::Gofer::Transport::null and create a new
module that maps the stored procedures into simple SQL statements and then 
 executes them in-process
against the test database
 
The code is pretty simple and included below.  Currently it has a map of 
 stored procedure
transformations hard-coded in the code and assumes ODBC style stored 
 procedure call syntax, but I was
thinking of allowing a mapping module to be specified as an attribute in 
 the DSN so that the DSN would
be specified like:
 
  
 dbi:Gofer:transport=adaptor;mapper=My::Mapping::Module;dsn=dbi:SQLite:test.db
 
Overriding the system's DSN then causes the system to use my test 
 database, transparently doing the
appropriate mappings.
 
I would appreciate any comments, suggestions, and thoughts as to whether 
 this would be a useful addition
to CPAN.
 
The immediate issue that I see is: what would the interface to the mapping 
 module be (probably the
module should provide a single function that takes a request object and 
 either modifies it or returns a
new request object).
 
Andrew
 
current proof-of-concept code:
 
  package DBD::Gofer::Transport::adaptor;
 
  use strict;
  use warnings;
 
  use DBI::Gofer::Execute;
 
  use base qw(DBD::Gofer::Transport::Base);
 
  __PACKAGE__-mk_accessors( qw( pending_response transmit_count ) );
 
  # Lookup table for stored procedure transformations
 
  my %stored_proc_transform
  = (
 fetch_user = 'select * from user where email

(Fwd) Sybase 15.0

[Tim Bunce]

- Forwarded message from Mackin, Thomas E. thomas.mac...@lfg.com -

Date: Fri, 19 Aug 2011 14:33:11 -0400
From: Mackin, Thomas E. thomas.mac...@lfg.com
To: tim.bu...@pobox.com
Subject: Sybase 15.0 

   Tim,

   We migrated our Sybase database (AIX) to 15.0.2 about 2 years ago.  We also 
use Open Client 15.0 and
   everything works, mostly.  We have been butting up against the 30 character 
limit for object names when
   running scripts through Perl (5.6) ever since.  Most of the time we simply 
rename things to be 30
   characters or less.  This is now becoming somewhat of a pain.  Is it 
possible to recompile/tweak/modify
   something in the Perl DBI code to get around this?  Keep in mind that I am 
NOT a Perl developer (he
   left!) but am tasked with trying to get this fixed.  We found some C code 
that uses Sybase.h, and we
   assume that somewhere in all that is the datatype restriction that limits 
the object names to 30
   characters. Can a newer header file be used to recompile the dll or am I 
barking up the wrong tree?

   Any help or direction you could give us would be great.  Surely someone has 
Sybase 15.0 and Perl 5.6
   working with long object names...

   Thanks,
   Tom Mackin
   ---
   Lincoln Financial Group

   Investments, IA, and Risk Management IT

   Application Systems Analysis  Programming Lead
   [1]thomas.mac...@lfg.com
   260.455.1466
   Mailstop: 2C12

   Notice of Confidentiality: **This E-mail and any of its attachments may 
contain
   Lincoln National Corporation proprietary information, which is privileged, 
confidential,
   or subject to copyright belonging to the Lincoln National Corporation family 
of
   companies. This E-mail is intended solely for the use of the individual or 
entity to
   which it is addressed. If you are not the intended recipient of this E-mail, 
you are
   hereby notified that any dissemination, distribution, copying, or action 
taken in
   relation to the contents of and attachments to this E-mail is strictly 
prohibited
   and may be unlawful. If you have received this E-mail in error, please 
notify the
   sender immediately and permanently delete the original and any copy of this 
E-mail
   and any printout. Thank You.**

References

   Visible links
   1. mailto:thomas.mac...@lfg.com

- End forwarded message -

(Fwd) Question about DBD::Oracle

[Tim Bunce]

- Forwarded message from Sumiya sumiy...@tradewintech.co.jp -

Date: Wed, 16 Mar 2011 09:19:15 +0900
From: Sumiya sumiy...@tradewintech.co.jp
To: tim.bu...@pobox.com
Subject: Question about DBD::Oracle

Dear Mr. Tim Bunce,

I would like to ask you about DBD::Oracle.
Would you tell me what makes difference behaviourbetween
OCI 8 and  OCI 9.2 on ORA_VARCHAR2?
Is is a bug of DBD or OCI?
Please explain more detail.


http://search.cpan.org/~pythian/DBD-Oracle-1.27/Oracle.pm#Connect_Attributes

ORA_VARCHAR2
Oracle clients using OCI 8 will strip trailing spaces and allow embedded
\0 bytes. Oracle clients using OCI 9.2 do not strip trailing spaces and
allow embedded \0 bytes. This is the normal default placeholder type.


Yours faithfully,

Kiyoshi Sumiya,







- End forwarded message -

(Fwd) Re: DBD::Oracle Slow cursors

[Tim Bunce]

- Forwarded message from P S Jameel Ahamed jaha...@idexcel.com -

Date: Tue, 17 May 2011 16:11:04 +0100
From: P S Jameel Ahamed jaha...@idexcel.com
To: tim.bu...@pobox.com
Subject: Re: DBD::Oracle Slow cursors
X-Mailer: Microsoft Office Outlook 11

   HI Tim,



   We are facing huge issues with the SYS_refcurors of oracle 10G when 
returning from Stored procedure. Is
   there any solution you found for the issue?



   Many Thanks in Advance



   Regards

   P S Jameel Ahamed

   Confidentiality Notice: This e-mail message, including any attachments, is 
for the sole use of the
   intended recipient(s) and may contain confidential and privileged 
information. Any unauthorized review,
   use, disclosure or distribution is prohibited. If you are not the intended 
recipient, please contact the
   sender by reply e-mail and destroy all copies of the original message

- End forwarded message -