Re: Oracle ncahr versus nvarchar2 and unicode data

[martin . evans]

Please ignore the bind_param/execute part of my email - I was being an
idiot. Would still like opinions on the rest of it though.

Martin

Quoting "Martin J. Evans" <[EMAIL PROTECTED]>:

> Hi,
> 
> Wondered if anyone can tell me if I've misunderstood something here. I am
> able
> to insert unicode data into nvarchar2 and get back what was inserted but
> same
> code with nchar column does not work.
> 
> OCI version is 10.1.0.2
> Database is Oracle 10.2 on Linux
> Database  CHAR set is WE8ISO8859P1 (Non-Unicode), NCHAR set is AL16UTF16
> (Unicode)
> This is perl, v5.8.7 built for i686-linux
> DBD::Oracle 1.16.
> 
> #!/disk2/martin/perl/bin/perl
> use charnames ':full';
> use DBI;
> use Encode;
> use DBD::Oracle qw( :ora_types ORA_OCI SQLCS_NCHAR );
> use Devel::Peek;
> 
> if ( $] < 5.008) {
> print "Need at least Perl 5.8\n";
> exit 1;
> }
> binmode(STDOUT, ":utf8");
> 
> print "";
> print q||;
> print "test\n";
> 
> $ENV{'ORACLE_HOME'} = '/home/oracle/product/10.1.0/db_2';
> $ENV{'NLS_NCHAR'} = 'AL32UTF8';
> my $dbh = DBI->connect('dbi:Oracle:tentwo.testing', 'xxx', 'yyy');
> $dbh->{ChopBlanks} = 1;
> print "OCI version is ", ORA_OCI, "\n";
> print "ora_can_unicode = ", $dbh->ora_can_unicode, "\n";
> if (!($dbh->ora_can_unicode & 1)) {
> print "Database does not support UNICODE\n";
> exit 1;
> }
> my $paramsH = $dbh->ora_nls_parameters();
> printf "Database $ora_server_version CHAR set is %s (%s), NCHAR set is %s
> (%s)\n",
> $paramsH->{NLS_CHARACTERSET}, 
> $dbh->ora_can_unicode & 2 ? "Unicode" : "Non-Unicode",
> $paramsH->{NLS_NCHAR_CHARACTERSET},
> $dbh->ora_can_unicode & 1 ? "Unicode" : "Non-Unicode";
> eval {
> $dbh->do('drop table martin');
> };
> # NOTE nchar does not work (why?)
> $dbh->do('create table martin (a nvarchar2(256))');
> my $sql = "insert into martin values (?)";
> my $x = "\N{EURO SIGN}\x{20ac}\N{LATIN CAPITAL LETTER A WITH ACUTE}";
> $x .= chr(0x20ac);
> print "is_utf8 returns ", utf8::is_utf8($x), "\n";
> print "valid returns ", utf8::valid($x), "\n";
> my $sth = $dbh->prepare($sql);
> $sth->bind_param(1, $x, {ora_csform => SQLCS_NCHAR});
> $sth->execute();
> $sql = "select a from martin";
> $sth = $dbh->prepare($sql);
> $sth->execute;
> while(my @row = $sth->fetchrow_array) {
> print "valid on returned string returns ", utf8::valid($row[0]), "\n";
> #$_ = decode("utf8", $_) foreach @row;
> print $row[0], "\n";
> print unpack("H*", $row[0]), "\n";
> print Dump(@row[0]);
> }
> $sth->finish;
> $dbh->disconnect();
> 
> Output is:
> 
>  content="text/html;charset=utf-8">test
> OCI version is 10.1.0.2
> ora_can_unicode = 1
> Database  CHAR set is WE8ISO8859P1 (Non-Unicode), NCHAR set is AL16UTF16
> (Unicode)
> is_utf8 returns 1
> valid returns 1
> valid on returned string returns 1
> 
> e282ace282acc381e282ac
> SV = PV(0x82a3efc) at 0x82e5b48
>   REFCNT = 1
>   FLAGS = (POK,pPOK,UTF8)
>   PV = 0x8361bb0 "\342\202\254\342\202\254\303\201\342\202\254"\0 [UTF8
> "\x{20ac}\x{20ac}\x{c1}\x{20ac}"]
>   CUR = 11
>   LEN = 12
> 
> 
> UTF-8 data output with Devel::Peek Dump looks fine.
>   
> A simple change to create the table with nchar instead of nvarchar2
> outputs:
> 
>  content="text/html;charset=utf-8">test
> OCI version is 10.1.0.2
> ora_can_unicode = 1
> Database  CHAR set is WE8ISO8859P1 (Non-Unicode), NCHAR set is AL16UTF16
> (Unicode)
> is_utf8 returns 1
> valid returns 1
> valid on returned string returns 1
> 
> 3f3f413f
> SV = PV(0x82a3efc) at 0x82e5c3c
>   REFCNT = 1
>   FLAGS = (POK,pPOK,UTF8)
>   PV = 0x8361cc0 "??A?"\0 [UTF8 "??A?"]
>   CUR = 4
>   LEN = 5
> 
> 
> Devel::Peek Dump output looks nothing like what I expected.
> 
> Also, do I have to use {ora_csform => SQLCS_NCHAR}? Does not seem to work in
> either case if I don't.
> 
> And one final thing. If I change $x containing the UTF-8 data to insert and
> run
> $sth->execute again it inserts the original data again and not the changed
> $x.
> I've not confirmed this yet with other drivers, but I am /fairly/ sure with
> DBD::ODBC you don't have to call bind_param again if the bound data is
> changed.
> 
> Thanks.
> 
> Martin
> --
> Martin J. Evans
> Easysoft Ltd, UK
> Development
> 
> 
> 

Re: Not able to connect to Mysql Database using ODBC

[Martin Evans]

Narayana, Sriman Export License Required - US UTCHQ wrote:

Hi,

  I am new to perl programming. Installed mysql,perl,mysql odbc drivers
in my machine. Following is my code, its not working. I googled it but
no use :(


I have not used MySQL on Windows myself but the normal way to access 
MySQL from Perl is using DBD::mysql - perhaps that is not available for 
Windows. If you need to use ODBC then there are two DBI drivers to 
choose from DBD::ODBC and DBD::W32ODBC. I've never used DBD:W32ODBC but 
then I've not used Perl and ODBC on Windows much.



use DBI::W32ODBC;
$dbh= DBI->connect("DBI:W32ODBC:TestMyDSN","rootuser","rootuserpwd") ||
  die "Got error $DBI::errstr when connecting to $dsn\n";

  error msg:
Error connecting to DBI:W32ODBC:TestMyDSN;UID=rootuser;PWD=rootuserpwd:
[911] []
 "[Microsoft][ODBC Driver Manager] Data source name not found and no
default dri
ver specified"
Got error  when connecting to


This has not even reached your ODBC driver. The driver manager cannot 
find DSN TestMyDSN.



 is there any thing missing in my machine? Any idea?

> Thanks in advance.
>
> Thanks & Regards,
> Sri
>

Have you created a DSN called TestMyDSN in the ODBC Administrator in 
Control Panel? If you have, is it a user DSN or a system DSN? User DSNs 
are only visible to the user who created them e.g. you log on as Sri, 
create a user DSN then attempt to use it under a web server not running 
as Sri (that won't work, you'd need a system DSN in that case).


There are also File DSN, but I'm assuming you are not using one of those.

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

Re: bind variable problem

[Martin Evans]

Anand.K.S. wrote:

Hi,



This problem has been killing me for a while …

Script 1 and script 2 (mentioned below) are very much the same. However I
have hard coded the query

in script 2 whereas constructed in script 1.



Also script 1 is implement on an environment  (which uses oracle
version 9and DBI
1.38) and script 2 on another environment  (which uses oracle version 
10gand

)



In script 1, the sql query is executed successfully, but in script 2 it
fails in execute() step..

The bind variable has the following values:



Tables => CHARGE NORMALISED_EVENT SUBTOTAL_RATING_VALUE
SUBTOTAL_RATING_DELTA NORMALISED_EVENT_ERROR



Hard coding bind variables goes trhough fine..  Is this a known bug  or 
am I

doing anything silly here.. Please  could someone help me out?





Script 1:



  $lsql = "SELECT NVL2(partition_name,

   segment_name || ':' || partition_name,

   segment_name)

 FROM user_segments

WHERE segment_type IN ('TABLE', 'TABLE PARTITION') AND

  segment_name NOT IN (";

   }

   # Create the necessary number of bind variable placeholders.

   $lsql .= '?,' x scalar(@ltables);

   # Remove the last ",".

   chop $lsql;

   # Order by BYTES if we are not splitting customer partitions out.

   $lsql .= ") ORDER BY bytes DESC";

   }



   print ("\n SQL => $lsql");

   # Execute the query and build an array of "other" table names.

   my @lotherTables;

   $lcsr = $zdb->prepare($lsql) ||

   zDie("Could not prepare query at line " . __LINE__ . ": " .

$zdb->errstr);

   $lcsr->execute(@ltables) ||

   zDie("Could not execute query at line " . __LINE__ . ": " .

$zdb->errstr);





Script 2

===

  $lsql = "SELECT NVL2(partition_name,

   segment_name || ':' || partition_name,

   segment_name)

 FROM user_segments

WHERE segment_type IN ('TABLE', 'TABLE PARTITION') AND

  segment_name NOT IN

  (SELECT object_name

 FROM recyclebin bin) AND

  segment_name NOT IN (?,?,?,?,?) ORDER BY bytes
DESC";#'CHARGE','NORMALISED_EVENT','SUBTOTAL_RATING_DELTA',

 #'NORMALISED_EVENT_ERROR')
ORDER BY bytes DESC";

   # Create the necessary number of bind variable placeholders.

   #$lsql .= '?,' x scalar(@ltables);

   # Remove the last ",".

   #chop $lsql;

   # Order by BYTES if we are not splitting customer partitions out.

   #if (!$opt_scp) {

   #$lsql .= ") ORDER BY bytes DESC";

   #}

   #else {

   #$lsql .= ")";

   #}

   print ("\n sql => $lsql");

   # Execute the query and build an array of "other" table names.

   my @lotherTables;

   $lcsr = $zdb->prepare($lsql) ||

   zDie("Could not prepare query at line " . __LINE__ . ": " .

$zdb->errstr);

   print ("\nTables => @ltables\n");


You haven't called $lcsr->bind_param for the parmeters!


   $lcsr->execute(@ltables) ||

   zDie("Could not execute query at line " . __LINE__ . ": " .

$zdb->errstr);


Thanks,

Anand.



Can I suggest that in future you should include the error messages you 
see as you will get better assistance that way.


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

most drivers share error variable for sth/dbh handles?

[Martin Evans]

From the DBI pod under "METHODS COMMON TO ALL HANDLES" for "err:

"The DBI resets $h->err to undef before almost all DBI method calls, so 
the value only has a short lifespan. Also, for most drivers, the 
statement handles share the same error variable as the parent database 
handle, so calling a method on one handle may reset the error on the 
related handles."


Given the "most drivers" above I presume some drivers don't share the 
error variable for database and statement handles. Which are these 
drivers? If you don't know of any, perhaps you can tell me how to find 
out whether they do? I did find the following in DBI.pm:


sub _new_drh {  # called by DBDdriver()
my ($class, $initial_attr, $imp_data) = @_;
# Provide default storage for State,Err and Errstr.
# Note that these are shared by all child handles by default! XXX
# State must be undef to get automatic faking in DBI::var::FETCH
my ($h_state_store, $h_err_store, $h_errstr_store) = (undef, 0, '');

The reason I'd like to know is that I have some circumstances where an 
error occurs on a statement handle which goes out of scope immediately
so err is not available. I notice the connection handle (with 
DBD::Oracle) also contains the same error number/string and this would 
be great except for the fact we use multiple DBDs.


Thanks.

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

Re: about dbi building error.

[Martin Evans]

Nancy Ni wrote:

If my unix system currently uses gcc to compile c program,then what
should I do without installing a new sun compiler?can change the make
file for c compile?
Thanks & regards,
Nancy


Firstly, you need to use the same compiler to compile Perl and any 
modules not included with the standard Perl distribution that you 
compile later (e.g. DBI). If you got Perl binaries from elsewhere

(e.g. Sun) they most probably were built with the Forte compiler
in which case you'll have to either a) get the Forte compiler or
b) rebuild Perl with the compiler you have).

You will need to ascertain what compiler was used to compile the Perl
you are using - I believe you can see this if you run Perl -V and
look for cc.

Then when you build DBI you need to ensure the first compiler
found in your path is the same compiler used to compile Perl
i.e. make sure /path/to/gcc (if it is gcc) is first in your PATH
environment variable.

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


-Original Message-
From: Michael Nhan [mailto:[EMAIL PROTECTED] 
Sent: Monday, January 22, 2007 10:52 PM

To: Nancy Ni
Cc: dbi-users@perl.org
Subject: Re: about dbi building error.

Hi,

 	If you choose to use the sun compilers to compile DBI, you must 
install the sun compilers. Sun does not come with the compilers 
preinstalled.   That's why you are getting "/usr/ucb/cc:  language 
optional software package not installed".  Have your sys-admin install
the 
Forte CC compilers for you.


Regards,
Michael



  > Date: Mon, 22 Jan 2007 10:50:24 -

From: Nancy Ni <[EMAIL PROTECTED]>
To: dbi-users@perl.org
Subject: about dbi building error.

Dear Sir./Ms.



My current server perl version is:

This is perl, version 5.005_03 built for sun4-solaris

The Bundle about DBI I using is DBI-1.37.

During building it,there is some error appearing:





sunbilldev% make

/bin/sh -c true

/bin/sh -c true

/bin/sh -c true

cc -c   -xO3 -xdepend-DVERSION=\"1.37\" -DXS_VERSION=\"1.37\"

-KPIC

-I/usr/perl5/5.00503/sun4-solaris/CORE -DDBI_NO_THREADS Perl.c

/usr/ucb/cc:  language optional software package not installed

make: *** [Perl.o] Error 1

sunbilldev% make test TEST_VERBOSE=1

/bin/sh -c true

/bin/sh -c true

/bin/sh -c true

cc -c   -xO3 -xdepend-DVERSION=\"1.37\" -DXS_VERSION=\"1.37\"

-KPIC

-I/usr/perl5/5.00503/sun4-solaris/CORE -DDBI_NO_THREADS Perl.c

/usr/ucb/cc:  language optional software package not installed

make: *** [Perl.o] Error 1



can u tell me what's wrong with it and how to continue to install it
successfully?





Thanks & Regards,



Nancy Ni






---//---
 "He who learns must suffer. And even in our sleep,
  pain that cannot forget, falls drop by drop upon the heart; 
and, in our own despair, against our will,

  comes wisdom to us by the awful grace of God."
--- Aeschylus

Re: bind variable problem

[Martin Evans]

Ronald Kimball wrote:
 


Martin Evans [mailto:[EMAIL PROTECTED] wrote:

You haven't called $lcsr->bind_param for the parmeters!


You don't need to call bind_param() if you pass the parameter values in the call
to execute(), as Anand is doing.


I know that, sorry, I missed that.

Perhaps if Anand told us what the error is.

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

Re: most drivers share error variable for sth/dbh handles?

[Martin Evans]

Thanks Jonathan,

Jonathan Leffler wrote:

DBD::Informix is careful about errors.


I would hope all DBDs are ;-)


Each statement handle keeps a copy of its most recent status/error
information out of the global sqlca variable (plus the sqlstate variable).
Each database handle has a copy of the most recently executed statement's
error/status information.  Of course, this is made more complex by
AutoCommit which requires extra statements to be executed to simulate the
AutoCommit; you have to ignore the status of the extra statements when they
succeed, but record the error if they fail.


So, I think you are saying that if you executed the following with 
DBD::Informix:


my $dbh = DBI->connect({RaiseError=>1});

eval {
   $dbh->begin_work;
   my $sth = $dbh->prepare(q/insert into table values (1)/);
   $sth->execute; # execute fails - say duplicate key error
   $dbh->commit;
};
$dbh->err here would be what $sth->err was above in the eval after the 
execute (assuming you could have looked at $sth->err which you can't in 
this case because RaiseError was set).


Yes?

I'm not actually using Informix (at the moment, Oracle, MySQL, DB2) but 
we have spent a great deal of effort making sure we work with these 3 
databases and don't want to rule out any others.


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


On 1/23/07, Martin Evans <[EMAIL PROTECTED]> wrote:


From the DBI pod under "METHODS COMMON TO ALL HANDLES" for "err:

"The DBI resets $h->err to undef before almost all DBI method calls, so
the value only has a short lifespan. Also, for most drivers, the
statement handles share the same error variable as the parent database
handle, so calling a method on one handle may reset the error on the
related handles."

Given the "most drivers" above I presume some drivers don't share the
error variable for database and statement handles. Which are these
drivers? If you don't know of any, perhaps you can tell me how to find
out whether they do? I did find the following in DBI.pm:

sub _new_drh {  # called by DBDdriver()
 my ($class, $initial_attr, $imp_data) = @_;
 # Provide default storage for State,Err and Errstr.
 # Note that these are shared by all child handles by default! XXX
 # State must be undef to get automatic faking in DBI::var::FETCH
 my ($h_state_store, $h_err_store, $h_errstr_store) = (undef, 0, '');

The reason I'd like to know is that I have some circumstances where an
error occurs on a statement handle which goes out of scope immediately
so err is not available. I notice the connection handle (with
DBD::Oracle) also contains the same error number/string and this would
be great except for the fact we use multiple DBDs.

Thanks.

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

Re: DBD::Oracle installation failed

[Martin Evans]

Pham, Tri wrote:

To DBI support,

 


I am having problem with DBI and DBD when I migrate the OS from AIX
5.1 to AIX 5.3. The migration was put the new version of Perl (5.8.2)
and wipe out the previously built DBI and DBD::Oracle on the Perl 5.6.1.
I was successful download and compile the DBI 1.53 from CPAN website but
I get the errors when I build the DBD::Oracle-1.19. I am appreciated for
your help.

 


Platform: IBM,7028-6C4

OS: AIX 5.3.04

 


[EMAIL PROTECTED]:/workarea/DBI/DBD-Oracle-1.19# make

Skip blib/lib/DBD/Oracle.pm (unchanged)

Skip blib/lib/DBD/mkta.pl (unchanged)

Skip blib/lib/oraperl.ph (unchanged)

Skip blib/arch/auto/DBD/Oracle/dbdimp.h (unchanged)

Skip blib/arch/auto/DBD/Oracle/ocitrace.h (unchanged)

Skip blib/lib/Oraperl.pm (unchanged)

Skip blib/arch/auto/DBD/Oracle/Oracle.h (unchanged)

Skip blib/arch/auto/DBD/Oracle/mk.pm (unchanged)

Skip blib/lib/DBD/Oracle/GetInfo.pm (unchanged)

cc_r -c  -I/apps/oracle/product/9.2.0/rdbms/demo -I.
-I/apps/oracle/product/9.2.0/precomp/public
-I/apps/oracle/product/9.2.0/rdbms/public
-I/apps/oracle/product/9.2.0/rdbms/demo
-I/apps/oracle/product/9.2.0/plsql/public
-I/apps/oracle/product/9.2.0/network/public
-I/usr/opt/perl5/lib/site_perl/5.8.2/aix-thread-multi/auto/DBI
-D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE -qmaxmem=16384
-qnoansialias -DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT -q32
-D_LARGE_FILES -qlonglong -O-DVERSION=\"1.19\"
-DXS_VERSION=\"1.19\"
"-I/usr/opt/perl5/lib/5.8.2/aix-thread-multi/CORE"  -DUTF8_SUPPORT
-DNEW_OCI_INIT -DORA_OCI_VERSION=\"9.2.0.1\" Oracle.c

1506-507 (W) No licenses available. Contact your program supplier to add
additional users. Compilation will proceed shortly.

cc_r -c  -I/apps/oracle/product/9.2.0/rdbms/demo -I.
-I/apps/oracle/product/9.2.0/precomp/public
-I/apps/oracle/product/9.2.0/rdbms/public
-I/apps/oracle/product/9.2.0/rdbms/demo
-I/apps/oracle/product/9.2.0/plsql/public
-I/apps/oracle/product/9.2.0/network/public
-I/usr/opt/perl5/lib/site_perl/5.8.2/aix-thread-multi/auto/DBI
-D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE -qmaxmem=16384
-qnoansialias -DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT -q32
-D_LARGE_FILES -qlonglong -O-DVERSION=\"1.19\"
-DXS_VERSION=\"1.19\"
"-I/usr/opt/perl5/lib/5.8.2/aix-thread-multi/CORE"  -DUTF8_SUPPORT
-DNEW_OCI_INIT -DORA_OCI_VERSION=\"9.2.0.1\" dbdimp.c

1506-507 (W) No licenses available. Contact your program supplier to add
additional users. Compilation will proceed shortly.

"dbdimp.c", line 1982.18: 1506-046 (S) Syntax error.

"dbdimp.c", line 1982.56: 1506-045 (S) Undeclared identifier undef.

make: 1254-004 The error code from the last command is 1.


Line 1982 is:
//check to see if value sv is a null (undef) if it is upgrade it
Your compiler does not like c++ comments.
You can:

1. find the switch to the compiler to allow c++ comments (I believe 
there is one but I can't remember it off the top of my head)


2. delete line 1982 and any other c++ comments or change the comments to 
c style ones e.g.


/* check to see if value sv is a null (undef) if it is upgrade it */

John Scholes, if you are reading: I would change all the comments to c 
style ones if I were you - it is not uncommon for c compilers to baulk 
at c++ comments.



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

Re: CLOB Problem with DBD::ODBC/DBD::ADO for SQL Server

[Martin Evans]

Mickautsch, Alfred wrote:

Hello,

I have a problem with inserting CLOBS into a SQL Server database.
With the attached scripts, we replicate table data schema between different 
databases.
These databases are located on different servers running Oracle, DB2 and SQL 
Server.
We use Activestate perl 5.8.0 on Windows 2000 and XP with DBI 1.52, DBD::Oracle 
1.15 for Oracle, DBD::DB2 0.78 for DB2 and DBD::ODBC 1.13 and/or DBD::ADO 2.95 
for SQL Server.
Replication between Oracle and DB2 works ok.
There is a difference on the SQL Server databases: all fields that are CLOBs on 
Oracle and DB2 are NCLOBs on SQL Server.
When inserting data (ASCII data which comes from a Oracle CLOB) into a NCLOB on 
SQL Server, backslashes at the end of a line seem to be interpreted as escape 
characters and the backslash and the following newline are removed from the 
data.
Can somebody tell me what to do, so that the CLOB data is not modified?
If this problem is off topic please point me to the right list. Thank you.

Servus -- Alfred



As no one else seems to have replied to you, if you can send a schema 
and working Perl script using DBD::ODBC that demonstrates the problem 
I'll take a look.


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

Re: AW: CLOB Problem with DBD::ODBC/DBD::ADO for SQL Server

[Martin Evans]

Mickautsch, Alfred wrote:

-Ursprüngliche Nachricht-
Von: Martin Evans [mailto:[EMAIL PROTECTED]
Gesendet: Montag, 29. Januar 2007 09:43
An: dbi-users@perl.org
Betreff: Re: CLOB Problem with DBD::ODBC/DBD::ADO for SQL Server

[...]
As no one else seems to have replied to you, if you can send a schema 
and working Perl script using DBD::ODBC that demonstrates the problem 
I'll take a look.


Martin

[...]

Thank you for your response Martin,

I have attached a ZIP file with the SQL script for creating the table with the 
NCLOB(ntext) field and a perl script which inserts a text into this table. You 
should edit the perl script to suit your environment.

Thank you for your help.

Servus -- Alfred


When I run this I do appear to getless chrs back than I put in but on 
further investigation you are not putting the right number in.


The first issue is the back slashes in the here document.

my $text = <<_EOF;
hello
_EOF
print length($text), "\n";

prints 8 not 10 because \\ in a here document ends up as \. So half of 
your '' go straight away to '\\'.


I have to admit to not being able to run your code on Windows at the 
moment but from UNIX to SQL Server via various drivers we have the 
following and they all work as expected:


isql -v install_dsn
SQL> insert into test_ntext values ('');
SQL> select * from test_ntext;
|  |

and in Perl:

use DBI;
$h = DBI->connect("dbi:ODBC:XXX","xxx", "yyy");
$s = $h->prepare(q/insert into test_ntext values(?)/);
$f = q//;
$s->execute($f);

isql -v install_dsn
SQL> select * from test_ntext;
| \\ |

returns \\ because Perl itself turned q// to '\\' before passing it 
to the driver.


Also with newlines, they appear to be kept:

use DBI;
$h = DBI->connect("dbi:ODBC:XXX","xxx", "yyy");
$s = $h->prepare(q/insert into test_ntext values(?)/);
$f = <<_EOF;


_EOF
$s->execute($f);
isql -v install_dsn
SQL> select * from test_ntext;
| \\
\\|

NOTE, those newlines above are UNIX new lines (in otherwords line feeds) 
and I note the file you sent had dos line endings CR/LF 
so I'm not sure you are losing the new lines but I am sure your losing 
half of your back slashes down to Perl.


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

Re: AW: CLOB Problem with DBD::ODBC/DBD::ADO for SQL Server

[Martin Evans]

Martin Evans wrote:


Mickautsch, Alfred wrote:

-Ursprüngliche Nachricht-
Von: Martin Evans [mailto:[EMAIL PROTECTED]
Gesendet: Montag, 29. Januar 2007 09:43
An: dbi-users@perl.org
Betreff: Re: CLOB Problem with DBD::ODBC/DBD::ADO for SQL Server

[...]
As no one else seems to have replied to you, if you can send a schema 
and working Perl script using DBD::ODBC that demonstrates the problem 
I'll take a look.


Martin

[...]

Thank you for your response Martin,

I have attached a ZIP file with the SQL script for creating the table 
with the NCLOB(ntext) field and a perl script which inserts a text 
into this table. You should edit the perl script to suit your 
environment.


Thank you for your help.

Servus -- Alfred


When I run this I do appear to getless chrs back than I put in but on 
further investigation you are not putting the right number in.


The first issue is the back slashes in the here document.

my $text = <<_EOF;
hello
_EOF
print length($text), "\n";

prints 8 not 10 because \\ in a here document ends up as \. So half of 
your '' go straight away to '\\'.


I have to admit to not being able to run your code on Windows at the 
moment but from UNIX to SQL Server via various drivers we have the 
following and they all work as expected:


isql -v install_dsn
SQL> insert into test_ntext values ('');
SQL> select * from test_ntext;
|  |

and in Perl:

use DBI;
$h = DBI->connect("dbi:ODBC:XXX","xxx", "yyy");
$s = $h->prepare(q/insert into test_ntext values(?)/);
$f = q//;
$s->execute($f);

isql -v install_dsn
SQL> select * from test_ntext;
| \\ |

returns \\ because Perl itself turned q// to '\\' before passing it 
to the driver.


Also with newlines, they appear to be kept:

use DBI;
$h = DBI->connect("dbi:ODBC:XXX","xxx", "yyy");
$s = $h->prepare(q/insert into test_ntext values(?)/);
$f = <<_EOF;


_EOF
$s->execute($f);
isql -v install_dsn
SQL> select * from test_ntext;
| \\
\\|

NOTE, those newlines above are UNIX new lines (in otherwords line feeds) 
and I note the file you sent had dos line endings CR/LF 
so I'm not sure you are losing the new lines but I am sure your losing 
half of your back slashes down to Perl.


Martin


Sorry, forgot to say. The inserted text is 11 chrs according to 
length in Perl and the returned chr length is 97145. I had 1486 lines 
containing  and got 97145 chrs back so that suggests all I lost was 
every  turned into \\ (i.e. no loss of line feeds).


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

Re: AW: Re: AW: CLOB Problem with DBD::ODBC/DBD::ADO for SQL Server

[Martin Evans]

Mickautsch, Alfred wrote:

-Ursprüngliche Nachricht-
Von: Martin Evans [mailto:[EMAIL PROTECTED] 
Gesendet: Montag, 29. Januar 2007 17:25

An: dbi-users@perl.org
Betreff: [EMAIL PROTECTED] - Bayesian Filter 
detected spam - Re: AW: CLOB Problem with DBD::ODBC/DBD::ADO 
for SQL Server


Martin Evans wrote:

[...]
so I'm not sure you are losing the new lines but I am sure 
your losing 

half of your back slashes down to Perl.

Martin
Sorry, forgot to say. The inserted text is 11 chrs according to 
length in Perl and the returned chr length is 97145. I had 1486 lines 
containing  and got 97145 chrs back so that suggests all 
I lost was 
every  turned into \\ (i.e. no loss of line feeds).



[...]

Sorry for answering so late Martin, but we seem to have a SPAM filter
problem here :(.
Yes, I put the  there because of perl (just for this example,
in the real perl script I use bind_parameter), expecting to find \\ in
the database.
But what I get out of the database is one \ and no newline (or CRLF).


OK, Alfred, I've found a windows box with active state perl but DBI and 
DBD::ODBC are pretty old, DBI=1.37 and DBD::ODBC=1.05. The following 
script works fine (i.e. it inserts 4 , a carriage return and a line 
feed, 4 more \ and a carriage return and a line feed.


use DBI;
$h = DBI->connect("dbi:ODBC:XXX","xxx", "yyy");
$s = $h->prepare(q/insert into test_ntext values(?)/);
$f = <<'_EOF';



_EOF
$s->execute($f);

I can try getting more up to date DBI and DBD::ODBC but in the mean time:

what version of sql server are you using?
what version of the sql server odbc driver are you using?
have you got any settings in the odbc dsn changed from default?

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

Re: AW: AW: Re: AW: CLOB Problem with DBD::ODBC/DBD::ADO for SQL Server

[Martin Evans]

Mickautsch, Alfred wrote:

-Ursprüngliche Nachricht-
Von: Martin Evans [mailto:[EMAIL PROTECTED]
Gesendet: Dienstag, 30. Januar 2007 10:44
An: dbi-users@perl.org
Betreff: Re: AW: Re: AW: CLOB Problem with DBD::ODBC/DBD::ADO for SQL
Server

[...]

what version of sql server are you using?
what version of the sql server odbc driver are you using?
have you got any settings in the odbc dsn changed from default?


[...]
SQL Server: Microsoft SQL Server 2005 Version 9.00.1399.06
ODBC Driver: SQL Native Client Version 2005.90.1399.00
I do not use a dsn, I use a connect string like the following:
'Driver={SQL Native Client};Server=schuler-sql01;Database=coedev03'
There are no other settings in the string.

Servus -- Alfred

--
Alfred Mickautsch


and previously you said:

> When inserting data (ASCII data which comes from a Oracle CLOB) into a
> NCLOB on SQL Server, backslashes at the end of a line seem to be
> interpreted as escape characters and the backslash and the following
> newline are removed from the data.

I'm sorry Alfred but I just can't reproduce exactly what you describe. I 
lose no back slashes (other than those perl would remove if not using a 
single quoted string) and no line feeds.


I have now got script running on Windows xp, active state perl 5.8.8 
build 819, DBI 1.52, DBD::ODBC 1.13. When I run the following script:


use DBI;
$h = DBI->connect("dbi:ODBC:XXX","xxx", "yyy");
$s = $h->prepare(q/insert into test_char values(?)/);
$f = <<_'EOF';








_EOF
$s->execute($f);
print "length=", length($f);
$x = $h->selectrow_arrayref(q/select * from test_char/);
print "length=", length($x->[0]);

I insert 24 chars (16 \ and 8 line feeds) and I get back 24 chrs.

I am unsure why the code does not insert CR\LF as I am 100% sure the 
file itself contains CR\LF.


I have tried with ntext, text and char fields - no difference.

I have run it through a tds spy and can see the line feeds going through 
fine in both directions.


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

Re: CLOB Problem with DBD::ODBC/DBD::ADO for SQL Server

[Martin Evans]

Mickautsch, Alfred wrote:

-Ursprüngliche Nachricht-
Von: Martin Evans [mailto:[EMAIL PROTECTED]
Gesendet: Dienstag, 30. Januar 2007 12:40
An: dbi-users@perl.org
Betreff: Re: AW: AW: Re: AW: CLOB Problem with DBD::ODBC/DBD::ADO for
SQL Server

[...]

I insert 24 chars (16 \ and 8 line feeds) and I get back 24 chrs.

I am unsure why the code does not insert CR\LF as I am 100% sure the 
file itself contains CR\LF.


I have tried with ntext, text and char fields - no difference.

I have run it through a tds spy and can see the line feeds 
going through 
fine in both directions.

[...]

Yes, it is a weird problem. There seems to be a byte count limit under which 
this effect does not occur. It happens with my text example of 102858 bytes. 
With a text of 569 bytes it does not seem to happen. It is very confusing.

Thank you for your efforts.

Servus -- Alfred


As you indicate, the length has something to do with it. At 24000 chrs 
it works and at 48000 chrs you lose each \ followed by a . It 
is of no consolation to you but I have duplicated it with the MS SQL 
Server driver and also demonstrated it works fine with our sql server 
driver. There is a difference. The MS SQL Server driver execs sp_execute 
to do the insert with the literal text whilst our driver calls 
sp_execute with a parameter.


I had thought turning off translation might help but it doesn't.

I am of the opinion this may be a ms sql server odbc driver bug in which 
your options are limited unless you have a support contract or can come 
up with a workaround.


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

Re: How to call DBD::Oracle's dbms_output_get from within a DBIx

[Martin Evans]

Tim Bunce wrote:

On Wed, Feb 21, 2007 at 04:25:54PM +, Martin J. Evans wrote:

Hi,

I have DBIx::Log4perl which is currently connected to a DBD::Oracle. In 
DBIx::Log4perl::st::execute I want to call DBD::Oracle's dbms_get_line 
which is usually (from applications) called like this:


@lines = $dbh->func('dbms_output_get');

As I'm in st::execute I have a $sth and can get hold of a $dbh.


I'd expect this to work:

  $dbh = $sth->FETCH('Database');
  @lines = $dbh->func('dbms_output_get');

Tim.




Thanks Tim, but that does not appear to work. In my 
DBIx::Log4perl::execute method I now have (simplified):


sub execute {
my ($sth, @args) = @_;

my $ret = $sth->SUPER::execute(@args);

my $dbh = $sth->FETCH('Database');
my @d = $dbh->func('dbms_output_get');

return $ret;
}

and I still get

Deep recursion on subroutine "DBD::Oracle::db::dbms_output_get"

dbms_output_get does create a new statement, prepare and execute it and 
it appears when execute is called it ends up back in my 
DBIx::Log4perl::execute method. I don't understand why this happens :-(


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

Re: DBD::Oracle 1.19 install on Fedora Core 5

[Martin Evans]

Chris Howard wrote:


On Wed, 2007-02-21 at 07:57 -0700, Chris Howard wrote:

Having problems with the install of DBD::Oracle 1.19 on
a Fedora Core 5 machine.

DBI 1.53 installed and the tests ran ok

I'm using the Oracle XE from the Oracle download 
oracle-xe-10.2.0.1-1.0.i386.rpm


The error message from 'make test':


PERL_DL_NONLAZY=1 /usr/local/bin/perl "-MExtUtils::Command::MM" "-e"
"test_harness(0, 'blib/lib', 'blib/arch')" t/*.t
t/01baseFailed to load Oracle extension and/or shared
libraries:
install_driver(Oracle) failed: Can't load
'/usr/local/src/DBD-Oracle-1.19/blib/arch/auto/DBD/Oracle/Oracle.so' for
module
DBD::Oracle: 
/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib/libnnz10.so: cannot 
restore segment prot after reloc: Permission denied at 
/usr/local/lib/perl5/5.8.8/i686-linux/DynaLoader.pm line 230.
 at (eval 3) line 3
Compilation failed in require at (eval 3) line 3.
Perhaps a required shared library or dll isn't installed where expected
 at t/01base.t line 19
The remaining tests will probably also fail with the same error.

I tried setting LD_LIBRARY_PATH and LD_RUN_PATH.  That didn't change
anything.  Here, see:

[EMAIL PROTECTED] DBD-Oracle-1.19]# env | grep LD
OLDPWD=/usr/local/src
LD_LIBRARY_PATH=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib
LD_RUN_PATH=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib

I tried building my own Perl 5.8.8, reinstalling DBI on that perl
and then trying DBD::Oracle with that perl, but it didn't help,
same error.

I'm doing all of this as root.



Are you running SELinux?
i.e. in your /etc/sysconfig/selinux file does it say "SELINUX=enabled".
If so, try changing it to disabled, rebooting and run make test again.
If that fixes it and you still want to use SELinux you'll have to read 
up about "chcon" command which I'm not really familiar with, I just saw 
the same error with something else when SELinux was enabled.


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

Re: DBD::Oracle 1.19 install on Fedora Core 5

[Martin Evans]

Martin Evans wrote:

Chris Howard wrote:


On Wed, 2007-02-21 at 07:57 -0700, Chris Howard wrote:

Having problems with the install of DBD::Oracle 1.19 on
a Fedora Core 5 machine.

DBI 1.53 installed and the tests ran ok

I'm using the Oracle XE from the Oracle download 
oracle-xe-10.2.0.1-1.0.i386.rpm


The error message from 'make test':


PERL_DL_NONLAZY=1 /usr/local/bin/perl "-MExtUtils::Command::MM" "-e"
"test_harness(0, 'blib/lib', 'blib/arch')" t/*.t
t/01baseFailed to load Oracle extension and/or shared
libraries:
install_driver(Oracle) failed: Can't load
'/usr/local/src/DBD-Oracle-1.19/blib/arch/auto/DBD/Oracle/Oracle.so' for
module
DBD::Oracle: 
/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib/libnnz10.so: 
cannot restore segment prot after reloc: Permission denied at 
/usr/local/lib/perl5/5.8.8/i686-linux/DynaLoader.pm line 230.

 at (eval 3) line 3
Compilation failed in require at (eval 3) line 3.
Perhaps a required shared library or dll isn't installed where expected
 at t/01base.t line 19
The remaining tests will probably also fail with the same error.

I tried setting LD_LIBRARY_PATH and LD_RUN_PATH.  That didn't change
anything.  Here, see:

[EMAIL PROTECTED] DBD-Oracle-1.19]# env | grep LD
OLDPWD=/usr/local/src
LD_LIBRARY_PATH=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib
LD_RUN_PATH=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib

I tried building my own Perl 5.8.8, reinstalling DBI on that perl
and then trying DBD::Oracle with that perl, but it didn't help,
same error.

I'm doing all of this as root.



Are you running SELinux?
i.e. in your /etc/sysconfig/selinux file does it say "SELINUX=enabled".
If so, try changing it to disabled, rebooting and run make test again.
If that fixes it and you still want to use SELinux you'll have to read 
up about "chcon" command which I'm not really familiar with, I just saw 
the same error with something else when SELinux was enabled.


Martin


Oops, the file might be /etc/security/selinux/src/policy on fedora 5. I 
just read that as root, you might be able to execute "setenforce 0" to 
disable SELinux until next reboot.


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

Re: How to call DBD::Oracle's dbms_output_get from within a DBIx

[Martin Evans]

Tim Bunce wrote:

On Thu, Feb 22, 2007 at 09:12:14AM +, Martin Evans wrote:

Tim Bunce wrote:

On Wed, Feb 21, 2007 at 04:25:54PM +, Martin J. Evans wrote:

Hi,

I have DBIx::Log4perl which is currently connected to a DBD::Oracle. In 
DBIx::Log4perl::st::execute I want to call DBD::Oracle's dbms_get_line 
which is usually (from applications) called like this:


@lines = $dbh->func('dbms_output_get');

As I'm in st::execute I have a $sth and can get hold of a $dbh.

I'd expect this to work:

 $dbh = $sth->FETCH('Database');
 @lines = $dbh->func('dbms_output_get');
Thanks Tim, but that does not appear to work. In my 
DBIx::Log4perl::execute method I now have (simplified):


sub execute {
my ($sth, @args) = @_;

my $ret = $sth->SUPER::execute(@args);

my $dbh = $sth->FETCH('Database');
my @d = $dbh->func('dbms_output_get');

return $ret;
}

and I still get

Deep recursion on subroutine "DBD::Oracle::db::dbms_output_get"

dbms_output_get does create a new statement, prepare and execute it and 
it appears when execute is called it ends up back in my 
DBIx::Log4perl::execute method. I don't understand why this happens :-(


Ah. Of course. Why would you except it not to happen? ;-)


because I was not thinking straight.


dbms_output_get calls execute() on a statement handle created from your
subclassed dbh handle. So your DBIx::Log4perl::st::execute will be
called when dbms_output_get calls the execute method.

You need to either use a separate non-DBIx::Log4perl dbh for the
dbms_output_get call, or try something more hackish like

my @d = $dbh->func('dbms_output_get')
unless $sth->{Statement} =~ /^begin dbms_output.get_line/;



Cheers, I took a variation on your second suggestion similar to what 
Philip Garret put forward. This works but I have (hopefully) one related 
last issue. In the following sequence the first statement handle loses 
errstr and err values (see comments):


DBIx::Log4perl::st::execute {
my ($sth, @args) = @_;
my $h = $sth->{private_DBIx_Log4perl};

my $ret = $sth->SUPER::execute(@args);

# execute failed and an error handler was called
# $sth->errstr and $sth->err are both true and contain values

if (($h->{logmask} & DBIX_L4P_LOG_DBDSPECIFIC) &&
($h->{driver} eq 'Oracle') && (!$h->{dbd_specific})) {
$h->{dbd_specific} = 1;
my $dbh = $sth->FETCH('Database');

# The following call causes a new sth to be created in
# DBD::Oracle from a prepare_cached call and execute
# to be called multiple times to retrieve dbms_output
# The execute calls bring us back to this method but
# because of $h{dbd_specific} we don't get here again.

my @d = $dbh->func('dbms_output_get');
$sth->_dbix_l4p_debug('dbms', @d);
$h->{dbd_specific} = 0;

# Now $sth->errstr and $sth->err are undef - why?
# Why has creating a new statement and executing
# successfully on it changed errstr/err in another
# statement?
}
return $ret;
}

BTW, this is DBI 1.54rc8 (though using 1.53 makes no difference) and 
DBD::Oracle 1.19 (with one tiny patch to execute_array I posted here 
last week). This may also be slightly related to my posting "most 
drivers share error variable for sth/dbh handles?"

http://www.nntp.perl.org/group/perl.dbi.users/2007/01/msg30761.html.

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

Re: How to call DBD::Oracle's dbms_output_get from within a DBIx

[Martin Evans]

Martin Evans wrote:

Cheers, I took a variation on your second suggestion similar to what 
Philip Garret put forward. This works but I have (hopefully) one related 
last issue. In the following sequence the first statement handle loses 
errstr and err values (see comments):


DBIx::Log4perl::st::execute {
my ($sth, @args) = @_;
my $h = $sth->{private_DBIx_Log4perl};

my $ret = $sth->SUPER::execute(@args);

# execute failed and an error handler was called
# $sth->errstr and $sth->err are both true and contain values

if (($h->{logmask} & DBIX_L4P_LOG_DBDSPECIFIC) &&
($h->{driver} eq 'Oracle') && (!$h->{dbd_specific})) {
$h->{dbd_specific} = 1;
my $dbh = $sth->FETCH('Database');

# The following call causes a new sth to be created in
# DBD::Oracle from a prepare_cached call and execute
# to be called multiple times to retrieve dbms_output
# The execute calls bring us back to this method but
# because of $h{dbd_specific} we don't get here again.

my @d = $dbh->func('dbms_output_get');
$sth->_dbix_l4p_debug('dbms', @d);
$h->{dbd_specific} = 0;

# Now $sth->errstr and $sth->err are undef - why?
# Why has creating a new statement and executing
# successfully on it changed errstr/err in another
# statement?
}
return $ret;
}

BTW, this is DBI 1.54rc8 (though using 1.53 makes no difference) and 
DBD::Oracle 1.19 (with one tiny patch to execute_array I posted here 
last week). This may also be slightly related to my posting "most 
drivers share error variable for sth/dbh handles?"

http://www.nntp.perl.org/group/perl.dbi.users/2007/01/msg30761.html.

Martin


It would appear the problem I'm seeing can be simplified to the 
following (not using any DBIx):


use DBI qw(neat);
use strict;
use warnings;

my $h = DBI->connect("dbi:Oracle:XXX","xxx","yyy",
 {RaiseError => 0, PrintError => 0});
$h->func('dbms_output_enable');
my $s1 = $h->prepare(q/update fred set fred = 'fred'/);
$s1->execute;
print "errstr:\n" . neat($s1->errstr) .
"\nerr:\n" . neat($s1->err) . "\n";
my $s2 = $h->prepare(q/begin
dbms_output.put_line('fred');
end;/);
$s2->execute;
print "errstr:\n" . neat($s1->errstr) .
"\nerr:\n" . neat($s1->err) . "\n";

which produces:

errstr:
'ORA-00942: table or view does not exist (DBD ERROR: error possibly near 
<*> indicator at char 7 in 'update <*>fred set fred = 'fred'')'

err:
'942'
errstr:
undef
err:
undef

So, as you can see the $s1 has lost its errstr and err values. Any idea 
why or where to look for this?


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

Re: How to call DBD::Oracle's dbms_output_get from within a DBIx

[Martin Evans]

Tim Bunce wrote:

On Thu, Feb 22, 2007 at 04:34:08PM +, Martin Evans wrote:

Martin Evans wrote:

It would appear the problem I'm seeing can be simplified to the 
following (not using any DBIx):


use DBI qw(neat);
use strict;
use warnings;

my $h = DBI->connect("dbi:Oracle:XXX","xxx","yyy",
 {RaiseError => 0, PrintError => 0});
$h->func('dbms_output_enable');
my $s1 = $h->prepare(q/update fred set fred = 'fred'/);
$s1->execute;
print "errstr:\n" . neat($s1->errstr) ."\nerr:\n" . neat($s1->err) . "\n";
my $s2 = $h->prepare(q/begin dbms_output.put_line('fred');   end;/);
$s2->execute;
print "errstr:\n" . neat($s1->errstr) . "\nerr:\n" . neat($s1->err) . "\n";

which produces:

errstr:
'ORA-00942: table or view does not exist (DBD ERROR: error possibly near 
<*> indicator at char 7 in 'update <*>fred set fred = 'fred'')'

err:
'942'
errstr:
undef
err:
undef

So, as you can see the $s1 has lost its errstr and err values. Any idea 
why or where to look for this?


By design the DBI clears the err/errstr/state values when it dispatches
most method calls.


ok, I doubt I'm going to get anywhere arguing with the "By design" but 
I'll give it a go anyway. Comparing it with ODBC, a statement handle has 
it's own errors separate from a connection handle. i.e. you can do:


sth1 = SQLAllocHandle(dbh);
sth2 = SQLAloocHandle(dbh);
SQLSomething(dbh) # errors
SQLGetDiagRec(dbh) # this returns the error SQLSomething produced
SQLSomethingElse(sth1) # errors
SQLGetDiagRec(sth1) # returns error on sth1
SQLSomethingElse(sth2) # errors
SQLGetDiagRec(sth2) # returns error on sth2
SQLGetDiagRec(dbh) # returns same error as above with SQLSomething
SQLGetDiagRec(sth1) # returns same error as above
SQLSomethingElse(sth1) # succeeds and clears previous error on sth1
SQLGetDiagRec(sth1) # returns no errors

How ODBC differs from DBI is that DBI appears to clear all errors in all 
statements and the connection when any method is called. ODBC only 
clears the errors on a handle, when /that/ handle is used again.


As another example the OCI interface to Oracle holds errors per 
statement and I believe mysql, DB2 and TDS are similar in that respect.


Even in JDBC, you can get the errors at any time you like, because it 
returns an sql exception object so the application owns it.


Now, even if you see my point and were inclined to be persuaded to 
change the "By design" you're probably going to say you'll accept 
patches ;-) That may be something I could do but I'm guessing it is 
going to take some working out.



FYI setting DBI trace level >=4 (or >=1 in 1.54) will show you the
current err/errstr and show you when they're cleared by the DBI.


Thanks, I did look at these, I just didn't expect sth errors to be
cleared when another sth was used so I assumed it was a bug.


The issue here is that $s1 and $s2 both share the same storage for the
err/errstr/state values (provided by and shared with the $dbh).
Most drivers are implemented that way.

Options:
1. Use a different $dbh.


I don't think that is going to work out here as I'm not even sure the 
dbms_output buffer isn't per connection.



2. Save the err/errstr/state values and restore them using set_err().


yes, I can do that but as set_err calls the HandleError routine I guess 
it will have to be something like:


$s1->execute;
my ($errstr, $err, $state) = ($s1->errstr, $s1->err, $s1->state);
$s2->execute;
{
local $s1->{HandleError} = undef;
# or HandleError routine will get called twice
$s1->set_err($err, $errstr, $state);
}


3. Perform some undocumented surgery on one of the handles to give it
   separate storage for the err/errstr/state values.


If I knew how I might give it a go.

I guess 2 will do me for now.

Could I politely request that you consider keeping the handle errors 
separate for DBI2.


As always, thanks for the help and insights. Always much appreciated.

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

Re: How to prevent sth sharing error status with parent dbh and siblings

[Martin Evans]

Tim Bunce wrote:

On Thu, Feb 22, 2007 at 06:11:38PM +, Martin Evans wrote:

Tim Bunce wrote:

On Thu, Feb 22, 2007 at 04:34:08PM +, Martin Evans wrote:

Martin Evans wrote:

It would appear the problem I'm seeing can be simplified to the 
following (not using any DBIx):


use DBI qw(neat);
use strict;
use warnings;

my $h = DBI->connect("dbi:Oracle:XXX","xxx","yyy",
 {RaiseError => 0, PrintError => 0});
$h->func('dbms_output_enable');
my $s1 = $h->prepare(q/update fred set fred = 'fred'/);
$s1->execute;
print "errstr:\n" . neat($s1->errstr) ."\nerr:\n" . neat($s1->err) . "\n";
my $s2 = $h->prepare(q/begin dbms_output.put_line('fred');   end;/);
$s2->execute;
print "errstr:\n" . neat($s1->errstr) . "\nerr:\n" . neat($s1->err) . 
"\n";


which produces:

errstr:
'ORA-00942: table or view does not exist (DBD ERROR: error possibly near 
<*> indicator at char 7 in 'update <*>fred set fred = 'fred'')'

err:
'942'
errstr:
undef
err:
undef

So, as you can see the $s1 has lost its errstr and err values. Any idea 
why or where to look for this?

By design the DBI clears the err/errstr/state values when it dispatches
most method calls.
ok, I doubt I'm going to get anywhere arguing with the "By design" but 
I'll give it a go anyway. Comparing it with ODBC, a statement handle has 
it's own errors separate from a connection handle.


I didn't say it was right :)  Many core parts of the DBI are over 10 years old
and there are pleanty of things I'd do differently now.

How ODBC differs from DBI is that DBI appears to clear all errors in all 
statements and the connection when any method is called. ODBC only 
clears the errors on a handle, when /that/ handle is used again.


The DBI just clears the values for the handle being used. It's just
that, by default, the handles are sharing the same storage.

Now, even if you see my point and were inclined to be persuaded to 
change the "By design" you're probably going to say you'll accept 
patches ;-) That may be something I could do but I'm guessing it is 
going to take some working out.


In this case it's up to the driver authors. They can arrange for handles
to have their own storage for err/errsr/state.

If you want to experiment, take a look at the driver's prepare method
where it calls DBI::_new_sth:

my $sth = DBI::_new_sth($dbh, {
Statement => $statement,
});

and add these lines:

my $sth = DBI::_new_sth($dbh, {
Statement => $statement,
Err => \my $err,
Errstr => \my $errstr,
State => \my $state,
});


As you point out later in your email, this could break do. Result of 
DBD::Oracle test after that change was:


t/10general.ok 1/33 


#   Failed test 'eval error: ``'' expected 'do failed:''
t/10general.NOK 20/33#   at t/10general.t line 82. 



There were 3 others all generated from tests like this:

eval {
local $SIG{__WARN__} = sub { $warn = $_[0] };
$dbh->{RaiseError} = 1;
$dbh->do("some invalid sql statement");
};
ok($@=~ /DBD::Oracle::db do failed:/, "eval error: ``$@'' expected 
'do failed:'");


so it looks like the statement for the do has not been destroyed yet.


Alternatively you could try doing the same thing after calling prepare:

$sth = $dbh->prepare(...);
$sth->{Err} = \my $err;
$sth->{Errstr} = \my $errstr;
$sth->{State} = \my $state;

but I've not tried that and there's a chance it won't work.


It doesn't:

"Can't set DBI::st=HASH(0x8c78ffc)->{Err}: unrecognised attribute name 
or invalid value" * 3



Also, be aware that there may be subtle issues where code expects the
dbh to reflect the error status of the last child sth call.

For example, when the $dbh->do() method is returning through the DBI
dispatcher with RaiseError/PrintError enabled, the DBI checks the $dbh
for an error. But for most drivers the error would not have happened on
the dbh itself, but on a child sth. In this case you might be okay
because the sth should have been DESTROYed by then and the DBI copies
up the error status from the sth to the dbh on DESTROY, but you're
relying on the timing of the DESTROY call - and I recall that some
(older) versions of perl could delay the call beyond do() returning.

That's just one example. There may be others.


That is the only one I've found so far - with perl 5.8.8 and DBI 1.54 
and DBD::Oracle 1.19.



Options:
3. Perform some undocumented surgery on one of the handles to give it
  separate storage for the err/er

Re: CLOB Problem with DBD::ODBC/DBD::ADO for SQL Server

[Martin Evans]

Alfred,

Thank you for coming back here to let us know how you got on and the 
resolution. The answer you got from Microsoft is very interesting and it 
is certainly worth remembering the workaround.


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

Mickautsch, Alfred wrote:

Hallo,

I got a solution for this problem so I thought you might be interested to hear 
it.
At Martin's advice I called Microsoft and Hans Lindgren from Microsoft Product 
Support confirmed the bug and found a workaround. I got the the OK from Hans 
Lindgren to post the summary of the case to the list, so here it is:

---
Action:  
INSERTing strings containing '\' or '\' into the database


 


Result:
Query succeeds but the Backslash and the following  or  are removed 
from the string

 

Cause:   
The T-SQL string parser removes these characters due to a parser artefact. 

 

Resolution: 
Parameters passed using RPC (remote procedure calls) are not parsed in the same way as strings used in T-SQL. Altering the INSERTion behaviour to use RPC avoids this issue (in this case the work around is to create a SP that handles the insert and to call it using RPC).

---

Besides the Microsoft case I did my own debugging and found out, that the bug 
does only occur when the data is sent to SQL Server with SQL_DATA_AT_EXEC and 
SQLParamData/SQLPutData. But this was not confirmed by Microsoft so I cannot 
guarantee that this works so in all cases. Nevertheless it works for me.

Servus -- Alfred


--
Alfred Mickautsch

Schuler Business Solutions AG
Karl-Berner-Str. 4
D-72285 Pfalzgrafenweiler
tel:+49 (0)74 45 830-184
fax:+49 (0)74 45 830-349
e-mail: [EMAIL PROTECTED]



-Ursprüngliche Nachricht-
Von: Martin Evans [mailto:[EMAIL PROTECTED]
Gesendet: Dienstag, 30. Januar 2007 15:54
An: dbi-users@perl.org
Betreff: Re: CLOB Problem with DBD::ODBC/DBD::ADO for SQL Server


Mickautsch, Alfred wrote:

-Ursprüngliche Nachricht-
Von: Martin Evans [mailto:[EMAIL PROTECTED]
Gesendet: Dienstag, 30. Januar 2007 12:40
An: dbi-users@perl.org
Betreff: Re: AW: AW: Re: AW: CLOB Problem with 

DBD::ODBC/DBD::ADO for

SQL Server

[...]

I insert 24 chars (16 \ and 8 line feeds) and I get back 24 chrs.

I am unsure why the code does not insert CR\LF as I am 
100% sure the 

file itself contains CR\LF.

I have tried with ntext, text and char fields - no difference.

I have run it through a tds spy and can see the line feeds 
going through 
fine in both directions.

[...]

Yes, it is a weird problem. There seems to be a byte count 
limit under which this effect does not occur. It happens with 
my text example of 102858 bytes. With a text of 569 bytes it 
does not seem to happen. It is very confusing.

Thank you for your efforts.

Servus -- Alfred
As you indicate, the length has something to do with it. At 
24000 chrs 
it works and at 48000 chrs you lose each \ followed by a 
. It 
is of no consolation to you but I have duplicated it with the MS SQL 
Server driver and also demonstrated it works fine with our sql server 
driver. There is a difference. The MS SQL Server driver execs 
sp_execute 
to do the insert with the literal text whilst our driver calls 
sp_execute with a parameter.


I had thought turning off translation might help but it doesn't.

I am of the opinion this may be a ms sql server odbc driver 
bug in which 
your options are limited unless you have a support contract 
or can come 
up with a workaround.


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



SCHULER Business Solutions AG 
Aktiengesellschaft mit Sitz in D-72285 Pfalzgrafenweiler, Karl-Berner-Straße 4 
Registergericht Stuttgart HRB 430947

Vorstand: Uwe Jonas, Harald Sieber
Vorsitzender des Aufsichtsrates: Gerhard Schuler 

Re: Cannot find ODBC driver

[Martin Evans]

Robert Denton wrote:
I installed using yum, as in yum install unixODBC.  I did a search for 
some of those files.  a 'find -name sql*.h' yielded a single result: 
./usr/include/sqlite3.h and 'find -name libodbc.so' yielded: 
./usr/lib/libodbc.so


Do you recommend I unyum it and and install from source?  I would hope 
that if it is yumable then the yum install would suffice.


Robert



You need to install unixODBC development with yum in addition to what 
you've probably already installed which is unixODBC runtime.


I don't use yum myself but normally unixODBC is split into runtime and 
development packages - you need both.



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


On Apr 24, 2007, at 8:17 AM, Martin J. Evans wrote:


Robert Denton wrote:

Hi,  I am hoping someone here recognizes this problem:
I have installed unixODBC for use as a driver manager for DBD::ODBC.  
However, when I run the command: 'cpan DBD::ODBC' I end up with the 
following error:

Hmm...I cannot find an ODBC driver manager that I recognize.
...And I know about these drivers:
Microsoft ODBC, adabas, easysoft, empress, esodbc, informix,
intersolve, iodbc, sapdb, solid, udbc, unixodbc
Warning: No success on command[/usr/bin/perl Makefile.PL]
CPAN: YAML loaded ok (v0.62)
Running make test
  Make had some problems, won't test
Running make install
  Make had some problems, won't install
Perhaps the problem is that the ODBCHOME env var is set wrong?  I 
have tried the following:

export ODBCHOME=/usr/bin
export ODBCHOME=/etc
both yield the same results...  Could someone make a recommedation?  
Thanks!!

Robert
First, make sure you've installed unixODBC fully i.e. if you installed 
from some sort of package (not from source) you installed development 
support or you won't have the necessary headers (e.g. sql.h, sqlext.h, 
sqltypes.h etc).


DBD::ODBC needs to know where to find:

o sql*.h header files
o libodbc.so, the ODBC driver manager.

It is expected they all reside under a single path e.g. /usr/local in 
which the headers are looked for in /usr/local/etc and the libraries 
in /usr/local/lib (in this case you'd set ODBCHOME to /usr/local). It 
may be your unixODBC is installed such that headers are in /etc and 
libraries in /lib (in which case ODBCHOME should be /). If you have 
unixODBC installed not under a single path (e.g. headers in /etc and 
libraries in /usr/lib) this will cause you a problem since DBD::ODBC 
does not expect this and there is no way to specify different 
locations. In this latter case you will have to hack the Makefile.PL 
or Makefile or reinstall unixODBC under a single path.


Martin

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

Re: data_source syntax

[Martin Evans]

Robert Denton wrote:

Well, as it turns out, I am jumping the gun.  Going by some tutorials here:

http://www.easysoft.com/developer/languages/perl/dbd_odbc_tutorial_part_1.html 



Having unixODBC installed is not enough, I also need an ODBC driver to 
go with it. Running 'odbcinst -q -d' reveals I only have [PostgreSQL] 
but apparently I also need something that can talk to an MSSQL db.


true, you do need a driver.


I thought unixODBC came with all the necessary drivers?  Can anyone 
confirm this? Perhaps I need to install a different version?  Thanks!


I can confirm that. unixODBC includes a postgres, an old mysql, a text 
driver and a nntp driver but not a SQL Server driver which you need. 
Your choices are various commercial drivers (like ours which you'll find 
on the same site as the tutorial you are reading) or FreeTDS.


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


Robert


On Apr 24, 2007, at 7:17 PM, Ron Savage wrote:


Hi Robert


While connecting to a MSsql database, do you need to pass the uid and
pwd in the $data_source string as well as in the parameter list for
the connect call?  For example, are either of these correct:


Errr, what happens when you try both?

--
Ron Savage
[EMAIL PROTECTED]
http://savage.net.au/index.html

Re: Is PERL --> MS SQL really that difficult?

[Martin Evans]

Robert Denton wrote:
Is it just me, or is using perl on a linux server to extract information 
from an MS SQL server insanely difficult?  Has no one else struggled 
with this?


It is not that difficult but you've taken a free route which is slightly 
harder than a few commercial ones. You haven't got the money for a 
commercial solution but if you had tried OOB or our sql server driver 
they would have checked your Perl was OK, checked DBI and DBD::ODBC was 
installed (and if not given recommendations), created a unixODBC/OOB 
data source which connected to your actual SQL Server, demonstrated data 
coming back through isql and through Perl and shown you the DBI 
connection string to use.


Anyway, to your problem:


 Here is my set up:

FC4 -> PERL w/DBI -> DBD::ODBC -> unixODBC -> FreeTDS

The irony is that all I need to do is a single simple select from a 
single table, and this is something I would run once a day. Yet the 
process of getting perl to talk to an MSSQL server seems 
disproportionately convoluted.


I am hoping some reader of this list will help me identify where my 
attempts are failing.  The error that I get when I try to run the script 
is this:


Can't connect to data source driver={SQL Server};Server=<192.168.0.149>; 
database=;uid=; pwd=;, no database 
driver specified and DBI_DSN env var not set at ./feedback.pl line 11


The error is telling you that you failed to specify a database driver 
i.e. you have not prefixed your connection string with "dbi" and then a 
driver name "ODBC" e.g.


dbi:ODBC:your_odbc_connection_string_goes_here

(see the connect method in the DBI documentation - perldoc DBI)

Fix this first. Then you've got further issues with your ODBC connection 
string as the "driver" attribute should be "DRIVER" (as the ODBC spec 
says). If you use "driver=" you'll get a unixODBC error like this:


[unixODBC][Driver Manager]Data source name not found, and no default 
driver specified (SQL-IM002)


I'm also assuming you don't really have <> in your connection string - 
if you do remove them. So you should end up with:


my $dsn = q/dbi:ODBC:DRIVER={SQL Server};Server=ip_address;attr=value/;
DBI->connect($dsn, $database_username, $database_password);

where "attr=value" represents all your other attributes.

However, you should note that the driver string in {} needs to be the
exact string name for your driver as it is in the odbcinst.ini file or
unixODBC won't know what driver you mean i.e. your odbcinst.ini file
might look like:

[SQL Server]
Driver  = /usr/local/lib/libxxx.so


However, my understanding is that the DBI_DSN env var (which I have not 
explicitly set, not am I sure what to set it to if necessary) is only 
called if the $data_source in the connect() statement is empty. My 
connect() statement is as follows:


$dbh = DBI->connect( $data_source, $username, $password );

And my $data_source var is set to this:

my $data_source = 'driver={SQL Server};Server=<192.168.0.149>; 
database=

;uid=; pwd=;';


see above.


I have seen other syntax for the connect statement for other databases 
such as MySQL and Oracle but never for MSSQL. I have searched 
dbi.perl.org as well for example syntax but all the examples I can find 
there are also for non-MS databases.  If anyone knows of an alternative 
syntax that they know for certain works for MSSQL that would be terrific.


You'll find a potentially useful tutorial ( Perl DBI/DBD::ODBC Tutorial 
Part 3 - Connecting Perl on UNIX or Linux to Microsoft SQL Server) at:


http://www.easysoft.com/developer/languages/perl/sql_server_unix_tutorial.html

It uses our driver for examples but most will apply equally to any ODBC 
driver.


You may also find the Linux/UNIX ODBC article below useful:
http://www.easysoft.com/developer/interfaces/odbc/linux.html

As for the other part of the error, "no database driver specified", I am 
not sure what else I need to do to make perl aware of the FreeTDS 
driver. I have 'use DBI;' in the top of the script which is supposed to 
be sufficient.  In otherword, I do not believe that I need to tell it to 
use DBD::ODBC.  Further, I specified the install dir for unixODBC when I 
installed DBD::ODBC through cpan. Finally, I registered FreeTDS with 
unixODBC using template files and odbcinst commands.


What am I missing here?  It seems to me that there is some piece of the 
puzzle that I am overlooking that will bring all of this together. If no 
one knows the answer I would love to at least see some pointers to 
guides/tutorials/examples etc of how this set up is supposed to work.  
The examples at dbi.perl.org and in Programming the Perl DBI seem to 
never include MSSQL examples, unfortunately.


Robert


Good luck.

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

Re: DBD::ODBC tries to bind nonexisting placeholders

[Martin Evans]

Andon Tschauschev wrote:

Hello everybody,

I'm using DBI v1.51 and DBD::ODBC v1.13. I'm connecting to MSSQL2000/MSSQL2005. 
Statements are executed in prepare-execute-fetch order.

Everything works fine, since I found something suspicious: if a string  ":00" occurs  in a comment, then 
DBD::ODBC tries to bind this "placeholder" and break with error "Can't rebind placeholder 00 at 
..." (line 1971 in dbdimp.c). The Microsoft ODBC driver produces error "[Microsoft][ODBC Driver Manager] 
Invalid parameter number" (described as error number S1093 on 
http://www.microsoft.com/technet/prodtechnol/sql/70/proddocs/diag/part3/75528c16.mspx?mfr=true)

Example 1:

--  :00
SELECT 'FOO'

Example 1 produces the error above.

Example 2:

 --  :01
 SELECT 'FOO'
 
 Example 2 works fine...

Can anybody else reproduce this error? Is this a bug?

Thanks!

Andon



I think you need to look up odbc_ignore_named_placeholders in the 
DBD::ODBC documentation.


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

Re: temporary table "disapears"

[Martin Evans]

CAMPBELL, BRIAN D (BRIAN) wrote:

You're right.  It's the the other way around from what I said.  However,
when I tested this yesterday it seemed I was getting an error on the
create command also.  But I re-examined the results more carefully today
and the create worked OK; it was just the insert that failed.  However
they were both run on the same connection (same $dbh handle).  So it
seems that local temps don't persist after an execute() call, as Andon
supposed.



What if you turn autocommit off - do the temporary tables exist for 
longer then?


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



From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] 
	Sent: Wednesday, May 09, 2007 10:49 PM

To: CAMPBELL, BRIAN D (BRIAN)
Cc: [EMAIL PROTECTED]; dbi-users@perl.org
Subject: RE: temporary table "disapears"



I'm pretty sure that #tmp is a local temporary table, and ##tmp
is a global temporary table... 
	

So the original problem is most likely that the create table
#tmp and the insert into #tmp statements aren't being run on the same
physical connection. I don't know DBD::ODBC, but I can tell you that
DBD::Sybase could possibly have opened a second connection under the
covers if it thought the first statement hadn't been completely
processed yet. 
	
	Michael 
	





	Extranet 
	[EMAIL PROTECTED] - 09.05.2007 18:40 
	  
	
	To:atschauschev, dbi-users 

	cc: 

	Subject:RE: temporary table "disapears" 
	

Actually I tried this against SQL 2000, DBI 1.53 and DBD::ODBC
1.13...

You should be getting 2 errors, the same error from both
prepares.  In
other words, #foo isn't being treated as a proper table name.
Naturally, these statements work fine if you just use foo (which
isn't
temp).

However, #foo should represent a "global temp" table, and this
is not
being accepted as a valid name.  Not sure why.

But ##foo works fine, and the table does persist across executes
while
the $dbh connection is open.   With 2 #'s, it's a "local temp"
table
which means it's not visible to other sessions.  If that's OK,
perhaps
you can use that instead.



-Original Message-
From: Andon Tschauschev [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 09, 2007 8:31 AM
To: dbi-users@perl.org
Subject: temporary table "disapears"

Hello,

I am using DBI 1.51 and DBD::ODBC 1.13, connecting to MSSQL2005.

Executing following statements:
$sth = $dbh->prepare('create table #foo  (a int not null)');
$sth->execute(); $sth = $dbh->prepare('insert into #foo values
(1)');
$sth->execute();

generate an error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
name
'#foo'.

So, the temporary table "disapears" (I tested it on Sybase,
using
DBD::Sybase, too, there is no an error). Since the two
statements are
dynamically created (between come other statements), I cannot
execute in
one batch $sth = $dbh->prepare('create table #foo  (a int not
null)
insert into #foo values (1));  $sth->execute();

at once...

How can I avoid this problem?

Regards!

Andon


-
	Sucker-punch spam with award-winning protection. 
	Try the free Yahoo! Mail Beta. 
	


This message and any attachments (the "message") is
	intended solely for the addressees and is confidential. 
	If you receive this message in error, please delete it and 
	immediately notify the sender. Any use not in accord with 
	its purpose, any dissemination or disclosure, either whole 
	or partial, is prohibited except formal approval. The internet
	can not guarantee the integrity of this message. 
	BNP PARIBAS (and its subsidiaries) shall (will) not 
	therefore be liable for the message if modified. 
	

-

	Ce message et toutes les pieces jointes (ci-apres le 
	"message") sont etablis a l'intention exclusive de ses 
	destinataires et sont confidentiels. Si vous recevez ce 
	message par erreur, merci de le detruire et d'en avertir 
	immediatement l'expediteur. Toute utilisation de ce 
	message non conforme a sa destination, toute diffusion 
	ou toute publication, totale ou partielle, est interdite, sauf 
	autorisation expresse. L'internet ne permettant pas 
	d'assurer l'integrite de ce message, BNP PARIBAS (et ses
	filiales) decline(nt) toute responsabilite au titre de ce 
	message, dans l'hypothese ou il aurait ete modifie.

Re: panic: DESTROY (dbih_clearcom) in trace output

[Martin Evans]

Tim Bunce wrote:

On Thu, Jun 07, 2007 at 08:53:56PM +0100, Martin J. Evans wrote:

   (in cleanup) panic: DESTROY (dbih_clearcom) inner handle HASH is not a 
hash ref at /usr/lib/perl5/site_perl/5.8.8/DBIx/Log4perl/db.pm line 55.



I can find in the following in dbih_inner

   /* extra checks if being paranoid */
   if (DBIS->debug && (!SvROK(hrv) || SvTYPE(SvRV(hrv)) != SVt_PVHV)) {
if (!what)
return NULL;
sv_dump(orv);
croak("panic: %s inner handle %s is not a hash ref",
what, neatsvpv(hrv,0));
   }

but I'm not sure what it is croaking about. Is this something I need to 
worry about?


Nope. Upgrade your DBI.

This was fixed in r9395. The log comment was: "Remove old DBI internal
sanity check that's no longer valid (after spending a few hours banging
my head against it)"


Thanks - that did the trick. Would you mind if I changed DBI::Changes to 
mention this in case others hit it?


Martin

Re: Error building DBD-ODBC on HPUX

[Martin Evans]

Webber, Scott wrote:

I'm having trouble building the DBD-ODBC driver on our HPUX server.
Below is the log of me running:

make realclean
perl Makefile.PL
make

Note:  unixODBC is version 2.2.12 that I just built today. I have
successfully created a connection with isql.


/home/x703299/downloads/extracted/DBD-ODBC-1.13 >make realclean
rm -rf ODBC.c ODBC.xsi dbdodbc.h ./blib Makefile.aperl
blib/arch/auto/DBD/ODBC/extralibs.all
blib/arch/auto/DBD/ODBC/extralibs.ld perlmain.c tmon.out mon.out
so_locations pm_to_blib *.o *.a perl.exe perl perl ODBC.bs ODBC.bso
ODBC.def libODBC.def ODBC.exp ODBC.x core core.*perl.*.? *perl.core
core.[0-9] core.[0-9][0-9] core.[0-9][0-9][0-9]
core.[0-9][0-9][0-9][0-9] core.[0-9][0-9][0-9][0-9][0-9]
mv Makefile Makefile.old > /dev/null 2>&1
rm -rf blib/lib/auto/DBD/ODBC blib/arch/auto/DBD/ODBC
rm -rf DBD-ODBC-1.13
rm -f blib/arch/auto/DBD/ODBC/ODBC.sl
blib/arch/auto/DBD/ODBC/ODBC.bs
rm -f blib/arch/auto/DBD/ODBC/ODBC.a
rm -f  blib/lib/DBD/ODBC.pm Makefile.old Makefile
/home/x703299/downloads/extracted/DBD-ODBC-1.13

ODBCHOME=/home/x703299/unixODBC perl Makefile.PL

Useless use of private variable in void context at Makefile.PL line 431.

Configuring DBD::ODBC ...


Remember to actually *READ* the README file!

And re-read it if you have any problems.

Using DBI 1.50 (for perl 5.008007 on PA-RISC2.0-LP64) installed in
/opt/perl587/lib/site_perl/5.8.7/PA-RISC2.0-LP64/auto/DBI/
Using ODBC in /home/x703299/unixODBC

Umm, this looks like a unixodbc type of driver manager.
We expect to find the sql.h, sqlext.h and (which were
supplied with unixODBC) in $ODBCHOME/include directory alongside
the /home/x703299/unixODBC/lib/libodbc.sl library. in $ODBCHOME/lib

Warning: LD_LIBRARY_PATH doesn't include /home/x703299/unixODBC

Checking if your kit is complete...
Looks good
Injecting selected odbc driver into cc command
Injecting selected odbc driver into cc command
Using DBI 1.50 (for perl 5.008007 on PA-RISC2.0-LP64) installed in
/opt/perl587/lib/site_perl/5.8.7/PA-RISC2.0-LP64/auto/DBI/
Writing Makefile for DBD::ODBC

The DBD::ODBC tests will use these values for the database connection:
DBI_DSN=e.g. dbi:ODBC:demo
DBI_USER=
DBI_PASS=
Warning: not all required environment variables are set.

/home/x703299/downloads/extracted/DBD-ODBC-1.13 >make
cp Changes blib/lib/DBD/ODBC/Changes.pm
cp ODBC.pm blib/lib/DBD/ODBC.pm
/usr/bin/perl -p -e "s/~DRIVER~/ODBC/g"
/opt/perl587/lib/site_perl/5.8.7/PA-RISC2.0-LP64/auto/DBI//Driver.xst >
ODBC.xsi
/usr/bin/perl /opt/perl587/lib/5.8.7/ExtUtils/xsubpp  -typemap
/opt/perl587/lib/5.8.7/ExtUtils/typemap  ODBC.xs > ODBC.xsc && mv
ODBC.xsc ODBC.c
Warning: duplicate function definition 'data_sources' detected in
ODBC.xs, line 202
cc -c -I/home/x703299/unixODBC/include  -I.
-I/opt/perl587/lib/site_perl/5.8.7/PA-RISC2.0-LP64/auto/DBI/  -Ae
-D_HPUX_SOURCE -Wl,+vnocompatwarnings +DD64 +z -I/usr/local/include
-D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 +O2 +Onolimit
-DVERSION=\"1.13\"  -DXS_VERSION=\"1.13\" +Z
"-I/opt/perl587/lib/5.8.7/PA-RISC2.0-LP64/CORE"
-I/home/x703299/unixODBC/include ODBC.c





Running Mkbootstrap for DBD::ODBC ()
chmod 644 ODBC.bs
rm -f blib/arch/auto/DBD/ODBC/ODBC.sl
LD_RUN_PATH="/home/x703299/unixODBC/lib" /usr/bin/ld  -b
+vnocompatwarnings -L/usr/local/lib -L/lib/pa20_64 ODBC.o  dbdimp.o  -o
blib/arch/auto/DBD/ODBC/ODBC.sl   -L/home/x703299/unixODBC/lib -lodbc
ld: Mismatched ABI (not an ELF file) for -lodbc
Fatal error.
*** Error exit code 1

Stop.
/home/x703299/downloads/extracted/DBD-ODBC-1.13 >


What does file /home/x703299/unixODBC/lib/libodbc.sl return?

I think you are compiling DBD::ODBC 64 bit and you have compiled 
unixODBC 32 bit.


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

Re: Urgent help needed! Trouble getting column names from tables

[Martin Evans]

[EMAIL PROTECTED] wrote:

Good morning,

I was given some tables on an Oracle database, but unfortunately I do
not know the field/column names of the table. I've made numerous
attempts to print such a list for a given table, but they never seem
to work. I've been trying SQL queries such as:

SELECT column_name FROM user_tab_cols WHERE table_name =
'mytable'sname'

Sometimes I'm able to get them to run with no errors... yet no result
is produced when I try to print them. I'm not sure if there is some
trick with DBI that I'm overlooking. Any tips would be greatly
appreciated!

Cheers,
Kacey




If you only want the info about columns in a table have you tried:

$sth = $dbh->column_info( $catalog, $schema, $table, $column );

I would guess if you get no result, the where clause is not matching a 
table.


column_info is the portable way to do it.

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

Re: next version of DBD::ODBC including available unicode patch?

[Martin Evans]

Gabor Szabo wrote:

On 6/27/07, Alexander Foken <[EMAIL PROTECTED]> wrote:

> I found this patch by Alexander Foken
(that's me)

and thanks for making it available


Do you know what SQL injection means? If yes, why do you still use this
code style? If no, please learn what it means, a good starting point is
http://en.wikipedia.org/wiki/SQL_injection


Sure I know. We are talking about some internal application at a client
that  it seems has no outside interface so the risks are much smaller.

In addition this is legacy code they would like to keep working.


It can't work, because the ODBC API only accepts non-Unicode SQL
statements, or at least I did not find a way to make ODBC work with SQL
strings encoded in UTF-8


they have a patch that fixes the problem for inline SQL statement but 
does not

work when using placeholders. I was asked to integrate this and publish it
but as I found your patch and as I guess it was already tried by much more
users than what we have it might make more sense to take your patch
and add the capability to handle sql statements without placeholders.

So I would like to see someone more knowledgeable than me starting to take
care of DBD::ODBC and starting to collect the available patches.

Once I see that the patches already available are integrated and
released as some development version I hope I can find a way to send a
patch including the extra fix for the maintainers consideration.

In any case uploading new development versions of the module including the
various patches will make it more accessible for anyone to test.

regards and thanks again for your work
  Gabor



I now have write access to the svn.perl.org subversion repository for 
DBD::ODBC. I will be adding the patches I posted over the last year or 
so later today (I've listed them below). Once these are in feel free to 
post any further patches to me (preferably against the trunk tree in 
subversion as of later today) and I will do my best to include them. 
When I work out how to produce a test release and depending on whether 
I'm inundated with changes I will do a test release next week and we can 
move forwards from there.


On the point of the Alexander's unicode patch I seem to remember 
applying it over a year a go to my copy of DBD::ODBC but it broke 
building of DBD::ODBC on UNIX - perhaps my recollection is wrong.


Fixes:

Fix bug in dbd_bind_ph which leads to the error
Can't change param 1 maxlen (51->50) after first bind
in the 20SqlServer test. This is caused by svGROW in Perl 5.8.8 being 
changed to possibly grow by more than you asked (e.g. up to the next 
longword boundary).


Fix problem with binding undef as an output parameter. Reported by 
Stephen More with IBM's ODBC driver for iSeries.


Removed comment delimiters in comments in dbdimp.h leading to warnings.

Removed some unused variable declarations leading to warnings.

Fixed use of unitialised variable in dbd_bind_ph where an undef is bound 
and tracing is enabled.


Fix to current test suite which fails some tests when connected to MS
SQL Server 2005 or Express.

Fix problem getting ParamValues after an execute has
failed e.g. in an error handler

and changes:

Removed PerlIO_flush calls as it is believed they are not required.

Add logging for whether SQLDescribeParam is supported.


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

Re: help compiling error on DBI.

[Martin Evans]

Floyd Wellershaus wrote:

Hello,

 


I'm trying to install DBI on an AIX 5.2 machine.

I got the files from /usr/bin/perl -MCPAN -e 'install DBI'

 


Whenever it runs the make portion, I get the following error that I
can't get past.

 


/usr/bin/perl /usr/opt/perl5/lib/5.8.0/ExtUtils/xsubpp  -typemap
/usr/opt/perl5/lib/5.8.0/ExtUtils/typemap -typemap typemap  Perl.xs >
Perl.xsc && mv Perl.xsc Perl.c

cc_r -c-D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE
-qmaxmem=16384 -qnoansialias -DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT
-q32 -D_LARGE_FILES -qlonglong -O-DVERSION=\"1.58\"
-DXS_VERSION=\"1.58\"
"-I/usr/opt/perl5/lib/5.8.0/aix-thread-multi/CORE"   Perl.c

cc_r: 1501-224 fatal error in /usr/vac/exe/xlcentry: signal 11 received

make: 1254-004 The error code from the last command is 251.

 


Any advice would be greatly appreciated.

 


Thanks,

Floyd

 

 


Floyd Wellershaus

National Student Clearinghouse

[EMAIL PROTECTED]  


703-733-4126.




It may be the bug I found with xlc some time ago which was either 1. 
you've run out of disk space - check especially /var or 2. you have lost 
your license. I think it was 1.


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

Re: help compiling error on DBI.

[Martin Evans]

Floyd Wellershaus wrote:

Thanks. That's not it. I have plenty of disk space. Not sure what
license you're talking about though.


Have you got an enabled license for the compiler? Our xlc exits wirh 
signal 11 if we have not got a valid, enabled license. Just try 
compiling a simple hello world C program to see if it is working at all.


Martin


-Original Message-----
From: Martin Evans [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 03, 2007 9:14 AM

To: dbi-users@perl.org
Subject: Re: help compiling error on DBI.

Floyd Wellershaus wrote:

Hello,

 


I'm trying to install DBI on an AIX 5.2 machine.

I got the files from /usr/bin/perl -MCPAN -e 'install DBI'

 


Whenever it runs the make portion, I get the following error that I
can't get past.

 


/usr/bin/perl /usr/opt/perl5/lib/5.8.0/ExtUtils/xsubpp

-typemap

/usr/opt/perl5/lib/5.8.0/ExtUtils/typemap -typemap typemap  Perl.xs >
Perl.xsc && mv Perl.xsc Perl.c

cc_r -c-D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE
-qmaxmem=16384 -qnoansialias -DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT
-q32 -D_LARGE_FILES -qlonglong -O-DVERSION=\"1.58\"
-DXS_VERSION=\"1.58\"
"-I/usr/opt/perl5/lib/5.8.0/aix-thread-multi/CORE"   Perl.c

cc_r: 1501-224 fatal error in /usr/vac/exe/xlcentry: signal 11

received

make: 1254-004 The error code from the last command is 251.

 


Any advice would be greatly appreciated.

 


Thanks,

Floyd

 

 


Floyd Wellershaus

National Student Clearinghouse

[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> 


703-733-4126.




It may be the bug I found with xlc some time ago which was either 1. 
you've run out of disk space - check especially /var or 2. you have lost


your license. I think it was 1.

Martin

Re: DBI V 1.58 && Can't exec "svnversion" under SunOS

[Martin Evans]

Ron Savage wrote:

Hi Tim

278 vusbac001 perl-modules> cd DBI-1.58
279 vusbac001 DBI-1.58> perl Makefile.PL
*** Note:
The optional PlRPC-modules (RPC::PlServer etc) are not installed.
If you want to use the DBD::Proxy driver and DBI::ProxyServer
modules, then you'll need to install the RPC::PlServer, RPC::PlClient,
Storable and Net::Daemon modules. The CPAN Bundle::DBI may help you.
You can install them any time after installing the DBI.
You do *not* need these modules for typical DBI usage.

Optional modules are available from any CPAN mirror, in particular
http://search.cpan.org/
http://www.perl.com/CPAN/modules/by-module
http://www.perl.org/CPAN/modules/by-module
ftp://ftp.funet.fi/pub/languages/perl/CPAN/modules/by-module

Your perl was compiled with gcc (version 3.3.2), okay.
Creating test wrappers for DBI::PurePerl:
t/zvp_01basics.t
[snip]
t/zvp_86gofer_fail.t
Creating test wrappers for DBD::Gofer:
t/zvg_01basics.t
[snip]
t/zvg_86gofer_fail.t
Creating test wrappers for PurePerl & Gofer:
t/zvxgp_01basics.t
[snip
t/zvxgp_86gofer_fail.t
Checking if your kit is complete...
Looks good

I see you're using perl 5.008007 on sun4-solaris, okay.
Remember to actually *read* the README file!
Use  'make' to build the software (dmake or nmake on Windows).
Then 'make test' to execute self tests.
Then 'make install' to install the DBI and then delete this working
directory before unpacking and building any DBD::* drivers.

Writing Makefile for DBI
280 vusbac001 DBI-1.58> make
/usr/local/bin/perl "-MExtUtils::Command" -e mkpath blib/lib/DBI
rm -f blib/lib/DBI/Changes.pm
cp Changes blib/lib/DBI/Changes.pm
/usr/local/bin/perl "-MExtUtils::Command" -e mkpath blib/lib/DBI
rm -f blib/lib/DBI/Roadmap.pm
cp Roadmap.pod blib/lib/DBI/Roadmap.pm
/usr/local/bin/perl dbixs_rev.pl
Can't exec "svnversion": No such file or directory at dbixs_rev.pl line 5.
Use of uninitialized value in string eq at dbixs_rev.pl line 8.
Neither current directory nor parent directory are an svn working copy
make: *** [dbixs_rev.h] Error 2
281 vusbac001 DBI-1.58> uname -a
SunOS vusbac001 5.9 Generic_118558-34 sun4u sparc SUNW,Sun-Fire-280R

Help!


Obviously because you don't have subversion client installed which I'm 
surprised you need. Do you have dbixs_rev.h in the untarred DBI tree? I 
can see from the Makefile:


dbixs_rev.h: DBIXS.h Driver_xst.h dbipport.h dbivport.h dbixs_rev.pl
$(PERL) dbixs_rev.pl

The dbixs_rev.h which came with my distribution contained:

#define DBIXS_REVISION 9659

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

Re: Fail DBD::Mysql 4.003 installation

[Martin Evans]

vicky wrote:

Hi all,

I am getting my feet wet with Linux and installing bugzilla 3.0 on it.
For bugzilla 3.0, perl module DBD::mysql 2.9003 or above is required.
I am trying to install DBD::mysql 4.003 on remote linux box. This is
the only module which is stopping the bugzilla installation.

OS version information is as below:
Red Hat Enterprise Linux AS release 3 (Taroon Update 4)
Linux localhost.localdomain 2.4.21-27.ELsmp #1 SMP Wed Dec 1 21:59:02
EST 2004 i
686 i686 i386 GNU/Linux


Upon installation I am getting the following error.





t/40listfields...DBI connect('test','',...) failed: Access denied
for user '
root'@'localhost' (using password: NO) at t/40listfields.t line 57
Cannot connect: Access denied for user 'root'@'localhost' (using
password: NO)
Either your server is not up and running or you have no
permissions for acessing the DSN DBI:mysql:test.
This test requires a running server and write permissions.
Please make sure your server is running and you have
permissions, then retry.
t/40listfields...dubious
Test returned status 10 (wstat 2560, 0xa00)
DIED. FAILED tests 1-19
Failed 19/19 tests, 0.00% okay






Any help on this regard, is greatly appreciated.
Note: I am very new to linux, so it would be really helpful if I get
the detailed steps to rectify this error and install it successfully.

regards,
Tejas




You need to give root access to your local mysql. Just search the mysql 
documentation on how to do this.


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

problems inserting a utf8 timestamp with DBD::Oracle

[Martin Evans]

Hi,

Hoping someone can help me here as I'm really stuck with this. I have a 
large amount of code which reads some XML and inserts data from the XML 
into a database. One particular insert fails:


insert into document 
(book,category,country,expiry,id,last_update,mnem,name,odec,route,source,tdate,ttimestamp,ttype,version) 
values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)


and the parameters are:

SD, HR, UK, 2017-07-08 14:10:00, XXX, 1183290197812, XX, x, 
N, 534453, xx, 2007-07-01, 1183269593843, xx, 1.2.6


the error is:

DBD::Oracle::st execute failed: ORA-01843: not a valid month (DBD ERROR: 
error possibly near <*> indicator at char 146 in 'insert into document 
(book,category,country,expiry,id,last_update,mnem,name,odec,route,source,tdate,ttimestamp,ttype,version) 
values(:p1,:p2,:p3,:<*>p4,:p5,:p6,:p7,:p8,:p9,:p10,:p11,:p12,:p13,:p14,:p15)') 
at /usr/lib/perl5/site_perl/5.8.8/DBIx/Log4perl/st.pm line 38.


There is nothing obviously wrong with p4 and 2017-07-08 14:10:00 as the 
session was previously altered to set the nls_timestamp_format to 
'-mm-dd hh24:mi:ss. I have even verified that by looking at 
NLS_SESSION_PARAMETERS just before the execute call.


After much head scratching I discovered the problem P4 has utf8 set on 
it and downgrading it makes the code work i.e. when utf8::is_utf8 
returns 1 on the timestamp scalar it fails and if I do a 
utf8::downgrade(scalar) on it before calling execute it works.


When I examine a DBD:Oracle (v 1.19 BTW) trace file the only difference 
I can see between the one that fails:


   bind :p4 <== "2017-07-08 14:10:00" (type 0)
   rebinding :p4 (is-utf8, ftype 1, csid 0, csform 0, inout 0)
   bind :p4 <== "2017-07-08 14:10:00" (size 19/20/0, ptype 4, otype 1)
   bind :p4 <== '2017-07-08 14:10:00' (size 19/19, otype 1, indp 0, 
at_exec 1)

OCIBindByName(8ef1a50,8ed3a8c,8dfdad8,":p4",3,8ece658,19,1,8ed3aa4,0,8ed3
a9c,0,0,2)=SUCCESS
OCIBindDynamic(8ef1460,8dfdad8,8ed3a68,cfece0,8ed3a68,cfefc0)=SUCCESS
   rebinding :p4 with UTF8 value so setting csform=SQLCS_IMPLICIT 
 OCIAttrSet(8ef1460,OCI_HTYPE_BIND,bfc3a76b,0,32,8dfdad8)=SUCCESS

OCIAttrGet(8ef1460,OCI_HTYPE_BIND,8ed3a78,0,31,8dfdad8)=SUCCESS
   bind :p4 <== "2017-07-08 14:10:00" (in, is-utf8, csid 
873->0->873, ftype 1, csform 0->2, maxlen 19, maxdata_size 0)

OCIAttrSet(8ef1460,OCI_HTYPE_BIND,bfc3a768,0,31,8dfdad8)=SUCCESS

and the successful one:

   bind :p4 <== '2017-07-08 14:10:00' (type 0)
   rebinding :p4 (not-utf8, ftype 1, csid 0, csform 0, inout 0)
   bind :p4 <== '2017-07-08 14:10:00' (size 19/20/0, ptype 4, otype 1)
   bind :p4 <== '2017-07-08 14:10:00' (size 19/19, otype 1, indp 0, 
at_exec 1)

OCIBindByName(9330bc8,9315214,923ed10,":p4",3,926b1e0,19,1,931522c,0,9315
224,0,0,2)=SUCCESS
OCIBindDynamic(93305d8,923ed10,93151f0,1c4ce0,93151f0,1c4fc0)=SUCCESS
OCIAttrGet(93305d8,OCI_HTYPE_BIND,9315200,0,31,923ed10)=SUCCESS
   bind :p4 <== '2017-07-08 14:10:00' (in, not-utf8, csid 
873->0->873, ftype 1, csform 0->0, maxlen 19, maxdata_size 0)


is the us-utf8 versus not-utf8.

I have tried reducing the problem to a small script but just setting the 
UTF8 flag on any timestamp does not make it fail so I'm unsure where to 
go now. Any ideas?


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

Re: Smart way to detect SELECT statements?

[Martin Evans]

Alexander Foken wrote:

Hello DBI-Users,

is there a smart way to detect if a prepared statement is a SELECT or a 
non-SELECT statement?


Examine NUM_OF_FIELDS on the statement which will be 0 for non-select 
statements.


From DBI:

Statements that don’t return rows of data, like "DELETE" and
   "CREATE" set "NUM_OF_FIELDS" to 0 (though it may be undef in some
   drivers).


Background: My application allows selected users (the Admin account, to 
be precise) to enter arbitary SQL statements, passes them to prepare(), 
execute(), some fetch()es, and finally finish(). Of course, when the 
Admin executes something like UPDATE or DELETE, there is nothing to 
fetch(), and fetch() complains loudly. The application is designed to 
work with several database servers, so a database-specific solution 
won't help me.


I found $sth->{'Active'}, but the DBI documentation is a bit vague about 
its meaning. Is there another, portable way than the brute-force 
$sth->fetch() if $sql=~/\s*SELECT\b/i ?


Thanks,
Alexander




Hope this helps.

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

Re: DBI-Help

[Martin Evans]

Samuel Zheng - keunghing.com wrote:

Hi DBI Guru,

I am hoping someone will point me to right direction.

I am using WindowsXP and IIS is running. ActivePerl 5.8.8 build 817 is
installed, dbd-odbc 1.13 is also installed. The problem I have is it fails
to open an access97 database, I used odbcad32.exe to define a DSN, I put in
the user as admin and its password accordingly and it can be opened by my
crystal report writer.

The problem was not there before.  I can developed many different pages in
Perl before it stopped working. and I don't know why it stopped. I have 
exhausted

everything I can do (inclding reinstall perl) but no avail. Hope you can
give me a hint . Thank you in advance. The following is the line it failed:

$dbh = DBI->connect($dsn,$user,$pswrd,$driver,{RaiseError=>1}) or die
"Database connection not made: $DBI::errstr";

the error is:
DBI connect('btn','',...) failed: [Microsoft][ODBC Microsoft Access Driver]
Disk or network error. (SQL-HY000)(DBD: db_login/SQLConnect err=-1) at
C:\ButtonS\khoffice\pwschedule.pl line 43

The script  worked before but not now. I don't know why. Definitely 
something happened to my computer but I don't know what. please help. I 
disable the firewall, I shared the foler, I recreated the DSN and I 
reinstalled everything but no use. What else should I tried?


Samuel Zheng



A quick google search produced the following link that looks promising:

http://support.microsoft.com/default.aspx/kb/251254

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

Re: Trouble Installing DBD::ODBC with postgresql

[Martin Evans]

Craig Metzer wrote:

I'm having trouble installing DBD::ODBC 1.13 on a system with postgresql.  It 
appears it's because it can't find the sql.h, sqlext.h, etc. headers.  I 
installed the developer and library packages that were supposed to contain 
these headers.  Could someone please tell me what I'm doing wrong or where to 
find the headers?
 
TIA,

Craig
 
~~~ install info   ~~
 
[EMAIL PROTECTED] DBD-ODBC-1.13]$ perl Makefile.PLUseless use of private variable in void context at Makefile.PL line 431.

Configuring DBD::ODBC ...

Remember to actually *READ* the README file!And re-read it if you 
have any problems.

Multiple copies of Driver.xst found in: 
/usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI/ 
/usr/lib64/perl5/vendor_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI/ at 
Makefile.PL line 61Using DBI 1.58 (for perl 5.008005 on 
x86_64-linux-thread-multi) installed in 
/usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI/Using ODBC 
in /usr
Umm, this looks like a unixodbc type of driver manager.We expect to find the 
sql.h, sqlext.h and (which weresupplied with unixODBC) in $ODBCHOME/include 
directory alongsidethe /usr/lib/libodbc.so library. in $ODBCHOME/lib
Injecting selected odbc driver into cc commandInjecting selected odbc driver 
into cc commandMultiple copies of Driver.xst found in: 
/usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI/ 
/usr/lib64/perl5/vendor_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI/ at 
Makefile.PL line 462Using DBI 1.58 (for perl 5.008005 on 
x86_64-linux-thread-multi) installed in 
/usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI/Writing 
Makefile for DBD::ODBC
The DBD::ODBC tests will use these values for the database connection:
DBI_DSN=dbi:ODBC:pgsql  e.g. dbi:ODBC:demoDBI_USER=postgres
DBI_PASS=postgres
[EMAIL PROTECTED] DBD-ODBC-1.13]$ rpm -qa | grep [EMAIL PROTECTED] 
DBD-ODBC-1.13]$ find /usr -name 
sql\*.h/usr/include/sql3types.h/usr/include/sqlca.h/usr/include/pgsql/informix/esql/sqltypes.h/usr/include/pgsql/informix/esql/sqlda.h/usr/include/mysql/sql_state.h/usr/include/mysql/sql_common.h/usr/share/doc/qt-devel-3.3.3/examples/demo/sql/sqlex.ui.h/usr/local/include/sqltypes.h/usr/src/redhat/SOURCES/postgresql-7.4.13/src/bin/psql/sql_help.h/usr/src/redhat/SOURCES/postgresql-7.4.13/src/inter

faces/ecpg/include/sql3types.h/usr/src/redhat/SOURCES/postgresql-7.4.13/src/interfaces/ecpg/include/sqlca.h/usr/src/redhat/SOURCES/postgresql-7.4.13/src/interfaces/ecpg/include/sqltypes.h/usr/src/redhat/SOURCES/postgresql-7.4.13/src/interfaces/ecpg/include/sqlda.h
 
_

See what you’re getting into…before you go there.
http://newlivehotmail.com


You need the unixODBC development RPM (unixodbc-dev) which contains 
sql.h, sqltypes.h, sqlext.h and sqlucode.h.


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

Re: Trouble Installing ODBC 1.13 on Linux 64

[Martin Evans]

Craig Metzer wrote:
 
Hope you all are having a pleasant summer.
 
I am ... just a little trouble installing ODBC for Perl 5.8.5.  Perhaps someone can help me out.
 
I get an error on make .. it appears to be in building ODBC.so:
 
gcc  -shared ODBC.o dbdimp.o  -o blib/arch/auto/DBD/ODBC/ODBC.so   -L/usr/lib -lodbc/usr/bin/ld: skipping incompatible /usr/lib/libodbc.so when searching for -lodbc/usr/lib/libodbc.a: could not read symbols: Bad valuecollect2: ld returned 1 exit statusmake: *** [blib/arch/auto/DBD/ODBC/ODBC.so] Error 1

TIA,
Craig
Washington DC
 
 output  ~

[EMAIL PROTECTED] DBD-ODBC-1.13]$ perl Makefile.PLUseless use of private 
variable in void context at Makefile.PL line 431.
Configuring DBD::ODBC ...

Remember to actually *READ* the README file!And re-read it if you 
have any problems.

Multiple copies of Driver.xst found in: 
/usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI/ 
/usr/lib64/perl5/vendor_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI/ at 
Makefile.PL line 61Using DBI 1.58 (for perl 5.008005 on 
x86_64-linux-thread-multi) installed in 
/usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI/Using ODBC 
in /usr
Umm, this looks like a unixodbc type of driver manager.We expect to find the 
sql.h, sqlext.h and (which weresupplied with unixODBC) in $ODBCHOME/include 
directory alongsidethe /usr/lib/libodbc.a /usr/lib/libodbc.so library. in 
$ODBCHOME/lib
Checking if your kit is complete...Looks goodInjecting selected odbc driver 
into cc commandInjecting selected odbc driver into cc commandMultiple copies of 
Driver.xst found in: 
/usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI/ 
/usr/lib64/perl5/vendor_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI/ at 
Makefile.PL line 462Using DBI 1.58 (for perl 5.008005 on 
x86_64-linux-thread-multi) installed in 
/usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI/Writing 
Makefile for DBD::ODBC
The DBD::ODBC tests will use these values for the database connection:
DBI_DSN=dbi:ODBC:pgsql  e.g. dbi:ODBC:demoDBI_USER=postgres
DBI_PASS=postgres
[EMAIL PROTECTED] DBD-ODBC-1.13]$ makecp Changes blib/lib/DBD/ODBC/Changes.pmcp ODBC.pm blib/lib/DBD/ODBC.pm/usr/bin/perl -p -e 
"s/~DRIVER~/ODBC/g" /usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI/Driver.xst > 
ODBC.xsi/usr/bin/perl /usr/lib/perl5/5.8.5/ExtUtils/xsubpp  -typemap /usr/lib/perl5/5.8.5/ExtUtils/typemap  ODBC.xs > ODBC.xsc 
&& mv ODBC.xsc ODBC.cWarning: duplicate function definition 'data_sources' detected in ODBC.xs, line 202gcc -c -I/usr/include 
 -I. -I/usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI -D_REENTRANT -D_GNU_SOURCE -DDEBUGGING 
-fno-strict-aliasing -pipe -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm -O2 -g -pipe -m64   
-DVERSION=\"1.13\" -DXS_VERSION=\"1.13\" -fPIC 
"-I/usr/lib64/perl5/5.8.5/x86_64-linux-thread-multi/CORE"  -I/usr/include ODBC.cgcc -c -I/usr/include  -I. 
-I/usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI -D_REENTRANT -D_GNU_SOURCE -DDEBUGGI

NG -fno-strict-aliasing -pipe -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 
-I/usr/include/gdbm -O2 -g -pipe -m64   -DVERSION=\"1.13\" -DXS_VERSION=\"1.13\" -fPIC 
"-I/usr/lib64/perl5/5.8.5/x86_64-linux-thread-multi/CORE"  -I/usr/include dbdimp.cdbdimp.c: In 
function `odbc_db_STORE_attrib':dbdimp.c:3108: warning: cast from pointer to integer of different 
sizedbdimp.c: In function `odbc_db_FETCH_attrib':dbdimp.c:3440: warning: cast to pointer from integer of 
different sizeRunning Mkbootstrap for DBD::ODBC ()chmod 644 ODBC.bsrm -f blib/arch/auto/DBD/ODBC/ODBC.sogcc  
-shared ODBC.o dbdimp.o  -o blib/arch/auto/DBD/ODBC/ODBC.so   -L/usr/lib -lodbc/usr/bin/ld: skipping 
incompatible /usr/lib/libodbc.so when searching for -lodbc/usr/lib/libodbc.a: could not read symbols: Bad 
valuecollect2: ld returned 1 exit statusmake: *** [blib/arch/auto/DBD/ODBC/ODBC.so] Error 1
 
[EMAIL PROTECTED] DBD-ODBC-1.13]$ ls -l $ODBCHOME/include/sql*.h-rw-r--r--  1 root root 31945 Nov 21  2005 /usr/include/sql.h-rw-r--r--  1 root root  1007 Feb 16  2006 /usr/include/sql3types.h-rw-r--r--  1 root root  1258 Feb 16  2006 /usr/include/sqlca.h-rw-r--r--  1 root root 78402 Nov 21  2005 /usr/include/sqlext.h-rw-r--r--  1 root root 11165 Nov 21  2005 /usr/include/sqltypes.h-rw-r--r--  1 root root 21671 Nov 21  2005 /usr/include/sqlucode.h
 
[EMAIL PROTECTED] DBD-ODBC-1.13]$ exportdeclare -x DBI_DSN="dbi:ODBC:pgsql"declare -x DBI_PASS="postgres"declare -x DBI_USER="postgres"declare -x LANG="C"declare -x LD_LIBRARY_PATH="/usr:/usr/lib/pgsql:/lib:/lib64"declare -x ODBCHOME="/usr"declare -x PATH="/sbin:/usr/sbin:/usr/local/sbin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:/usr/local/pgsql/bin:/opt/CSCOnfc/bin:/opt/CSCOnfc/tools:/usr/local/mrtg-2/bin:/home/mrtg/bin:./"declare -x POSTGRES_INCLUDE="/usr/include/pgsql"dec

Re: Trouble Installing ODBC 1.13 on Linux 64

[Martin Evans]

Craig Metzer wrote:

Martin,
Thanks for the reply.
I'm not sure I understand the problem with the .a files.  How would I go about 
makeing it use teh .la?


That was just example showing what happens when the gnu linker hits a 32 
bit .a when it is attempting to do a 64 bit build - I was not suggesting 
you need to use .la files as you are not using configure and libtool.



Would I have to mod the Makefile?
I have two versions of libodbc one 32 and another 64.
The paths that Makefile chose were the 32-bit ... should I mod these to use the 
lib64 instead?


It looked to me that your perl was 64 bit and hence you need to point 
DBD::ODBC at the 64 bit unixODBC. That might be a little tricky as it 
stands without editing the makefile as you look like you want DBD::ODBC 
to look in /usr/lib64 for libodbc.so but /usr/include for the header 
files and DBD::ODBC only supports specifying the one location for the 
driver/driver_manager.


I'd do a perl Makefile.PL with ODBCHOME set to /usr then edit the 
makefile for now and change the -L/usr/lib to -L/usr/lib64.



Thanks for the help.
Craig
 file output  ~~
[EMAIL PROTECTED] DBD-ODBC-1.13]# file 
/usr/lib64/libodbc.so/usr/lib64/libodbc.so: symbolic link to 
`libodbc.so.1.0.0'[EMAIL PROTECTED] DBD-ODBC-1.13]# file 
/usr/lib64/libodbc.so.1.0.0/usr/lib64/libodbc.so.1.0.0: ELF 64-bit LSB shared 
object, AMD x86-64, version 1 (SYSV), [EMAIL PROTECTED] DBD-ODBC-1.13]# file 
/usr/lib/libodbc.so/usr/lib/libodbc.so: symbolic link to 
`libodbc.so.1.0.0'[EMAIL PROTECTED] DBD-ODBC-1.13]# file 
/usr/lib/libodbc.so.1.0.0/usr/lib/libodbc.so.1.0.0: ELF 32-bit LSB shared 
object, Intel 80386, version 1 (SYSV), stripped
 





Date: Fri, 13 Jul 2007 16:26:26 +0100> From: [EMAIL PROTECTED]> To: dbi-users@perl.org> Subject: Re: Trouble Installing ODBC 1.13 on Linux 64> > Craig Metzer wrote:> > > > 
Hope you all are having a pleasant summer.> > > > I am ... just a little trouble installing ODBC for Perl 5.8.5. Perhaps someone can help me out.> > > > I get an error on make 
.. it appears to be in building ODBC.so:> > > > gcc -shared ODBC.o dbdimp.o -o blib/arch/auto/DBD/ODBC/ODBC.so -L/usr/lib -lodbc/usr/bin/ld: skipping incompatible /usr/lib/libodbc.so when 
searching for -lodbc/usr/lib/libodbc.a: could not read symbols: Bad valuecollect2: ld returned 1 exit statusmake: *** [blib/arch/auto/DBD/ODBC/ODBC.so] Error 1> > TIA,> > Craig> > 
Washington DC> > > >  output ~> > [EMAIL PROTECTED] DBD-ODBC-1.13]$ perl Makefile.PLUseless use of private variable in void context at Makefile.PL line 431.> > 
Configuring DBD::ODBC ...>  Remember to actually *READ* the README file! And re-read i
t if you have any problems.> > Multiple copies of Driver.xst found in: /usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI/ /usr/lib64/perl5/vendor_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI/ at Makefile.PL line 61Using DBI 1.58 (for perl 5.008005 on x86_64-linux-thread-multi) installed in /usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI/Using ODBC in /usr> > Umm, this looks like a unixodbc type of driver manager.We expect to find the sql.h, sqlext.h and (which weresupplied with unixODBC) in $ODBCHOME/include directory alongsidethe /usr/lib/libodbc.a /usr/lib/libodbc.so library. in $ODBCHOME/lib> > Checking if your kit is complete...Looks goodInjecting selected odbc driver into cc commandInjecting selected odbc driver into cc commandMultiple copies of Driver.xst found in: /usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI/ /usr/lib64/perl5/vendor_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI/ at Makefile.PL line 462Using 
DBI 1.58 (for perl 5.008005 on x86_64-linux-thread-multi) installed in /usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI/Writing Makefile for DBD::ODBC> > The DBD::ODBC tests will use these values for the database connection: DBI_DSN=dbi:ODBC:pgsql e.g. dbi:ODBC:demo DBI_USER=postgres DBI_PASS=postgres> > [EMAIL PROTECTED] DBD-ODBC-1.13]$ makecp Changes blib/lib/DBD/ODBC/Changes.pmcp ODBC.pm blib/lib/DBD/ODBC.pm/usr/bin/perl -p -e "s/~DRIVER~/ODBC/g" /usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI/Driver.xst > ODBC.xsi/usr/bin/perl /usr/lib/perl5/5.8.5/ExtUtils/xsubpp -typemap /usr/lib/perl5/5.8.5/ExtUtils/typemap ODBC.xs > ODBC.xsc && mv ODBC.xsc ODBC.cWarning: duplicate function definition 'data_sources' detected in ODBC.xs, line 202gcc -c -I/usr/include -I. -I/usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI -D_REENTRANT -D_GNU_SOURCE -DDEBUGGING -fno-strict-aliasing -pipe -I/usr/local/include -D_LARGEFILE_SOURCE -D_

FILE_OFFSET_BITS=64 -I/usr/include/gdbm -O2 -g -pipe -m64 -DVERSION=\"1.13\" -DXS_VERSION=\"1.13\" -fPIC 
"-I/usr/lib64/perl5/5.8.5/x86_64-linux-thread-multi/CORE" -I/usr/include ODBC.cgcc -c -I/usr/include -I. 
-I/usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI -D_REENTRANT -D_GNU_SOURCE -DDEBUGGI> NG -fno-strict-aliasin

Re: Trouble Installing ODBC 1.13 on Linux 64

[Martin Evans]

Martin,
 
Thanks ... make builds the libs now.  I'm still failing a few test, but 
I'll try sorting it out from here.
 
 
Craig


Craig,

I've taken a quick look at some of the errors you are getting with the 
postgres ODBC driver - see below:


Craig Metzer wrote:

 
~~~ results  
 
[EMAIL PROTECTED]  DBD-ODBC-1.13]$ make test 
TEST_VERBOSE=1
PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-e" 
"test_harness(1, 'blib/lib', 'blib/arch')" t/*.t

t/01base.1..5
ok 1 - require DBI;
ok 2 - import DBI
ok 3 - DBI->internal is DBI::dr
ok 4 - Install ODBC driver OK
ok 5 - Version is not empty
ok
t/02simple...1..36
ok 1 - use DBI;
ok 2 - use ODBCTEST;
ok 3 - Set Auto commit
ok 4 - Auto commit retrieved to what was set
ok 5 - create test table
ok 6 - test table exists
ok 7 - insert test data
ok 8 - select test data
ok 9 - Set Long Read len
ok 10 - Set Long Truncok 1
ok 11 - Set Print Error
ok 12 - Select Long data
ok 13 - Set Long Truncok 0
ok 14 - Select Long Data failure
ok 15 - prepare select from table
ok 16 - Execute select
# Coltype 5 not found in list # #
# Coltype 12 not found in list # #
# Coltype -1 not found in list # #
# Coltype 9 not found in list # #
# Failed test (t/02simple.t at line 76)
not ok 17 - Col count matches correct col count
#  got: '0'
# expected: '4'


Caused by the postgres driver returning lowercase column names when the 
test expects uppercase names - I will have this fixed in next release of 
DBD::ODBC as lowercase names are fine.



ok 18 - Set RaiseError 0
ok 19 - Set PrintError 0
ok 20 - Error reported on bad query
ok 21 - date check select
ok 22 - date check execute
ok 23 - date check rows
ok 24 - group by query prepare
ok 25 - group by query execute
ok 26 - group by query returned rows
# Failed test (t/02simple.t at line 132)
# '0'
# >
# '0'
not ok 27 - data sources test
ok 28 - test ping method
ok 29 - Attrib odbc_ignore_named_placeholders 0 to start
ok 30 - Attrib odbc_ignore_named_placeholders set to 1
ok 31 - test connecting twice to the same database
ok 32 - database name is returned successfully
ok 33 - automatically finish when execute run again
ok 34 - INVALID DSN Test: [unixODBC][Driver Manager]Data source name not 
found, and no default driver specified (SQL-IM002)(DBD: 
db_login/SQLConnect err=-1)

#
ok 35 - Connection with DSN=
# Looks like you failed 2 tests of 36.
ok 36 - Connection with DSN= and uid and pwd are set
dubious
Test returned status 2 (wstat 512, 0x200)
DIED. FAILED tests 17, 27
Failed 2/36 tests, 94.44% okay


Works for me with latest postgres ODBC driver on Linux.


t/03dbatt1..24
ok 1 - use DBI;
ok 2 - use ODBCTEST;
ok 3 - Set Long Read Len
ok 4 - AutoCommit set on dbh
ok 5 - commitTest with AutoCommit
ok 6 - AutoCommit turned off
ok 7 - commitTest with AutoCommit off
DBD driver has not implemented the AutoCommit attribute at t/03dbatt.t 
line 54.
Issuing rollback() for database handle being DESTROY'd without explicit 
disconnect().


Sure I've seen this one before on this list. It works with the latest 
version of the postgres ODBC driver I have.



# Looks like you planned 24 tests but only ran 7.
# Looks like your test died just after 7.
dubious
Test returned status 255 (wstat 65280, 0xff00)
DIED. FAILED tests 8-24
Failed 17/24 tests, 29.17% okay


Obviously, because it failed it skipped some tests in 03dbatt some of 
which fail for me.


The test for the column names from a SQLTables call returns ODBC 2 
column names instead of ODBC 3 column names. I think this is a bug in 
Postgres because it reports itself as an ODBC 3 driver and I will see 
about reporting it. For now, I've changed the test to capture and handle 
the old column names and output a warning instead.


The test to check $sth->{NAME} returns an empty hash ref before and 
after an execute call on an update statement fails for me. I think this 
is also a bug in Postgres as the sequence of ODBC calls and the final 
error are:


SQLPrepare(update table set column = 100 where column = 100);
SQLNumResultCols returns 0 columns
SQLMoreResults returns SQL_NO_DATA
SQLExecute
SQLRowCount returns 1 row affected
SQLNumResultCols returns 0 columns
SQLMoreResults returns SQL_NO_DATA
SQLNumResultCols returns an error saying
  HY000 No query has been executed with that handle

The unixODBC driver manager lets this call through because it is no a 
function sequence error (usually the only error you get from 
SQLNumResultCols) and:


1. as far as I can see from the ODBC spec SQLNumResultCols should return 
success and 0 columns here

2. 3 other drivers I've tried today all work fine with this.

I will attempt to file a bug with Postgres people.
In the mean time, the next release of DBD::ODBC will skip this test for 
postgres with a warning.



t/05meth.1..8
ok 1 - use DBI;
ok 2 - delete prepared statement
ok 3 - Number of rows > 0
ok 4 - Number of rows from

DBD::ODBC multiple active statements (was Trouble Installing DBD::ODBC with postgresql)

[Martin Evans]

Wm Mussatto wrote:

On Sat, July 14, 2007 2:40, Martin J. Evans said:

Alexander Foken wrote:

Is there a special reason why you do not use DBD::Pg? It should be
faster because it has less overhead and it supports Unicode better
than DBD::ODBC, should you need it. DBD::ODBC has seen no update since
about three years, while the current DBD::Pg is just one year old.

Alexander


Just so everyone on the list knows. I did a development release of
DBD::ODBC (1.14_1) a little over a week ago. You can find it on cpan. It
fixes all bugs I know about except one on rt.cpan I have not sufficient
info as yet to look at. If anyone knows of any other issues please
report them on rt.cpan and I will look in to them. Unless I hear from
anyone I'll release 1.14 properly late next week.

Martin

On 13.07.2007 00:11, Craig Metzer wrote:

I'm having trouble installing DBD::ODBC 1.13 on a system with
postgresql.  It appears it's because it can't find the sql.h,
sqlext.h, etc. headers.  I installed the developer and library
packages that were supposed to contain these headers.  Could someone
please tell me what I'm doing wrong or where to find the headers?

TIA,
Craig


Please excuse my ignorance, but is DBD::ODBC still limited to one running
query through each Database Handle at a time?  That is
$sth=$dbh->prepare(...); $sth->execute;
$sth1=$dbh->prepare(...);
$sth1 will invalidate $sth's result set.  Stubbed my toe on this when I
was trying to apply DBD::mysql to DBD::ODBC (target MS-SQL server).  Of
course that was a number of years ago.

Thanks.

Bill




This depends on the ODBC Driver - it was never a limitation of DBD::ODBC.

By default SQL Server did not used to support multiple active statements 
if any of them were select statements. You could get around this by 
changing to a dynamic cursor (I believe there is a setting in DBD::ODBC 
to enable this and perhaps even a test case for it in the t subdir of 
the distribution in 20SqlServer.t).


In MS SQL Server 2005, there is a new thing called MARS (Multiple Active 
Result Sets) which allows multiple active select statements but it has 
some nasty implications it you are also doing transactions.


For other drivers it depends. I believe Oracle ODBC driver does support 
multiple active statements as myodbc does. Not sure about the rest.


If anyone wants to report success with a particular driver and multiple 
active statements I will collect them and add a FAQ.


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

DBD::ODBC 1.14 released to CPAN

[Martin Evans]

I have uploaded DBD::ODBC 1.14 to CPAN where it should be available later.

From the changes file:

Fix bug reported where ping crashes after disconnect thanks to Steffen
Goeldner.

Fix bug in dbd_bind_ph which leads to the error Can't change param 1
maxlen (51->50) after first bind in the 20SqlServer test. This is
caused by svGROW in Perl 5.8.8 being changed to possibly grow by more
than you asked (e.g. up to the next longword boundary).

Fix problem with binding undef as an output parameter. Reported by
Stephen More with IBM's ODBC driver for iSeries.

Removed comment delimiters in comments in dbdimp.h leading to warnings.

Removed some unused variable declarations leading to warnings.

Removed PerlIO_flush calls as it is believed they are not required.

Add logging for whether SQLDescribeParam is supported.

Fixed use of unitialised variable in dbd_bind_ph where an undef is
bound and tracing is enabled.

Fixed issue with TRACESTATUS change in 20SqlServer.t tests 28, 31, 32
and 33 leading to those tests failing when testing with SQL Server
2005 or Express.

Many compiler warnings fixed - especially for incompatible types.

Add provisional Unicode support - thanks to Alexander Foken. This
change is very experimental (especially on UNIX). Please see ODBC.pm
documentation. Also see README.unicode and README.af. New database
attribute odbc_has_unicode to test if DBD::ODBC was built with UNICODE
support. New tests for Unicode. New requirement for Perl 5.8.1 if
Unicode support required. New -[no]u argument to Makefile.PL. New
warning in Makefile.PL if Unicode support built for UNIX.

Fix use of unitialised var in Makefile.PL.

Fix use of scalar with no effect on Makefile.PL

Added warning to Makefile.PL about building/running with LANG using
UTF8.

Added warning to Makefile.PL about using thread-safe ODBC drivers.

Updated MANIFEST to include more test code from mytest and remove
MANIFEST.SKIP etc.

Removed calls to get ODBC errors when SQLMoreResults returns SQL_NO_DATA.
These are a waste of time since SQL_NO_DATA is expected and there is no
error diagnostic to retrieve.

Changes to test 17 of 02simple.t which got "not ok 17 - Col count
matches correct col count" errors with some Postgres ODBC
drivers. Caused by test expecting column names to come back
uppercase. Fixes by uppercasing returned column names.

Changes to tests in 03batt.t which correctly expects an ODBC 3 driver
to return the column names in SQLTables result-set as per ODBC 3.0
spec. Postgres which reports itself as an ODBC 3.0 driver seems to
return the ODBC 2 defined column names. Changed tests to catch ODBC
2.0 names are pass test put issue warning.

For postgres skip test (with warning) checking $sth->{NAME} returns
empty listafter execute on update

Updated FAQ, added a few more questions etc.

DBD::ODBC requires at least 5.6.0 of Perl.

Many updates to pod documentation.

Removed some dead HTTP links in the pod I could not find equivalents for -
  let me know if you have working replacements for ones removed

Add some HTTP links to useful tutorials on DBD::ODBC

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

Re: Efficient scanning of SQL Server 2005 tables?

[Martin Evans]

Amos Shapira wrote:

On 18/07/07, Ron Savage <[EMAIL PROTECTED]> wrote:


Amos Shapira wrote:

Hi Amos

> Our local SQL Server/Windows/C++ guru told me that the standard way we
scan
> tables causes the server to practically copy the entire result set into
a
> temporary table on the server before feeding it to the client.

I assume MS are so cynical they do this to deliberately slow down the
process.



Hanlon's Razor: Don't attribute to malice what can be adequately explained
by incompetence.
Bruce's Razor: Don't attribute to incompetence what can be adequately
explained by Microsoft funding.
- Craig Bruce (
http://www.brainyquote.com/quotes/quotes/c/craigbruce189141.html)


He found a way to avoid this in C++ by some clever direct ODBC hacks on
> Windows which make the server practically stream the results to the
client
> without creating a temporary table.
>
> Is there a way to achieve this with Perl on Linux too?

Well, if you can publish his code here it should be convertible in Perl
(perhaps with a C++ component). But if the code remain secret, I can't
see how it can be replicated without being independently created.



Here is what I got from him:

1. A pointer to the MSDN article which (tries to) explain this. Maybe it's
more meaningful to people with more experience with SQL Server than me (he
specifically quotes the last paragraph beginning with "The SQL Server ODBC
driver offers an optimization"):
http://msdn2.microsoft.com/en-us/library/aa197691(sql.80).aspx


Which initially basically says fetch multiple rows using row-wise or 
column-wise binding which anyone who has worked with the ODBC API knows 
is often a lot faster (there are examples of times in the following url 
(although they are specific to our bridge and sql server)


http://www.easysoft.com/products/data_access/odbc_odbc_bridge/performance_white_paper.html#3_1_1

However the text

"The SQL Server ODBC driver offers an optimization using rowsets to 
quickly retrieve an entire result set. To use this optimization, set the 
cursor attributes to their defaults (forward-only, read-only, rowset 
size = 1) at the time SQLExecDirect or SQLExecute is called"


seems a little confusing. Set things up to their defaults!


2. Pseudo code which supposedly demonstrate what this article is talking
about:

Set CursorType = forward_only, read_only
Set RowSize = 1
ExecDirect( query )
SQLFetch
Set RowSize = 1000
BindColums (To buffer big enough to hold 1000 rows)
While ( moreResults )
Begin
 SQLFetchScroll( SQL_FETCH_NEXT )
end

Thanks,

--Amos



This is certainly quicker (see reference above) but more difficult to do 
in DBD::ODBC than it first appears. A few years ago I spent a day 
starting to implement it and got diverted when we could use another DBD 
instead. I'm certainly not rushing in to this right now but don't let 
that stop anyone else.


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

Re: float bug? perl 5.8, DBI and oracle 10.2.0

[Martin Evans]

Erwan Lemonnier wrote:

Hi again! Thanks Tim for this thorough explanation :)

For the record, DBD::Oracle binds parameters and fetches values as 
strings.


Great, now we are getting closer to the heart :)

As I wrote in my first post, the problem can be triggered with the
following perl test:


use strict;
use warnings;
use Data::Dumper;
use Test::More tests => 1;
use Carp qw(confess);
use DBI;

# database credentials: EDIT HERE
my $ORASID = $ENV{ORACLE_SID};
my $ORAUSR = 'username';
my $ORAPWD = 'password';

my $DBC;

sub sql_execute {
   my ($sql,@arg) = @_;
   my $sth = $DBC->prepare($sql);
   if(!$sth || $sth->err) {
   confess "prepare failed for [$sql]\nbecause: [".$DBC->errstr."]";
   }
   $sth->execute(@arg) ||
   confess "exec failed:  [".$sth->errstr."]\nin query [$sql]";
   return $sth;
}

# connect to oracle
($DBC = DBI->connect("dbi:Oracle:$ORASID",$ORAUSR,$ORAPWD,
{
PrintError=>0,
AutoCommit=>0,
}
)) ||
   confess "failure connecting to $ORASID: ".$DBI::errstr;

# create one temporary table with one numeric column filled with test data
eval { sql_execute("DROP TABLE test_oracle_bug"); };
sql_execute("CREATE TABLE test_oracle_bug (DATA NUMBER)");
sql_execute("INSERT INTO test_oracle_bug (DATA) VALUES (1.73696)");
$DBC->commit;

# fetch numeric from table
my $ret = sql_execute("SELECT DATA FROM 
test_oracle_bug")->fetchrow_arrayref;

my ($val) = @$ret;

my $sum = 1.73696 - $val;
is($sum,0,"does sum $sum == 0?");




I don't want to get involved in the mechanics of your specific problem 
but as an aside if wanted to do what your example does I'd let the 
database work it out:


select data - ? from test_oracle_bug
$sth->execute("1.73696")

then all the maths is done at the database end.

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



Martin

Re: DBD::ODBC does not support bind_param_inout?

[Martin Evans]

hu.darren wrote:
> Hi,
>I have installed the DBD::ODBC from the latest svn.
> 
>   when I want execute a procedure like this:
> 
> 
> my $whoami = "";
> my $csr = $dbh->prepare(q{
> BEGIN
> :whoami := PLSQL_EXAMPLE_DARREN.FUNC_NP;
> END;
>   });

I presume since that starts PLSQL you are using Oracle.
Which Oracle ODBC driver are you using?

What does the procedure look like?

Are you sure this is a procedure - procedures don't usually return
values (especially in oracle).

The ODBC syntax to call a procedure is:

{call proc_name(?,?...)}

You are better using that.

Having said procedures don't usually return values MS SQL Server can
return an integer in which case you can use:

{? = call procname(?,?,...)}


> $csr->bind_param_inout(":whoami", \$whoami, 20);
> $csr->execute;
> print "Your database user name is $whoami\n";
> $dbh->disconnect;
> --
> 
> but I failed with:
> 
> 
> /usr/bin/perl -w /home/darren/perl/dbitest.pl
> Can't bind unknown placeholder ':whoami' at /home/darren/perl/dbitest.pl
> line 90.
> -
> 
> I have both used $dbh->{odbc_ignore_named_placeholders} = 1 and
> $dbh->{odbc_ignore_named_placeholders} = 1

These are the same aren't they!

> Any suggestions?

Why would you set odbc_ignore_named_placeholders - you are using named
placeholders so don't set it.

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

Re: float bug? perl 5.8, DBI and oracle 10.2.0

[Martin Evans]

Erwan Lemonnier wrote:

Hi Tim!


Ah, now it's getting interesting! :)


So I thought :)


Are the two versions of DBD::Oracle built against the same Oracle
version/installation?


No.
The one used by perl 5.6.2 was built against an oracle 9.2.1.0 (unsure
of the exact version, but it was a 9.*). The one used by perl 5.8.5
and perl 5.8.8 was built against an oracle 10.2.0.


What differences are there in the configuration of the two perl versions?


a diff between perl -V for my perl 5.6.2 and perl 5.8.8 shows the
following differences (the other options are in the 'perl -V' I
included in my very first post):

* 5.6.2:
osvers=2.6.9-42.0.2.elsmp
config_args='-Dprefix=/opt/perl-5.6.2'
useperlio=undef
ccflags ='-fno-strict-aliasing -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64'
cppflags='-fno-strict-aliasing'
libs=-lnsl -lgdbm -ldb -ldl -lm -lc -lcrypt -lutil
ccdlflags='-rdynamic'
Compile-time options: USE_LARGE_FILES

* 5.8.8:
osvers=2.6.9-55.elsmp
config_args=''
useperlio=define
bincompat5005=undef
ccflags ='-fno-strict-aliasing -pipe -Wdeclaration-after-statement
-I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64'
cppflags='-fno-strict-aliasing -pipe -Wdeclaration-after-statement
-I/usr/local/include'
libs=-lnsl -ldb -ldl -lm -lcrypt -lutil -lc
gnulibc_version='2.3.4'
ccdlflags='-Wl,-E'
Compile-time options: PERL_MALLOC_WRAP USE_LARGE_FILES USE_PERLIO



Take a look at the source for the Perl_sv_2nv() function in sv.c
in the two distributions.
Also check for differences in the perl config
items that impact that code.


Damn. I had a bad feeling I would end up there.
Ok, I'll put on my deep dive googles and switch on the tolkien
translator. If you don't hear from me within a week, call the asylum
to fetch me ;)


Try it with:

  my $v1 =  "1.73696";
  showbin($v1);
  require DBD::Oracle;
  my $v2 =  "1.73696";
  showbin($v2);


I get:

[HEAD] ~/HEAD/test/t/> !1030$ /opt/perl-5.6.2/bin/perl
02_test_require_dbd_oracle.t
0011100010101001011010010001101001110101110011010001
requiring
0011100010101001011010010001101001110101110011010001

[HEAD] ~/HEAD/test/t/> !1031$ /opt/perl-5.8.8/bin/perl
02_test_require_dbd_oracle.t
0011100010101001011010010001101001110101110011010001
requiring
0011100010101001011010010001101001110101110011010001

No difference. What triggered the problem happened during the connect,
but not during 'require DBD::Oracle'.



I know that on Solaris the act of loading the Oracle library (e.g.
libclntsh.so) could suck in other libraries which would cause
subtle side effects.

The particular case I (vagely) recall related to alarm(). Loading the
Oracle library caused the threaded version of alarm() to be loaded and
take precedence. So alarm(0) after loading DBD::Oracle wouldn't cancel
the alarm($timeout) set before loading it. Fun. I don't recall now
if/how that got resolved.

Anyway, I mention it because it seems like this is a similar issue.


Agggh. This example gives me goose flesh. I just hope I am not facing
something like that...

Is there anyone around with access to an oracle database 10.* and
running perl 5.8.* who could run the test code I provided earlier? The
one that just connects? It would be interesting to know how strongly
dependent it is to just my specific setup...



perl --version

This is perl, v5.8.8 built for i486-linux-gnu-thread-multi

select * from v$version
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE10.2.0.1.0  Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

cat x.pl

use DBI;
sub showbin {
print "bin: ".unpack("B70",reverse pack("d",$_[0]))."\n";
}
my $v1 =  "1.73696";
showbin($v1);
print "connecting\n";
my $DBC = DBI->connect("dbi:Oracle:XE",'XXX','YYY',
   { PrintError=>0, AutoCommit=>0 } );
my $v2 =  "1.73696";
showbin($v2);

perl x.pl

bin: 0011100010101001011010010001101001110101110011010001
connecting
bin: 001110001010100101101001000110100111010111001101

Hope this helps.

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

Re: error

[Martin Evans]

Samuel_Zheng wrote:

this is the error message, Because it on a Chinese 2003 server and the chinese 
is missing some bytes. I cannot read what it says. Does anyone can tell what it 
means and suggest things to try?
thanks,
samuel

Software error:
DBI connect('btn','',...) failed: [Microsoft][ODBC Microsoft Access Driver] uEz 
(SQL-42000)(DBD: db_login/SQLConnect err=-1) at C:\web\validate.pl line 130



Can't really tell much from that except it is a 42000 error which is 
"Syntax error or access violation" and the problem depends on what ODBC 
API you were calling (probably SQLPrepare/SQLExecDirect). You can find 
42000 description at:


http://www.easysoft.com/developer/interfaces/odbc/sqlstate_status_return_codes.html#42000

but I think you are going to have to dig deeper or learn Chinese.

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

Re: error

[Martin Evans]

Samuel_Zheng wrote:

Hi Martin,

I know Chinese. Just that it is not displayed properly Icannot read it. 
what more info is needed? Do you think.

I use the excel to open the ACCESS97 database and it was fine.
regards,
samuel


With a 42000 error should be some text describing the error - the syntax 
error in your SQL. If you can't get this you'll have to output the SQL 
when prepare fails and examine it to see what looks wrong. I can't 
really say anything more since you appear to have a syntax error but 
there is no associated description so the problem could be anything.


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

- Original Message - From: "Martin Evans" 
<[EMAIL PROTECTED]>

To: 
Sent: Wednesday, August 01, 2007 3:20 AM
Subject: Re: error



Samuel_Zheng wrote:
this is the error message, Because it on a Chinese 2003 server and 
the chinese is missing some bytes. I cannot read what it says. Does 
anyone can tell what it means and suggest things to try?

thanks,
samuel

Software error:
DBI connect('btn','',...) failed: [Microsoft][ODBC Microsoft Access 
Driver] uEz (SQL-42000)(DBD: db_login/SQLConnect err=-1) at 
C:\web\validate.pl line 130




Can't really tell much from that except it is a 42000 error which is 
"Syntax error or access violation" and the problem depends on what 
ODBC API you were calling (probably SQLPrepare/SQLExecDirect). You can 
find 42000 description at:


http://www.easysoft.com/developer/interfaces/odbc/sqlstate_status_return_codes.html#42000 



but I think you are going to have to dig deeper or learn Chinese.

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

__ NOD32 2430 (20070731) Information __

This message was checked by NOD32 antivirus system.
http://www.nod32.com.hk

why is ora_check_sql only a prepare attribute?

[Martin Evans]

I'm always looking to speed up my perl/DBI operations and noticed in 
DBD::Oracle ora_check_sql which is described under "prepare attributes" as:


If 1 (default), force SELECT statements to be described in prepare(). If 
0, allow SELECT statements to defer describe until execute().


Why is this only a prepare attribute? It would be much more useful if it 
could be set on the database handle and be inherited by statement 
handles as this would avoid having to set it on every prepare. As it 
stands I think it is no use to me unless I change every single prepare 
and then also run the risk of another DBD throwing my prepares out when 
they don't recognise the attribute whereas if it was on the database 
handle I could easily set it per DBD.


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

Re: DBI DBD-ODBC

[Martin Evans]

[EMAIL PROTECTED] wrote:
I created an MSACCESS Database on my Windows XP O/S,  set up the System 
Name in the DATA SOURCES panel associating the "Microsoft Access 
Driver(*.mdb)  with my database name (db1).   I installed the DBI Module 
and DBD-ODBC and I still get the following error when I run my PERL 
script..


Could not make a connection to the database: [Microsoft][ODBC Microsoft 
Access D
river]General error Not enough information to connect to this DSN with 
SQLConnec
t.   Use SQLDriverConnect. (SQL-HY000)(DBD: db_login/SQLConnect err=-1) at 
C:\Do

cuments and Settings\O015181\Desktop\Edit1.pl line 8.


If anyone knows the reason for this error and how I can fix it, I would 
appreciate it very much.  I am sending this email as a last resort as I 
have exhausted

all of my resources.

Regards,

Robert V Simmons



Did you use:

DBI->connect('dbi:ODBC:mydsn');

or

DBI->connect('dbi:ODBC:DSN=mydsn');

I would try the latter.

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

Re: DBI DBD-ODBC

[Martin Evans]

Samuel_Zheng wrote:

I am having almost the same error on a windows 2003 server.


I don't see this as the same. Robert is having a connection problem and 
you are having an execute problem.


Same set of 
scripts run fine on a Windows XP,

the following is the error:
DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver] 
囀湔祛堤 (SQL-22018)(DBD: st_execute/SQLExecute err=-1) at 
c:\web\factory.pl line 61.


As I said when you posted this before - What does "囀湔祛堤" mean in 
English? Error 22018 in access, is "Invalid character value for cast 
specification" which is described for SQLExecute as:


"StatementText contained a C type that was an exact or approximate 
numeric, a datetime, or an interval data type; the SQL type of the 
column was a character data type; and the value in the column was not a 
valid literal of the bound C type."


See 
http://www.easysoft.com/developer/interfaces/odbc/sqlstate_status_return_codes.html#22018


I would be suspicious of the "wkmaster.refno like '$ord_num%'"" in your 
code. What type is column refno, what is in $ord_num? What does an ODBC 
trace contain?



it failed at the line to execute after prepared the sql.I wish someone 
will shed some light on this. Thanks.I am using the format of 
DBI->connect('dbi:ODBC:mydsn');I think. What is the difference?


The difference is that DBD::ODBC calls the newer ODBC connection API 
SQLDriverConnect first then if this fails it attempts to use the older 
ODBC API SQLConnect. If you are using a DSN then omitting DSN= will 
cause SQLDriverConnect to attempt to connect using the DEFAULT data 
source which for most people does not exist and hence fails. When 
SQLDriverConnect fails DBD::ODBC will have a second attempt with the 
older ODBC API SQLConnect.


I believe this is historical in that initially:

DBI->connect('dbi:ODBC:mydsn_name', 'uid', 'pwd')

(before ODBC 3) used to call SQLConnect and works fine so long as the 
DSN mydsn_name exists but SQLConnect is less flexible than the newer 
SQLDriverConnect which supports new ODBC attributes and driver-specific

attributes which are required for say DSN-less connections.

the 
sample code as following:my ($dbh, $sth);my $dsn   = "dbi:ODBC:btn";my 
$user ="";my $pswrd ="";my $driver = "ODBC";my $wkno;use Time::gmtime;my 
$ltime=localtime(time);open(LOG,">>chkorder.log");print LOG 
"$ltime~~$ord_num~~\t$ENV{'REMOTE_ADDR'}\t$ENV{'HTTP_USER_AGENT'}\t$ENV{'REMOTE_HTTP_REFERER'}\n";close(LOG);$sql="Select 
refno, workno,prdcode,prdDesc,duedate from wkMaster WHERE  
wkmaster.refno like '$ord_num%'"; print $starter; $dbh = 
DBI->connect($dsn,$user,$pswrd,$driver,{RaiseError=>1}) or die "Database 
connection not made: $DBI::errstr"; $sth = $dbh->prepare($sql); 
$sth->execute();It failed at the last line shown above. I highly 
appreciate your help!Samuel


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

Re: DBI DBD-ODBC

[Martin Evans]

[EMAIL PROTECTED] wrote:


Yes that is the statement I am using in my PERL script.


Robert V Simmons



Can you run your script with DBI_TRACE set to a number >= 10

e.g.
set DBI_TRACE=10=x.log
myperlscript

then send me the x.log file.

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



*Martin Evans <[EMAIL PROTECTED]>*

08/15/2007 03:31 AM


To
dbi-users 
cc

Subject
Re: DBI DBD-ODBC








[EMAIL PROTECTED] wrote:
 > I created an MSACCESS Database on my Windows XP O/S,  set up the System
 > Name in the DATA SOURCES panel associating the "Microsoft Access
 > Driver(*.mdb)  with my database name (db1).   I installed the DBI Module
 > and DBD-ODBC and I still get the following error when I run my PERL
 > script..
 >
 > Could not make a connection to the database: [Microsoft][ODBC Microsoft
 > Access D
 > river]General error Not enough information to connect to this DSN with
 > SQLConnec
 > t.   Use SQLDriverConnect. (SQL-HY000)(DBD: db_login/SQLConnect 
err=-1) at

 > C:\Do
 > cuments and Settings\O015181\Desktop\Edit1.pl line 8.
 >
 >
 > If anyone knows the reason for this error and how I can fix it, I would
 > appreciate it very much.  I am sending this email as a last resort as I
 > have exhausted
 > all of my resources.
 >
 > Regards,
 >
 > Robert V Simmons
 >

Did you use:

DBI->connect('dbi:ODBC:mydsn');

or

DBI->connect('dbi:ODBC:DSN=mydsn');

I would try the latter.

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

Re: DBI DBD-ODBC

[Martin Evans]

Samuel_Zheng wrote:
Thank you Martin for your response. "囀湔祛堤" is not readable, no one 
knows what it means. refno is a string type and $ord_num is also string 
type contains number characters. e.g. "88090"
I just don't understand the same codes run on XP is ok but not on 
Windows 2003 server. the only thing I can think of is the permision 
issue. BUT proper rights are given to the a/c runs the script.


Samuel


So try and narrow it down. Does it happen on all values of refno or just 
one particular one? Does it still happen if you remove the where clause 
from your select statement altogether. If it is none of that start 
taking columns out the select - start with duedate.


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

- Original Message - From: "Martin Evans" 
<[EMAIL PROTECTED]>

To: "dbi-users" 
Sent: Wednesday, August 15, 2007 8:26 AM
Subject: Re: DBI DBD-ODBC



Samuel_Zheng wrote:

I am having almost the same error on a windows 2003 server.


I don't see this as the same. Robert is having a connection problem 
and you are having an execute problem.



Same set of scripts run fine on a Windows XP,
the following is the error:
DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access 
Driver] 囀湔祛堤 (SQL-22018)(DBD: st_execute/SQLExecute err=-1) at 
c:\web\factory.pl line 61.


As I said when you posted this before - What does "囀湔祛堤" mean in 
English? Error 22018 in access, is "Invalid character value for cast 
specification" which is described for SQLExecute as:


"StatementText contained a C type that was an exact or approximate 
numeric, a datetime, or an interval data type; the SQL type of the 
column was a character data type; and the value in the column was not 
a valid literal of the bound C type."


See 
http://www.easysoft.com/developer/interfaces/odbc/sqlstate_status_return_codes.html#22018 



I would be suspicious of the "wkmaster.refno like '$ord_num%'"" in 
your code. What type is column refno, what is in $ord_num? What does 
an ODBC trace contain?



it failed at the line to execute after prepared the sql.I wish 
someone will shed some light on this. Thanks.I am using the format of 
DBI->connect('dbi:ODBC:mydsn');I think. What is the difference?


The difference is that DBD::ODBC calls the newer ODBC connection API 
SQLDriverConnect first then if this fails it attempts to use the older 
ODBC API SQLConnect. If you are using a DSN then omitting DSN= will 
cause SQLDriverConnect to attempt to connect using the DEFAULT data 
source which for most people does not exist and hence fails. When 
SQLDriverConnect fails DBD::ODBC will have a second attempt with the 
older ODBC API SQLConnect.


I believe this is historical in that initially:

DBI->connect('dbi:ODBC:mydsn_name', 'uid', 'pwd')

(before ODBC 3) used to call SQLConnect and works fine so long as the 
DSN mydsn_name exists but SQLConnect is less flexible than the newer 
SQLDriverConnect which supports new ODBC attributes and driver-specific

attributes which are required for say DSN-less connections.

the sample code as following:my ($dbh, $sth);my $dsn   = 
"dbi:ODBC:btn";my $user ="";my $pswrd ="";my $driver = "ODBC";my 
$wkno;use Time::gmtime;my 
$ltime=localtime(time);open(LOG,">>chkorder.log");print LOG 
"$ltime~~$ord_num~~\t$ENV{'REMOTE_ADDR'}\t$ENV{'HTTP_USER_AGENT'}\t$ENV{'REMOTE_HTTP_REFERER'}\n";close(LOG);$sql="Select 
refno, workno,prdcode,prdDesc,duedate from wkMaster WHERE  
wkmaster.refno like '$ord_num%'"; print $starter; $dbh = 
DBI->connect($dsn,$user,$pswrd,$driver,{RaiseError=>1}) or die 
"Database connection not made: $DBI::errstr"; $sth = 
$dbh->prepare($sql); $sth->execute();It failed at the last line shown 
above. I highly appreciate your help!Samuel


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

__ NOD32 2463 (20070815) Information __

This message was checked by NOD32 antivirus system.
http://www.nod32.com.hk

Re: Cannot see / enumerate DSN ODBC using perl in Windows

[Martin Evans]

[EMAIL PROTECTED] wrote:

Hi,

I've moved my working W2k3 Activestate Perl 5.8 system to another
server, but now, when I list my ODBC data sources (my @dsns = DBI-

data_sources('ODBC')) I get a blank, not my system DSN (which IS

configured and working), which DOES appear using the same code on my
old system, urmmm?

I'm assuming that this is a windows security/permissions issue, in
that the account running the perl script does not have access to the
ODBC system DSNs. I have absolutley no idea where to permission this
up, as there appears to be no ODBC/SYSTEM DSN service, or special
security group I can find, any ideas???

Thanks

Ed ([EMAIL PROTECTED])




Are you saying that when you logon to your 2003 box as user X you can 
run up the odbc administrator and see a SYSTEM DSN , you can run another 
app that uses that SYSTEM DSN but Perl's data_sources run as the same 
user does not return the DSN?


Unless there is something under 2003 which associates certain 
permissions with executables I cannot see how the system would 
differentiate between user X running non-perl apps and perl. Of course 
there could be a bug in DBD::ODBC (looking now I see it does not report 
any errors but would just return an empty list if it errored).


What is the name of the SYSTEM DSN you have created?

Assuming nothing else occurs, if I sent you some C code (and/or an 
executable) could you compile/run it on this machine? It would simply be 
calls to SQLDataSources with error reporting.


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

Re: DBI DBD-ODBC

[Martin Evans]

Samuel_Zheng wrote:

Martin,

I did as you suggested and it ran! But taking out the where clause makes 
the result not meaningful.
I use the 'like' word is to include the records begin with '88090'. Some 
records may have some Chinese characters after '88090'. Is there another 
way of doing it? Thanks.


I realised why you were using the like clause but by removing it and 
seeing the problem go away we now know the problem is with the argument 
to the where clause. I still believe you are putting something in 
$ord_num which the driver does not like the fact you are using '%' when 
if you are using MS Access it should be '*'.


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


Samuel

- Original Message - From: "Martin Evans" 
<[EMAIL PROTECTED]>

To: "dbi-users" 
Sent: Wednesday, August 15, 2007 10:59 AM
Subject: Re: DBI DBD-ODBC



Samuel_Zheng wrote:
Thank you Martin for your response. "囀湔祛堤" is not readable, no 
one knows what it means. refno is a string type and $ord_num is also 
string type contains number characters. e.g. "88090"
I just don't understand the same codes run on XP is ok but not on 
Windows 2003 server. the only thing I can think of is the permision 
issue. BUT proper rights are given to the a/c runs the script.


Samuel


So try and narrow it down. Does it happen on all values of refno or 
just one particular one? Does it still happen if you remove the where 
clause from your select statement altogether. If it is none of that 
start taking columns out the select - start with duedate.


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

- Original Message - From: "Martin Evans" 
<[EMAIL PROTECTED]>

To: "dbi-users" 
Sent: Wednesday, August 15, 2007 8:26 AM
Subject: Re: DBI DBD-ODBC



Samuel_Zheng wrote:

I am having almost the same error on a windows 2003 server.


I don't see this as the same. Robert is having a connection problem 
and you are having an execute problem.



Same set of scripts run fine on a Windows XP,
the following is the error:
DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access 
Driver] 囀湔祛堤 (SQL-22018)(DBD: st_execute/SQLExecute err=-1) at 
c:\web\factory.pl line 61.


As I said when you posted this before - What does "囀湔祛堤" mean in 
English? Error 22018 in access, is "Invalid character value for cast 
specification" which is described for SQLExecute as:


"StatementText contained a C type that was an exact or approximate 
numeric, a datetime, or an interval data type; the SQL type of the 
column was a character data type; and the value in the column was 
not a valid literal of the bound C type."


See 
http://www.easysoft.com/developer/interfaces/odbc/sqlstate_status_return_codes.html#22018 



I would be suspicious of the "wkmaster.refno like '$ord_num%'"" in 
your code. What type is column refno, what is in $ord_num? What does 
an ODBC trace contain?



it failed at the line to execute after prepared the sql.I wish 
someone will shed some light on this. Thanks.I am using the format 
of DBI->connect('dbi:ODBC:mydsn');I think. What is the difference?


The difference is that DBD::ODBC calls the newer ODBC connection API 
SQLDriverConnect first then if this fails it attempts to use the 
older ODBC API SQLConnect. If you are using a DSN then omitting DSN= 
will cause SQLDriverConnect to attempt to connect using the DEFAULT 
data source which for most people does not exist and hence fails. 
When SQLDriverConnect fails DBD::ODBC will have a second attempt 
with the older ODBC API SQLConnect.


I believe this is historical in that initially:

DBI->connect('dbi:ODBC:mydsn_name', 'uid', 'pwd')

(before ODBC 3) used to call SQLConnect and works fine so long as 
the DSN mydsn_name exists but SQLConnect is less flexible than the 
newer SQLDriverConnect which supports new ODBC attributes and 
driver-specific

attributes which are required for say DSN-less connections.

the sample code as following:my ($dbh, $sth);my $dsn   = 
"dbi:ODBC:btn";my $user ="";my $pswrd ="";my $driver = "ODBC";my 
$wkno;use Time::gmtime;my 
$ltime=localtime(time);open(LOG,">>chkorder.log");print LOG 
"$ltime~~$ord_num~~\t$ENV{'REMOTE_ADDR'}\t$ENV{'HTTP_USER_AGENT'}\t$ENV{'REMOTE_HTTP_REFERER'}\n";close(LOG);$sql="Select 
refno, workno,prdcode,prdDesc,duedate from wkMaster WHERE 
wkmaster.refno like '$ord_num%'"; print $starter; $dbh = 
DBI->connect($dsn,$user,$pswrd,$driver,{RaiseError=>1}) or die 
"Database connection not made: $DBI::errstr"; $sth = 
$dbh->prepare($sql); $sth->execute();It failed at the last line 
shown above. I highly appreciate your help!Samuel


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

__ NOD32 2463 (20070815) Information __

This message was checked by NOD32 antivirus system.
http://www.nod32.com.hk







__ NOD32 2463 (20070815) Information __

This message was checked by NOD32 antivirus system.
http://www.nod32.com.hk

Re: DBI DBD-ODBC

[Martin Evans]

Samuel_Zheng wrote:

Thank you Martin. Do you mean I should be using '*' for MSAccess file?


Yes.

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

- Original Message ----- From: "Martin Evans" 
<[EMAIL PROTECTED]>

To: "dbi-users" 
Sent: Monday, August 20, 2007 4:43 AM
Subject: Re: DBI DBD-ODBC



Samuel_Zheng wrote:

Martin,

I did as you suggested and it ran! But taking out the where clause 
makes the result not meaningful.
I use the 'like' word is to include the records begin with '88090'. 
Some records may have some Chinese characters after '88090'. Is there 
another way of doing it? Thanks.


I realised why you were using the like clause but by removing it and 
seeing the problem go away we now know the problem is with the 
argument to the where clause. I still believe you are putting 
something in $ord_num which the driver does not like the fact you are 
using '%' when if you are using MS Access it should be '*'.


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


Samuel

- Original Message - From: "Martin Evans" 
<[EMAIL PROTECTED]>

To: "dbi-users" 
Sent: Wednesday, August 15, 2007 10:59 AM
Subject: Re: DBI DBD-ODBC



Samuel_Zheng wrote:
Thank you Martin for your response. "囀湔祛堤" is not readable, no 
one knows what it means. refno is a string type and $ord_num is 
also string type contains number characters. e.g. "88090"
I just don't understand the same codes run on XP is ok but not on 
Windows 2003 server. the only thing I can think of is the permision 
issue. BUT proper rights are given to the a/c runs the script.


Samuel


So try and narrow it down. Does it happen on all values of refno or 
just one particular one? Does it still happen if you remove the 
where clause from your select statement altogether. If it is none of 
that start taking columns out the select - start with duedate.


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

- Original Message - From: "Martin Evans" 
<[EMAIL PROTECTED]>

To: "dbi-users" 
Sent: Wednesday, August 15, 2007 8:26 AM
Subject: Re: DBI DBD-ODBC



Samuel_Zheng wrote:

I am having almost the same error on a windows 2003 server.


I don't see this as the same. Robert is having a connection 
problem and you are having an execute problem.



Same set of scripts run fine on a Windows XP,
the following is the error:
DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access 
Driver] 囀湔祛堤 (SQL-22018)(DBD: st_execute/SQLExecute err=-1) 
at c:\web\factory.pl line 61.


As I said when you posted this before - What does "囀湔祛堤" mean 
in English? Error 22018 in access, is "Invalid character value for 
cast specification" which is described for SQLExecute as:


"StatementText contained a C type that was an exact or approximate 
numeric, a datetime, or an interval data type; the SQL type of the 
column was a character data type; and the value in the column was 
not a valid literal of the bound C type."


See 
http://www.easysoft.com/developer/interfaces/odbc/sqlstate_status_return_codes.html#22018 



I would be suspicious of the "wkmaster.refno like '$ord_num%'"" in 
your code. What type is column refno, what is in $ord_num? What 
does an ODBC trace contain?



it failed at the line to execute after prepared the sql.I wish 
someone will shed some light on this. Thanks.I am using the 
format of DBI->connect('dbi:ODBC:mydsn');I think. What is the 
difference?


The difference is that DBD::ODBC calls the newer ODBC connection 
API SQLDriverConnect first then if this fails it attempts to use 
the older ODBC API SQLConnect. If you are using a DSN then 
omitting DSN= will cause SQLDriverConnect to attempt to connect 
using the DEFAULT data source which for most people does not exist 
and hence fails. When SQLDriverConnect fails DBD::ODBC will have a 
second attempt with the older ODBC API SQLConnect.


I believe this is historical in that initially:

DBI->connect('dbi:ODBC:mydsn_name', 'uid', 'pwd')

(before ODBC 3) used to call SQLConnect and works fine so long as 
the DSN mydsn_name exists but SQLConnect is less flexible than the 
newer SQLDriverConnect which supports new ODBC attributes and 
driver-specific

attributes which are required for say DSN-less connections.

the sample code as following:my ($dbh, $sth);my $dsn   = 
"dbi:ODBC:btn";my $user ="";my $pswrd ="";my $driver = "ODBC";my 
$wkno;use Time::gmtime;my 
$ltime=localtime(time);open(LOG,">>chkorder.log");print LOG 
"$ltime~~$ord_num~~\t$ENV{'REMOTE_ADDR'}\t$ENV{'HTTP_USER_AGENT'}\t$ENV{'REMOTE_HTTP_REFERER'}\n";close(LOG);$sql="Select 
refno, workno,prdcode,prdDesc

Re: TWO_TASK environment variable

[Martin Evans]

John Scoles wrote:

isn't TWO_TASK  only used when you have SQL*NET installed??


Yes.


I do not think Instant-client installs that??


$ strings libclntsh.so | grep sqlnet.ora
Getting the path of sqlnet.ora
Failure in reading sqlnet.ora parameter %s

so I think it is in libclntsh.so.

AFAIK, I need SQL*Net to be able to communicate with a remote Oracle 
database. I am communicating with a remote Oracle database and I only 
have instant client installed.


I will get onto one of my Oracle DBA types later to day and ask that.  
Might

have to change the POD a bit to reflect that


Yes, that was my point. As it stands the instructions for using TWO_TASK 
in the pod don't appear to work with Oracle 10 (either instant client or 
full client).


... and whilst I'm posting here I noticed the README says:
  Build, test and install the DBI module (at least DBI 1.21).
but Makefile.PL says:
use DBI 1.28

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


- Original Message - From: "Martin J. Evans" <[EMAIL PROTECTED]>
To: 
Cc: "tom r" <[EMAIL PROTECTED]>
Sent: Thursday, September 06, 2007 5:47 AM
Subject: Re: TWO_TASK environment variable


I was playing with TWO_TASK myself this morning and like Tom, I cannot 
make

the advice in DBD::Oracle work with Instant Client 10.2.0.3. I wonder if
the description of TWO_TASK is correct or perhaps out of date wrt to 
newer

Oracle versions. See below:

tom r wrote:
reading man DBD::Oracle gives an in depth explanation on how the 
TWO_TASK

environment variable can be used.  however, when i tried to use it in
place of .tnsnames.ora nothing worked.  i believe that sql*plus does not
use TWO_TASK.

-
[~] cat .tnsnames.ora
infra=(description=(address=(protocol=tcp)(host=infradb.fs.usda.gov)
(port=1521))(connect_data=(sid=iwebdb)))
[~] sqlplus 'fs_public/[EMAIL PROTECTED]'

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Apr 11 10:36:04 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit
Production
With the Partitioning, OLAP and Data Mining options

SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release
10.1.0.5.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[~] printenv | grep TWO
TWO_TASK=T:infradb.fs.usda.gov:iwebdb


Try as I might, I cannot make T:*** work either. What does work for me 
is:


TWO_TASK=//xxx.easysoft.local:1521/ORACLE_SID

but none of:

TWO_TASK=T:xxx.easysoft.local:1521:ORACLE_SID
TWO_TASK=T:xxx.easysoft.local:ORACLE_SID
TWO_TASK=T:xxx.easysoft.local/ORACLE_SID

so for a totally empty DBI->connect call (and using instant client) I 
have

the following working:

DBI_DSN=dbi:Oracle:
DBI_USER=xxx
DBI_PASS=yyy
LD_LIBRARY_PATH=/home/martin/instantclient_10_2
TWO_TASK=//xxx.easysoft.local:1521/ORACLE_SID

perl -e 'use DBI; $h = DBI->connect();'


[~] sqlplus 'fs_public/fs_public'







in addition, it appears sql*plus doesn't even allow one to specify the
connection parameters on the command line.


That is also what I found which makes me think the syntax suggested by
DBD::Oracle POD for TWO_TASK may no longer be applicable for Oracle 10.


-
[~] sqlplus 'fs_public/[EMAIL PROTECTED]/sid=iwebdb'


For me what works using your values would be:

sqlplus 'fs_public/fs_public@//infradb.fs.usda.gov/iwebdb'




-

unless i'm missing something here, it seems sql*plus must use the
.tnsnames.ora file.


No, I don't have a tnsnames.ora file but I don't use the syntax mentioned
in DBD::Oracle.

From what I can find on the net, it may be that the syntax "T:", "P:" (in
fact a letter followed by a colon) at the start of the connect string
tells SQL*Net to use V1 and since Oracle 7.3, SQL*Net V1 is no longer
supported.

This is not the first time someone has failed to use TWO_TASK following
the directions in DBD::Oracle. Although I cannot be sure of the SQL*Net
V1/V2 changes in syntax I can say that the syntax I show above does work
with sqlplus and DBD::Oracle since Oracle 10. I suggest the DBD::Oracle
POD is changed and I am happy to provide a patch against svn tree.

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

Re: DBI-1.59 install failure on Ubuntu. lacking......compiler complains of missing files

[Martin Evans]

NN NN wrote:

Hi!

I can't install DBI-1.59 on ubuntu 7.04 with 
This is perl, v5.8.8 built for

i486-linux-gnu-thread-multi
Seems like ubuntu lacks some files. but which?
Can't install through cpan or synaptic either.

After downloading DBI-1.59, the 'Makefile.PL' runs
fine, but When trying to 'make' I get this:

cc -c   -D_REENTRANT -D_GNU_SOURCE -DTHREADS_HAVE_PIDS
-DDEBIAN -fno-strict-aliasing -pipe
-I/usr/local/include -D_LARGEFILE_SOURCE
-D_FILE_OFFSET_BITS=64 -O2   -DVERSION=\"1.59\"
-DXS_VERSION=\"1.59\" -fPIC "-I/usr/lib/perl/5.8/CORE"
 -W -Wall -Wpointer-arith -Wbad-function-cast
-Wno-comment -Wno-sign-compare -Wno-cast-qual
-Wmissing-noreturn -Wno-unused-parameter Perl.c
In file included from DBIXS.h:19,
 from Perl.xs:6:
/usr/lib/perl/5.8/CORE/perl.h:420:24: error:
sys/types.h: No such file or directory
/usr/lib/perl/5.8/CORE/perl.h:451:19: error: ctype.h:
No such file or directory
/usr/lib/perl/5.8/CORE/perl.h:463:23: error: locale.h:
No such file or directory
/usr/lib/perl/5.8/CORE/perl.h:480:20: error: setjmp.h:
No such file or directory
/usr/lib/perl/5.8/CORE/perl.h:486:26: error:
sys/param.h: No such file or directory
/usr/lib/perl/5.8/CORE/perl.h:491:23: error: stdlib.h:
No such file or directory
/usr/lib/perl/5.8/CORE/perl.h:496:23: error: unistd.h:
No such file or directory
/usr/lib/perl/5.8/CORE/perl.h:776:23: error: string.h:






What should I install?

Thanks!


  





Looks like you need to install the C header files - that is probably a 
package something like libc development/headers?.


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

Re: SV: Re: DBI-1.59 install failure on Ubuntu. lacking......compiler complains of missing files

[Martin Evans]

NN NN wrote:

I have tried to install some libc packages, but no
luck.
I guess I have to sign up for a ubuntu mailing list
too, but thanks for replying.



I believe you need libc-dev. Basically you need to find the package with 
the file stdlib.h in it.


Martin


--- Martin Evans <[EMAIL PROTECTED]> skrev:


NN NN wrote:

Hi!

I can't install DBI-1.59 on ubuntu 7.04 with 
This is perl, v5.8.8 built for

i486-linux-gnu-thread-multi
Seems like ubuntu lacks some files. but which?
Can't install through cpan or synaptic either.

After downloading DBI-1.59, the 'Makefile.PL' runs
fine, but When trying to 'make' I get this:

cc -c   -D_REENTRANT -D_GNU_SOURCE

-DTHREADS_HAVE_PIDS

-DDEBIAN -fno-strict-aliasing -pipe
-I/usr/local/include -D_LARGEFILE_SOURCE
-D_FILE_OFFSET_BITS=64 -O2   -DVERSION=\"1.59\"
-DXS_VERSION=\"1.59\" -fPIC

"-I/usr/lib/perl/5.8/CORE"

 -W -Wall -Wpointer-arith -Wbad-function-cast
-Wno-comment -Wno-sign-compare -Wno-cast-qual
-Wmissing-noreturn -Wno-unused-parameter Perl.c
In file included from DBIXS.h:19,
 from Perl.xs:6:
/usr/lib/perl/5.8/CORE/perl.h:420:24: error:
sys/types.h: No such file or directory
/usr/lib/perl/5.8/CORE/perl.h:451:19: error:

ctype.h:

No such file or directory
/usr/lib/perl/5.8/CORE/perl.h:463:23: error:

locale.h:

No such file or directory
/usr/lib/perl/5.8/CORE/perl.h:480:20: error:

setjmp.h:

No such file or directory
/usr/lib/perl/5.8/CORE/perl.h:486:26: error:
sys/param.h: No such file or directory
/usr/lib/perl/5.8/CORE/perl.h:491:23: error:

stdlib.h:

No such file or directory
/usr/lib/perl/5.8/CORE/perl.h:496:23: error:

unistd.h:

No such file or directory
/usr/lib/perl/5.8/CORE/perl.h:776:23: error:

string.h:




What should I install?

Thanks!


  




Looks like you need to install the C header files -
that is probably a 
package something like libc development/headers?.


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






  






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

Very slow executes with utf8 integer parameters in DBD::Oracle

[Martin Evans]

I have NLS_LANG=AMERICAN_AMERICA.AL32UTF8 as recommended in DBD::Oracle 
when using utf8 and I need to as my data is utf8 in Perl.


Grossly simplified my code does:

o select integer_primary_key_field from table
o prepare(select from another_table where field = ?)
o execute($inter_primary_key_value_retrieved_from_select)
  This query is vastly more complex than this really

Even though the field retrieved from the first table is an integer when 
I look at it, Perl has utf8 flag set. When these utf8 encoded integers 
are then passed into the execute for a select on another table the 
execute takes 0.7s. Now that may not sound a lot to you but this query 
gets runs a lot. If I downgrade the integer parameter with 
utf8::downgrade before passing it to execute the execute takes 0.01s.


When I look at the QEP the filter contains a TO_NUMBER(:p1) and I think 
Oracle has decided it cannot use an index on the column.


I tried binding the parameter as ora_number but that does not help. The 
only thing which seems to work is to downgrade the parameter from utf8.


Any ideas?

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

Re: Very slow executes with utf8 integer parameters in DBD::Oracle

[Martin Evans]

Peter J. Holzer wrote:

On 2007-09-14 18:03:15 +0100, Martin Evans wrote:
I have NLS_LANG=AMERICAN_AMERICA.AL32UTF8 as recommended in DBD::Oracle when 
using utf8 and I need to as my data is utf8 in Perl.


Grossly simplified my code does:

o select integer_primary_key_field from table
o prepare(select from another_table where field = ?)
o execute($inter_primary_key_value_retrieved_from_select)
  This query is vastly more complex than this really

Even though the field retrieved from the first table is an integer when I look 
at it, Perl has utf8 flag set. When these utf8 encoded integers are then passed 
into the execute for a select on another table the execute takes 0.7s. Now that 
may not sound a lot to you but this query gets runs a lot. If I downgrade the 
integer parameter with utf8::downgrade before passing it to execute the execute 
takes 0.01s.


When I look at the QEP the filter contains a TO_NUMBER(:p1) and I think Oracle 
has decided it cannot use an index on the column.


I tried binding the parameter as ora_number but that does not help. The only 
thing which seems to work is to downgrade the parameter from utf8.


Any ideas?


This may be the same problem I ran into a few weeks ago. See
http://www.mail-archive.com/dbi-users@perl.org/msg30138.html

I have a patch for this but I still haven't gotten around to testing it,
so even though it's only a few lines I don't want to post it yet. Feel
free to contact me off-list if you want to try it.

The workaround which I actually use in production code is to set
NLS_NCHAR=US7ASCII. Of course this isn't a good idea if you have
nvarchar2 or nclob columns. Explicitely binding with the correct cs_form
also works:

$sth->bind_param(1, $name, { ora_csform => SQLCS_IMPLICIT });


hp



Thank you Peter. Rather stupidly, I had marked the post your refer to as 
particularly noteworthy but forgot I'd seen it.


My time for the query has come down from at best .7s (some were a lot 
worse) to 0.035s - a huge difference. All I've done to achieve this is:


1) bound the select columns which are integers as ORA_NUMBER (which I 
don't think is having an real affect as the numbers I get back are still 
marked utf-8).


2) added SQLCS_IMPLICIT to the bound parameters which are numbers (keys 
in my case).


Like you found, when I look at the QEP, I find Oracle is doing a lot of 
different things now including the creation of a view and use of a index 
it was not using before.


All my data is utf8 so this problem probably exists elsewhere as well. 
Does anyone know what the disadvantage of changing DBD::Oracle to 
default to SQLCS_IMPLICIT instead of SQLCS_NCHAR is?


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

Re: Looking for a good explanation of select function in perl dbi

[Martin Evans]

Scott Mohnkern wrote:

Well, I've figured out how to put data into a sqlite database.  I can
extract data from the command line, however, for the life of me, I just
can't find any good documentation on using Select.

What I'm trying to do:

SELECT name, size FROM filelist where uid=

This works from the command line:

sqlite3 data.dbl "SELECT name, uid from filelist where UID=\"1000\""

I'd like to pop this into an array, where I could manipulate it, where each
element of the array would contain name,size.


Read about DBI's selectall_arrayref in the pod as it returns a ref to an 
array of arrays as you want. Alternatively, do the fetching yourself 
with fetchrow_arrayref.


Use bound parameters in your select SQL:
SELECT name, uid from filelist where UID=?
- see DBI's bind_param.


Are there any good web pages on using the SELECT function?  I took a look
at:

http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html

But it kind of lost me.

Scott Mohnkern



I'm not sure if you want help on use of select in SQL or obtaining 
result sets created by select in your Perl. The following link (if you 
scroll down past the ODBC stuff) does the latter:


http://www.easysoft.com/developer/languages/perl/dbd_odbc_tutorial_part_2.html

There are literally loads of SQL tutorials on the web including the 
w3schools one at http://www.w3schools.com/sql/default.asp. You may have 
to refer back to sqllite to check what syntax it supports - I don't use 
sqllite so I don't know.



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

Re: DBD::Oracle 1.19 fails to find Oracle version, problem and solution

[Martin Evans]

John Scoles wrote:
Yes it is a rather delicate subject as if we make it work for one it 
will break for everyone else.


Don't agree in this case since a) the code is already not working and b) 
the change only comes into play if sqlplus fails with SP2-0750.


The real root of the problem is that Oracle in its divine wisdom has 
compacted all the .so files into one so there is no way you c compiler 
can get them but of SQL on the Ubuntu Server it could be the case that 
the Makefile fort 1.19 is not recognizing it as a Linux server or alike. 


We could try to debug it but I would need access to your server for an 
hour or so or if you have the time we can debug it together as I can 
send you a Makefile.PL with a little more debugging into?  I have time 
tomorrow if you are interested Martin




I am happy to anything to help now, tomorrow or whenever. However, I 
don't understand your response since the problem I am seeing is that 
sqlplus will NOT run if ORACLE_HOME is defined and it does not seem 
related to combining libraries into one. The actual code failing 
(omitting comments) is:


sub get_client_version {
my ($force_version) = @_;
my $client_version_full = '';
my $sqlplus_exe = ($os eq 'Win32' || $os eq 'MSWin32' || $os eq 
'cygwin') ? "sqlplus.exe" : "sqlplus";

my $OH_path = $OH;
chomp($OH_path = `/usr/bin/cygpath -u $OH_path`) if $os eq 'cygwin' 
&& $OH;
local $ENV{PATH} = join $Config{path_sep}, "$OH_path/bin", 
$OH_path, $ENV{PATH} if $OH;

print "PATH=$ENV{PATH}\n" if $::opt_v;

if (find_bin($sqlplus_exe)) {

# code gets here since find_bin finds sqlplus on my path

local $ENV{SQLPATH} = "";
open FH, ">define.sql" or warn "Can't create define.sql: $!";
print FH "DEFINE _SQLPLUS_RELEASE\nQUIT\n";
my $sqlplus_release = `$sqlplus_exe -S /nolog [EMAIL PROTECTED] 2>&1`;
unlink "define.sql";
print $sqlplus_release; # the _SQLPLUS_RELEASE may not be on 
first line:
if ($sqlplus_release =~ /DEFINE _SQLPLUS_RELEASE = 
"(\d?\d)(\d\d)(\d\d)(\d\d)(\d\d)"/) {


# code does not get here since sqlplus returned an error
# instead of the version. It returns SP2-0750 because
# when it sees ORACLE_HOME is defined it expects to find
# message files which are not present with instant client
$client_version_full = sprintf("%d.%d.%d.%d", $1, $2, $3, $4);
}

My change (although ugly) should not break anyone else because all it 
does is re-run sqlplus with ORACLE_HOME temporarily deleted from 
environment IF sqlplus returns the error SP2-0750. i.e., I insert into 
above before "unlink":


if ($sqlplus_release =~ /SP2-0750/) {
my $x = $ENV{ORACLE_HOME};
delete $ENV{ORACLE_HOME};
$sqlplus_release = `$sqlplus_exe -S /nolog [EMAIL PROTECTED] 
2>&1`;

$ENV{ORACLE_HOME} = $x;
}

After this minor addition it works fine.

If you still want me to run anything on my machine let me know. I cannot 
provide access to it from outside our network but I can run anything you 
like.


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


Martin J. Evans wrote:

Hi,

Just attempted to install DBD::Oracle 1.19 on an Ubuntu Server 7.04 
Feisty machine running Perl v5.8.8 (and instant client 
10.2.0.3-20061115) and hit a problem with the Makefile.PL not being 
able to determine my Oracle version. The symptom is:


==
WARNING: Setting ORACLE_HOME env var to /home/XXX/instantclient_10_2/ 
for you.
WARNING: The tests will probably fail unless you set ORACLE_HOME 
yourself!

Using Oracle in /home/XXX/instantclient_10_2/
Error 6 initializing SQL*Plus
Message file sp1.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software 
directory




I'm having trouble finding your Oracle version number... trying harder

WARNING: I could not determine Oracle client version so I'll just
default to version 8.0.0.0. Some features of DBD::Oracle may not work.
Oracle version based logic in Makefile.PL may produce erroneous results.
You can use "perl Makefile.PL -V X.Y.Z" to specify a your client version.

Oracle version 8.0.0.0 (8.0)
Looks like an Instant Client installation, okay
==

The real problem is that if you set ORACLE_HOME (which Makefile.PL 
does) sqlplus does not work e.g.,


# echo $ORACLE_HOME
# ./instantclient_10_2/sqlplus -S /nolog \@/tmp/x.sql 2>&1
DEFINE _SQLPLUS_RELEASE = "1002000300" (CHAR)
# ORACLE_HOME=/home/XXX/instantclient_10_2/ 
./instantclient_10_2/sqlplus -S /nolog \@/tmp/x.sql 2>&1

Error 6 initializing SQL*Plus
Message file sp1.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software 
directory


Contrary to the SP2-0750 error and description, Oracle's advice (on 
their web page) for using InstantClient is NOT to set ORACLE_HOME but 
to set LD_LIBRARY_PATH (or configure the dynamic linker using ldconfig 
etc). I can't reme

Re: DBD::Oracle 1.19 fails to find Oracle version, problem and solution

[Martin Evans]

Apologies to anyone attempting to answer me directly on the original 
post, I accidentally broke my From address which should be as in this 
posting.


Martin J. Evans wrote:

Hi,

Just attempted to install DBD::Oracle 1.19 on an Ubuntu Server 7.04 
Feisty machine running Perl v5.8.8 (and instant client 
10.2.0.3-20061115) and hit a problem with the Makefile.PL not being able 
to determine my Oracle version. The symptom is:


==
WARNING: Setting ORACLE_HOME env var to /home/XXX/instantclient_10_2/ 
for you.

WARNING: The tests will probably fail unless you set ORACLE_HOME yourself!
Using Oracle in /home/XXX/instantclient_10_2/
Error 6 initializing SQL*Plus
Message file sp1.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory



I'm having trouble finding your Oracle version number... trying harder

WARNING: I could not determine Oracle client version so I'll just
default to version 8.0.0.0. Some features of DBD::Oracle may not work.
Oracle version based logic in Makefile.PL may produce erroneous results.
You can use "perl Makefile.PL -V X.Y.Z" to specify a your client version.

Oracle version 8.0.0.0 (8.0)
Looks like an Instant Client installation, okay
==

The real problem is that if you set ORACLE_HOME (which Makefile.PL does) 
sqlplus does not work e.g.,


# echo $ORACLE_HOME
# ./instantclient_10_2/sqlplus -S /nolog \@/tmp/x.sql 2>&1
DEFINE _SQLPLUS_RELEASE = "1002000300" (CHAR)
# ORACLE_HOME=/home/XXX/instantclient_10_2/ ./instantclient_10_2/sqlplus 
-S /nolog \@/tmp/x.sql 2>&1

Error 6 initializing SQL*Plus
Message file sp1.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

Contrary to the SP2-0750 error and description, Oracle's advice (on 
their web page) for using InstantClient is NOT to set ORACLE_HOME but to 
set LD_LIBRARY_PATH (or configure the dynamic linker using ldconfig 
etc). I can't remember now exactly what the issue was but I've had other 
problems using instant client with ORACLE_HOME set before. I don't think 
DBD::Oracle's Makefile.PL should set ORACLE_HOME if it detects instant 
client however, it is so ingrained in the Makefile.PL it might 
compromise some other installation path to remove it. As a workaround (I 
know not pretty) I changed Makefile.PL in get_client_version() around 
line 1473 as follows:


open FH, ">define.sql" or warn "Can't create define.sql: $!";
print FH "DEFINE _SQLPLUS_RELEASE\nQUIT\n";
close FH;
my $sqlplus_release = `$sqlplus_exe -S /nolog [EMAIL PROTECTED] 2>&1`;
# +MJE
if ($sqlplus_release =~ /SP2-0750/) {
my $x = $ENV{ORACLE_HOME};
delete $ENV{ORACLE_HOME};
$sqlplus_release = `$sqlplus_exe -S /nolog [EMAIL PROTECTED] 2>&1`;
$ENV{ORACLE_HOME} = $x;
}
# -MJE
unlink "define.sql";

Originally, I just deleted ORACLE_HOME but it is used all over the place 
later and led to use of uninitialised variable warnings.


Once change made the outcome is:

==
WARNING: Setting ORACLE_HOME env var to /home/XXX/instantclient_10_2/ 
for you.

WARNING: The tests will probably fail unless you set ORACLE_HOME yourself!
Using Oracle in /home/XXX/instantclient_10_2/
DEFINE _SQLPLUS_RELEASE = "1002000300" (CHAR)
Oracle version 10.2.0.3 (10.2)
Looks like an Instant Client installation, okay
Your LD_LIBRARY_PATH env var is set to '/home/XXX/instantclient_10_2/'
==

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

Re: help please

[Martin Evans]

Ron Savage wrote:

Samuel_Zheng wrote:

Hi Sam

Hi, Can Somebody help me?This is my perl scrip:#!/usr/local/bin/perl use 


Sorry, but no. This is a DBI-related mailing list, and for your post to 
be relevant you'd have to tell us what why you think the DBI part of the 
code is the part that's failing.


In addition to Ron's comment, I think it is poor etiquette to post a new 
topic as a reply to someone else's thread on a totally different topic. 
Assuming you have a topic which is relevant to the list please start a 
new thread in the future.


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

Re: Segmentation fault

[Martin Evans]

[EMAIL PROTECTED] wrote:

Hi all,
I have a script about query record from MS Access database.
This database contains a table named Lengths.
The list of field names is Sample_ID, Taxon_ID, Lenght.

Question is coming:
1. When I use the query command: select * from Lengths
It works well!
2. When I use the query command: select  * from Lengths where Taxon_ID
=1
It gives the following:
18-1-16
23-1-17
23-1-15
23-1-14
24-1-15
Segmentation fault
3. When I use the query command: select  * from Lengths where
Sample_ID = 1
The result is :
[EMAIL PROTECTED] msaccess]$ perl test.pl
Segmentation fault

Does anyone meet this problem?
My code is like this:
my $dbh = DBI-> connect( "dbi:ODBC:DSN=mdb1") or die "$DBI::errstr";
my $query = "select * from Lengths";

my $tp=$dbh->prepare("select  * from Lengths where Sample_ID = 1") or
die "cna nont $dbh->err $dbh->errstr \n";
$tp->execute();

#print $tp;

while (my @tmp_l=$tp->fetchrow_array())
{ print join('-',@tmp_l),"\n";}

The operating system is Red Hat Enterprise 3.
Perl : v5.8.3 built for i386-linux-thread-multi
ODBC: unixODBC 2.2.12
DBD::ODBC: 1.14

Best Regards,
Xu




You don't mention which driver you are using. Is it by any chance the 
one in mdbtools? If it is then this project does not seem to have been 
updated since 2004-06-18 and I see a number of problems reported like yours.


If you are not using mdbtools what driver are you using?

Why not run perl under a debugger to see where the segmentation fault is 
occurring e.g. gdb /usr/bin/perl myscript.pl, then run and you should 
get a stack dump hopefully showing where the problem is.


If you want to access a MS Access database from Linux and can put the 
access database on a Windows machine there are a number of other 
alternatives to mdbtools which are a lot more mature/reliable using 
Microsoft's own ODBC driver including using DBI Proxy or an ODBC-ODBC 
Bridge.


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

Re: DBI and DBD::Oracle throw error Had to create DBD::Oracle::dr::imp_data_size unexpectedly

[Martin Evans]

Peter McLarty wrote:

Hi
Running some redhat servers and created some scripts that use DBI and 
DBD::Oracle to access our 10.2.0.3 databases.
The script was built and tested and has been used successfully on dual core 
64bit Xeon CPU servers and with as best as we can tell an identical 
installation on some new quad core servers the script fails with the above 
error.

We have tried a later version of DBI we were using the 1.40 version installed 
as a RPM and have installed 1.59 using perl and make.

DBD::Oracle is 1.19

I have some strace output from where it fails running a simple piece of code 
that simply connects the the system as / and runs select * from dual as well as 
then would do a couple of log switches

open("/usr/lib64/perl5/5.8.5/x86_64-linux-thread-multi/CORE/libclntsh.so.10.1", 
O_RDONLY) = -1 ENOENT (No such file or directory)
open("/usr/local/oracle/product/10g/srs/lib/libclntsh.so.10.1", O_RDONLY) = 3
read(3, "\177ELF\2\1\1\0\0\0\0\0\0\0\0\0\3\0>\0\1\0\0\0\340\311"..., 832) = 832
fstat(3, {st_dev=makedev(104, 2), st_ino=1583105, st_mode=S_IFREG|0750, 
st_nlink=1, st_uid=500, st_gid=501, st_blksize=4096, st_blocks=40792, 
st_size=20859839, st_atime=2007/10/04-08:23:43, st_mtime=2007/08/23-15:57:23, 
st_ctime=2007/08/23-16:05:28}) = 0
mmap(NULL, 20553416, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 3, 0) = 
0x2a98723000
mprotect(0x2a99907000, 1793736, PROT_NONE) = 0
mmap(0x2a99a07000, 667648, PROT_READ|PROT_WRITE, 
MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 3, 0x11e4000) = 0x2a99a07000
mmap(0x2a99aaa000, 77512, PROT_READ|PROT_WRITE, 
MAP_PRIVATE|MAP_FIXED|MAP_ANONYMOUS, -1, 0) = 0x2a99aaa000
close(3)= 0
open("/usr/local/oracle/product/10g/srs/lib/libnnz10.so", O_RDONLY) = 3
read(3, "\177ELF\2\1\1\0\0\0\0\0\0\0\0\0\3\0>\0\1\0\0\\245\f"..., 832) = 832
fstat(3, {st_dev=makedev(104, 2), st_ino=1583103, st_mode=S_IFREG|0644, 
st_nlink=1, st_uid=500, st_gid=501, st_blksize=4096, st_blocks=7448, 
st_size=3808761, st_atime=2007/10/04-08:23:43, st_mtime=2006/11/03-04:46:42, 
st_ctime=2007/08/23-16:05:28}) = 0
mmap(NULL, 4860216, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 3, 0) = 
0x2a99abd000
mprotect(0x2a99daf000, 1771832, PROT_NONE) = 0
mmap(0x2a99eae000, 724992, PROT_READ|PROT_WRITE, 
MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 3, 0x2f1000) = 0x2a99eae000
mmap(0x2a99f5f000, 2360, PROT_READ|PROT_WRITE, 
MAP_PRIVATE|MAP_FIXED|MAP_ANONYMOUS, -1, 0) = 0x2a99f5f000
close(3)= 0
mprotect(0x2a99abd000, 3088384, PROT_READ|PROT_WRITE) = 0
mprotect(0x2a99abd000, 3088384, PROT_READ|PROT_EXEC) = 0
mprotect(0x2a98723000, 18759680, PROT_READ|PROT_WRITE) = 0
mprotect(0x2a98723000, 18759680, PROT_READ|PROT_EXEC) = 0
mprotect(0x2a985f6000, 176128, PROT_READ|PROT_WRITE) = 0
mprotect(0x2a985f6000, 176128, PROT_READ|PROT_EXEC) = 0
write(2, "Had to create DBD::Oracle::dr::i"..., 140) = 140
write(2, "Use of uninitialized value in su"..., 129) = 129
brk(0x925000)   = 0x925000
write(2, "Had to create DBD::Oracle::db::i"..., 140) = 140
write(2, "Use of uninitialized value in su"..., 129) = 129
write(2, "Undefined subroutine &DBD::Oracl"..., 139) = 139
open("/usr/share/locale/locale.alias", O_RDONLY) = 3
fstat(3, {st_dev=makedev(104, 1), st_ino=19664, st_mode=S_IFREG|0644, 
st_nlink=1, st_uid=0, st_gid=0, st_blksize=4096, st_blocks=16, st_size=2528, 
st_atime=2007/10/04-08:23:40, st_mtime=2006/02/09-09:02:24, 
st_ctime=2007/08/14-03:33:24}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 
0x2a99f6
read(3, "# Locale name alias data base.\n#"..., 4096) = 2528
read(3, "", 4096)   = 0
close(3)= 0
munmap(0x2a99f6, 4096)  = 0
open("/usr/share/locale/en_US.UTF-8/LC_MESSAGES/libc.mo", O_RDONLY) = -1 ENOENT 
(No such file or directory)
open("/usr/share/locale/en_US.utf8/LC_MESSAGES/libc.mo", O_RDONLY) = -1 ENOENT 
(No such file or directory)
open("/usr/share/locale/en_US/LC_MESSAGES/libc.mo", O_RDONLY) = -1 ENOENT (No 
such file or directory)
open("/usr/share/locale/en.UTF-8/LC_MESSAGES/libc.mo", O_RDONLY) = -1 ENOENT 
(No such file or directory)
open("/usr/share/locale/en.utf8/LC_MESSAGES/libc.mo", O_RDONLY) = -1 ENOENT (No 
such file or directory)
open("/usr/share/locale/en/LC_MESSAGES/libc.mo", O_RDONLY) = -1 ENOENT (No such 
file or directory)
stat("/usr/lib64/perl5/vendor_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI/DESTROY.al",
 0x504140) = -1 ENOENT (No such file or directory)
open("/usr/local/oracle/admin/scripts/auto/DBI/DESTROY.al", O_RDONLY) = -1 
ENOENT (No such file or directory)
open("/usr/lib64/perl5/5.8.5/x86_64-linux-thread-multi/auto/DBI/DESTROY.al", 
O_RDONLY) = -1 ENOENT (No such file or directory)
open("/usr/lib/perl5/5.8.5/auto/DBI/DESTROY.al", O_RDONLY) = -1 ENOENT (No such 
file or directory)
open("/usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI/DESTROY.al",
 O_RDONLY) = -1 ENOENT (No such file or

Re: DBD::ODBC 1.14 does not work on Windows x64 using Microsoft ODBC drivers

[Martin Evans]

Nelson Oliveira wrote:

I have Perl 5.8.8 built for Windows x64 from Activestate (version 822)
installed on a Windows 2003 x64 bit server (Xeon processor).

I have installed version 1.59 of DBI and version 1.14 of DBD::ODBC compiled
for x64 bit, using the freely available Microsoft SDK.

The ODBC-ODBC bridge used is supposed to be for 64 bit, as described by
Microsoft.


Could you tell me what this "ODBC-ODBC bridge" is and where you found it?


I ran the attached test script and get an error:
DBD::ODBC::st execute failed:
[Microsoft][ODBC SQL Server Driver]Invalid string or buffer length
(SQL-HY090)(DBD: st_execute/SQLExecute err=-1) at test_dbd-odbc.pl line 48.

This seems to be due to passing an undef value as an argument in the
DBI->execute() call.

The same script runs flawlessly on a in32.
The script also runs flawlessly if I use the DBD::mysql driver built for
Windows x64 bit.

Is this a bug in DBD::ODBC or in the Microsoft ODBC driver?

Attached script:
#!/usr/bin/perl
#
# Test script for DBD::ODBC for 64/32 bit Windows

use DBI;


# define $user and $password for your database
my $dsn = "dbi:ODBC:my_database_schema";


my $dbh;
eval {
$dbh = DBI->connect($dsn, $user, $password,
{ AutoCommit => 0, RaiseError => 0});

if (! defined $dbh) {
print "Cannot connect to database with DSN = $dsn";
exit(0);
} else {
print "Connected to SQL Server with parameters:\n",
  "DSN = $dsn\n",
  "User= $user\n",
  "Password= **\n";
}

my $drop_stmt = "if object_id(N'my_test',N'U') is not null " .
"drop table my_test";
$sth = $dbh->prepare($drop_stmt);
$sth->execute();

# create a table with one key and all other columns optional
my $create_stmt =
'create table my_test ' .
'   (colnvarchar(80) not null,' .
'strnvarchar(20))';
my $sth = $dbh->prepare($create_stmt);
$sth->execute();

# insert a simple row with three nulls
my $value = undef;
my $insert_one =
'insert into my_test (col, str) values (?, ?)';
$sth = $dbh->prepare($insert_one);
$sth->execute('This is my key!', $value);

# on Windows x64 the above statement fails because of the last argument
$dbh->commit();

my $key;
my $str;
my $select = 'select col, str from my_test';
$sth = $dbh->prepare($select);
$sth->execute();

$sth->bind_col(1, \$key);
$sth->bind_col(2, \$str);
while ($sth->fetch) {
print "Retrieved ";
if (! defined $str) {
$str = "";
}
print "($key, $str) ";
print " from database table\n";
}


$dbh->commit();

$dbh->disconnect;
};
if ($@) {
print "Error in connecting to the database DSN: $dsn\n";
if (defined $dbh) {
$dbh->rollback();
print "with error " . $dbh->errstr() . "\n";
}
}

1;




Microsoft changed their ODBC sql header files when they released 64 bit 
windows so some of the ODBC APIs use a new SQLULEN which is 64 bits 
where it was 32 bits before. My guess is that this is causing your 
problem. I cannot at present build DBD::ODBC for ActiveState (or even 
Perl for that matter) on Windows because the MS Visual Studio Express 
won't build it. I believe the APIs affected are:


SQLDescribeParam - 4th arg
SQLBindParameter - 6th arg
SQLBindParam - 5th arg
SQLDescribeCol - 7th arg
SQLSetConnectOption - 3rd arg
SQLSetParam - 5th arg

Of course, DBD::ODBC only uses a few of these presently. You will 
probably need to to at least change calls to SQLDescribeParam, 
SQLBindParameter and SQLDescribeCol for your precise situation such that 
the lengths use a SQLULEN/SQLLEN instead of SQLUINTEGER and SQLINTEGER 
especially where a pointer to that type is passed.


I'm sorry I cannot help more at this stage but if you are in a position 
to make those changes and confirm they work I'm happy to accept patches 
until I can get hold of a 64bit windows machine and a working compiler.


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

Re: DBD::ODBC 1.14 does not work on Windows x64 using Microsoft ODBC drivers

[Martin Evans]

Nelson Oliveira wrote:

Thanks!

Your guess was right. I changed the datatypes from a 4 byte type to
an 8 byte type in several places where DBD::ODBC interfaces with the
ODBC functions. I compiled it for WIN64 and ran the test suite in DBD::ODBC,
which for the most part reported everything ok. My test script runs and
more complex software we have here seems to run flawlessly as well.

I will send you the changed files directly to you in a separate email.

Nelson Oliveira
The Medea Group
Toronto, Ontario


Thank you.

I look forward to them and will make the necessary changes in subversion 
for the next release.


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


Martin Evans wrote:


Nelson Oliveira wrote:

I have Perl 5.8.8 built for Windows x64 from Activestate (version 822)
installed on a Windows 2003 x64 bit server (Xeon processor).

I have installed version 1.59 of DBI and version 1.14 of DBD::ODBC
compiled
for x64 bit, using the freely available Microsoft SDK.

The ODBC-ODBC bridge used is supposed to be for 64 bit, as described by
Microsoft.

Could you tell me what this "ODBC-ODBC bridge" is and where you found it?


This is the Microsoft ODBC driver for SQL Server. It's found under
Control Panel->Administrative tools->ODBC sources


I ran the attached test script and get an error:
DBD::ODBC::st execute failed:
[Microsoft][ODBC SQL Server Driver]Invalid string or buffer length
(SQL-HY090)(DBD: st_execute/SQLExecute err=-1) at test_dbd-odbc.pl line
48.

This seems to be due to passing an undef value as an argument in the
DBI->execute() call.

The same script runs flawlessly on a in32.
The script also runs flawlessly if I use the DBD::mysql driver built for
Windows x64 bit.

Is this a bug in DBD::ODBC or in the Microsoft ODBC driver?

Attached script:
#!/usr/bin/perl
#
# Test script for DBD::ODBC for 64/32 bit Windows

use DBI;


# define $user and $password for your database
my $dsn = "dbi:ODBC:my_database_schema";


my $dbh;
eval {
$dbh = DBI->connect($dsn, $user, $password,
{ AutoCommit => 0, RaiseError => 0});

if (! defined $dbh) {
print "Cannot connect to database with DSN = $dsn";
exit(0);
} else {
print "Connected to SQL Server with parameters:\n",
  "DSN = $dsn\n",
  "User= $user\n",
  "Password= **\n";
}

my $drop_stmt = "if object_id(N'my_test',N'U') is not null " .
"drop table my_test";
$sth = $dbh->prepare($drop_stmt);
$sth->execute();

# create a table with one key and all other columns optional
my $create_stmt =
'create table my_test ' .
'   (colnvarchar(80) not null,' .
'strnvarchar(20))';
my $sth = $dbh->prepare($create_stmt);
$sth->execute();

# insert a simple row with three nulls
my $value = undef;
my $insert_one =
'insert into my_test (col, str) values (?, ?)';
$sth = $dbh->prepare($insert_one);
$sth->execute('This is my key!', $value);

# on Windows x64 the above statement fails because of the last
argument
$dbh->commit();

my $key;
my $str;
my $select = 'select col, str from my_test';
$sth = $dbh->prepare($select);
$sth->execute();

$sth->bind_col(1, \$key);
$sth->bind_col(2, \$str);
while ($sth->fetch) {
print "Retrieved ";
if (! defined $str) {
$str = "";
}
print "($key, $str) ";
print " from database table\n";
}


$dbh->commit();

$dbh->disconnect;
};
if ($@) {
print "Error in connecting to the database DSN: $dsn\n";
if (defined $dbh) {
$dbh->rollback();
print "with error " . $dbh->errstr() . "\n";
}
}

1;



Microsoft changed their ODBC sql header files when they released 64 bit
windows so some of the ODBC APIs use a new SQLULEN which is 64 bits
where it was 32 bits before. My guess is that this is causing your
problem. I cannot at present build DBD::ODBC for ActiveState (or even
Perl for that matter) on Windows because the MS Visual Studio Express
won't build it. I believe the APIs affected are:

SQLDescribeParam - 4th arg
SQLBindParameter - 6th arg
SQLBindParam - 5th arg
SQLDescribeCol - 7th arg
SQLSetConnectOption - 3rd arg
SQLSetParam - 5th arg

Of course, DBD::ODBC only uses a few of these presently. You will
probably need to to at least change calls to SQLDescribeParam,
SQLBindParameter and SQLDescribeCol for your precise situation such that
the lengths use a SQLULEN/SQLLEN instead of SQLUINTEGER and SQLINTEGER
especially where a pointer to that type is passed.

I'm sorry I cannot help more at this stage but if you are in a position
to make those changes and confirm they work I'm happy to accept patches
until I can get hold of a 64bit windows machine and a working compiler.

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

Re: using $dbh->quote with UTF

[Martin Evans]

[EMAIL PROTECTED] wrote:

Hello,

I'm trying to use $dbh->quote with a UTF string, and I'm not having
luck.  How can I get it to return a UTF8 string?

Here is my example:

#!/usr/bin/perl -w
use strict;


Add use warnings;


use DBI;

my $dbh = DBI->connect("DBI:mysql:mysql", "root", "");
my $string = "test äñèé";
utf8::upgrade($string);

my $sqlutfstring = $dbh->quote($string);
print qq~



UTF Quote Test


~;
print $string . " == " . $sqlutfstring;
print qq~


~;



If you add use warnings I suspect your print will issue:

Wide character in print at xxx, which might have given you the clue that 
I believe you should do binmode(STDOUT, ":utf8") before you print.


quote seems to work for me:

perl -e 'use DBI 
qw(neat);$h=DBI->connect("dbi:Oracle:XE","xxx","yyy");$x="\x{20ac}";open 
OUT, ">x.x";binmode(OUT,":utf8");print OUT $x;close OUT;print neat($x);'

"€"

and as neat has put double quotes around the Euro, then DBI thinks it is 
a unicode character too.


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

New test version of DBD::ODBC and some outstanding development questions

[Martin Evans]

I have uploaded DBD::ODBC 1.15_1 to CPAN. See below for a list of the 
Changes. I'd appreciate any testing from anyone using DBD::ODBC as there 
have been some very significant changes in error handling and describing 
result-sets and repercussion of problems building/using on WIN64.


I also have a couple of outstanding questions re DBI::DBD development:

1. can I now throw away any DBIh_EVENT2 calls as they are now noops?
  e.g. DBIh_EVENT2(drh, ERROR_event, DBIc_ERR(imp_drh),
   DBIc_ERRSTR(imp_drh));

2. the set_err docs say setting err to "" is an informational state. I 
am using that for some SQL_SUCCESS_WITH_INFO msgs. Under what conditions 
do/can you see those?


3. I have a post on dbi-dev subject "Resetting odbc_err_handler - help 
with dbd_db_STORE_attrib". For now I have replaced the code in question 
with a test for SvOK. If anyone knows what the original author may have 
meant by "if(valuesv == &sv_undef)" I love to hear from you.


Minor changes to 20SqlServer.t test for SQL Server 2008 (Katmai).
Timestamps now return an extra 4 digits of precision (all ) and
the driver reported in dbcc messages has a '.' in the version which
was not handled.

New FAQ entry and test code for "Datetime field overflow" problem in
Oracle.

Changed all ODBC code to use new SQLLEN/SQLULEN types where
Microsoft's headers indicate, principally so DBD::ODBC builds and
works on win64. NOTE: you will need an ODBC Driver Manager on UNIX
which knows SQLLEN/SQLULEN types. The unixODBC driver manager uses
SQLLEN/SQLULEN in versions from at least 2.2.12. Thanks to Nelson
Oliveira for finding, patching and testing this and then fixing
problems with bound parameters on 64 bit Windows.

Added private_attribute_info method DBI introduced (see DBI docs)
and test cases to 02simple.t.

Fairly major changes to dbd_describe in dbdimp.c to reduce ODBC calls
by 1 SQLDescribeCol call per column when describing result
sets. Instead of calculating the amount of memory required to hold
every column name we work on the basis that (num_columns + 1) *
SQL_MAX_COLUMN_NAME_LEN can hold all column names. However, to avoid
using a large amount of memory unnecessarily if an ODBC driver
supports massive column name lengths the maximum size per column is
restricted to 256.

Changed to avoid using explicit use of DBIc_ERRXXX in favour of newish
(ok, DBD::ODBC is a bit behind the times in this respect)
DBIh_SET_ERR_CHAR.  This involved a reworking or the error handling
and although all test cases still pass I cannot guarantee it has no
other effects - please let me know if you spot differences in error
messages.

Fixed bug in 20SqlServer test for multiple results that was passing
but for the wrong reason (namely, that the odbc_err_handler was being
called when it should not have been).

Fixed bug in odbc_err_handler that prevented it from being reset so
you don't have an error handler. Looks like the problem was in
dbd_db_STORE_attrib where "if(valuesv == &PL_sv_undef)" was used to
detect undef and I think it should be "if (!SvOK(valuesv))".

Improvements to odbc_err_handler documentation.

Added 10handler.t test cases.

More tests in 02simple.t to check NUM_OF_FIELDS and NAMES_uc.

Bit of a tidy up:

Removed some unused variable declarations from dbdimp.c.

Lots of changes to DBD::ODBC tracing, particularly in dbd_describe,
and dbd_error2 and login6.

Removed a lot of tracing code in comments or #if 0 as it never gets
built.

Changed dual tests on SQL_SUCCESS and SQL_SUCCESS_WITH_INFO to use
SQL_SUCCEEDED.

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

Re: DBD::Oracle and Perl's internal representation of strings

[Martin Evans]

E R wrote:

Hi,

I just posted this question on Perl Monks:

http://perlmonks.com/?node_id=649489

Am I correct in my analysis of what's going?

I would be happy if I could pass strings to DBD::Oracle without having
to worry about Perl's internal representation of the string, and it would
be ok if an exception was thrown whenever a string contained a character > 255.

Is there a way to configure DBD::Oracle to do that?

Thanks,
ER


I have already answered your reply on perl monks. Of course someone here 
may disagree with it ;-)


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

Re: DBD::Oracle and Perl's internal representation of strings

[Martin Evans]

E R wrote:

Alright, so here's another mystery:

use DBI;
...
$ENV{NLS_LANG} = ""AMERICAN_AMERICA.WE8ISO8859P1";

$dbh = DBI->connect(...);
$c = chr(228);
print "result: ", select_scalar("SELECT 1 FROM DUAL WHERE '$c' =
chr(228)"), "\n";

The select_scalar subroutine merely returns the first column of the
first row of the query.

The above code emits:

result:

However, if I change NLS_LANG to "AMERICAN_AMERICA.US7ASCII", it emits:

result: 1

Is there another NLS_LANG setting I should use, or should I check my
versions of OCI, DBD::Oracle and perl? I think I'm running OCI version
8. My perl is 5.8.0.

Thanks,
ER



Are you actually running the code you keep sending? In:

sub select_scalar {
  my ($dbi, $sql) = @_;
  my $sth = $dbi->prepare($sql);
  $sth->execute();
  my $r = $sth->fetch_row_array();
  return $r->[0];
}

there is no such method fetch_row_array - it should be fetchrow_array.

In:

$ENV{NLS_LANG} = ""AMERICAN_AMERICA.WE8ISO8859P1"

there is an extra ".

In:

select_scalar("SELECT 1.

you have missed off the $dbi argument select_scalar requires.

It is also a good idea to put RaiseError=>1 in you DBI connect for 
examples like this so any errors are trapped and use strict, use 
warnings never goes amiss.


So fixing all of that we have:

use strict;
use warnings;
use DBI;
$ENV{NLS_LANG} = "AMERICAN_AMERICA.WE8ISO8859P1";
my $dbi = DBI->connect('dbi:Oracle:xxx','xxx','xxx',{RaiseError=>1})
  || die $DBI::errstr;

my $c = chr(228);
print "result: ", select_scalar($dbi, "SELECT 1 FROM DUAL WHERE '$c' = 
chr(228)"), "\n";


sub select_scalar {
  my ($dbi, $sql) = @_;
  my $sth = $dbi->prepare($sql);
  $sth->execute();
  my $r = $sth->fetchrow_array();
  return $r->[0];
}

I think you may be on rather too old an Oracle (at 8) for this. What do 
you get when you add:


my $sth = $dbi->prepare(q{select chr(228) from dual});
$sth->execute;
DBI::dump_results($sth);

just after the connect call and what does:

my $nls_params = $dbi->ora_nls_parameters();
foreach my $k (keys %$nls_params) {
print "$k, $nls_params->{$k}\n";
}

output.

Rather interestingly I get "DBD::Oracle::st fetch failed: ORA-29275: 
partial multibyte character (DBD ERROR: OCIStmtFetch) [for Statement 
"select chr(228) from dual"]" for the select so there might be something 
there but then again my database character set is AL32UTF8.


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

New test release (1.15_2) of DBD::ODBC

[Martin Evans]

I have uploaded to CPAN 1.15_2 test release of DBD::ODBC. This contains
a lot of changes (even since 1.15_1), in particular wrt building, so I'd
welcome any feedback. You can also find it temporarily at:

ftp://ftp.easysoft.com/pub/odbc-odbc-bridge/Perl/modules/DBD-ODBC-1.15_2.tar.gz

The changes are:

Fix bug in DBD::ODBC's private function data_sources which was
returning data sources prefixed with "DBI:ODBC" instead of "dbi:ODBC".

If you don't have at least DBI 1.21 it is now a fatal error instead of
just a warning.

DBI->connect changed so informational diagnostics like "Changed
database context to 'master'" from SQL Server are available in
errstr/state. These don't cause DBI->connect to die but you can test
$h->err eq "" after connect and obtain the informational diagnostics
from errstr/state if you want them.

Fixed problem in 41Unicode.t where utf8 was used before testing we had
a recent enough Perl - thank you cpan testers.

Changed "our" back to "my" in Makefile.PL - thank you cpan testers.

Removed all calls to DBIh_EVENT2 in dbdimp.c as it is no longer used
(see posts on dbi-dev).

Changed text output when a driver manager is not found to stop
referring to iodbcsrc which is no longer included with DBD::ODBC.

Changed Makefile.PL to attempt to find unixODBC if -o or ODBCHOME not
specified.

Updated META.yml based on new 1.2 spec.

Changed Makefile.PL so if an ODBC driver manager is not found then we
issue warning and exit cleanly but not generating a Makefile. This
should stop cpan-testers from flagging a fail because they haven't got
an ODBC driver manager.

Changed Makefile.PL so it no longer "use"s DBI/DBI::DBD because this
makes cpan-testers log a fail if DBI is not installed. Changed to put
the DBI::DBD use in the CONFIGURE sub so PREREQ_PM will filter out
machines where DBI is not installed.

Fix a possible typo, used once in 10handler.t.

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

Re: Describe failed for NUM_OF_FIELDS when using odbc_exec_direct (MS SQL Server / DBI::ODBC)

[Martin Evans]

Toni Salomäki wrote:

Hi,

I'm trying to write MS SQL Server support for a generic perl module
used to execute arbitrary SQL statements and then return the array
reference for the result set (if one exists). My basic idea was to do
in following way:

my $sth = $dbh->prepare($clause);
$sth->execute();

return if ($sth->{NUM_OF_FIELDS} == 0);
return @{$sth->fetchall_arrayref()};

But I also want to be able to create temporary tables using this same
routine (for example with select ... into #tmp). This is not possible
in MS SQL when prepare + execute is used. Changing the behavior of
execute method to use SQLExecDirect will solve this problem (by
setting $dbh->{odbc_exec_direct} = 1). But this causes following error
message:

Describe failed during DBI::st=HASH(0x19c2048)->FETCH(NUM_OF_FIELDS,0)

The script works ok, but is there any way to get rid of this error
message? I tried to read all the documentation, but I could not figure
out any other way to check if the statement contains any result sets
than using the NUM_OF_FIELDS.

Versions in use:

This is perl, v5.8.8 built for MSWin32-x86-multi-thread
Binary build 822 [280952] provided by ActiveState http://www.ActiveState.com
DBD-ODBC: ODBC Driver for DBI Version: 1.13
SQL Native Client 2005.90.3042.00

Thanks in advance for any help, Toni




I'll try and reproduce here but in the mean time can you send my a 
simple example demonstrating this problem.


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

Re: Describe failed for NUM_OF_FIELDS when using odbc_exec_direct (MS SQL Server / DBI::ODBC)

[Martin Evans]

Martin Evans wrote:

Toni Salomäki wrote:

Hi,

I'm trying to write MS SQL Server support for a generic perl module
used to execute arbitrary SQL statements and then return the array
reference for the result set (if one exists). My basic idea was to do
in following way:

my $sth = $dbh->prepare($clause);
$sth->execute();

return if ($sth->{NUM_OF_FIELDS} == 0);
return @{$sth->fetchall_arrayref()};

But I also want to be able to create temporary tables using this same
routine (for example with select ... into #tmp). This is not possible
in MS SQL when prepare + execute is used. Changing the behavior of
execute method to use SQLExecDirect will solve this problem (by
setting $dbh->{odbc_exec_direct} = 1). But this causes following error
message:

Describe failed during DBI::st=HASH(0x19c2048)->FETCH(NUM_OF_FIELDS,0)

The script works ok, but is there any way to get rid of this error
message? I tried to read all the documentation, but I could not figure
out any other way to check if the statement contains any result sets
than using the NUM_OF_FIELDS.

Versions in use:

This is perl, v5.8.8 built for MSWin32-x86-multi-thread
Binary build 822 [280952] provided by ActiveState 
http://www.ActiveState.com

DBD-ODBC: ODBC Driver for DBI Version: 1.13
SQL Native Client 2005.90.3042.00

Thanks in advance for any help, Toni




I'll try and reproduce here but in the mean time can you send my a 
simple example demonstrating this problem.


Martin


Toni,

Thanks for the report and off this list the example code.

So others can keep up with this:

It is expected prepare/execute which generates a temporary table will 
lose the temporary table immediately (see 
http://technet.microsoft.com/en-US/library/ms131667.aspx which describes 
the issue). As a result, as you have also found you need to set 
odbc_exec_direct if you want to use temporary tables. I will be updating 
the pod and faq on this to make it clear.


The error:

Describe failed during DBI::st=HASH(0x19c2048)->FETCH(NUM_OF_FIELDS,0)

is caused by DBD::ODBC calling SQLNumResultCols after SQLMoreResults 
says there are no more results and the ODBC driver manager is returning 
a function sequence error for the second call. Strangely, some Microsoft 
ODBC driver managers (e.g. the one that comes with Vista) don't report 
this problem as MS seem to have relaxed their reporting of function 
sequence errors in some places.


The bug in DBD::ODBC which caused SQLNumResultCols to be called in this 
situation is now fixed. I can supply you with a new DBD::ODBC (1.15_3) 
tar source file which includes this fix (although I'm afraid if you 
cannot build it yourself we could have a problem here as builds I do for 
activestate installs do not seem to work). Alternatively, you can 
attempt to apply the fix below but you'll still need to be able to build it:


In dbdimp.c in the function dbd_describe look for:

   imp_sth->done_desc = 1;   /* assume ok from here on */
   if (!more) {

  while (num_fields == 0 && imp_dbh->odbc_sqlmoreresults_supported 
== 1) {

 rc = SQLMoreResults(imp_sth->hstmt);
 if (ODBC_TRACE_LEVEL(imp_sth) >= 8) {
	PerlIO_printf(DBIc_LOGPIO(imp_sth), "Numfields == 0, SQLMoreResults 
== %d\n", rc);

PerlIO_flush(DBIc_LOGPIO(imp_sth));
 }
 if (rc == SQL_SUCCESS_WITH_INFO) {
	AllODBCErrors(imp_sth->henv, imp_sth->hdbc, imp_sth->hstmt, 
ODBC_TRACE_LEVEL(imp_sth) >= 8, DBIc_LOGPIO(imp_dbh));

 }
 imp_sth->done_desc = 0; /* reset describe flags, so that we 
re-describe */
 if (rc == SQL_NO_DATA) {
imp_sth->moreResults = 0;
dbd_error(h, rc, "dbd_describe/SQLNumResultCols");
break;
 }
 if (!SQL_ok(rc)) break;

and move the line:

imp_sth->done_desc = 0;  /* reset describe flags, so that we re-describe */

to after the:

 if (!SQL_ok(rc)) break;


Let me know what you want to do; I can easily send you a 1.15_3 
distribution.


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

Re: Describe failed for NUM_OF_FIELDS when using odbc_exec_direct (MS SQL Server / DBI::ODBC)

[Martin Evans]

Martin Evans wrote:

Martin Evans wrote:

Toni Salomäki wrote:

Hi,

I'm trying to write MS SQL Server support for a generic perl module
used to execute arbitrary SQL statements and then return the array
reference for the result set (if one exists). My basic idea was to do
in following way:

my $sth = $dbh->prepare($clause);
$sth->execute();

return if ($sth->{NUM_OF_FIELDS} == 0);
return @{$sth->fetchall_arrayref()};

But I also want to be able to create temporary tables using this same
routine (for example with select ... into #tmp). This is not possible
in MS SQL when prepare + execute is used. Changing the behavior of
execute method to use SQLExecDirect will solve this problem (by
setting $dbh->{odbc_exec_direct} = 1). But this causes following error
message:

Describe failed during DBI::st=HASH(0x19c2048)->FETCH(NUM_OF_FIELDS,0)

The script works ok, but is there any way to get rid of this error
message? I tried to read all the documentation, but I could not figure
out any other way to check if the statement contains any result sets
than using the NUM_OF_FIELDS.

Versions in use:

This is perl, v5.8.8 built for MSWin32-x86-multi-thread
Binary build 822 [280952] provided by ActiveState 
http://www.ActiveState.com

DBD-ODBC: ODBC Driver for DBI Version: 1.13
SQL Native Client 2005.90.3042.00

Thanks in advance for any help, Toni




I'll try and reproduce here but in the mean time can you send my a 
simple example demonstrating this problem.


Martin


Toni,

Thanks for the report and off this list the example code.

So others can keep up with this:

It is expected prepare/execute which generates a temporary table will 
lose the temporary table immediately (see 
http://technet.microsoft.com/en-US/library/ms131667.aspx which describes 
the issue). As a result, as you have also found you need to set 
odbc_exec_direct if you want to use temporary tables. I will be updating 
the pod and faq on this to make it clear.


The error:

Describe failed during DBI::st=HASH(0x19c2048)->FETCH(NUM_OF_FIELDS,0)

is caused by DBD::ODBC calling SQLNumResultCols after SQLMoreResults 
says there are no more results and the ODBC driver manager is returning 
a function sequence error for the second call. Strangely, some Microsoft 
ODBC driver managers (e.g. the one that comes with Vista) don't report 
this problem as MS seem to have relaxed their reporting of function 
sequence errors in some places.


The bug in DBD::ODBC which caused SQLNumResultCols to be called in this 
situation is now fixed. I can supply you with a new DBD::ODBC (1.15_3) 
tar source file which includes this fix (although I'm afraid if you 
cannot build it yourself we could have a problem here as builds I do for 
activestate installs do not seem to work). Alternatively, you can 
attempt to apply the fix below but you'll still need to be able to build 
it:


In dbdimp.c in the function dbd_describe look for:

   imp_sth->done_desc = 1;/* assume ok from here on */
   if (!more) {

  while (num_fields == 0 && imp_dbh->odbc_sqlmoreresults_supported 
== 1) {

 rc = SQLMoreResults(imp_sth->hstmt);
 if (ODBC_TRACE_LEVEL(imp_sth) >= 8) {
PerlIO_printf(DBIc_LOGPIO(imp_sth), "Numfields == 0, 
SQLMoreResults == %d\n", rc);

PerlIO_flush(DBIc_LOGPIO(imp_sth));
 }
 if (rc == SQL_SUCCESS_WITH_INFO) {
AllODBCErrors(imp_sth->henv, imp_sth->hdbc, imp_sth->hstmt, 
ODBC_TRACE_LEVEL(imp_sth) >= 8, DBIc_LOGPIO(imp_dbh));

 }
 imp_sth->done_desc = 0;/* reset describe flags, so that we 
re-describe */

 if (rc == SQL_NO_DATA) {
imp_sth->moreResults = 0;
dbd_error(h, rc, "dbd_describe/SQLNumResultCols");
break;
 }
 if (!SQL_ok(rc)) break;

and move the line:

imp_sth->done_desc = 0;/* reset describe flags, so that we 
re-describe */


to after the:

 if (!SQL_ok(rc)) break;


Let me know what you want to do; I can easily send you a 1.15_3 
distribution.


Martin


I forgot to mention using "do" should work if all you are doing is 
creating a temporary object but of course in your case you are 
attempting to use one single piece of code to do 
non-result-set-producing and result-set producing SQL. You would be best 
advised to split these into calling "do" and "prepare/execute" instead 
of checking for NUM_OF_FIELDS but if you can't/won't you will need the 
fix described previously.


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

Re: DBD-ODBC-1.14 unicode bug?

[Martin Evans]

Jergen Dutch wrote:

Hello,

I am building DBD::ODBC without unicode support.

In dbdimp.c, I have:

  case SQL_VARCHAR: return "VARCHAR";
  case SQL_WCHAR: return "UNICODE CHAR";
#ifdef SQL_WVARCHAR
  case SQL_WVARCHAR: return "UNICODE VARCHAR"; /* added for
SQLServer 7 ntext type 2/24/2000 */
#endif

Shouldn't SQL_WCHAR be included too? i.e.

  case SQL_VARCHAR: return "VARCHAR";
#ifdef SQL_WVARCHAR
  case SQL_WCHAR: return "UNICODE CHAR";
  case SQL_WVARCHAR: return "UNICODE VARCHAR"; /* added for
SQLServer 7 ntext type 2/24/2000 */
#endif

I get the following error otherwise:

dbdimp.c: In function 'odbc_describe':
dbdimp.c:1780: error: 'SQL_WCHAR' undeclared (first use in this function)
dbdimp.c:1780: error: (Each undeclared identifier is reported only once
dbdimp.c:1780: error: for each function it appears in.)
dbdimp.c:1781: error: 'SQL_WVARCHAR' undeclared (first use in this function)
dbdimp.c:1782: error: 'SQL_C_WCHAR' undeclared (first use in this function)
dbdimp.c:1784: error: 'WCHAR' undeclared (first use in this function)
dbdimp.c: In function 'odbc_st_fetch':
dbdimp.c:2401: error: 'SQL_C_WCHAR' undeclared (first use in this function)
dbdimp.c:2402: error: 'SQL_WCHAR' undeclared (first use in this function)
dbdimp.c:2404: error: 'WCHAR' undeclared (first use in this function)
dbdimp.c:2404: error: 'p' undeclared (first use in this function)
dbdimp.c:2404: error: expected expression before ')' token
dbdimp.c:2409: error: expected expression before ')' token
dbdimp.c:2409: error: too few arguments to function 'sv_setwvn'
dbdimp.c: In function '_dbd_get_param_type':
dbdimp.c:2601: error: 'SQL_WCHAR' undeclared (first use in this function)
dbdimp.c:2602: error: 'SQL_WVARCHAR' undeclared (first use in this function)
dbdimp.c:2603: error: 'SQL_WLONGVARCHAR' undeclared (first use in this function)
dbdimp.c:2604: error: 'SQL_C_WCHAR' undeclared (first use in this function)
dbdimp.c: In function '_dbd_rebind_ph':
dbdimp.c:2657: error: 'WCHAR' undeclared (first use in this function)
dbdimp.c:2694: error: 'SQL_C_WCHAR' undeclared (first use in this function)
dbdimp.c:2887: error: expected expression before ')' token
make: *** [dbdimp.o] Error 1

To get it to compile, I made the above change, as well as commenting
out line 1585:
 s_c(SQL_C_WCHAR);
Not the right approach, but it got me going.

Is this a bug, or have I done something monstrously wrong?

Thanks.


Could you please tell me which ODBC driver manager or driver you were 
building with and its version. i.e. What did you set -o (or ODBCHOME to) 
and what driver/driver_manager is at that location? Some of this is 
output during perl Makefile.PL stage.


Thanks.

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

Re: DBD-ODBC-1.14 unicode bug?

[Martin Evans]

Jergen Dutch wrote:

On Nov 26, 2007 6:44 PM, Martin Evans <[EMAIL PROTECTED]> wrote:

Jergen Dutch wrote:

Hello,

I am building DBD::ODBC without unicode support.

In dbdimp.c, I have:

  case SQL_VARCHAR: return "VARCHAR";
  case SQL_WCHAR: return "UNICODE CHAR";
#ifdef SQL_WVARCHAR
  case SQL_WVARCHAR: return "UNICODE VARCHAR"; /* added for
SQLServer 7 ntext type 2/24/2000 */
#endif

Shouldn't SQL_WCHAR be included too? i.e.

  case SQL_VARCHAR: return "VARCHAR";
#ifdef SQL_WVARCHAR
  case SQL_WCHAR: return "UNICODE CHAR";
  case SQL_WVARCHAR: return "UNICODE VARCHAR"; /* added for
SQLServer 7 ntext type 2/24/2000 */
#endif

I get the following error otherwise:

dbdimp.c: In function 'odbc_describe':
dbdimp.c:1780: error: 'SQL_WCHAR' undeclared (first use in this function)




To get it to compile, I made the above change, as well as commenting
out line 1585:
 s_c(SQL_C_WCHAR);
Not the right approach, but it got me going.

Is this a bug, or have I done something monstrously wrong?

Thanks.



Could you please tell me which ODBC driver manager or driver you were
building with and its version. i.e. What did you set -o (or ODBCHOME to)
and what driver/driver_manager is at that location? Some of this is
output during perl Makefile.PL stage.

Thanks.

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



Sure: intersystems cache.

I extracted dist/ODBC/ODBC-8206-lnxrhx86.tar.Z from
5.2.0MV.8206.0_lnxrhx86_su.tar.gz and used:
 perl Makefile.PL -o /usr/cacheodbc/dev/odbc/redist/iodbc

Some of the tests from make test fail, I can post these if you want.




Thanks.
I have changed dbdimp.c to to test for SQL_WCHAR before using it. It 
looks like you are using iODBC which should define SQL_WCHAR but perhaps 
cache distributes with an old one.


I would be interested in the errors you see. Could you run the test with 
TEST_VERBOSE set and send me the errors (no need to send them to this 
list as if I find anything I'll post it back here).


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

Re: Connecting to Database

[Martin Evans]

[EMAIL PROTECTED] wrote:

Hello

At the present time I am using Perl, cgi, Apache Web Page, SQL, and
SYBASE database on a unix server(SUN Fire V 880).  I have no problem
accessing the Sybase database, fetching records and display them on the
web page.

My Available Database Drivers are.
DBM
ExampleP
File
Proxy
Sponge
Sybase

Now, I would like to connect to another database on a Microsoft SQL
Server, using Perl from my unix server.

What do I need?

Where can I get information? (book or web page)

Thanks

Oscar




http://search.cpan.org/~mjevans/DBD-ODBC-1.15_2/ODBC.pm#Frequently_Asked_Questions
Scroll down to "How do I access a MS SQL Server database from Linux?" 
The "Linux" could should be "Unix".


http://search.cpan.org/~mjevans/DBD-ODBC-1.15_2/ODBC.pm#Random_Links 
also has some links to tutorials for connecting to SQL Server.


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

Re: ORA-12154: TNS:could not resolve the connect identifier specified

[Martin Evans]

Loo, Peter # PHX wrote:

Hello All,
 
I am confused as to why I am getting this error message.  I am trying to

connect to two different Oracle instances within one program run.  The
two Oracle instances are different in versions.  inst1 is 9.2 while
inst2 is 10.2.  I am able to connect to the first instance without
problems, but the instance yields "ORA-12154" error.  However, when I
reverse the connection order by connecting the inst2 with 10.2 version
first, then connecting to the inst1 with 9.2 version, there is no error.
I hope someone can shed some lite.  Here is the code:
 
#!/usr/bin/perl
 
use DBI;


$ENV{'ORACLE_HOME'} = "/opt/app/oracle/product/9.2";
$ENV{'PATH'} =
"/opt/app/oracle/product/9.2/bin:/usr/ccs/bin:/opt/studio9/SUNWspro/bin:
/opt/app/oracle/product/10.2/bin:/opt/CA/Shar
edComponents/dmscript/bin:/usr/bin:/usr/bin:/bin:/usr/sbin:/sbin:/usr/lo
cal/bin:/opt/app/oracle/product/8.1.7.4/bin:/usr/local/bin:/
usr/local/apps/common/devl/bin:/usr/lbin:/usr/ccs/bin:/usr/xpg4/bin:/opt
/pkware:/opt/RICHPse/bin:/opt/RICHPse/examples:/opt/sas82:/o
pt/sas82/sasexe:/opt/Navisphere/bin:/opt/fileport/bin:/opt/syncsort/bin:
/usr/bin:/usr/openwin/bin:/opt/SUNWspro/bin:/opt/mfcobol/cob
ol/bin:/opt/starsql/bin:/opt/uni/bin:/opt/uni/scripts:/etc:/opt/emc/EMCp
ower/bin/sparcv9:/etc/emc/bin:/usr/local/nz/bin/:.";
 
my $dbh_inst1 = DBI->connect("dbi:Oracle:inst1", "schema", "password", {

RaiseError => 1, PrintError => 1});
 
$ENV{'ORACLE_HOME'} = "/opt/app/oracle/product/10.2";

$ENV{'PATH'} =
"/opt/app/oracle/product/10.2/bin:/opt/app/oracle/product/9.2/bin:/usr/c
cs/bin:/opt/studio9/SUNWspro/bin:/opt/CA/Shar
edComponents/dmscript/bin:/usr/bin:/usr/bin:/bin:/usr/sbin:/sbin:/usr/lo
cal/bin:/opt/app/oracle/product/8.1.7.4/bin:/usr/local/bin:/
usr/local/apps/common/devl/bin:/usr/lbin:/usr/ccs/bin:/usr/xpg4/bin:/opt
/pkware:/opt/RICHPse/bin:/opt/RICHPse/examples:/opt/sas82:/o
pt/sas82/sasexe:/opt/Navisphere/bin:/opt/fileport/bin:/opt/syncsort/bin:
/usr/bin:/usr/openwin/bin:/opt/SUNWspro/bin:/opt/mfcobol/cob
ol/bin:/opt/starsql/bin:/opt/uni/bin:/opt/uni/scripts:/etc:/opt/emc/EMCp
ower/bin/sparcv9:/etc/emc/bin:/usr/local/nz/bin/:.";
 
my $dbh_inst2 = DBI->connect("dbi:Oracle:inst2", "schema", "password", {

RaiseError => 1, PrintError => 1});
 
$dbh_inst1->disconnect();

$dbh_inst2->disconnect();
 
exit;
 
Here is the error message:
 
DBI connect('inst2','schema',...) failed: ORA-12154: TNS:could not

resolve the connect identifier specified (DBD ERROR: OCIServerAttach)
 
Thanks.
 
Peter




Sounds as though you have hit an issue sharing an env.
I'm not sure as I've not done this myself but you may need to look at 
the ora_envhp attribute:


The first time a connection is made a new OCI ’environment’ is created 
by DBD::Oracle and stored in the driver handle.  Subsequent connects 
reuse (share) that same OCI environment by default.


The ora_envhp attribute can be used to disable the reuse of the OCI 
environment from a previous connect. If the value is 0 then a new OCI 
environment is allocated and used for this connection.


The OCI environment is what holds information about the client side con‐
text, such as the local NLS environment. So by altering %ENV and setting
ora_envhp to 0 you can create connections with different NLS settings. 
This is most useful for testing.


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

Re: Problem with DBD::ODBC : -DBD::ODBC::st execute failed: [unixODBC][FreeTDS][SQL Server]Procedure has no parameters and arguments were supplied. (SQL-42000)(DBD: st_execute/SQLExecute err=-1)

[Martin Evans]

[EMAIL PROTECTED] wrote:

When I try to run a perl script that uses ODBC drivers to insert data
more than 7 rows I am getting the following error

DBD::ODBC::st execute failed: [unixODBC][FreeTDS][SQL
Server]Procedure  has no parameters and arguments were supplied.
(SQL-42000)(DBD: st_execute/SQLExecute err=-1) [for Statement "INSERT
INTO
db.guest.portfolio_event_1_310_3560_3(col1,col2,col3,col4,col4,col5)
VALUES (?,?,?,?,?)" with ParamValues:  4=' ', 1='1754079', 2=' ',
3='0', 5=' '] at /usr/local/lib/perl5/site_perl/5.8.6/DBIx/
ContextualFetch.pm line 51

Could someone help me in this aspect..

Thanks,
Neel




It is a SQL Server error reported by the FreeTDS ODBC Driver. I would 
not rule out DBD::ODBC but:


"Procedure  has"

looks like something (FreeTDS perhaps) was attempting to call a 
procedure but for some reason the name is not known (look at the two 
spaces between "Procedure" and "has". I don't use FreeTDS myself but I 
do know that for "insert into" DBD::ODBC is not going to call any 
procedures itself.


Has your table perhaps got triggers associated with it?

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

Oracle procedure raises exception but looks to be successful from DBI

[Martin Evans]

Hi,

I've just spent a long time tracking down a bug in an Oracle procedure 
because DBI's execute method returned success even though the procedure 
raised an exception. Shouldn't exceptions raised in procedures cause 
execute to fail?


Here is an example:

use DBI;
use strict;
use warnings;

my $h = DBI->connect("xxx","xxx","xxx",{RaiseError=>1,PrintError=>1});
eval {$h->do("drop table test");};
$h->do("create table test (a integer)");
$h->do(q{create or replace procedure proctest(vv integer) as
x integer;
begin
select a into x from test where a = vv;
end;});

my $s = $h->prepare("call proctest(?)");
$s->bind_param(1, 99);
my $y = $s->execute;
print "$y\n";
print $s->err(), $s->errstr(), $s->state(), "\n";

which outputs:

0E0
Use of uninitialized value in print at procfail.pl line 19.
Use of uninitialized value in print at procfail.pl line 19.

If I run exactly the same procedure from sqlplus I get an error:

SQL> execute proctest(99);
BEGIN proctest(99); END;

*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "BET.PROCTEST", line 4
ORA-06512: at line 1

If I replace the procedure with a single call to raise_application_error 
execute does fail.


I admit there is a bug in the procedure causing the exception which 
should have been caught but I was very surprised to find an exception in 
this procedure did not cause execute to fail.


Any ideas or suggestions.

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

Re: Oracle procedure raises exception but looks to be successful from DBI

[Martin Evans]

John Scoles wrote:

I sort if expected that for the exec it is usualy only used withing SP
perhaps

"begin; exec(user.package.proctest(?)); end; "  ?? who knows


same problem as without exec:

PLS-00302: component 'PACKAGE' must be declared

The next error is usually a permission problem on the Oracle side. 
Either the use who is calling the SP dose not have execute permission 
for the package or something in the package (hard to debug that) or the 
present state of the package is stopping you from executing it.


you might want to try

GRANT execute  to the package


The fact that DBD is returning the error from Oracle usually means that 
DBD is running correctly.


Cheers


It is not a permission issue.

call user.package.proctest(?)

works and

begin user.package.proctest(?); end;

fails with the error above.

Thanks for the help though - it almost got me there. I'll keep 
investigating.


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


Martin Evans wrote:

John Scoles wrote:

Wow I go one right for once.

Not 100% sure on why that is? Me thinks when you use 'Begin End' it 
forces OCI to take it as an pseudo 'stored procedure' and runs in the 
current OCI client.


I think using call just executes as a thread off the present client 
and in the background someplace??


You might want to try 'exec proctest(?); '


Doesn't work:

DBD::Oracle::st execute failed: ORA-00900: invalid SQL statement (DBD 
ERROR: OCIStmtExecute) [for Statement "exec proctest(?)" with 
ParamValues: :p1=99] at procfail.pl line 17.



I think it does it in the local client.

Will have to crack open the OCI  docs to see and  as I am not 
suffering from insomnia right now I have no real want to start 
digging though it right now.


Cheers


Your solution with begin/end works for:

begin proctest(?); end;

but does not work for:

begin user.package.proctest(?); end;

DBD::Oracle::st execute failed: ORA-06550: line 1, column 11:
PLS-00302: component 'PACKAGE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored (DBD ERROR: error possibly near <*> 
indicator at char 10 in 'begin user.<*>package.proctest(:p1); end;') 
[for Statement "begin user.package.proctest(?); end;" with 
ParamValues: :p1=99] at procfail.pl line 17.


Any idea how to call a procedure in a package in a particular uses 
schema because that would be a complete solution for me then?


Martin

Re: Oracle procedure raises exception but looks to be successful from DBI

[Martin Evans]

John Scoles wrote:
That is an odd one,  I will check with the DBAs here to see if this is 
normal Oracle behavior



Well the error id correct but and on my box an error is thrown that I catch

I did change my code over to

"begin track.proctest(?) end;"

what version of DBI and DBD are you using??

cheers




Thanks for the reply John.

I am using Oracle XE, DBI 1.59 and DBD::Oracle 1.19.

I changed my sql to "begin proctest(?); end;" and lo and behold I get an 
error:


$ perl procfail.pl
DBD::Oracle::st execute failed: ORA-01403: no data found
ORA-06512: at "XXX.PROCTEST", line 4
ORA-06512: at line 1 (DBD NO_DATA: OCIStmtExecute) [for Statement "begin 
proctest(?); end;" with ParamValues: :p1=99] at procfail.pl line 17.


Any idea why that is? Is it something to do with those tests in 
DBD::Oracle (dbdimp.c) for whether the statement is a select or not or 
perhaps because DBD::Oracle recognises this as a procedure call now.


I can change to put begin/end around procedure calls but I'd like to 
know why this is required.


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


Martin Evans wrote:

Hi,

I've just spent a long time tracking down a bug in an Oracle procedure 
because DBI's execute method returned success even though the 
procedure raised an exception. Shouldn't exceptions raised in 
procedures cause execute to fail?


Here is an example:

use DBI;
use strict;
use warnings;

my $h = DBI->connect("xxx","xxx","xxx",{RaiseError=>1,PrintError=>1});
eval {$h->do("drop table test");};
$h->do("create table test (a integer)");
$h->do(q{create or replace procedure proctest(vv integer) as
x integer;
begin
select a into x from test where a = vv;
end;});

my $s = $h->prepare("call proctest(?)");
$s->bind_param(1, 99);
my $y = $s->execute;
print "$y\n";
print $s->err(), $s->errstr(), $s->state(), "\n";

which outputs:

0E0
Use of uninitialized value in print at procfail.pl line 19.
Use of uninitialized value in print at procfail.pl line 19.

If I run exactly the same procedure from sqlplus I get an error:

SQL> execute proctest(99);
BEGIN proctest(99); END;

*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "BET.PROCTEST", line 4
ORA-06512: at line 1

If I replace the procedure with a single call to 
raise_application_error execute does fail.


I admit there is a bug in the procedure causing the exception which 
should have been caught but I was very surprised to find an exception 
in this procedure did not cause execute to fail.


Any ideas or suggestions.

Martin

Re: Oracle procedure raises exception but looks to be successful from DBI

[Martin Evans]

John Scoles wrote:

Wow I go one right for once.

Not 100% sure on why that is? Me thinks when you use 'Begin End' it 
forces OCI to take it as an pseudo 'stored procedure' and runs in the 
current OCI client.


I think using call just executes as a thread off the present client and 
in the background someplace??


You might want to try 'exec proctest(?); '


Doesn't work:

DBD::Oracle::st execute failed: ORA-00900: invalid SQL statement (DBD 
ERROR: OCIStmtExecute) [for Statement "exec proctest(?)" with 
ParamValues: :p1=99] at procfail.pl line 17.



I think it does it in the local client.

Will have to crack open the OCI  docs to see and  as I am not suffering 
from insomnia right now I have no real want to start digging though it 
right now.


Cheers


Your solution with begin/end works for:

begin proctest(?); end;

but does not work for:

begin user.package.proctest(?); end;

DBD::Oracle::st execute failed: ORA-06550: line 1, column 11:
PLS-00302: component 'PACKAGE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored (DBD ERROR: error possibly near <*> indicator 
at char 10 in 'begin user.<*>package.proctest(:p1); end;') [for 
Statement "begin user.package.proctest(?); end;" with ParamValues: 
:p1=99] at procfail.pl line 17.


Any idea how to call a procedure in a package in a particular uses 
schema because that would be a complete solution for me then?


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


Martin Evans wrote:

John Scoles wrote:
That is an odd one,  I will check with the DBAs here to see if this 
is normal Oracle behavior



Well the error id correct but and on my box an error is thrown that I 
catch


I did change my code over to

"begin track.proctest(?) end;"

what version of DBI and DBD are you using??

cheers




Thanks for the reply John.

I am using Oracle XE, DBI 1.59 and DBD::Oracle 1.19.

I changed my sql to "begin proctest(?); end;" and lo and behold I get 
an error:


$ perl procfail.pl
DBD::Oracle::st execute failed: ORA-01403: no data found
ORA-06512: at "XXX.PROCTEST", line 4
ORA-06512: at line 1 (DBD NO_DATA: OCIStmtExecute) [for Statement 
"begin proctest(?); end;" with ParamValues: :p1=99] at procfail.pl 
line 17.


Any idea why that is? Is it something to do with those tests in 
DBD::Oracle (dbdimp.c) for whether the statement is a select or not or 
perhaps because DBD::Oracle recognises this as a procedure call now.


I can change to put begin/end around procedure calls but I'd like to 
know why this is required.


Martin

Re: Oracle procedure raises exception but looks to be successful from DBI

[Martin Evans]

Jeffrey Seger wrote:

begin proctest(?); end;

is the more desirable syntax for calling oracle procedures.  It's an
anonymous pl/sql block.  I personally had never seen the "call proctest()"
syntax used in Oracle. It's valid syntax, but it doesn't appear to be doing
what you want it to do.

According to the Oracle documentation:

Using the BEGIN .. END block is recommended in several situations. Calling
the subprogram from a BEGIN .. END block allows named or mixed notation for
parameters which the CALL statement does not support. For information on
named parameters, see "Using Positional, Named, or Mixed Notation for
Subprogram 
Parameters"<http://localhost/oracle/B19306_01/appdev.102/b14261/subprograms.htm#i4072>.
In addition, using the CALL statement can suppress an ORA-01403: no data
found error that has not been handled in the PL/SQL subprogram.

Pretty specific.


Thanks for that, I did not see that.


As  far as

"begin user.package.proctest(11); end;"

failingwell it's right there in the error message.  proctest is not in a
package called "package".  In fact, there is no package named "package".

user.proctest(11) will fail as well since there is no user named "user".
user is a variable containing the name of the currently signed in user.


I know that, it was just an example. Assuming there is a user called 
fred and a package called test containing a procedure called proctest:


begin fred.test.proctest(11); end;

does not work where:

call fred.test.proctest(11)

does work.


Try just username.proctest(11) where username is the user you are connecting
as.


Thanks for documentation pointer.

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



On Jan 25, 2008 11:42 AM, Martin Evans <[EMAIL PROTECTED]> wrote:


John Scoles wrote:

That is an odd one,  I will check with the DBAs here to see if this is
normal Oracle behavior


Well the error id correct but and on my box an error is thrown that I

catch

I did change my code over to

"begin track.proctest(?) end;"

what version of DBI and DBD are you using??

cheers



Thanks for the reply John.

I am using Oracle XE, DBI 1.59 and DBD::Oracle 1.19.

I changed my sql to "begin proctest(?); end;" and lo and behold I get an
error:

$ perl procfail.pl
DBD::Oracle::st execute failed: ORA-01403: no data found
ORA-06512: at "XXX.PROCTEST", line 4
ORA-06512: at line 1 (DBD NO_DATA: OCIStmtExecute) [for Statement "begin
proctest(?); end;" with ParamValues: :p1=99] at procfail.pl line 17.

Any idea why that is? Is it something to do with those tests in
DBD::Oracle (dbdimp.c) for whether the statement is a select or not or
perhaps because DBD::Oracle recognises this as a procedure call now.

I can change to put begin/end around procedure calls but I'd like to
know why this is required.

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


Martin Evans wrote:

Hi,

I've just spent a long time tracking down a bug in an Oracle procedure
because DBI's execute method returned success even though the
procedure raised an exception. Shouldn't exceptions raised in
procedures cause execute to fail?

Here is an example:

use DBI;
use strict;
use warnings;

my $h = DBI->connect("xxx","xxx","xxx",{RaiseError=>1,PrintError=>1});
eval {$h->do("drop table test");};
$h->do("create table test (a integer)");
$h->do(q{create or replace procedure proctest(vv integer) as
x integer;
begin
select a into x from test where a = vv;
end;});

my $s = $h->prepare("call proctest(?)");
$s->bind_param(1, 99);
my $y = $s->execute;
print "$y\n";
print $s->err(), $s->errstr(), $s->state(), "\n";

which outputs:

0E0
Use of uninitialized value in print at procfail.pl line 19.
Use of uninitialized value in print at procfail.pl line 19.

If I run exactly the same procedure from sqlplus I get an error:

SQL> execute proctest(99);
BEGIN proctest(99); END;

*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "BET.PROCTEST", line 4
ORA-06512: at line 1

If I replace the procedure with a single call to
raise_application_error execute does fail.

I admit there is a bug in the procedure causing the exception which
should have been caught but I was very surprised to find an exception
in this procedure did not cause execute to fail.

Any ideas or suggestions.

Martin

Re: Silly Question

[Martin Evans]

John Scoles wrote:

Who is the current maintainer of DBD::MySQL??




Patrick Galbraith I think.

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

DBD::ODBC 1.15 released to CPAN

[Martin Evans]

Since things seemed to have quietened down since the last development 
release of DBD::ODBC I have released 1.15 to CPAN today. The changes in 
1.15 (final) and previous development releases are listed below.  There 
are some fairly serious changes in this version compared with 1.14.


=head2 Changes in DBD::ODBC 1.15 January 29, 2008

Fixed bug reported by Toni Salomaki where DBD::ODBC may call
SQLNumResultCols after SQLMoreResults returns SQL_NO_DATA. It led to
the error:

Describe failed during DBI::st=HASH(0x19c2048)->FETCH(NUM_OF_FIELDS,0)

when NUM_OF_FIELDS was referenced in the Perl script.

Updated odbc_exec_direct documentation to describe its requirement
when creating temporary objects in SQL Server.

Added FAQ on SQL Server temporary tables.

Fixed bug in dbdimp.c which was using SQL_WCHAR without testing it was
defined - thanks Jergen Dutch.

Fixed use of "our" in UCHelp.pm which fails on older Perls.

Minor changes to 02simple.t and 03dbatt.t to fix diagnostics output
and help debug DBD which does not handle long data properly.

Further changes to Makefile.PL to avoid change in behavior of
ExtUtils::MakeMaker wrt order of execution of PREREQ_PM and CONFIGURE.
Now if DBI::DBD is not installed we just warn and exit 0 to avoid a
cpan-testers failure.

=head2 Changes in DBD::ODBC 1.15_2 November 14, 2007

Fix bug in DBD::ODBC's private function data_sources which was
returning data sources prefixed with "DBI:ODBC" instead of "dbi:ODBC".

If you don't have at least DBI 1.21 it is now a fatal error instead of
just a warning.

DBI->connect changed so informational diagnostics like "Changed
database context to 'master'" from SQL Server are available in
errstr/state. These don't cause DBI->connect to die but you can test
$h->err eq "" after connect and obtain the informational diagnostics
from errstr/state if you want them.

Fixed problem in 41Unicode.t where utf8 was used before testing we had
a recent enough Perl - thank you cpan testers.

Changed "our" back to "my" in Makefile.PL - thank you cpan testers.

Removed all calls to DBIh_EVENT2 in dbdimp.c as it is no longer used
(see posts on dbi-dev).

Changed text output when a driver manager is not found to stop
referring to iodbcsrc which is no longer included with DBD::ODBC.

Changed Makefile.PL to attempt to find unixODBC if -o or ODBCHOME not
specified.

Updated META.yml based on new 1.2 spec.

Changed Makefile.PL so if an ODBC driver manager is not found then we
issue warning and exit cleanly but not generating a Makefile. This
should stop cpan-testers from flagging a fail because they haven't got
an ODBC driver manager.

Changed Makefile.PL so if an ODBC driver manager is not found then we
issue warning and exit cleanly but not generating a Makefile. This
should stop cpan-testers from flagging a fail because they haven't got
an ODBC driver manager.

Changed Makefile.PL so it no longer "use"s DBI/DBI::DBD because this
makes cpan-testers log a fail if DBI is not installed. Changed to put
the DBI::DBD use in the CONFIGURE sub so PREREQ_PM will filter out
machines where DBI is not installed.

Fix a possible typo, used once in 10handler.t.

=head2 Changes in DBD::ODBC 1.15_1 November 6, 2007

Minor changes to 20SqlServer.t test for SQL Server 2008 (Katmai).
Timestamps now return an extra 4 digits of precision (all ) and
the driver reported in dbcc messages has a '.' in the version which
was not handled.

New FAQ entry and test code for "Datetime field overflow" problem in
Oracle.

Changed all ODBC code to use new SQLLEN/SQLULEN types where
Microsoft's headers indicate, principally so DBD::ODBC builds and
works on win64. NOTE: you will need an ODBC Driver Manager on UNIX
which knows SQLLEN/SQLULEN types. The unixODBC driver manager uses
SQLLEN/SQLULEN in versions from at least 2.2.12. Thanks to Nelson
Oliveira for finding, patching and testing this and then fixing
problems with bound parameters on 64 bit Windows.

Added private_attribute_info method DBI introduced (see DBI docs)
and test cases to 02simple.t.

Fairly major changes to dbd_describe in dbdimp.c to reduce ODBC calls
by 1 SQLDescribeCol call per column when describing result
sets. Instead of calculating the amount of memory required to hold
every column name we work on the basis that (num_columns + 1) *
SQL_MAX_COLUMN_NAME_LEN can hold all column names. However, to avoid
using a large amount of memory unnecessarily if an ODBC driver
supports massive column name lengths the maximum size per column is
restricted to 256.

Changed to avoid using explicit use of DBIc_ERRXXX in favour of newish
(ok, DBD::ODBC is a bit behind the times in this respect)
DBIh_SET_ERR_CHAR.  This involved a reworking or the error handling
and although all test cases still pass I cannot guarantee it has no
other effects - please let me know if you spot differences in error
messages.

Fixed bug in 20SqlServer test for multiple results that was passing
but for the wrong reason (namely, that the odbc_err_handler was

Re: Perl and ODBC on Win32

[Martin Evans]

doktorZee wrote:

Salutations all, I'm working on a Perl script to migrate data through
ODBC from a series of tables on one DBMS to a single table on a
separate DBMS, with access connections defined in the ODBC Sources
Win32 control panel applet.

It works fine on my computer and a co-worker's computer, which both
run Windows XP. However, on the client's computer which runs Windows
2000, it gives an ODBC error. All three setups use MS SQL 2000 and
ActiveState Perl 5.8.8.  The operating system is the only difference,
and why it could be the source of the problem I don't know.

On Windows 2000 I get:
DBD:ODBC::st fetchrow_arrayref failed: [Microsoft][ODBC SQL Server
Driver] String data, right truncation (SQL-01004)(DBD: st_fetch/
SQLFetch (long truncated DBI attribute LongTruncOK not set and/or
LongReadLen too small) err=-1)

I searched online for the error and all I could find was that this
should work:
$dbh->{LongReadLen} = 16384;
$dbh->{LongTruncOk} = 1;

I tried this, and it does not impair the functionality on the XP
systems but it does not resolve the problem. I also did some analysis
on the data, and found that the source DB has data that is 100 or 200
characters (nvarchar) and the target DB takes 255 characters of
nvarchar. The largest piece of data is 80 characters, so there really
shouldn't be any truncation problem to begin with.  Any help would be
greatly appreciated, as its been weeks of no leads or success and I'm
going insane.  Thanks again.


Can you send me DBI_TRACE=15 output from running your script on the 
broken machine. The only way to get data truncated error on a fetch is 
if the buffer DBD::ODBC supplies is too small. If you really have set 
LongTruncOk then the error should be ignored by DBD::ODBC.


It would also be useful to know:

DBI version
DBD::ODBC version
ODBC Driver Manager version (see your DataSources in control panel)
version of the ODBC driver you are using (as above)

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

Re: Redefinition of target errors

[Martin Evans]

Michael Leonida wrote:

I am getting 3 duplicate (3*2=6 total) redefinition of target errors when I
try to make the latest DBD::ODBC 1.15 from CPAN. I am using the Borland C++
compiler on Windows XP Pro. The duplicate redefinitions occur after these
two lines in the Makefile on blank lines:

$(OBJECT) : $(PERL_HDRS)

$(OBJECT) : $(FIRST_MAKEFILE

I am also using ActivePerl 5.10.0. During running Makefile.PL I get a bunch
of "probably harmless" Notes that some libraries were not found, including
ODBC32.LIB.

Here are the errors:

Error makefile 908: Redefinition of target 'ConvertUTF.obj'

Error makefile 908: Redefinition of target 'ODBC.obj'

Error makefile 908: Redefinition of target 'dbdimp.obj'

Error makefile 915: Redefinition of target 'ConvertUTF.obj'

Error makefile 915: Redefinition of target 'ODBC.obj'

Error makefile 915: Redefinition of target 'dbdimp.obj'



Any ideas?

Thanks,

Mike



What make are you using (nmake? dmake?) and did you specify the make you 
are using on the command line for perl Makefile.PL e.g.,


perl Makefile.PL MAKE=nmake

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

Re: Redefinition of target errors

[Martin Evans]

Martin Evans wrote:

Michael Leonida wrote:
I am getting 3 duplicate (3*2=6 total) redefinition of target errors 
when I
try to make the latest DBD::ODBC 1.15 from CPAN. I am using the 
Borland C++

compiler on Windows XP Pro. The duplicate redefinitions occur after these
two lines in the Makefile on blank lines:

$(OBJECT) : $(PERL_HDRS)

$(OBJECT) : $(FIRST_MAKEFILE

I am also using ActivePerl 5.10.0. During running Makefile.PL I get a 
bunch
of "probably harmless" Notes that some libraries were not found, 
including

ODBC32.LIB.

Here are the errors:

Error makefile 908: Redefinition of target 'ConvertUTF.obj'

Error makefile 908: Redefinition of target 'ODBC.obj'

Error makefile 908: Redefinition of target 'dbdimp.obj'

Error makefile 915: Redefinition of target 'ConvertUTF.obj'

Error makefile 915: Redefinition of target 'ODBC.obj'

Error makefile 915: Redefinition of target 'dbdimp.obj'



Any ideas?

Thanks,

Mike



What make are you using (nmake? dmake?) and did you specify the make you 
are using on the command line for perl Makefile.PL e.g.,


perl Makefile.PL MAKE=nmake

Martin


This thread continued off the dbi-users list as Michael was not getting 
dbi-users list emails for some reason.


As it turned out, Michael was using Borland Make which it appears is not 
well supported by ExtUtils::MakeMaker. The following may be useful for 
anyone else using Borland Make:


ExtUtils::MakeMaker only specifically supports dmake and nmake on 
Windows and not Borland make although it does support Borlands C compiler.


There is an interesting thread on perlmonks about Borland make:

http://www.perlmonks.org/?node_id=62924

which mentions a Perl version of make and changing your Config.pm to use it.

Also, from the Perl README for Windows (perldoc perlwin32):

Borland C++
If you are using the Borland compiler, you will need dmake.  (The make 
that Borland supplies is seriously crippled and will not work for 
MakeMaker builds.)


You can get nmake from the following links:

ftp://ftp.microsoft.com/Softlib/MSLFILES/nmake15.exe
http://download.microsoft.com/download/vc15/Patch/1.52/W95/EN-US/Nmake15.exe

Hope this proves useful.

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

Re: DBD::ODBC - default timeout is 30 seconds instead of none

[Martin Evans]

Dino Kern wrote:

Hello,
I'm using  DBD::ODBC Version 1.13 and had following problem:
Te connection timed out after exactly 30 seconds even when
"odbc_query_timeout" was not set.
According to the documentation there should be no timeout in this case.
After setting the timeout to a higher value it worked.

Maybe the documentation has to be rectified to:
"Default timeout is 30 second".

Best regards,
Dino




Don't confuse connection timeout and query timeout - they are not the 
same. Connection timeout only applies to non login and query execution 
ODBC APIS.


The default in DBD::ODBC is actually to not set it at all because the 
current DBD::ODBC code sets the default internal variable 
odbc_query_timeout to 0 and then does:


if (imp_dbh->odbc_query_timeout) {
  ret = odbc_set_query_timeout(dbh, stmt,
  imp_dbh->odbc_query_timeout);
}

in the execdirect and prepare methods which obviously does not get run 
if odbc_query_timeout is 0.


The ODBC spec says:

o the default is 0 which is no timeout
o the driver may implement minimum and maximum values the timeout may be 
set to.


so not setting the query timeout should use the default of 0 or no 
timeout. I believe the DBD::ODBC code is correct in this other than if 
you were to set it to N (where N <> 0) then set it to 0, the second set 
would not get executed - I will rectify this.


I suspect the ODBC driver you are using is defaulting it to 30 as 
DBD::ODBC is not setting the timeout by default.


I will attempt to make this clearer in the pod for the next DBD::ODBC.

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

Re: Redefinition of target errors

[Martin Evans]

Michael Leonida wrote:
I got nmake from one of the links, thank you. Also, thanks for all your 
help.


No problem - just bare in mind Windows is neither a platform I work on 
regularly nor one I want to work on regularly so you should probably 
take any windows advice from me with a pinch of salt.


I still get an error that cl is not recognized as an internal or 
external command (there is a call to cl with a lot of parameters that 
also gets printed on the screen).


ExtUtils::MakeMaker is now thinking you are building with the VC 
compiler instead of the Borland compiler. I see in ExtUtils::MM_Win32


my $BORLAND = 1 if $Config{'cc'} =~ /^bcc/i;

so I'd guess your Config.pm does not contain bcc or bcc32 i.e. your Perl 
was not built with Borland's compiler. I can't remember if you said your 
Perl was activestate. All the modules you build with a C compiler need 
to built with the same compiler used for Perl itself and that is why 
ExtUtils gets 'cc' from Config.pm.


Do I need the Visual C++ compiler in 
order to use nmake, or is it good to go as is? If I need VC++, what's 
the cheapest way to get it?


No, nmake just runs the commands in the Makefile some of which will be 
commands to compile code (and hence use your compiler). Your first 
problem was you were generating an nmake/dmake Makefile which Borland's 
make did not understand. However, if your Perl was not built with 
Borlands compiler you will need to get hold of the compiler, used to 
build Perl:


perl -MConfig -le "print $Config{cc};"

You can get Visual Studio Express (for free) from Microsoft but you 
might want to read the README.Windows (or whatever it is) in the Perl 
source tree as it makes recommendations on other stuff you need to get.


Also as far as Borland goes my compiler is called bcc32. I wasn't able 
to find a link or a way to get dmake for Borland.


See above.



The Perl version of make link in the other thread is not working.


ok, it was worth a shot.


I am finally getting the emails from this mailing list.


:-)

The last time I tried to compile a perl module written in C for Windows 
it was a real PITA (I think I was trying to build DBD::ODBC to run with 
ActiveState Perl). I gave up in the end and built my own Perl from 
scratch. Welcome to the wonderful world of Microsoft Windows ;-) If you 
have something like ActiveState perl try and get a DBD::ODBC binary from 
them too.


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


On 2/21/08, *Martin Evans* <[EMAIL PROTECTED] 
<mailto:[EMAIL PROTECTED]>> wrote:


Martin Evans wrote:
 > Michael Leonida wrote:
 >> I am getting 3 duplicate (3*2=6 total) redefinition of target errors
 >> when I
 >> try to make the latest DBD::ODBC 1.15 from CPAN. I am using the
 >> Borland C++
 >> compiler on Windows XP Pro. The duplicate redefinitions occur
after these
 >> two lines in the Makefile on blank lines:
 >>
 >> $(OBJECT) : $(PERL_HDRS)
 >>
 >> $(OBJECT) : $(FIRST_MAKEFILE
 >>
 >> I am also using ActivePerl 5.10.0. During running Makefile.PL I
get a
 >> bunch
 >> of "probably harmless" Notes that some libraries were not found,
 >> including
 >> ODBC32.LIB.
 >>
 >> Here are the errors:
 >>
 >> Error makefile 908: Redefinition of target 'ConvertUTF.obj'
 >>
 >> Error makefile 908: Redefinition of target 'ODBC.obj'
 >>
 >> Error makefile 908: Redefinition of target 'dbdimp.obj'
 >>
 >> Error makefile 915: Redefinition of target 'ConvertUTF.obj'
 >>
 >> Error makefile 915: Redefinition of target 'ODBC.obj'
 >>
 >> Error makefile 915: Redefinition of target 'dbdimp.obj'
 >>
 >>
 >>
 >> Any ideas?
 >>
 >> Thanks,
 >>
 >> Mike
 >>
 >
 > What make are you using (nmake? dmake?) and did you specify the
make you
 > are using on the command line for perl Makefile.PL e.g.,
 >
 > perl Makefile.PL MAKE=nmake
 >
 > Martin

This thread continued off the dbi-users list as Michael was not getting
dbi-users list emails for some reason.

As it turned out, Michael was using Borland Make which it appears is not
well supported by ExtUtils::MakeMaker. The following may be useful for
anyone else using Borland Make:

ExtUtils::MakeMaker only specifically supports dmake and nmake on
Windows and not Borland make although it does support Borlands C
compiler.

There is an interesting thread on perlmonks about Borland make:

http://www.perlmonks.org/?

Re: perl DBI on windows 64

[Martin Evans]

Ramakrishna Raju wrote:

Hi,

 


I am trying to connect to SQL Server 2005 on a windows 64bit
machine from the same machine. And I get this error message:

 


DBI connect(xxxsql01,'sa',...) failed: [Microsoft][ODBC Driver Manager]
Data source name not found and no default driver specified (SQL-IM002)
at a.pl line 10

Can't connect to dbi:ODBC:xxxsql01: [Microsoft][ODBC Driver Manager]
Data source name not found and no default driver specified (SQL-IM002)
at a.pl line 10.

 


The Perl script is:

#===

use strict;

 


use DBI;

 


my $data_source = q/dbi:ODBC:xxxsql01/;

my $user = q/sa/;

my $password = q/xx/;

 


# Connect to the data source and get a handle for that connection.

my $dbh = DBI->connect($data_source, $user, $password)

or die "Can't connect to $data_source: $DBI::errstr";

#=

 


If I run this script from a 32-bit windows machine, it is
able to connect successfully.

 


I applied the latest DBD-ODBC 1.15 version (Martin J Evans)
and it still is a problem. Is there any fix or work around for this
issue?

 

 


Appreciate your time,

 


Thanks,

Ramakrishna Raju ( "Raju" )

[EMAIL PROTECTED]  

 



DISCLAIMER: NOTICE REGARDING PRIVACY AND CONFIDENTIALITY 


This e-mail, and any attachments thereto, is intended only for use by the addressee(s) named herein 
and may contain legally privileged and/or confidential information. If you are not the intended 
recipient of this e-mail, you are hereby notified that any dissemination, distribution or copying 
of this e-mail, and any attachments thereto, is strictly prohibited. If you have received this 
e-mail in error, please immediately notify me and permanently delete the original and any copy of 
any e-mail and any printout thereof. E-mail transmission cannot be guaranteed to be secure or 
error-free. The sender therefore does not accept liability for any errors or omissions in the 
contents of this message which arise as a result of e-mail transmission. TJM Proprietary Trading, 
LLC ("TJMPT") may, at its discretion, monitor and review the content of all e-mail 
communications. TJMPT is a registered  joint back office broker dealer with the Chicago Board of 
Options Exchange "CBOE".  TJMPT's o

ffice is located at 318 W. Adams Floor 9, Chicago, IL 60606. For more 
information about  TJMPT, please call us at (312)-432- 5100 or fax 
(312)-432-4498.

Are you sure you have created a 64bit data source called xxxsql01. In 
Windows 64 there are 2 odbc driver managers - one for 32bit and one for 
64bit. If you Perl is 64bit you'll need to create a 64bit ODBC data 
source and if you Perl is 32bit you'll need to create a 32bit data source.


Of course, it may just be you created a user DSN and you are not logged 
in as that user.


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

Re: perl DBI on windows 64

[Martin Evans]

Sorry for the mess of replying but for some reason my email client won't 
include your text when it was added after my signature. There are 2 odbc 
administrators - one you get to in the normal way through control panel, 
administrative tools, data sources (the 64 bit one) and one you have to 
go to X:\windows\syswow64 and double click on odbcad32.exe. If you start 
both you should see you data source in one and not the other.


You will need to use the latter one.

Martin

Ramakrishna Raju wrote:

Martin,

My Perl on the 64-bit windows is 32 bit. The version string is:

D:\perl\testing>perl -v

This is perl, v5.8.8 built for MSWin32-x86-multi-thread
(with 33 registered patches, see perl -V for more detail)

It's a System DSN entry and references the SQL Server driver
called SQLSRV32.DLL . The name implies it's a 32bit driver. When I add
an ODBC entry the drivers tab in the wizard lists only 2 drivers: SQL
Native Client ( SQLNCLI.DLL ) and SQL Server (SQLSRV32.DLL )

And my SQL Server version string is:


Microsoft SQL Server 2005 - 9.00.3054.00 (X64)
Mar 23 2007 18:41:50
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790:
Service Pack 2)

And Windows is: Windows server 2003 Standard x64 edition, SP2 build
3790.

I searched the Windows folder for sqlsrv32.dll and I see 2
files.


C:\WINDOWS>dir sqlsrv*.DLL /s
 Volume in drive C has no label.
 Volume Serial Number is 4C12-6CB9

 Directory of C:\WINDOWS\system32

02/18/2007  07:00 AM   671,744 sqlsrv32.dll
   1 File(s)671,744 bytes

 Directory of C:\WINDOWS\SysWOW64

02/18/2007  07:00 AM   446,464 sqlsrv32.dll
   1 File(s)446,464 bytes

 Total Files Listed:
   2 File(s)  1,118,208 bytes
   0 Dir(s)  10,055,450,624 bytes free

C:\WINDOWS>
=

Now, I don't know which one is used by the odbc wizard. Is the problem?

Thanks
Raju.

Re: perl DBI on windows 64

[Martin Evans]

Alexander Foken wrote:

On 13.03.2008 19:49, Ramakrishna Raju wrote:

And now, I am looking for a web link or a short snippet that
does robust error handling of SQL errors. 

Use the RaiseError DBI attribute, preferably during connect().


And how to process the output
of sql print statements. 
SQL does not print, it has no print statements (at least there not 
portable ones). You may want to print what $sth->fetchXXX returns. For 
debugging, you may want to use Data::Dumper.


In SQL Server there is a print statement that may be used in procedures 
- see the t/20SQLServer test.



I've done Sybase db-lib programming more than
15 years back and I realize that are 2 channels back to the client, a
message handler and an error handler. How is it done in perl odbc?
  
You don't care about that. DBI will handle that for you. Sybase db-lib 
is one level below DBI. Look at the DBI documentation 
. There is also an O'Reily book 
about the DBI , it even has an 
example collection page online at .


There is one annoyance with SQL server: You can't have more than one 
"active" statement, i.e.  a statement that is executing but not yet 
finished, per connection. This is a limitation of the SQL server 
protocol, not a DBI limitation. Other databases, like Oracle and the 
free PostgreSQL, can handle at least a sufficiently large number of 
parallel active statements. For the MS SQL Server, you have to use 
several distinct connections if you need parallel active statements.


That is no longer true Alexander:

MARS. Multiple Active Result Sets (MARS), enables your applications to 
have more than one pending request per connection, and, in particular to 
have more than one default result set open per connection. The MARS 
feature removes the restriction present in earlier versions of SQL 
Server in which an open default result set blocks the driver from 
sending requests to the server until the entire result set is consumed.


Personally, I would never start a project on MS SQL Server if I can use 
Oracle or PostgreSQL. Not just because of that limitation, but also 
because of some other annoyances, like the trigger implementation and 
the regular deadlocks of MS SQL Server.


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

Re: perl DBI on windows 64

[Martin Evans]

Ramakrishna Raju wrote:

Martin,

Great !! That works. I am so happy now. I went thru so much
frustration I almost abandoned the Perl approach. Thanks, Martin. 


And now, I am looking for a web link or a short snippet that
does robust error handling of SQL errors. And how to process the output
of sql print statements. I've done Sybase db-lib programming more than
15 years back and I realize that are 2 channels back to the client, a
message handler and an error handler. How is it done in perl odbc?


Glad you are working now - I will perhaps add that as a FAQ entry to 
DBD::ODBC.


For capturing sql server print statements see the t/20SqlServe test 
which contains an example.


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

Thanks, 
Ramakrishna Raju ( "Raju" )

[EMAIL PROTECTED]

-Original Message-----
From: Martin Evans [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 13, 2008 12:28 PM

To: dbi-users@perl.org
Subject: Re: perl DBI on windows 64

Sorry for the mess of replying but for some reason my email client won't

include your text when it was added after my signature. There are 2 odbc

administrators - one you get to in the normal way through control panel,

administrative tools, data sources (the 64 bit one) and one you have to 
go to X:\windows\syswow64 and double click on odbcad32.exe. If you start


both you should see you data source in one and not the other.

You will need to use the latter one.

Martin

Ramakrishna Raju wrote:

Martin,

My Perl on the 64-bit windows is 32 bit. The version string is:

D:\perl\testing>perl -v

This is perl, v5.8.8 built for MSWin32-x86-multi-thread
(with 33 registered patches, see perl -V for more detail)

It's a System DSN entry and references the SQL Server driver
called SQLSRV32.DLL . The name implies it's a 32bit driver. When I add
an ODBC entry the drivers tab in the wizard lists only 2 drivers: SQL
Native Client ( SQLNCLI.DLL ) and SQL Server (SQLSRV32.DLL )

And my SQL Server version string is:


Microsoft SQL Server 2005 - 9.00.3054.00 (X64)
Mar 23 2007 18:41:50
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790:
Service Pack 2)

And Windows is: Windows server 2003 Standard x64 edition, SP2 build
3790.

I searched the Windows folder for sqlsrv32.dll and I see 2
files.


C:\WINDOWS>dir sqlsrv*.DLL /s
  Volume in drive C has no label.
  Volume Serial Number is 4C12-6CB9

  Directory of C:\WINDOWS\system32

02/18/2007  07:00 AM   671,744 sqlsrv32.dll
1 File(s)671,744 bytes

  Directory of C:\WINDOWS\SysWOW64

02/18/2007  07:00 AM   446,464 sqlsrv32.dll
1 File(s)446,464 bytes

  Total Files Listed:
2 File(s)  1,118,208 bytes
0 Dir(s)  10,055,450,624 bytes free

C:\WINDOWS>
=

Now, I don't know which one is used by the odbc wizard. Is the problem?

Thanks
Raju.

DISCLAIMER: NOTICE REGARDING PRIVACY AND CONFIDENTIALITY 


This e-mail, and any attachments thereto, is intended only for use by the addressee(s) named herein 
and may contain legally privileged and/or confidential information. If you are not the intended 
recipient of this e-mail, you are hereby notified that any dissemination, distribution or copying 
of this e-mail, and any attachments thereto, is strictly prohibited. If you have received this 
e-mail in error, please immediately notify me and permanently delete the original and any copy of 
any e-mail and any printout thereof. E-mail transmission cannot be guaranteed to be secure or 
error-free. The sender therefore does not accept liability for any errors or omissions in the 
contents of this message which arise as a result of e-mail transmission. TJM Proprietary Trading, 
LLC ("TJMPT") may, at its discretion, monitor and review the content of all e-mail 
communications. TJMPT is a registered  joint back office broker dealer with the Chicago Board of 
Options Exchange "CBOE".  TJMPT's o

ffice is located at 318 W. Adams Floor 9, Chicago, IL 60606. For more 
information about  TJMPT, please call us at (312)-432- 5100 or fax 
(312)-432-4498.