Re: DBD error
On 17/07/2020 13:02, Bala GANESH wrote: LD_RUN_PATH="/oracle/product/19.7/lib:/oracle/product/19.7/rdbms/lib" ld -bhalt:4 -G -bI:/usr/opt/perl5/lib/5.28.1/aix-thread-multi/CORE/perl.exp -bnoentry -lpthreads -lc -lm -bE:Oracle.exp Oracle.o dbdimp.o oci8.o -o blib/arch/auto/DBD/Oracle/Oracle.so -L/oracle/product/19.7/lib -lclntsh -ldl -lc -lm -lpthreads -lodm -lbsd_r -lld -lperfstat ld: 0711-736 ERROR: Input file /oracle/product/19.7/lib/libclntsh.so: XCOFF64 object files are not allowed in 32-bit mode. make: 1254-004 The error code from the last command is 8. Stop. pibdev:root[/oracle/PIBDEV2/post/softwares/DBD-Oracle-1.80]# make install "/usr/bin/perl" -MExtUtils::Command::MM -e 'cp_nonempty' -- Oracle.bs blib/arch/auto/DBD/Oracle/Oracle.bs 644 rm -f blib/arch/auto/DBD/Oracle/Oracle.so LD_RUN_PATH="/oracle/product/19.7/lib:/oracle/product/19.7/rdbms/lib" ld -bhalt:4 -G -bI:/usr/opt/perl5/lib/5.28.1/aix-thread-multi/CORE/perl.exp -bnoentry -lpthreads -lc -lm -bE:Oracle.exp Oracle.o dbdimp.o oci8.o -o blib/arch/auto/DBD/Oracle/Oracle.so -L/oracle/product/19.7/lib -lclntsh -ldl -lc -lm -lpthreads -lodm -lbsd_r -lld -lperfstat ld: 0711-736 ERROR: Input file /oracle/product/19.7/lib/libclntsh.so: XCOFF64 object files are not allowed in 32-bit mode. make: 1254-004 The error code from the last command is 8. Stop. I'd guess you are not using the same C compiler to build DBD::Oracle that was used to build Perl. Did you build Perl or did you get it pre-built from a repository for AIX? If the latter then you need to use the same compiler as was used to build Perl. You are mixing 32 bit compiled code with 64 bit compiled code as it stands. Martin -- Martin J. Evans Wetherby, UK
Re: Perl script excessively executing statement
On 18/02/2020 07:57, JohnD Blackburn wrote: After reviewing the log4perl output from DBIx when running this script I found the following perl module being executed: /usr/local/lib64/perl5/DBD/Oracle.pm Which contains the following subroutine: sub execute_for_fetch { my ($sth, $fetch_tuple_sub, $tuple_status) = @_; my $row_count = 0; my $err_total = 0; my $tuple_count="0E0"; my $tuple_batch_status; my $dbh = $sth->{Database}; my $batch_size =($dbh->{'ora_array_chunk_size'}||= 1000); if(defined($tuple_status)) { @$tuple_status = (); $tuple_batch_status = [ ]; } my $finished; while (1) { my @tuple_batch; for (my $i = 0; $i < $batch_size; $i++) { $finished = $fetch_tuple_sub->(); push @tuple_batch, [@{$finished || last}]; } last unless @tuple_batch; my $err_count = 0; my $res = ora_execute_array($sth, \@tuple_batch, scalar(@tuple_batch), $tuple_batch_status, $err_count ); if (defined($res)) { #no error $row_count += $res; } else { $row_count = undef; } $err_total += $err_count; $tuple_count+=@tuple_batch; push @$tuple_status, @$tuple_batch_status if defined($tuple_status); last if !$finished; } #error check here return $sth->set_err($DBI::stderr, "executing $tuple_count generated $err_total errors") if $err_total; return wantarray ? ($tuple_count, defined $row_count ? $row_count : undef) : $tuple_count; } Is that "while(1)" loop a potential contender for causing this? Not unless you are using execute_array. The code you posted didn't look like it was. Martin -- Martin J. Evans Wetherby, UK
New release of DBD::ODBC 1.61
1.61 2020-01-30 [BUG FIXES] Fix 12blob.t test by pali Fix searching for ODBC libraries in system by pali (#15) [ENHANCEMENTS] use PERL_NO_GET_CONTEXT for more performance by markusbeth (#13) [MISCELLANEOUS] Fix travis builds for older Perls by pali Thank you to all who contributed. Martin -- Martin J. Evans Wetherby, UK
DBD-Oracle 1.79 released to the CPAN
I have just uploaded DBD::Oracle 1.79 to the CPAN. [BUG FIXES] Fix Avoid GCC-ism so that HP-UX can compile (GH#92, Dean Hamstead) Destroy envhp with last dbh (GH#93, GH#89, Dean Hamstead, CarstenGrohmann) Thanks to everyone who contributed. Martin -- Martin J. Evans Wetherby, UK
Re: DBD::ODBC and issues getting LongTruncOk / LongReadLen to take effect
On 15/11/2018 23:54, Daniel Kasak wrote: Hi Martin. Sorry for the very long delay. We had abandoned the ODBC driver in favour of the native DBD::DB2, and I've been working on this project outside my normal work hours anyway, so got bogged down in other issues ... I've uploaded the trace to: https://tesla.duckdns.org/downloads/trace.log This might be the problem: DescribeCol column = 11, name = D, namelen = 7, type = unknown(-99), precision/column size = 65536, scale = 0, nullable = 1 SQL_COLUMN_DISPLAY_SIZE = 65536 SQL_COLUMN_LENGTH = 65536 now using col 11: type = unknown (-99), len = 65537, display size = 65537, prec = 65536, scale = 0 and later fetch col#11 D datalen=4294967295 displ=65537 ^^ that is very large and DBD::ODBC does not know what column type -99 is !!dbd_error2(err_rc=-999, what=st_fetch/SQLFetch (long truncated DBI attribute LongTruncOk not set and/or LongReadLen too small), handles=(55ddd5896920,55ddd5896f20,55ddd5926680) !SQLError(55ddd5896920,55ddd5896f20,55ddd5926680) = (HY000, 1, st_fetch/SQLFetch (long truncated DBI attribute LongTruncOk not set and/or LongReadLen too small)) -- HandleSetErr err=1, errstr='st_fetch/SQLFetch (long truncated DBI attribute LongTruncOk not set and/or LongReadLen too small) (SQL-HY000)', state='HY000', undef !! ERROR: 1 'st_fetch/SQLFetch (long truncated DBI attribute LongTruncOk not set and/or LongReadLen too small) (SQL-HY000)' (err#1) 1 <- fetch= ( undef ) [1 items] row1 at db2_syscat_columns.pl line 36 !! ERROR: 1 'st_fetch/SQLFetch (long truncated DBI attribute LongTruncOk not set and/or LongReadLen too small) (SQL-HY000)' (err#1) <- fetchrow_hashref= ( undef ) [1 items] row1 at db2_syscat_columns.pl line 36 seems to confirm it. What is column type -99? Can you dump the schema of that table so we can see what column 11 is? I'm guessing it is an XML type which by default is being returned as a BLOB. See https://www.ibm.com/support/knowledgecenter/fi/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.cli.doc/doc/r0023298.html A temporary workaround (although rather ugly) would be instead of doing a select *, you selected all the columns except column 11. A hack would be to map -99 to a blob in dbdimp.c in dbd_describe() around these lines: /* * change fetched size, decimal digits etc for some types, * The tests for ColDef = 0 are for when the driver does not give * us a length for the column e.g., "max" column types in SQL Server * like varbinary(max). */ fbh->ftype = SQL_C_CHAR; switch(fbh->ColSqlType) { case SQL_VARBINARY: case SQL_BINARY: case -99: <-- try adding this line and rebuilding DBD::ODBC fbh->ftype = SQL_C_BINARY; if (fbh->ColDef == 0) { /* cope with varbinary(max) */ fbh->ColDisplaySize = DBIc_LongReadLen(imp_sth); } break; but of course if you change MapXMLDescribe (which I hadn't heard about before today) to map it to a different type the code would need changing again. Probably a better solution in the short term is to set MapXMLDescribe to -152 as that is SQL_SS_XML which I think may be the same as MS_SQLS_XML_TYPE and of so, that is already handled. See https://fossies.org/linux/DBD-ODBC/dbdimp.h which suggests MS_SQLS_XML_TYPE is -152. Martin -- Martin J. Evans Wetherby, UK The script I'm using to generate this is: https://tesla.duckdns.org/downloads/db2_syscat_columns.pl It dies on the 1st call of $sth->fetchrow_hashref(): DBD::ODBC::st fetchrow_hashref failed: st_fetch/SQLFetch (long truncated DBI attribute LongTruncOk not set and/or LongReadLen too small) (SQL-HY000) at db2_syscat_columns.pl <http://db2_syscat_columns.pl> line 36. Thanks for your time ... Dan On Thu, Apr 5, 2018 at 9:50 PM Martin J. Evans via dbi-users mailto:dbi-users@perl.org>> wrote: On 05/04/18 12:24, Daniel Kasak wrote: > Hi all. > > I'm writing a database utility that has to access IBM's "DashDB" and other DB2-variants. I have their latest ODBC driver, and I have simple queries working. However queries against their system catalog are not working - queries appear to be returning *empty* recordsets. After some laborious debugging, I can see that when I call $sth->fetchrow_array, DBI::errstr is set to: > > st_fetch/SQLFetch (long truncated DBI attribute LongTruncOk not set and/or LongReadLen too small) (SQL-HY000) > > I've dealt with this before. So when constructing my $dbh, I do: > > $self->{dbh}->{LongReadLen} = 65535 * 1024 # 64MB - yes I know it's big > $self->{dbh}->{LongTruncOK} = 1; > > This usually fixes things - or at least h
Re: DBD::ODBC and issues getting LongTruncOk / LongReadLen to take effect
On 05/04/18 12:24, Daniel Kasak wrote: Hi all. I'm writing a database utility that has to access IBM's "DashDB" and other DB2-variants. I have their latest ODBC driver, and I have simple queries working. However queries against their system catalog are not working - queries appear to be returning *empty* recordsets. After some laborious debugging, I can see that when I call $sth->fetchrow_array, DBI::errstr is set to: st_fetch/SQLFetch (long truncated DBI attribute LongTruncOk not set and/or LongReadLen too small) (SQL-HY000) I've dealt with this before. So when constructing my $dbh, I do: $self->{dbh}->{LongReadLen} = 65535 * 1024 # 64MB - yes I know it's big $self->{dbh}->{LongTruncOK} = 1; This usually fixes things - or at least has for SQL Server and Netezza. For DB2 connections however, it appears to have no effect. I've turned on ODBC tracing immediately prior to calling $sth->prepare ... $sth->execute ... $sth->fetchrow_array: https://paste.pound-python.org/show/hS6ur7dwGRsQubr29HFT/ This one was from the query: select * from SYSCAT.COLUMNS Does anyone know why LongTruncOk / LongReadLen are not being honored here? I'm using unixODBC-2.3.6, DBD::ODBC from git, and the latest available DB2 ODBC driver. Please help! Thanks :) Dan There are some very long columns in that result-set but I can't tell from the unixodbc log alone what has happened. Can you reduce your perl to the simplest test you can and rerun with DBI_TRACE=15=trace.log perl mycode.pl and show me the trace.log file. Martin
Re: MySQL IV vs PV
On 13/11/17 17:43, James Cloos wrote: Is there anything in a db handle or statement handle one use to know whether the running instance of DBD::MySQL will return integer columns as IV? I'm adding some code to a layer atop DBI which will force IV (via +=0), but want to avoid doing that were it is not required. The particular code path serializes the returned rows via JSON::XS, hense the need. -JimC See http://search.cpan.org/~timb/DBI-1.637/DBI.pm#sql_type_cast http://search.cpan.org/~mjevans/DBD-ODBC-1.56/ODBC.pm#DiscardString http://search.cpan.org/~mjevans/DBD-ODBC-1.56/ODBC.pm#StrictlyTyped These attributes are implemented in DBD::Oracle and DBD::ODBC and allow the caller of bind_param to stipulate it wants an integer instead of a string. They were originally implemented by me (with Tim's help) when I was retrieving result-sets which I wanted to convert to JSON. JSON treats numbers and string differently and I required numbers to be numbers and not strings. There is of course, the type argument to bind_param as well. Best of luck. Martin
Re: Perl DBI libraries for connecting 12c
On 10/10/17 00:07, Bruce Johnson wrote: On Oct 9, 2017, at 3:52 PM, John R Piercewrote: On 10/9/2017 2:49 PM, Furst, Carl wrote: I believe you have to go to Oracle to get those.. You can get DBI and even DBB::Oracle but it won’t build. You won’t have the Oracle C libs to link off of. You’ll need to install an oracle client library. as I recall, to use CPAN and build modules that work with the Solaris Perl, you have to have the Sun/Oracle Studio C compiler (formerly called Forte), not GCC, unless you leap through some hoops. This used to work, but I've not been down these bunny trails for years and years : http://search.cpan.org/~aburlison/Solaris-PerlGcc-1.3/pod/perlgcc.pod ... you also need to make sure you installed the full oracle developer stuff as part of the client. With Instant Client you have to install the Basic and the SDK packages. The latter gets the developer header files and libs that DBD::Oracle uses to compile the c code. You should probably get the sqlplus package as well as it is used if found, to determine the version of Oracle client you are using. Without it, the Makefile might assume you are oracle 8. Martin
Re: DBD::SQLAnywhere SEGV when Oracle handle exists
On 06-Mar-17 5:38 PM, Douglas Wilson wrote: After some searching, I tried using the ora_connect_with_default_signals with INT and CHLD, and tried setting BEQUEATH_DETACH=yes in a local sqlnet.ora, but still same result. Try reversing the order in which you connect - if you can. On Mar 4, 2017 5:17 AM, "Martin J. Evans" <boh...@ntlworld.com <mailto:boh...@ntlworld.com>> wrote: On 02-Mar-17 10:54 PM, Douglas Wilson wrote: DBD::SQLAnywhere seems to work ok for Sybase IQ, but if I first create a DBD:Oracle handle, the SQLAnywhere connect hangs for a while, and eventually segfaults. FYI on redhat Linux. I don't have the info to hand right now but I've heard similar reports before. I think it had something to do with the method used to connect to Oracle and if that method is chosen it captures SIGCHLD and maybe another signal as well. Martin -- Martin J. Evans Wetherby, UK
Re: DBD::SQLAnywhere SEGV when Oracle handle exists
On 02-Mar-17 10:54 PM, Douglas Wilson wrote: DBD::SQLAnywhere seems to work ok for Sybase IQ, but if I first create a DBD:Oracle handle, the SQLAnywhere connect hangs for a while, and eventually segfaults. FYI on redhat Linux. I don't have the info to hand right now but I've heard similar reports before. I think it had something to do with the method used to connect to Oracle and if that method is chosen it captures SIGCHLD and maybe another signal as well. Martin -- Martin J. Evans Wetherby, UK
Re: DBD for SQL Server
On 06/09/16 14:40, Rob Dixon wrote: Hey Tim I'm wondering why there is no DBD module for Microsoft SQL Server? Is it a technical impossibility, or is it just waiting to be written? Thank you, Rob Dixon Norfolk England Microsoft has aligned itself with ODBC in the past and has reaffirmed that more recently. You can use any number of MS SQL Server ODBC drivers with DBD::ODBC. You can also use DBD::Sybase I'm told. Personally, I see little mileage in writing DBD::FreeTDS (I say that instead of DBD::MSSQLServer as that is the only open implementation of TDS I am aware of) but if someone was brave enough to try then all the best to them. Martin
Re: Help with DBD::Oracle install
On 05/02/2016 20:35, Tim Bunce wrote: Hi. I don't provide support for DBD::Oracle directly. Please email dbi-users@perl.org or, if you'd rather not just yet, then something here will probably help: https://www.google.com/search?q=dbd-oracle+/usr/ucb/cc:++language+optional+software+package+not+installed Tim. On Fri, Feb 05, 2016 at 05:40:52PM +, Linn, Steven D CTR (US) wrote: Hi Tim, I have hit a wall as far as trying to get this module to install, so I was wondering if you might know where I go from here...I am on a Solaris 10 server running Oracle 10.2.0. When I run 'make', I get the following error...Any help would be greatly appreciated! [root: ] rsol0018# ls CONTRIBUTING.mkd Makefile.PL blib oci.def CONTRIBUTORS Oracle.c dbdimp.c oci8.c Changes Oracle.h dbdimp.h ocitrace.h INSTALL Oracle.xs dbivport.hpm_to_blib LICENSE Oracle.xsiexamples t MANIFEST READMEhints typemap META.json README.help.txt lib META.yml README.mkdn mk.pm Makefile Todo mkta.pl [root: ] redslsa0sol0018# make cc -c -I/opt/oracle/product/10.2.0/client_1/rdbms/public -I/opt/oracle/product/10.2.0/client_1/rdbms/demo -I/opt/oracle/product/10.2.0/client_1/rdbms/public -I/opt/oracle/product/10.2.0/client_1/plsql/public -I/opt/oracle/product/10.2.0/client_1/network/public -I/usr/perl5/vendor_perl/5.8.4/i86pc-solaris-64int/auto/DBI -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -D_TS_ERRNO -DPERL_USE_SAFE_PUTENV -xO3 -xspace -xildoff-DVERSION=\"1.74\" -DXS_VERSION=\"1.74\" -KPIC "-I/usr/perl5/5.8.4/lib/i86pc-solaris-64int/CORE" -DUTF8_SUPPORT -DORA_OCI_VERSION=\"10.2.0.5\" -DORA_OCI_102 Oracle.c /usr/ucb/cc: language optional software package not installed *** Error code 1 make: Fatal error: Command failed for target `Oracle.o' You need to install a C compiler - the one your Perl was built with is most likely to work. Martin -- Martin J. Evans Wetherby, UK
Fwd: Re: Extra nulls inserted with binary data (DBD::ODBC on Win7)
--- Begin Message --- On 28/01/16 15:08, Jerrad Pierce wrote: Hello Martin, $DBI::VERSION = 1.634 $DBD::ODBC::VERSION = 1.52 It would also be useful to know the column type in your access DB. I'm updating a LONGBINARY column in a Jet 4 (MDB) database. Thanks Thank you I forgot, MS Access does not support the ODBC API SQLDescribeParam which a) it should and b) is a PITA when a driver does not. Because DBD::ODBC does not know the column type it binds it as a VARCHAR. Access will convert that varchar to a binary using whatever conversion algorithm it uses - obviously not one that works for you. I think you will have to resort to specifying the bind type when you call bind_param. If you are not calling bind_param right now you'll have to start calling it and specify as the type SQL_BINARY. As bind_param types are sticky you can probably call bind_param like so: $st->bind_param(1, undef, {TYPE => SQL_BINARY}); and still continue to call execute as before: $st->execute($parameter_1_value); or you can just call bind_param with the real value and execute with no values. Let me know if you need an example or if this does not sort it out for you. I'm afraid the MS Access ODBC driver is a poor example of ODBC. http://search.cpan.org/~mjevans/DBD-ODBC-1.52/FAQ#Why_am_I_getting_errors_with_bound_parameters? might provide some background. Martin --- End Message ---
Re: MS access
On 02/11/15 07:51, Ismail Chamseddine wrote: I am using Active Perl 5.18.4 64 bit on Windows 7 64 bit The connect string code my $dbh = DBI->connect('dbi:ODBC:driver=microsoft access driver (*.mdb); database= C:\Users\User\Documents\Mydb.accdb'); On Fri, Oct 30, 2015 at 4:07 PM, Martin J. Evans <martin.ev...@easysoft.com <mailto:martin.ev...@easysoft.com>> wrote: On 30/10/2015 13:21, Ismail Chamseddine wrote: I am trying to connect to MS access database on my PC. I am getting an error message of failure, data source not found, (ODBC driver manager) no default driver is mentioned . please help Please try and help us help you and post more information. What Perl are you using, on what operating system? Show the extract of perl script calling DBI's connect method with the arguments. What data sources have you defined in the driver manager? Are they user or system DSNs? Are you using 32 or 64 bit platform (on Windows they use different ODBC administrators. What versions of DBI and presumably DBD::ODBC are you using? Martin Please don't reply just to me - keep it on the list. Try DRIVER= instead of driver= and put {} around the driver name e.g. 'dbi:ODBC:DRVIER={microsoft access driver (*.mdb)};database=C:\Users\User\Documents\Mydb.accdb' Martin
Re: MS access
On 30/10/2015 13:21, Ismail Chamseddine wrote: I am trying to connect to MS access database on my PC. I am getting an error message of failure, data source not found, (ODBC driver manager) no default driver is mentioned . please help Please try and help us help you and post more information. What Perl are you using, on what operating system? Show the extract of perl script calling DBI's connect method with the arguments. What data sources have you defined in the driver manager? Are they user or system DSNs? Are you using 32 or 64 bit platform (on Windows they use different ODBC administrators. What versions of DBI and presumably DBD::ODBC are you using? Martin
Re: DBD::Oracle and RHEL Instant Client rpms
On 20/10/2015 19:53, Bruce Johnson wrote: Has anyone else had trouble installing DBD::Oracle with the latest 12.1 instant client? I installed the Basic and Development IC rpm’s and the DBD installer complained it couldn’t determine what version I had, then complained it couldn’t find demo.mk. I finally got it to work by manually telling it what version and the path to the .mk file (which hadn’t changed from previous versions: /usr/share/oracle//client64…) The various env values were set correctly, and the files were installed in the usual place, the only difference between this time and the last time I set up a RHEL system was I used the latest 12.1 instant client files rather than 11.2. [root@kalendaetest DBD-Oracle-1.74-EIJTlU]# set |grep ora LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib ORACLE_HOME=/usr/lib/oracle/12.1/client64 PATH=/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin:/usr/lib/oracle/12.1/client64/bin TNS_ADMIN=/usr/lib/oracle/12.1/client64/admin It worked, eventually, just wondering if others have run into the issue... I don't use an RPM based system. I download instantclient basic, devel and sqlplus zips and unzip them then point DBD::Oracle at them by setting LD_LIBRARY_PATH. Did you download the sqlplus RPM (assuming there is one) as DBD::Oracle uses sqlplus during the install to work out what version of the Oracle client you have? Martin -- Martin J. Evans Wetherby, UK
new development release 1.53_1 of DBD::ODBC
I've just uploaded DBD::ODBC 1.53_1 to the CPAN. The most significant change is to support DBI's new 64 bit row counts. DBD::ODBC did (and still does) support 64 bit row counts via its own API but this now means if you have an up to date DBI, you can use the normal return from the execute method. If you have a 64 bit platform and Perl you probably want to give this a go before I do a full release. The changes since 1.52 are: 1.53_1 2015-10-16 [BUG FIXES] Strictly speaking this is a bug fix to DBI and not DBD::ODBC but DBI now supports 64 bit row counts where an IV in perl is 64 bits. However, it necessitated changes to DBD::ODBC to pick up the fix. odbc_rows (my workaround since 2012) is still supported but should no longer be required so long as you use this DBD::ODBC and DBI 1.633_92 or above. [INTERNALS] Removed dbd_st_rows and now setting DBIc_ROW_COUNT. [DOCUMENTATION] Add tables and table_info section to deviations from the DBI spec. [MISCELLANEOUS] Change column name in t/rt_101579.t as "method" is a reserved word in. Teradata Thanks to Zhenyi Zhou. Remove duplicate dynamic_config from META.yml. Martin
Re: help with odd DBI perpare/execute errors
On 03/06/15 14:06, William Bulley wrote: Environment Perl script trying to query Oracle 11g database: FreeBSD 9.3-STABLE DBI 1.633 oracle8-client 0.2.0 DBD::Oracle 1.19 I have no trouble connecting with the Oracle database. And I do recover data when I use the temporary workaround described below. I have a query/prepare setup outside a foreach loop where I execute() the prepared query something like this, only more complex: my $query = select column from table where column = ?; my $sth = $dbh-prepare ($query); foreach () { $sth-execute($value); } I was getting invalid string ORA-0911 errors at the question mark. I then replaced the question mark with a number (555) and made the execute() call just $sth-execute(); So, when this fails, what is the value of $value. Assuming you have RaiseError set, you can just put an eval around the execute and if $@ is set, print out $value. This worked. But I really needed to bind to the $value variable in the foreach loop. In reading the DBI POD it said for Oracle the ? is turned into :p1 (in this case). So I replaced the question mark with :p1. Strictly speaking you do not need to do this - ? is fine. DBD::Oracle also supports named parameters e.g., ':myparam' which only has an advantage if you want to use :myparam more than once in the SQL. The prepare statement no longer generated an error, instead the execute statement generated the error: DBD::Oracle::st execute failed: called with 1 bind variables when 0 are needed [for Statement ... ] at script.pl line xxx. Either because you omitted the value from execute but more likely because you need to associate $value with the NAMED parameter i.e., call bind_param. Can any one help me figure out this confusing situation? BTW, I have been using Perl for twenty years and DBI for perhaps ten, and I have used this query/prepare/bind/execute methodology in the past with success. Something is different, but I don't know what to look for. Regards, web... Tell us the column type of 'column' and the value of $value when the original code fails. If this does not enlighten you, reduce this to a small reproducible script and re-run with ora_verbose set to 7. Paste the output somewhere we can view it. Martin
Re: help with odd DBI perpare/execute errors
On 03/06/15 14:38, William Bulley wrote: According to Martin J. Evans boh...@ntlworld.com on Wed, 06/03/15 at 09:15: So, when this fails, what is the value of $value. I just ran it again. The value is 547. Sounds ok but the error is invalid string ORA-0911 You tried to execute a SQL statement that included a special character. http://www.techonthenet.com/oracle/errors/ora00911.php lists various causes. Assuming you have RaiseError set, you can just put an eval around the execute and if $@ is set, print out $value. I don't. I have a print statement in front of the execute to show my what I am passing to the execute() method. I'm not sure I'd trust that - doesn't that mean you are expecting stdin and stout to be in order. If you can easily do it I would stick an eval around it and trap it that way. Also, if you trap it you can print the SQL using http://search.cpan.org/~timb/DBI-1.633/DBI.pm#Statement and the parameters using http://search.cpan.org/~timb/DBI-1.633/DBI.pm#ParamValues Strictly speaking you do not need to do this - ? is fine. DBD::Oracle also supports named parameters e.g., ':myparam' which only has an advantage if you want to use :myparam more than once in the SQL. The query is quite complex -- two SELECT statements connected by a UNION statement -- and the column = ? syntax is used twice. I changed the ? to :myparam in both places and I still get the error: DBD::Oracle::st execute failed: called with 1 bind variables when 0 are needed [for Statement... Either because you omitted the value from execute In this case, it was not omitted. I would not bother changing from ? to named - I seriously doubt this is the issue. but more likely because you need to associate $value with the NAMED parameter i.e., call bind_param. That will be my next test, but I don't hold out much hope for that working either (I've never had to do this in the past). Tell us the column type of 'column' and the value of $value when the original code fails. If this does not enlighten you, reduce this to a small reproducible script and re-run with ora_verbose set to 7. Paste the output somewhere we can view it. The Oracle type for the column in question is NUMBER. I assumed that any integer value would be compatible. The value is 547 for the run that just failed. Never heard of ora_verbose -- where is this to be set? Just in my code somewhere, or on the DBI-connect() method, or where? http://search.cpan.org/~pythian/DBD-Oracle-1.74/lib/DBD/Oracle.pm#ora_verbose Can be set in the connect attributes. Regards, web... If I were you I'd try and simply the original case down as much as possible but getting a trace with ora_verbose might help identify the problem. Martin
Re: help with odd DBI perpare/execute errors
On 03/06/15 17:12, Bruce Johnson wrote: On Jun 3, 2015, at 7:19 AM, William Bulley w...@umich.edu wrote: According to Bruce Johnson john...@pharmacy.arizona.edu on Wed, 06/03/15 at 10:10: Make sure your original $query is delimited by double quotes, not single. I've tried _everything_!! Single quotes. Double quotes. q{} and qq{} (using the latter now). But no matter what I try DBI complains about the darn question mark! It is infuriating, I tell you! :-) Well, I just tested MY theory (RHEL v6.5, oracle 11.2g, oracle instant client for 11.2, perl, v5.10.1 (*) built for x86_64-linux-thread-multi) with: #!/usr/bin/perl use strict; use DBI; my $dbh= DBI-connect(“dbi:Oracle:host=$host, $user, $pass, {RaiseError =1}); my $qry1 ='select ? from dual'; my $qry2 = select ? from dual; my $sth = $dbh-prepare($qry1); $sth-execute('foo'); my ($res)=$sth-fetchrow(); print single quote result is $res \n; $sth =$dbh-prepare($qry2); $sth-execute('bar'); ($res)=$sth-fetchrow(); print double quote result is $res \n; exit; And got: # ./qmarktest.pl single quote result is foo double quote result is bar So yet another fine theory destroyed by reality… Possibly some sort of character set mess up? Could your 'question mark' be something else in the script? Maybe a 16-bit vs 8-bit character? I’ve had some weird issues in the past when I was handed a 16-bit unicode text file of insert statements and tried to run them. Bruce, Just so you know, I've already told William off list that question mark should not get through to Oracle and so the preparse method in DBD::Oracle is not spotting the ? - perhaps because it is not a ? or it thinks it is in a comment, literal etc. However, I expect, as you do, that one of the chrs in the SQL is not what it seems. Sorry, as log file was sent to me I answered it and did not cc the list. Martin
Re: help with odd DBI perpare/execute errors
On 03/06/15 14:06, William Bulley wrote: Environment Perl script trying to query Oracle 11g database: FreeBSD 9.3-STABLE DBI 1.633 oracle8-client 0.2.0 DBD::Oracle 1.19 It seems I missed this ^ A 9 year old DBD::Oracle. I can well believe the preparse code has changed or been fixed in all that time. I've recommended William tries a newer version as I suspect the preparse code it failing since those ? should not get to Oracle. Martin
Re: Issue with DBD::ODBC (SQLServer) and bcp
On 18/05/2015 16:03, Mike Martin wrote: Hi Is it a known issue with running bcp while an active db connection I have a script that basically opens a db connection selects some data parses/transforms the data writes to a text file -- runs bcp to import the data into same database -- does some updates in SQLServer The issue is that if I dont disconnect before running bcp, the op just hangs (with no output). If I disconnect before bcp and then reconnect everything is fine. Is this a known issue Platform Windows 7 SQLServer 2012 DBD::ODBC=1.609 Just sounds like a locking issue to me. How do you connect to your SQL Server db via Perl and do you disable autocommit or start a transaction? Is this a known issue - not given the information provided unless the above. Martin -- Martin J. Evans Wetherby, UK
Re: dbi selectall_hashref drops results
On 15/05/15 15:56, Kyle A. Hamilton wrote: I ran into an issue where dbi’s selectall_hashref will drop results returned from the database. If I change the query to count the number of results returned, it returns 96, but I am missing some records that I get when I manually query the database. If I use selectall_array ref and loop through the results, I get the same data as I get from manually querying the database. I can provide the proprietary code as an example, just want to make sure this is the correct place to submit this. For selectall_hashref you have to give it a key field. If that field is not a unique key you will lose rows. Martin
Re: Memory not being freed with DBD::ODBC
On 20/04/15 17:54, Mike Martin wrote: I have the following script (extremley simplified) foreach my $key (sort {$a=$b} keys %run){ my $inp; $inp=$dbh-selectall_arrayref(select id, [description] from Kaonix_import_base where idint between ? and ? ,undef,$run{$key}-[0], $run{$key}-[1]) ; addrec($inp); -- sub to run very heavy regexs against the select and insert into another table undef($inp); } The problem is that memory is not being freed against the statement handle on each iteration and eventually the script stalls due lack of memories any ideas? Hi Mike, What makes you point the finger at the statement handle not being freed? Can you make this happen in a small standalone script and provide schema? What ODBC driver are you using and what versions of DBI, DBD::ODBC and ODBC driver manager? Martin
New DBD::ODBC 1.52 release
I have just uploaded DBD::ODBC 1.52 to the CPAN. Thanks to everyone who reported issues and in particular to Greg for his Pull Request from the PR challenge and to Neil for running it. If you are not part of the CPAN pull request challenge I believe you can still join - see http://cpan-prc.org/. Below are the changes since the last full release. In particular, I draw your attention to the fix for RT101579 which has caught a few people out recently. If you are upgrading from a much older version please read the Changes file as there have been a number of changes in behaviour in the last year. 1.52 2015-04-15 [MISCELLANEOUS] Changes to the test suite to make it run better with Postgres thanks to Greg Sabino Mullane. 1.51_4 2015-01-18 [BUG FIXES] Numerous errors in the test suite (with SQLite ODBC driver) mostly down to not creating the test table first. [MISCELLANEOUS] Try and make the test suite run ok for SQLite ODBC driver so I can use it in travis-ci. 1.51_3 2015-01-17 [BUG FIXES] RT101579 - using bound input parameters for numeric columns (e.g., SQL_NUMERIC) only works the first time and will quite likely fail with string data, right truncation on the second and subsequent calls to execute. Thanks to Laura Cox for finding. 1.51_2 2014-11-19 [BUG FIXES] The table_info method (ANSI version only) was incorrectly passing the table name for the type argument. I think this bug was introduced last year. 1.51_1 2014-11-14 [BUG FIXES] RT100186 - handle VARBINARY(MAX) parameters with SQL Server native client. Identify libmsodbcsql* as the MS ODBC Driver for Linux as there are some specific workarounds for MS Native Client ODBC driver. Martin
Re: DBD::ODBC Destroy bug?
works flawlessly with the Easysoft and MS ODBC drivers and your ODBC log contradicts the DBI/DBD::ODBC log so I'm going to suggest you've updated or changed your freeTDS driver and this one is broken. Martin On Tue, Mar 31, 2015 at 4:23 AM, Martin J. Evans martin.ev...@easysoft.com mailto:martin.ev...@easysoft.com wrote: On 31/03/15 06:04, Joel Plotkin wrote: Hi, I've attached the sample test8.pl http://test8.pl http://test8.pl script (smallest one possible that creates the problem) and a trace file at level 15. Thanks for any insight, Joel -dbd_st_execute(ac3cb0)=-1 - execute= -1 at test8.pl http://test8.pl line 74 via at test8.pl http://test8.pl line 53 - DESTROY for DBD::ODBC::st (DBI::st=HASH(0xac3818)~INNER) thr#974010 SQLFreeHandle(stmt)=-1 !!dbd_error2(err_rc=-1, what=st_destroy/SQLFreeHandle(__stmt), handles=(c2abd0,c2b1c0,c802c0) ** No error found -1 ** !! ERROR: 1 'Unable to fetch information about the error' (err#1) - DESTROY= undef at test8.pl http://test8.pl line 54 via at test8.pl http://test8.pl line 54 !! ERROR: 1 CLEARED by call to fetchall_arrayref method This is suspicious - SQLFreeHandle failed and then the error was cleared. I cannot reproduce and we need further clues. Instead of starting tracing in the script could you rerun with DBI_TRACE=DBD=x.log e.g., set DBI_TRACE=DBD=x.log perl myscript.pl http://myscript.pl This will put in the x.log file a load of ODBC info for the driver etc - could you send me the whole log file. Another thing well worth doing is enabling tracing at the ODBC level as then we can try and find out why SQLFreeHandle is failing. You can do this by going to the ODBC administrator (make sure you pick the right one 32 bit or 64 bit depending on what your perl is) and selecting the tracing tab, enter a file and click on start then run your script. Martin
Re: DBD::ODBC Destroy bug?
On 31/03/15 06:04, Joel Plotkin wrote: Hi, I've attached the sample test8.pl http://test8.pl script (smallest one possible that creates the problem) and a trace file at level 15. Thanks for any insight, Joel -dbd_st_execute(ac3cb0)=-1 - execute= -1 at test8.pl line 74 via at test8.pl line 53 - DESTROY for DBD::ODBC::st (DBI::st=HASH(0xac3818)~INNER) thr#974010 SQLFreeHandle(stmt)=-1 !!dbd_error2(err_rc=-1, what=st_destroy/SQLFreeHandle(stmt), handles=(c2abd0,c2b1c0,c802c0) ** No error found -1 ** !! ERROR: 1 'Unable to fetch information about the error' (err#1) - DESTROY= undef at test8.pl line 54 via at test8.pl line 54 !! ERROR: 1 CLEARED by call to fetchall_arrayref method This is suspicious - SQLFreeHandle failed and then the error was cleared. I cannot reproduce and we need further clues. Instead of starting tracing in the script could you rerun with DBI_TRACE=DBD=x.log e.g., set DBI_TRACE=DBD=x.log perl myscript.pl This will put in the x.log file a load of ODBC info for the driver etc - could you send me the whole log file. Another thing well worth doing is enabling tracing at the ODBC level as then we can try and find out why SQLFreeHandle is failing. You can do this by going to the ODBC administrator (make sure you pick the right one 32 bit or 64 bit depending on what your perl is) and selecting the tracing tab, enter a file and click on start then run your script. Martin
Re: DBD::ODBC Destroy bug?
On 24/03/15 18:04, Joel Plotkin wrote: Hi, A few notes: 1) the final return; at the end of Main was a typo... I was trying to clean up the example and had consolidated an additional sub-routine into one big main() function even w/ this typo, I still get the original error as described previously. I guessed as much. 2) I (just) tried to get the latest version from git, but I just see $DBD::ODBC::VERSION = '1.51_4'; from ODBC.pm I can't seem to find version 1.53_3 at git... I'm not a git expert, so I'll ask my staff for help to see if they can find 1.53_3 for me. sorry, typo from me - that is the version I am using. 3) *** I'm using perl 5.10.1 ... maybe this is the issue. I'll upgrade to perl 5.19.10 and see if the issue goes away. I'll keep you posted, Joel I've since tried on a load of perlbrews and I still cannot duplicate your issue. Martin On Tue, Mar 24, 2015 at 12:24 PM, Martin J. Evans martin.ev...@easysoft.com mailto:martin.ev...@easysoft.com wrote: On 24/03/15 15:45, Joel Plotkin wrote: Hi, I have recently ported a large (1.4M line) perl application from: Centos 6.6 DBI version 1.6.09 DBD::ODBC version 1.23 To: Centos 6.6 DBI version 1.6.33 DBD::ODBC version 1.50 (and same issue with 1.50_4) The error doesn't occur in the 1.23 version, only the later 1.50* versions. This error occurs using MS SQL Server 2005, but I don't think it's db server dependent. The specific error occurs when one creates a statement handle in a subroutine and returns the statement handle via a return value. Specific test program attached. Sample code that generates error below: **__** #!/usr/bin/perl use strict; select((select(STDOUT), $|=1)[$[]); package main; BEGIN { # Standard modules use Carp; use DBI; }; my $dbh; MAIN: { # open data connection my $attr = {}; my $odbc = DBI:ODBC:_odbc; my $db_nm=X; my $db_user_nm=X; my $db_user_passwd=X; carp opening new db connection\n; $dbh = DBI-connect($odbc, $db_user_nm, $db_user_passwd, $attr) || carp Error opening database; my $trace_filename = /tmp/dbi_trace.dat; $dbh-trace(2, $trace_filename); # run query my $sql = SELECT Name FROM Person WHERE p_id = 123; *my ($sth) = do_pexect($sql);* my $rowcache = $sth-fetchall_arrayref(); #pop off all rows. destroys $rowcache structure. while (my $row = shift @$rowcache) { my ($last_nm) = @$row; } $sth-finish(); # # Run same sql a 2nd time # #undef $sth; *($sth) = do_pexect($sql);* carp This is a test 3\n; $rowcache = $sth-fetchall_arrayref(); carp This is a test 4\n; #pop off all rows. destroys $rowcache structure while (my $row = shift @$rowcache) { my ($last_nm) = @$row; } $sth-finish(); undef $sth; return; } sub do_pexect { # input parameters my($sql) = @_; my $out; $out = $dbh-prepare($sql) || carp prepare ($sql): .$dbh-errstr; $out-execute() || carp execute ($sql): .$dbh-errstr; return($out); } **__** Basic trace error: - prepare for DBD::ODBC::db (DBI::db=HASH(0x13c8928)~__0x1668428 ' SELECT name FROM Person WHERE p_id = 123') thr#139f010 - prepare= DBI::st=HASH(0x156c370) at test12.pl http://test12.pl http://test12.pl line 92 - execute for DBD::ODBC::st (DBI::st=HASH(0x156c370)~__0x16e8898) thr#139f010 - execute= -1 at test12.pl http://test12.pl http://test12.pl line 95 - DESTROY for DBD::ODBC::st (DBI::st=HASH(0x13c8c28)~__INNER) thr#139f010 !! ERROR: 1 'Unable to fetch information about the error' (err#1) - DESTROY= undef at test12.pl http://test12.pl http://test12.pl line 70 DBD::ODBC::st DESTROY failed: Unable to fetch information about the error at test12.pl http://test12.pl http://test12.pl line 70. This is a test 3 at test12.pl http://test12.pl http://test12.pl line 70. main::joel_init() called at test12.pl http://test12.pl http://test12.pl line 36 !! The ERROR '1' was CLEARED by call to fetchall_arrayref method *** The basic
Re: DBD::ODBC Destroy bug?
On 25/03/15 16:29, Joel Plotkin wrote: Hi, Are you running under Windows or Linux? We don't see the issue under ActiveState Perl- just under Linux/Centos. Linux Ubuntu 12.04.5 LTS Also, if it matters, we're running FreeTDS and UnixODBC. I am also running unixODBC. unixODBC.x86_64 2.2.14-14.el6 @base unixODBC-devel.x86_64 2.2.14-14.el6 @base freetds.x86_64 0.91-2.el6 @epel freetds-devel.x86_640.91-2.el6 @epel I guess we can try installed a demo versoin of the EasySoft drivers and see if that makes a difference. I really wouldn't bother right now. Could you reduce the script to the smallest which demonstrates the problem but enable level 15 tracing and post the new script and the output? Joel Ps- we too used PerlBrew to install Perl 5.20 - specifically: perl 5.20.2 DBI 1.633 DBD::ODBC = 1.50 Martin
Re: DBD::ODBC Destroy bug?
On 24/03/15 15:45, Joel Plotkin wrote: Hi, I have recently ported a large (1.4M line) perl application from: Centos 6.6 DBI version 1.6.09 DBD::ODBC version 1.23 To: Centos 6.6 DBI version 1.6.33 DBD::ODBC version 1.50 (and same issue with 1.50_4) The error doesn't occur in the 1.23 version, only the later 1.50* versions. This error occurs using MS SQL Server 2005, but I don't think it's db server dependent. The specific error occurs when one creates a statement handle in a subroutine and returns the statement handle via a return value. Specific test program attached. Sample code that generates error below: #!/usr/bin/perl use strict; select((select(STDOUT), $|=1)[$[]); package main; BEGIN { # Standard modules use Carp; use DBI; }; my $dbh; MAIN: { # open data connection my $attr = {}; my $odbc = DBI:ODBC:_odbc; my $db_nm=X; my $db_user_nm=X; my $db_user_passwd=X; carp opening new db connection\n; $dbh = DBI-connect($odbc, $db_user_nm, $db_user_passwd, $attr) || carp Error opening database; my $trace_filename = /tmp/dbi_trace.dat; $dbh-trace(2, $trace_filename); # run query my $sql = SELECT Name FROM Person WHERE p_id = 123; *my ($sth) = do_pexect($sql);* my $rowcache = $sth-fetchall_arrayref(); #pop off all rows. destroys $rowcache structure. while (my $row = shift @$rowcache) { my ($last_nm) = @$row; } $sth-finish(); # # Run same sql a 2nd time # #undef $sth; *($sth) = do_pexect($sql);* carp This is a test 3\n; $rowcache = $sth-fetchall_arrayref(); carp This is a test 4\n; #pop off all rows. destroys $rowcache structure while (my $row = shift @$rowcache) { my ($last_nm) = @$row; } $sth-finish(); undef $sth; return; } sub do_pexect { # input parameters my($sql) = @_; my $out; $out = $dbh-prepare($sql) || carp prepare ($sql): .$dbh-errstr; $out-execute() || carp execute ($sql): .$dbh-errstr; return($out); } Basic trace error: - prepare for DBD::ODBC::db (DBI::db=HASH(0x13c8928)~0x1668428 ' SELECT name FROM Person WHERE p_id = 123') thr#139f010 - prepare= DBI::st=HASH(0x156c370) at test12.pl http://test12.pl line 92 - execute for DBD::ODBC::st (DBI::st=HASH(0x156c370)~0x16e8898) thr#139f010 - execute= -1 at test12.pl http://test12.pl line 95 - DESTROY for DBD::ODBC::st (DBI::st=HASH(0x13c8c28)~INNER) thr#139f010 !! ERROR: 1 'Unable to fetch information about the error' (err#1) - DESTROY= undef at test12.pl http://test12.pl line 70 DBD::ODBC::st DESTROY failed: Unable to fetch information about the error at test12.pl http://test12.pl line 70. This is a test 3 at test12.pl http://test12.pl line 70. main::joel_init() called at test12.pl http://test12.pl line 36 !! The ERROR '1' was CLEARED by call to fetchall_arrayref method *** The basic error is that when the handle is created the 2nd time in the sub-routine, DBD destroys the original $sth attached to the $dbh. Then, when the sub-routine finishes, perl auto-trash compactor tries to destroy $sth a 2nd time as it's the left hand value of the equate statement: ($sth) = do_pexec($sql); but this doesn't work as the $sth has already been destroyed in the subroutine. This trace/error didn't occur in the earlier versions of DBD but now do... I can get rid of the error by doing: undef $sth; before the 2nd use of the variable but this should not be necessary. Has anyone else experienced this issue with the newer versions of DBD? Thanks for any insight, Joel Sorry Joel, I forgot about your email but in any case this list is a good place to send your issue. When I run your script as it stands (with edits below) I get: opening new db connection at mje/joel.pl line 23. This is a test 3 at mje/joel.pl line 45. This is a test 4 at mje/joel.pl line 47. Can't return outside a subroutine at mje/joel.pl line 56. perl 5.19.10 DBI 1.631 DBD::ODBC 1.53_3 (which is latest git) I'm going to SQL Server enterprise edition via the Easysoft SQL Server driver. Obviously I changed the logon details and the SQL. BTW, I don't think you need those calls to finish (see finish in DBI docs). Martin
Re: Help with using Triggers MySql/Innodb
On 26/02/15 14:42, david wrote: MySql Gurus This may be the wrong mailing list for this question, so I apologize in advance for breaking some rule. I'm trying to get a trigger to work. My environment is a Perl DBI interface to a MySql database on Centos 6. I use the InnoDB database. I have two tables involved in this operation: table: House, with fields: HouseID INT AUTO_INCREMENT PeopleCount INT other fields not relevant to this discussion Table: People, with fields PeopleID INT AUTO_INCREMENT HouseID INT (shows which house the person belongs to) Name VARCHAR(30) other fields not relevant to this discussion Whenever I insert (or delete) a row to the People table, I want the PeopleCount in the House (identified by the HouseID in the people table) to be incremented or decremented. My latest attempt is a trigger: CREATE TRIGGER after_insert_people AFTER INSERT ON People FOR EACH ROW BEGIN UPDATE House SET PeopleCount = PeopleCount + 1 WHERE HouseID = People.HouseID; END I don't use mysql these days but in Oracle that would be something like where houseid = :new.houseid. The trigger gets triggered, but a diagnostic says that People.HouseID is an undefined column. Any clues would help. Thanks David I would actually suggest you don't use a trigger for this and also that you don't store the count of people in a house in a column in the house table. You can always calculate how many people there are in a house but if you use triggers or some other mechanism to keep the count you run the risk of the count not actually agreeing with the rows with that house id. In other words, I think this is generally bad design. Martin
Re: Statement Handle Attribute Precision
On 22/01/15 15:29, Josh Nijenhuis wrote: old docs from dbi = 1.39 i believe The PRECISION attribute contains a reference to an array of integer values that represent the defined length or size of the columns in the SQL statement. There are two general ways in which the precision of a column is calculated. String datatypes, such as CHAR and VARCHAR, return the maximum length of the column. For example, a column defined within a table as: locationVARCHAR2(1000) would return a precision value of 1000. Numeric datatypes are treated slightly differently in that the number of significant digits is returned. This may have no direct relationship with the space used to store the number. Oracle, for example, stores numbers with 38 digits of precision but uses a variable length internal format of between 1 and 21 bytes. For floating-point types such as REAL, FLOAT, and DOUBLE, the maximum ``display size'' can be up to seven characters greater than the precision due to concatenated sign, decimal point, the letter ``E,'' a sign, and two or three exponent digits. CHANGES in DBI 1.41 (svn rev 130), 22nd February 2004 |Clarified that ||$sth||-{PRECISION} is OCTET_LENGTH ||for| |char types.| dbi = 1.41 Type: array-ref, read-only Returns a reference to an array of integer values for each column. For numeric columns, the value is the maximum number of digits (without considering a sign character or decimal point). Note that the display size for floating point types (REAL, FLOAT, DOUBLE) can be up to 7 characters greater than the precision (for the sign + decimal point + the letter E + a sign + 2 or 3 digits). For any character type column the value is the OCTET_LENGTH, in other words the number of bytes, not characters. That is a little strange as I believed the definition initially came from the ODBC spec which says The maximum column size that the server supports for this data type. For numeric data, this is the maximum precision. For string data, this is the length in characters. I can assure you that DBD::ODBC will not return octets unless an ODBC driver is broken. (More recent standards refer to this as COLUMN_SIZE but we stick with PRECISION for backwards compatibility.) I have tried COLUMN_SIZE but it seems to not work, with this error. Can't get DBI::st=HASH(0x7fcee73ce078)-{COLUMN_SIZE}: unrecognised attribute name at ./ut_testdictsync line 19. because of the bit you quoted but we stick with PRECISION for backwards compatibility Martin On 01/22/15 08:26, Michael Gerdau wrote: In in standard command-line client this session choice would be set in my.ini under the name default-character-set, if I'm not mistaken. Changing its value from utf8 to latin1 could maybe solve the problem. On linux, so its my.cnf and all the character-sets are latin1 and character-set-server as well Even if it were utf8 and switching to latin1 would solve the problem: My possibly naive expectation would be that PRECISION returns the number of characters and not the number of bytes required to represent these characters. Best wishes, Michael
Re: Escaping placeholders
On 22/12/2014 00:39, Darren Duncan wrote: I agree with Greg's counter-proposal, from which I derive my own words here. 1. I propose that there be no modification to the DBI spec related to new escaping whether optional or not, so leave things the way they are here, SQL continues to be interpreted the way it long has by default. 2. When users want to use operators in PostgreSQL that contain literal ? in their names, then they enable DBD::Pg's pg_placeholder_dollaronly so that ? are no longer treated as placeholders. Likewise, pg_placeholder_nocolons can be enabled when they don't want literal : to indicate a placeholder either. Users would either do this directly if they're using DBI/DBD::Pg directly, or indirectly using their over-top framework of choice. When users aren't using the ? operators et al, they can leave things alone which will work as normal. 3. SQL::Abstract et al, those tools external to DBI/DBDs, are the correct and proper places to modify where users of said want to use the operators with ? names and such. These tools already have special knowledge of individual DBMS systems to work with them effectively, and the ? operators is just one more of those things. The users of said tools may have to flip a configuration switch possibly so $1 etc are used behind the scenes, if necessary, but that's okay because the use of ? operators only happens when the users choose to make a change to use them anyway. In summary, now is not the time or place to be introducing backslashing doubled or otherwise in DBI such as discussed, that's a poor solution and its better to save such risky/etc changes for when there's a more compelling case for them. I should also mention I feel it is perfectly reasonable for each DBMS to have operators composed of any characters they want where doing so makes sense within the context of the SQL/etc itself. See also that Perl itself has both ? and : and etc as operator names, Perl 6 even more so, and I don't see anyone thinking that's a bad idea. So I have no problem with PostgreSQL having ? in operator names such as it did. Its not like the SQL standard reserves ? or whatever for prepared statement parameters, that's defined to be implementation dependent I believe (WD 9075-2:200w(E) 20.6 prepare statement). -- Darren Duncan I tend to agree with Greg and Darren on this for all the same reasons. If it could be done in DBI without affecting DBDs then I might have less of a problem with it as anything wanting this feature would only have to check DBI. There are already too many differences between DBDs and introducing yet another one which would make applications and interfaces even more complex. Plus, this seems to be a postgres specific issue and escaping of ? or : would only benefit other DBDs in the few rare cases where those are legitmate chrs in themselves. There is already an inconsistency in placeholders as some DBDs need/support ':name' when binding and some only support 'name'. DBD::ODBC already has a ignore placeholders switch for when users want to pass SQL containing a ? or : when neither is a placeholder e.g., SQL Server procedures often contain colons. If it was not for some poor ODBC drivers that don't support SQLNumParams DBD::ODBC could get away with not parsing the SQL at all which would be my preferred solution and in fact, the version I use does not parse the SQL at all. Adding escaping would mean more work and probably taking a copy of the scalar for modification. However, I think Greg and Darren's arguments are strong enough on their own. If, it ends up where the DBI spec is changed to allow escaping can we please introduce the DBD capabilities API at the same time. I know I started the thread some time ago and put together an initial list then did not move it forward, but it would make application and interface layers so much easier if they could just say 'does this DBD do X' and maybe 'how do I make it do X'. Martin -- Martin J. Evans Wetherby, UK On 2014-12-21 7:17 AM, Greg Sabino Mullane (the tenth man) wrote: Tim Bunce wrote: For code not using DBIx::Class the pg_placeholder_dollaronly attribute might work, see https://metacpan.org/pod/DBD::Pg#Placeholders Yes, this is the current canonical solution. It's a real shame that ? was used as an operator, but that horse has left the barn. For code using DBIx::Class the problem is more tricky. I'm pretty sure that SQL::Abstract and thus DBIx::Class only support question mark placeholders. That means it probably impossible to use expressions containing a question mark operator with SQL::Abstract/DBIx::Class. (Though I'd be delighted to be proven wrong.) So I think the DBI spec for placeholders needs to be extended to allow a way to 'escape' a question mark that the driver would otherwise treat as a placeholder. The obvious and natural approach would be to use a backslash before a question mark. The backslash would be removed by the driver before
New 1.50 release of DBD::ODBC
I've just uploaded DBD::ODBC 1.50 to the CPAN. This is the culmination of a series of 4 development releases in the 1.49 series. There are a number of bug fixes, enhancements, deprecated features and most importantly some changes in behaviour. See below for a list of changes. 1.50 2014-07-25 [BUG FIXES] The 80_odbc_diags.t test could fail if a driver fails a table does not exist test in the prepare instead of the execute. 1.49_4 2014-07-08 [BUG FIXES] Fixed sql_type_cast.t test which assumed column aliases which stay lowercase. Fixed 87_odbc_lob_read.t test which did not bow out of the test properly if the database was not MS SQL Server. [DOCUMENTATION] Revised the query notification example and documentation. Added a link to a better Query Notification article. 1.49_3 2014-05-01 [CHANGE IN BEHAVIOUR] As warned years ago, this release removes the odbc_old_unicode attribute. If you have a good reason to use it speak up now before the next non-development release. [BUG FIXES] Fix rt89255: Fails to create test table for tests using PostgreSQL odbc driver. Change test suite to fallback on PRECISION if COLUMN_SIZE is not found. [ENHANCEMENTS] Added support for MS SQL Server Query Notification. See the new section in the pod. Added a currently undocumented (and experimental) odbc_describe_param method on a statement handle which takes a parameter number as the only argument and returns an array of the data type, parameter size, decimal digits and nullable (as per SQLDescribeParam). [DOCUMENTATION] Added FAQ on truncated column names with freeTDS. [MISCELLANEOUS] I have removed the experimental tag for odbc_getdiagfield and odbc_getdiagrec methods. 1.49_2 2014-04-26 [BUG FIXES] Change to data_sources in 1.49_1 could lead to a compile error since data_sources was not returning a value if an error occurred. 1.49_1 2014-04-25 [BUG FIXES] If you had a lot of DSNs on Windows (more than 280 but it depends on the length of their names) and called the data_sources method it could crash your script. Code internally changed to stop putting the DSNs returned on the stack. [CHANGE IN BEHAVIOUR] As warned years ago, the private data_sources method has been removed - use DBI one instead. [MISCELLANEOUS] Added FAQ entry of maximum number of allowed parameters. Martin
Re: DBD::Informix
On 09/07/14 15:58, Helmut wrote: Hi all, i have problems building DBD::Informix. Running Makefile.pl ends up with .. /opt/IBM/informix/lib/libifasf.so: undefined reference to `dlopen' /opt/IBM/informix/lib/esql/libifos.so: undefined reference to `crypt' /opt/IBM/informix/lib/libifasf.so: undefined reference to `dlclose' /opt/IBM/informix/lib/libifasf.so: undefined reference to `dlerror' /opt/IBM/informix/lib/libifasf.so: undefined reference to `dlsym' collect2: Fehler: ld gab 1 als Ende-Status zurück Failed to link test program esqltest running on configuration at lib/DBD/Informix/TechSupport.pm line 225. System: ArchLinux x86-64 Perl: v5.18.2 Informix: clientsdk.4.10.FC4DE.LINUX DBD-Informix-2013.0521 The bug report perl -Ilib BugReport A is below / attached. What can i do to track the problem down? Thank you Helmut I don't do Informix but all those missing symbols are in dl.so. Try to find a way to get the Makefile.PL to add -ldl to the link line. Maybe just setting LDFLAGS=-ldl and exporting it before running Makefile.PL will do it. Martin
Re: (Fwd) Issue :DBD::Oracle installation
On 04/06/14 12:44, tim.bu...@pobox.com wrote: - Forwarded message from Ranjitha ranji...@infosys.com - Date: Wed, 4 Jun 2014 11:05:34 + From: Ranjitha ranji...@infosys.com To: tim.bu...@pobox.com tim.bu...@pobox.com, t...@cpan.org t...@cpan.org Subject: Issue :DBD::Oracle installation Hi Tim Facing an issue while installing DBD::Oracle module from CPAN. Direct installation on server cpan DBD::Oracle is failing. I manually tried installing the module which CPAN has downloaded, below is the log. ciw-app1:~/.cpan/build/DBD-Oracle-1.74-vjnZmt # perl Makefile.PL Multiple copies of Driver.xst found in: /usr/lib/perl5/site_perl/5.10.0/x86_64-linux-thread-multi/auto/DBI/ /usr/lib/perl5/vendor_perl/5.10.0/x86_64-linux-thread-multi/auto/DBI/ at Makefile.PL line 39 Using DBI 1.631 (for perl 5.01 on x86_64-linux-thread-multi) installed in /usr/lib/perl5/site_perl/5.10.0/x86_64-linux-thread-multi/auto/DBI/ Configuring DBD::Oracle for perl 5.01 on linux (x86_64-linux-thread-multi) Remember to actually *READ* the README file! Especially if you have any problems. Installing on a linux, Ver#2.6 Using Oracle in /usr/lib/oracle/11.2/client64 sqlplus: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory This is your problem. You need to get sqlplus working in your environment before attempting to build DBD::Oracle. Martin
Re: (Fwd) Issue :DBD::Oracle installation
On 06/06/14 12:17, Ranjitha wrote: Hi Martin Screen shot from yast : showing sqlplus is installed. I have sqlplus installed in my server, then why the error? Same way I have installed sqlplus in other servers also, no issue there. Thanks and regards, You've misunderstood me. I wasn't saying you have not installed sqlplus. I was saying you need to make sure you can run sqlplus before attempting to build DBD::Oracle. So sqlplus should be on your PATH or findable under LD_LIBRARY_PATH which you should set and export. It is pointless trying to build DBD::Oracle if you cannot successfully run sqlplus - at best it will default to oracle 8. Martin -Original Message- From: Martin J. Evans [mailto:martin.ev...@easysoft.com] Sent: Friday, June 06, 2014 1:44 PM To: Ranjitha; dbi-users@perl.org Subject: Re: (Fwd) Issue :DBD::Oracle installation On 04/06/14 12:44, tim.bu...@pobox.com mailto:tim.bu...@pobox.com wrote: - Forwarded message from Ranjitha ranji...@infosys.com mailto:ranji...@infosys.com - Date: Wed, 4 Jun 2014 11:05:34 + From: Ranjitha ranji...@infosys.com mailto:ranji...@infosys.com To: tim.bu...@pobox.com mailto:tim.bu...@pobox.com tim.bu...@pobox.com mailto:tim.bu...@pobox.com, t...@cpan.org mailto:t...@cpan.org t...@cpan.org mailto:t...@cpan.org Subject: Issue :DBD::Oracle installation Hi Tim Facing an issue while installing DBD::Oracle module from CPAN. Direct installation on server cpan DBD::Oracle is failing. I manually tried installing the module which CPAN has downloaded, below is the log. ciw-app1:~/.cpan/build/DBD-Oracle-1.74-vjnZmt # perl Makefile.PL Multiple copies of Driver.xst found in: /usr/lib/perl5/site_perl/5.10.0/x86_64-linux-thread-multi/auto/DBI/ /usr/lib/perl5/vendor_perl/5.10.0/x86_64-linux-thread-multi/auto/DBI/ at Makefile.PL line 39 Using DBI 1.631 (for perl 5.01 on x86_64-linux-thread-multi) installed in /usr/lib/perl5/site_perl/5.10.0/x86_64-linux-thread-multi/auto/DBI/ Configuring DBD::Oracle for perl 5.01 on linux (x86_64-linux-thread-multi) Remember to actually *READ* the README file! Especially if you have any problems. Installing on a linux, Ver#2.6 Using Oracle in /usr/lib/oracle/11.2/client64 sqlplus: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory This is your problem. You need to get sqlplus working in your environment before attempting to build DBD::Oracle. Martin
Re: Probably simple, stupid question about Oracle TAF and DBI
On 02/06/2014 01:25, Bruce Johnson wrote: I've been tasked with making our Oracle db (used primarily as a DB back end for web applications) function within a disaster recovery plan now that we actually have hardware at a second site to do disaster recovery (other than 'Go buy a new server and restore it from backup' :-) I'm planning to use Oracle's Data Guard to manage the db backend, which seems quite straightforward, but my question is about managing failover (what Oracle calls TAF) From what I'm reading http://search.cpan.org/~pythian/DBD-Oracle-1.74/lib/DBD/Oracle.pm#TAF_(Transparent_Application_Failover) DBD::Oracle uses the tns entry for TAF: eg: (FAILOVER_MODE= (TYPE=select) (METHOD=basic) (RETRIES=10) (DELAY=10)) Am I right in thinking that since each time a script is called it creates a handle, does its thing, then ends, in the event of a failure, the, each call of the script will take over 100 seconds to fail over, given the above settings? Therefore, to take advantage of TAF I'd have to utilize persistent DBI handles (using, for example, Apache::DBI http://perl.apache.org/docs/1.0/guide/databases.html) and once the first call to a failed instance takes 100 seconds to fail over, the $dbh will then talk to the backup server thereafter? I fixed the TAF support when we were using Oracle RAC and I haven't used it in a while. Our application connects and stays connected and when the first server failed it took a while (initially) to fail over to the other server. I'm sorry but I don't have that kit available right now to retest so this is only my recollection. All I know for definite is what I wrote in the DBD::Oracle pod. The actual failover is implemented in the OCI libraries and all DBD::Oracle does is make the appropriate calls to set it up and call the callback. As such Oracle docs will be your friend. Try it and see and report back. Up to this point I only know of myself as having used TAF with DBD::Oracle. There may be an article on the Easysoft web site. Martin -- Martin J. Evans Wetherby, UK
New development release of DBD::ODBC 1.49_3
I've just uploaded DBD::ODBC 1.49_3 to the CPAN. Please test it especially if you've always wanted to use MS SQL Server Query Notification as it should now support it. Changes since last full release are: 1.49_3 2014-05-01 [CHANGE IN BEHAVIOUR] As warned years ago, this release removes the odbc_old_unicode attribute. If you have a good reason to use it speak up now before the next non-development release. [BUG FIXES] Fix rt89255: Fails to create test table for tests using PostgreSQL odbc driver. Change test suite to fallback on PRECISION if COLUMN_SIZE is not found. [ENHANCEMENTS] Added support for MS SQL Server Query Notification. See the new section in the pod. Added a currently undocumented (and experimental) odbc_describe_param method on a statement handle which takes a parameter number as the only argument and returns an array of the data type, parameter size, decimal digits and nullable (as per SQLDescribeParam). [DOCUMENTATION] Added FAQ on truncated column names with freeTDS. [MISCELLANEOUS] I have removed the experimental tag for odbc_getdiaffield and odbc_getdiagrec methods. 1.49_2 2014-04-26 [BUG FIXES] Change to data_sources in 1.49_1 could lead to a compile error since data_sources was not returning a value if an error occurred. 1.49_1 2014-04-25 [BUG FIXES] If you had a lot of DSNs on Windows (more than 280 but it depends on the length of their names) and called the data_sources method it could crash your script. Code internally changed to stop putting the DSNs returned on the stack. [CHANGE IN BEHAVIOUR] As warned years ago, the private data_sources method has been removed - use DBI one instead. [MISCELLANEOUS] Added FAQ entry of maximum number of allowed parameters. Martin
Re: make test failed for perl module DBD-Oracle-1.70
On 19/03/2014 18:31, Jillapelli, Ramakrishna wrote: Hi, struck with “DBD-Oracle-1.70” You are likely to get more/better help if you tell us more. Platform? Perl version and where it came from? compiler? Oracle you are compiling against - full Oracle or Instant Client and version? Anyway, I can guess some from the following: Make test returned the following error: cp lib/DBD/Oracle/Troubleshooting/Win32.pod blib/lib/DBD/Oracle/Troubleshooting/Win32.pod cp mk.pm blib/arch/auto/DBD/Oracle/mk.pm /usr/bin/perl -e 'use ExtUtils::Mksymlists; Mksymlists(NAME = DBD::Oracle, DL_FUNCS = { }, FUNCLIST = [], DL_VARS = []);' /usr/bin/perl -p -e s/~DRIVER~/Oracle/g /usr/opt/perl5/lib/site_perl/5.10.1/aix-thread-multi/auto/DBI/Driver.xst Oracle.xsi You are possibly using perl 5.10.1 - pretty old now but should still be ok. /usr/opt is an unusual place to see Perl so I'm guessing this is a Perl installed via your operating system vendor. /usr/bin/perl /usr/opt/perl5/lib/5.10.1/ExtUtils/xsubpp -typemap /usr/opt/perl5/lib/5.10.1/ExtUtils/typemap -typemap typemap Oracle.xs Oracle.xsc mv Oracle.xsc Oracle.c xlc_r -q32 -c -I/oracle/product/v102/fullclnt/rdbms/public xlc_r. That looks like the IBM AIX compiler? You need to compile and link DBD::Oracle using the same tools (compiler and linker) as IBM (or whoever) built your Perl. If this is an IBM compiled Perl for AIX they probably used their compiler (xlc) - a perl -V should verify this. -I/oracle/product/v102/fullclnt/rdbms/demo -I/oracle/product/v102/fullclnt/rdbms/public -I/oracle/product/v102/fullclnt/plsql/public -I/oracle/product/v102/fullclnt/network/public -I/usr/opt/perl5/lib/site_perl/5.10.1/aix-thread-multi/auto/DBI -D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE -qmaxmem=-1 -qnoansialias -DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT -qlanglvl=extended -I/usr/local/include -q32 -D_LARGE_FILES -qlonglong -O -DVERSION=\1.70\ -DXS_VERSION=\1.70\ -I/usr/opt/perl5/lib/5.10.1/aix-thread-multi/CORE -DUTF8_SUPPORT -DORA_OCI_VERSION=\10.2.0.1\ -DORA_OCI_102 Oracle.c Guessing you are using Oracle OCI 10.2.0.1 and this is a full install of Oracle and not instant client. xlc_r -q32 -c -I/oracle/product/v102/fullclnt/rdbms/public -I/oracle/product/v102/fullclnt/rdbms/demo -I/oracle/product/v102/fullclnt/rdbms/public -I/oracle/product/v102/fullclnt/plsql/public -I/oracle/product/v102/fullclnt/network/public -I/usr/opt/perl5/lib/site_perl/5.10.1/aix-thread-multi/auto/DBI -D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE -qmaxmem=-1 -qnoansialias -DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT -qlanglvl=extended -I/usr/local/include -q32 -D_LARGE_FILES -qlonglong -O -DVERSION=\1.70\ -DXS_VERSION=\1.70\ -I/usr/opt/perl5/lib/5.10.1/aix-thread-multi/CORE -DUTF8_SUPPORT -DORA_OCI_VERSION=\10.2.0.1\ -DORA_OCI_102 dbdimp.c xlc_r -q32 -c -I/oracle/product/v102/fullclnt/rdbms/public -I/oracle/product/v102/fullclnt/rdbms/demo -I/oracle/product/v102/fullclnt/rdbms/public -I/oracle/product/v102/fullclnt/plsql/public -I/oracle/product/v102/fullclnt/network/public -I/usr/opt/perl5/lib/site_perl/5.10.1/aix-thread-multi/auto/DBI -D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE -qmaxmem=-1 -qnoansialias -DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT -qlanglvl=extended -I/usr/local/include -q32 -D_LARGE_FILES -qlonglong -O -DVERSION=\1.70\ -DXS_VERSION=\1.70\ -I/usr/opt/perl5/lib/5.10.1/aix-thread-multi/CORE -DUTF8_SUPPORT -DORA_OCI_VERSION=\10.2.0.1\ -DORA_OCI_102 oci8.c Running Mkbootstrap for DBD::Oracle () chmod 644 Oracle.bs rm -f blib/arch/auto/DBD/Oracle/Oracle.so LD_RUN_PATH=/oracle/product/v102/fullclnt/lib32:/oracle/product/v102/fullclnt/rdbms/lib32 ld -bhalt:4 -G -bI:/usr/opt/perl5/lib/5.10.1/aix-thread-multi/CORE/perl.exp -bE:Oracle.exp -bnoentry -lpthreads -lc -lm -L/usr/local/lib Oracle.o dbdimp.o oci8.o -q32 -L/oracle/product/v102/fullclnt/lib32/ -L/oracle/product/v102/fullclnt/rdbms//lib32/ -lclntsh -lld -lm `cat /oracle/product/v102/fullclnt/lib32/sysliblist` -lm -lpthreads -o blib/arch/auto/DBD/Oracle/Oracle.so ld: 0706-012 The -q flag is not recognized. ld: 0706-012 The -3 flag is not recognized. ld: 0706-012 The -2 flag is not recognized. That is a bit strange as it is passing -q32 to ld which is the linker. What does a perl -V output? You could try searching the Makefile for -q32 and removing it from anything that looks like a linker (ld) command argument e.g., LDFLAGS. That is a bit of a hack but it should get you further. If you are struggling with this mail the Makefile to me (only) and I'll take a look. make: The error code from the last command is 255. Stop. [root@ews-pgh1-esmd2] /home/rj46/DBD-Oracle-1.70 q32 option is mandatory? no idea what your question is there. Can you please guide how to proceed on this? Thanks Regards, Martin -- Martin J. Evans Wetherby, UK
Re: I have a weird issue with a script running under cron
On 17/02/14 18:50, Bruce Johnson wrote: On Feb 17, 2014, at 11:41 AM, Bruce Ferrell bferr...@baywinds.org wrote: Bruce, The error says your script is attempting to load /usr/local/lib64/perl5/auto/DBD/Oracle/Oracle.so for DBD::Oracle. is that the correct path for that library? it looks like you built DBD::Oracle against the Oracle occi libraries and those are often in different locations than the regular client libs. Where is libocci.so.11.1 located? Make sure that path is available also. I built DBD::Oracle against this Instant client install. Both command-line and web apps run fine, it’s just this cron job. [root@merthiolate bin]# find / -name libocci.so.11.1 -print /usr/lib/oracle/11.2/client64/lib/libocci.so.11.1 Right where it’s supposed to be on $LD_LIBRARY_PATH Permissions are correct as well : -rw-r--r-- 1 root root 1971762 Sep 17 2011 /usr/lib/oracle/11.2/client64/lib/libocci.so.11.1 This is odd… Just as an aside as this caught me out yesterday. I moved our test system to the latest instant client and DBD::Oracle. As per usual I changed LD_LIBRARY_PATH to point to new instant client but when I ran perl Makefile.PL I noticed it set ORACLE_HOME to the OLD instant client path. I wondered how it had managed to do that then realised my PATH still pointed to the old instant client and it had managed to run sqlplus from there and chosen the old path. It was easily solved by setting my PATH to the new instant client too. Martin
Re: Surprising DBD::Oracle error raised
On 04/02/14 19:36, David Nicol wrote: $price_sth-execute; my ($o_file_price) = $price_sth-fetchrow_array(); if ($price_sth-fetch) { $this-log_error('ERROR: scalar select returned second row at %s line %d', __FILE__, __LINE__); } I expected the fetch to return undef, but it throws an Oracle error. My best ignorant guess here is that fetchrow_array does some cleanup on one-row datasets, but that isn't documented. Advise? That is in deed interesting. When I run the following with DBD::ODBC to MS SQL Server: use strict; use warnings; use DBI; my $h = DBI-connect(); eval { $h-do(q/drop table mje/); }; $h-do(q/create table mje (a int)/); my $s = $h-prepare(q/insert into mje values(?)/); $s-execute(1); $s-execute(2); $s = $h-prepare(q/select * from mje where a = 1/); $s-execute; my ($row) = $s-fetchrow_array; print $row\n; $row = $s-fetch; print $row\n; I get: 1 Use of uninitialized value $row in concatenation (.) or string at mje/fetch_off_end.pl line 20. However, I get the same with DBD::Oracle so how is you code different from the above. Martin
Re: Surprising DBD::Oracle error raised
On 05/02/14 13:36, John Scoles wrote: Well isn't he is calling with the alias 'fetch' isn't he: if ($price_sth-fetch) { and he is calling it in I think scalar context $row = $s-fetch; so am I. my ($row) = $s-fetchrow_array; As far as I can see I did the same. vs if ($price_sth-fetch) There is the odd chance that he is doing the SQL against a 'view', 'cursor' or alike but I doupt that is it. Perhaps David can tell us more. Martin Date: Wed, 5 Feb 2014 13:25:03 + From: martin.ev...@easysoft.com To: davidni...@gmail.com; dbi-users@perl.org Subject: Re: Surprising DBD::Oracle error raised On 04/02/14 19:36, David Nicol wrote: $price_sth-execute; my ($o_file_price) = $price_sth-fetchrow_array(); if ($price_sth-fetch) { $this-log_error('ERROR: scalar select returned second row at %s line %d', __FILE__, __LINE__); } I expected the fetch to return undef, but it throws an Oracle error. My best ignorant guess here is that fetchrow_array does some cleanup on one-row datasets, but that isn't documented. Advise? That is in deed interesting. When I run the following with DBD::ODBC to MS SQL Server: use strict; use warnings; use DBI; my $h = DBI-connect(); eval { $h-do(q/drop table mje/); }; $h-do(q/create table mje (a int)/); my $s = $h-prepare(q/insert into mje values(?)/); $s-execute(1); $s-execute(2); $s = $h-prepare(q/select * from mje where a = 1/); $s-execute; my ($row) = $s-fetchrow_array; print $row\n; $row = $s-fetch; print $row\n; I get: 1 Use of uninitialized value $row in concatenation (.) or string at mje/fetch_off_end.pl line 20. However, I get the same with DBD::Oracle so how is you code different from the above. Martin
Re: Surprising DBD::Oracle error raised
On 05/02/14 14:08, David Nicol wrote: On Wed, Feb 5, 2014 at 7:25 AM, Martin J. Evans martin.ev...@easysoft.com wrote: That is indeed interesting. When I run the following with DBD::ODBC to MS SQL Server: use strict; use warnings; use DBI; my $h = DBI-connect(); eval { $h-do(q/drop table mje/); }; $h-do(q/create table mje (a int)/); my $s = $h-prepare(q/insert into mje values(?)/); $s-execute(1); $s-execute(2); $s = $h-prepare(q/select a from mje where a = 1/); $s-execute; my ($row) = $s-fetchrow_array; # fetch result print $row\n; $row = $s-fetch; # fetch status print $row\n; I get: 1 Use of uninitialized value $row in concatenation (.) or string at mje/fetch_off_end.pl line 20. However, I get the same with DBD::Oracle so how is you code different from the above. Martin The code is the same. Rather, close enough, my second fetch was in boolean context while yours is in a storage context, but I don't think Perl cares, both contexts are scalar. And I've got RaiseError set. So have I now and it made no difference. And my SQL, which joins two tables, has three placeholders, bound with bind_param between the prepare_cached and the execute. Mine also use prepare_cached and a bind_param now - no change. And your warning verifies that the second fetch returned an undef, as documented, while mine threw an exception falsely claiming that the statement hadn't been executed yet: An Oracle error has occurred : DBD::Oracle::st fetch failed: ERROR no statement executing (perhaps you need to call execute first) SQL Text select a from mje where a = 1 ... And you're certainly using more recent builds of everything. Which is probably it. Can you try updated versions so we can rule this out or alternatively, tell me what you are using and I'll try and reproduce it. Martin
Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
On 31/01/14 16:21, Tim Bunce wrote: On Fri, Jan 31, 2014 at 09:11:28AM -0500, John Scoles wrote: A final note on this. Seems there was a very very long unknown bug in DBI which was only fix a few days ago wiht DB 1.6.31 If you mean Callbacks getting an inner handle, that wasn't a bug as such. More like a design choice that proved non-optimal. [1]http://blogs.perl.org/mt/mt.fcgi?__mode=view_type=entryid=5570blog_id=2165 That's http://blogs.perl.org/users/byterock/2014/01/callbacks-ate-my-brain.html I presume. The end result of this bug was that when callbacks are used on the statement handle some attributes will not be there so you programmer who did this $sth-FETCH( 'ParamValues' ), # WTF? - returns a reference to an array of hashes was most likely complaing that the $sth-{ParamValues}, should return a ref but was just returning undef. So he 'Kludged' the code to get the value directly with the FETCH which works I'm not sure what you're saying here John. Using $sth-FETCH('ParamValues') is perfectly reasonable. It was required before 1.631 and optional with 1.631+ now that $h-{ParamValues} works. sort of, but it does bleed memory every so slighly. Are you sure? This is the first I've heard of such a leak. Tim. I've found no evidence of a memory leak with a simple test calling ParamValues a lot with some parameters. However, I'm not using ORA_VARCHAR2_TABLE. The code is: else if (kl==11 strEQ(key, ParamValues)) { HV *pvhv = newHV(); if (imp_sth-all_params_hv) { SV *sv; char *key; I32 keylen; hv_iterinit(imp_sth-all_params_hv); while ( (sv = hv_iternextsv(imp_sth-all_params_hv, key, keylen)) ) { phs_t *phs = (phs_t*)(void*)SvPVX(sv); /* placeholder struct */ (void)hv_store(pvhv, key, keylen, newSVsv(phs-sv), 0); } } retsv = newRV_noinc((SV*)pvhv); cacheit = FALSE; } which looks sane to me right now. ORA_VARCHAR2_TABLE seems to do strange things with parameters I don't quite get right now. As I said previously to Hélder and John (some of the discussion was off dbi-users list presumably because it contained log data), although I accept taking the call to ParamValues out has on this occasion made the problem go away I don't understand why. I think there is more to this than it so far looks but without a way of reproducing it myself I won't be spending any more time on it. If it is reproducible in a standalone script I will happily look again. Martin
Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
On 28/01/2014 10:56, hhferreira wrote: Hi John Martin, Our application is single threaded and does not uses connection pool neither statement caching. Below I'm sending the code where new connections are created. snipped a lot Hi Hélder, I'm answering now as I don't want you to think I/we are ignoring you - certainly I am not. The most likely way for us/me to find a fix is if you provide us/me with some standalone code which demonstrates the problem. I've been asked this myself some times and it is also some times disheartening but the fact here is that your issue is complex and very difficult to diagnose remotely when we cannot see and execute the failing code. I wrote a tiny fraction of DBD::Oracle but help maintain it now and I don't even use the facility you are using and certainly never wrote it. I realise you may have management wanting a quick solution and I will definitely look at any code which reproduces the problem. Sadly, I don't right now see any other way to go. Martin -- Martin J. Evans Wetherby, UK
Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
Just so everyone knows, I pointed hhferreira here in the following thread on perl monks: https://perlmonks.org/?node_id=1071794 Martin On 24/01/14 10:29, hhferreira wrote: Hi Guys, Hope you can provide us some enlightenment! We have the following code which basically calls an oracle procedure passing as inputs 6 bind arrays (converted in ORA_VARCHAR2_TABLE elements) and one string: /my $sth = $self-prepare( q{/ /begin pkg_abcdef.pr_setAbcdef(/ / :in_sourceType,/ / :in_sourceNames,/ / :in_peerTypes,/ / :in_peerNames,/ / :in_writables,/ / :in_requireLevels,/ / :in_testdefs/ / );/ /end;/ /} );/ / / /$sth-bind_param( ':in_sourceType',$sourceType, / /{ ora_type = ORA_VARCHAR2 } );/ /$sth-bind_param( ':in_sourceNames', $sourceNames, / /{ ora_type = ORA_VARCHAR2_TABLE } );/ /$sth-bind_param( ':in_peerTypes', $peerTypes, / /{ ora_type = ORA_VARCHAR2_TABLE } );/ /$sth-bind_param( ':in_peerNames', $peerNames, / /{ ora_type = ORA_VARCHAR2_TABLE } );/ /$sth-bind_param( ':in_writables', $writables, / /{ ora_type = ORA_VARCHAR2_TABLE } );/ /$sth-bind_param( ':in_requireLevels', $requireLevels, / /{ ora_type = ORA_VARCHAR2_TABLE } );/ /$sth-bind_param( ':in_testdefs', $testDefs, / /{ ora_type = ORA_VARCHAR2_TABLE } );/ / / /$sth-execute();/ The problem is that if we have around 1000 elements per array the call works beautifully, but with for instance 1500 it raises an exception, namely an invalid number of bind elements! /19:05:57 ERROR: Caught an exception from DB: DBD::Oracle::st execute failed: called with 3443804 bind variables when 7 are needed [for Statement.../ / / Here is the output of dbi_Trace=15: /.../ /bd_rebind_ph_varchar2_table(): Copying length=1 array[1558]='N'. dbd_rebind_ph_varchar2_table(): Copying length=1 array[1559]='N'. dbd_rebind_ph_varchar2_table(): Copying length=1 array[1560]='N'. dbd_rebind_ph_varchar2_table(): Copying length=1 *array[1561]*='N'. OCIBindByName(b877a34,c2e3b5c,b8724c8,:in_testdefs,placeh_len=12,value_p=dd5d418,value_sz=2,dty=5,indp=dcaf908,alenp=dbc6708,rcodep=0,maxarr_len=1562,curelep=c2e3b90 (*=1562),mode=DEFAULT,0)=SUCCESS OCIOCIBindArrayOfStruct(b90a14c,b8724c8,2,2,2,0)=SUCCESS OCIAttrGet(b90a14c,OCI_HTYPE_BIND,c2e3b48,0,31,b8724c8)=SUCCESS dbd_rebind_ph_varchar2_table(): bind :in_testdefs == ARRAY(0xdc7adc0) (in, not-utf8, csid 1-0-1, ftype 201, csform 0 (0)-0 (0), maxlen 2, maxdata_size 0) OCIAttrSet(b90a14c,OCI_HTYPE_BIND, ffa5e998,0,Attr=31,b8724c8)=SUCCESS rebind :in_testdefs done with ftype 201 (ORA_VARCHAR2_TABLE) - bind_param= ( 1 ) [1 items] at /mycode/start/..ComponentSet.pm line 400 via at /mycode/start/..ComponentSet.pm line 877 {{ execute callback CODE(0xb832be8) being invoked 1 - FETCH for DBD::Oracle::st (DBI::st=HASH(0xd077218)~INNER 'ParamValues') thr#8916008 1 - FETCH= ( HASH(0xd0758e8)7keys ) [1 items] at /mycode/start/..DBConn.pm line 301 via at /mycode/start/..ComponentSet.pm line 402 }} execute callback CODE(0xb832be8) returned - execute for DBD::Oracle::st (DBI::st=HASH(0xd39bbf0)~0xd077218) thr#8916008 *!! ERROR: -1 'called with 11541898 bind variables when 7 are needed' (err#0)*- execute= ( undef ) [1 items] at /mycode/start/..ComponentSet.pm line 402 via at /mycode/start/..ComponentSet.pm line 877 FETCH DISPATCH (DBI::st=HASH(0xd077218) rc2/1 @2 g2 ima404 pid#30775) at /mycode/start/..ComponentSet.pm line 402 via at /mycode/start/..ComponentSet.pm line 877 1 - FETCH for DBD::Oracle::st (DBI::st=HASH(0xd077218)~INNER 'ParamValues') thr#8916008 ERROR: -1 'called with 11541898 bind variables when 7 are needed' (err#0) 1 - FETCH= ( HASH(0xd39f058)7keys ) [1 items] at /mycode/start/..ComponentSet.pm line 402 via at /mycode/start/..ComponentSet.pm line 877 - HandleError on DBI::st=HASH(0xd077218) via CODE(0xb832ab8) (undef) can DISPATCH (DBI::st=HASH(0xd39bbf0) rc1/6 @2 g2 ima100 pid#30775) at /mycode/start/..Dumper/HumanReadable.pm line 130 via at /mycode/start/..Dumper/HumanReadable.pm line 259 - can(dump) = 0 (? 0) can DISPATCH (DBI::st=HASH(0xd39bbf0) rc1/6 @2 g2 ima100 pid#30775) at /mycode/start/..Dumper/HumanReadable.pm line 130 via at /mycode/start/..Dumper/HumanReadable.pm line 259 - can(dumpKeyNames) = 0 (? 0) can DISPATCH (DBI::st=HASH(0xd39bbf0) rc1/6 @2 g2 ima100 pid#30775) at /mycode/start/..Dumper/HumanReadable.pm line 130 via at /mycode/start/..Dumper/HumanReadable.pm line 259 - can(dumpKeyStyles) = 0 (? 0) can DISPATCH (DBI::st=HASH(0xd39bbf0) rc1/6 @2 g2 ima100 pid#30775) at /mycode/start/..Dumper/HumanReadable.pm line 130 via at /mycode/start/..Dumper/HumanReadable.pm line 259 - can(dumpKeyComments) = 0 (? 0) can DISPATCH (DBI::st=HASH(0xd39bbf0) rc1/6 @2 g2 ima100 pid#30775) at /mycode/start/..Dumper/HumanReadable.pm
Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
On 24/01/14 10:29, hhferreira wrote: Hi Guys, Hope you can provide us some enlightenment! We have the following code which basically calls an oracle procedure passing as inputs 6 bind arrays (converted in ORA_VARCHAR2_TABLE elements) and one string: /my $sth = $self-prepare( q{/ /begin pkg_abcdef.pr_setAbcdef(/ / :in_sourceType,/ / :in_sourceNames,/ / :in_peerTypes,/ / :in_peerNames,/ / :in_writables,/ / :in_requireLevels,/ / :in_testdefs/ / );/ /end;/ /} );/ / / /$sth-bind_param( ':in_sourceType',$sourceType, / /{ ora_type = ORA_VARCHAR2 } );/ /$sth-bind_param( ':in_sourceNames', $sourceNames, / /{ ora_type = ORA_VARCHAR2_TABLE } );/ /$sth-bind_param( ':in_peerTypes', $peerTypes, / /{ ora_type = ORA_VARCHAR2_TABLE } );/ /$sth-bind_param( ':in_peerNames', $peerNames, / /{ ora_type = ORA_VARCHAR2_TABLE } );/ /$sth-bind_param( ':in_writables', $writables, / /{ ora_type = ORA_VARCHAR2_TABLE } );/ /$sth-bind_param( ':in_requireLevels', $requireLevels, / /{ ora_type = ORA_VARCHAR2_TABLE } );/ /$sth-bind_param( ':in_testdefs', $testDefs, / /{ ora_type = ORA_VARCHAR2_TABLE } );/ / / /$sth-execute();/ The problem is that if we have around 1000 elements per array the call works beautifully, but with for instance 1500 it raises an exception, namely an invalid number of bind elements! /19:05:57 ERROR: Caught an exception from DB: DBD::Oracle::st execute failed: called with 3443804 bind variables when 7 are needed [for Statement.../ / / Here is the output of dbi_Trace=15: snipped log As I answered in perlmonks, that log didn't give me enough info. Perhaps you could send me personally all the log. PERL version: 5.12.1 DBI version: 1.611 DBD::Oracle: 1.21 That version of DBD::Oracle is very old - 11th April 2008. On the other hand your DBI is 19th April 2010. I'd rather not debug the issue on versions so old. Is it at all possible you can try the latest versions so we can at least see if the problem is already fixed? You can do this without actually installing (overwriting) your existing versions (ask if you are unsure how to do this). We have made a standalone script and it works perfectly even with 2 entries per array. In our application we are using perl objects all over the code, can this be a memory leak somewhere else in the code?! Shame, as this would definitely be the best way to go. It would be worth putting a bit more effort into this. Any help would be highly appreciated! Thanks in advance. Best Regards, Hélder Hugo Ferreira Martin
Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
On 24/01/14 12:26, hhferreira wrote: Hi, We have already tried using ora_maxarray_numentries and other similar attributes unsuccessfully. Martin found that immediately before the error the following message is written: {{ execute callback CODE(0xb832be8) being invoked However we have done a dbi_trace with 1000 elements in the arrays (which works!) to see whether a similar message is logged and it is, so I would not go into that direction though. Will setup a test environment using the latest DBD and DBI versions to see whether the leaks in DBD are causing this behavior. Thanks. Best Regards, Hélder Hugo Ferreira The reason I pointed out the execute callback is that it is only called just before the failure and we cannot see from the trace what code is in it. If we cannot see the code who knows what it is doing? 1 - FETCH for DBD::Oracle::st (DBI::st=HASH(0xd077218)~INNER 'ParamValues') thr#8916008 1 - FETCH= ( HASH(0xd0758e8)7keys ) [1 items] at /home/ Might have been interesting if we knew what was in it. Perhaps you could get ParamValues just before execute and if execute fails catch it and Dumper them. use Data::Dumper; . . my $pv = $sth-{ParamValues}; eval { $sth-execute; }; if (my $ev = $@) { print Dumper($pv); die $ev; } However, I still think testing the latest DBI/DBD::Oracle is the best thing to do first. Martin On Fri, Jan 24, 2014 at 12:09 PM, John Scoles byter...@hotmail.com mailto:byter...@hotmail.com wrote: As Martin said that is rather old version of DBD only 3 since native exe_array was introduced 1.18, and I rember there being some leaks in early version of the native exe_array. If you can upgrade you DBD. Yyou might try to set the 'ora_maxarray_numentries' on you binds as well as that works on the Oracle side of things to limit memory. As it runs stands alone as you say it might be that the things you are binnding are not being released by perl as a referace to them may still exist. Cheers John Date: Fri, 24 Jan 2014 10:53:54 + From: boh...@ntlworld.com mailto:boh...@ntlworld.com To: hhferre...@gmail.com mailto:hhferre...@gmail.com; dbi-users@perl.org mailto:dbi-users@perl.org Subject: Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE) On 24/01/14 10:29, hhferreira wrote: Hi Guys, Hope you can provide us some enlightenment! We have the following code which basically calls an oracle procedure passing as inputs 6 bind arrays (converted in ORA_VARCHAR2_TABLE elements) and one string: /my $sth = $self-prepare( q{/ / begin pkg_abcdef.pr_setAbcdef(/ / :in_sourceType,/ / :in_sourceNames,/ / :in_peerTypes,/ / :in_peerNames,/ / :in_writables,/ / :in_requireLevels,/ / :in_testdefs/ / );/ / end;/ /} );/ / / /$sth-bind_param( ':in_sourceType', $sourceType, / / { ora_type = ORA_VARCHAR2 } );/ /$sth-bind_param( ':in_sourceNames', $sourceNames, / / { ora_type = ORA_VARCHAR2_TABLE } );/ /$sth-bind_param( ':in_peerTypes', $peerTypes, / / { ora_type = ORA_VARCHAR2_TABLE } );/ /$sth-bind_param( ':in_peerNames', $peerNames, / / { ora_type = ORA_VARCHAR2_TABLE } );/ /$sth-bind_param( ':in_writables', $writables, / / { ora_type = ORA_VARCHAR2_TABLE } );/ /$sth-bind_param( ':in_requireLevels', $requireLevels, / / { ora_type = ORA_VARCHAR2_TABLE } );/ /$sth-bind_param( ':in_testdefs', $testDefs, / / { ora_type = ORA_VARCHAR2_TABLE } );/ / / /$sth-execute();/ The problem is that if we have around 1000 elements per array the call works beautifully, but with for instance 1500 it raises an exception, namely an invalid number of bind elements! /19:05:57 ERROR: Caught an exception from DB: DBD::Oracle::st execute failed: called with 3443804 bind variables when 7 are needed [for Statement.../ / / Here is the output of dbi_Trace=15: snipped log As I answered in perlmonks, that log didn't give me enough info. Perhaps you could send me personally all the log. PERL version: 5.12.1 DBI version: 1.611 DBD::Oracle: 1.21 That version of DBD::Oracle is very old - 11th April 2008. On the other hand your DBI is 19th April 2010. I'd rather not debug the issue on versions so old. Is it at all possible you can try the latest versions so we can at least see if the problem is already fixed? You can do this without actually installing (overwriting) your existing versions (ask if you are unsure how to do this). We have made a standalone script and it works perfectly even with 2 entries per array. In our application we are using perl objects all over
Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
On 24/01/14 15:03, John Scoles wrote: I will try and have a quick look at it. I would go with perlbrew http://perlbrew.pl/ as you can have many differnt versions of the mods/perl and test them all at once. The problem with perlbrew is Hélder will have to install all the modules his app needs in addition to the new DBI and DBD::Oracle and he'll probably end up with loads of other newer modules thus potentially moving the problem. PERL5LIB setting or: download and build DBI in dir1 and download and build DBD::Oracle in dir2 then run your app with perl -Idir1/blib/lib -Idir1/blib/arch -Idir2/blib/lib -Idir2/blib/arch myapp.pl Martin -- Date: Fri, 24 Jan 2014 14:59:25 + Subject: Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE) From: hhferre...@gmail.com To: martin.ev...@easysoft.com CC: byter...@hotmail.com; boh...@ntlworld.com; dbi-users@perl.org Hi Guys, We have made the dump of the contents of /$sth-{ParamValues} /into the attached file which basically contains all values set for the 6 input binds (ora_varchar2_table elements actually). We will proceed with the test environment setup using the latest perl and module versions, Martin mentioned this could be done without actually installing (overwriting) our existing versions, I suppose you are referring to those environment variables such like PERL5LIB which we can tweak to use the right versions right? Or there is a better approach? Our working environment is rather complex (big company overweight) and that task can take a while although seems simple to perform so if there is a simple way do let us know :-) Thanks for your hints! Best Regards, Hélder Hugo Ferreira On Fri, Jan 24, 2014 at 1:28 PM, Martin J. Evans martin.ev...@easysoft.com mailto:martin.ev...@easysoft.com wrote: On 24/01/14 12:26, hhferreira wrote: Hi, We have already tried using ora_maxarray_numentries and other similar attributes unsuccessfully. Martin found that immediately before the error the following message is written: {{ execute callback CODE(0xb832be8) being invoked However we have done a dbi_trace with 1000 elements in the arrays (which works!) to see whether a similar message is logged and it is, so I would not go into that direction though. Will setup a test environment using the latest DBD and DBI versions to see whether the leaks in DBD are causing this behavior. Thanks. Best Regards, Hélder Hugo Ferreira The reason I pointed out the execute callback is that it is only called just before the failure and we cannot see from the trace what code is in it. If we cannot see the code who knows what it is doing? 1 - FETCH for DBD::Oracle::st (DBI::st=HASH(0xd077218)~INNER 'ParamValues') thr#8916008 1 - FETCH= ( HASH(0xd0758e8)7keys ) [1 items] at /home/ Might have been interesting if we knew what was in it. Perhaps you could get ParamValues just before execute and if execute fails catch it and Dumper them. use Data::Dumper; . . my $pv = $sth-{ParamValues}; eval { $sth-execute; }; if (my $ev = $@) { print Dumper($pv); die $ev; } However, I still think testing the latest DBI/DBD::Oracle is the best thing to do first. Martin On Fri, Jan 24, 2014 at 12:09 PM, John Scoles byter...@hotmail.com mailto:byter...@hotmail.com mailto:byter...@hotmail.com mailto:byter...@hotmail.com wrote: As Martin said that is rather old version of DBD only 3 since native exe_array was introduced 1.18, and I rember there being some leaks in early version of the native exe_array. If you can upgrade you DBD. Yyou might try to set the 'ora_maxarray_numentries' on you binds as well as that works on the Oracle side of things to limit memory. As it runs stands alone as you say it might be that the things you are binnding
Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)
On 24/01/14 14:59, hhferreira wrote: Hi Guys, We have made the dump of the contents of /$sth-{ParamValues} /into the attached file which basically contains all values set for the 6 input binds (ora_varchar2_table elements actually). Looks good to me. wc -l on the file shows 9389 and / 6 = ~ 1564 I cannot as yet explain why dbdxst_bind_params thinks there are 3443804 parameters. Even in hex 3443804 is 348c5c so it doesn't look like an overflow. The code reporting the issue is as follows: static int dbdxst_bind_params(SV *sth, imp_sth_t *imp_sth, I32 items, I32 ax) { /* Handle binding supplied values to placeholders. */ /* items = one greater than the number of params*/ /* ax = ax from calling sub, maybe adjusted to match items */ dTHX; int i; SV *idx; if (items-1 != DBIc_NUM_PARAMS(imp_sth) DBIc_NUM_PARAMS(imp_sth) != DBIc_NUM_PARAMS_AT_EXECUTE ) { char errmsg[99]; /* clear any previous ParamValues before error is generated */ SV **svp = hv_fetch((HV*)DBIc_MY_H(imp_sth),ParamValues,11,FALSE); if (svp SvROK(*svp) SvTYPE(SvRV(*svp)) == SVt_PVHV) { HV *hv = (HV*)SvRV(*svp); hv_clear(hv); } sprintf(errmsg,called with %d bind variables when %d are needed, (int)items-1, DBIc_NUM_PARAMS(imp_sth)); DBIh_SET_ERR_CHAR(sth, (imp_xxh_t*)imp_sth, -1, -1, errmsg, Nullch, Nullch); return 0; } and I don't particularly like that casting but I don't think it explains the problem. Martin We will proceed with the test environment setup using the latest perl and module versions, Martin mentioned this could be done without actually installing (overwriting) our existing versions, I suppose you are referring to those environment variables such like PERL5LIB which we can tweak to use the right versions right? Or there is a better approach? Our working environment is rather complex (big company overweight) and that task can take a while although seems simple to perform so if there is a simple way do let us know :-) Thanks for your hints! Best Regards, Hélder Hugo Ferreira On Fri, Jan 24, 2014 at 1:28 PM, Martin J. Evans martin.ev...@easysoft.com mailto:martin.ev...@easysoft.com wrote: On 24/01/14 12:26, hhferreira wrote: Hi, We have already tried using ora_maxarray_numentries and other similar attributes unsuccessfully. Martin found that immediately before the error the following message is written: {{ execute callback CODE(0xb832be8) being invoked However we have done a dbi_trace with 1000 elements in the arrays (which works!) to see whether a similar message is logged and it is, so I would not go into that direction though. Will setup a test environment using the latest DBD and DBI versions to see whether the leaks in DBD are causing this behavior. Thanks. Best Regards, Hélder Hugo Ferreira The reason I pointed out the execute callback is that it is only called just before the failure and we cannot see from the trace what code is in it. If we cannot see the code who knows what it is doing? 1 - FETCH for DBD::Oracle::st (DBI::st=HASH(0xd077218)~INNER 'ParamValues') thr#8916008 1 - FETCH= ( HASH(0xd0758e8)7keys ) [1 items] at /home/ Might have been interesting if we knew what was in it. Perhaps you could get ParamValues just before execute and if execute fails catch it and Dumper them. use Data::Dumper; . . my $pv = $sth-{ParamValues}; eval { $sth-execute; }; if (my $ev = $@) { print Dumper($pv); die $ev; } However, I still think testing the latest DBI/DBD::Oracle is the best thing to do first. Martin On Fri, Jan 24, 2014 at 12:09 PM, John Scoles byter...@hotmail.com mailto:byter...@hotmail.com mailto:byter...@hotmail.com mailto:byter...@hotmail.com wrote: As Martin said that is rather old version of DBD only 3 since native exe_array was introduced 1.18, and I rember there being some leaks in early version of the native exe_array. If you can upgrade you DBD. Yyou might try to set the 'ora_maxarray_numentries' on you binds as well as that works on the Oracle side of things to limit memory. As it runs stands alone as you say it might be that the things you are binnding are not being released by perl as a referace to them may still exist. Cheers John Date: Fri, 24 Jan 2014 10:53:54 + From: boh...@ntlworld.com mailto:boh...@ntlworld.com mailto:boh...@ntlworld.com mailto:boh...@ntlworld.com To: hhferre...@gmail.com mailto:hhferre...@gmail.com mailto:hhferre...@gmail.com mailto:hhferre...@gmail.com; dbi-users@perl.org mailto:dbi-users@perl.org mailto:dbi-users@perl.org mailto:dbi-users
Re: Table-Valued Parameters to MS SQL Server stored procedure
On 29/12/2013 22:20, Jürgen Wenzel wrote: Hello, I'm adapting a Windows perl program to work with a new MS SQL Server database and it involves making calls to stored procedures that takes Table-Valued Parameters. Is this possible with DBD::ODBC or any other DBI? Or at all with (strawberry) perl? Would really appreciate some help -- answers and perhaps even a short example -- since trying to figure this out is really starting to wear on me. Thanks in advance! JW . I don't believe you can do that with DBD::ODBC. In fact, I don't think you can even do that with ODBC (if you know different and can point me at it I'll take a look). The traditional way around this is to use temporary tables. Martin -- Martin J. Evans Wetherby, UK
Re: DBI: selectrow_array() return first column when called in list context
On 20/12/2013 14:13, mimic...@gmail.com wrote: Hi I need to understand why selectrow_array() return only one column (first column) when called in list context as in the code below. On the otherhand selectrow_arrayref behaves correctly. code = You've missed out how you actually called selectrow_array and that is the important bit. I suspect you are not calling it in list context. if ($x){ print When using selectrow_array(), DBI returned:; print \$x: $x \$y: $y; print \n\n; }else{ print DB returned no data\n\n; } selectrow_arrayref behaves correctly. my ($arrref) = $dbh-selectrow_arrayref(qq{ You don't need brackets around $arrref here BTW. selectrow_arrayref only returns one value. SELECT user,perm FROM access WHERE token=\'Tt9VpStL4xADSDJQtd4AkMc6cVi66Mwmr9pMcCRgO4NVJ\'})|| die Could not query database: $DBI::errstr\n; my @retarr = @$arrref; if (@retarr){ print When using selectrow_arrayref, DBI returned:; print \@retarr contains: @retarr ; print \n\n; }else{ print DB returned no data\n\n; } Output = perl misc_test.pl http://misc_test.pl Use of uninitialized value $y in concatenation (.) or string at misc_test.pl http://misc_test.pl line 69. When using selectrow_array(), DBI returned:$x: markb $y: When using selectrow_arrayref, DBI returned:@retarr contains: markb ro Installed versions perl -MDBD::mysql -le 'print $DBD::mysql::VERSION;' 4.013 perl -MDBI -le 'print $DBI::VERSION;' 1.609 Martin -- Martin J. Evans Wetherby, UK
DBD::ODBC 1.46_2 released - your really need to test this
I have uploaded DBD::ODBC 1.46_2 to the CPAN today. As I previously warned the 1.46_xx series of development releases contain a number of Unicode fixes. You really should test this as without your feedback it will be released eventually and these changes are substantial. The changes since the last official release are: DBD::ODBC::Changes - Log of significant changes to the DBD::ODBC =head2 1.46_2 2013-12-17 [BUG FIXES] When built with unicode support and odbc_old_unicode is not enabled columns reported as SQL_LONGVARCHAR were not by default bound as SQL_WCHAR and hence were not returned correctly unless the bind was overridden. [MISCELLANEOUS] Added test 90_trace_flag.t =head2 1.46_1 2013-11-16 [CHANGE IN BEHAVIOUR] As warned in release 1.45, the binding of unicode parameters to char/varchar columns has changed significantly. If you don't attempt to insert unicode into char/varchar columns or if you only inserted unicode into nchar/nvarchar columns you should see no difference. From this release, unicode data inserted into char/varchar/longvarchar columns is bound as SQL_WCHAR and not whatever the driver reports the parameter as (which is mostly SQL_CHAR). Previously if DBD::ODBC received an error or (SQL_SUCCESS_WITH_INFO) from an ODBC API call and then the driver refused to return the error state/text DBD::ODBC would issue its own error saying Unable to fetch information about the error and state IM008. That state was wrong and has been changed to HY000. [BUG FIXES] Some drivers cannot support catalogs and/or schema names in SQLTables. Recent changes set the schema/catalog name to the empty string (good reasons below) which causes optional feature not implemented from MS Access (which does not support schemas - even for a simply ping (which uses SQLTables)). Now we call SQLCATALOG_NAME and SQLSCHEMA_USAGE on connect to ascertain support which modifies SQLTables call. [MISCELLANEOUS] Added test 45_unicode_varchar.t for MS SQL Server only so far. Martin -- Martin J. Evans Wetherby, UK
Re: DBD::ODBC and Unicode
On 20/11/13 09:39, Michiel Beijen wrote: Hi, I ran into this issue with DBD::ODBC; I read Martin asked about feedback on the 1.46_1 devel release. I tested my code against both versions 1.43 and 1.46_1 an the results are the same. Thanks for this. It has raised questions that needed answering. Basically, if I do SELECT example FROM foo WHERE example LIKE 'string%'; this is OK as long as long as 'string' is ascii. But whenever 'string' is unicode, it does not return rows, when it should. if I use bind variables, this problem goes away. DBD::mysql, DBD::Oracle and DBD::Pg do not have this problem. An alternative which I found worked is using N: SELECT example FROM foo WHERE example LIKE N'string%'; but as I said, using the statement WITHOUT the N works on the other three DBD drivers I mentioned. Is this a bug in DBD::ODBC or expected behaviour? Test code is here: https://gist.github.com/mbeijen/7560268 Result is here: https://gist.github.com/mbeijen/5a497e05e0739ef93ce9 -- Mike All DBD::ODBC does when it gets a piece of unicode SQL is encode it in UTF16 and pass it to SQLPrepareW. I've confirmed that the data sent over the wire in the TDS protocol to MS SQL Server is encoded correctly and is the correct data. I've also confirmed the results of your test here with 4 different SQL Server drivers (2 windows, 2 unix). You can even do a select of your data from MS SQL Management studio and then paste it into a select with a where and it does not return any rows unless the N prefix is added. Also, there are some gotchas using like - see http://technet.microsoft.com/en-us/library/ms179859.aspx If you read http://support.microsoft.com/kb/239530 it explains why you need to prefix all unicode strings with N. In fact, I'm not sure you do so long as the characters in your literal are in your client codepage but that is difficult to prove conclusively. A small change to your script shows you MS SQL Server will report the literal suffix required for nchar columns and your script is ignoring that: my @types = $dbh-type_info(-9); print Dumper(@types); $VAR1 = { 'UNSIGNED_ATTRIBUTE' = undef, 'MAXIMUM_SCALE' = undef, 'INTERVAL_PRECISION' = undef, 'CREATE_PARAMS' = 'max length', 'NUM_PREC_RADIX' = undef, 'SEARCHABLE' = '3', 'MONEY' = '0', 'AUTO_INCREMENT' = undef, 'USERTYPE' = '0', 'LOCAL_TYPE_NAME' = 'nvarchar', 'LITERAL_PREFIX' = 'N\'', 'COLUMN_SIZE' = 4000, 'MINIMUM_SCALE' = undef, 'TYPE_NAME' = 'nvarchar', 'NULLABLE' = '1', 'SQL_DATA_TYPE' = '-9', 'DATA_TYPE' = -9, 'LITERAL_SUFFIX' = '\'', 'CASE_SENSITIVE' = '0', 'SQL_DATETIME_SUB' = undef }; So this is probably not what you wanted to hear but if you stick to bound parameters (which is good practise anyway) there should not be a problem. I'll try and add some of this to my unicode problems document. Martin
Re: :ODBC 1.46_1 released - You REALLY need to test this release
On 17/11/2013 08:32, Meir Guttman wrote: Dear Martin -Original Message- From: Martin J. Evans [mailto:boh...@ntlworld.com] Sent: שבת 16 נובמבר 2013 12:34 To: dbi-users@perl.org; DBI Developers Mailing List; dbi-annou...@perl.org Subject: DBD::ODBC 1.46_1 released - You REALLY need to test this release I've just uploaded DBD::ODBC 1.46_1 to the CPAN. In the process of writing http://www.easysoft.com/developer/languages/perl/sql-server-unicode.html and https://github.com/mjegh/dbd_odbc_sql_server_unicode I discovered a serious bug in the way DBD::ODBC can attempt to insert unicode characters into char/varchar/longvarchar columns. This experimental release fixes that issue but it does mean this release contains a significant change in behaviour. The issue ONLY applied to unicode builds of DBD::ODBC (the default on Windows) and enabled in Linux/Unix via the -u switch to Makefile.PL. The DBD::mysql driver supported Unicode/UTF-8 for some times now by turning ON the driver's 'mysql_enable_utf8' attribute. MySQL also supports UTF-8 text encoding by default. Do the last changes affect any of this behavior? (I am sorry that I cannot test your release. I am using ActivePerl, and ActiveState, as a rule, does not compile and release experimental releases of packages. I am afraid I'll have to wait until you release a stable one.) Meir Meir, I think you've got slightly mixed up. This is DBD::ODBC, not DBD::mysql. Martin
DBD::ODBC 1.46_1 released - You REALLY need to test this release
I've just uploaded DBD::ODBC 1.46_1 to the CPAN. In the process of writing http://www.easysoft.com/developer/languages/perl/sql-server-unicode.html and https://github.com/mjegh/dbd_odbc_sql_server_unicode I discovered a serious bug in the way DBD::ODBC can attempt to insert unicode characters into char/varchar/longvarchar columns. This experimental release fixes that issue but it does mean this release contains a significant change in behaviour. The issue ONLY applied to unicode builds of DBD::ODBC (the default on Windows) and enabled in Linux/Unix via the -u switch to Makefile.PL. The problem was that when inserting parameter data into char/varchar/longvarchar columns DBD::ODBC ignored what your parameter actually looked like and simply bound the parameter as the type described by the database (SQL_CHAR). This meant that if you bound data was unicode, the separate octets of the perl UTF-8 encoded data would be inserted instead of the unicode characters. A simple example illustrates this easiest: Say you had a unicode euro in a perl scalar. This is U+20AC and is encoded in UTF-8 as 0xe2,0x82,0xc2. If you inserted into a char/varchar/longvarchar the database would receive it as 3 separate chrs instead of 1 i.e., select len(mycol) from mytable would return 3 instead of 1. There are a few situations when this did not apply 1) if you overrode the bind type with SQL_WVARCHAR 2) if your ODBC driver did not support SQLDescribeParam or you told DBD::ODBC not to use it. A new test (45_unicode_varchar.t which has high verbosity set right now) has been added to the test suite. Unfortunately, this test only runs to MS SQL Server right now. If this test does not pass for you please report it and the output to me as soon as possible. You are strongly advised to test this release with your development environment as I've not implemented a deprecation policy for this change as yet. I'm hoping to release a full version as 1.46_1 is, BUT if it is reported to me that this will cause too many people problems I'll reconsider. I full description of the issues and other problems can be found at http://www.easysoft.com/developer/languages/perl/sql-server-unicode.html and https://github.com/mjegh/dbd_odbc_sql_server_unicode. Here is a full list of the changes since 1.45: [CHANGE IN BEHAVIOUR] As warned in release 1.45, the binding of unicode parameters to char/varchar columns has changed significantly - see warning at start of DBD::ODBC pod. If you don't attempt to insert unicode into char/varchar columns or if you only inserted unicode into nchar/nvarchar columns you should see no difference. Previously if DBD::ODBC received an error or (SQL_SUCCESS_WITH_INFO) from an ODBC API call and then the driver refused to return the error state/text DBD::ODBC would issue its own error saying Unable to fetch information about the error and state IM008. That state was wrong and has been changed to HY000. [BUG FIXES] Some drivers cannot support catalogs and/or schema names in SQLTables. Recent changes set the schema/catalog name to the empty string (good reasons below) which causes optional feature not implemented from MS Access (which does not support schemas - even for a simply ping (which uses SQLTables)). Now we call SQLCATALOG_NAME and SQLSCHEMA_USAGE on connect to ascertain support which modifies SQLTables call. [MISCELLANEOUS] Added test 45_unicode_varchar.t for MS SQL Server only so far. Martin -- Martin J. Evans Wetherby, UK
Re: DBD::ODBC fails after Execute immediate success with info
anything. Because DBD::ODBC got the informational message it happily posted it and by default informationals in DBI just not print or raise an error. (SQL-01000)' (err#0) - do= -1 at ./odbctest.pl line 16 - DESTROY for DBD::ODBC::db (DBI::db=HASH(0x24ef420)~INNER) thr#24bd010 info: '' 'NOTICE 4185: Nothing was dropped (SQL-01000)' (err#0) - DESTROY= undef --cut-- Both worked with DBD::ODBC 1.23. Through binary searching DBD::ODBC releases, I was able to figure out that release 1.31 introduced this behavior, namely this change https://metacpan.org/pod/release/MJEVANS/DBD-ODBC-1.45/Changes#pod1.30_6-2011-06-04 So my question is: is it me, ODBC manager, DBD::ODBC or Vertica doing something wrong? Any insight is more than welcome! Thanks Jan To rule out DBD::ODBC being at fault in its ODBC calls I'd need to see a ODBC driver manager trace. You can do this by: 1. run odbcinst -j to find out where your odbcinst file is located e.g., $ odbcinst -j unixODBC 2.2.14 DRIVERS: /etc/odbcinst.ini - this file SYSTEM DATA SOURCES: /etc/odbc.ini FILE DATA SOURCES..: /etc/ODBCDataSources USER DATA SOURCES..: /home/martin/.odbc.ini SQLULEN Size...: 4 SQLLEN Size: 4 SQLSETPOSIROW Size.: 2 2. add to the top of the odbcinst.ini file: [ODBC] Trace=yes TraceFile=/tmp/unixodbc.log 3. now run your basic script and send me /tmp/unixodbc.log As to whether DBD::ODBC should issue an error if the driver says there is a problem and then won't tell us what it is - that is another issue I prepared to debate. Martin-- Martin J. Evans Easysoft Limited http://www.easysoft.com
Re: DBD::ODBC fails after Execute immediate success with info
On 06/11/13 12:36, Jan Holčapek wrote: Hi Martin, 3. now run your basic script and send me /tmp/unixodbc.log attached is the log file you've requested. Please let me know your findings, thanks! --Jan Hi Jan, Your log shows: [ODBC][7270][1383740710.126962][SQLExecDirect.c][240] Entry: Statement = 0x2266860 SQL = [drop table if exists foo][length = 24 (SQL_NTS)] [ODBC][7270][1383740710.130936][SQLExecDirect.c][503] Exit:[SQL_SUCCESS_WITH_INFO] Here SQL_SUCCESS_WITH_INFO was returned. ODBC specifies that this state should only be returned if the driver succeeded in performing your action but perhaps with some notable event like, I did what you asked but I had to change it slightly, or in this case, I could not drop the table foo because it does not exist but the net effect is the same - foo does not exist. [ODBC][7270][1383740710.131029][SQLError.c][352] Entry: Statement = 0x2266860 SQLState = 0x7fff72663d80 Native = 0x7fff72663978 Message Text = 0x7fff72663980 Buffer Length = 1023 Text Len Ptr = 0x7fff7266397e [ODBC][7270][1383740710.131053][SQLError.c][389] Exit:[SQL_NO_DATA] We tried to find out the additional information on the statement handle but it returned nothing. [ODBC][7270][1383740710.131079][SQLError.c][434] Entry: Connection = 0x2097c60 SQLState = 0x7fff72663d80 Native = 0x7fff72663978 Message Text = 0x7fff72663980 Buffer Length = 1023 Text Len Ptr = 0x7fff7266397e [ODBC][7270][1383740710.131094][SQLError.c][471] Exit:[SQL_NO_DATA] We tried again on the connection handle - same result. [ODBC][7270][1383740710.131122][SQLError.c][514] Entry: Environment = 0x2097670 SQLState = 0x7fff72663d80 Native = 0x7fff72663978 Message Text = 0x7fff72663980 Buffer Length = 1023 Text Len Ptr = 0x7fff7266397e [ODBC][7270][1383740710.131140][SQLError.c][551] Exit:[SQL_NO_DATA] we tried again on the environment handle - same result. As you can see DBD::ODBC tried very hard to find out what the informational msg was. At this point DBD::ODBC considered this an error and issued the error that SQLExecDirect returned SQL_SUCCESS_WITH_INFO but no informational message could be found. I believe this is a bug in your ODBC driver. However, I took the decision in DBD::ODBC to report this an error and some might argue differently. In this case it is fairly innocuous as you tried to drop a table which did not exist and afterwards it does not exist - although what happens if it was an error in your application and the table should have existed? In other cases it is more clear cut - what if you tried to set a statement attribute like a cursor to type A and the driver said, I've set a cursor, but not to type A, I've set it to type B - an app would likely want to know this. I can see it might be inconvenient for you the way it is now as your forced to do something like: $h-{RaiseError} = 1; eval { $h-do(q/drop table foo/); }; if ($@ $h-errstr =~ /Unable to fetch information about the error/) { # assume the drop was ok and the table did not exist # but that is not really a safe assumption to make } elsif ($@) { # it is a real error } What should have happened is that the driver returned SQL_SUCCESS_WITH_INFO and an informational msg was retrieved. execute/do would return success. DBD::ODBC would have posted an informational msg and if you'd examined $h-err it would contain '' (the empty string) to indicate an informational msg was available in $h-errstr. I could be swayed to change this to a warning (which would be less inconvenient to you since you could disable warnings when you are dropping a table) but I'd need to be persuaded. This is one of those occasions when I'm damned either way. If I don't report it as an error I end up debugging peoples ODBC logs only to tell them their driver is broken and if I do report an error people come back to me saying why is this an error. However, to reiterate, I think your driver is broken in the following ways: 1. although it is perfectly reasonable for a driver to behave differently in a transaction to out of one, yours is inconsistent. In a txn, it reports SQL_SUCCESS_WITH_INFO and no informational msg, and out it reports SQL_SUCCESS_WITH_INFO and gives an informational message that your table does not exist. 2. I'd expect: drop table foo if not exists; to be successful, even if foo does not exist - it isn't for
Re: DBD::ODBC fails after Execute immediate success with info
On 06/11/13 14:36, Jan Holčapek wrote: Hi Martin, On Wed, Nov 6, 2013 at 3:05 PM, Martin J. Evans boh...@ntlworld.com wrote: I believe this is a bug in your ODBC driver. I kind of expected that. I'll file a bugreport to Vertica Support. good, that was part of what I was hoping to achieve when I did this. However, I took the decision in DBD::ODBC to report this an error and some might argue differently. In this case it is fairly innocuous as you tried to drop a table which did not exist and afterwards it does not exist - although what happens if it was an error in your application and the table should have existed? In other cases it is more clear cut - what if you tried to set a statement attribute like a cursor to type A and the driver said, I've set a cursor, but not to type A, I've set it to type B - an app would likely want to know this. IMO current behavior is correct. I can see it might be inconvenient for you the way it is now as your forced to do something like: $h-{RaiseError} = 1; eval { $h-do(q/drop table foo/); }; if ($@ $h-errstr =~ /Unable to fetch information about the error/) { # assume the drop was ok and the table did not exist # but that is not really a safe assumption to make } elsif ($@) { # it is a real error } I've already adjusted the code with this, I also narrowed the if-condition with $dbh-state eq 'IM008', as I'm getting this state along with the error. Not that it's neat, but allows me to work around this driver issue for the moment. BTW I found out IM008 is returned by DBD::ODBC, then checked http://www.easysoft.com/developer/interfaces/odbc/sqlstate_status_return_codes.html#IM008 and must admit I can't see a relationship between the actual error and the state. hah, my own document comes back to haunt me. You are right, it is down to: if (!error_found (err_rc != SQL_NO_DATA_FOUND)) { if (DBIc_TRACE(imp_xxh, DBD_TRACING, 0, 3)) TRACE1(imp_dbh, ** No error found %d **\n, err_rc); DBIh_SET_ERR_CHAR( h, imp_xxh, Nullch, 1, Unable to fetch information about the error, IM008, Nullch); } and I'm guessing a cut and paste. DBD::ODBC has to invent a state here and I guess it should be HY000. If I were you I'd not rely on IM008 as now you've pointed it out I may change it to HY000 so you might be better looking for IM008|HY000 or No error found in errstr. I've just added a comment to the source, not to change that string. I could be swayed to change this to a warning (which would be less inconvenient to you since you could disable warnings when you are dropping a table) but I'd need to be persuaded. This is one of those occasions when I'm damned either way. If I don't report it as an error I end up debugging peoples ODBC logs only to tell them their driver is broken and if I do report an error people come back to me saying why is this an error. Agree. BTW, are you sure your db can actually rollback a drop table, and if it cannot, then why bother doing it in a txn? This is due to the code being used to handle data both in PostgreSQL database (which supports transactional DDL) and Vertica (which does not support that). We are likely about to consider changing the application logic. Thank very much for the explanation! Cheers Jan np. Martin
Re: (Fwd) Problems with DBD::Oracle package: function plsql_errstr
Thank you for providing this - please see below. On 03/11/2013 12:06, Tim Bunce wrote: - Forwarded message from Eljot na Onet.pl eljot_...@poczta.onet.pl - Date: Sat, 02 Nov 2013 21:11:48 +0100 From: Eljot na Onet.pl eljot_...@poczta.onet.pl To: t...@cpan.org Subject: Problems with DBD::Oracle package: function plsql_errstr Hi, I have found two problems with your DBD::Oracle package. (you have all examples in the attachment plsql_errstr.zip, please read README.txt file) which was missing unfortunately - perhaps when it was forwarded on. problems concern sub plsql_errstr described in http://search.cpan.org/~pythian/DBD-Oracle-1.64/lib/DBD/Oracle.pm What's the point? You use view *user_errors* to fetch info about errors in *last* query parsed. But the view returns list of ALL current user errors - as name of the view means. So if I execute example code from pod : EXAMPLE 1: (01-example-1-sub.sh, calls pl/01-example-1-sub.pl) # Show the errors if CREATE PROCEDURE fails $dbh-{RaiseError} = 0; if ( $dbh-do( q{ CREATE OR REPLACE PROCEDURE perl_dbd_oracle_test as BEGIN PROCEDURE filltab( stuff OUT TAB ); asdf END; } ) ) {} # Statement succeeded } elsif ( 6550 != $dbh-err ) { die $dbh-errstr; } # Utter failure else { my $msg = $dbh-func( 'plsql_errstr' ); die $dbh-errstr if ! defined $msg; die $msg if $msg; } I will get msg as in attachment 01-example-01.log containing the error info I have expected. But the following code will not work properly: (please execute 00.example-del-subs.sh first) EXAMPLE 2: (02-example-2-subs.sh, calls pl/02-example-2-subs.pl) # Show the errors if CREATE PROCEDURE fails $dbh-{RaiseError} = 0; if ( $dbh-do( q{ CREATE OR REPLACE PROCEDURE perl_dbd_oracle_test_1st as BEGIN PROCEDURE filltab( stuff OUT TAB ); asdf END; } ) ) {} # Statement succeeded } elsif ( 6550 != $dbh-err ) { die $dbh-errstr; } # Utter failure else { my $msg = $dbh-func( 'plsql_errstr' ); warn $dbh-errstr, \n if ! defined $msg defined $dbh-errstr; warn $msg, \n if $msg; } # but this works not exactly as one should expect... $dbh-{RaiseError} = 0; if ( $dbh-do( q{ CREATE OR REPLACE PROCEDURE perl_dbd_oracle_test_2nd as BEGIN PROCEDURE filltab( stuff OUT TAB ); asdf END; } ) ) {} # Statement succeeded } elsif ( 6550 != $dbh-err ) { die $dbh-errstr; } # Utter failure else { my $msg = $dbh-func( 'plsql_errstr' ); warn $dbh-errstr, \n if ! defined $msg defined $dbh-errstr; warn $msg, \n if $msg; } ooops - first we've got info about procedure perl_dbd_oracle_test_1st (that's ok) but then we've got info about *both* procedures: perl_dbd_oracle_test_1st and perl_dbd_oracle_test_2nd as well... The second problem is more subtle. I think the common idea due to security reasons is to have only one database user which has resource and CREATE USER privilege, and the only user creates other users and tables, views, etc for the them. Other users can do only DML queries. In this case the view user_errors will obviously return the empty row list. So the code: EXAMPLE 3 (03-example-other-user.sh) # Show the errors if CREATE PROCEDURE fails $dbh-{RaiseError} = 0; if ( $dbh-do( q{ CREATE OR REPLACE PROCEDURE myuser.perl_dbd_oracle_test as BEGIN PROCEDURE filltab( stuff OUT TAB ); asdf END; } ) ) {} # Statement succeeded } elsif ( 6550 != $dbh-err ) { die $dbh-errstr; } # Utter failure else { my $msg = $dbh-func( 'plsql_errstr' ); die $dbh-errstr if ! defined $msg; die $msg if $msg; } will return no info at all! I have solved both problems, would You look at my version of plsql_errstr in file 04-example-new-plsql-errstr.pl, please? I don't know if you omitted an attachment of it didn't get passed on when it was forwarded to dbi-users list. Would you mind sending me your full email again with your new files/changes. Alternatively, if you use git you could always submit a pull request to the github repository (which you'll find at https://github.com/pythian/DBD-Oracle). As You can see I have defined *sub* *plsql_errarray* which select error info from database and returns them in array reference much more convinient for later use; *plsql_errstr* only converts that array into a single string. Of course one should provide at least two additional parameters to new plsql_errstr function. Martin -- Martin J. Evans Wetherby, UK
DBD::ODBC 1.45 released
I've just uploaded DBD::ODBC 1.45 to the CPAN. As always I'd draw your attention to a few small changes in behaviour. The changes since 1.43 are listed below but I need to warn you about an upcoming change first. WARNING - PLEASE READ: = The next development cycle of DBD::ODBC will contain signficant changes to the way unicode strings in your Perl scripts are inserted into CHAR and VARCHAR columns. In an attempt to write up exactly how this all works (see https://github.com/mjegh/dbd_odbc_sql_server_unicode and http://www.mail-archive.com/dbi-dev@perl.org/msg07364.html) I have discovered that unicode strings are not being inserted into CHAR/VARCHAR columns correctly in the unicode build of DBD::ODBC. There may also be changes to how unicode strings are read back from the database but I have not evaluated that yet. Please make sure you keep an eye out of DBD::ODBC development releases 1.46_N and ensure you test them before the next full release is made. In the mean time if you are using unicode with DBD::ODBC and have any comments, have hit any strange issues or are using any workarounds I strongly urge you to contact me now before I get too far into these changes. = Thanks to everyone who has contributed with code or advice. Here are the changes since 1.43: =head2 1.45 2013-10-28 [CHANGE IN BEHAVIOUR] There is no intentional change in behaviour in this release but I'm adding a warning that the next development release is highly liking to contain some significant unicode changes in behaviour to fix some bugs which have been around for quite a long time now. [BUG FIXES] If an SQLExecute ODBC API call returned SQL_NO_DATA DBD::ODBC was still calling SQLError (which was a waste of time). Since 1.44_1 odbc_out_connect_string stopped returning anything. [MISCELLANEOUS] Added another link to resources for supplementary characters. Added 1 more test to 20SqlServer.t for update statement. Small changes to 20SqlServer.t test to skip some tests and note the problem if SQLExecute returns SQL_NO_DATA on a non searched update. =head2 1.44_4 2013-10-16 [BUG FIXES] Fix method redefinition warnings in threads on Perl = 5.16 thanks Dagfinn Ilmari Mannsåker [MISCELLANEOUS] Changed this Changes file to be closer to the version 0.03 change file spec. Added t/version.t test. Added recommends Test::Version. Updates to the odbc_more_results pod to help clarify its use after some confusion was seen in a perlmonks thread. =head2 1.44_3 2013-10-11 [CHANGE IN BEHAVIOUR] If you attempt to set the ReadOnly attribute and the underlying ODBC driver does not support this (SQL_SUCCESS_WITH_INFO and option value changed is returned) a warning is issued. However, until RT 89015 You cannot issue a warning in the STORE method in DBI is resolved you won't get this warning. As DBI 1.628 it is not resolved. I've only seen the SQLite ODBC driver do this. If you set ReadOnly and the underlying ODBC driver does not support this then any subsequent attempts to fetch the ReadOnly attribute will return the value you set. [BUG FIXES] The 82_table_info test assumed all database and ODBC Drivers supported catalogs and schemas (some don't). Use get_info to find out if catalogs and schemas are supported before running these tests. The rt_79190.t could incorrectly fail if your test DSN contained the DRIVER attribute. [MISCELLANEOUS] Added RedHat spec file to examples courtesy of Michiel Beijen. Added use strict to FAQ/Changes etc to quieten kwalitee test. Added a workaround in the test suite for a probable bug in the postgres ODBC driver which does not return COLUMN_SIZE from SQLGetTypeInfo. It also issues a warning. See http://www.postgresql.org/message-id/524ef455.6050...@ntlworld.com =head2 1.44_3 2013-10-11 [MISCELLANEOUS] Skip 70execute_array_native.t test if MS Access - even if behind an ODBC Bridge. Fixed some compiler warnings when attempting to print/trace SvCUR. =head2 1.44_2 2013-09-07 [BUG FIXES] When table_info was called with a '%' for any one of the catalog, schema or type arguments with the rest all '' (the empty string), only a list of catalogs, schemas or types should be returned. It was not doing that as it was changing empty strings to undef/NULL. pod for odbc_lob_read had an example only saying lob_read. TYPE attribute for odbc_lob_read was actually coded as Type. It is now as documented. The example lob_read.pl had the TYPE set to 999 from when I was testing it but it got checked in like this. MANIFEST contained column_info.pl but the file was coltest.pl [MISCELLANEOUS] Fixed RT 86379 - spelling mistakes in ODBC.pm and FAQ - thanks to David Steinbrunner. Added 82_table_info.t test. Added 87_odbc_log_read.t test. =head2 1.44_1 2013-06-06 Moved from subversion to github as svn.perl.org is closing down. Changed docs to show new
Re: (Fwd) Can't use an undefined value as an ARRAY reference at /usr/local/lib/perl/5.14.2/DBI.pm
On 28/09/2013 12:17, Tim Bunce wrote: - Forwarded message from Yonatan Sisay ysi...@cloudxdpo.com - Date: Sat, 28 Sep 2013 01:25:04 -0700 From: Yonatan Sisay ysi...@cloudxdpo.com To: tim.bu...@pobox.com Subject: Greetings, I have been having an issue with DBI. I installed a web system called KOHA, and in the middle of it all, this library started having some issues. Here is how it shows the error. Can't use an undefined value as an ARRAY reference at /usr/local/lib/perl/5.14.2/DBI.pm I have tried re-installing the library and it doesn't seem to fix that. Could it be another library that is causing the issue? Thank you for your time, and I will be waiting for your reply. -- Yonatan Sisay Gebregiorgis CloudX Support and Development Team [1]supp...@cloudxdpo.com [2]www.cloudxdpo.com [3]Inline image 1 References Visible links 1. mailto:supp...@cloudxdpo.com 2. http://www.cloudxdpo.com/ - End forwarded message - I answered a similar question at http://www.mail-archive.com/dbi-users@perl.org/msg34804.html a few weeks ago but the op never came back to me/us. I see in the Change log for 1.620: =head2 Changes in DBI 1.620 (svn r15300) 25th April 2012 Modified column renaming in fetchall_arrayref, added in 1.619, to work on column index numbers not names (an incompatible change). Reworked the fetchall_arrayref documentation. Hash slices in fetchall_arrayref now detect invalid column names. Notice the incompatible change. I vaguely recollect this change. Does 1.618 work for you? Can you find the code which called fetchall_arrayref and show us it as I don't use Koha. Martin -- Martin J. Evans Wetherby, UK
Re: DBD::Oracle installation on AIX
On 22/09/2013 22:41, Nagendra Singh wrote: Hi Martin, Thanks a bunch for looking into this problem. Here are the details asked by you:- PERL Version is v5.10.1 DBI Version is 1.609 DBD::ODBC version is 1.43. Logs file are attached. Regards, Mr. Singh Thank you for the trace file. Your versions above should be ok. You DBI_TRACE shows: !SQLError(26b11a0,26b1320,0) = (82, 0, 523 80) ^^^ environment handle ^^^ connection handle ^ statement handle and the (82, 0, 523 80) are: sql state, native error, error message '82' is not a valid ODBC state - it should be 5 characters. 0 as a native error is unusual but not an error. '523 80' is peculiar as an error msg. None of this is enough for me to diagnose any problem other than your ODBC driver seems broken or is perhaps using an incompatible ODBC API (e.g., one where DBD::ODBC was built with SQLLEN as 64 bit and vertica was built with SQLLEN 32 bit). You will need to contact Vertica I guess. You might want to ask them: a) why the state is only 2 chrs b) what error msg '523 80' means c) whether their ODBC driver was built with 32 bit or 64 bit SQLLEN run odbcinst -j on your machine to see how unixODBC was built. d) include a unixODBC trace - which by the way, the one you sent me was not a unixodbc trace - it was the start of the DBI trace. Martin -- Martin J. Evans Wetherby, UK *From:* Martin J. Evans boh...@ntlworld.com *To:* Nagendra Singh nagsu...@yahoo.com *Cc:* Michael Nhan mn...@genome.wustl.edu; Manimegalai Visvanathan mvisvanat...@wsgc.com; 'dbi-users@perl.org' dbi-users@perl.org *Sent:* Sunday, September 22, 2013 11:51 AM *Subject:* Re: DBD::Oracle installation on AIX On 21/09/2013 19:42, Nagendra Singh wrote: Hi, I have installed DBD::ODBC latest version on 64 bit Linux Machine. One one machine I am able to make connection to Vertica using DSN but on other machine I am getting following error message. My odbc.ini, odbcinst.ini files are matching exactly:- set_err:State('82') is not a 5 character string, using 's1000' instead at /usr/local/lib64/perl5/DBD/ODBC.pm line 153. DBI connect('VertcaiABITPRIM','nbx',...)failed: 523 80 (SQL-82). Any pointer would be appreciated. Regards, Mr. Singh What version of Perl, DBI and DBD::ODBC are you using (latest does not help as new versions have recently been released))? You can generally get these using: perl --version perl -MDBI -le 'print $DBI::VERSION' perl -MDBD::ODBC -le 'print $DBD::ODBC::VERSION' Error state '82' is not a 5 character string and ODBC states should be. It might also be useful if you can provide any details of the Vertica ODBC Driver. S1000 is now HY000 which is a general error and should be accompanied with some error string. Error '82' (even 82000) does not exist. Error code 08002 does exist and that can be found here - http://www.easysoft.com/developer/interfaces/odbc/sqlstate_status_return_codes.html#08002 If you are using a recent DBI and DBD::ODBC you can do: DBI_TRACE=DBD=x.log perl myprogram.pl http://myprogram.pl/ and x.log should contain the log of odbc calls which would help me identify your problem. If this file ends up empty, try again with: DBI_TRACE=15=x.log perl myprogram.pl Martin -- Martin J. Evans Wetherby, UK
Re: DBD::Oracle installation on AIX
On 23/09/2013 18:43, Nagendra Singh wrote: Thanks Martin. How can I get UnixODBC trace? Could you please let me know the command. Strange thing is on one Linux machine its working fine and on another Linux machine( Same configuration) its not. Result of odbcinst-j is:- UnixODBC2.2.14 Drivers: /etc/odbcinst.ini SYSTEm Data Sources /etc/odbc.ini File DataSources : /etc/ODBCDataSources User Data Sources: /etc/odbc.ini SQLUEN Size:8 SQLLEN Sixe : 8 SQLSETPOSIROW Size:8 Regards, Mr. Singh Could you please keep dbi-users list on the cc list of your emails as other people might be able to help. Also, top posting (putting your reply above mine) makes it hard for people to follow. Your odbcinst output shows you are using unixODBC on a 64 bit platform built with the ODBC type SQLLEN set to 8 bytes. If your driver was built with SQLLEN set to 4 bytes it can lead to all sorts of difficult to track down problems. To get a unixODBC trace you look at the odbcinst -j output and find the file pointed to by: Drivers: /etc/odbcinst.ini At the top of this file you add the following: [ODBC] Trace=yes TraceFile=/tmp/unixodbc.log which tells unixODBC to trace all ODBC calls to the file /tmp/unixodbc.log. You then run the smallest script which produces the error and look in /tmp/unixodbc.log. It will show each ODBC API call, what arguments were passed and what was returned. However, I'm currently of the opinion this won't help us much as either: a) your ODBC driver is broken b) it was built with SQLLEN 4 bytes instead of 8 bytes Neither of (a) or (b) DBD::ODBC can do anything about and both would require you contacting vertica to get a fixed ODBC driver. Martin -- Martin J. Evans Wetherby, UK *From:* Martin J. Evans boh...@ntlworld.com *To:* Nagendra Singh nagsu...@yahoo.com *Cc:* dbi-users@perl.org dbi-users@perl.org *Sent:* Monday, September 23, 2013 12:32 PM *Subject:* Re: DBD::Oracle installation on AIX On 22/09/2013 22:41, Nagendra Singh wrote: Hi Martin, Thanks a bunch for looking into this problem. Here are the details asked by you:- PERL Version is v5.10.1 DBI Version is 1.609 DBD::ODBC version is 1.43. Logs file are attached. Regards, Mr. Singh Thank you for the trace file. Your versions above should be ok. You DBI_TRACE shows: !SQLError(26b11a0,26b1320,0) = (82, 0, 523 80) ^^^ environment handle ^^^ connection handle ^ statement handle and the (82, 0, 523 80) are: sql state, native error, error message '82' is not a valid ODBC state - it should be 5 characters. 0 as a native error is unusual but not an error. '523 80' is peculiar as an error msg. None of this is enough for me to diagnose any problem other than your ODBC driver seems broken or is perhaps using an incompatible ODBC API (e.g., one where DBD::ODBC was built with SQLLEN as 64 bit and vertica was built with SQLLEN 32 bit). You will need to contact Vertica I guess. You might want to ask them: a) why the state is only 2 chrs b) what error msg '523 80' means c) whether their ODBC driver was built with 32 bit or 64 bit SQLLEN run odbcinst -j on your machine to see how unixODBC was built. d) include a unixODBC trace - which by the way, the one you sent me was not a unixodbc trace - it was the start of the DBI trace. Martin -- Martin J. Evans Wetherby, UK *From:* Martin J. Evans boh...@ntlworld.com mailto:boh...@ntlworld.com *To:* Nagendra Singh nagsu...@yahoo.com mailto:nagsu...@yahoo.com *Cc:* Michael Nhan mn...@genome.wustl.edu mailto:mn...@genome.wustl.edu; Manimegalai Visvanathan mvisvanat...@wsgc.com mailto:mvisvanat...@wsgc.com; 'dbi-users@perl.org mailto:dbi-users@perl.org' dbi-users@perl.org mailto:dbi-users@perl.org *Sent:* Sunday, September 22, 2013 11:51 AM *Subject:* Re: DBD::Oracle installation on AIX On 21/09/2013 19:42, Nagendra Singh wrote: Hi, I have installed DBD::ODBC latest version on 64 bit Linux Machine. One one machine I am able to make connection to Vertica using DSN but on other machine I am getting following error message. My odbc.ini, odbcinst.ini files are matching exactly:- set_err:State('82') is not a 5 character string, using 's1000' instead at /usr/local/lib64/perl5/DBD/ODBC.pm line 153. DBI connect('VertcaiABITPRIM','nbx',...)failed: 523 80 (SQL-82). Any pointer would be appreciated. Regards, Mr. Singh What version of Perl, DBI and DBD::ODBC are you using (latest does not help as new versions have recently been released))? You can generally get these using: perl --version perl -MDBI -le 'print $DBI::VERSION' perl -MDBD::ODBC -le 'print $DBD::ODBC::VERSION' Error state '82' is not a 5 character string and ODBC states should be. It might also be useful if you can provide any details of the Vertica ODBC Driver. S1000 is now HY000 which is a general error and should be accompanied with some error string. Error
Re: DBD::Oracle installation on AIX
On 21/09/2013 19:42, Nagendra Singh wrote: Hi, I have installed DBD::ODBC latest version on 64 bit Linux Machine. One one machine I am able to make connection to Vertica using DSN but on other machine I am getting following error message. My odbc.ini, odbcinst.ini files are matching exactly:- set_err:State('82') is not a 5 character string, using 's1000' instead at /usr/local/lib64/perl5/DBD/ODBC.pm line 153. DBI connect('VertcaiABITPRIM','nbx',...)failed: 523 80 (SQL-82). Any pointer would be appreciated. Regards, Mr. Singh What version of Perl, DBI and DBD::ODBC are you using (latest does not help as new versions have recently been released))? You can generally get these using: perl --version perl -MDBI -le 'print $DBI::VERSION' perl -MDBD::ODBC -le 'print $DBD::ODBC::VERSION' Error state '82' is not a 5 character string and ODBC states should be. It might also be useful if you can provide any details of the Vertica ODBC Driver. S1000 is now HY000 which is a general error and should be accompanied with some error string. Error '82' (even 82000) does not exist. Error code 08002 does exist and that can be found here - http://www.easysoft.com/developer/interfaces/odbc/sqlstate_status_return_codes.html#08002 If you are using a recent DBI and DBD::ODBC you can do: DBI_TRACE=DBD=x.log perl myprogram.pl and x.log should contain the log of odbc calls which would help me identify your problem. If this file ends up empty, try again with: DBI_TRACE=15=x.log perl myprogram.pl Martin -- Martin J. Evans Wetherby, UK
Re: Trouble with DBI - bug or rewrite?
On 18/09/13 09:58, Michael Seaton wrote: Hi there, I and a colleague have been trying to install Koha (an integrated library system written in Perl) onto a server. While we've been mostly successful in installing Koha, we keep getting the following message whenever we try to log in: Can't use an undefined value as an ARRAY reference at /usr/local/lib64/perl5/DBI.pm line 2059. While it might be possible to blame Koha for this error message, I understand that it is only comparatively recently that this message has started to appear ... and not just for Koha. The same error message (referring to the same line in DBI.pm) has also recently been reported for another program (Netdisco), as the following links to discussion boards/blogs show: http://sourceforge.net/mailarchive/message.php?msg_id=30768434 http://netdisco.blogspot.co.uk/2013/04/manual-netdisco-installation.html My question is: has there been a recent rewrite of DBI.pm that the developers of Koha (and Netdisco) have yet to account for, or is the above error message the result of a bug in DBI.pm? I'd very much appreciate any advice or information anyone can offer on this. (Many apologies if someone else has already posted on this topic!) Regards, Michael Seaton Hi, It might help to know what version of DBI you are using. I see from the first link you gave, it looks like someone getting this issue is using DBI 1.625 and line 2059 is in the middle of the fetchall_arrayref method. I see in the Change log for 1.620: =head2 Changes in DBI 1.620 (svn r15300) 25th April 2012 Modified column renaming in fetchall_arrayref, added in 1.619, to work on column index numbers not names (an incompatible change). Reworked the fetchall_arrayref documentation. Hash slices in fetchall_arrayref now detect invalid column names. I vaguely recollect this change. Does 1.619 work for you? Can you find the code which called fetchall_arrayref and show us it as I don't use Koha. Martin
Re: Trouble with DBI - bug or rewrite?
On 18/09/13 10:26, Martin J. Evans wrote: On 18/09/13 09:58, Michael Seaton wrote: Hi there, I and a colleague have been trying to install Koha (an integrated library system written in Perl) onto a server. While we've been mostly successful in installing Koha, we keep getting the following message whenever we try to log in: Can't use an undefined value as an ARRAY reference at /usr/local/lib64/perl5/DBI.pm line 2059. While it might be possible to blame Koha for this error message, I understand that it is only comparatively recently that this message has started to appear ... and not just for Koha. The same error message (referring to the same line in DBI.pm) has also recently been reported for another program (Netdisco), as the following links to discussion boards/blogs show: http://sourceforge.net/mailarchive/message.php?msg_id=30768434 http://netdisco.blogspot.co.uk/2013/04/manual-netdisco-installation.html My question is: has there been a recent rewrite of DBI.pm that the developers of Koha (and Netdisco) have yet to account for, or is the above error message the result of a bug in DBI.pm? I'd very much appreciate any advice or information anyone can offer on this. (Many apologies if someone else has already posted on this topic!) Regards, Michael Seaton Hi, It might help to know what version of DBI you are using. I see from the first link you gave, it looks like someone getting this issue is using DBI 1.625 and line 2059 is in the middle of the fetchall_arrayref method. I see in the Change log for 1.620: =head2 Changes in DBI 1.620 (svn r15300) 25th April 2012 Modified column renaming in fetchall_arrayref, added in 1.619, to work on column index numbers not names (an incompatible change). Reworked the fetchall_arrayref documentation. Hash slices in fetchall_arrayref now detect invalid column names. I vaguely recollect this change. Does 1.619 work for you? Can you find the code which called fetchall_arrayref and show us it as I don't use Koha. Martin Sorry, I just noticed the change in question was originally started in 1.619 so that should have been does 1.618 work for you? Martin
Re: DBD::Oracle installation on AIX
On 12/09/2013 23:13, Manimegalai Visvanathan wrote: Hi Team, I need one help from you… While installing in DBD::Oracle perl module am facing some challenges. Can you please help me to fix that issue. Server OS: AIX Am not able to run the make command: Some of the files you need to build DBD::Oracle are not installed by default in Oracle 11. It is usually much easier to install Oracle's instant client and it also has the advantage that you are immune to upgrades in your main Oracle too. Download instant client basic, sdk and sqlplus. Unzip them somewhere and point your dynamic linker search path to that dir (cannot remember what it is for AIX right now, it's LD_LIBRARY_PATH on linux and some other unixes and it may be SHLIB_PATH on AIX). Then run perl Makefile.PL. See man page for the ld command to find the right env var. You may also want to check http://cpansearch.perl.org/src/PYTHIAN/DBD-Oracle-1.66/README.help.txt Martin -- Martin J. Evans Wetherby, UK
New DBD::ODBC 1.44_2 development release
DBD::ODBC moved to github on my account a while ago and now it has moved again to perl5-dbi group (thanks Merijn [Tux]). After struggling to get github working on my Windows machine I'm now back in action and I've done a new development version of DBD::ODBC. There is one potentially serious change you should note which is the table_info fix. I have tried not to break backwards compatibility but I cannot guarantee it. Also odbc_lob_read was just plain broken when retrieving data as SQL_BINARY (although ok otherwise). Below are all the changes since the last full release. Please let me know if you find any problems and as usual, thanks to all contributors and especially my friends on #dbi who are always so helpful. =head2 Changes in DBD::ODBC 1.44_2 September 7 2013 [BUG FIXES] When table_info was called with a '%' for any one of the catalog, schema or type arguments with the rest all '' (the empty string), only a list of catalogs, schemas or types should be returned. It was not doing that as it was changing empty strings to undef/NULL. pod for odbc_lob_read had an example only saying lob_read. TYPE attribute for odbc_lob_read was actually coded as Type. It is now as documented. The example lob_read.pl had the TYPE set to 999 from when I was testing it but it got checked in like this. MANIFEST contained column_info.pl but the file was coltest.pl [MISCELLANEOUS] Fixed RT 86379 - spelling mistakes in ODBC.pm and FAQ - thanks to David Steinbrunner. Added 82_table_info.t test. Added 87_odbc_log_read.t test. =head2 Changes in DBD::ODBC 1.44_1 June 6 2013 Moved from subversion to github as svn.perl.org is closing down. Changed docs to show new repository. [BUG FIXES] Fixed RT 84450 - Database Handle Attribute Fetch broken. Thanks to Stephen Oberholtzer for finding and supplying patch. Fixed problem with attributes on bind_col not being sticky. You'll probably only see this if you are using fetchall_arrayref with a slice and setting TYPE or attributes in bind_col first. Martin -- Martin J. Evans Wetherby, UK
Re: odbc_lob_read chunk is short
On 05/09/13 20:28, Mike Grau wrote: Hello, I am completely new at this, but I am trying to read a BLOB in chunks via ODBC in Oracle 11g using DBD::ODBC-1.43, unixODBC-2.3.1 and oracle-instantclient11.2-odbc-11.2. I can read an entire BLOB in one read and write it to the filesystem, but I really need to be able to do it in chunks, I think. Reading the BLOB in chunks, but I am always getting one byte less than requested, losing the last byte, which results in a corrupt document (a PDF) when written to the filesystem. I'm at a loss as to why this is happening and how to correct it. Using this snippet based on the example provided by DBD::ODBC... while($len = $s-odbc_lob_read(1, \my $x, 8, {TYPE = 999})) { print len=$len, x=$x\n; } ... produces this output: len=7, x=435886 len=7, x= n len=7, x=0001282 len=7, x=60 And a tracefile shows the same. SQLGetData(col=1,type=-2)=1 (retlen=2046252) - odbc_lob_read= ( 7 ) [1 items] at /root/lob_read.pl line 45 odbc_lob_read DISPATCH (DBI::st=HASH(0x203c940) rc1/1 @5 g2 ima0 pid#11494) at /root/lob_read.pl line 45 - odbc_lob_read for DBD::ODBC::st (DBI::st=HASH(0x203c940)~0x203c9d0 1 SCALAR(0x1e0a440) 8 HASH(0x203c970)) thr#1cea010 SQLGetData(col=1,type=-2)=1 (retlen=2046244) - odbc_lob_read= ( 7 ) [1 items] at /root/lob_read.pl line 45 odbc_lob_read DISPATCH (DBI::st=HASH(0x203c940) rc1/1 @5 g2 ima0 pid#11494) at /root/lob_read.pl line 45 - odbc_lob_read for DBD::ODBC::st (DBI::st=HASH(0x203c940)~0x203c9d0 1 SCALAR(0x1e0a440) 8 HASH(0x203c9a0)) thr#1cea010 Can someone please give me some guidance how to get the full chunk requested? I'm using unixODBC-2.3.1 - built from the tarball DBD-ODBC-1.43 - installed from the tarball (perl Makefile.PL; make; make install) oracle-instantclient11.2-odbc-11.2.0.3 installed from RPM DBI is the distro's perl-DBI-1.617-4.1.1.x86_64 (openSUSE 12.3) The database and client are both x86_64 The instant client is link against libodbcinst.so.1 and unixODBC now provides libodbcinst.so.2, so I symlinked them: libodbcinst.so.1 - /usr/local/lib64/libodbcinst.so.2 Ugly, but seems to work. Ultimately I want to index documents stored in Oracle with Sphinx and so must use ODBC. I'd like to know that this much is working before moving on to Sphinx. TIA -- Mike G. Bear with me and I'll take another look at that code. If I cannot see anything I might need to send you a new version with slightly different logging. By all means nag me if I don't get back to you by tomorrow. Martin -- Martin J. Evans Easysoft Limited http://www.easysoft.com
Re: odbc_lob_read chunk is short
On 06/09/13 09:18, Martin J. Evans wrote: On 05/09/13 20:28, Mike Grau wrote: Hello, I am completely new at this, but I am trying to read a BLOB in chunks via ODBC in Oracle 11g using DBD::ODBC-1.43, unixODBC-2.3.1 and oracle-instantclient11.2-odbc-11.2. I can read an entire BLOB in one read and write it to the filesystem, but I really need to be able to do it in chunks, I think. Reading the BLOB in chunks, but I am always getting one byte less than requested, losing the last byte, which results in a corrupt document (a PDF) when written to the filesystem. I'm at a loss as to why this is happening and how to correct it. Using this snippet based on the example provided by DBD::ODBC... while($len = $s-odbc_lob_read(1, \my $x, 8, {TYPE = 999})) { print len=$len, x=$x\n; } ... produces this output: len=7, x=435886 len=7, x= n len=7, x=0001282 len=7, x=60 And a tracefile shows the same. SQLGetData(col=1,type=-2)=1 (retlen=2046252) - odbc_lob_read= ( 7 ) [1 items] at /root/lob_read.pl line 45 odbc_lob_read DISPATCH (DBI::st=HASH(0x203c940) rc1/1 @5 g2 ima0 pid#11494) at /root/lob_read.pl line 45 - odbc_lob_read for DBD::ODBC::st (DBI::st=HASH(0x203c940)~0x203c9d0 1 SCALAR(0x1e0a440) 8 HASH(0x203c970)) thr#1cea010 SQLGetData(col=1,type=-2)=1 (retlen=2046244) - odbc_lob_read= ( 7 ) [1 items] at /root/lob_read.pl line 45 odbc_lob_read DISPATCH (DBI::st=HASH(0x203c940) rc1/1 @5 g2 ima0 pid#11494) at /root/lob_read.pl line 45 - odbc_lob_read for DBD::ODBC::st (DBI::st=HASH(0x203c940)~0x203c9d0 1 SCALAR(0x1e0a440) 8 HASH(0x203c9a0)) thr#1cea010 Can someone please give me some guidance how to get the full chunk requested? I'm using unixODBC-2.3.1 - built from the tarball DBD-ODBC-1.43 - installed from the tarball (perl Makefile.PL; make; make install) oracle-instantclient11.2-odbc-11.2.0.3 installed from RPM DBI is the distro's perl-DBI-1.617-4.1.1.x86_64 (openSUSE 12.3) The database and client are both x86_64 The instant client is link against libodbcinst.so.1 and unixODBC now provides libodbcinst.so.2, so I symlinked them: libodbcinst.so.1 - /usr/local/lib64/libodbcinst.so.2 Ugly, but seems to work. Ultimately I want to index documents stored in Oracle with Sphinx and so must use ODBC. I'd like to know that this much is working before moving on to Sphinx. TIA -- Mike G. Bear with me and I'll take another look at that code. If I cannot see anything I might need to send you a new version with slightly different logging. By all means nag me if I don't get back to you by tomorrow. Martin When I tested odbc_lob_read it does not miss bytes out but it does return at most buf len - 1 bytes and this is because SQLGetData always puts a trailing NUL byte in the buffer if the type is a char. So if you want 8 bytes at a time you currently need to pass a buf len of 9 if you want them back as chars. My test was retrieving bytes back as SQL_C_CHARs. It is arguable, DBD::ODBC should add 1 knowing that SQLGetData will terminate with a NUL byte if the type is a SQL_C_CHAR. I'd accept an RT for that as if everyone is using odbc_lob_read as documented they should never assume the buffer returned contains buf len bytes - they should use the returned length. As for that TYPE=999 which I'm assuming you got from the example included with DBD::ODBC, it was a mistake that got checked in when I was testing the TYPE parameter - set it to SQL_BINARY or omit it if your sure Oracle has this down as a binary column. The bug in DBD::ODBC is that if you retrieve a lob as SQL_BINARY it is still assuming the buffer is NUL terminated and it is not. The secondary bug is TYPE is coded as Type. The following patch should fix both of these: $ git diff diff --git a/ODBC.xs b/ODBC.xs index 574dc47..8cc8e1a 100644 --- a/ODBC.xs +++ b/ODBC.xs @@ -155,7 +155,7 @@ odbc_lob_read(sth, colno, bufsv, length, attr = NULL) if (attr) { SV **svp; DBD_ATTRIBS_CHECK(odbc_lob_read, sth, attr); - DBD_ATTRIB_GET_IV(attr, Type, 4, svp, sql_type); + DBD_ATTRIB_GET_IV(attr, TYPE, 4, svp, sql_type); } if (SvROK(bufsv)) { bufsv = SvRV(bufsv); diff --git a/dbdimp.c b/dbdimp.c index 5abfb27..ff3226e 100644 --- a/dbdimp.c +++ b/dbdimp.c @@ -5938,7 +5938,10 @@ IV odbc_st_lob_read( Driver did not return the lob length - SQL_NO_TOTAL)); return -1; } -retlen = length - 1; +retlen = length; +if (col_type == SQL_C_CHAR) { +retlen -= 1; +} } else if (rc == SQL_SUCCESS) { if (len == SQL_NULL_DATA) { return 0; If you can apply those changes to your a newly download copy of DBD::ODBC and rebuild it does that fix your issue? If you cannot do that I'll try and release a new test version tonight but I'm mid moving git repositories right now so I'm not promising that. Martin
Re: odbc_lob_read chunk is short
On 06/09/2013 17:52, Martin J. Evans wrote: On 06/09/13 09:18, Martin J. Evans wrote: On 05/09/13 20:28, Mike Grau wrote: Hello, I am completely new at this, but I am trying to read a BLOB in chunks via ODBC in Oracle 11g using DBD::ODBC-1.43, unixODBC-2.3.1 and oracle-instantclient11.2-odbc-11.2. I can read an entire BLOB in one read and write it to the filesystem, but I really need to be able to do it in chunks, I think. Reading the BLOB in chunks, but I am always getting one byte less than requested, losing the last byte, which results in a corrupt document (a PDF) when written to the filesystem. I'm at a loss as to why this is happening and how to correct it. Using this snippet based on the example provided by DBD::ODBC... while($len = $s-odbc_lob_read(1, \my $x, 8, {TYPE = 999})) { print len=$len, x=$x\n; } ... produces this output: len=7, x=435886 len=7, x= n len=7, x=0001282 len=7, x=60 And a tracefile shows the same. SQLGetData(col=1,type=-2)=1 (retlen=2046252) - odbc_lob_read= ( 7 ) [1 items] at /root/lob_read.pl line 45 odbc_lob_read DISPATCH (DBI::st=HASH(0x203c940) rc1/1 @5 g2 ima0 pid#11494) at /root/lob_read.pl line 45 - odbc_lob_read for DBD::ODBC::st (DBI::st=HASH(0x203c940)~0x203c9d0 1 SCALAR(0x1e0a440) 8 HASH(0x203c970)) thr#1cea010 SQLGetData(col=1,type=-2)=1 (retlen=2046244) - odbc_lob_read= ( 7 ) [1 items] at /root/lob_read.pl line 45 odbc_lob_read DISPATCH (DBI::st=HASH(0x203c940) rc1/1 @5 g2 ima0 pid#11494) at /root/lob_read.pl line 45 - odbc_lob_read for DBD::ODBC::st (DBI::st=HASH(0x203c940)~0x203c9d0 1 SCALAR(0x1e0a440) 8 HASH(0x203c9a0)) thr#1cea010 Can someone please give me some guidance how to get the full chunk requested? I'm using unixODBC-2.3.1 - built from the tarball DBD-ODBC-1.43 - installed from the tarball (perl Makefile.PL; make; make install) oracle-instantclient11.2-odbc-11.2.0.3 installed from RPM DBI is the distro's perl-DBI-1.617-4.1.1.x86_64 (openSUSE 12.3) The database and client are both x86_64 The instant client is link against libodbcinst.so.1 and unixODBC now provides libodbcinst.so.2, so I symlinked them: libodbcinst.so.1 - /usr/local/lib64/libodbcinst.so.2 Ugly, but seems to work. Ultimately I want to index documents stored in Oracle with Sphinx and so must use ODBC. I'd like to know that this much is working before moving on to Sphinx. TIA -- Mike G. Bear with me and I'll take another look at that code. If I cannot see anything I might need to send you a new version with slightly different logging. By all means nag me if I don't get back to you by tomorrow. Martin When I tested odbc_lob_read it does not miss bytes out but it does return at most buf len - 1 bytes and this is because SQLGetData always puts a trailing NUL byte in the buffer if the type is a char. So if you want 8 bytes at a time you currently need to pass a buf len of 9 if you want them back as chars. My test was retrieving bytes back as SQL_C_CHARs. It is arguable, DBD::ODBC should add 1 knowing that SQLGetData will terminate with a NUL byte if the type is a SQL_C_CHAR. I'd accept an RT for that as if everyone is using odbc_lob_read as documented they should never assume the buffer returned contains buf len bytes - they should use the returned length. As for that TYPE=999 which I'm assuming you got from the example included with DBD::ODBC, it was a mistake that got checked in when I was testing the TYPE parameter - set it to SQL_BINARY or omit it if your sure Oracle has this down as a binary column. The bug in DBD::ODBC is that if you retrieve a lob as SQL_BINARY it is still assuming the buffer is NUL terminated and it is not. The secondary bug is TYPE is coded as Type. The following patch should fix both of these: $ git diff diff --git a/ODBC.xs b/ODBC.xs index 574dc47..8cc8e1a 100644 --- a/ODBC.xs +++ b/ODBC.xs @@ -155,7 +155,7 @@ odbc_lob_read(sth, colno, bufsv, length, attr = NULL) if (attr) { SV **svp; DBD_ATTRIBS_CHECK(odbc_lob_read, sth, attr); - DBD_ATTRIB_GET_IV(attr, Type, 4, svp, sql_type); + DBD_ATTRIB_GET_IV(attr, TYPE, 4, svp, sql_type); } if (SvROK(bufsv)) { bufsv = SvRV(bufsv); diff --git a/dbdimp.c b/dbdimp.c index 5abfb27..ff3226e 100644 --- a/dbdimp.c +++ b/dbdimp.c @@ -5938,7 +5938,10 @@ IV odbc_st_lob_read( Driver did not return the lob length - SQL_NO_TOTAL)); return -1; } -retlen = length - 1; +retlen = length; +if (col_type == SQL_C_CHAR) { +retlen -= 1; +} } else if (rc == SQL_SUCCESS) { if (len == SQL_NULL_DATA) { return 0; If you can apply those changes to your a newly download copy of DBD::ODBC and rebuild it does that fix your issue? If you cannot do that I'll try and release a new test version tonight but I'm mid moving git repositories right now so I'm not promising that. Martin Sorry
Re: Error running make command for DBI1.51
On 14/08/2013 13:25, pawan bajoria wrote: Hi, I am receiving the error while executing the make command for DBI 1.51. I was able to execute the first command of perl Makefile.PL and create a file Makefile. Please help me resolve this issue as I am stuck here. I want to install the DBD::Oracle finally which needs DBI1.51. RHEL 64-bit DBI1.51 DBD1.64 Oracle Thanks, Pawan Warning: prerequisite File::Spec 1 not found. We have 0.86. This is your first problem which you chose to ignore. Install a newer File::Spec. I see you're using perl 5.008002 on i686-linux-multi, okay. This is very old. /opt/Interwoven/TeamSite/iw-perl/lib/CORE/perl.h:380:24: error: sys/types.h: No such file or directory This is a serious problem - looks like whoever installed this Perl did not run the script to generate header files. Martin -- Martin J. Evans Wetherby, UK
Re: ODBC Driver failing?
On 27/06/13 13:05, Peter J. Holzer wrote: On 2013-06-26 14:55:31 -0500, Dan Bent wrote: $ strace -o strace.log isql -v prod1 user password usage: [ mid sid level] ... The strace equivalent to strace is called tusc on HP-UX. I have it installed in /usr/local/bin which implies that I compiled it myself (almost exactly 10 years ago), but I don't seem to have the source code any more and don't remember where I got it. Unless one of your co-admins installed it you'll probably have to google (or duckduckgo or whatever) for it. hp Thanks Peter. We found tusc off this thread and the resultant trace pin pointed the problem which was a bug with the semaphore code I think I fixed in unixODBC in 2004 - which explains why I didn't remember it. Deleting the semaphore fixed the problem temporarily but upgrading to 2.2.9 (or later) or recompiling without stats support is the only sure long term fix. Martin -- Martin J. Evans Easysoft Limited http://www.easysoft.com
Re: ODBC Driver failing?
On 26/06/2013 17:28, Dan Bent wrote: I suddenly lost the ability to connect to my ODBC database yesterday, after years of using the same function to establish a connection: sub dbaseconnect { if (defined($testing)) { if ($testing eq YES) { $dsn = 'dbi:ODBC:test1' ; print Using test database\n ; } elsif ($testing eq TRAIN) { $dsn = 'dbi:ODBC:train1' ; print Using train1 database\n ; } else { $dsn = 'dbi:ODBC:prod1' ; } } else { $dsn = 'dbi:ODBC:prod1' ; } $user = 'USER' ; $passwd = 'PASSWORD' ; my %adrivers = DBI-available_drivers(); print join(, , %adrivers), \n ; print connecting to DATABASE $dsn $user $passwd\n ; $dbh = DBI-connect($dsn, $user, $passwd, {RaiseError = 1, AutoCommit = 0}) or die Could not connect to database: . DBI-errstr ; print connected to DATABASE $dsn \n ; } So, to gather information about where the failure is, I ran the following program: #! /usr/bin/perl use DBI ; use DBD::ODBC ; use strict ; use warnings ; print Available Drivers: ; my @adrivers = DBI-available_drivers(); print join(, , @adrivers), \n ; print Data Sources: ; foreach my $driver ( @adrivers ) { print Driver: $driver\n; my @dataSources = DBI-data_sources( $driver ); foreach my $dataSource ( @dataSources ) { print \tData Source is $dataSource\n; } print \n; } and the output I got was: Available Drivers: DBM, ExampleP, File, ODBC, Proxy, Sponge Installed Drivers: Data Sources: Driver: DBM Data Source is DBI:DBM:f_dir=. Data Source is DBI:DBM:f_dir=CIGNA Data Source is DBI:DBM:f_dir=Logs Data Source is DBI:DBM:f_dir=ONCOURSE Data Source is DBI:DBM:f_dir=autemp Data Source is DBI:DBM:f_dir=config Data Source is DBI:DBM:f_dir=fh.cob Data Source is DBI:DBM:f_dir=perlscripts Data Source is DBI:DBM:f_dir=pndspndwk Data Source is DBI:DBM:f_dir=prgrun_dir Data Source is DBI:DBM:f_dir=scripts Driver: ExampleP Data Source is dbi:ExampleP:dir=. Driver: File Data Source is DBI:File:f_dir=. Data Source is DBI:File:f_dir=CIGNA Data Source is DBI:File:f_dir=Logs Data Source is DBI:File:f_dir=ONCOURSE Data Source is DBI:File:f_dir=autemp Data Source is DBI:File:f_dir=config Data Source is DBI:File:f_dir=fh.cob Data Source is DBI:File:f_dir=perlscripts Data Source is DBI:File:f_dir=pndspndwk Data Source is DBI:File:f_dir=prgrun_dir Data Source is DBI:File:f_dir=scripts Driver: ODBC and the program just hangs when it looks for data sources using the ODBC driver. So, I suspect that there are issues with the ODBC driver. Here are the versions of the various DBI module components: perl -MDBI -e 'DBI-installed_versions' Perl: 5.008008(PA-RISC1.1-thread-multi) OS : hpux(11.00) DBI : 1.50 DBD::Sponge : 11.10 DBD::Proxy : install_driver(Proxy) failed: Can't locate RPC/PlClient.pm in @INC DBD::ODBC : 1.14 DBD::File : 0.33 DBD::ExampleP : 11.12 DBD::DBM: 0.03 I imagine that I may not have the latest versions of everything, and updates are probably in order, but, while updates are desirable, I'd like to be sure that I'm addressing the root cause of the problem, so I get it resolved. This issue affects a lot of programs, and is critical to our business. Any help or suggestions will be greatly appreciated. You are in deed running VERY old versions - especially of DBD::ODBC. You first need to think about what Jonathan said - he's probably right that working out what changed yesterday is probably going to give the quickest result. Assuming you cannot find anything here are some suggestions. DBD::ODBC is usually linked to an ODBC driver manager but back in the days of 1.14 people still linked directly to an ODBC driver sometimes - ODBC drivers did not support enumerating DSNs - only the driver manager does that. So first thing is hwo was DBD::ODBC built? If you don't know that look for ODBC.so in your perl tree and run the HPUX equivalent of Linux's ldd command on it to find what libraries it depends on (right now I cannot remember what the command is). Once you've done that if the answer is libodbc.so.something then you are probably using the unixODBC driver manager. In that case you should hopefully have an isql binary and you should have an odbc.ini and odbcinst.ini defining your drivers and DSNs. What is in those files. Can you run: isql -v TEST1 username password isql -v TRAIN1 username password isql -v PROD1 username password as you didn't say which one you are using? If you get back with this info I'll help more. Martin -- Martin J. Evans Wetherby, UK
Re: ODBC Driver failing?
On 26/06/2013 18:42, Dan Bent wrote: I agree, and I've been trying to identify what changed yesterday morning. The database, Perl,and the program all reside on the same machine, so I think we can rule out network issues. As far as I know, the DBMS, Perl and ODBC infrastructure have been stable for quite a while, and I haven't tinkered with any of that in recent memory. However, there is another administrator who might have unknowingly deleted files, and there are many users with access to this host (though most can only run one application, and shouldn't be able to get to the ODBC config stuff). I am really the only user who uses Perl and ODBC in the Unix environment. I suppose I could compare the backup tape with what is currently on the system to see if there are files obviously missing, but I'm not exactly sure what I would be looking for, and there could be thousands of files to compare. Am I thinking clearly on this? The files you want to look for are: odbc.ini odbcinst.ini any shared object referenced in any odbcinst.ini file any file DBD::ODBC's ODBC.sl depends on (e.g., libodbc.sl or the driver shared object) If you are not using unixODBC driver manager and you are using iODBC driver manager it is libiodbc.sl. Then you might have had something in your environment e.g., most ODBC driver managers will follow ODBCINI env var or ODBCINSTINI etc. Some ODBC drivers need env vars setting in the environment e.g., DB2 often needs DB2INSTANCE. SO also check any profile files for the user doing ODBC. But if you answer the other questions in my other reply I might be able to help more. Martin On Wed, Jun 26, 2013 at 11:46 AM, Jonathan Leffler jonathan.leff...@gmail.com mailto:jonathan.leff...@gmail.com wrote: On Wed, Jun 26, 2013 at 9:28 AM, Dan Bent db...@comcast.net mailto:db...@comcast.net wrote: I suddenly lost the ability to connect to my ODBC database yesterday, after years of using the same function to establish a connection: So, the question you must ask yourself is: What changed yesterday? Or, if not yesterday, since the previous time when you successfully used the code. Something crucial changed. If it wasn't the Perl plus ODBC infrastructure, then what changed outside that? The DBMS? The networking? Change analysis is likely to get you to the answer quicker than anything else. -- Jonathan Leffler jonathan.leff...@gmail.com mailto:jonathan.leff...@gmail.com #include disclaimer.h Guardian of DBD::Informix - v2013.0521 - http://dbi.perl.org Blessed are we who can laugh at ourselves, for we shall never cease to be amused.
Re: ODBC Driver failing?
On 26/06/2013 19:35, Dan Bent wrote: Big thanks! I did this: ldd /opt/perl_32/lib/site_perl/5.8.8/PA-RISC1.1-thread-multi/auto/DBD/ODBC/ODBC.sl and got: /usr/local/liant/lib/libodbc.sl.1 =/usr/local/liant/lib/libodbc.sl.1 /usr/lib/libc.2 = /usr/lib/libc.2 /usr/lib/libdld.2 =/usr/lib/libdld.2 /usr/lib/libc.2 = /usr/lib/libc.2 /usr/lib/libpthread.1 =/usr/lib/libpthread.1 hmm - never heard of liant. It looks like the unixODBC driver manager but I've never seen it installed in that location. Also, I see you've got isql and that comes with unixODBC. Have you also got a binary called odbcinst and if you have output from odbcinst -j would be useful. I tried isql -v prod1 username password and it just hung like other attempts to access the database. No error messages. OK, so we've ruled out a change in Perl and DBI and DBD::ODBC as it is still going wrong without them. If this really is unixODBC you should have an odbc.ini and odbcinst.ini file probably in /usr/local/liant/etc or /usr/local/etc of /etc. What is in those files? There may also be a .odbc.ini in the users home dir. When we see the contents of those files we'll have a better idea of what driver you are using and the shared library used so you can check that too to see if it has been updated. You could enable unixODBC tracing but it rarely outputs much before connection is complete. I think you need to find the equivalent of strace on HPUX and run it on the isql command to see what system calls are being made. Did you say everything is on one box, so networking off that box cannot be the issue? Martin On Wed, Jun 26, 2013 at 12:11 PM, Martin J. Evans boh...@ntlworld.com mailto:boh...@ntlworld.com wrote: On 26/06/2013 17:28, Dan Bent wrote: I suddenly lost the ability to connect to my ODBC database yesterday, after years of using the same function to establish a connection: sub dbaseconnect { if (defined($testing)) { if ($testing eq YES) { $dsn = 'dbi:ODBC:test1' ; print Using test database\n ; } elsif ($testing eq TRAIN) { $dsn = 'dbi:ODBC:train1' ; print Using train1 database\n ; } else { $dsn = 'dbi:ODBC:prod1' ; } } else { $dsn = 'dbi:ODBC:prod1' ; } $user = 'USER' ; $passwd = 'PASSWORD' ; my %adrivers = DBI-available_drivers(); print join(, , %adrivers), \n ; print connecting to DATABASE $dsn $user $passwd\n ; $dbh = DBI-connect($dsn, $user, $passwd, {RaiseError = 1, AutoCommit = 0}) or die Could not connect to database: . DBI-errstr ; print connected to DATABASE $dsn \n ; } So, to gather information about where the failure is, I ran the following program: #! /usr/bin/perl use DBI ; use DBD::ODBC ; use strict ; use warnings ; print Available Drivers: ; my @adrivers = DBI-available_drivers(); print join(, , @adrivers), \n ; print Data Sources: ; foreach my $driver ( @adrivers ) { print Driver: $driver\n; my @dataSources = DBI-data_sources( $driver ); foreach my $dataSource ( @dataSources ) { print \tData Source is $dataSource\n; } print \n; } and the output I got was: Available Drivers: DBM, ExampleP, File, ODBC, Proxy, Sponge Installed Drivers: Data Sources: Driver: DBM Data Source is DBI:DBM:f_dir=. Data Source is DBI:DBM:f_dir=CIGNA Data Source is DBI:DBM:f_dir=Logs Data Source is DBI:DBM:f_dir=ONCOURSE Data Source is DBI:DBM:f_dir=autemp Data Source is DBI:DBM:f_dir=config Data Source is DBI:DBM:f_dir=fh.cob Data Source is DBI:DBM:f_dir=perlscripts Data Source is DBI:DBM:f_dir=pndspndwk Data Source is DBI:DBM:f_dir=prgrun_dir Data Source is DBI:DBM:f_dir=scripts Driver: ExampleP Data Source is dbi:ExampleP:dir=. Driver: File Data Source is DBI:File:f_dir=. Data Source is DBI:File:f_dir=CIGNA Data Source is DBI:File:f_dir=Logs Data Source is DBI:File:f_dir=ONCOURSE Data Source is DBI:File:f_dir=autemp Data Source is DBI:File:f_dir=config Data Source is DBI:File:f_dir=fh.cob Data Source is DBI:File:f_dir
Re: ODBC Driver failing?
On 26/06/2013 20:29, Dan Bent wrote: I have strace, but don't know how to use it. Usually something like: strace -o out.log isql -v mydsn username password then after it hangs hit ctrl/c and look at (paste here) the last 100 lines of out.log Martin On Wed, Jun 26, 2013 at 2:01 PM, Martin J. Evans boh...@ntlworld.com mailto:boh...@ntlworld.com wrote: On 26/06/2013 19:35, Dan Bent wrote: Big thanks! I did this: ldd /opt/perl_32/lib/site_perl/5.__8.8/PA-RISC1.1-thread-multi/__auto/DBD/ODBC/ODBC.sl and got: /usr/local/liant/lib/libodbc.__sl.1 = /usr/local/liant/lib/libodbc.__sl.1 /usr/lib/libc.2 = /usr/lib/libc.2 /usr/lib/libdld.2 =/usr/lib/libdld.2 /usr/lib/libc.2 = /usr/lib/libc.2 /usr/lib/libpthread.1 =/usr/lib/libpthread.1 hmm - never heard of liant. It looks like the unixODBC driver manager but I've never seen it installed in that location. Also, I see you've got isql and that comes with unixODBC. Have you also got a binary called odbcinst and if you have output from odbcinst -j would be useful. I tried isql -v prod1 username password and it just hung like other attempts to access the database. No error messages. OK, so we've ruled out a change in Perl and DBI and DBD::ODBC as it is still going wrong without them. If this really is unixODBC you should have an odbc.ini and odbcinst.ini file probably in /usr/local/liant/etc or /usr/local/etc of /etc. What is in those files? There may also be a .odbc.ini in the users home dir. When we see the contents of those files we'll have a better idea of what driver you are using and the shared library used so you can check that too to see if it has been updated. You could enable unixODBC tracing but it rarely outputs much before connection is complete. I think you need to find the equivalent of strace on HPUX and run it on the isql command to see what system calls are being made. Did you say everything is on one box, so networking off that box cannot be the issue? Martin On Wed, Jun 26, 2013 at 12:11 PM, Martin J. Evans boh...@ntlworld.com mailto:boh...@ntlworld.com mailto:boh...@ntlworld.com mailto:boh...@ntlworld.com wrote: On 26/06/2013 17:28, Dan Bent wrote: I suddenly lost the ability to connect to my ODBC database yesterday, after years of using the same function to establish a connection: sub dbaseconnect { if (defined($testing)) { if ($testing eq YES) { $dsn = 'dbi:ODBC:test1' ; print Using test database\n ; } elsif ($testing eq TRAIN) { $dsn = 'dbi:ODBC:train1' ; print Using train1 database\n ; } else { $dsn = 'dbi:ODBC:prod1' ; } } else { $dsn = 'dbi:ODBC:prod1' ; } $user = 'USER' ; $passwd = 'PASSWORD' ; my %adrivers = DBI-available_drivers(); print join(, , %adrivers), \n ; print connecting to DATABASE $dsn $user $passwd\n ; $dbh = DBI-connect($dsn, $user, $passwd, {RaiseError = 1, AutoCommit = 0}) or die Could not connect to database: . DBI-errstr ; print connected to DATABASE $dsn \n ; } So, to gather information about where the failure is, I ran the following program: #! /usr/bin/perl use DBI ; use DBD::ODBC ; use strict ; use warnings ; print Available Drivers: ; my @adrivers = DBI-available_drivers(); print join(, , @adrivers), \n ; print Data Sources: ; foreach my $driver ( @adrivers ) { print Driver: $driver\n; my @dataSources = DBI-data_sources( $driver ); foreach my $dataSource ( @dataSources ) { print \tData Source is $dataSource\n; } print \n; } and the output I got was: Available Drivers: DBM, ExampleP, File, ODBC, Proxy, Sponge Installed Drivers: Data Sources: Driver: DBM
Re: ODBC Driver failing?
On 26/06/2013 20:25, Dan Bent wrote: Everything is on one box. Liant was a small development company out of Austin, TX that created a product called Relativity, which allows C-ISAM files to be manipulated as if they were a relational database. The developer of our primary business application (developed in COBOL) used Relativity as a reporting/data extract solution. Over the years Liant got acquired by MicroFocus, and for a number of reasons support is difficult to obtain. Interesting. My company do an ODBC driver for ISAM files too. $ odbcinst -j unixODBC 2.2.7 DRIVERS: /usr/local/liant/etc/odbcinst.ini SYSTEM DATA SOURCES: /usr/local/liant/etc/odbc.ini USER DATA SOURCES..: /home/dbent/.odbc.ini ok, so this is quite old but we now know definitely it is unixODBC. We also know your ODBC drivers are defined in /usr/local/liant/etc/odbcinst.ini and your DSNs are in the other 2 files - I'm presuming dbent is the logged in user. # cat /usr/local/liant/etc/odbc.ini [ODBC Data Sources] prod1 = Relativity Client verify = Relativity Client [prod1] Driver = /usr/local/liant/lib/relclient.sl http://relclient.sl ServerName = chicago.1583 ServerDSN = prod1 QryPlan = 0 ArrayFetchOn= 1 ArrayBufferSize = 8 This is a bit unusual. It looks more like an iODBC odbc.ini file but it doesn't really matter, unixODBC will just ignore [ODBC Data Sources] section. I'm also surprised by that http string but I'm guessing again this is ignored by unixODBC. Also Driver is usually set to the key from the odbcinst.ini file instead of the .sl file again but what you have also works. [test1] Driver = /usr/local/liant/lib/relclient.sl http://relclient.sl ServerName = chicago.1583 ServerDSN = test1 QryPlan = 0 ArrayFetchOn= 1 ArrayBufferSize = 8 [verify] Driver = /usr/local/liant/lib/relclient.sl http://relclient.sl ServerName = chicago.1583 ServerDSN = verify QryPlan = 0 ArrayFetchOn= 1 ArrayBufferSize = 8 # cat /usr/local/liant/etc/odbcinst.ini [ODBC Drivers] Relativity Client = Installed [Relativity Client] Driver = /usr/local/liant/lib/relclient.sl http://relclient.sl Setup = /usr/local/liant/lib/relclnsu.sl http://relclnsu.sl APILevel= 2 ConnectFunction = YYY DriverODBCVer = 02.50 FileUsage = 4 SQLLevel= 0 DefaultServer = chicago SvcEnableBroadcasting = 0 SvcPort = 1599 SvcServer = 127.0.0.1 SvcClntTimeOut = 1 SvcSystemDSN= 0 UpdateEveryXHourSec = 3600 SvcDirect = 1 UpdateAlways= 0 SvcSvrPort = 1583 EnableAutoUpdate= 0 don't really get that last entry. $ ll /usr/local/liant/lib/* lrwxrwxrwx 1 root sys 10 Nov 8 2007 /usr/local/liant/lib/libiodbc.sl http://libiodbc.sl - libodbc.sl http://libodbc.sl lrwxrwxrwx 1 root sys 14 Nov 8 2007 /usr/local/liant/lib/libiodbcinst.sl http://libiodbcinst.sl - libodbcinst.sl http://libodbcinst.sl lrwxrwxrwx 1 root sys 14 Nov 8 2007 /usr/local/liant/lib/libodbc.sl http://libodbc.sl - libodbc.sl.1.0 lrwxrwxrwx 1 root sys 14 Nov 8 2007 /usr/local/liant/lib/libodbc.sl.1 - libodbc.sl.1.0 -r-xr-xr-x 1 root sys2322328 Jan 18 2005 /usr/local/liant/lib/libodbc.sl.1.0 lrwxrwxrwx 1 root sys 18 Nov 8 2007 /usr/local/liant/lib/libodbcinst.sl http://libodbcinst.sl - libodbcinst.sl.1.0 lrwxrwxrwx 1 root sys 18 Nov 8 2007 /usr/local/liant/lib/libodbcinst.sl.1 - libodbcinst.sl.1.0 -r-xr-xr-x 1 root sys 582304 Jan 18 2005 /usr/local/liant/lib/libodbcinst.sl.1.0 -rwxrwxrwx 1 root root638976 May 30 2006 /usr/local/liant/lib/relclient.sl http://relclient.sl -rwxrwxrwx 1 root root 49152 May 30 2006 /usr/local/liant/lib/relclnsu.sl http://relclnsu.sl That explains some things. All the iodbc files are links to odbc and your driver really is named with a space in it. strace it is then - since this pretty much looks ok. Martin On Wed, Jun 26, 2013 at 2:01 PM, Martin J. Evans boh...@ntlworld.com mailto:boh...@ntlworld.com wrote: On 26/06/2013 19:35, Dan Bent wrote: Big thanks! I did this: ldd /opt/perl_32/lib/site_perl/5.__8.8/PA-RISC1.1-thread-multi/__auto/DBD/ODBC/ODBC.sl and got: /usr/local/liant/lib/libodbc.__sl.1 = /usr/local/liant/lib/libodbc.__sl.1 /usr/lib/libc.2 = /usr/lib/libc.2 /usr/lib/libdld.2 =/usr/lib/libdld.2 /usr/lib/libc.2 = /usr/lib/libc.2 /usr/lib/libpthread.1 =/usr/lib/libpthread.1 hmm - never heard of liant. It looks like the unixODBC driver manager but I've never seen
Re: (Fwd) Quick Perl Questions - DBI and DBD
On 10/06/2013 19:14, Prindle, Douglas E wrote: Hi John. Thanks a bunch for responding. Yes we had a new oracle 11 client installed as well. So with that in mind should I recompile or not ? *Thank You,* *Douglas E. Prindle*| Credit Decisioning Systems | Apollo Infrastructure ((904) 954-2472 |6(904) 954-6305 |*douglas.e.prin...@citi.com mailto:douglas.e.prin...@citi.com When you upgrade your Oracle client libs you should generally rebuild DBD::Oracle. Some features in DBD::Oracle depend on the version of Oracle client libs you build against. Also, there is nothing I'm aware from Oracle that says they cannot change the Oracle client libs so you could hit some incompatibility if you don't recompile. Martin -- Martin J. Evans Wetherby, UK
Re: DBI article in Korean
On 24/05/13 06:51, Gabor Szabo wrote: Hi, On Wed, May 22, 2013 at 10:08 PM, Martin J. Evans boh...@ntlworld.com wrote: Another good tutorial - please keep up the good work of promoting Perl. Thanks. I hope others will also help promoting my articles and interviews. The DSN (Data Source Name) (in the $dsn variable) is very straight forward. It contains the type of the database. That will be the clue to DBI which DBD to load. In case of SQLite, the only thing we really need is the path to the database file. It may be a language thing but it is hardly a clue. DBI clearly states the connection string is dbi:DRIVER_NAME:something_else and DRIVER_NAMEs are registered with DBI. Its also not always as straight forward as you suggest as the something_else is usually a ';' separated string of attributes and values e.g. DRIVER={this driver}. I know it can be more complex than my example, but I don't have to frighten away the reader :) I am confused with your comment about the word clue. Isn't the name of DBD::* derived from the DRIVER_NAME ? Or does clue mean something else then guiding information? Or do you mean that DRIVER_NAME is exact and a clue is only a direction? I was only commenting on the use of the word clue. Clue is usually read a little like hint. A clue usually gives you some information but is not everything you need to know whereas the DRIVER_NAME is everything. The call to disconnect from the database is optional as it will automatically be called when the variable $dbh goes out of scope, but it having it might be a clear indication for the next programmer dealing with this code, that you are done with the database. There are a number of gotchas with this. You may still have a select statement active where you've not fetched all the rows yet from the cursor in which case you'll get a warning. Also, you may be in the middle of a transaction and in that case the transaction may be rolled back. Are these gotchas different in case $dbh goes out of scope and when I call $dbh-disconnect ? I'm afraid so because in one case the DBD knows and in the other case it doesn't. Then there is PrintWarn and Warn. use 5.010; use strict; use warnings; use DBI; my $h = DBI-connect(dbi:ODBC:baugi, xx, yy, {RaiseError = 1, PrintError = 0, PrintWarn = 0}); eval { $h-do(q/drop table mje/); }; $h-do(q/create table mje (a integer)/); my $s = $h-prepare(q/insert into mje values(?)/); $s-execute_array(undef, [1,2,3,4,5]); $h-disconnect; doit(Disconnect = 1, PrintWarn = 1, Warn = 1, Txn = 0); doit(Disconnect = 0, PrintWarn = 1, Warn = 1, Txn = 0); doit(Disconnect = 1, PrintWarn = 1, Warn = 0, Txn = 0); doit(Disconnect = 1, PrintWarn = 1, Warn = 1, Txn = 1); doit(Disconnect = 0, PrintWarn = 1, Warn = 1, Txn = 1); doit(Disconnect = 1, PrintWarn = 1, Warn = 0, Txn = 1); sub doit { my %args = @_; say Disconnect=$args{Disconnect}, PrintWarn=$args{PrintWarn}, , Warn=$args{Warn}, , ($args{Txn} ? Uncommitted txn : Unfinished select); my $h = DBI-connect(dbi:ODBC:baugi, xx, yy, {RaiseError = 1, PrintError = 0, PrintWarn = $args{PrintWarn}}); $h-{Warn} = $args{Warn}; my $s; if ($args{Txn}) { $h-{AutoCommit} = 0; $s = $h-prepare(q/delete from mje where a = ?/); $s-execute(1); } else { $s = $h-prepare(q/select * from mje/); $s-execute; $s-fetch; } if ($args{Disconnect}) { say Disconnecting with Warn=$args{Warn}, PrintWarn=$args{PrintWarn}; $h-disconnect; } else { say connection handle going out of scope; } } produces: Disconnect=1, PrintWarn=1, Warn=1, Unfinished select Disconnecting with Warn=1, PrintWarn=1 DBI::db=HASH(0x89ac660)-disconnect invalidates 1 active statement handle (either destroy statement handles or call finish on them before disconnecting) at /tmp/x.pl line 50. Disconnect=0, PrintWarn=1, Warn=1, Unfinished select connection handle going out of scope Disconnect=1, PrintWarn=1, Warn=0, Unfinished select Disconnecting with Warn=0, PrintWarn=1 Disconnect=1, PrintWarn=1, Warn=1, Uncommitted txn Disconnecting with Warn=1, PrintWarn=1 DBD::ODBC::db disconnect warning: Disconnect with transaction in progress - rolling back at /tmp/x.pl line 50. Disconnect=0, PrintWarn=1, Warn=1, Uncommitted txn connection handle going out of scope Issuing rollback() due to DESTROY without explicit disconnect() of DBD::ODBC::db handle baugi at /tmp/x.pl line 22. Disconnect=1, PrintWarn=1, Warn=0, Uncommitted txn Disconnecting with Warn=0, PrintWarn=1 DBD::ODBC::db disconnect warning: Disconnect with transaction in progress - rolling back at /tmp/x.pl line 50. Note how connection handle going out of scope when an unfinished select does not generate anything but calling disconnect does and then specifically calling disconnect but disabling Warn hides the warning. Note for the txn
Re: DBI article in Korean
On 22/05/2013 06:55, Gabor Szabo wrote: Hi, recently I published an introduction to DBI article on the Perl Maven site: http://perlmaven.com/simple-database-access-using-perl-dbi-and-sql the really interesting thing is that articles are constantly being translated to 13 languages. The Korean version has already arrived: http://ko.perlmaven.com/simple-database-access-using-perl-dbi-and-sql regards Gabor Gabor, Another good tutorial - please keep up the good work of promoting Perl. I have a few comments I hope you'll take constructively. Please ignore some of these if you think it is beyond the scope of your tutorial - it is difficult for me to tell the audience you are targeting. Those drivers are compiled together with the C client libraries of the respective database engines. although this is mostly true there are pure perl DBDs. The DSN (Data Source Name) (in the $dsn variable) is very straight forward. It contains the type of the database. That will be the clue to DBI which DBD to load. In case of SQLite, the only thing we really need is the path to the database file. It may be a language thing but it is hardly a clue. DBI clearly states the connection string is dbi:DRIVER_NAME:something_else and DRIVER_NAMEs are registered with DBI. Its also not always as straight forward as you suggest as the something_else is usually a ';' separated string of attributes and values e.g. DRIVER={this driver}. The call to disconnect from the database is optional as it will automatically be called when the variable $dbh goes out of scope, but it having it might be a clear indication for the next programmer dealing with this code, that you are done with the database. There are a number of gotchas with this. You may still have a select statement active where you've not fetched all the rows yet from the cursor in which case you'll get a warning. Also, you may be in the middle of a transaction and in that case the transaction may be rolled back. INSERT You've presented an insert with an 4 columns and then inserted 3 - I know you know why but that might confuse people i.e., it is an auto incrementing column with a default value. SQLite is a bit different from other database in this respect as most would require you to define the id column as auto incrementing or having a default value. UPDATE Your example with the do method if fine but often people want to insert or update multiple rows and I think it is worth showing you can prepare a stmt and execute it many times with different parameters. As you do with the select example. This is by far the most interesting part of the database access. As the SELECT statement can return a lot of rows and a lot of values in each row we cannot use a simple call to the do method. Some DBDs really dislike using the do method for select stmts and especially multiple stmts in the same SQL e.g., do(q/something; select something from something/); Martin -- Martin J. Evans Wetherby, UK
Re: DBI Module installtion
On 29/04/2013 14:55, Anoop Kumar Paramesweran wrote: Hi Support, I am receiving below error while installing (make ) DBI module in my AIX box. As I am new to Perl installations could you please help me to solve this.. xlc_r -q32 -c -D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE -qmaxmem=-1 -qnoansialias -DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT -qlanglvl=extended -I/usr/local/include -q32 -D_LARGE_FILES -qlonglong -O -DVERSION=\1.625\ -DXS_VERSION=\1.625\ -I/usr/opt/perl5/lib/5.10.1/aix-thread-multi/CORE Perl.c /bin/sh: xlc_r: not found. make: 1254-004 The error code from the last command is 127. Stop. The Perl you are using was compiled with the AIX C compiler and it is either a) not installed or b) it is not on your path. You can either: a) add the AIX compiler xlc_r to your path if you have it installed (you usually have to pay IBM for this compiler) b) install the IBM compiler and add it to your PATH. c) install another compiler such as gcc (which is free), build Perl yourself into some dir you can point your PATH at and then install DBI. You might find perlbrew useful if you end up here. Mostly when people report the issue you have they have installed Perl from a package provided by IBM that they built with their compiler and you don't have this compiler. When Perl is built is records the compiler and options used and generally you cannot compile Perl itself with one compiler and then modules which require a C compiler with another compiler. Martin -- Martin J. Evans Wetherby, UK
Re: (Fwd) Bug in Oracle 11g2 with DBD::Oracle
On 05/03/13 12:14, Charles Jardine wrote: On 05/03/13 10:08, tim.bu...@pobox.com wrote: - Forwarded message from Mahdi Sbeih mahdi_sb...@hotmail.com - Date: Tue, 5 Mar 2013 00:45:26 -0800 From: Mahdi Sbeih mahdi_sb...@hotmail.com To: tim.bu...@pobox.com tim.bu...@pobox.com Subject: Bug in Oracle 11g2 with DBD::Oracle Dear Tim, Sorry for sending this email directly to you, but I am not an active member in Perl lists and forums and maybe you can direct this email to whom is responsible for the development of Perl DBD::Oracle module. I found a bug related to the ora_connect_with_default_signals feature. On our system RHEL5 Oracle11gR2, we had to use this feature on the child signal in order to avoid the -1 return from the system call ora_connect_with_default_signals = ['CHLD'] The best way to deal with the -1 return from system() is to add bequeath_detach = yes Agreed. to your client-side sqlnet.ora file. If you do this you will not need to use ora_connect_with_default_signals. This caused a sever bug, if the Perl script is running in the background and doesn't exit, every time it connects to the oracle database server it will create a zombie process, and this will later crash the machine itself since it will consume all the processes on the machine. I think that ora_connect_with_default_signals is misguided. As it happens so do I when it comes to SIGCHLD. However, some Oracle client libraries also trap SIGINT and SIGQUIT where I have more of a problem. Perhaps I'll find some time to update the pod. Martin -- Martin J. Evans Easysoft Limited http://www.easysoft.com
Final development release of DBD::ODBC 1.42 series
I've just uploaded DBD::ODBC 1.42_5 to CPAN. I'm hoping this is going to be the final development release of the 1.42 series. If you rely on DBD::ODBC then please test it. The changes since the last full release are below. In particular note that there is a small change in behaviour. =head2 Changes in DBD::ODBC 1.42_5 January 25 2013 [BUG FIXES] Not all modules used in test code were specified in build_requires. =head2 Changes in DBD::ODBC 1.42_4 January 21 2013 [ENHANCEMENTS] odbc_trace and odbc_trace_file are now full connection attributes so you can set them any time you like, not just in connect. =head2 Changes in DBD::ODBC 1.42_3 January 17 2013 [ENHANCEMENTS] Added odbc_trace_file and odbc_trace attributes to the connect method so you can now enable ODBC API tracing from the connect method instead of having to use the ODBC Driver Manager. These also only enable ODBC API tracing in the application which made the call unlike the ODBC Driver Manager settings. =head2 Changes in DBD::ODBC 1.42_2 December 17 2012 [MISCELLANEOUS] Changed any use of if SvUPGRADE to remove the if test as per email from Dave Mitchell and posting at http://www.xray.mpe.mpg.de/mailing-lists/perl5-porters/2012-12/msg00424.html. =head2 Changes in DBD::ODBC 1.42_1 December 12 2012 [BUG FIXES] DBD::ODBC's ExecDirect method did not return an SQLLEN so if you managed to affect a massive number of rows it would be cast to an int and hence precision lost. [CHANGE IN BEHAVIOUR] When you called DBI's execute method and odbc_exec_direct was not set (the default) if you managed to affect more rows than would fit into an int you would get the incorrect count (NOTE on 32 bit platforms ODBC's SQLRowCount can only return a 32bit value anyway). You would get whatever casting an SQLLEN to an int would give you. The fix for this needs a change to DBI (see RT 81911) and the change would probably impact every DBD so until then DBD::ODBC will a) warn if an overflow occurs and Warn is set on the handle b) return INT_MAX and c) provide a new statement method odbc_rows which you can use to get the correct value. [ENHANCEMENTS] New odbc_rows statement method (see above). [MISCELLANEOUS] New rt_81911.t test case. =head2 Changes in DBD::ODBC 1.42_0 November 28 2012 [BUG FIXES] MS Access requires a longchar column to be bound using SQL_LONGVARCHAR. However, MS Access does not support SQLDescribeParam and we default to SQL_VARCHAR in this case. The point at which we switch to SQL_LONGVARCHAR was defaulted to 4000 (for MS SQL Server). We now default to SQL_LONGVARCHAR for MS Access when data is 255. This means you can remove those {TYPE = SQL_LONGVARCHAR} from your bind_param calls for longchar columns in MS Access. I seem to have introduced a bug in the test suite for MS Access. The last test in the 09bind test binds dates as varchars (by default) and this cannot work in MS Access (it needs to be a timestamp). This test was skipped in the past and the skip got removed. [MISCELLANEOUS] Steffen Goeldner reported some issues with execute_array in DBD::Oracle where if ArrayTupleStatus was not specified and an error occurred DBD::Oracle did not do the right thing. As I used DBD::Oracle as a base when I wrote execute_for_fetch in DBD::ODBC I added tests to the test suite to ensure these issues did not exist in DBD::ODBC. Minor change to sql_type_cast.t test which attempts to insert an integer into a varchar. No databases so far have complained about this until we ran the test against Derby. Changed to use '100'. RT 80446 - fix spelling mistake - thanks to Xavier Guimar. Martin -- Martin J. Evans Easysoft Limited http://www.easysoft.com
Re: Possible database handle leak in DBI?
On 05/12/12 03:33, Duncan McEwan wrote: Hi, So to summarize, you are seeing that sometimes connect_cached uses a previous entry, and sometimes it does not, right? ... ... When the connect_cached fails with a false negative, it is because the CachedKids hash is empty, when it should have at least one existing item, right? Good summary! Yes, the problem only occurs occasionally. Based on the debugging I have in DBI.pm, out of approx 160,000 calls to connect_cached() over the last 2 days, the problem occurred around 360 times. I'd bump up the trace level to at least 3 ... I had thought about turning on DBI tracing but I was a bit worried about the amount of output that would be generated. But having just read the TRACING section of the DBI pod I see that I can also specify that I only want to trace the connection process, so maybe it won't be so bad. I'll try it and find out! If you mean the CON trace flag I think you are going to be disappointed. Although DBI defines this trace flag you'll get very little out of it. There is only one DBD which I know (DBD::ODBC) which adds anything for CON tracing. When I connect using CON tracing and DBD::mysql I get: $ perl -le 'use DBI; DBI-trace(DBI-parse_trace_flag('CON'));my $h = DBI-connect_cached(dbi:mysql:database=test);$h-disconnect; $h = undef;$h = DBI-connect_cached(dbi:mysql:database=test);' - default_user in DBD::_::dr for DBD::mysql::dr (DBI::dr=HASH(0x8f7d180)~0x8f7d2f0 undef undef HASH(0x8f8c130)) - default_user= ( undef undef ) [2 items] at DBI.pm line 646 - connect_cached in DBD::_::dr for DBD::mysql::dr (DBI::dr=HASH(0x8f7d180)~0x8f7d2f0 'database=test' undef HASH(0x8f8c130)) - connect_cached= ( DBI::db=HASH(0x9018e80) ) [1 items] at DBI.pm line 658 - connected in DBD::_::db for DBD::mysql::db (DBI::db=HASH(0x9018e80)~0x9018f70 'dbi:mysql:database=test' undef HASH(0x8eed1a8)) - connected= ( undef ) [1 items] at DBI.pm line 720 - disconnect for DBD::mysql::db (DBI::db=HASH(0x9018e80)~0x9018f70) - disconnect= ( 1 ) [1 items] at -e line 1 - default_user in DBD::_::dr for DBD::mysql::dr (DBI::dr=HASH(0x8f7d180)~0x8f7d2f0 undef undef HASH(0x9018d00)) - default_user= ( undef undef ) [2 items] at DBI.pm line 646 - connect_cached in DBD::_::dr for DBD::mysql::dr (DBI::dr=HASH(0x8f7d180)~0x8f7d2f0 'database=test' undef HASH(0x9018d00)) - connect_cached= ( DBI::db=HASH(0x8f80ab0) ) [1 items] at DBI.pm line 658 - connected in DBD::_::db for DBD::mysql::db (DBI::db=HASH(0x8f80ab0)~0x8f80b10 'dbi:mysql:database=test' undef HASH(0x9019530)) - connected= ( undef ) [1 items] at DBI.pm line 720 - disconnect_all for DBD::mysql::dr (DBI::dr=HASH(0x8f7d180)~0x8f7d2f0) - disconnect_all= ( ) [0 items] (not implemented) at DBI.pm line 737 DESTROY DBI::dr=HASH(0x8f7d2f0) clearing 1 CachedKids which apart from the clearing 1 CachedKids I don't think tells you much. If you add/change tracing whilst you are debugging and you think it is useful please feed it back. or at least check out the clear_cached_kids function inside of DBI.xs and get some debugging there to see who is clearing that cache ... Delving into the C code was also something I was hoping to avoid, but if turning on DBI connection tracing doesn't reveal anything I'll have a look at that. It is not that bad. The clear_cached_kids is in DBI.xs and the tracing code is fairly straight forward. Bear in mind you might have to rebuild DBD::mysql after some changes to DBI. As far as DBI itself, I think only destroying a handle will call that function, and that function is the only thing that will empty out {CachedKids}. Sounds reasonable. I'll see if debugging or code inspection reveals any way that a handle could be destroyed without the connection being closed. The other thing to consider is the web app. I know that Apache::DBI/ modperl has a lot of special rules regarding connecting and reconnecting: it's possible your fastcgi does as well. You might also want to poke around with the mysql_auto_reconnect setting; it's possible it is on and causing issues. Yes - I did see a test for the GATEWAY_INTERFACE env variable in DBD::mysql::connect() which causes the mysql_auto_reconnect setting to be turned on. That was the only reference I found (in the perl code at least) to that environment variable. From what I could make out, if MOD_PERL is set a whole different connection procedure is invoked using Apache::DBI::connect, but that doesn't seem to be the case if running under fastcgi. Anyway, thanks for the pointers. I'll report back with any extra information I find. Duncan Martin -- Martin J. Evans Easysoft Limited http://www.easysoft.com
Fwd: DBD::Oracle Schema different than User question
Forwarded to dbi-users. Original Message Subject: DBD::Oracle Schema different than User question Date: Mon, 12 Nov 2012 13:04:05 -0500 From: Kevin L. Kane kevin.k...@gmail.com To: Tim Bunce t...@cpan.org, Yanick Champoux yan...@cpan.org, Martin J. Evans mjev...@cpan.org Hi all, I am running into a problem and was planning on modifying my local DBD::Oracle to add support for a schema=mySchema construct in the connect string. Specifically, I want to connect as user X but set current_schema to Y. Another solution i've toyed with is having a trigger that switches my schema when I log in but I need to do this for a lot of different schemas and I will always be the same user. It seems weird to me that support for this isn't included in DBD::Oracle currently. If I do this in a sane way are you at all interested in the patch? Am I just missing something and this functionality is already there? Or should this functionality not exist in the first place and why? Thanks, Kevin -- Kevin L. Kane kevin.kane at gmail.com
Fwd: DBD::Oracle Schema different than User question
Hi Kevin, I've forwarded your email on to the dbi-users list. See http://dbi.perl.org and look at the support page. Sorry for top posting but my email client is having some sort of fit with your email. I don't have any issue with any well formed patch to set the schema but I'll wait to see what others say as personally I've never had to change it. Martin Original Message Subject: DBD::Oracle Schema different than User question Date: Mon, 12 Nov 2012 13:04:05 -0500 From: Kevin L. Kane kevin.k...@gmail.com To: Tim Bunce t...@cpan.org, Yanick Champoux yan...@cpan.org, Martin J. Evans mjev...@cpan.org Hi all, I am running into a problem and was planning on modifying my local DBD::Oracle to add support for a schema=mySchema construct in the connect string. Specifically, I want to connect as user X but set current_schema to Y. Another solution i've toyed with is having a trigger that switches my schema when I log in but I need to do this for a lot of different schemas and I will always be the same user. It seems weird to me that support for this isn't included in DBD::Oracle currently. If I do this in a sane way are you at all interested in the patch? Am I just missing something and this functionality is already there? Or should this functionality not exist in the first place and why? Thanks, Kevin -- Kevin L. Kane kevin.kane at gmail.com
Re: Trying to get DBI to install on Raspberry Pi with Wheezy OS
On 28/10/2012 13:18, Steven Haun wrote: I have tried cpan and cpanm installs and get very non description errors (other than failed). Any help would be appreciated. Thanks. I installed DBI on a raspberry Pi ages ago. See bottom of http://www.martin-evans.me.uk/node/142 and http://www.martin-evans.me.uk/node/144 I had some problems running out of memory (from the above post): BTW, I tried to install Perl DBI (which does not have many dependencies) with cpanp and after 13 minutes it died with the signal 'Killed' - hmm. I've not investigated further yet - but I had plenty of disk space left on a 4gb card. *UPDATE:* It appears I had some problems with my sd card - switched to another one and the machine seems faster but cpanp still bombs out as before - trying cpanminus. *UPDATE2:* ilmari suggested cpanp might be a big memory user so I installed cpanm and managed to install DBI - although the test suite took over 60 minutes to run. Doesn't cpanm have a verbose mode. Martin
Re: Trying to get DBI to install on Raspberry Pi with Wheezy OS
On 28/10/2012 13:34, Steven Haun wrote: I am using 'sudo cpanm -i DBI' . I found the details in the log (forgot to open it as sudo, so it looked blank). After the cp commands I found this (but don't know what 'gcc-4.7' is): /usr/bin/perl -p -e s/~DRIVER~/Perl/g ./Driver.xst Perl.xsi /usr/bin/perl /usr/share/perl/5.14/ExtUtils/xsubpp -typemap /usr/share/perl/5.14/ExtUtils/typemap -typemap typemap Perl.xs Perl.xsc mv Perl.xsc Per$ gcc-4.7 -c -D_REENTRANT -D_GNU_SOURCE -DDEBIAN -fstack-protector -fno-strict-aliasing -pipe -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS$ /bin/sh: 1: gcc-4.7: not found make: *** [Perl.o] Error 127 - FAIL Installing DBI failed. See /root/.cpanm/build.log for details. gcc is a C compiler - you need it to compile the C code in DBI. You need to install the gcc package. As your using debian it is probably something like sudo apt-get install gcc Martin On Oct 28, 2012, at 8:27 AM, Martin J. Evans martin.ev...@easysoft.com wrote: On 28/10/2012 13:18, Steven Haun wrote: I have tried cpan and cpanm installs and get very non description errors (other than failed). Any help would be appreciated. Thanks. I installed DBI on a raspberry Pi ages ago. See bottom of http://www.martin-evans.me.uk/node/142 and http://www.martin-evans.me.uk/node/144 I had some problems running out of memory (from the above post): BTW, I tried to install Perl DBI (which does not have many dependencies) with cpanp and after 13 minutes it died with the signal 'Killed' - hmm. I've not investigated further yet - but I had plenty of disk space left on a 4gb card. *UPDATE:* It appears I had some problems with my sd card - switched to another one and the machine seems faster but cpanp still bombs out as before - trying cpanminus. *UPDATE2:* ilmari suggested cpanp might be a big memory user so I installed cpanm and managed to install DBI - although the test suite took over 60 minutes to run. Doesn't cpanm have a verbose mode. Martin
Re: Trying to get DBI to install on Raspberry Pi with Wheezy OS
On 28/10/2012 13:40, Steven Haun wrote: It appears to be installed: root@raspberrypi:/home/pi# sudo apt-get install gcc Reading package lists... Done Building dependency tree Reading state information... Done gcc is already the newest version. gcc set to manually installed. 0 upgraded, 0 newly installed, 0 to remove and 0 not upgraded. root@raspberrypi:/home/pi What does perl -V output? Martin On Oct 28, 2012, at 8:36 AM, Martin J. Evans martin.ev...@easysoft.com wrote: On 28/10/2012 13:34, Steven Haun wrote: I am using 'sudo cpanm -i DBI' . I found the details in the log (forgot to open it as sudo, so it looked blank). After the cp commands I found this (but don't know what 'gcc-4.7' is): /usr/bin/perl -p -e s/~DRIVER~/Perl/g ./Driver.xst Perl.xsi /usr/bin/perl /usr/share/perl/5.14/ExtUtils/xsubpp -typemap /usr/share/perl/5.14/ExtUtils/typemap -typemap typemap Perl.xs Perl.xsc mv Perl.xsc Per$ gcc-4.7 -c -D_REENTRANT -D_GNU_SOURCE -DDEBIAN -fstack-protector -fno-strict-aliasing -pipe -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS$ /bin/sh: 1: gcc-4.7: not found make: *** [Perl.o] Error 127 - FAIL Installing DBI failed. See /root/.cpanm/build.log for details. gcc is a C compiler - you need it to compile the C code in DBI. You need to install the gcc package. As your using debian it is probably something like sudo apt-get install gcc Martin On Oct 28, 2012, at 8:27 AM, Martin J. Evans martin.ev...@easysoft.com wrote: On 28/10/2012 13:18, Steven Haun wrote: I have tried cpan and cpanm installs and get very non description errors (other than failed). Any help would be appreciated. Thanks. I installed DBI on a raspberry Pi ages ago. See bottom of http://www.martin-evans.me.uk/node/142 and http://www.martin-evans.me.uk/node/144 I had some problems running out of memory (from the above post): BTW, I tried to install Perl DBI (which does not have many dependencies) with cpanp and after 13 minutes it died with the signal 'Killed' - hmm. I've not investigated further yet - but I had plenty of disk space left on a 4gb card. *UPDATE:* It appears I had some problems with my sd card - switched to another one and the machine seems faster but cpanp still bombs out as before - trying cpanminus. *UPDATE2:* ilmari suggested cpanp might be a big memory user so I installed cpanm and managed to install DBI - although the test suite took over 60 minutes to run. Doesn't cpanm have a verbose mode. Martin
Re: Trying to get DBI to install on Raspberry Pi with Wheezy OS
On 28/10/2012 13:47, Steve Haun wrote: Summary of my perl5 (revision 5 version 14 subversion 2) configuration: Platform: osname=linux, osvers=3.2.0-2-mx5, archname=arm-linux-gnueabihf-thread-multi-64int uname='linux build04.raspbian.lan 3.2.0-2-mx5 #1 sun apr 15 19:59:08 utc 2012 armv7l gnulinux ' config_args='-Dusethreads -Duselargefiles -Dcc=gcc-4.7 -Dccflags=-DDEBIAN -D_FORTIFY_SOURCE=2 -g -O2 -fstack-protector --param=ssp-buffer-size=4 -Wformat -Werror=format-security -Dldflags= -Wl,-z,relro -Dlddlflags=-shared -Wl,-z,relro -Dcccdlflags=-fPIC -Darchname=arm-linux-gnueabihf -Dprefix=/usr -Dprivlib=/usr/share/perl/5.14 -Darchlib=/usr/lib/perl/5.14 -Dvendorprefix=/usr -Dvendorlib=/usr/share/perl5 -Dvendorarch=/usr/lib/perl5 -Dsiteprefix=/usr/local -Dsitelib=/usr/local/share/perl/5.14.2 -Dsitearch=/usr/local/lib/perl/5.14.2 -Dman1dir=/usr/share/man/man1 -Dman3dir=/usr/share/man/man3 -Dsiteman1dir=/usr/local/man/man1 -Dsiteman3dir=/usr/local/man/man3 -Duse64bitint -Dman1ext=1 -Dman3ext=3perl -Dpager=/usr/bin/sensible-pager -Uafs -Ud_csh -Ud_ualarm -Uusesfio -Uusenm -Ui_libutil -DDEBUGGING=-g -Doptimize=-O2 -Duseshrplib -Dlibperl=libperl.so.5.14.2 -des' hint=recommended, useposix=true, d_sigaction=define useithreads=define, usemultiplicity=define useperlio=define, d_sfio=undef, uselargefiles=define, usesocks=undef use64bitint=define, use64bitall=undef, uselongdouble=undef usemymalloc=n, bincompat5005=undef Compiler: cc='gcc-4.7', ccflags ='-D_REENTRANT -D_GNU_SOURCE -DDEBIAN -fstack-protector Your perl was compiled with gcc-4.7 and so that is what gets used when you build a module with C code. My raspberry pi is at work right now so I cannot check but basically the build is attempting to run gcc-4.7 and it either does not exist or is not on your path. What do you get when you type the following at the command line: $ which gcc $ gcc $ gcc --version $ gcc-4.7 If gcc --version reports 4.7 then you can probably get away with adding a symbolic link in whatever dir gcc is found (see which gcc above) from gcc-4.7 to gcc. Something like: cd /usr/bin ln -s /usr/bin/gcc gcc-4.7 but that depends on what which gcc and gcc --version says. BTW, I'm on irc.perl.org in the #dbi channel (as mje) if you want a chat. Martin snipped more -V output On Oct 28, 2012, at 8:42 AM, Martin J. Evans wrote: On 28/10/2012 13:40, Steven Haun wrote: It appears to be installed: root@raspberrypi:/home/pi# sudo apt-get install gcc Reading package lists... Done Building dependency tree Reading state information... Done gcc is already the newest version. gcc set to manually installed. 0 upgraded, 0 newly installed, 0 to remove and 0 not upgraded. root@raspberrypi:/home/pi What does perl -V output? Martin On Oct 28, 2012, at 8:36 AM, Martin J. Evans martin.ev...@easysoft.com wrote: On 28/10/2012 13:34, Steven Haun wrote: I am using 'sudo cpanm -i DBI' . I found the details in the log (forgot to open it as sudo, so it looked blank). After the cp commands I found this (but don't know what 'gcc-4.7' is): /usr/bin/perl -p -e s/~DRIVER~/Perl/g ./Driver.xst Perl.xsi /usr/bin/perl /usr/share/perl/5.14/ExtUtils/xsubpp -typemap /usr/share/perl/5.14/ExtUtils/typemap -typemap typemap Perl.xs Perl.xsc mv Perl.xsc Per$ gcc-4.7 -c -D_REENTRANT -D_GNU_SOURCE -DDEBIAN -fstack-protector -fno-strict-aliasing -pipe -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS$ /bin/sh: 1: gcc-4.7: not found make: *** [Perl.o] Error 127 - FAIL Installing DBI failed. See /root/.cpanm/build.log for details. gcc is a C compiler - you need it to compile the C code in DBI. You need to install the gcc package. As your using debian it is probably something like sudo apt-get install gcc Martin On Oct 28, 2012, at 8:27 AM, Martin J. Evans martin.ev...@easysoft.com wrote: On 28/10/2012 13:18, Steven Haun wrote: I have tried cpan and cpanm installs and get very non description errors (other than failed). Any help would be appreciated. Thanks. I installed DBI on a raspberry Pi ages ago. See bottom of http://www.martin-evans.me.uk/node/142 and http://www.martin-evans.me.uk/node/144 I had some problems running out of memory (from the above post): BTW, I tried to install Perl DBI (which does not have many dependencies) with cpanp and after 13 minutes it died with the signal 'Killed' - hmm. I've not investigated further yet - but I had plenty of disk space left on a 4gb card. *UPDATE:* It appears I had some problems with my sd card - switched to another one and the machine seems faster but cpanp still bombs out as before - trying cpanminus. *UPDATE2:* ilmari suggested cpanp might be a big memory user so I installed cpanm and managed to install DBI - although the test suite took over 60 minutes to run. Doesn't cpanm have a verbose mode. Martin
Re: Trying to get DBI to install on Raspberry Pi with Wheezy OS
#73398] cpan/CPANPLUS: add NAME headings in modules with POD DEBPKG:fixes/manpage_name_Test-Harness - http://bugs.debian.org/650451 [rt.cpan.org #73399] cpan/Test-Harness: add NAME headings in modules with POD DEBPKG:fixes/manpage_name_Term-UI - http://bugs.debian.org/650452 [rt.cpan.org #73400] cpan/Term-UI: add NAME headings in modules with POD DEBPKG:fixes/podlators_ae_ligature_fallback - http://bugs.debian.org/652851 Fix the ASCII fallback string for AE DEBPKG:fixes/fsf_postal_address - [de89470] Update references to the FSF's postal address DEBPKG:fixes/cpan_module_pod_fixes - [perl #106870] [rt.cpan.org #73447] [rt.cpan.org #73446] Fix POD formatting in Term-Cap and Pod-Parser DEBPKG:fixes/cgi_no_shellwords_pl - Use Text::ParseWords instead of shellwords.pl DEBPKG:fixes/path_max_fallback - [perl #109262] http://bugs.debian.org/656869 Don't use _POSIX_PATH_MAX as a fallback PATH_MAX DEBPKG:debian/makemaker-pasthru - http://bugs.debian.org/660195 [rt.cpan.org #28632] Make EU::MM pass LD through to recursive Makefile.PL invocations DEBPKG:fixes/propagate_tainted_errors.patch - http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=663158 [perl #111654] properly propagate tainted errors DEBPKG:debian/perl5db-x-terminal-emulator.patch - http://bugs.debian.org/668490 Invoke x-terminal-emulator rather than xterm in perl5db.pl DEBPKG:fixes/socket_cache_propagate - http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=659075 [rt.cpan.org #61577] [perl #112736] sockdomain and socktype undef on newly accepted sockets Built under linux Compiled at Jun 26 2012 01:20:41 @INC: /etc/perl /usr/local/lib/perl/5.14.2 /usr/local/share/perl/5.14.2 /usr/lib/perl5 /usr/share/perl5 /usr/lib/perl/5.14 /usr/share/perl/5.14 /usr/local/lib/site_perl . On Oct 28, 2012, at 8:42 AM, Martin J. Evans wrote: On 28/10/2012 13:40, Steven Haun wrote: It appears to be installed: root@raspberrypi:/home/pi# sudo apt-get install gcc Reading package lists... Done Building dependency tree Reading state information... Done gcc is already the newest version. gcc set to manually installed. 0 upgraded, 0 newly installed, 0 to remove and 0 not upgraded. root@raspberrypi:/home/pi What does perl -V output? Martin On Oct 28, 2012, at 8:36 AM, Martin J. Evans martin.ev...@easysoft.com wrote: On 28/10/2012 13:34, Steven Haun wrote: I am using 'sudo cpanm -i DBI' . I found the details in the log (forgot to open it as sudo, so it looked blank). After the cp commands I found this (but don't know what 'gcc-4.7' is): /usr/bin/perl -p -e s/~DRIVER~/Perl/g ./Driver.xst Perl.xsi /usr/bin/perl /usr/share/perl/5.14/ExtUtils/xsubpp -typemap /usr/share/perl/5.14/ExtUtils/typemap -typemap typemap Perl.xs Perl.xsc mv Perl.xsc Per$ gcc-4.7 -c -D_REENTRANT -D_GNU_SOURCE -DDEBIAN -fstack-protector -fno-strict-aliasing -pipe -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS$ /bin/sh: 1: gcc-4.7: not found make: *** [Perl.o] Error 127 - FAIL Installing DBI failed. See /root/.cpanm/build.log for details. gcc is a C compiler - you need it to compile the C code in DBI. You need to install the gcc package. As your using debian it is probably something like sudo apt-get install gcc Martin On Oct 28, 2012, at 8:27 AM, Martin J. Evans martin.ev...@easysoft.com wrote: On 28/10/2012 13:18, Steven Haun wrote: I have tried cpan and cpanm installs and get very non description errors (other than failed). Any help would be appreciated. Thanks. I installed DBI on a raspberry Pi ages ago. See bottom of http://www.martin-evans.me.uk/node/142 and http://www.martin-evans.me.uk/node/144 I had some problems running out of memory (from the above post): BTW, I tried to install Perl DBI (which does not have many dependencies) with cpanp and after 13 minutes it died with the signal 'Killed' - hmm. I've not investigated further yet - but I had plenty of disk space left on a 4gb card. *UPDATE:* It appears I had some problems with my sd card - switched to another one and the machine seems faster but cpanp still bombs out as before - trying cpanminus. *UPDATE2:* ilmari suggested cpanp might be a big memory user so I installed cpanm and managed to install DBI - although the test suite took over 60 minutes to run. Doesn't cpanm have a verbose mode. Martin
New 1.41 release of DBD::ODBC
attempt to bind an rv without amagic DBD::ODBC will now croak - related to rt 78838. Martin -- Martin J. Evans Easysoft Limited http://www.easysoft.com
New development release of DBD::ODBC 1.40_3
I will probably release this as 1.41 in the next week. Please note the changes in behaviour. =head2 Changes in DBD::ODBC 1.40_3 October 8 2012 [BUG FIXES] Oops, changes to some rt tests fail when not run to MS SQL Server and they should not be run for other drivers - there was a double done_testing call. [CHANGE IN BEHAVIOUR] As I warned literally years ago DBD::ODBC's private function DescribeCol has been removed. You can use DBI's statement attributes like NAME, PRECISION etc, instead. All test code has been changed to remove calls to DescribeCol and GetTypeInfo. [MISCELLANEOUS] New example sqlserver_supplementary_chrs.pl added which shows that in MS SQL Server 2012 you can now store unicode characters over 0x (ones which are surrogate pairs). More documentation for odbc_out_connect_string. =head2 Changes in DBD::ODBC 1.40_2 September 6 2012 [BUG FIXES] Fixed rt 78838 - bind_param does not correctly stringify blessed objects when connected to MS SQL Server Fix issue in dbd_bind_ph where if you passed a sql type and were also attempting to change from in to out or vice versa or increasing the size of an output bound param it would not spot this error. Allowed the test cases to spot DB2 driver as libXXXdb2. [MISCELLANEOUS] New test cases added for some rts. Added Test::NoWarnings to some tests where it was missing. =head2 Changes in DBD::ODBC 1.40_1 September 4 2012 [BUG FIXES] Debian/Ubuntu have moved unixODBC into /usr/lib/i386-linux-gnu so look in this dir for unixODBC as well - thanks to Meastro for finding. Fixed rt 78838 I had a sequence point error which is only seen with some compilers as it is sometimes optimized out. It could cause DBD::ODBC to omit adding the UID/PWD to the end of the connection string when using DSN=. Thanks to Zsolt Cserna for spotting it and to ilmari and Leon for explaining it to me. Fixed rt 79397 Output bound parameters may be incorrectly bound if changed after bind_param_inout is called. If you start with an undef bound param and change it to a defined string/number less than 28 characters before calling execute the original undef will probably be bound. Thanks to runrig on perl monks for providing an example. [CHANGE IN BEHAVIOUR] If you attempt to bind an rv without amagic DBD::ODBC will now croak - related to rt 78838.
Re: is this a viable option?
On 01/10/2012 18:29, Jack Craig wrote: Hi Folks, I have an html / perl app running on host A, i want to do a remote oracle query on host B from host A. Assuming proper credentials, is this a viable configuration? This as opposed to running perl dbi module installed on host B. TIA, jackc... Yes. Install Oracle Instant Client (you need the sdk, client, sqlplus). Untar it somewhere accessible from your web user. Add untarred_dir/bin to PATH and export it. Run sqlplus with the correct args to connect to your Oracle to prove the client works. Download DBD::Oracle. Assuming Unix or Linux, set LD_LIBRARY_PATH (or equivalent) to point to the dir you untarred instant client into. Do the usual with DBD::Oracle - perl Makfile.PL, make, make test, make install. Write Perl code using DBI module to issue query and get results. Martin
Fwd: Re: oracle instant client for Linux (oracle 9.2) ?
Oops, forgot dbi-users: Original Message Subject:Re: oracle instant client for Linux (oracle 9.2) ? Date: Mon, 01 Oct 2012 19:17:43 +0100 From: Martin J. Evans martin.ev...@easysoft.com Organisation: Easysoft Limited To: Jack Craig jack.craig.ap...@gmail.com On 01/10/2012 19:14, Jack Craig wrote: Ok, A further install question; on my webserver that has no oracle, it seems i need oracle instant client for oracle 9.2, but i am only seeing options for 10.2 or later. that is ok. Some clients only work with some servers. trying to 'perl Makefile.PL', i get the error about LD_LIBRARY_PATH not having ORACLE_HOME, so i added the path as will be true on host B, ... You didn't mention the platform. On Linux set LD_LIBRARY_PATH NOT ORACLE_HOME and rerun perl Makefile.PL. any pointers how to get this install accomplished on my webserver? will instantclient 10.2 work on 9.2? I believe so. TIA, jackc... Martin
Re: Help please with DBD::ODBC on SUSE-Linux
On 28/09/2012 23:54, Jeff Tate wrote: I have got back to the task. I have installed an openSUSE virtual machine (matches the production server) and then installed Teradata GSS, ICU, CLIV2 and ODBC -packages. I have verified that the drivers function by installing the Teradata navigator and successfully getting data over ODBC. This time, when I run cpan test DBD::ODBC, I get an undefined symbol error (SQLFetch) from dynaloader trying to load ODBC.so. I have attached the full build and test trace from the cpan session. Thanx for any help. From: Martin J. Evans [mailto:martin.ev...@easysoft.com] Sent: Wednesday, August 15, 2012 1:33 PM To: dbi-users@perl.org Subject: Re: Help please with DBD::ODBC on SUSE-Linux On 15/08/2012 18:25, Jeff Tate wrote: Step 1) added ODBC trace information to odbcinst.ini (made my own copy (system file)) and change $ODBCINST to address NO output produced in trace file That suggests you are NOT using unixODBC as the ODBC driver manager. Therefore this increases the places where the problem may be as I've not tried whatever driver manager comes with teradata. Martin The make did not find unixODBC or iodbc via odbc_config or iodbc_config. It then looked in standard places for it and finally in your ODBCHOME /opt/teradata/client/ODBC_64. It seemed to find iODBC libraries and headers in ODBCHOME and built against it but there was no SQLFetch symbol. As I said before, I've not tried DBD::ODBC against an iODBC that comes with Teradata. However, I have had problems with missing symbols in iODBC before. I'd install the unixODBC dev and bin packages, unset ODBCHOME and run perl Makefile.PL -x. That should build fine then you need to add your driver to the /etc/odbcinst.ini and put a DSN in the /etc/odbc.ini. Unfortunately I cannot be of much help as to what settings teradata needs in the DSN. Basically, you need to find your teradata ODBC driver shared object and add to your odbcinst.ini file something like: [ODBC] Trace=yes TraceFile=/tmp/unixodbc.log [teradata] Description = Teradata ODBC driver Driver = /path/to/teradata/odbc/driver/shared_object.so Your /etc/odbc.ini should look something like: [my_teradata_dsn] Driver = teradata some_attribute = some_value another_attribute = another_value Then you should use isql -v my_teradata_dsn username password Once that works build DBD::ODBC setting DBI_DSN=dbi:ODBC:my_terdata_dsn, DBI_USER=username, DBI_PASS=password and run a make test. Martin
Re: DBI Issue With iSeries AS400 Returning Null Columns on 64-bit Linux
On 06/09/12 18:32, Keith Carangelo wrote: Hello, We connect to an iSeries AS400 using DBI and DBD::ODBC on a RHEL6 64-bit web server. Since upgrading to a 64-bit server, we've had trouble with outer joins that return NULLs (when there is no corresponding record on the joined table). select a.PRSK01, b.PSTA15 from TESTHA.POLMAST a left join TESTHA.P15POLDP b on a.POLC01 = b.PPOL16 where POLC01 = 3950136 This statement generates a long truncated DBI attribute LongTruncOk not set and/or LongReadLen too small) error. A DBI trace shows that the datalen of column #2 is being set to 429467295 (0x) instead of -1: bind_columns fbh=ba55a0 fields=2 Bind 1: type = CHAR(1), buf=c35940, buflen=4 Bind 2: type = CHAR(1), buf=c35944, buflen=3 bind_columns=0 SQLFetch=0 fetch num_fields=2 fetch col#1 PRSK01 datalen=1 displ=4 '2'(1) fetch col#2 PSTA15 datalen=4294967295 displ=3 !!dbd_error2(err_rc=-999, what=st_fetch/SQLFetch (long truncated DBI attribute LongTruncOk not set and/or LongReadLen too small), handles=(bdfef0,be0600,c4e040) !SQLError(bdfef0,be0600,c4e040) = (HY000, 1, st_fetch/SQLFetch (long truncated DBI attribute LongTruncOk not set and/or LongReadLen too small)) I can work around the error in ODBC::DBD by setting the datalen to -1 in dbdimp.c, but I'm hoping there is a better solution. if (DBIc_TRACE(imp_sth, DBD_TRACING, 0, 4)) PerlIO_printf( DBIc_LOGPIO(imp_dbh), fetch col#%d %s datalen=%ld displ=%lu\n, i+1, fbh-ColName, (long)fbh-datalen, (unsigned long)fbh-ColDisplaySize); if (fbh-datalen == 0x) { fbh-datalen = -1; } if (fbh-datalen == SQL_NULL_DATA) {/* NULL value*/ SvOK_off(sv); continue; } This error does not occur using isql, which is part of unixOBDC. Any hints to how I can better fix this issue? Thanks, Keith Carangelo How recent are your DBI and DBD::ODBC? If you can get them up to date with the recent releases could then send me a trace of a simple script illustrating your problem. You'd do that like this: export DBI_TRACE=DBD=x.log perl myscript.pl and if you are using bash you can do that all in one go. It might also be worth rt'ing it at rt.cpan.org. I'm not sure it is a bug in DBD::ODBC as yet but I would not rule it out. Martin -- Martin J. Evans Easysoft Limited http://www.easysoft.com