Re: DBI and DBF Files
Samuel_Zheng wrote: Hi Samuel CPAN says: http://search.cpan.org/~janpaz/DBD-XBase-0.241/ I use this module successfully. -- Ron Savage [EMAIL PROTECTED] http://savage.net.au/
DBI and DBF Files
Hi Gurus, I need your help. I am using active perl 5.8.8, windowsXP second edition. I am trying to access an old app by using perl and odbc. I created a system DSN in ODBC data source administrator and I tested by using crstal report writer. Things are fine. I use DBI (1.58 & dbd-odbc is 1.13) through this DSN(odbc) to read an old dBaseIII file, only one record can be retrived while there are two in the file. The sql is the following: $sql="Select size, color, qty, totship from smtran02 where sono='S9190' order by size, color"; $dbh=DBI->connect($dsn,$user,$pswrd,$driver,{RaiseError=>1}) or die "Database connection not made: $DBI::errstr"; $sth = $dbh->prepare($sql); $sth->execute(); do you know any thing about it or any idea how to access an old dBaseIII file? Thanks. Samuel Zheng
Re: looks like one database from the outside, but it's two inside
And unfortunately I don't know enough about MySQL to be more helpful. Database links may be an Oracle-only thing. On 7/19/07, Theron Stanford <[EMAIL PROTECTED]> wrote: This sounds like a good idea. I guess I just don't know enough about databases yet to know how to do this linking. Mostly I just know about how to get DBI to interact with MySQL. I should mention that I'd also like to be able to make some rules in the "real" database temporarily disappear while testing, but I fear this will make things even more complicated. On 7/19/07, Sven Miller <[EMAIL PROTECTED]> wrote: > I would consider setting up the test database to consist of a database > link to the "real" database, along with views for all the tables. > Then on those tables where I wanted to interject some data, I would > create test-data tables and alter the views to merge the data. > > Then switching from "test" to "real" is a matter of changing where you > connect to. Alternately, you could change the views to only > incorporate test-data if a certain flag is set in yet another test > table. >
Re: looks like one database from the outside, but it's two inside
This sounds like a good idea. I guess I just don't know enough about databases yet to know how to do this linking. Mostly I just know about how to get DBI to interact with MySQL. I should mention that I'd also like to be able to make some rules in the "real" database temporarily disappear while testing, but I fear this will make things even more complicated. On 7/19/07, Sven Miller <[EMAIL PROTECTED]> wrote: I would consider setting up the test database to consist of a database link to the "real" database, along with views for all the tables. Then on those tables where I wanted to interject some data, I would create test-data tables and alter the views to merge the data. Then switching from "test" to "real" is a matter of changing where you connect to. Alternately, you could change the views to only incorporate test-data if a certain flag is set in yet another test table.
Re: looks like one database from the outside, but it's two inside
I would consider setting up the test database to consist of a database link to the "real" database, along with views for all the tables. Then on those tables where I wanted to interject some data, I would create test-data tables and alter the views to merge the data. Then switching from "test" to "real" is a matter of changing where you connect to. Alternately, you could change the views to only incorporate test-data if a certain flag is set in yet another test table. On 7/19/07, Theron Stanford <[EMAIL PROTECTED]> wrote: Thanks to all, and apologies to all. I should have waited another day to flesh this out before sending, but I was trying to take advantage of living on the west coast of the USA to send this out last night and come to work this morning to find my inbox teeming with lots of interesting ideas. Dumb, dumb, dumb (and selfish). On 7/19/07, Tim Bunce <[EMAIL PROTECTED]> wrote: > > It's not really clear what you're trying to do. Suppose I have a database with a table with two columns, 'key' and 'value'. (This is a simplification, of course.) The contents of this database determine how my software interacts. The database is read-only, as far as the software is concerned; this should make the implementation simpler. Now, suppose I'd like to test out the result of adding or updating some new key-value pairs in this database. What I'd like to do is have a local database table to put these temporary key-value pairs in, and have my software obtain a $dbh that makes it transparent to the software that it's really looking at two tables, mixing their data together as one. By the way, when I say "my software", I mean software written by the company that isn't really amenable to changing, which is why I thought of the DBI_AUTOPROXY approach (see below). But I could hack a local copy of it if necessary. > From what you say below > it seems that you just want to "just change the env var to switch > between servers". That's relatively simple but doesn't seem to match > what you said above. What I intended was that I could use the environment variable to switch between "normal", using just the one database, or "test", adding in the data from the new database as well. Since I usually have more than one "test" database, having a switch would really help. > > My thoughts have turned to something like DBD::Proxy, because then I > > could just change the environment variable DBI_PROXY when needed to > > switch between servers. > > Perhaps you could just use the DBI_AUTOPROXY env var to force a > different DSN without using DBD::Proxy. Although I got the name of the environment variable wrong, I'm glad to see you caught what I had in mind and seem to think it's a possibility. The trouble is determining what this different DSN would be. I don't know of any DBDs that act the way I have envisioned above. > DBD::Multiplex may be worth exploring. (I have a modified and more > flexible version I worked on a couple of years ago that never quite got > polished up and released. It should probably be renamed as it's > slightly incompatible with the old one. Volunteers welcome.) I did take a look at DBD::Multiplex, but it struck me that it was intended for situations in which there are multiple servers with duplicate contents: each database has the same data in it, so you can read any server you want, and a write operation propagates that change to each of the servers, keeping them in sync. The situation I envision is of multiple servers whose contents are not the same. But I'll take another look at the module if you think it warrants it. Theron
Re: looks like one database from the outside, but it's two inside
Thanks to all, and apologies to all. I should have waited another day to flesh this out before sending, but I was trying to take advantage of living on the west coast of the USA to send this out last night and come to work this morning to find my inbox teeming with lots of interesting ideas. Dumb, dumb, dumb (and selfish). On 7/19/07, Tim Bunce <[EMAIL PROTECTED]> wrote: It's not really clear what you're trying to do. Suppose I have a database with a table with two columns, 'key' and 'value'. (This is a simplification, of course.) The contents of this database determine how my software interacts. The database is read-only, as far as the software is concerned; this should make the implementation simpler. Now, suppose I'd like to test out the result of adding or updating some new key-value pairs in this database. What I'd like to do is have a local database table to put these temporary key-value pairs in, and have my software obtain a $dbh that makes it transparent to the software that it's really looking at two tables, mixing their data together as one. By the way, when I say "my software", I mean software written by the company that isn't really amenable to changing, which is why I thought of the DBI_AUTOPROXY approach (see below). But I could hack a local copy of it if necessary. From what you say below it seems that you just want to "just change the env var to switch between servers". That's relatively simple but doesn't seem to match what you said above. What I intended was that I could use the environment variable to switch between "normal", using just the one database, or "test", adding in the data from the new database as well. Since I usually have more than one "test" database, having a switch would really help. > My thoughts have turned to something like DBD::Proxy, because then I > could just change the environment variable DBI_PROXY when needed to > switch between servers. Perhaps you could just use the DBI_AUTOPROXY env var to force a different DSN without using DBD::Proxy. Although I got the name of the environment variable wrong, I'm glad to see you caught what I had in mind and seem to think it's a possibility. The trouble is determining what this different DSN would be. I don't know of any DBDs that act the way I have envisioned above. DBD::Multiplex may be worth exploring. (I have a modified and more flexible version I worked on a couple of years ago that never quite got polished up and released. It should probably be renamed as it's slightly incompatible with the old one. Volunteers welcome.) I did take a look at DBD::Multiplex, but it struck me that it was intended for situations in which there are multiple servers with duplicate contents: each database has the same data in it, so you can read any server you want, and a write operation propagates that change to each of the servers, keeping them in sync. The situation I envision is of multiple servers whose contents are not the same. But I'll take another look at the module if you think it warrants it. Theron
Re: DBI requires 5.6.0 or 5.6.1?
Paul DuBois wrote: Tim, thanks for clarifying. I was wondering because it wasn't clear to me, in writing about DBI, how to characterize what version of Perl a reader should have. I'll write that 5.6.0 is required but 5.6.1 is preferred. What are you writing about DBI? ; ) Robert
Re: DBI requires 5.6.0 or 5.6.1?
Robert Hicks wrote: Paul DuBois wrote: Tim, thanks for clarifying. I was wondering because it wasn't clear to me, in writing about DBI, how to characterize what version of Perl a reader should have. I'll write that 5.6.0 is required but 5.6.1 is preferred. What are you writing about DBI? ; ) Robert There is a DBI chapter and appendix in the Doorstop. http://www.kitebird.com/mysql-book/
Re: float bug? perl 5.8, DBI and oracle 10.2.0
Hi all! I have started a thread on perl5-porters discussing the same issue. The posts are not yet archived on http://www.nntp.perl.org/group/perl.perl5.porters/ but should appear there quite soon. What this new thread boils down to (so far) is that perl 5.6 uses the native atof to convert from string to float, and this atof in its turn save, alter then restore the FPU flags that tells the microprocessor's FPU which rounding mode it should use when computing a significand. Perl 5.8 on the other hand uses its own implementation of atof, a function called Perl_my_atof2 located in numeric.c. This implementation does not alter the FPU flags. Apparently, one of the oracle libraries (or at least something executed when doing DBI->connect) changes the FPU flags and does not restore them afterward. Hence the change in behavior we observed. Now, part of what I just explained are still just hypothesis, but I am getting pretty sure that it's what's happening. A quick work around consists in recompiling perl 5.8 with the cc/gcc flag "-DUSE_PERL_ATOF=0". The new perl binary obtained in that way uses the system's atof and the string-to-float conversion is not altered anymore by whatever happens during DBI->connect: [HEAD] ~/HEAD/test/t> !1086$ /opt/perl-5.8.8_native_atof/bin/perl 01_test1.t 0 011 100010101001011010010001101001110101110011010001 connecting 0 011 100010101001011010010001101001110101110011010001 Voila! That being said, we have a few possibilities: - restore the FPU flags to the expected default after doing DBI->connect. But it's probably a bad idea since there probably is a good reason why those flags needed to be altered in the first place. - patch perl's Perl_my_atof2 to act as the native atof with respect to FPU flags. That may be a bad idea as well, since it means altering the behavior of perl 5.8. /Erwan
Re: float bug? perl 5.8, DBI and oracle 10.2.0
On Thu, 2007-07-19 at 09:36 -0500, Scott T. Hildreth wrote: > On Thu, 2007-07-19 at 09:42 +0200, Erwan Lemonnier wrote: > > Hi Tim! > > > > > Ah, now it's getting interesting! :) > > > > So I thought :) > > > > > Are the two versions of DBD::Oracle built against the same Oracle > > > version/installation? > > > > No. > > The one used by perl 5.6.2 was built against an oracle 9.2.1.0 (unsure > > of the exact version, but it was a 9.*). The one used by perl 5.8.5 > > and perl 5.8.8 was built against an oracle 10.2.0. > > > > > What differences are there in the configuration of the two perl versions? > > > > a diff between perl -V for my perl 5.6.2 and perl 5.8.8 shows the > > following differences (the other options are in the 'perl -V' I > > included in my very first post): > > > > * 5.6.2: > > osvers=2.6.9-42.0.2.elsmp > > config_args='-Dprefix=/opt/perl-5.6.2' > > useperlio=undef > > ccflags ='-fno-strict-aliasing -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64' > > cppflags='-fno-strict-aliasing' > > libs=-lnsl -lgdbm -ldb -ldl -lm -lc -lcrypt -lutil > > ccdlflags='-rdynamic' > > Compile-time options: USE_LARGE_FILES > > > > * 5.8.8: > > osvers=2.6.9-55.elsmp > > config_args='' > > useperlio=define > > bincompat5005=undef > > ccflags ='-fno-strict-aliasing -pipe -Wdeclaration-after-statement > > -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64' > > cppflags='-fno-strict-aliasing -pipe -Wdeclaration-after-statement > > -I/usr/local/include' > > libs=-lnsl -ldb -ldl -lm -lcrypt -lutil -lc > > gnulibc_version='2.3.4' > > ccdlflags='-Wl,-E' > > Compile-time options: PERL_MALLOC_WRAP USE_LARGE_FILES USE_PERLIO > > > > > > > Take a look at the source for the Perl_sv_2nv() function in sv.c > > > in the two distributions. > > > Also check for differences in the perl config > > > items that impact that code. > > > > Damn. I had a bad feeling I would end up there. > > Ok, I'll put on my deep dive googles and switch on the tolkien > > translator. If you don't hear from me within a week, call the asylum > > to fetch me ;) > > > > > Try it with: > > > > > > my $v1 = "1.73696"; > > > showbin($v1); > > > require DBD::Oracle; > > > my $v2 = "1.73696"; > > > showbin($v2); > > > > I get: > > > > [HEAD] ~/HEAD/test/t/> !1030$ /opt/perl-5.6.2/bin/perl > > 02_test_require_dbd_oracle.t > > 0011100010101001011010010001101001110101110011010001 > > requiring > > 0011100010101001011010010001101001110101110011010001 > > > > [HEAD] ~/HEAD/test/t/> !1031$ /opt/perl-5.8.8/bin/perl > > 02_test_require_dbd_oracle.t > > 0011100010101001011010010001101001110101110011010001 > > requiring > > 0011100010101001011010010001101001110101110011010001 > > > > No difference. What triggered the problem happened during the connect, > > but not during 'require DBD::Oracle'. > > > > > > > I know that on Solaris the act of loading the Oracle library (e.g. > > > libclntsh.so) could suck in other libraries which would cause > > > subtle side effects. > > > > > > The particular case I (vagely) recall related to alarm(). Loading the > > > Oracle library caused the threaded version of alarm() to be loaded and > > > take precedence. So alarm(0) after loading DBD::Oracle wouldn't cancel > > > the alarm($timeout) set before loading it. Fun. I don't recall now > > > if/how that got resolved. > > > > > > Anyway, I mention it because it seems like this is a similar issue. > > > > Agggh. This example gives me goose flesh. I just hope I am not facing > > something like that... > > > > Is there anyone around with access to an oracle database 10.* and > > running perl 5.8.* who could run the test code I provided earlier? The > > one that just connects? It would be interesting to know how strongly > > dependent it is to just my specific setup... > > I know you asked for Oracle 10, but here is the result of > > Perl: 5.008006(i686-linux) > OS : linux (8.0) > DBI : 1.54 > DBD::Sponge : 12.008696 > DBD::Proxy : 0.2004 > DBD::Oracle : 1.19 > > Oracle 9.2.0.1.0 > > bin: 0011100010101001011010010001101001110101110011010001 > connecting > bin: 0011100010101001011010010001101001110101110011010001 ... and bin: 0011100010101001011010010001101001110101110011010001 connecting bin: 001110001010100101101001000110100111010111001101 with Perl: 5.008008(i686-linux) OS : linux (2.6.14.6) DBI : 1.54 DBD::mysql : 3.0002 DBD::Sponge : 12.008696 DBD::SQLite : 1.11 DBD::Proxy : 0.2004 DBD::Oracle : 1.19 connecting to 10.2.0.3.0 or 9.2.0.1.0 > It seems to be the DBD::Oracle built against Oracle 10.x. -- Scott T. Hildreth <[EMAIL PROTECTED]>
Re: float bug? perl 5.8, DBI and oracle 10.2.0
On Thu, 2007-07-19 at 09:42 +0200, Erwan Lemonnier wrote: > Hi Tim! > > > Ah, now it's getting interesting! :) > > So I thought :) > > > Are the two versions of DBD::Oracle built against the same Oracle > > version/installation? > > No. > The one used by perl 5.6.2 was built against an oracle 9.2.1.0 (unsure > of the exact version, but it was a 9.*). The one used by perl 5.8.5 > and perl 5.8.8 was built against an oracle 10.2.0. > > > What differences are there in the configuration of the two perl versions? > > a diff between perl -V for my perl 5.6.2 and perl 5.8.8 shows the > following differences (the other options are in the 'perl -V' I > included in my very first post): > > * 5.6.2: > osvers=2.6.9-42.0.2.elsmp > config_args='-Dprefix=/opt/perl-5.6.2' > useperlio=undef > ccflags ='-fno-strict-aliasing -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64' > cppflags='-fno-strict-aliasing' > libs=-lnsl -lgdbm -ldb -ldl -lm -lc -lcrypt -lutil > ccdlflags='-rdynamic' > Compile-time options: USE_LARGE_FILES > > * 5.8.8: > osvers=2.6.9-55.elsmp > config_args='' > useperlio=define > bincompat5005=undef > ccflags ='-fno-strict-aliasing -pipe -Wdeclaration-after-statement > -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64' > cppflags='-fno-strict-aliasing -pipe -Wdeclaration-after-statement > -I/usr/local/include' > libs=-lnsl -ldb -ldl -lm -lcrypt -lutil -lc > gnulibc_version='2.3.4' > ccdlflags='-Wl,-E' > Compile-time options: PERL_MALLOC_WRAP USE_LARGE_FILES USE_PERLIO > > > > Take a look at the source for the Perl_sv_2nv() function in sv.c > > in the two distributions. > > Also check for differences in the perl config > > items that impact that code. > > Damn. I had a bad feeling I would end up there. > Ok, I'll put on my deep dive googles and switch on the tolkien > translator. If you don't hear from me within a week, call the asylum > to fetch me ;) > > > Try it with: > > > > my $v1 = "1.73696"; > > showbin($v1); > > require DBD::Oracle; > > my $v2 = "1.73696"; > > showbin($v2); > > I get: > > [HEAD] ~/HEAD/test/t/> !1030$ /opt/perl-5.6.2/bin/perl > 02_test_require_dbd_oracle.t > 0011100010101001011010010001101001110101110011010001 > requiring > 0011100010101001011010010001101001110101110011010001 > > [HEAD] ~/HEAD/test/t/> !1031$ /opt/perl-5.8.8/bin/perl > 02_test_require_dbd_oracle.t > 0011100010101001011010010001101001110101110011010001 > requiring > 0011100010101001011010010001101001110101110011010001 > > No difference. What triggered the problem happened during the connect, > but not during 'require DBD::Oracle'. > > > > I know that on Solaris the act of loading the Oracle library (e.g. > > libclntsh.so) could suck in other libraries which would cause > > subtle side effects. > > > > The particular case I (vagely) recall related to alarm(). Loading the > > Oracle library caused the threaded version of alarm() to be loaded and > > take precedence. So alarm(0) after loading DBD::Oracle wouldn't cancel > > the alarm($timeout) set before loading it. Fun. I don't recall now > > if/how that got resolved. > > > > Anyway, I mention it because it seems like this is a similar issue. > > Agggh. This example gives me goose flesh. I just hope I am not facing > something like that... > > Is there anyone around with access to an oracle database 10.* and > running perl 5.8.* who could run the test code I provided earlier? The > one that just connects? It would be interesting to know how strongly > dependent it is to just my specific setup... I know you asked for Oracle 10, but here is the result of Perl: 5.008006(i686-linux) OS : linux (8.0) DBI : 1.54 DBD::Sponge : 12.008696 DBD::Proxy : 0.2004 DBD::Oracle : 1.19 Oracle 9.2.0.1.0 bin: 0011100010101001011010010001101001110101110011010001 connecting bin: 0011100010101001011010010001101001110101110011010001 -- Scott T. Hildreth <[EMAIL PROTECTED]>
Re: looks like one database from the outside, but it's two inside
Hi, What about a round robin dns for the database server? Connect to the server via a dns name that can be round robin to 2 ips. One per database server. The outside host wouldn't know the difference and would simply connect to the dns name and external ip that it was given and your inside host handles which internal ip it belonging to a server it was actually talking to. Just a thought. Michael On Wed, 18 Jul 2007, Theron Stanford wrote: Date: Wed, 18 Jul 2007 22:23:52 -0700 From: Theron Stanford <[EMAIL PROTECTED]> To: dbi-users@perl.org Subject: looks like one database from the outside, but it's two inside Overview: I'm trying to create the situation in the subject line: from the outside, the database handle thinks it's just one database, but inside it's really two databases containing tables with the same schema but not necessarily the same data. Specifics: Where I work we have a web site with content determined jointly by user interaction and by data in various tables in a very large database. I would like to test what would happen if I add other data to the database. Typically what I have to do is copy the relevant parts of the very large database to my box, add in the new data, and test locally. (The very large database is far too large to copy between machines.) There are a number of things that make this setup difficult: 1) Determining what is "relevant" is not always an easy task and is often fraught with errors. 2) The very large database can be modified by many, many other people at will. So, each time I perform a test, I have to go back to the very large database and copy the relevant parts to my box. 3) Handles to the database are created through an intermediary module. So, the call XXX::DBH->new('YYY') (where XXX::DBH is a subclass of DBI::db) returns a $dbh to the correct database (names have been changed to protect the innocent). This module relies on a configuration file to turn 'YYY' into the correct parameters for DBI->connect(); that means I'm always fiddling with the configuration file, depending if I want to use the original very large database or my local one. 4) I'd like to fiddle with the original code as little as possible, including the intermediary module. My thoughts have turned to something like DBD::Proxy, because then I could just change the environment variable DBI_PROXY when needed to switch between servers. But DBI::ProxyServer doesn't seem very easily adaptable to this situation; it seems restricted to the rather specific task of acting as a proxy for another driver. I have also thought about writing a driver from scratch, using SQL::Statement to help out, but that breaks the first rule of driver writing ("Don't!"), and since this is for my job, the second rule ("Don't -- get someone else to do it for you!") isn't really an option. I'm not afraid of putting in the time to do this right, and I'm not trying to get something for free. But I'd like some opinions about what might be the correct approaches to this problem, especially if there's something obvious that I've overlooked. (So OK, I'm trying to get some advice for free :) Thanks. -- ---//--- "He who learns must suffer. And even in our sleep, pain that cannot forget, falls drop by drop upon the heart; and, in our own despair, against our will, comes wisdom to us by the awful grace of God." --- Aeschylus
Re: float bug? perl 5.8, DBI and oracle 10.2.0
Here is the test again. Same DB and same version of DBI, DBD::Oracle and the Oracle client but this time on a Lunix box [EMAIL PROTECTED] wwwtest]$ perl x.pl bin: 0011100010101001011010010001101001110101110011010001 connecting bin: 0011100010101001011010010001101001110101110011010001 [EMAIL PROTECTED] wwwtest]$ [EMAIL PROTECTED] wwwtest]$ perl -V Summary of my perl5 (revision 5.0 version 8 subversion 0) configuration: Platform: osname=linux, osvers=2.6.9-34.0.2.el.jmm1, archname=i386-linux-thread-multi uname='linux roark.whiteboxlinux.org 2.6.9-34.0.2.el.jmm1 #1 fri nov 10 16:42:55 cst 2006 i686 athlon i386 gnulinux ' config_args='-des -Doptimize=-O2 -g -pipe -march=i386 -mcpu=i686 -Dmyhostname=localhost [EMAIL PROTECTED] -Dcc=gcc -Dcf_by=Red Hat, Inc. -Dinstallprefix=/usr -Dprefix=/usr -Darchname=i386-linux -Dvendorprefix=/usr -Dsiteprefix=/usr -Dotherlibdirs=/usr/lib/perl5/5.8.0 -Duseshrplib -Dusethreads -Duseithreads -Duselargefiles -Dd_dosuid -Dd_semctl_semun -Di_db -Ui_ndbm -Di_gdbm -Di_shadow -Di_syslog -Dman3ext=3pm -Duseperlio -Dinstallusrbinperl -Ubincompat5005 -Uversiononly -Dpager=/usr/bin/less -isr' hint=recommended, useposix=true, d_sigaction=define usethreads=define use5005threads=undef useithreads=define usemultiplicity=define useperlio=define d_sfio=undef uselargefiles=define usesocks=undef use64bitint=undef use64bitall=undef uselongdouble=undef usemymalloc=n, bincompat5005=undef Compiler: cc='gcc', ccflags ='-D_REENTRANT -D_GNU_SOURCE -DTHREADS_HAVE_PIDS -DDEBUGGING -fno-strict-aliasing -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm', optimize='-O2 -g -pipe -march=i386 -mcpu=i686', cppflags='-D_REENTRANT -D_GNU_SOURCE -DTHREADS_HAVE_PIDS -DDEBUGGING -fno-strict-aliasing -I/usr/local/include -I/usr/include/gdbm' ccversion='', gccversion='3.2.3 20030502 (Red Hat Linux 3.2.3-54)', gccosandvers='' intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=1234 d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=12 ivtype='long', ivsize=4, nvtype='double', nvsize=8, Off_t='off_t', lseeksize=8 alignbytes=4, prototype=define Linker and Libraries: ld='gcc', ldflags =' -L/usr/local/lib' libpth=/usr/local/lib /lib /usr/lib libs=-lnsl -lgdbm -ldb -ldl -lm -lpthread -lc -lcrypt -lutil perllibs=-lnsl -ldl -lm -lpthread -lc -lcrypt -lutil libc=/lib/libc-2.3.2.so, so=so, useshrplib=true, libperl=libperl.so gnulibc_version='2.3.2' Dynamic Linking: dlsrc=dl_dlopen.xs, dlext=so, d_dlsymun=undef, ccdlflags='-rdynamic -Wl,-rpath,/usr/lib/perl5/5.8.0/i386-linux-thread-multi/CORE' cccdlflags='-fPIC', lddlflags='-shared -L/usr/local/lib' Characteristics of this binary (from libperl): Compile-time options: DEBUGGING MULTIPLICITY USE_ITHREADS USE_LARGE_FILES PERL_IMPLICIT_CONTEXT Locally applied patches: MAINT18379 Built under linux Compiled at Dec 21 2006 18:39:03 @INC: /usr/lib/perl5/5.8.0/i386-linux-thread-multi /usr/lib/perl5/5.8.0 /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.0 /usr/lib/perl5/site_perl /usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.0 /usr/lib/perl5/vendor_perl /usr/lib/perl5/5.8.0/i386-linux-thread-multi /usr/lib/perl5/5.8.0 . [EMAIL PROTECTED] wwwtest]$ - Original Message - From: "John Scoles" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; "Martin Evans" <[EMAIL PROTECTED]> Cc: Sent: Thursday, July 19, 2007 7:56 AM Subject: Re: float bug? perl 5.8, DBI and oracle 10.2.0 Well maybe it is time if piped up. I ran the code in x.pl as well and just to add some more fuel to the fier I ran it in activestate perl on a windows XP box. I will try it on some different boxes as well Here are my results C:\johns\testperl>perl x.pl bin: 001110001010100101101001000110100111010111001101 connecting bin: 001110001010100101101001000110100111010111001101 Now here is what I ran it on select * from v$version Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod PL/SQL Release 10.2.0.3.0 - Production CORE 10.2.0.3.0 Production TNS for Linux: Version 10.2.0.3.0 - Production NLSRTL Version 10.2.0.3.0 - Production and the perl -V Summary of my perl5 (revision 5 version 8 subversion 8) configuration: Platform: osname=MSWin32, osvers=4.0, archname=MSWin32-x86-multi-thread uname='' config_args='undef' hint=recommended, useposix=true, d_sigaction=undef usethreads=define use5005threads=undef useithreads=define usemultiplicity=define useperlio=define d_sfio=undef uselargefiles=define usesocks=undef use64bitint=undef use64bitall=undef uselongdouble=undef usemymalloc=n, bincompat5005=undef Compiler: cc='cl', ccflags ='-nologo -GF -W3 -MD -Zi -DNDEBUG -O1 -DWIN32 -D_CONSOLE -DNO_STRICT -DHAVE_DE S_FCRYPT -DNO_HASH_SEED -DUSE_SITECUS
Re: float bug? perl 5.8, DBI and oracle 10.2.0
Well maybe it is time if piped up. I ran the code in x.pl as well and just to add some more fuel to the fier I ran it in activestate perl on a windows XP box. I will try it on some different boxes as well Here are my results C:\johns\testperl>perl x.pl bin: 001110001010100101101001000110100111010111001101 connecting bin: 001110001010100101101001000110100111010111001101 Now here is what I ran it on select * from v$version Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod PL/SQL Release 10.2.0.3.0 - Production CORE 10.2.0.3.0 Production TNS for Linux: Version 10.2.0.3.0 - Production NLSRTL Version 10.2.0.3.0 - Production and the perl -V Summary of my perl5 (revision 5 version 8 subversion 8) configuration: Platform: osname=MSWin32, osvers=4.0, archname=MSWin32-x86-multi-thread uname='' config_args='undef' hint=recommended, useposix=true, d_sigaction=undef usethreads=define use5005threads=undef useithreads=define usemultiplicity=define useperlio=define d_sfio=undef uselargefiles=define usesocks=undef use64bitint=undef use64bitall=undef uselongdouble=undef usemymalloc=n, bincompat5005=undef Compiler: cc='cl', ccflags ='-nologo -GF -W3 -MD -Zi -DNDEBUG -O1 -DWIN32 -D_CONSOLE -DNO_STRICT -DHAVE_DE S_FCRYPT -DNO_HASH_SEED -DUSE_SITECUSTOMIZE -DPERL_IMPLICIT_CONTEXT -DPERL_IMPLICIT_SYS -DUSE_PERLIO -DPERL_MSVCRT_READFIX', optimize='-MD -Zi -DNDEBUG -O1', cppflags='-DWIN32' ccversion='12.00.8804', gccversion='', gccosandvers='' intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=1234 d_longlong=undef, longlongsize=8, d_longdbl=define, longdblsize=10 ivtype='long', ivsize=4, nvtype='double', nvsize=8, Off_t='__int64', lseeksize=8 alignbytes=8, prototype=define Linker and Libraries: ld='link', ldflags '-nologo -nodefaultlib -debug -opt:ref,icf -libpath:"C:\Perl\lib\CORE" -ma chine:x86' libpth=\lib libs= oldnames.lib kernel32.lib user32.lib gdi32.lib winspool.lib comdlg32.lib advapi32.lib sh ell32.lib ole32.lib oleaut32.lib netapi32.lib uuid.lib ws2_32.lib mpr.lib winmm.lib version.lib od bc32.lib odbccp32.lib msvcrt.lib perllibs= oldnames.lib kernel32.lib user32.lib gdi32.lib winspool.lib comdlg32.lib advapi32.li b shell32.lib ole32.lib oleaut32.lib netapi32.lib uuid.lib ws2_32.lib mpr.lib winmm.lib version.li b odbc32.lib odbccp32.lib msvcrt.lib libc=msvcrt.lib, so=dll, useshrplib=yes, libperl=perl58.lib gnulibc_version='' Dynamic Linking: dlsrc=dl_win32.xs, dlext=dll, d_dlsymun=undef, ccdlflags=' ' cccdlflags=' ', lddlflags='-dll -nologo -nodefaultlib -debug -opt:ref,icf -libpath:"C:\Perl\lib \CORE" -machine:x86' Characteristics of this binary (from libperl): Compile-time options: MULTIPLICITY PERL_IMPLICIT_CONTEXT PERL_IMPLICIT_SYS PERL_MALLOC_WRAP PL_OP_SLAB_ALLOC USE_ITHREADS USE_LARGE_FILES USE_PERLIO USE_SITECUSTOMIZE Locally applied patches: ActivePerl Build 820 [274739] Iin_load_module moved for compatibility with build 806 PerlEx support in CGI::Carp Less verbose ExtUtils::Install and Pod::Find Patch for CAN-2005-0448 from Debian with modifications Rearrange @INC so that 'site' is searched before 'perl' Partly reverted 24733 to preserve binary compatibility 29930 win32.c typo in #define MULTIPLICITY 29868 win32_async_check() can still loop indefinitely 29690,29732 ANSIfy the PATH environment variable on Windows 29689 Add error handling to win32_ansipath 29675 Use short pathnames in $^X and @INC 29607,29676 allow blib.pm to be used for testing Win32 module 29605 Implement killpg() for MSWin32 29598 cwd() to return the short pathname 29597 let readdir() return the alternate filename 29590 Don't destroy the Unicode system environment on Perl startup 29528 get ext/Win32/Win32.xs to compile on cygwin 29509,29510,29511 Move Win32::* functions into Win32 module 29483 Move Win32 from win32/ext/Win32 to ext/Win32 29481 Makefile.PL changes to compile Win32.xs using cygwin 28671 Define PERL_NO_DEV_RANDOM on Windows 28376 Add error checks after execing PL_cshname or PL_sh_path 28305 Pod::Html should not convert "foo" into ``foo'' 27833 Change anchor generation in Pod::Html for '=item item 2' 27832,27847 fix Pod::Html::depod() for multi-line strings 27719 Document the functions htmlify() and anchorify() in Pod::Html 27619 Bug in Term::ReadKey being triggered by a bug in Term::ReadLine 27549 Move DynaLoader.o into libperl.so 27528 win32_pclose() error exit doesn't unlock mutex 27527 win32_async_check() can loop indefinitely 27515 ignore directories when searching @INC 27359 Fix -d:Foo=bar syntax 27210 Fix quote typo in c2ph 27203 Allow compiling swigged C++ cod
Re: looks like one database from the outside, but it's two inside
On Wed, Jul 18, 2007 at 10:23:52PM -0700, Theron Stanford wrote: > Overview: I'm trying to create the situation in the subject line: > from the outside, the database handle thinks it's just one database, > but inside it's really two databases containing tables with the same > schema but not necessarily the same data. It's not really clear what you're trying to do. From what you say below it seems that you just want to "just change the env var to switch between servers". That's relatively simple but doesn't seem to match what you said above. > My thoughts have turned to something like DBD::Proxy, because then I > could just change the environment variable DBI_PROXY when needed to > switch between servers. Perhaps you could just use the DBI_AUTOPROXY env var to force a different DSN without using DBD::Proxy. > I have also thought about writing a driver from scratch, using > SQL::Statement to help out, but that breaks the first rule of driver > writing ("Don't!"), and since this is for my job, the second rule > ("Don't -- get someone else to do it for you!") isn't really an > option. > > I'm not afraid of putting in the time to do this right, and I'm not > trying to get something for free. But I'd like some opinions about > what might be the correct approaches to this problem, especially if > there's something obvious that I've overlooked. (So OK, I'm trying to > get some advice for free :) DBD::Multiplex may be worth exploring. (I have a modified and more flexible version I worked on a couple of years ago that never quite got polished up and released. It should probably be renamed as it's slightly incompatible with the old one. Volunteers welcome.) Tim.
Re: float bug? perl 5.8, DBI and oracle 10.2.0
perl --version This is perl, v5.8.8 built for i486-linux-gnu-thread-multi select * from v$version Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product PL/SQL Release 10.2.0.1.0 - Production CORE10.2.0.1.0 Production TNS for Linux: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production perl x.pl bin: 0011100010101001011010010001101001110101110011010001 connecting bin: 001110001010100101101001000110100111010111001101 Thanks Martin! It does really help! It means you have the same problem as me, and that it is worth tracking it down :) The problem occurs only in seldom cases, with specific numbers, and the error introduced can easily get mixed up with usual floating point precision issues, if not disappear among them, so it's quite likely to pass un-noticed and have no serious consequences...
Re: float bug? perl 5.8, DBI and oracle 10.2.0
Erwan Lemonnier wrote: Hi Tim! Ah, now it's getting interesting! :) So I thought :) Are the two versions of DBD::Oracle built against the same Oracle version/installation? No. The one used by perl 5.6.2 was built against an oracle 9.2.1.0 (unsure of the exact version, but it was a 9.*). The one used by perl 5.8.5 and perl 5.8.8 was built against an oracle 10.2.0. What differences are there in the configuration of the two perl versions? a diff between perl -V for my perl 5.6.2 and perl 5.8.8 shows the following differences (the other options are in the 'perl -V' I included in my very first post): * 5.6.2: osvers=2.6.9-42.0.2.elsmp config_args='-Dprefix=/opt/perl-5.6.2' useperlio=undef ccflags ='-fno-strict-aliasing -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64' cppflags='-fno-strict-aliasing' libs=-lnsl -lgdbm -ldb -ldl -lm -lc -lcrypt -lutil ccdlflags='-rdynamic' Compile-time options: USE_LARGE_FILES * 5.8.8: osvers=2.6.9-55.elsmp config_args='' useperlio=define bincompat5005=undef ccflags ='-fno-strict-aliasing -pipe -Wdeclaration-after-statement -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64' cppflags='-fno-strict-aliasing -pipe -Wdeclaration-after-statement -I/usr/local/include' libs=-lnsl -ldb -ldl -lm -lcrypt -lutil -lc gnulibc_version='2.3.4' ccdlflags='-Wl,-E' Compile-time options: PERL_MALLOC_WRAP USE_LARGE_FILES USE_PERLIO Take a look at the source for the Perl_sv_2nv() function in sv.c in the two distributions. Also check for differences in the perl config items that impact that code. Damn. I had a bad feeling I would end up there. Ok, I'll put on my deep dive googles and switch on the tolkien translator. If you don't hear from me within a week, call the asylum to fetch me ;) Try it with: my $v1 = "1.73696"; showbin($v1); require DBD::Oracle; my $v2 = "1.73696"; showbin($v2); I get: [HEAD] ~/HEAD/test/t/> !1030$ /opt/perl-5.6.2/bin/perl 02_test_require_dbd_oracle.t 0011100010101001011010010001101001110101110011010001 requiring 0011100010101001011010010001101001110101110011010001 [HEAD] ~/HEAD/test/t/> !1031$ /opt/perl-5.8.8/bin/perl 02_test_require_dbd_oracle.t 0011100010101001011010010001101001110101110011010001 requiring 0011100010101001011010010001101001110101110011010001 No difference. What triggered the problem happened during the connect, but not during 'require DBD::Oracle'. I know that on Solaris the act of loading the Oracle library (e.g. libclntsh.so) could suck in other libraries which would cause subtle side effects. The particular case I (vagely) recall related to alarm(). Loading the Oracle library caused the threaded version of alarm() to be loaded and take precedence. So alarm(0) after loading DBD::Oracle wouldn't cancel the alarm($timeout) set before loading it. Fun. I don't recall now if/how that got resolved. Anyway, I mention it because it seems like this is a similar issue. Agggh. This example gives me goose flesh. I just hope I am not facing something like that... Is there anyone around with access to an oracle database 10.* and running perl 5.8.* who could run the test code I provided earlier? The one that just connects? It would be interesting to know how strongly dependent it is to just my specific setup... perl --version This is perl, v5.8.8 built for i486-linux-gnu-thread-multi select * from v$version Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product PL/SQL Release 10.2.0.1.0 - Production CORE10.2.0.1.0 Production TNS for Linux: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production cat x.pl use DBI; sub showbin { print "bin: ".unpack("B70",reverse pack("d",$_[0]))."\n"; } my $v1 = "1.73696"; showbin($v1); print "connecting\n"; my $DBC = DBI->connect("dbi:Oracle:XE",'XXX','YYY', { PrintError=>0, AutoCommit=>0 } ); my $v2 = "1.73696"; showbin($v2); perl x.pl bin: 0011100010101001011010010001101001110101110011010001 connecting bin: 001110001010100101101001000110100111010111001101 Hope this helps. Martin -- Martin J. Evans Easysoft Limited http://www.easysoft.com
Re: DBD::ODBC does not support bind_param_inout?
hu.darren wrote: > Hi, >I have installed the DBD::ODBC from the latest svn. > > when I want execute a procedure like this: > > > my $whoami = ""; > my $csr = $dbh->prepare(q{ > BEGIN > :whoami := PLSQL_EXAMPLE_DARREN.FUNC_NP; > END; > }); I presume since that starts PLSQL you are using Oracle. Which Oracle ODBC driver are you using? What does the procedure look like? Are you sure this is a procedure - procedures don't usually return values (especially in oracle). The ODBC syntax to call a procedure is: {call proc_name(?,?...)} You are better using that. Having said procedures don't usually return values MS SQL Server can return an integer in which case you can use: {? = call procname(?,?,...)} > $csr->bind_param_inout(":whoami", \$whoami, 20); > $csr->execute; > print "Your database user name is $whoami\n"; > $dbh->disconnect; > -- > > but I failed with: > > > /usr/bin/perl -w /home/darren/perl/dbitest.pl > Can't bind unknown placeholder ':whoami' at /home/darren/perl/dbitest.pl > line 90. > - > > I have both used $dbh->{odbc_ignore_named_placeholders} = 1 and > $dbh->{odbc_ignore_named_placeholders} = 1 These are the same aren't they! > Any suggestions? Why would you set odbc_ignore_named_placeholders - you are using named placeholders so don't set it. Martin -- Martin J. Evans Easysoft Limited http://www.easysoft.com
Re: float bug? perl 5.8, DBI and oracle 10.2.0
> Take a look at the source for the Perl_sv_2nv() function in sv.c > in the two distributions. > Also check for differences in the perl config > items that impact that code. Back from a first dive. There are significant differences between 5.6.2 and 5.8.8 with respect to how PVs are translated to NVs. The function Perl_sv_2nv differs strongly between the 2 versions. It might be enough to say that it has doubled in size, contain many more branches (and a few more comments ;). One thing attracted my attention (but it might be a false lead and I may be wrong): perl 5.6.2 ultimately converts a string of chars to a double (64 bits on my hardware) with one of the functions strtold, atolf or sscanf, depending on what's available. In 5.8.8, perl calls atof or (and that's the default) an own implementation of atof called Perl_my_atof2, located in numeric.c. So in my case, it seems 5.6.2 gets its string-to-float answer from the standard linux libraries, while 5.8.* gets it from its own algorithm. Fishy smell, ain't it? We are kind of living the realm of DBI here, so I will continue this thread on the perl-porters mailing list and see what we can get there. /Erwan
Re: float bug? perl 5.8, DBI and oracle 10.2.0
Hi Tim! Ah, now it's getting interesting! :) So I thought :) Are the two versions of DBD::Oracle built against the same Oracle version/installation? No. The one used by perl 5.6.2 was built against an oracle 9.2.1.0 (unsure of the exact version, but it was a 9.*). The one used by perl 5.8.5 and perl 5.8.8 was built against an oracle 10.2.0. What differences are there in the configuration of the two perl versions? a diff between perl -V for my perl 5.6.2 and perl 5.8.8 shows the following differences (the other options are in the 'perl -V' I included in my very first post): * 5.6.2: osvers=2.6.9-42.0.2.elsmp config_args='-Dprefix=/opt/perl-5.6.2' useperlio=undef ccflags ='-fno-strict-aliasing -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64' cppflags='-fno-strict-aliasing' libs=-lnsl -lgdbm -ldb -ldl -lm -lc -lcrypt -lutil ccdlflags='-rdynamic' Compile-time options: USE_LARGE_FILES * 5.8.8: osvers=2.6.9-55.elsmp config_args='' useperlio=define bincompat5005=undef ccflags ='-fno-strict-aliasing -pipe -Wdeclaration-after-statement -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64' cppflags='-fno-strict-aliasing -pipe -Wdeclaration-after-statement -I/usr/local/include' libs=-lnsl -ldb -ldl -lm -lcrypt -lutil -lc gnulibc_version='2.3.4' ccdlflags='-Wl,-E' Compile-time options: PERL_MALLOC_WRAP USE_LARGE_FILES USE_PERLIO Take a look at the source for the Perl_sv_2nv() function in sv.c in the two distributions. Also check for differences in the perl config items that impact that code. Damn. I had a bad feeling I would end up there. Ok, I'll put on my deep dive googles and switch on the tolkien translator. If you don't hear from me within a week, call the asylum to fetch me ;) Try it with: my $v1 = "1.73696"; showbin($v1); require DBD::Oracle; my $v2 = "1.73696"; showbin($v2); I get: [HEAD] ~/HEAD/test/t/> !1030$ /opt/perl-5.6.2/bin/perl 02_test_require_dbd_oracle.t 0011100010101001011010010001101001110101110011010001 requiring 0011100010101001011010010001101001110101110011010001 [HEAD] ~/HEAD/test/t/> !1031$ /opt/perl-5.8.8/bin/perl 02_test_require_dbd_oracle.t 0011100010101001011010010001101001110101110011010001 requiring 0011100010101001011010010001101001110101110011010001 No difference. What triggered the problem happened during the connect, but not during 'require DBD::Oracle'. I know that on Solaris the act of loading the Oracle library (e.g. libclntsh.so) could suck in other libraries which would cause subtle side effects. The particular case I (vagely) recall related to alarm(). Loading the Oracle library caused the threaded version of alarm() to be loaded and take precedence. So alarm(0) after loading DBD::Oracle wouldn't cancel the alarm($timeout) set before loading it. Fun. I don't recall now if/how that got resolved. Anyway, I mention it because it seems like this is a similar issue. Agggh. This example gives me goose flesh. I just hope I am not facing something like that... Is there anyone around with access to an oracle database 10.* and running perl 5.8.* who could run the test code I provided earlier? The one that just connects? It would be interesting to know how strongly dependent it is to just my specific setup...
Re: float bug? perl 5.8, DBI and oracle 10.2.0
Hi Jonathan, Silly question time - I assume that if you don't includes the DBI->connect line, then the two invocations of showbin produce the same output in both versions of Perl. It does. And in that case, the only float representation obtained is: 0011100010101001011010010001101001110101110011010001 A wise programmer once said 'moving floating point numbers is like moving sand piles; every time you do, you lose a little sand and you pick up a little dirt'. Sure. And others have made this point on this list and it is true. But in that particular case we are doing the *same operation* before and after, on the same start data. I just want to emphasize that the issue discussed in this thread is not related to floating point precision, at least not in the meaning of the usual loss of precisions following conversion to floating point and arithmetic operations on them. This issue is about how perl alters its behavior when converting a string to a native float, after some oracle modules have been loaded. See Tim's latest email for hints of the details behind this conversion.