Re: last insert id
Ron Savage schreef: Dr.Ruud: [ /mysql|Pg/ ] Notice that 'CertainlyNotPgAtAll' matches, so maybe you were looking for /\A(?:mysql|pg)\z/i Nope. Method db_vendor() extracts the vendor's name from the connect string passed to DBI, so I don't see how it could return that :-))). Is it legal to pass 'MySQL'? How about ' mysql '? MySQL's native LAST_INSERT_ID() will return the *first* id from a multi row insert. I don't [k]now what DBI does with that. Good point. But then my code doesn't come with a written guarantee to work with multi-row inserts... But then I don't claim that you claim. g -- Affijn, Ruud Gewoon is een tijger.
Semantics of InactiveDestroy
Hi! I have a problem with parsing the documentation regarding the InactiveDestroy attribute on DB handles. Citing the documentation: InactiveDestroy (boolean) The InactiveDestroy attribute can be used to disable the *database engine* related effect of DESTROYing a handle (which would normally close a prepared statement or disconnect from the database etc). The default value, false, means a handle will be fully destroyed when it passes out of scope. For a database handle, this attribute does not disable an *explicit* call to the disconnect method, only the implicit call from DESTROY that happens if the handle is still marked as Active. Think of the name as meaning 'treat the handle as not-Active in the DESTROY method'. This attribute is specifically designed for use in Unix applications that fork child processes. Either the parent or the child process, but not both, should set InactiveDestroy on all their shared handles. Note that some databases, including Oracle, don't support passing a database connection across a fork. To help tracing applications using fork the process id is shown in the trace log whenever a DBI or handle trace() method is called. The process id also shown for *every* method call if the DBI trace level (not handle trace level) is set high enough to show the trace from the DBI's method dispatcher, e.g. = 9. What's missing here is a more detailed description of what actually happens if I set it to non-default value (which is more interesting). I suspect by negation, that setting it to true would fully destroy the handle (or partly) destroy the handle when it goes out of scope, but there should be a clear explanation what it actually does. I'm actually quite confused by this fragment of documentation, since the attribute name suggests that it enables destroying, while the documentation suggests that it does the reverse. Could anyone clarify what are effects of setting this attribute to true (and possibly change it in the documentation)? -- Best Regards, Aleksander Adamowski GG#: 274614 ICQ UIN: 19780575 http://olo.ab.altkom.pl
RE: Semantics of InactiveDestroy
-Original Message-
From: Aleksander Adamowski [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 18, 2006 4:39 AM
To: dbi-users@perl.org
Subject: Semantics of InactiveDestroy
Hi!
I have a problem with parsing the documentation regarding the
InactiveDestroy attribute on DB handles.
Citing the documentation:
InactiveDestroy (boolean) The InactiveDestroy attribute can be
used to disable the *database engine* related effect of DESTROYing
a handle (which would normally close a prepared statement or
disconnect from the database etc). The default value, false, means
a handle will be fully destroyed when it passes out of scope.
For a database handle, this attribute does not disable an
*explicit* call to the disconnect method, only the implicit
call from DESTROY that happens if the handle is still marked
as Active.
Think of the name as meaning 'treat the handle as not-Active
in the DESTROY method'.
This attribute is specifically designed for use in Unix
applications that fork child processes. Either the parent or
the child process, but not both, should set InactiveDestroy
on all their shared handles. Note that some databases,
including Oracle, don't support passing a database connection
across a fork.
To help tracing applications using fork the process id is
shown in the trace log whenever a DBI or handle trace() method
is called. The process id also shown for *every* method call
if the DBI trace level (not handle trace level) is set high
enough to show the trace from the DBI's method dispatcher,
e.g. = 9.
What's missing here is a more detailed description of what actually
happens if I set it to non-default value (which is more interesting).
I suspect by negation, that setting it to true would fully destroy
the handle (or partly) destroy the handle when it goes out of scope,
but there should be a clear explanation what it actually does.
It's actually the reverse. It means, in practice, don't *automatically*
call disconnect() when the $dbh is destroyed. It can save you from some
pretty nasty trouble when you fork processes. You don't want your child
process exiting to close your parent's database connection. That's what
this solves.
$dbh-{InactiveDestroy} = 1;# safe(r) for forking
Philip
Re: DBD::Oracle install problems
Upgrading is something I would love to do, but can't at this time. I have, however, tried DBD-Oracle 1.15 and 1.16 with the same error. I was hoping 1.17 would work. I was glad to see it available. I'm pretty sure it's an Oracle installation or evnironment issue. I just don't know where else to look for help. Thanks. John Scoles [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Using DBI 1.40 (for perl 5.006001 on sun4-solaris) installed in /usr/local/lib/perl5/site_perl/5.6.1/sun4-solaris/auto/DB That version of DBI is over 2 years old so I doubt it will work with DBD::Oracle 1.17. You might want to try upgrading your DBI first. MIKE VANOLE [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi, I've googled my error and have seen similar problems, but no solutions are found. /usr/local/sparc-sun-solaris2.8/bin/ld: cannot find -lclntsh I'm trying to install any version of DBD::Oracle on Solaris 2.8, with perl 5.6.1. Currently trying DBD-Oracle 1.17. My Oracle environment is set up and working as far as I can tell by using sqlplus successfully. My current solution for Perl and Oracle invloves using open3 and sqlplus, and I have to get away from this. My problem may be library related but I've tried numerous combinations of LD_LIBRARY_PATH, etc... Many thanks in advance. Mike Here's the output from my latest attempt: # perl Makefile.PL Using DBI 1.40 (for perl 5.006001 on sun4-solaris) installed in /usr/local/lib/perl5/site_perl/5.6.1/sun4-solaris/auto/DB I Configuring DBD::Oracle for perl 5.006001 on solaris (sun4-solaris) Remember to actually *READ* the README file! Especially if you have any problems. Using Oracle in /apps/oracle/product/9.2.0 DEFINE _SQLPLUS_RELEASE = 902000100 (CHAR) Oracle version 9.2.0.1 (9.2) Found /apps/oracle/product/9.2.0/rdbms/demo/demo_rdbms.mk Found /apps/oracle/product/9.2.0/precomp/demo/proc/demo_proc.mk Using /apps/oracle/product/9.2.0/rdbms/demo/demo_rdbms.mk Your LD_LIBRARY_PATH env var is set to '/apps/oracle/product/9.2.0/lib64:/apps/oracle/product/9.2.0/lib32' Reading /apps/oracle/product/9.2.0/rdbms/demo/demo_rdbms.mk Reading /apps/oracle/product/9.2.0/rdbms/lib/env_rdbms.mk Attempting to discover Oracle OCI build rules gcc -c DBD_ORA_OBJ.c by executing: [make -f /apps/oracle/product/9.2.0/rdbms/demo/demo_rdbms.mk build ECHODO=echo ECHO=echo GENCLNTSH='echo ge nclntsh' CC=true OPTIMIZE= CCFLAGS= EXE=DBD_ORA_EXE OBJS=DBD_ORA_OBJ.o] Oracle oci build prolog: [Building client shared library libclntsh.so ...] [Call script /apps/oracle/product/9.2.0/bin/genclntsh ...] [genclntsh] [Built /apps/oracle/product/9.2.0/lib/libclntsh.so ... DONE] Oracle oci build command: [true -L/apps/oracle/product/9.2.0/lib32/ -L/apps/oracle/product/9.2.0/rdbms /lib32/ -o DBD_ORA_EXE DBD_ORA_OBJ.o -lclntsh `cat /apps/oracle/product/9.2.0/lib/sysliblist` -R/apps/oracle/product/9.2.0/lib -laio -lposix4 -lkstat -lm -lthread] Found header files in /apps/oracle/product/9.2.0/rdbms/demo /apps/oracle/product/9.2.0/rdbms/public. Checking for functioning wait.ph System: perl5.006001 sunos solaris 5.8 generic sun4u sparc sunw,ultra-5_10 Compiler: gcc -O -fno-strict-aliasing -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE _OFFSET_BITS=64 Linker: /usr/ccs/bin/ld Sysliblist: -lnsl -lsocket -lgen -ldl -lsched Oracle makefiles would have used these definitions but we override them: CC: cc CFLAGS: $(GFLAG) $(OPTIMIZE) $(CDEBUG) $(CCFLAGS) $(PFLAGS)\ $(SHARED_CFLAG) $(USRFLAGS) [$(GFLAG) -xO3 $(CDEBUG) -Xa $(PROFILE) -xstrconst -dalign -xF $(XS) $(MR) -xildoff -errtags=yes -v -xarch=v9 -xchip=ultra3 -W2,-AKNR_S -Wd,-xsafe=unboundsym -Wc,-Qiselect-funcalign=32 - xcode=abs44 -Wc,-Qgsched-trace_late=1 -Wc,-Qg sched-T5 -xalias_level=weak -D_REENTRANT -DSS_64BIT_SERVER -DBIT64 -DMACHINE 64 -K PIC -I/apps/oracle/product/9.2.0/rdbms/ demo -I/apps/oracle/product/9.2.0/rdbms/public -I/apps/oracle/product/9.2.0/ plsql/public -I/apps/oracle/product/9.2.0/net work/public -DSLMXMX_ENABLE -DSLTS_ENABLE -D_SVID_GETTOD -D_REENTRANT $(LPFLAGS) $(USRFLAGS)] LDFLAGS: -o $@ $(LDPATHFLAG)$(PRODLIBHOME) $(LDPATHFLAG)$(LIBHOME) [-o $@ -L/apps/oracle/product/9.2.0/rdbms/lib/ -L$(LIBHOME)] Linking with OTHERLDFLAGS = -L/apps/oracle/product/9.2.0/lib32/ -L/apps/oracle/product/9.2.0/rdbms/lib 32/ -lclntsh `c at /apps/oracle/product/9.2.0/lib/sysliblist` -R/apps/oracle/product/9.2.0/lib -laio -lposix4 -lkstat -lm -lthread [fro m 'build' rule] WARNING: If you have problems you may need to rebuild perl with threading enabled. WARNING: If you have problems you may need to rebuild perl with -Uusemymalloc. Checking if your kit is complete... Looks good
Re: problem DBD-Oracle-1.17 linux enterprise AS
Hi, I had the same problem in a similar system (Oracle 10.1, Enterprise Linux AS). A script running many queries always failed but at a different place at each run. The problem vanished after running the Patch 3612581 for the Oracle bug: Bug 3612581 OERI[kpofdr-long] can occur on SELECT Best regards, Teemu Kivioja At 07:49 10.4.2006 -0500, you wrote: i have this error when i made interface program perl with oracle 10g trough DBD module. DBD::Oracle::st execute failed: ORA-00600: internal error code, arguments: [kpofdr-long], [], [], [], [], [], [], [] (DBD ERROR: error possibly near * indicator at char 37 in 'select itmpar, texpar from useru.*par where ciapar = :p1 and fampar = :p2 ') [for Statement select itmpar, texpar from useru.par where ciapar = ? and fampar = ? with ParamValues: :p1=0, :p2='py-ley'] at ./1230.pl line 28. but,if I execute it for the second time it work ok. thanks -- Open WebMail Project (http://openwebmail.org) --- End of Forwarded Message --- -- Open WebMail Project (http://openwebmail.org)
Bad int8 external representation (SQL-HY000)(DBD: st_execute/SQLExecute err=-1)
Hello All,
I am attempting to INSERT into a table while reading in from a piped
delimited file and am getting the following error:
[unixODBC]ERROR: copy: line 1, Bad int8 external representation
(SQL-HY000)(DBD: st_execute/SQLExecute err=-1)
The destination table (Netezza) layout is (38 columns):
Table p_dlvrb_study_attributes
Attribute| Type | Modifier | Default
Value
++--+---
dlvrb_gid | bigint | |
study_gid | bigint | |
client_gid | bigint | |
slsfc_gid | bigint | |
mkt_def_gid| bigint | |
mkt_def_desc | character varying(40) | |
store_panl_gid | bigint | |
study_anlys_typ_cde| character varying(5) | |
extnd_lkbck_strt_dte | date | |
cohrt_strt_dte | date | |
cohrt_end_dte | date | |
study_end_dte | date | |
lkbck_prd_days_nbr | numeric(3,0) | |
study_prd_days_nbr | numeric(3,0) | |
dlvry_freq_typ_cde | character(1) | |
dlvrb_error_flg_desc | character varying(100) | |
std_err_thrhld_nbr | numeric(8,4) | |
std_err_ind| character varying(1) | |
actl_dlvry_dte | date | |
dlvry_frmt_typ_desc| character varying(40) | |
study_nam | character varying(100) | |
study_anlys_desc | character varying(100) | |
slsfc_nam | character varying(250) | |
client_long_nam| character varying(100) | |
std_err_rsn_desc | character varying(100) | |
sob_clsfy_row_cnt | bigint | |
sob_prctr_demo_row_cnt | bigint | |
sob_pay_typ_row_cnt| bigint | |
sob_sw_dtl_row_cnt | bigint | |
sob_prctr_row_cnt | bigint | |
sob_unq_ptnt_row_cnt | bigint | |
pc_clsfy_row_cnt | bigint | |
pc_ptnt_demo_row_cnt | bigint | |
pc_prctr_row_cnt | bigint | |
mkt_cnfgr_gid | bigint | |
itime_prjct_id | character varying(25) | |
client_cntct_nam | character varying(40) | |
dlvrb_nbr | smallint | |
Distributed on random: (round-robin)
and the file record I am trying to INSERT is:
28526|205|12|1581|1027|Int 2
P2||PC|2002-10-04|2005-01-01|2005-02-28|2005-03-03|730|30|M|||1||F|Int 2
P2|PC|XX XX X|XX, XXX.|||1530|15|UI
Integration Test 205|1
This is very odd because I have another process where I get the above
data directly from a table in another database using fetchall_arrayref
and dynamically creating an INSERT statement using the table definition
of the detination table and doing the INSERT and it works. But when I
read in the same data from a piped delimited file and doing the INSERT,
I get an error.
The INSERT statement that is dynamically created as shown below and the
column count and the bind variables count appears to be correct.
insert into pl_dlvrb_study_attributes (dlvrb_gid, study_gid, client_gid,
slsfc_gid, mkt_def_gid, mkt_def_desc, store_panl_gid,
study_anlys_typ_cde, extnd_lkbck_strt_dte, cohrt_strt_dte,
cohrt_end_dte, study_end_dte, lkbck_prd_days_nbr, study_prd_days_nbr,
dlvry_freq_typ_cde, dlvrb_error_flg_desc, std_err_thrhld_nbr,
std_err_ind, actl_dlvry_dte, dlvry_frmt_typ_desc, study_nam,
study_anlys_desc, slsfc_nam, client_long_nam, std_err_rsn_desc,
sob_clsfy_row_cnt, sob_prctr_demo_row_cnt, sob_pay_typ_row_cnt,
sob_sw_dtl_row_cnt, sob_prctr_row_cnt, sob_unq_ptnt_row_cnt,
pc_clsfy_row_cnt, pc_ptnt_demo_row_cnt, pc_prctr_row_cnt, mkt_cnfgr_gid,
itime_prjct_id, client_cntct_nam, dlvrb_nbr) values (?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?)
I have been at it for awhile and hope that someone can assist.
Here is my code:
sub sub_executeBindFromInputFile() {
print STDERR \n* sub_executeBindFromInputFile() *\n\n;
local ($dbDriver, $dbh, $sth, $fh) = @_;
print STDERR DATABASE DRIVER IS: $dbDriver\n;
print STDERR DATABASE HANDLE IS: $dbh\n;
print STDERR STATEMENT HANDLE IS:$sth\n;
print STDERR FILE HANDLE IS: $fh\n;
my ($bindVar, @row);
Re: Bad int8 external representation (SQL-HY000)(DBD: st_execute/SQLExecute err=-1)
On Tue, 18 Apr 2006 11:44:50 -0700, Loo, Peter # PHX wrote: Hi Peter [unixODBC]ERROR: copy: line 1, Bad int8 external representation (SQL-HY000)(DBD: st_execute/SQLExecute err=-1) Line 1, eh? First attempt to insert data? Did you try chomp-ing the line? -- Cheers Ron Savage, [EMAIL PROTECTED] on 19/04/2006 http://savage.net.au/index.html Let the record show: Microsoft is not an Australian company
Re: dump_results
On Thu, Apr 13, 2006 at 03:22:19PM -0700, Loo, Peter # PHX wrote: Hi, I am passing the correct arguments to dump_results, but it appears that the records are not getting separated with new line. $maxlen = 500; $lsep = '\n'; $fsep = '|'; $fh = \*FH; $rows = $sth-dump_results($maxlen, $lsep, $fsep, $fh); Also, how can I force it so that the output does not have the quotes for every field? Don't use dump_results() for this. The docs say: Since it uses L/neat_list to format and edit the string for reading by humans, it is not recomended for data transfer applications. Tim.
Re: problem using table_info and column_info with DBD::Proxy
On Mon, Apr 10, 2006 at 03:14:21PM +0800, Allan Dyer wrote: I've found that the problem with table_info has been reported three times before in this group: Here's how I call table_info(...). I have not used DBD::Proxy. Who's maintaining DBD::Proxy? Perhaps I should contact them direct. I'd *love* someone to help maintain DBD::Proxy. Any volunteers? Meanwhile, see this in Proxy.pm: # XXX probably many more methods need to be added here. # See notes in ToDo about method metadata sub commit; sub connected; sub rollback; sub ping; try adding extra lines for any methods that seem unsupported by DBD::Proxy. Please let me know if that helps. Tim.
Re: problem using table_info and column_info with DBD::Proxy
On 18 Apr 2006 at 23:00, Tim Bunce wrote: On Mon, Apr 10, 2006 at 03:14:21PM +0800, Allan Dyer wrote: I've found that the problem with table_info has been reported three times before in this group: Here's how I call table_info(...). I have not used DBD::Proxy. Who's maintaining DBD::Proxy? Perhaps I should contact them direct. I'd *love* someone to help maintain DBD::Proxy. Any volunteers? Sorry, I can't commit on this. I can help document this problem, and test any solutions suggested. Meanwhile, see this in Proxy.pm: # XXX probably many more methods need to be added here. # See notes in ToDo about method metadata sub commit; sub connected; sub rollback; sub ping; try adding extra lines for any methods that seem unsupported by DBD::Proxy. I didn't find any explanation of method metadata in ToDo, so I added: sub table_info; sub column_info; Please let me know if that helps. For my column_info example, a change. I previously got: Can't call method fetchall_hashref on an undefined value at ./example2.pl line 17. Now I get: DBD::Proxy::db column_info failed: Server returned error: Failed to execute method CallMethod: Can't call method execute without a package or object reference at /usr/lib/perl5/site_perl/5.8.8/i486-linux/DBI.pm line 1584. For my table_info example, no change, I still get: DBD::Proxy::db table_info failed: Server returned error: Failed to execute method CallMethod: Can't call method execute without a package or object reference at /usr/lib/perl5/site_perl/5.8.8/i486-linux/DBD/mysql.pm line 262. it seems that table_info in mysql.pm is not getting a valid statement handle, which implies the database handle it has been given is invalid. I looked at ProxyServer.pm and found this comment in table_info: # We wouldn't need to send all the rows at this point, instead we could # make use of $rsth-fetch() on the client as usual. # The problem is that some drivers (namely DBD::ExampleP, DBD::mysql and # DBD::mSQL) are returning foreign sth's here, thus an instance of # DBI::st and not DBI::ProxyServer::st. We could fix this by permitting # the client to execute method DBI::st, but I don't like this. I'm wondering if this is related, but I'm finding it difficult to follow what's happening. Did the behaviour of DBD::mysql change after DBD:Proxy was written, perhaps? Allan Allan Dyer, CISSP, MHKCS, MIAP | [EMAIL PROTECTED] Chief Consultant| http://www.yuikee.com.hk/ Yui Kee Computing Ltd. | +852 28708555
