Re: DBD error

[Martin J. Evans]

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

[Martin J. Evans]

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

[Martin J. Evans]

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

[Martin J. Evans]

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

[Martin J. Evans]

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->{db

Re: debugging and stepping into ->do

[Martin J. Evans]

On 25/04/18 16:13, Jeff Macdonald wrote:

Hi,

When using DBD::mysql, I can step into the prepare and execute methods of DBD, 
but I can't step into do (execution is the next code line after the do 
statement). 'do' is defined in DBI.pm this way:

 sub do {
my($dbh, $statement, $attr, @params) = @_;
my $sth = $dbh->prepare($statement, $attr) or return undef;
$sth->execute(@params) or return undef;
my $rows = $sth->rows;
($rows == 0) ? "0E0" : $rows;
 }

Based on this, if I were to put a breakpoint on execute, I should be able to 
see 'do' statements via execute, but I don't. For example:

$dbh->do('insert into foo (a, b) values (1,2)')

should cause the execute method to be called, but that does not seem to be the 
case. Is 'do' in DBI/DBD calling some magic method?
--
Jeff Macdonald
Ayer, MA


do may be implemented by the driver in XS (c code) in which case you would not 
be able to step into it. Drivers can override the DBI methods.

Martin

Re: DBD::ODBC and issues getting LongTruncOk / LongReadLen to take effect

[Martin J. Evans]

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

[Martin J. Evans]

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

[Martin J. Evans]

On 10/10/17 00:07, Bruce  Johnson wrote:



On Oct 9, 2017, at 3:52 PM, John R Pierce  wrote:

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

[Martin J. Evans]

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" 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

[Martin J. Evans]

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

new DBD::ODBC released

[Martin J. Evans]

I have uploaded DBD::ODBC 1.56 to the CPAN (1.54 was skipped due to an indexing 
problem).

Here are the changes since the 1.52:

1.53_2 2016-02-03

  [MISCELLANEOUS]

  Add new FAQs

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: DBD for SQL Server

[Martin J. Evans]

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

[Martin J. Evans]

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)

[Martin J. Evans]

 --- 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: Extra nulls inserted with binary data (DBD::ODBC on Win7)

[Martin J. Evans]

On 27/01/16 22:10, Jerrad Pierce wrote:

Hello all, I'm updating a LONGBINARY column in a Jet 4 (MDB) database on 
Windows 7 with
DBD::ODBC and have encountered an odd issue. Rather than the common problem of 
Unicode
data being treated as bytes, I have bytes that seem to be being treated as 
UCS-2LE.

Data that should look like this in hex:
0x3000400210004e04051482240724

Ends up end the database like this: 
0x0300040021000400E0004000500041002800420070004200

i.e; there is a null byte inserted between each legitimate byte of data.
How can I ensure the integrity of my inserted data?

DBI tracing with SQL|odbcconnection|2|odbcunicode emits the following for a 
column update:

DBI::db=HASH(0x24d0eb8) trace level set to 0x6000100/2 (DBI @ 0x0/0) in DBI 1.634-ithread (pid 10652) -> 
prepare for DBD::ODBC::db (DBI::db=HASH(0x24d0f90)~0x24d0eb8 'Update Room Set FloorUValue=? WHERE Number=?') 
thr#519fb8 SQLPrepare Update Room Set FloorUValue=? WHERE Number=? Processing non-utf8 sql in unicode mode <- 
prepare= ( DBI::st=HASH(0x24d35b8) ) [1 items] at db_ODBC.pl line 90 -> execute for DBD::ODBC::st 
(DBI::st=HASH(0x24d35b8)~0x24d45c8 '. α@..PA..(B..pB' 1) thr#519fb8 <- execute= ( 1 ) [1 
items] at db_ODBC.pl line 91 -> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x24d45c8)~INNER) thr#519fb8 <- 
DESTROY= ( undef ) [1 items] at db_ODBC.pl line 70 
3000400210004e04051482240724! -> DESTROY for DBD::ODBC::db 
(DBI::db=HASH(0x24d0eb8)~INNER) thr#519fb8 SQLDisconnect=0 ! <- DESTROY= ( undef ) [1 items] during global 
destruction And DBI::data_string_desc returns "UTF8 off, non-ASCII". Any assistance would be much
appreciated. (A similar inquiry has been posted at 
http://perlmonks.org/?node_id=1153796)

--
Jerrad Pierce, Research Assistant 2, NMR Group
617-284-6230x21



Hello Jerrad,

To help identify the problem could you please provide:

DBI version
DBD::ODBC version

Assuming you have recent DBI and DBD::ODBC could you set DBI_TRACE=DBD=x.log 
then rerun your code e.g.,

set DBI_TRACE=DBD=x.log
perl mycode.pl

It should produce DBD::ODBC specific output in x.log that will help me identity 
the problem. There is no need to send the log to the list.

If this produces nothing in x.log you've probably got an older DBI in which 
case use:

set DBI_TRACE=15=x.log
perl mycode.pl

It would also be useful to know the column type in your access DB.

Thanks

Martin

Re: Data sources

[Martin J. Evans]

On 02/11/15 14:47, Ismail Chamseddine wrote:

SQL server v6.01.7601. is installed . version of ODBC is 6.01.7601
When I click configure, it says that MS access driver is not installed.


Then I'm afraid you need to to fix that problem first.

If you have a 64bit perl I've no idea where you get the MS Access driver from.

Be careful, as there are 2 ODBC driver managers, one for 32 bit apps and one 
for 64 bit apps. You need to use the one that is the same as your Perl.

Martin

Re: error message

[Martin J. Evans]

On 02/11/15 09:56, Ismail Chamseddine wrote:

I did and it didn't work. for more information, the error message is the 
following:
failed: [Microsoft][ODBC driver manager]  data source name not found and no 
default driver specified (SQL - IM002) at line..


Then what drivers are listed in your ODBC driver manager?

Are you using a 64 bit perl and don't have a 64 bit Access driver for instance?

Martin

Re: MS access

[Martin J. Evans]

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 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

[Martin J. Evans]

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

[Martin J. Evans]

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

[Martin J. Evans]

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

[Martin J. Evans]

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: help with odd DBI perpare/execute errors

[Martin J. Evans]

On 03/06/15 17:12, Bruce Johnson wrote:



On Jun 3, 2015, at 7:19 AM, William Bulley  wrote:

According to Bruce  Johnson  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

[Martin J. Evans]

On 03/06/15 14:38, William Bulley wrote:

According to "Martin J. Evans"  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

[Martin J. Evans]

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: Issue with DBD::ODBC (SQLServer) and bcp

[Martin J. Evans]

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

[Martin J. Evans]

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

[Martin J. Evans]

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

[Martin J. Evans]

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?

[Martin J. Evans]

 ok. I don't know if Tim could comment on this.

So, in summary, I cannot reproduce your error, I've seen freeTDS error and fail 
to tell us the error before, your code works flawlessly with the Easysoft and 
MS ODBC drivers and your ODBC log contradicts the DBI/DBD::ODBC log so I'm 
going to suggest you've updated or changed your freeTDS driver and this one is 
broken.

Martin



On Tue, Mar 31, 2015 at 4:23 AM, Martin J. Evans mailto:martin.ev...@easysoft.com>> wrote:

On 31/03/15 06:04, Joel Plotkin wrote:

Hi,

I've attached the sample test8.pl <http://test8.pl> <http://test8.pl> 
script (smallest one possible that creates the problem) and a trace file at level 15.

Thanks for any insight,

Joel


 -dbd_st_execute(ac3cb0)=-1
 <- execute= -1 at test8.pl <http://test8.pl> line 74 via  at test8.pl 
<http://test8.pl> line 53
 -> DESTROY for DBD::ODBC::st (DBI::st=HASH(0xac3818)~INNER) thr#974010
 SQLFreeHandle(stmt)=-1
 !!dbd_error2(err_rc=-1, what=st_destroy/SQLFreeHandle(__stmt), 
handles=(c2abd0,c2b1c0,c802c0)
 ** No error found -1 **
 !! ERROR: 1 'Unable to fetch information about the error' (err#1)
 <- DESTROY= undef at test8.pl <http://test8.pl> line 54 via  at test8.pl 
<http://test8.pl> line 54
 !! ERROR: 1 CLEARED by call to fetchall_arrayref method

This is suspicious - SQLFreeHandle failed and then the error was cleared.

I cannot reproduce and we need further clues.

Instead of starting tracing in the script could you rerun with 
DBI_TRACE=DBD=x.log

e.g.,
set DBI_TRACE=DBD=x.log
perl myscript.pl <http://myscript.pl>

This will put in the x.log file a load of ODBC info for the driver etc - 
could you send me the whole log file.

Another thing well worth doing is enabling tracing at the ODBC level as 
then we can try and find out why SQLFreeHandle is failing. You can do this by 
going to the ODBC administrator (make sure you pick the right one 32 bit or 64 
bit depending on what your perl is) and selecting the tracing tab, enter a file 
and click on start then run your script.

Martin

Re: DBD::ODBC Destroy bug?

[Martin J. Evans]

On 31/03/15 06:04, Joel Plotkin wrote:

Hi,

I've attached the sample 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?

[Martin J. Evans]

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?

[Martin J. Evans]

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 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 <ht

Re: DBD::ODBC Destroy bug?

[Martin J. Evans]

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  line 92
 -> execute for DBD::ODBC::st (DBI::st=HASH(0x156c370)~0x16e8898) 
thr#139f010
 <- execute= -1 at 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  line 70
DBD::ODBC::st DESTROY failed: Unable to fetch information about the error at 
test12.pl  line 70.
This is a test 3
  at test12.pl  line 70.
main::joel_init() called at 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

[Martin J. Evans]

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
   



Table:  People, with fields
   PeopleID INT AUTO_INCREMENT
   HouseID INT  (shows which house the person belongs to)
   Name VARCHAR(30)
   

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

[Martin J. Evans]

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

[Martin J. Evans]

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 ).

-- 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

Re: foreign_key_info() nits

[Martin J. Evans]

On 16/11/14 23:59, Kurt Starsinic wrote:

Hi all,

I've been using DBI with DBD::Sybase and SQL Server, and discovered a
couple of issues. I will be happy to offer a patch if I can get some
consensus on what the correct resolutions are:

The DBI docs say that foreign_key_info() can take from 3 to 7
arguments, but the code demands 6 or 7 (even though many of the
arguments can be undef). My opinion is that the docs are good and the
code is broken. Any objections on this point?


The docs say:

  $sth = $dbh->foreign_key_info( $pk_catalog, $pk_schema, $pk_table
   , $fk_catalog, $fk_schema, $fk_table );

Perhaps you are referring to "If both PKT and FKT are given" or the examples:

  $sth = $dbh->foreign_key_info( undef, $user, 'master');
  $sth = $dbh->foreign_key_info( undef, undef,   undef , undef, $user, 
'detail');
  $sth = $dbh->foreign_key_info( undef, $user, 'master', undef, $user, 
'detail');

The addition of \%attr on the end of foreign_key_info is probably what is 
dictating the minimum of 6 so I think the example with 3 arguments is probably 
wrong. Also the ODBC API for SQLForeignKeys takes the same 6 arguments.


Also, at least with SQL Server, foreign_key_info() returns columns
named [FP]KTABLE_OWNER and [FP]KTABLE_QUALIFIER instead of
[FP]KTABLE_SCHEMA and [FP]KTABLE_CAT. I think the driver should
rewrite the column names. Does anybody disagree?


They are the old ODBC 2 names for the columns. You can certainly get those with 
DBD::ODBC with very old drivers. The ODBC docs say:

"The following columns have been renamed for ODBC 3.x. The column name changes do 
not affect backward compatibility because applications bind by column number."

As the DBD::ODBC maintainer I would prefer not to have to map ODBC 2 names to 3 
- it would set a precedent that would lead to many other changes.



Thanks, Kurt Starsinic



Martin

New 1.50 release of DBD::ODBC

[Martin J. Evans]

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

[Martin J. Evans]

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

[Martin J. Evans]

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 mailto:ranji...@infosys.com>> -

 >

 > Date: Wed, 4 Jun 2014 11:05:34 +

 > From: Ranjitha mailto:ranji...@infosys.com>>

 > To: "tim.bu...@pobox.com <mailto:tim.bu...@pobox.com>" mailto:tim.bu...@pobox.com>>, "t...@cpan.org <mailto: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: (Fwd) Issue :DBD::Oracle installation

[Martin J. Evans]

On 04/06/14 12:44, tim.bu...@pobox.com wrote:

- Forwarded message from Ranjitha  -

Date: Wed, 4 Jun 2014 11:05:34 +
From: Ranjitha 
To: "tim.bu...@pobox.com" , "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

[Martin J. Evans]

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

[Martin J. Evans]

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

[Martin J. Evans]

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

New DBD::ODBC 1.47 release

[Martin J. Evans]

I have just uploaded DBD::ODBC 1.47 to the CPAN.

This release does contain significant changes in behaviour for unicode builds 
of DBD::ODBC so I hope when I warned you months ago you tested it.

Thanks to everyone on the dbi-dev list and irc that helped me work my way 
through the unicode issue.

I'm now going to sit back and wait for the usual rush of reports from everyone 
who didn't test it.

See below for changes since last full release.

Martin

1.47 2014-02-19

  Full release of the 1.46 development releases.

  [MISCELLANEOUS]

  Just some tidying up of dbdimp.c - shouldn't make a difference to anyone.

  Further changes to this change file to make it CPAN::Changes spec.
  NOTE the changes.cpanhq.com site does not yet support "unknown" for
  dates.

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

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.

Re: I have a weird issue with a script running under cron

[Martin J. Evans]

On 17/02/14 18:50, Bruce Johnson wrote:


On Feb 17, 2014, at 11:41 AM, Bruce Ferrell  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

[Martin J. Evans]

On 05/02/14 14:08, David Nicol wrote:

On Wed, Feb 5, 2014 at 7:25 AM, Martin J. Evans
 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: Surprising DBD::Oracle error raised

[Martin J. Evans]

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

[Martin J. Evans]

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: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)

[Martin J. Evans]

On 31/01/14 16:21, Tim Bunce wrote:

On Fri, Jan 31, 2014 at 09:11:28AM -0500, John Scoles wrote:

 A final note on this.

Seems there was a very very long unknown bug in DBI which was only fix a 
few days ago wiht DB 1.6.31


If you mean Callbacks getting an inner handle, that wasn't a bug as such.
More like a design choice that proved non-optimal.



[1]http://blogs.perl.org/mt/mt.fcgi?__mode=view&_type=entry&id=5570&blog_id=2165


That's http://blogs.perl.org/users/byterock/2014/01/callbacks-ate-my-brain.html
I presume.


The end result of this bug was that when callbacks are used on the
statement handle some attributes will not be there so you
programmer who did this

$sth->FETCH( 'ParamValues' ), # WTF? - returns a reference to an array of 
hashes

was most likely complaing that the

$sth->{ParamValues},

should return a ref but was just returning undef.

So he 'Kludged' the code to get the value directly with the FETCH which 
works


I'm not sure what you're saying here John. Using $sth->FETCH('ParamValues')
is perfectly reasonable. It was required before 1.631 and optional with
1.631+ now that $h->{ParamValues} works.


sort of, but it does bleed memory every so slighly.


Are you sure? This is the first I've heard of such a leak.

Tim.


I've found no evidence of a memory leak with a simple test calling ParamValues 
a lot with some parameters. However, I'm not using ORA_VARCHAR2_TABLE. The code 
is:

else if (kl==11 && strEQ(key, "ParamValues")) {
HV *pvhv = newHV();
if (imp_sth->all_params_hv) {
SV *sv;
char *key;
I32 keylen;
hv_iterinit(imp_sth->all_params_hv);
while ( (sv = hv_iternextsv(imp_sth->all_params_hv, &key, 
&keylen)) ) {
phs_t *phs = (phs_t*)(void*)SvPVX(sv); /* 
placeholder struct   */
(void)hv_store(pvhv, key, keylen, 
newSVsv(phs->sv), 0);
}
}
retsv = newRV_noinc((SV*)pvhv);
cacheit = FALSE;

}

which looks sane to me right now. ORA_VARCHAR2_TABLE seems to do strange things 
with parameters I don't quite get right now.

As I said previously to Hélder and John (some of the discussion was off 
dbi-users list presumably because it contained log data), although I accept 
taking the call to ParamValues out has on this occasion made the problem go 
away I don't understand why. I think there is more to this than it so far looks 
but without a way of reproducing it myself I won't be spending any more time on 
it. If it is reproducible in a standalone script I will happily look again.

Martin

Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)

[Martin J. Evans]

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.




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)

[Martin J. Evans]

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 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 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> &l

Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)

[Martin J. Evans]

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 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 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 

Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)

[Martin J. Evans]

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 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 
 > To: hhferre...@gmail.com ; 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:
 > >
 > 
 >
 > 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 

Re: Issues with DBI Oracle Input Array Binds (ORA_VARCHAR2_TABLE)

[Martin J. Evans]

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:




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)

[Martin J. Evans]

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

Re: Table-Valued Parameters to MS SQL Server stored procedure

[Martin J. Evans]

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

[Martin J. Evans]

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

[Martin J. Evans]

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

[Martin J. Evans]

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

[Martin J. Evans]

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

[Martin J. Evans]

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"

[Martin J. Evans]

On 06/11/13 14:36, Jan Holčapek wrote:

Hi Martin,

On Wed, Nov 6, 2013 at 3:05 PM, Martin J. Evans  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: DBD::ODBC fails after "Execute immediate success with info"

[Martin J. Evans]

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'

Re: DBD::ODBC fails after "Execute immediate success with info"

[Martin J. Evans]

n issue and I refuse to tell you what it 
is (a bug either in the driver or DBD::ODBC).

In the first case the driver said it succeeded but then against the ODBC API, 
would not tell us the informational part. DBD::ODBC posted an error saying this.

In the second case the same happened but the driver told us it succeeded but it 
did not really drop 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: (Fwd) Problems with DBD::Oracle package: function plsql_errstr

[Martin J. Evans]

Thank you for providing this - please see below.

On 03/11/2013 12:06, Tim Bunce wrote:

- Forwarded message from "Eljot na Onet.pl"  -

Date: Sat, 02 Nov 2013 21:11:48 +0100
From: "Eljot na Onet.pl" 
To: t...@cpan.org
Subject: Problems with DBD::Oracle package: function plsql_errstr

Hi,

I have found two problems with your DBD::Oracle package.

(you have all examples in the attachment plsql_errstr.zip, please read
README.txt file)


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

[Martin J. Evans]

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 sho

Re: (Fwd) Can't use an undefined value as an ARRAY reference at /usr/local/lib/perl/5.14.2/DBI.pm

[Martin J. Evans]

On 28/09/2013 12:17, Tim Bunce wrote:

- Forwarded message from Yonatan Sisay  -

Date: Sat, 28 Sep 2013 01:25:04 -0700
From: Yonatan Sisay 
To: tim.bu...@pobox.com
Subject:

Greetings,
I have been having an issue with DBI. I installed a web system called KOHA, 
and in the middle of it all,
this library started having some issues. Here is how it shows the error.
Can't use an undefined value as an ARRAY reference at 
/usr/local/lib/perl/5.14.2/DBI.pm
I have tried re-installing the library and it doesn't seem to fix that. 
Could it be another library that
is causing the issue?
Thank you for your time, and I will be waiting for your reply.
--
Yonatan Sisay Gebregiorgis
CloudX Support and Development Team
[1]supp...@cloudxdpo.com
[2]www.cloudxdpo.com
[3]Inline image 1

References

Visible links
1. mailto:supp...@cloudxdpo.com
2. http://www.cloudxdpo.com/



- End forwarded message -



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

[Martin J. Evans]

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 
*To:* Nagendra Singh 
*Cc:* "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 mailto:boh...@ntlworld.com>>
 > *To:* Nagendra Singh mailto:nagsu...@yahoo.com>>
 > *Cc:* Michael Nhan mailto:mn...@genome.wustl.edu>>; Manimegalai Visvanathan
 > mailto:mvisvanat...@wsgc.com>>;
"'dbi-users@perl.org <mailto: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 chara

Re: DBD::Oracle installation on AIX

[Martin J. Evans]

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 
*To:* Nagendra Singh 
*Cc:* Michael Nhan ; Manimegalai Visvanathan
; "'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

[Martin J. Evans]

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?

[Martin J. Evans]

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: Trouble with DBI - bug or rewrite?

[Martin J. Evans]

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: DBD::Oracle installation on AIX

[Martin J. Evans]

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

[Martin J. Evans]

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

[Martin J. Evans]

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 d

Re: odbc_lob_read chunk is short

[Martin J. Evans]

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

[Martin J. Evans]

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: Error running make command for DBI1.51

[Martin J. Evans]

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?

[Martin J. Evans]

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?

[Martin J. Evans]

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 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

Re: ODBC Driver failing?

[Martin J. Evans]

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 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
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 "

Re: ODBC Driver failing?

[Martin J. Evans]

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 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

Re: ODBC Driver failing?

[Martin J. Evans]

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
mailto:jonathan.leff...@gmail.com>> wrote:




On Wed, Jun 26, 2013 at 9:28 AM, Dan Bent 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 mailto:jonathan.leff...@gmail.com>>  #include 
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?

[Martin J. Evans]

ni 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: (Fwd) Quick Perl Questions - DBI and DBD

[Martin J. Evans]

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

[Martin J. Evans]

On 24/05/13 06:51, Gabor Szabo wrote:

Hi,

On Wed, May 22, 2013 at 10:08 PM, Martin J. Evans  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 

Re: DBI article in Korean

[Martin J. Evans]

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

[Martin J. Evans]

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

[Martin J. Evans]

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  -

Date: Tue, 5 Mar 2013 00:45:26 -0800
From: Mahdi Sbeih 
To: "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

[Martin J. Evans]

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: Problems with dbi:Oracle

[Martin J. Evans]

On 24/01/13 19:03, Bruce Johnson wrote:


On Jan 21, 2013, at 3:08 PM, Alexander Foken 
wrote:


You seem to run from a CGI environment. Look at the http server
error log for details of the error.

Note that Oracle may depend on some environment variables that are
NOT available by default when running in the context of the http
server.

Try to debug with a simple command line script instead of a CGI
first, then blame DBI or DBD::Oracle.



What Alexander said.

Any script that uses Oracle via DBI must have at least the
$ORACLE_HOME and $LD_LIBRARY_PATH environment vars available.


That is no longer strictly true. If you use instant client you do not need 
ORACLE_HOME set and in fact, if you do set it with some older versions of 
instant client it causes problems (I had to change Makefile.PL a few years back 
to fix this as it was stopping sqlplus from running properly so the Makefile.PL 
fell back on assuming I had Oracle 8 - which I did not).
 
If you are using a full Oracle install then you should definitely set ORACLE_HOME and recently this has got harder because a basic oracle installation does not include all the files DBD::Oracle requires to build.



In Apache you can pass specified environment vars from the user who
starts the Apache httpd processes in a directive, SetEnv:

#PassEnv PATH SetEnv LD_LIBRARY_PATH
/usr/lib/oracle/11.2/client64/lib SetEnv ORACLE_HOME
/usr/lib/oracle/11.2/client64

This is for linux-based Oracle Instant Client.



That's a newer instant client which does not seem to mind ORACLE_HOME being set 
but it is not necessary.

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

Re: Possible database handle leak in DBI?

[Martin J. Evans]

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

[Martin J. Evans]

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 
To: Tim Bunce , Yanick Champoux , 
"Martin J. Evans" 


Hi all,
   I am running into a problem and was planning on modifying my local
DBD::Oracle to add support for a "schema=" 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

[Martin J. Evans]

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 
To: Tim Bunce , Yanick Champoux ,
"Martin J. Evans" 

Hi all,
   I am running into a problem and was planning on modifying my local
DBD::Oracle to add support for a "schema=" 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

[Martin J. Evans]

e failing GNU/Hurd tests dist/IO/t/io_pipe.t
DEBPKG:fixes/manpage_name_CPAN - http://bugs.debian.org/650448 
[rt.cpan.org #73396] cpan/CPAN: add NAME headings in modules with POD
DEBPKG:fixes/manpage_name_CPANPLUS - http://bugs.debian.org/650450 
[rt.cpan.org #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"  
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"  
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

[Martin J. Evans]

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



 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"  
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"  
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

[Martin J. Evans]

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"  
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"  
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