Re: Perl/DBI/ORACLE Troubles. Help, please!
Unfortunately, I do need to use the iAS for now. We don't have root privileges on the box, changes to the system require that we contact IT and have to put through a service request and all that stuff. We have a Perl install on the box, but we're using the iAS libraries to talk to everything else. There's an eventual upgrade to Oracle 9.2 coming, but not for a while. It would be nice to solve this situation so if it comes up again, I can handle it on the fly. Thanks for your consideration, Jared. Christopher R. Marbach Data Quality Coordinator Pharmacy Analytical Services Kaiser Permanente 8-345-2485 (562) 401-2485 Jared Still [EMAIL PROTECTED] 09/06/2004 09:27 PM To: Chris R Marbach/CA/[EMAIL PROTECTED] cc: DBI List [EMAIL PROTECTED] Subject:Re: Perl/DBI/ORACLE Troubles. Help, please! On Wed, 2004-09-01 at 17:46, [EMAIL PROTECTED] wrote: Hello, I'm new to the list and am experiencing an issue that I hope some of you all have dealt with previous and can lend me some advise on.. or supply a possible fix. Perl 5.6.1 with DBI module 1.32 operating on AIX 4.3. I'm attempting to query an Oracle 9.0.1.0.0 database through the perl installed on the Oracle 9.0.3 iAS. Through Perl, I can insert and select counts with no problem. However, when I try to select values from fields, I'm getting: DBD::Oracle::st execute failed: ORA-03106: fatal two-task communication protocol error (DBD ERROR: OCIStmtExecute) at tstora2.pl line 29. Though I haven't looked in depth at the rest of your post, I would still offer this advice: don't use the Perl supplied by Oracle unless you are required to do so because you are using iAS. You're much better off installing your own version of Perl. That allows you to stay current without testing all the other stuff that would be dependent on the Oracle version. On unix this is fairly simple. Get the source and build it either in ~oracle or build one in each of your ORACLE_HOME, say $ORACLE_HOME/myperl, or some such thing. HTH Jared
UnixODBC
Hello there, one brief question: needs UnixODBC the DBI or is it totally indipendent? Gruss Christian -- Christian Stalp Institut für Medizinische Biometrie, Epidemiologie und Informatik (IMBEI) Obere Zahlbacher Straße 69 55131 Mainz Tel.: 06131/ 17-6852 E-Mail: [EMAIL PROTECTED] Internet: www.imbei.de
RE: [dbi] UnixODBC
DBI does not need unixODBC. If you want to use the DBD::ODBC driver under DBI you'll need an ODBC driver or (more likely) an ODBC driver manager (like unixODBC) and an ODBC driver. Martin -- Martin J. Evans Easysoft Ltd, UK Development On 07-Sep-2004 Christian Stalp wrote: Hello there, one brief question: needs UnixODBC the DBI or is it totally indipendent? Gruss Christian -- Christian Stalp Institut für Medizinische Biometrie, Epidemiologie und Informatik (IMBEI) Obere Zahlbacher Straße 69 55131 Mainz Tel.: 06131/ 17-6852 E-Mail: [EMAIL PROTECTED] Internet: www.imbei.de
Re: UnixODBC
Am Dienstag, 7. September 2004 11:09 schrieben Sie: Hello there, one brief question: needs UnixODBC the DBI or is it totally indipendent? Gruss Christian DBI are Perl related. unixODBC it's a C library. Sure, but there is also a perl-Module. http://search.cpan.org/~rkies/UnixODBC-0.32/UnixODBC.pm And so I ask, is there a way to combine these? Gruss Christian -- Christian Stalp Institut für Medizinische Biometrie, Epidemiologie und Informatik (IMBEI) Obere Zahlbacher Straße 69 55131 Mainz Tel.: 06131/ 17-6852 E-Mail: [EMAIL PROTECTED] Internet: www.imbei.de
Re: How to use sequences in DBI/DBD::Oracle with bind
Hallo, why not using the returing clause? $sth-prepare ( insert into foo (my_id, data) values (my_id_seq.nextval,?) returning my_id into ? ); $sth-bind_param( 1, bar); my $id; $sth-bind_param_inout( 2, \$id, 12); $sth-execute(); Tuesday, September 7, 2004, 4:05:09 AM, Jared wrote: my $start_num = $dbh-selectrow_array( select my_id_seq.nextval from dual); my @seq = ( $start_num..100,000 ); # some end number $sth-prepare ( insert into foo (my_id, data) values (?,?) ); $sth-bind_param( 1, shift(@seq) ); $sth-bind_param( 2, bar); $sth-execute(); JS the 'select from dual' causes a lot of unnecessary overhead. JS The original method is most performant. JS Jared Grüße Wielandmailto:[EMAIL PROTECTED]
Re: [SPAM]* transaction among different dbh
I think that you'll need some kind of session management. You could either: 1) save all the information within a session object as you step through all the screens, and then save the object data to the database when the final information has been received and validated. 2) at each step, save the information to the database with a transaction id (set for the session) and a status or working, or in progress. When all of the information has been gathered, change the status to 'completed' for the transaction id. If the transaction needs to roll back then delete all in progress data for the transaction id CGI::Session should help you out here for storing either your session object or your transaction id. Dan Luca Ferrari [EMAIL PROTECTED] 07/09/2004 03:55 To: [EMAIL PROTECTED] cc: (bcc: Dan Horne/IT/AKLWHG/WHNZ) Subject:[SPAM]* transaction among different dbh Hi, here's my problem: I've got a cgi script that must update a database thru different user interactions, that means it start updating the database at the first user 'OK' click, and continue for other two 'OK's. During these steps, the cgi script is reloaded, thus the dbh is not the same among the calls. If I need to make a transaction around the different updates, thus they become a single one, how can I do? Does it suffice to use the commit/rollback methods at the last step (I mean, does the database recognize a single transaction at time?) or should I use something else? I cannot understand from the DBI documentation. I'm using postgresql 7.3. Thanks, Luca -- Luca Ferrari, [EMAIL PROTECTED] NOTICE This email and any attachments are confidential. They may contain privileged information or copyright material. If you are not an intended recipient, you should not read, copy, use or disclose the contents without authorisation as we request you contact us as once by return email. Please then delete the email and any attachments from your system. We do not accept liability in connection with computer viruses, data corruption, delay, interruption, unauthorised access or unauthorised amendment. Any views expressed in this email and any attachments do not necessarily reflect the views of the company.
Link Fails with Personal Oracle8 and DBD-Oracle-1.15
Hi, I have oracle 8 personal edition (ora8p) with OCI80 installed on WinNT. I have DBD-Oracle-1.15 and running in cygwin downloaded in the last 90 days. perl Makefile.PL can't find the Oracle version number. Not a problem, apparently. But, LobWriteAppend is in an ifdef for V8_SYNTAX. Dropping V8_SYNTAX creates even more problems. If I comment out OCILobWriteAppend, then the database updates don't work. Can you please help me fix this. Thanks. make (-f Makefile) generates the following. David p.s. Please also reply to [EMAIL PROTECTED] gcc -c -IC://sys//ora8p/OCI80/include -IC://sys//ora8p/rdbms/demo -I/usr/lib/perl5/site_perl/5.8.2/cygwin-thread-multi-64int/auto/DBI -DPERL_USE_SAFE_PUTENV -fno-strict-aliasing -DUSEIMPORTLIB -O2 -DVERSION=\1.15\ -DXS_VERSION=\1.15\ -I/usr/lib/perl5/5.8.2/cygwin-thread-multi-64int/CORE -DUTF8_SUPPORT Oracle.c gcc -c -IC://sys//ora8p/OCI80/include -IC://sys//ora8p/rdbms/demo -I/usr/lib/perl5/site_perl/5.8.2/cygwin-thread-multi-64int/auto/DBI -DPERL_USE_SAFE_PUTENV -fno-strict-aliasing -DUSEIMPORTLIB -O2 -DVERSION=\1.15\ -DXS_VERSION=\1.15\ -I/usr/lib/perl5/5.8.2/cygwin-thread-multi-64int/CORE -DUTF8_SUPPORT dbdimp.c dbdimp.c: In function `ora_db_login6': dbdimp.c:307: warning: cast to pointer from integer of different size dbdimp.c:317: warning: cast to pointer from integer of different size dbdimp.c:321: warning: cast to pointer from integer of different size dbdimp.c:325: warning: cast to pointer from integer of different size dbdimp.c: In function `dbd_rebind_ph_char': dbdimp.c:1145: warning: cast from pointer to integer of different size gcc -c -IC://sys//ora8p/OCI80/include -IC://sys//ora8p/rdbms/demo -I/usr/lib/perl5/site_perl/5.8.2/cygwin-thread-multi-64int/auto/DBI -DPERL_USE_SAFE_PUTENV -fno-strict-aliasing -DUSEIMPORTLIB -O2 -DVERSION=\1.15\ -DXS_VERSION=\1.15\ -I/usr/lib/perl5/5.8.2/cygwin-thread-multi-64int/CORE -DUTF8_SUPPORT oci7.c gcc -c -IC://sys//ora8p/OCI80/include -IC://sys//ora8p/rdbms/demo -I/usr/lib/perl5/site_perl/5.8.2/cygwin-thread-multi-64int/auto/DBI -DPERL_USE_SAFE_PUTENV -fno-strict-aliasing -DUSEIMPORTLIB -O2 -DVERSION=\1.15\ -DXS_VERSION=\1.15\ -I/usr/lib/perl5/5.8.2/cygwin-thread-multi-64int/CORE -DUTF8_SUPPORT oci8.c rm -f blib/arch/auto/DBD/Oracle/Oracle.dll LD_RUN_PATH=C://sys//ora8p/lib:C://sys//ora8p/rdbms/lib ld2 -s -L/usr/local/lib Oracle.o dbdimp.o oci7.o oci8.o -o blib/arch/auto/DBD/Oracle/Oracle.dll /usr/lib/perl5/5.8.2/cygwin-thread-multi-64int/CORE/libperl.dll.a -L/DBD-Oracle-1.15 -loci gcc -shared -o Oracle.dll -Wl,--out-implib=libOracle.dll.a -Wl,--export-all-symbols -Wl,--enable-auto-import -Wl,--stack,8388608 \ -s -L/usr/local/lib Oracle.o dbdimp.o oci7.o oci8.o /usr/lib/perl5/5.8.2/cygwin-thread-multi-64int/CORE/libperl.dll.a -L/DBD-Oracle-1.15 -loci Creating library file: libOracle.dll.a Oracle.o(.text+0x79a1):Oracle.c: undefined reference to `_OCILobWriteAppend' collect2: ld returned 1 exit status perlld: *** system() failed to execute gcc -shared -o Oracle.dll -Wl,--out-implib=libOracle.dll.a -Wl,--export-all-symbols -Wl,--enable-auto-import -Wl,--stack,8388608 \ -s -L/usr/local/lib Oracle.o dbdimp.o oci7.o oci8.o /usr/lib/perl5/5.8.2/cygwin-thread-multi-64int/CORE/libperl.dll.a -L/DBD-Oracle-1.15 -loci make: *** [blib/arch/auto/DBD/Oracle/Oracle.dll] Error 1
Re: install perl DBD::mysql problem on linux
Hi Rudy - Many thanks for your reply! I actually got it installed by manually moving the default perl (came with the system) out of paths and reinstall. What does unset LANG do? (language related?) Best regards, Zhiliang At 09:26 AM 9/6/2004, Rudy Lippan wrote: On Thu, 2 Sep 2004, Zhiliang Hu wrote: I have following problem when tried to install perl DBD::mysql (on a Red Hat Linux AS server 3.2.3-34, a dual-processor machine): Multiple lines of Unsuccessful stat on filename containing newline at /usr/lib/perl5/5.8.0/ExtUtils/Liblist/Kid.pm line 97. unset LANG before running 'perl Makefile.PL' or upgrade perl. Rudy
Re: UnixODBC
I tryed to call UnixODBC via DBI. my $data_source = dbi:UnixODBC:refdb; $dbh = DBI-connect ( $data_source, $user, $password ) or die unable to connect !\n; . and got : install_driver(UnixODBC) failed: Can't locate DBD/UnixODBC.pm in @INC (@INC contains: /usr/local/lib/perl5/5.8.3/PA-RISC2.0 /usr/local/lib/perl5/5.8.3 /usr/local/lib/perl5/site_perl/5.8.3/PA-RISC2.0 /usr/local/lib/perl5/site_perl/5.8.3 /usr/local/lib/perl5/site_perl .) at (eval 1) line 3. Perhaps the DBD::UnixODBC perl module hasn't been fully installed, or perhaps the capitalisation of 'UnixODBC' isn't right. Available drivers: Adabas, DBM, ExampleP, File, Proxy, Sponge. at ./dbi-test1.pl line 15 But there is a UnixODBC.pm in /usr/local/lib/perl5/site_perl/5.8.3/PA-RISC2.0/ and this directory is part of @INC. you see: [imserv03]/home/STALP perl -e 'printf %d %s\n, $i++, $_ for @INC' 0 /usr/local/lib/perl5/5.8.3/PA-RISC2.0 1 /usr/local/lib/perl5/5.8.3 2 /usr/local/lib/perl5/site_perl/5.8.3/PA-RISC2.0 3 /usr/local/lib/perl5/site_perl/5.8.3 4 /usr/local/lib/perl5/site_perl 5 . Why perl don't except the module? Gruss Christian -- Christian Stalp Institut für Medizinische Biometrie, Epidemiologie und Informatik (IMBEI) Obere Zahlbacher Straße 69 55131 Mainz Tel.: 06131/ 17-6852 E-Mail: [EMAIL PROTECTED] Internet: www.imbei.de
Re: UnixODBC
You don't call unixODBC. You setup a data source for unixODBC, then you reference the datasource as if it was a dataserver. I just went through this exercise. The odbc.ini and odbcinst.ini need to have the correct entries for the source of your data. The name that is created in brackets is the name you pass to DBI. Christian Stalp wrote on 9/7/2004, 10:21 AM: I tryed to call UnixODBC via DBI. my $data_source = dbi:UnixODBC:refdb; $dbh = DBI-connect ( $data_source, $user, $password ) or die unable to connect !\n; . and got : install_driver(UnixODBC) failed: Can't locate DBD/UnixODBC.pm in @INC (@INC contains: /usr/local/lib/perl5/5.8.3/PA-RISC2.0 /usr/local/lib/perl5/5.8.3 /usr/local/lib/perl5/site_perl/5.8.3/PA-RISC2.0 /usr/local/lib/perl5/site_perl/5.8.3 /usr/local/lib/perl5/site_perl .) at (eval 1) line 3. Perhaps the DBD::UnixODBC perl module hasn't been fully installed, or perhaps the capitalisation of 'UnixODBC' isn't right. Available drivers: Adabas, DBM, ExampleP, File, Proxy, Sponge. at ./dbi-test1.pl line 15 But there is a UnixODBC.pm in /usr/local/lib/perl5/site_perl/5.8.3/PA-RISC2.0/ and this directory is part of @INC. you see: [imserv03]/home/STALP perl -e 'printf %d %s\n, $i++, $_ for @INC' 0 /usr/local/lib/perl5/5.8.3/PA-RISC2.0 1 /usr/local/lib/perl5/5.8.3 2 /usr/local/lib/perl5/site_perl/5.8.3/PA-RISC2.0 3 /usr/local/lib/perl5/site_perl/5.8.3 4 /usr/local/lib/perl5/site_perl 5 . Why perl don't except the module? Gruss Christian -- Christian Stalp Institut für Medizinische Biometrie, Epidemiologie und Informatik (IMBEI) Obere Zahlbacher Straße 69 55131 Mainz Tel.: 06131/ 17-6852 E-Mail: [EMAIL PROTECTED] Internet: www.imbei.de -- Your Friendly Neighborhood DBA, Chuck
RE: How to use sequences in DBI/DBD::Oracle with bind
I've never seen before insert trigger being slower than doing the same thing in the client code. I guess it would depend on what you are doing in the trigger and how many sequences are cached (but the amount cached would also have the same effect with client code). - Ron Reidy Lead DBA Array BioPharma, Inc. -Original Message- From: Jared Still [mailto:[EMAIL PROTECTED] Sent: Monday, September 06, 2004 9:06 PM To: Reidy, Ron Cc: Jean-Pierre Utter Löfgren; DBI List Subject: RE: How to use sequences in DBI/DBD::Oracle with bind On Tue, 2004-08-31 at 07:36, Reidy, Ron wrote: Use a before insert trigger to populate the column. Triggers have the cachet of being a neat way to automatically populate key columns, and I have used them many times myself. They are also much slower than the posters original code. Jared This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system.
1.43 make test hangs at t/10examp under cygwin
I just downloaded DBI-1.43.tar.gz from cpan and am trying to install on windows xp / cygwin. Make goes fine, but 'make test' hangs on the tenth test. Tail of output with TEST_VERBOSE=1 below: -- warn: DBI::db=HASH(0x10334e54)-parse_trace_flags(nonesuch|SQL|nonesuch2) ignored unknown trace flags: 'nonesuch' 'nonesuch2' at t/09trace.t line 106 ok 63 ok 64 ok 65 ok 66 ok t/10examp. -- I killed the process after 10 minutes or so, as none of the other tests took more than a second or two. Can I ignore this or have I really got a problem? Paul
Re: 1.43 make test hangs at t/10examp under cygwin
On Tue, Sep 07, 2004 at 12:01:22PM -0400, Paul Galbraith wrote: I just downloaded DBI-1.43.tar.gz from cpan and am trying to install on windows xp / cygwin. Make goes fine, but 'make test' hangs on the tenth test. Tail of output with TEST_VERBOSE=1 below: -- warn: DBI::db=HASH(0x10334e54)-parse_trace_flags(nonesuch|SQL|nonesuch2) ignored unknown trace flags: 'nonesuch' 'nonesuch2' at t/09trace.t line 106 ok 63 ok 64 ok 65 ok 66 ok t/10examp. -- I killed the process after 10 minutes or so, as none of the other tests took more than a second or two. Can I ignore this or have I really got a problem? Probably a problem with the test rather than the DBI. Can you dig into it a little more? It's probably something to do with trace_to_file() in t/10examp.t Tim.
Re: 1.43 make test hangs at t/10examp under cygwin
Paul Galbraith wrote: I just downloaded DBI-1.43.tar.gz from cpan and am trying to install on windows xp / cygwin. Make goes fine, but 'make test' hangs on the tenth test. Tail of output with TEST_VERBOSE=1 below: I should have included this before...here is my complete build session: -- [EMAIL PROTECTED] /cygdrive/c/DBI-1.43 $ make realclean rm -f blib/script/dbiprof blib/script/dbiproxy rm -rf Perl.c DBI.c DBI-1.43 Perl.xsi t/zv*_*.t dbiproxy dbiprof dbitrace.log dbi.prof ndtest.prt ./blib Makefile.aperl blib/arch/auto/DBI/extralibs.all blib/arch/auto/DBI/extralibs.ld perlmain.c tmon.out mon.out so_locations pm_to_blib *.o *.a perl.exe perl perl.exe DBI.bs DBI.bso DBI.def libDBI.def DBI.exp DBI.x core core.*perl.*.? *perl.core core.[0-9] core.[0-9][0-9] core.[0-9][0-9][0-9] core.[0-9][0-9][0-9][0-9] core.[0-9][0-9][0-9][0-9][0-9] mv Makefile Makefile.old /dev/null 21 rm -rf blib/lib/auto/DBI blib/arch/auto/DBI rm -rf DBI-1.43 rm -f blib/arch/auto/DBI/DBI.dll blib/arch/auto/DBI/DBI.bs rm -f blib/arch/auto/DBI/DBI.a rm -f blib/lib/DBD/NullP.pm blib/lib/DBI.pm blib/lib/DBD/DBM.pm blib/lib/DBI/DBD.pm blib/lib/Win32/DBIODBC.pm blib/arch/auto/DBI/DBIXS.h blib/lib/DBI/Const/GetInfoReturn.pm rm -f blib/lib/DBI/ProxyServer.pm blib/lib/DBD/Sponge.pm blib/arch/auto/DBI/dbipport.h blib/lib/DBI/FAQ.pm Makefile blib/lib/DBI/W32ODBC.pm blib/lib/DBD/ExampleP.pm rm -f blib/arch/auto/DBI/Driver.xst blib/lib/DBI/ProfileDumper.pm blib/lib/DBI/SQL/Nano.pm blib/arch/auto/DBI/dbivport.h blib/lib/DBI/Const/GetInfoType.pm blib/lib/DBD/Proxy.pm rm -f blib/lib/DBI/DBD/Metadata.pm blib/lib/DBI/Profile.pm Makefile.old blib/arch/auto/DBI/dbd_xsh.h blib/lib/DBI/ProfileDumper/Apache.pm blib/arch/auto/DBI/Driver_xst.h rm -f blib/arch/auto/DBI/dbi_sql.h blib/lib/DBI/Const/GetInfo/ODBC.pm blib/lib/DBI/ProfileData.pm blib/lib/Bundle/DBI.pm blib/lib/DBI/Const/GetInfo/ANSI.pm blib/lib/DBD/File.pm rm -f blib/lib/DBI/PurePerl.pm [EMAIL PROTECTED] /cygdrive/c/DBI-1.43 $ perl Makefile.PL *** You are using a perl configured with threading enabled. *** You should be aware that using multiple threads is *** not recommended for production environments. *** Note: The optional PlRPC-modules (RPC::PlServer etc) are not installed. If you want to use the DBD::Proxy driver and DBI::ProxyServer modules, then you'll need to install the RPC::PlServer, RPC::PlClient, Storable and Net::Daemon modules. The CPAN Bundle::DBI may help you. You can install them any time after installing the DBI. You do *not* need these modules for typical DBI usage. Optional modules are available from any CPAN mirror, in particular http://search.cpan.org/ http://www.perl.com/CPAN/modules/by-module http://www.perl.org/CPAN/modules/by-module ftp://ftp.funet.fi/pub/languages/perl/CPAN/modules/by-module Creating DBI::PurePerltest variant: t/zvpp_01basics.t Creating DBI::PurePerltest variant: t/zvpp_02dbidrv.t Creating DBI::PurePerltest variant: t/zvpp_03handle.t Creating DBI::PurePerltest variant: t/zvpp_04mods.t Creating DBI::PurePerltest variant: t/zvpp_05thrclone.t (use threads) Creating DBI::PurePerltest variant: t/zvpp_06attrs.t Creating DBI::PurePerltest variant: t/zvpp_07kids.t Creating DBI::PurePerltest variant: t/zvpp_08keeperr.t Creating DBI::PurePerltest variant: t/zvpp_09trace.t Creating DBI::PurePerltest variant: t/zvpp_10examp.t Creating DBI::PurePerltest variant: t/zvpp_15array.t Creating DBI::PurePerltest variant: t/zvpp_20meta.t Creating DBI::PurePerltest variant: t/zvpp_30subclass.t Creating DBI::PurePerltest variant: t/zvpp_40profile.t Creating DBI::PurePerltest variant: t/zvpp_41prof_dump.t Creating DBI::PurePerltest variant: t/zvpp_42prof_data.t Creating DBI::PurePerltest variant: t/zvpp_50dbm.t Creating DBI::PurePerltest variant: t/zvpp_60preparse.t Creating DBI::PurePerltest variant: t/zvpp_80proxy.t Checking if your kit is complete... Looks good I see you're using perl 5.008005 on cygwin-thread-multi-64int, okay. Remember to actually *read* the README file! Use 'make' to build the software (dmake or nmake on Windows). Then 'make test' to execute self tests. Then 'make install' to install the DBI and then delete this working directory before unpacking and building any DBD::* drivers. Writing Makefile for DBI [EMAIL PROTECTED] /cygdrive/c/DBI-1.43 $ make cp Changes blib/lib/DBI/Changes.pm cp dbd_xsh.h blib/arch/auto/DBI/dbd_xsh.h cp dbivport.h blib/arch/auto/DBI/dbivport.h cp lib/DBI/FAQ.pm blib/lib/DBI/FAQ.pm cp Driver_xst.h blib/arch/auto/DBI/Driver_xst.h cp lib/DBD/Proxy.pm blib/lib/DBD/Proxy.pm cp lib/DBI/SQL/Nano.pm blib/lib/DBI/SQL/Nano.pm cp lib/DBI/Const/GetInfo/ANSI.pm blib/lib/DBI/Const/GetInfo/ANSI.pm cp lib/DBD/DBM.pm blib/lib/DBD/DBM.pm cp DBI.pm blib/lib/DBI.pm cp lib/DBI/Const/GetInfoReturn.pm blib/lib/DBI/Const/GetInfoReturn.pm cp DBIXS.h blib/arch/auto/DBI/DBIXS.h cp
RE: Parsing question...
Actually, yes. There is a VERY good reason that I am not using
this module...
I had no idea that it existed. Now that I know, I'll take a
peak and see if this will indeed do the trick for me. Thanks a lot for
pointing me in the direction of the easy solution.
Scott Nipp
Phone: (214) 858-1289
E-mail: [EMAIL PROTECTED]
Web: http:\\ldsa.sbcld.sbc.com
-Original Message-
From: Jeff Zucker [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 07, 2004 1:18 PM
To: NIPP, SCOTT V (SBCSI)
Cc: [EMAIL PROTECTED]
Subject: Re: Parsing question...
Is there some reason you aren't using DBD::CSV or its parser
(Text::CSV_XS), which handle this and a number of other problem issues
with parsing CSV? Why reinvent the wheel?
--
Jeff
NIPP, SCOTT V (SBCSI) wrote:
This isn't specifically a DBI question, but it is a part of a
database script I am writing. I am parsing a CSV file and populating
a
MySQL database table. The problem I have run into is that a couple of
lines of data have a comma in a filed that is in quotes, such as
First
part, second part. This makes it difficult to parse the CSV file on
commas like my intention was. Could someone help me figure out how to
parse this? Thanks.
Basically, I am reading in the file in a 'while' loop. I am
then splitting the line into an array using the 'split' function. The
next step is to simply populate the database table with the fields.
Here is the 'split' I am currently using.
...snip...
while (DATA) {
@data = split(/,\s*/);
print $data[1]\n;
}
...snip...
Thanks in advance for the help.
Scott Nipp
Phone: (214) 858-1289
E-mail: [EMAIL PROTECTED]
Web: http:\\ldsa.sbcld.sbc.com
Re: Parsing question...
What i understood from your question is , you want to split strings that
have comma within
quotes. Forgive me if i'm wrong.
If elements of @data are re-split , you can get multiple fields from it.
...snip...
my @new_data;
while (DATA) {
@data = split(/,\s*/);
foreach my $string ( @data ) {
@temp = split /,\s*/, $string;
push @new_data, @temp;
}
print $data[1]\n;
}
...snip...
there may be better solutions, though, as i've never dealt with CSV files.
regs,
Ravi
NIPP, SCOTT V (SBCSI) wrote:
This isn't specifically a DBI question, but it is a part of a
database script I am writing. I am parsing a CSV file and populating a
MySQL database table. The problem I have run into is that a couple of
lines of data have a comma in a filed that is in quotes, such as First
part, second part. This makes it difficult to parse the CSV file on
commas like my intention was. Could someone help me figure out how to
parse this? Thanks.
Basically, I am reading in the file in a 'while' loop. I am
then splitting the line into an array using the 'split' function. The
next step is to simply populate the database table with the fields.
Here is the 'split' I am currently using.
...snip...
while (DATA) {
@data = split(/,\s*/);
print $data[1]\n;
}
...snip...
Thanks in advance for the help.
Scott Nipp
Phone: (214) 858-1289
E-mail: [EMAIL PROTECTED]
Web: http:\\ldsa.sbcld.sbc.com
RE: Parsing question...
The suggestion of using Text::CSV_XS was the winner. This is a
very simple module to use. Thanks for the feedback though.
Scott Nipp
Phone: (214) 858-1289
E-mail: [EMAIL PROTECTED]
Web: http:\\ldsa.sbcld.sbc.com
-Original Message-
From: Ravi Kongara [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 07, 2004 2:05 PM
To: NIPP, SCOTT V (SBCSI)
Cc: [EMAIL PROTECTED]
Subject: Re: Parsing question...
What i understood from your question is , you want to split strings that
have comma within
quotes. Forgive me if i'm wrong.
If elements of @data are re-split , you can get multiple fields from
it.
...snip...
my @new_data;
while (DATA) {
@data = split(/,\s*/);
foreach my $string ( @data ) {
@temp = split /,\s*/, $string;
push @new_data, @temp;
}
print $data[1]\n;
}
...snip...
there may be better solutions, though, as i've never dealt with CSV
files.
regs,
Ravi
NIPP, SCOTT V (SBCSI) wrote:
This isn't specifically a DBI question, but it is a part of a
database script I am writing. I am parsing a CSV file and populating a
MySQL database table. The problem I have run into is that a couple of
lines of data have a comma in a filed that is in quotes, such as First
part, second part. This makes it difficult to parse the CSV file on
commas like my intention was. Could someone help me figure out how to
parse this? Thanks.
Basically, I am reading in the file in a 'while' loop. I am
then splitting the line into an array using the 'split' function. The
next step is to simply populate the database table with the fields.
Here is the 'split' I am currently using.
...snip...
while (DATA) {
@data = split(/,\s*/);
print $data[1]\n;
}
...snip...
Thanks in advance for the help.
Scott Nipp
Phone: (214) 858-1289
E-mail: [EMAIL PROTECTED]
Web: http:\\ldsa.sbcld.sbc.com
RE: Large SQL statements...
Use an array of bind vars.
-
Ron Reidy
Lead DBA
Array BioPharma, Inc.
-Original Message-
From: NIPP, SCOTT V (SBCSI) [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 07, 2004 2:04 PM
To: [EMAIL PROTECTED]
Subject: Large SQL statements...
I have a table with 22 columns, and my INSERT on this table is
actually putting in data to possibly all 22 fields. My question is, is
there a better way to write this INSERT? Here's what I have:
while ($line = DATA) {
$csv-parse($line);
@data = $csv-fields;
$sth = $dbh-prepare(qq{INSERT INTO AllMid_Data
VALUES(?,?,?,?,?,?,?,?,?,?,?,?
,?,?,?,?,?,?,?,?,?,?)}) or print Error with INSERT _prepare_:
$DBI::errstr\n;
$sth-execute(undef,$data[0],$data[1],$data[2],$data[3],$data[4],$data[5
],$dat
a[6],$data[7],$data[8],$data[9],$data[10],$data[11],$data[12],$data[13],
$data[14
],$data[15],$data[16],$data[17],$data[18],$data[19],$data[20]) or print
Error with
INSERT _execute_: $DBI::errstr\n;
}
The 'execute' statement is quite long, and almost all of the
data comes from a single array. I don't think this is a big deal in and
of itself, just looking for a better way to do this. Thanks.
Scott Nipp
Phone: (214) 858-1289
E-mail: [EMAIL PROTECTED]
Web: http:\\ldsa.sbcld.sbc.com
This electronic message transmission is a PRIVATE communication which contains
information which may be confidential or privileged. The information is intended
to be for the use of the individual or entity named above. If you are not the
intended recipient, please be aware that any disclosure, copying, distribution
or use of the contents of this information is prohibited. Please notify the
sender of the delivery error by replying to this message, or notify us by
telephone (877-633-2436, ext. 0), and then delete it from your system.
RE: Large SQL statements...
-Original Message- $sth-execute(undef,$data[0],$data[1],$data[2],$data[3],$data[4],$data[5 ],$dat a[6],$data[7],$data[8],$data[9],$data[10],$data[11],$data[12],$data[13], $data[14 ],$data[15],$data[16],$data[17],$data[18],$data[19],$data[20]) or print Error with INSERT _execute_: $DBI::errstr\n; } The 'execute' statement is quite long, and almost all of the data comes from a single array. I don't think this is a big deal in and of itself, just looking for a better way to do this. Thanks. Scott Nipp Phone: (214) 858-1289 E-mail: [EMAIL PROTECTED] Web: http:\\ldsa.sbcld.sbc.com --- This should work: $sth-bind_param(1, undef); $sth-bind_param($_+2, $data[$_]) foreach 0..20; $sth-execute() or print something; but if you are guarenteed to have 21 elements in @data (never more) then you probably want: $sth-execute(undef, @data) or print something; In the latter case: recall that in Perl any method just takes a list, and recall that in Perl, a list can be created by interpolating scalars and lists. Brian Campbell (E-mail).vcf Description: Binary data
Re: Large SQL statements...
NIPP, SCOTT V (SBCSI) wrote:
I have a table with 22 columns, and my INSERT on this table is
actually putting in data to possibly all 22 fields. My question is, is
there a better way to write this INSERT? Here's what I have:
while ($line = DATA) {
$csv-parse($line);
@data = $csv-fields;
$sth = $dbh-prepare(qq{INSERT INTO AllMid_Data
VALUES(?,?,?,?,?,?,?,?,?,?,?,?
,?,?,?,?,?,?,?,?,?,?)}) or print Error with INSERT _prepare_:
$DBI::errstr\n;
Put the prepare outside the loop - it only needs to be done once
use the 'x' contruct to build the slq
Re: Large SQL statements...
Sorry about this. My groupwise client has a nasty habit of
taking all my formatting out. If you like I can send an attachment
to you. Just let me know.-Ian
Ian Harisay [EMAIL PROTECTED] 09/07 8:33 am
A good approach is to to define your dataset. You are just
using a CSV so I would do the following: 1) load CSV record into a
data structure (hashes are great for this).2) insert record into table
by way of a generic loader method. -- code snippet --use strict;use
Text::CSV; #-- I use this because I am familiar with it.use DBI;my
$dbh
= DBI-connect($data_source, $username, $auth, \%attr);my $csv =
Text::CSV-new(); #-- create CSV parsing object.#-- the
file.open(FILE,
datafile.txt) || die $!;while(FILE){ chomp;
insertRecord(buildRecord($_)) || die some error occured; }sub
buildRecord { my $line = shift; my $tbl_nam = 'table'; my @fields =
qw(field1 field2 field3); my $record = {}; $csv-parse($line); my
@data = $csv-fields(); for(my $i=0; $i@fields; $i++ ){
$record-{$tbl_name}-{$_} = $data[$i]; }}sub insertRecord { my
$record = shift; my $tbl_nam = keys %{$record}; #-- build sql my $sql
= INSERT INTO $tbl_nam (; my $columns; my $placeholder; my @data;
foreach my $col (keys %{$record-{$tbl_nam}} ){ $columns .= $col, ;
$placeholder .= '?, '; push(@data, $record-{$tbl_nam}-{$col}); }
$columns =~ s/, $//; $placeholder =~ s/, $//; my $dbh-do(INSERT INTO
$tbl_nam ($columns) values($placeholders), undef, @data) || die
$dbh-errstr();} -- end code snippet --this code has not been compiled,
so you may find some syntax problems. I think the general logic is
there though. Obviously some things can be done to optimize the
code. I have a Db.pm I use to handle some of the optimization and
don't want to go into detail here. Also, you can change your
buildRecord method to be more generic by passing in the fields.
Also, references could be used a little more extenisively to eliminate
pass by value. Don't forget to commit your data if you don't have
autocommit on.
NIPP, SCOTT V (SBCSI) [EMAIL PROTECTED] 09/07 7:04 am
I have a table with 22 columns, and my INSERT on this table is
actually putting in data to possibly all 22 fields. My question is,
is
there a better way to write this INSERT? Here's what I have:
while ($line = DATA) {
$csv-parse($line);
@data = $csv-fields;
$sth = $dbh-prepare(qq{INSERT INTO AllMid_Data
VALUES(?,?,?,?,?,?,?,?,?,?,?,?
,?,?,?,?,?,?,?,?,?,?)}) or print Error with INSERT _prepare_:
$DBI::errstr\n;
$sth-execute(undef,$data[0],$data[1],$data[2],$data[3],$data[4],$data[5
],$dat
a[6],$data[7],$data[8],$data[9],$data[10],$data[11],$data[12],$data[13],
$data[14
],$data[15],$data[16],$data[17],$data[18],$data[19],$data[20]) or
print
Error with
INSERT _execute_: $DBI::errstr\n;
}
The 'execute' statement is quite long, and almost all of the
data comes from a single array. I don't think this is a big deal in
and
of itself, just looking for a better way to do this. Thanks.
Scott Nipp
Phone: (214) 858-1289
E-mail: [EMAIL PROTECTED]
Web: http:\\ldsa.sbcld.sbc.com
RE: :ODBC, MSSQL, output params
From: Jenda Krynicky [EMAIL PROTECTED]
From: Jeff Urlwin [EMAIL PROTECTED]
It could be a bug. Can you wrap up a quick test case and I'll trace
it through?
Regards,
Jeff
Sorry for the delay.
#!perl
use DBI;
my $PROC = '*END*';
...
*END*
$db = DBI-connect('dbi:ODBC:', 'xxx', 'xxx',
{PrintError = 0,RaiseError = 1,LongReadLen = 65536,AutoCommit =
1});
$db-do($PROC);
eval{
{
my ($JobCity, $JobState, $JobCountry, $JobZIP);
# this one returns just one char in each output variable in the
first call # my ($JobCity, $JobState, $JobCountry, $JobZIP) =
('','','','');
# this one returns just one char in each output variable in the
first call # my ($JobCity, $JobState, $JobCountry, $JobZIP) = ( x
50, x 50, , x 5); # my ($JobCity, $JobState, $JobCountry,
$JobZIP) = (\0 x 50, \0 x 50, \0 x 2, \0 x 5, );
# the two above work fine
# my ($JobCity, $JobState, $JobCountry, $JobZIP) = ( x 3, x 3,
, x 3);
# my ($JobCity, $JobState, $JobCountry, $JobZIP) = ( , , ,
);
# it's strange but these two work fine
...
If I do not initialize the variables or initialize them to '' then the
first call returns just the first characters of the return values, in
all other cases it works correctly. The second and following calls
work fine, even if the values are longer than the ones returned
before.
Any news?
I just found out that it breaks even if the stored procedure returns
some nulls. It returns nulls once and the following calls all return
just the first character.
Thanks, Jenda
= [EMAIL PROTECTED] === http://Jenda.Krynicky.cz =
When it comes to wine, women and song, wizards are allowed
to get drunk and croon as much as they like.
-- Terry Pratchett in Sourcery
RE: :ODBC, MSSQL, output params
Fixed in 1.10 released about 10 minutes ago ;)
Sorry for the wait.
Jeff
-Original Message-
From: Jenda Krynicky [mailto:[EMAIL PROTECTED]
Sent: Friday, August 20, 2004 9:51 AM
To: Jeff Urlwin
Cc: [EMAIL PROTECTED]
Subject: RE: :ODBC, MSSQL, output params
From: Jeff Urlwin [EMAIL PROTECTED]
It could be a bug. Can you wrap up a quick test case and
I'll trace
it through?
Regards,
Jeff
Sorry for the delay.
#!perl
use DBI;
my $PROC = '*END*';
CREATE PROCEDURE dbo.GetL__ocationInfo(
@LocationID Int,
@City varchar(50) OUTPUT,
@State varchar(50) OUTPUT,
@Country char(2) OUTPUT,
@Zip char(5) OUTPUT
) AS
BEGIN
SET @Zip = '12345';
SET @State = 'Whatever';
SET @Country = 'US'
SET @City = 'Some City ' + Convert(varchar(10), @LocationID) END
*END*
$db = DBI-connect('dbi:ODBC:', 'xxx', 'xxx',
{PrintError = 0,RaiseError = 1,LongReadLen =
65536,AutoCommit =
1});
$db-do($PROC);
eval{
{
my ($JobCity, $JobState, $JobCountry, $JobZIP);
# this one returns just one char in each output
variable in the
first call
# my ($JobCity, $JobState, $JobCountry, $JobZIP)
= ('','','','');
# this one returns just one char in each output
variable in the
first call
# my ($JobCity, $JobState, $JobCountry, $JobZIP)
= ( x 50, x
50, , x 5);
# my ($JobCity, $JobState, $JobCountry, $JobZIP)
= (\0 x 50, \0
x 50, \0 x 2, \0 x 5, );
# the two above work fine
# my ($JobCity, $JobState, $JobCountry, $JobZIP)
= ( x 3, x 3,
, x 3);
# my ($JobCity, $JobState, $JobCountry, $JobZIP)
= ( , , ,
);
# it's strange but these two work fine
my $sp = $db-prepare('EXEC
dbo.GetL__ocationInfo '. join(',
',('?') x 5));
$sp-bind_param_inout(2, \$JobCity, 50,
DBI::SQL_VARCHAR);
$sp-bind_param_inout(3, \$JobState, 50,
DBI::SQL_VARCHAR);
$sp-bind_param_inout(4, \$JobCountry, 2,
DBI::SQL_VARCHAR);
$sp-bind_param_inout(5, \$JobZIP, 5, DBI::SQL_VARCHAR);
sub GetLocation {
$sp-bind_param(1, $_[0]);
$sp-execute();
$_[1] = $JobCity;
$_[2] = $JobState;
$_[3] = $JobCountry;
$_[4] = $JobZIP;
}
}
my ($JobCity, $JobState, $JobCountry, $JobZIP);
GetLocation( 10, $JobCity, $JobState, $JobCountry, $JobZIP);
print ' . join( ', ', $JobCity, $JobState, $JobCountry,
$JobZIP) . '\n;
GetLocation( 15456, $JobCity, $JobState, $JobCountry, $JobZIP);
print ' . join( ', ', $JobCity, $JobState, $JobCountry,
$JobZIP) . '\n;
GetLocation( 15457716, $JobCity, $JobState,
$JobCountry, $JobZIP);
print ' . join( ', ', $JobCity, $JobState, $JobCountry,
$JobZIP) . '\n;
};
$db-do('DROP PROCEDURE dbo.GetL__ocationInfo');
__END__
If I do not initialize the variables or initialize them to '' then
the first call returns just the first characters of the return
values, in all other cases it works correctly.
The second and following calls work fine, even if the values are
longer than the ones returned before.
# $Id: DBI.pm,v 11.43 2004/02/01 11:16:16 timbo Exp $
$DBI::VERSION = 1.43; # == ALSO update the version in the pod text
below!
# $Id: ODBC.pm 124 2004-02-22 15:57:00Z jurl $
$DBD::ODBC::VERSION = '1.07';
This is perl, v5.8.0 built for MSWin32-x86-multi-thread
...
Binary build 805 provided by ActiveState Corp.
http://www.ActiveState.com
Built 18:08:02 Feb 4 2003
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Thanks, Jenda
= [EMAIL PROTECTED] === http://Jenda.Krynicky.cz =
When it comes to wine, women and song, wizards are allowed
to get drunk and croon as much as they like.
-- Terry Pratchett in Sourcery
