Fwd: please help with DBD-Oracle-1.76
> 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
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
- 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
=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
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
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
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
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
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
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
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 > 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
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
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
- Forwarded message from Gowtham - Date: Mon, 6 Mar 2017 15:40:59 -0600 From: Gowtham 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
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" ],
>
(Fwd) RE: Issues with Oracle DBD in Cygwin
- 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. 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
Re: sth->prepare() setting Active?
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?
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
- 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: Platform: osname=cygwin, osvers=2.5.1(0.29753), archname=cygwin-thread-multi uname='cygwin_nt-6.3 cygwin 2.5.1(0.29753) 201
Re: DBD::XML
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
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
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
On Sat, Aug 20, 2016 at 08:32:09PM -0400, Nigel Horne wrote:
> On 19/8/16 11:51, Tim Bunce wrote:
> > 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:
> > >
> > >
> > >
> > >
> > > Nigel Horne
> > > n...@bandsman.co.uk
> > >
> > >
> > > A N Other
> > > nob...@example.com
> > >
> > >
> > Does that format ('table', 'row', 'id') correspond with a known XML Schema?
>
> Nope, that's me creating random test data to poke around.
:)
> > > use DBD::XML;
> > (Ideally users shouldn't need to use the driver module explicitly.)
>
> I'm assuming after registration that would go away, or am I wrong?
It's not needed now or after registration. (The only time importing a
driver would be useful is if there's a need to import something from
the module, like a bunch of constants.)
> > > 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'?
>
> "Pinched from" to get a bootstrap while I'm developing before registration.
:)
> > Are any other XML Schema supported, or supportable?
> I hope so, once I'm ready to create more test data beyond the trivial stuff
> I'm using to get started.
> > 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?
> I'm hoping to do chunk by chunk, but that's not done yet.
> > In other words, can it read files larger than the available memory?
> > (Not related to the naming, just curious :)
> I really hope so, but not yet. I need to walk before I can run :-)
Understood, though I'd caution that it's worth thinking about as these
things may require a different kind of architecture under the hood.
Anyway, back to the topic of naming... I'd suggest something like
DBD::XMLSimpleTable which would have a corresponding prefix of 'xmlst_'.
Sound ok?
Tim.
Re: DBD::XML
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
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
- 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
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
> Cc: dbi-users@perl.org
> Subject: Re: suppress quoting in prepared sql
>
>
> > On Apr 5, 2016, at 11:55 AM, Bruce Ferrell 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&d=CwIF-g&c=MOptNlV
> >> >tIETeDALC_lULrw&r=rwT9R07bCzfhX6apOj8NoPX-TbEkSSLuFkjri49xQ-0&m=QpMl
> >> >4dk0ZSYHx2vhZSJDCeS1tdTQ9Z8GWCyZqgIjc28&s=2uZZNLLOkgh5xJfTn_SVli361r
> >> >ZOaGOrDxGPv_yVwd8&e=
> >>
> >> 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?
> >>
> >>
> >
>
Re: Help with DBD::Oracle install
Hi. I don't provide support for DBD::Oracle directly. Please email dbi-users@perl.org or, if you'd rather not just yet, then something here will probably help: https://www.google.com/search?q=dbd-oracle+/usr/ucb/cc:++language+optional+software+package+not+installed Tim. On Fri, Feb 05, 2016 at 05:40:52PM +, Linn, Steven D CTR (US) wrote: > Hi Tim, > > I have hit a wall as far as trying to get this module to install, so I was > wondering if you might know where I go from here...I am on a Solaris 10 > server running Oracle 10.2.0. When I run 'make', I get the following > error...Any help would be greatly appreciated! > > > [root: ] > rsol0018# ls > CONTRIBUTING.mkd Makefile.PL blib oci.def > CONTRIBUTORS Oracle.c dbdimp.c oci8.c > Changes Oracle.h dbdimp.h ocitrace.h > INSTALL Oracle.xs dbivport.hpm_to_blib > LICENSE Oracle.xsiexamples t > MANIFEST READMEhints typemap > META.json README.help.txt lib > META.yml README.mkdn mk.pm > Makefile Todo mkta.pl > [root: ] > redslsa0sol0018# make > cc -c -I/opt/oracle/product/10.2.0/client_1/rdbms/public > -I/opt/oracle/product/10.2.0/client_1/rdbms/demo > -I/opt/oracle/product/10.2.0/client_1/rdbms/public > -I/opt/oracle/product/10.2.0/client_1/plsql/public > -I/opt/oracle/product/10.2.0/client_1/network/public > -I/usr/perl5/vendor_perl/5.8.4/i86pc-solaris-64int/auto/DBI > -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -D_TS_ERRNO > -DPERL_USE_SAFE_PUTENV -xO3 -xspace -xildoff-DVERSION=\"1.74\" > -DXS_VERSION=\"1.74\" -KPIC > "-I/usr/perl5/5.8.4/lib/i86pc-solaris-64int/CORE" -DUTF8_SUPPORT > -DORA_OCI_VERSION=\"10.2.0.5\" -DORA_OCI_102 Oracle.c > /usr/ucb/cc: language optional software package not installed > *** Error code 1 > make: Fatal error: Command failed for target `Oracle.o' > > Steve Linn > Data Center, SA > Brockwell Technology, Inc > Team IBM (Contractor) > steven.d.linn@mail.mil > 256-313-0363 > DSN:897
(Fwd) perl DBI bug report
- 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
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
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.
On Thu, May 28, 2015 at 03:59:51PM +1200, Duncan McEwan wrote: > On Tue, 26 May 2015 14:13:05 +0100 Tim Bunce 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.
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 = < >#dbi:ODBC:Driver=Firebird;Dbname=/home/image/Documents/Endoscopia/DB/newEndo.fdb;
> >
> >#ib_dialect=3;
> >#DSN
> >my $loc_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
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
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
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
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?
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 ><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
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
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)
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
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(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
eing able to escape placeholders is only of use if the placeholder
syntax has some meaning in the SQL dialect of the database.
For question mark placeholders the only database we're aware of that
assigns a meaning to a question mark is PostgreSQL.
For colon placeholders there are many databases that assign a meaning to
colons as a prefix to name and/or numbers. Individual driver authors
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)
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
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
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 >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
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.
Re: Escaping placeholders
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
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=9506395904&r=1&w=2>,
> <http://173.79.223.25/?l=dbi-dev&m=95077716125217&w=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
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
Escaping placeholders (was: [Dbix-class] Using Postgres JSONB operators in queries)
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
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
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=71&uid=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=71&uid=swg21297335
> 10. http://www.wire2wire.org/
Re: Using DBI::Profile to count and time database calls
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!
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!
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: DBI doc buglet
On Thu, Feb 06, 2014 at 02:22:35PM +1100, Ron Savage wrote: > Hi > > Under connect(), 9 lines down, the text '(i.e., the $data_source prefix > is "dbi::")' should only have 1 colon. You mean here?: https://github.com/perl5-dbi/dbi/blob/3b5b5a5f3ab4797f3e85d2f0ee244897ad47377a/DBI.pm#L2675 If just the driver_name part is empty (i.e., the $data_source prefix is "dbi::"), the environment variable DBI_DRIVER is used. I think it's correct as-is. There's no driver_name between the two colons. Tim.
Re: PostgreSQL driver DBD::Pg releases version 3.0.0
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)
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=entry&id=5570&blog_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
Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
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=entry&id=5570&blog_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}
>� � � � � � � � � �
Announce: DBI 1.631
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
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
- Forwarded message from "Eljot na Onet.pl" -
Date: Sat, 02 Nov 2013 21:11:48 +0100
From: "Eljot na 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 -
Announce: DBI 1.630
file: $CPAN/authors/id/T/TI/TIMB/DBI-1.630.tar.gz size: 588554 bytes md5: 306020fe7b54a53773f54ad581af8c54 =head2 Changes in DBI 1.630 - 28th Oct 2013 NOTE: This release enables PrintWarn by default regardless of $^W. Your applications may generate more log messages than before. Fixed err for new drh to be undef not to 0 [Martin J. Evans] Fixed RT#83132 - moved DBIstcf* constants to util export tag [Martin J. Evans] PrintWarn is now triggered by warnings recorded in methods like STORE that don't clear err RT#89015 [Tim Bunce] Changed tracing to no longer show quote and quote_identifier calls at trace level 1. Changed DBD::Gofer ping while disconnected set_err from warn to info. Clarified wording of log message when err is cleared. Changed bootstrap to use $XS_VERSION RT#89618 [Andreas Koenig] Added connect_cached.connected Callback PR#3 [David E. Wheeler] Clarified effect of refs in connect_cached attributes [David E. Wheeler] Extended ReadOnly attribute docs for when the driver cannot ensure read only [Martin J. Evans] Corrected SQL_BIGINT docs to say ODBC value is used PR#5 [ilmari] There was no DBI 1.629 release. =cut Enjoy! Tim.
Re: Please test DBI-1.628_50 re change in PrintWarn
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
Please test DBI-1.628_50 re change in PrintWarn
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
- Forwarded message from Yonatan Sisay - Date: Sat, 28 Sep 2013 01:25:04 -0700 From: Yonatan Sisay 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
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
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 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
- Forwarded message from reist87 - Date: Mon, 26 Aug 2013 17:31:11 +0600 From: reist87 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
On Sun, Aug 25, 2013 at 01:38:57PM +0200, David E. Wheeler wrote:
> On Aug 24, 2013, at 9:40 PM, Vincent Veyron 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
On Thu, Aug 01, 2013 at 10:28:41AM +0300, Gabor Szabo wrote:
> On Wed, Jul 31, 2013 at 7:39 PM, Meir Guttman 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
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
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
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?
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
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.
Announce: DBI 1.623
file: $CPAN/authors/id/T/TI/TIMB/DBI-1.623.tar.gz size: 596517 bytes md5: b45654dca3b495f3d496c359f0029d96 =head2 Changes in DBI 1.623 (svn r15547) 2nd Jan 2012 Fixed RT#64330 - ping wipes out errstr (Martin J. Evans). Fixed RT#75868 - DBD::Proxy shouldn't call connected() on the server. Fixed RT#80474 - segfault in DESTROY with threads. Fixed RT#81516 - Test failures due to hash randomisation in perl 5.17.6 thanks to Jens Rehsack and H.Merijn Brand and feedback on IRC Fixed RT#81724 - Handle copy-on-write scalars (sprout) Fixed unused variable / self-assignment compiler warnings. Fixed default table_info in DBI::DBD::SqlEngine which passed NAMES attribute instead of NAME to DBD::Sponge RT72343 (Martin J. Evans) Corrected a spelling error thanks to Chris Sanders. Corrected typo in DBI->installed_versions docs RT#78825 thanks to Jan Dubois. Refactored table meta information management from DBD::File into DBI::DBD::SqlEngine (H.Merijn Brand, Jens Rehsack) Pevent undefined f_dir being used in opendir (H.Merijn Brand) Added logic to force destruction of children before parents during global destruction. See RT#75614. Added DBD::File Plugin-Support for table names and data sources (Jens Rehsack, #dbi Team) Added new tests to 08keeperr for RT#64330 thanks to Kenichi Ishigaki. Added extra internal handle type check, RT#79952 thanks to Reini Urban. Added cubrid_ registered prefix for DBD::cubrid, RT#78453 Removed internal _not_impl method (Martin J. Evans). NOTE: The "old-style" DBD::DBM attributes 'dbm_ext' and 'dbm_lockfile' have been deprecated for several years and their use will now generate a warning. =cut Enjoy! Many thanks to all those who have contributed to the DBI and drivers this year, especially Jens Rehsack, H.Merijn Brand and Martin J. Evans. Happy New Year. Tim.
Re: Prepared statements- variable number of placeholders- SOLVED
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 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
> >
>
Prepared statements- variable number of placeholders
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?
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
- Forwarded message from Rune Henssel - Date: Tue, 28 Aug 2012 13:03:08 +0200 From: Rune Henssel 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
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.
Re: make test hangs when re-installing DBI under 64-bit
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: Error running "make" on AIX 5.3
The =encoding directive is a standard part of perl pod
http://perldoc.perl.org/5.8.9/perlpod.html#Formatting-Codes
I don't know why it's causing a problem for you. It works for
most people with perl 5.8.8.
http://matrix.cpantesters.org/?dist=DBI%201.622;reports=1;perl=5.8.8
The quick fix is to just delete the =encoding line.
Tim.
On Mon, Aug 13, 2012 at 10:15:21PM -0400, Martin Gainty wrote:
>
> Looks like a comment
> http://fossies.org/dox/DBI-1.622/Gofer_8pm_source.html
>
> what happens if you wrap the comment in a comment block?
> /* =encoding ISO8859-1 */
>
> > Date: Mon, 13 Aug 2012 13:44:29 -0600
> > From: ha...@persoftware.com
> > To: dbi-users@perl.org
> > Subject: Error running "make" on AIX 5.3
> >
> > I have been unable to install DBI on our AIX 5.3 using either CPAN ("out
> > of memory") or the "perl Makefile.PL" method. I've read the entire
> > thread started by Don Walters on this subject begun on 3 Aug 2012, and
> > it looks like his problem boiled down to his cc_r being linked to gcc.
> > Mine is linked to xlc, as it should be. My perl is 5.8.8. The results
> > from my Makefile.PL run are in the attached file "makefile.tst". They
> > look normal to me. The installation fails during "make", which dies
> > with the error "lib/DBD/Gofer.pm:841: Unknown command paragraph
> > "=encoding ISO8859-1""
ANNOUNCE: DBI 1.622
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
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
> wrote:
>
> > 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
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
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
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 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
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
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
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
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.
ANNOUNCE: DBI 1.617
file: $CPAN/authors/id/T/TI/TIMB/DBI-1.617.tar.gz
size: 579214 bytes
md5: d59b16a1cf06a76154234287509882b9
=head2 Changes in DBI 1.617 (svn r15107) 30th January 2012
NOTE: The officially supported minimum perl version will change
from perl 5.8.1 (2003) to perl 5.8.3 (2004) in a future release.
(The last change, from perl 5.6 to 5.8.1, was announced
in July 2008 and implemented in DBI 1.611 in April 2010.)
Fixed ParamTypes example in the pod (Martin J. Evans)
Fixed the definition of ArrayTupleStatus and remove confusion over
rows affected in list context of execute_array (Martin J. Evans)
Fixed sql_type_cast example and typo in errors (Martin J. Evans)
Fixed Gofer error handling for keeperr methods like ping (Tim Bunce)
Fixed $dbh->clone({}) RT73250 (Tim Bunce)
Fixed is_nested_call logic error RT73118 (Reini Urban)
Enhanced performance for threaded perls (Dave Mitchell, Tim Bunce)
Enhanced and standardized driver trace level mechanism (Tim Bunce)
Removed old code that was an inneffective attempt to detect
people doing DBI->{Attrib}.
Clear ParamValues on bind_param param count error RT66127 (Tim Bunce)
Changed DBI::ProxyServer to require DBI at compile-time RT62672 (Tim Bunce)
Added pod for default_user to DBI::DBD (Martin J. Evans)
Added CON, ENC and DBD trace flags and extended 09trace.t (Martin J. Evans)
Added TXN trace flags and applied CON and TXN to relevant methods (Tim Bunce)
Added some more fetchall_arrayref(..., $maxrows) tests (Tim Bunce)
Clarified docs for fetchall_arrayref called on an inactive handle.
Clarified docs for clone method (Tim Bunce)
Added note to DBI::Profile about async queries (Marcel Grünauer).
Reserved spatialite_ as a driver prefix for DBD::Spatialite
Reserved mo_ as a driver prefix for DBD::MO
Updated link to the SQL Reunion 95 docs, RT69577 (Ash Daminato)
Changed links for DBI recipes. RT73286 (Martin J. Evans)
=cut
Many thanks to all those who contributed to this release.
Enjoy!
Tim.
Re: DBD-Oracle - obtaining OCI handles from $dbh
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: DBD-Oracle - obtaining OCI handles from $dbh
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: DBI-Users> RE: DBD-Oracle - obtaining OCI handles from $dbh
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++ o
Re: Quoting of values in arrays in broken in DBD::Pg, can you help fix it? ( CPAN RT#58552 ) [SumsaultRT #9386]
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: Oracle and Two Phase commit with Perl?
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.
