Re: DBD::Oracle 1.19 in FreeBSD Ports (was Re: help with odd DBI perpare/execute errors)
Hi! There's a newer linux-oracle-instantclient-sdk available in the ports (10.2.0.3), and maybe it helps to compile and link a newer DBD::Oracle. I have that one (actually, those three), but they are nine years old as well: 20061115_5 is coded into the ports name. The API did not change that much, so I guess it might work to use 10.2.0.3 to update the DBD::Oracle port to 1.74. I'm currently upgrading my test host from perl5.20 to perl5.22, so it will take some time until I can experiment with 1.74. -- p...@opsec.eu+49 171 3101372 5 years to go !
Re: DBD::Oracle 1.19 in FreeBSD Ports (was Re: help with odd DBI perpare/execute errors)
According to Kurt Jaeger dbi-us...@opsec.eu on Thu, 06/04/15 at 09:38: My guess: no-one had the need, time and skills to fix it. There's a newer linux-oracle-instantclient-sdk available in the ports (10.2.0.3), and maybe it helps to compile and link a newer DBD::Oracle. I have that one (actually, those three), but they are nine years old as well: 20061115_5 is coded into the ports name. And there's an newer version (12.1.0.2.0) pending commit: https://bugs.freebsd.org/bugzilla/show_bug.cgi?id=157544 Thanks! :-) Regards, web... -- /\ ASCII RIBBON / William Bulley \ / CAMPAIGN AGAINST / XHTML E-MAIL AND / E-MAIL: w...@umich.edu / \ LISTSERV POSTINGS / 72 characters width template -|
Re: DBD::Oracle 1.19 in FreeBSD Ports (was Re: help with odd DBI perpare/execute errors)
Hi! What is confusing to me is why FreeBSD _still_ contains DBD::Oracle 1.19 in their ports tree! After all these years! Because: DBD::Oracle no longer supports Oracle client versions before 9.2 Try a version before 1.25 for 9 and 1.18 for 8! at ./Makefile.PL line 271. and there is no oracle9-client port on FreeBSD. -- p...@opsec.eu+49 171 3101372 5 years to go !
Re: DBD::Oracle 1.19 in FreeBSD Ports (was Re: help with odd DBI perpare/execute errors)
Hi! There's a newer linux-oracle-instantclient-sdk available in the ports (10.2.0.3), and maybe it helps to compile and link a newer DBD::Oracle. I have that one (actually, those three), but they are nine years old as well: 20061115_5 is coded into the ports name. The API did not change that much, so I guess it might work to use 10.2.0.3 to update the DBD::Oracle port to 1.74. I'm currently upgrading my test host from perl5.20 to perl5.22, so it will take some time until I can experiment with 1.74. It looks promising, but: The FreeBSD ld does not like the SYSV shared lib to link with: LD_RUN_PATH=/compat/linux/usr/lib/oracle/10.2.0.3/client/lib cc -shared -L/usr/ports/lang/perl5.22/work/perl-5.22.0 -L/usr/local/lib/perl5/5.22/mach/CORE -Wl,-rpath=/usr/local/lib/perl5/5.22/mach/CORE -lperl -L/usr/local/lib -fstack-protector Oracle.o dbdimp.o oci8.o -o blib/arch/auto/DBD/Oracle/Oracle.so -L/compat/linux/usr/lib/oracle/10.2.0.3/client/lib -L/usr/local/home/pi/myp/databases/p5-DBD-Oracle/work/DBD-Oracle-1.74/../.. -locci -lclntsh -lpthread /usr/bin/ld: skipping incompatible /compat/linux/usr/lib/oracle/10.2.0.3/client/lib/libocci.so when searching for -locci /usr/bin/ld: cannot find -locci cc: error: linker command failed with exit code 1 (use -v to see invocation) file /compat/linux/usr/lib/oracle/10.2.0.3/client/lib/libocci.so libocci.so.10.1: ELF 32-bit LSB shared object, Intel 80386, version 1 (SYSV), dynamically linked, not stripped and I do not have a non-shared lib to link against. Any ideas ? Can a SYSV shared lib be transformed into some other shlib ? -- p...@opsec.eu+49 171 3101372 5 years to go !
Re: DBD::Oracle 1.19 in FreeBSD Ports (was Re: help with odd DBI perpare/execute errors)
According to Kurt Jaeger dbi-us...@opsec.eu on Thu, 06/04/15 at 02:50: DBD::Oracle no longer supports Oracle client versions before 9.2 Try a version before 1.25 for 9 and 1.18 for 8! at ./Makefile.PL line 271. and there is no oracle9-client port on FreeBSD. Thanks. That makes sense. What doesn't make sense and begs the following question is: Why isn't there an oracle9-client port for FreeBSD? Is this an Oracle issue? Is this a FreeBSD issue? Or what? Thanks! Regards, web... -- /\ ASCII RIBBON / William Bulley \ / CAMPAIGN AGAINST / XHTML E-MAIL AND / E-MAIL: w...@umich.edu / \ LISTSERV POSTINGS / 72 characters width template -|
Re: DBD::Oracle 1.19 in FreeBSD Ports (was Re: help with odd DBI perpare/execute errors)
Hi! According to Kurt Jaeger dbi-us...@opsec.eu on Thu, 06/04/15 at 02:50: DBD::Oracle no longer supports Oracle client versions before 9.2 Try a version before 1.25 for 9 and 1.18 for 8! at ./Makefile.PL line 271. and there is no oracle9-client port on FreeBSD. Thanks. That makes sense. What doesn't make sense and begs the following question is: Why isn't there an oracle9-client port for FreeBSD? Is this an Oracle issue? Is this a FreeBSD issue? Or what? Thanks! My guess: no-one had the need, time and skills to fix it. There's a newer linux-oracle-instantclient-sdk available in the ports (10.2.0.3), and maybe it helps to compile and link a newer DBD::Oracle. And there's an newer version (12.1.0.2.0) pending commit: https://bugs.freebsd.org/bugzilla/show_bug.cgi?id=157544 -- p...@opsec.eu+49 171 3101372 5 years to go !
DBD::Oracle 1.19 in FreeBSD Ports (was Re: help with odd DBI perpare/execute errors)
in message 20150603172207.gf2...@itcom245.staff.itd.umich.edu, wrote William Bulley thusly... ... My focus now is trying to install DBD::Oracle 1.74 from CPAN (from April of 2014) on my system. ... What is confusing to me is why FreeBSD _still_ contains DBD::Oracle 1.19 in their ports tree! After all these years! Since it's maintainership was passed to perl@freebsd ... r296145 | skv | 2012-05-07 05:42:51 -1000 (Mon, 07 May 2012) | 4 lines Pass maintainership of almost of my p5-* ports to perl@. (I hope updating of them will be improved). ... DBD::Oracle has not been updated. (The updates that are there are related to Ports infrastructure build/install.) Somebody needs to file a PR to update the port. (Tsk, tsk [0,1].) - parv [0] http://www.freebsd.org/doc/en_US.ISO8859-1/books/porters-handbook/ [1] https://bugs.freebsd.org/bugzilla/enter_bug.cgi?component=Individual%20Port%28s%29product=Ports%20%26%20Packages --
Re: help with odd DBI perpare/execute errors
According to Martin J. Evans boh...@ntlworld.com on Wed, 06/03/15 at 09:48: Sounds ok but the error is invalid string ORA-0911 You tried to execute a SQL statement that included a special character. http://www.techonthenet.com/oracle/errors/ora00911.php lists various causes. Yep, I've been all over the net looking for this issue. I am not doing anything wrong -- the invalid string is the darn ?!!! DBD::Oracle::db prepare failed: ORA-00911: invalid character (DBD ERROR: error possibly near * indicator at char 370 in ' select distinct s.ITEMIDNUM, c.STATUSDES, s.ADDRESS128BIT, s.PREFIX_LEN, s.ENDADDRESS128BIT, s.CREATED_USER, to_char(s.CREATED_DATE, '-MM-DD HH24:MI:SS'), i.ITEMDES, i.COMMENTS, s.RESERVEDFOR, s.AGGREGATE_STATUS, i.STATUSCD, i.ITEMNAME from UMNET_ONLINE.IP6NET s, UMNET_ONLINE.ITEM i, UMNET_ONLINE.STATUS_CODE c where i.PARENTITEMIDNUM = *? and i.ITEMIDNUM = s.ITEMIDNUM and i.STATUSCD = c.STATUSCD and i.ITEMCATCD like 'IPv6' union select distinct s.ITEMIDNUM, c.ITEMRELTYPDES, s.ADDRESS128BIT, s.PREFIX_LEN, s.ENDADDRESS128BIT, s.CREATED_USER, ...' You can see the error complaining about the question mark above. There is a second question mark in the second select statement. I'm not sure I'd trust that - doesn't that mean you are expecting stdin and stout to be in order. Yes, and they are. If you can easily do it I would stick an eval around it and trap it that way. Also, if you trap it you can print the SQL using http://search.cpan.org/~timb/DBI-1.633/DBI.pm#Statement and the parameters using http://search.cpan.org/~timb/DBI-1.633/DBI.pm#ParamValues I'll have to look into that. But recall this: I am not getting to the execute() statement. The above error is on the prepare() statment. This is so very confusing to me... I would not bother changing from ? to named - I seriously doubt this is the issue. And you can see from the above that the question mark is back in the mix. http://search.cpan.org/~pythian/DBD-Oracle-1.74/lib/DBD/Oracle.pm#ora_verbose Can be set in the connect attributes. Thanks. :-) If I were you I'd try and simply the original case down as much as possible but getting a trace with ora_verbose might help identify the problem. Okay, I'll try that next. Regards, web... -- /\ ASCII RIBBON / William Bulley \ / CAMPAIGN AGAINST / XHTML E-MAIL AND / E-MAIL: w...@umich.edu / \ LISTSERV POSTINGS / 72 characters width template -|
RE: help with odd DBI perpare/execute errors
Can you post a copy of your prepare statement? -Original Message- From: William Bulley [mailto:w...@umich.edu] Sent: Wednesday, June 03, 2015 7:57 AM To: Martin J. Evans Cc: dbi-users@perl.org Subject: Re: help with odd DBI perpare/execute errors According to Martin J. Evans boh...@ntlworld.com on Wed, 06/03/15 at 09:48: Sounds ok but the error is invalid string ORA-0911 You tried to execute a SQL statement that included a special character. http://www.techonthenet.com/oracle/errors/ora00911.php lists various causes. Yep, I've been all over the net looking for this issue. I am not doing anything wrong -- the invalid string is the darn ?!!! DBD::Oracle::db prepare failed: ORA-00911: invalid character (DBD ERROR: error possibly near * indicator at char 370 in ' select distinct s.ITEMIDNUM, c.STATUSDES, s.ADDRESS128BIT, s.PREFIX_LEN, s.ENDADDRESS128BIT, s.CREATED_USER, to_char(s.CREATED_DATE, '-MM-DD HH24:MI:SS'), i.ITEMDES, i.COMMENTS, s.RESERVEDFOR, s.AGGREGATE_STATUS, i.STATUSCD, i.ITEMNAME from UMNET_ONLINE.IP6NET s, UMNET_ONLINE.ITEM i, UMNET_ONLINE.STATUS_CODE c where i.PARENTITEMIDNUM = *? and i.ITEMIDNUM = s.ITEMIDNUM and i.STATUSCD = c.STATUSCD and i.ITEMCATCD like 'IPv6' union select distinct s.ITEMIDNUM, c.ITEMRELTYPDES, s.ADDRESS128BIT, s.PREFIX_LEN, s.ENDADDRESS128BIT, s.CREATED_USER, ...' You can see the error complaining about the question mark above. There is a second question mark in the second select statement. I'm not sure I'd trust that - doesn't that mean you are expecting stdin and stout to be in order. Yes, and they are. If you can easily do it I would stick an eval around it and trap it that way. Also, if you trap it you can print the SQL using http://search.cpan.org/~timb/DBI-1.633/DBI.pm#Statement and the parameters using http://search.cpan.org/~timb/DBI-1.633/DBI.pm#ParamValues I'll have to look into that. But recall this: I am not getting to the execute() statement. The above error is on the prepare() statment. This is so very confusing to me... I would not bother changing from ? to named - I seriously doubt this is the issue. And you can see from the above that the question mark is back in the mix. http://search.cpan.org/~pythian/DBD-Oracle-1.74/lib/DBD/Oracle.pm#ora_verbose Can be set in the connect attributes. Thanks. :-) If I were you I'd try and simply the original case down as much as possible but getting a trace with ora_verbose might help identify the problem. Okay, I'll try that next. Regards, web... -- /\ ASCII RIBBON / William Bulley \ / CAMPAIGN AGAINST / XHTML E-MAIL AND / E-MAIL: w...@umich.edu / \ LISTSERV POSTINGS / 72 characters width template -|
Re: help with odd DBI perpare/execute errors
On Jun 3, 2015, at 6:57 AM, William Bulley w...@umich.edu wrote: Yep, I've been all over the net looking for this issue. I am not doing anything wrong -- the invalid string is the darn ?!!! Make sure your original $query is delimited by double quotes, not single. if you do $sth-prepare(‘select column from table where column = ?’); you’ll get that error. That’s the only way the ? would get past DBI, I’d think, which is what your oracle error seems to be indicating. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: help with odd DBI perpare/execute errors
According to Bruce Johnson john...@pharmacy.arizona.edu on Wed, 06/03/15 at
10:10:
Make sure your original $query is delimited by double quotes, not single.
I've tried _everything_!!
Single quotes. Double quotes. q{} and qq{} (using the latter now).
But no matter what I try DBI complains about the darn question mark!
It is infuriating, I tell you! :-)
if you do $sth-prepare('select column from table where column = ?');
you'll get that error.
That's the only way the ? would get past DBI, I'd think, which is
what your oracle error seems to be indicating.
It is still not repsonding to any attempts to remedy the problem, sigh... :-(
Regards,
web...
--
/\ ASCII RIBBON / William Bulley
\ / CAMPAIGN AGAINST /
XHTML E-MAIL AND / E-MAIL: w...@umich.edu
/ \ LISTSERV POSTINGS /
72 characters width template -|
Re: help with odd DBI perpare/execute errors
On 03/06/15 14:06, William Bulley wrote:
Environment Perl script trying to query Oracle 11g database:
FreeBSD 9.3-STABLE
DBI 1.633
oracle8-client 0.2.0
DBD::Oracle 1.19
I have no trouble connecting with the Oracle database. And I do
recover data when I use the temporary workaround described below.
I have a query/prepare setup outside a foreach loop where I execute()
the prepared query something like this, only more complex:
my $query = select column from table where column = ?;
my $sth = $dbh-prepare ($query);
foreach ()
{
$sth-execute($value);
}
I was getting invalid string ORA-0911 errors at the question mark.
I then replaced the question mark with a number (555) and made the
execute() call just $sth-execute();
So, when this fails, what is the value of $value. Assuming you have RaiseError
set, you can just put an eval around the execute and if $@ is set, print out
$value.
This worked. But I really needed to bind to the $value variable
in the foreach loop.
In reading the DBI POD it said for Oracle the ? is turned into
:p1 (in this case). So I replaced the question mark with :p1.
Strictly speaking you do not need to do this - ? is fine. DBD::Oracle also
supports named parameters e.g., ':myparam' which only has an advantage if you
want to use :myparam more than once in the SQL.
The prepare statement no longer generated an error, instead the
execute statement generated the error:
DBD::Oracle::st execute failed: called with 1 bind variables
when 0 are needed [for Statement ... ] at script.pl line xxx.
Either because you omitted the value from execute but more likely because you
need to associate $value with the NAMED parameter i.e., call bind_param.
Can any one help me figure out this confusing situation? BTW, I
have been using Perl for twenty years and DBI for perhaps ten,
and I have used this query/prepare/bind/execute methodology in
the past with success. Something is different, but I don't know
what to look for.
Regards,
web...
Tell us the column type of 'column' and the value of $value when the original
code fails. If this does not enlighten you, reduce this to a small reproducible
script and re-run with ora_verbose set to 7. Paste the output somewhere we can
view it.
Martin
help with odd DBI perpare/execute errors
Environment Perl script trying to query Oracle 11g database:
FreeBSD 9.3-STABLE
DBI 1.633
oracle8-client 0.2.0
DBD::Oracle 1.19
I have no trouble connecting with the Oracle database. And I do
recover data when I use the temporary workaround described below.
I have a query/prepare setup outside a foreach loop where I execute()
the prepared query something like this, only more complex:
my $query = select column from table where column = ?;
my $sth = $dbh-prepare ($query);
foreach ()
{
$sth-execute($value);
}
I was getting invalid string ORA-0911 errors at the question mark.
I then replaced the question mark with a number (555) and made the
execute() call just $sth-execute();
This worked. But I really needed to bind to the $value variable
in the foreach loop.
In reading the DBI POD it said for Oracle the ? is turned into
:p1 (in this case). So I replaced the question mark with :p1.
The prepare statement no longer generated an error, instead the
execute statement generated the error:
DBD::Oracle::st execute failed: called with 1 bind variables
when 0 are needed [for Statement ... ] at script.pl line xxx.
Can any one help me figure out this confusing situation? BTW, I
have been using Perl for twenty years and DBI for perhaps ten,
and I have used this query/prepare/bind/execute methodology in
the past with success. Something is different, but I don't know
what to look for.
Regards,
web...
--
/\ ASCII RIBBON / William Bulley
\ / CAMPAIGN AGAINST /
XHTML E-MAIL AND / E-MAIL: w...@umich.edu
/ \ LISTSERV POSTINGS /
72 characters width template -|
Re: help with odd DBI perpare/execute errors
According to Martin J. Evans boh...@ntlworld.com on Wed, 06/03/15 at 09:15: So, when this fails, what is the value of $value. I just ran it again. The value is 547. Assuming you have RaiseError set, you can just put an eval around the execute and if $@ is set, print out $value. I don't. I have a print statement in front of the execute to show my what I am passing to the execute() method. Strictly speaking you do not need to do this - ? is fine. DBD::Oracle also supports named parameters e.g., ':myparam' which only has an advantage if you want to use :myparam more than once in the SQL. The query is quite complex -- two SELECT statements connected by a UNION statement -- and the column = ? syntax is used twice. I changed the ? to :myparam in both places and I still get the error: DBD::Oracle::st execute failed: called with 1 bind variables when 0 are needed [for Statement... Either because you omitted the value from execute In this case, it was not omitted. but more likely because you need to associate $value with the NAMED parameter i.e., call bind_param. That will be my next test, but I don't hold out much hope for that working either (I've never had to do this in the past). Tell us the column type of 'column' and the value of $value when the original code fails. If this does not enlighten you, reduce this to a small reproducible script and re-run with ora_verbose set to 7. Paste the output somewhere we can view it. The Oracle type for the column in question is NUMBER. I assumed that any integer value would be compatible. The value is 547 for the run that just failed. Never heard of ora_verbose -- where is this to be set? Just in my code somewhere, or on the DBI-connect() method, or where? Regards, web... -- /\ ASCII RIBBON / William Bulley \ / CAMPAIGN AGAINST / XHTML E-MAIL AND / E-MAIL: w...@umich.edu / \ LISTSERV POSTINGS / 72 characters width template -|
Re: help with odd DBI perpare/execute errors
On 03/06/15 14:38, William Bulley wrote: According to Martin J. Evans boh...@ntlworld.com on Wed, 06/03/15 at 09:15: So, when this fails, what is the value of $value. I just ran it again. The value is 547. Sounds ok but the error is invalid string ORA-0911 You tried to execute a SQL statement that included a special character. http://www.techonthenet.com/oracle/errors/ora00911.php lists various causes. Assuming you have RaiseError set, you can just put an eval around the execute and if $@ is set, print out $value. I don't. I have a print statement in front of the execute to show my what I am passing to the execute() method. I'm not sure I'd trust that - doesn't that mean you are expecting stdin and stout to be in order. If you can easily do it I would stick an eval around it and trap it that way. Also, if you trap it you can print the SQL using http://search.cpan.org/~timb/DBI-1.633/DBI.pm#Statement and the parameters using http://search.cpan.org/~timb/DBI-1.633/DBI.pm#ParamValues Strictly speaking you do not need to do this - ? is fine. DBD::Oracle also supports named parameters e.g., ':myparam' which only has an advantage if you want to use :myparam more than once in the SQL. The query is quite complex -- two SELECT statements connected by a UNION statement -- and the column = ? syntax is used twice. I changed the ? to :myparam in both places and I still get the error: DBD::Oracle::st execute failed: called with 1 bind variables when 0 are needed [for Statement... Either because you omitted the value from execute In this case, it was not omitted. I would not bother changing from ? to named - I seriously doubt this is the issue. but more likely because you need to associate $value with the NAMED parameter i.e., call bind_param. That will be my next test, but I don't hold out much hope for that working either (I've never had to do this in the past). Tell us the column type of 'column' and the value of $value when the original code fails. If this does not enlighten you, reduce this to a small reproducible script and re-run with ora_verbose set to 7. Paste the output somewhere we can view it. The Oracle type for the column in question is NUMBER. I assumed that any integer value would be compatible. The value is 547 for the run that just failed. Never heard of ora_verbose -- where is this to be set? Just in my code somewhere, or on the DBI-connect() method, or where? http://search.cpan.org/~pythian/DBD-Oracle-1.74/lib/DBD/Oracle.pm#ora_verbose Can be set in the connect attributes. Regards, web... If I were you I'd try and simply the original case down as much as possible but getting a trace with ora_verbose might help identify the problem. Martin
Re: help with odd DBI perpare/execute errors
According to Howard, Chris howa...@prpa.org on Wed, 06/03/15 at 10:44: Can you post a copy of your prepare statement? Sure. Here it is: $sth = $dbh-prepare ($query) or die Couldn't prepare statement: . $dbh-errstr; Regards, web... -- /\ ASCII RIBBON / William Bulley \ / CAMPAIGN AGAINST / XHTML E-MAIL AND / E-MAIL: w...@umich.edu / \ LISTSERV POSTINGS / 72 characters width template -|
Re: help with odd DBI perpare/execute errors
On Jun 3, 2015, at 7:19 AM, William Bulley w...@umich.edu wrote:
According to Bruce Johnson john...@pharmacy.arizona.edu on Wed, 06/03/15
at 10:10:
Make sure your original $query is delimited by double quotes, not single.
I've tried _everything_!!
Single quotes. Double quotes. q{} and qq{} (using the latter now).
But no matter what I try DBI complains about the darn question mark!
It is infuriating, I tell you! :-)
Well, I just tested MY theory (RHEL v6.5, oracle 11.2g, oracle instant client
for 11.2, perl, v5.10.1 (*) built for x86_64-linux-thread-multi) with:
#!/usr/bin/perl
use strict;
use DBI;
my $dbh= DBI-connect(“dbi:Oracle:host=$host, $user, $pass, {RaiseError =1});
my $qry1 ='select ? from dual';
my $qry2 = select ? from dual;
my $sth = $dbh-prepare($qry1);
$sth-execute('foo');
my ($res)=$sth-fetchrow();
print single quote result is $res \n;
$sth =$dbh-prepare($qry2);
$sth-execute('bar');
($res)=$sth-fetchrow();
print double quote result is $res \n;
exit;
And got:
# ./qmarktest.pl
single quote result is foo
double quote result is bar
So yet another fine theory destroyed by reality…
Possibly some sort of character set mess up? Could your 'question mark' be
something else in the script? Maybe a 16-bit vs 8-bit character? I’ve had some
weird issues in the past when I was handed a 16-bit unicode text file of insert
statements and tried to run them.
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
Re: help with odd DBI perpare/execute errors
On 03/06/15 17:12, Bruce Johnson wrote:
On Jun 3, 2015, at 7:19 AM, William Bulley w...@umich.edu wrote:
According to Bruce Johnson john...@pharmacy.arizona.edu on Wed, 06/03/15 at
10:10:
Make sure your original $query is delimited by double quotes, not single.
I've tried _everything_!!
Single quotes. Double quotes. q{} and qq{} (using the latter now).
But no matter what I try DBI complains about the darn question mark!
It is infuriating, I tell you! :-)
Well, I just tested MY theory (RHEL v6.5, oracle 11.2g, oracle instant client
for 11.2, perl, v5.10.1 (*) built for x86_64-linux-thread-multi) with:
#!/usr/bin/perl
use strict;
use DBI;
my $dbh= DBI-connect(“dbi:Oracle:host=$host, $user, $pass, {RaiseError =1});
my $qry1 ='select ? from dual';
my $qry2 = select ? from dual;
my $sth = $dbh-prepare($qry1);
$sth-execute('foo');
my ($res)=$sth-fetchrow();
print single quote result is $res \n;
$sth =$dbh-prepare($qry2);
$sth-execute('bar');
($res)=$sth-fetchrow();
print double quote result is $res \n;
exit;
And got:
# ./qmarktest.pl
single quote result is foo
double quote result is bar
So yet another fine theory destroyed by reality…
Possibly some sort of character set mess up? Could your 'question mark' be
something else in the script? Maybe a 16-bit vs 8-bit character? I’ve had some
weird issues in the past when I was handed a 16-bit unicode text file of insert
statements and tried to run them.
Bruce,
Just so you know, I've already told William off list that question mark should
not get through to Oracle and so the preparse method in DBD::Oracle is not
spotting the ? - perhaps because it is not a ? or it thinks it is in a comment,
literal etc. However, I expect, as you do, that one of the chrs in the SQL is
not what it seems.
Sorry, as log file was sent to me I answered it and did not cc the list.
Martin
Re: help with odd DBI perpare/execute errors
On Jun 3, 2015, at 9:44 AM, William Bulley w...@umich.edu wrote:
Martin thinks the parsing in the dbd_preparse() function within the
dbdimp.c file (part of DBD::Oracle) has issues so that it cannot deal
with the second question mark given the preceding single quote(s).
It seems plausible, yet odd, to me, but it isn't my module. Perhaps
I have an older version? I dunno... :-(
I have numerous constructions like yours in my scripts and they work just fine
(and have for a very long time, I’m pretty sure prior to 2006 which is when
1.19 was released, so I don’t believe it’s your DBD::Oracle version )
Double check those single quotes, though, I’ve seen editors that try to sneak
in ‘smart quotes’ and those’ll bollix you every time.
Here’s a quicky test:
#!/usr/bin/perl
use strict;
foreach my $i (qw (? ' “ ” ‘ ’ )){
print $i is ascii .(ord $i).\n
}
exit;
This is what it produces:
dbdev2:~ johnson$ perl test
? is ascii 63
' is ascii 39
is ascii 34
? is ascii 210
? is ascii 211
? is ascii 212
? is ascii 213
Note the displayed ‘?’’s….this is in my standard OSX terminal, which is a
VT-100 emulator using UTF-8 as the text encoding.
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
RE: help with odd DBI perpare/execute errors
cat scriptname | od -bc | more
-Original Message-
From: Bruce Johnson [mailto:john...@pharmacy.arizona.edu]
Sent: Wednesday, June 03, 2015 11:13 AM
To: William Bulley
Cc: dbi-users@perl.org
Subject: Re: help with odd DBI perpare/execute errors
On Jun 3, 2015, at 9:44 AM, William Bulley w...@umich.edu wrote:
Martin thinks the parsing in the dbd_preparse() function within the
dbdimp.c file (part of DBD::Oracle) has issues so that it cannot deal
with the second question mark given the preceding single quote(s).
It seems plausible, yet odd, to me, but it isn't my module. Perhaps
I have an older version? I dunno... :-(
I have numerous constructions like yours in my scripts and they work just fine
(and have for a very long time, I’m pretty sure prior to 2006 which is when
1.19 was released, so I don’t believe it’s your DBD::Oracle version )
Double check those single quotes, though, I’ve seen editors that try to sneak
in ‘smart quotes’ and those’ll bollix you every time.
Here’s a quicky test:
#!/usr/bin/perl
use strict;
foreach my $i (qw (? ' “ ” ‘ ’ )){
print $i is ascii .(ord $i).\n
}
exit;
This is what it produces:
dbdev2:~ johnson$ perl test
? is ascii 63
' is ascii 39
is ascii 34
? is ascii 210
? is ascii 211
? is ascii 212
? is ascii 213
Note the displayed ‘?’’s….this is in my standard OSX terminal, which is a
VT-100 emulator using UTF-8 as the text encoding.
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
Re: help with odd DBI perpare/execute errors
On 03/06/15 14:06, William Bulley wrote: Environment Perl script trying to query Oracle 11g database: FreeBSD 9.3-STABLE DBI 1.633 oracle8-client 0.2.0 DBD::Oracle 1.19 It seems I missed this ^ A 9 year old DBD::Oracle. I can well believe the preparse code has changed or been fixed in all that time. I've recommended William tries a newer version as I suspect the preparse code it failing since those ? should not get to Oracle. Martin
DBD::ODBC fails after Execute immediate success with info
Hello there,
I've been using DBD::ODBC to connect to Vertica 6.1 for some time now,
yet updating (quite old 1.23) DBD::ODBC to a recent one (1.45) introduced
an issue. Frankly, I'm not sure if it's really a bug or not.
My environment:
Arch: x86_64
OS: Scientific Linux 6.4
Perl: 5.10.1
DBI: 1.630 (updated from 1.609)
DBD::ODBC 1.45 (updated from 1.23)
unixODBC: 2.3.2
Simple script reproducing the issue is this:
--cut--
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
my $dbh = DBI-connect(
'dbi:ODBC:driver=vertica;database=verticadb;server=localhost',
'vertica',
undef,
{
PrintError = 0,
RaiseError = 1,
AutoCommit = 1,
} );
$dbh-trace(5);
$dbh-begin_work;
$dbh-do(drop table if exists foo);
$dbh-commit;
--cut--
It fails and produces the following trace:
--cut--
DBI::db=HASH(0x270e1d0) trace level set to 0x0/5 (DBI @ 0x0/0) in
DBI 1.630-ithread (pid 27687)
- begin_work in DBD::_::db for DBD::ODBC::db
(DBI::db=HASH(0x270e2a8)~0x270e1d0) thr#249b010
1 - FETCH for DBD::ODBC::db (DBI::db=HASH(0x270e1d0)~INNER
'AutoCommit') thr#249b010
1 - FETCH= 1 at /usr/local/lib64/perl5/DBI.pm line 1732 via at
./odbctest.pl line 15
1 - STORE for DBD::ODBC::db (DBI::db=HASH(0x270e1d0)~INNER
'AutoCommit' 0) thr#249b010
setting AutoCommit
1 - STORE= 1 at /usr/local/lib64/perl5/DBI.pm line 1734 via at
./odbctest.pl line 15
1 - STORE for DBD::ODBC::db (DBI::db=HASH(0x270e1d0)~INNER
'BegunWork' 1) thr#249b010
!!DBD::ODBC unsupported attribute passed (BegunWork)
STORE DBI::db=HASH(0x270e1d0) 'BegunWork' = 1
1 - STORE= 1 at /usr/local/lib64/perl5/DBI.pm line 1735 via at
./odbctest.pl line 15
- begin_work= 1 at ./odbctest.pl line 15
- do for DBD::ODBC::db (DBI::db=HASH(0x270e2a8)~0x270e1d0 'drop
table if exists foo') thr#249b010
1 - STORE for DBD::ODBC::db (DBI::db=HASH(0x270e1d0)~INNER
'Statement' 'drop table if exists foo') thr#249b010
!!DBD::ODBC unsupported attribute passed (Statement)
STORE DBI::db=HASH(0x270e1d0) 'Statement' = 'drop table if exists foo'
1 - STORE= 1 at /usr/local/lib64/perl5/DBD/ODBC.pm line 433 via at
./odbctest.pl line 16
SQLExecDirect drop table if exists foo
SQLExecDirect = 1
!!dbd_error2(err_rc=1, what=Execute immediate success with info,
handles=(2727670,2727c60,28f6480)
** No error found 1 **
!! ERROR: 1 'Unable to fetch information about the error' (err#1)
- do= -1 at ./odbctest.pl line 16
DBD::ODBC::db do failed: Unable to fetch information about the
error at ./odbctest.pl line 16.
- DESTROY for DBD::ODBC::db (DBI::db=HASH(0x270e1d0)~INNER) thr#249b010
Issuing rollback() due to DESTROY without explicit disconnect() of
DBD::ODBC::db handle
driver=vertica;database=verticadb;server=localhost at ./odbctest.pl
line 16.
ERROR: 1 'Unable to fetch information about the error' (err#1)
- DESTROY= undef at ./odbctest.pl line 16 via at ./odbctest.pl line 16
--cut--
If I comment out begin_work and commit, there is no error:
--cut--
DBI::db=HASH(0x24ef420) trace level set to 0x0/5 (DBI @ 0x0/0) in
DBI 1.630-ithread (pid 29782)
- do for DBD::ODBC::db (DBI::db=HASH(0x24ef4f8)~0x24ef420 'drop
table if exists foo') thr#24bd010
1 - STORE for DBD::ODBC::db (DBI::db=HASH(0x24ef420)~INNER
'Statement' 'drop table if exists foo') thr#24bd010
!!DBD::ODBC unsupported attribute passed (Statement)
STORE DBI::db=HASH(0x24ef420) 'Statement' = 'drop table if exists foo'
1 - STORE= 1 at /usr/local/lib64/perl5/DBD/ODBC.pm line 433 via at
./odbctest.pl line 16
SQLExecDirect drop table if exists foo
SQLExecDirect = 1
!!dbd_error2(err_rc=1, what=Execute immediate success with info,
handles=(25e89d0,27492f0,2917f70)
!SQLError(25e89d0,27492f0,2917f70) = (01000, 4185, NOTICE 4185:
Nothing was dropped
)
!! info: '' 'NOTICE 4185: Nothing was dropped
(SQL-01000)' (err#0)
- do= -1 at ./odbctest.pl line 16
- DESTROY for DBD::ODBC::db (DBI::db=HASH(0x24ef420)~INNER) thr#24bd010
info: '' 'NOTICE 4185: Nothing was dropped
(SQL-01000)' (err#0)
- DESTROY= undef
--cut--
Both worked with DBD::ODBC 1.23. Through binary searching DBD::ODBC releases,
I was able to figure out that release 1.31 introduced this behavior,
namely this change
https://metacpan.org/pod/release/MJEVANS/DBD-ODBC-1.45/Changes#pod1.30_6-2011-06-04
So my question is: is it me, ODBC manager, DBD::ODBC or Vertica doing
something wrong?
Any insight is more than welcome!
Thanks
Jan
Re: DBD::ODBC fails after Execute immediate success with info
On 06/11/13 10:57, Jan Holčapek wrote: Hello there,
I've been using DBD::ODBC to connect to Vertica 6.1 for some time now,
yet updating (quite old 1.23) DBD::ODBC to a recent one (1.45) introduced
an issue. Frankly, I'm not sure if it's really a bug or not.
My environment:
Arch: x86_64
OS: Scientific Linux 6.4
Perl: 5.10.1
DBI: 1.630 (updated from 1.609)
DBD::ODBC 1.45 (updated from 1.23)
unixODBC: 2.3.2
Simple script reproducing the issue is this:
--cut--
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
my $dbh = DBI-connect(
'dbi:ODBC:driver=vertica;database=verticadb;server=localhost',
'vertica',
undef,
{
PrintError = 0,
RaiseError = 1,
AutoCommit = 1,
} );
$dbh-trace(5);
$dbh-begin_work;
$dbh-do(drop table if exists foo);
$dbh-commit;
--cut--
It fails and produces the following trace:
--cut--
DBI::db=HASH(0x270e1d0) trace level set to 0x0/5 (DBI @ 0x0/0) in
DBI 1.630-ithread (pid 27687)
- begin_work in DBD::_::db for DBD::ODBC::db
(DBI::db=HASH(0x270e2a8)~0x270e1d0) thr#249b010
1 - FETCH for DBD::ODBC::db (DBI::db=HASH(0x270e1d0)~INNER
'AutoCommit') thr#249b010
1 - FETCH= 1 at /usr/local/lib64/perl5/DBI.pm line 1732 via at
./odbctest.pl line 15
1 - STORE for DBD::ODBC::db (DBI::db=HASH(0x270e1d0)~INNER
'AutoCommit' 0) thr#249b010
setting AutoCommit
1 - STORE= 1 at /usr/local/lib64/perl5/DBI.pm line 1734 via at
./odbctest.pl line 15
1 - STORE for DBD::ODBC::db (DBI::db=HASH(0x270e1d0)~INNER
'BegunWork' 1) thr#249b010
!!DBD::ODBC unsupported attribute passed (BegunWork)
STORE DBI::db=HASH(0x270e1d0) 'BegunWork' = 1
1 - STORE= 1 at /usr/local/lib64/perl5/DBI.pm line 1735 via at
./odbctest.pl line 15
- begin_work= 1 at ./odbctest.pl line 15
- do for DBD::ODBC::db (DBI::db=HASH(0x270e2a8)~0x270e1d0 'drop
table if exists foo') thr#249b010
1 - STORE for DBD::ODBC::db (DBI::db=HASH(0x270e1d0)~INNER
'Statement' 'drop table if exists foo') thr#249b010
!!DBD::ODBC unsupported attribute passed (Statement)
STORE DBI::db=HASH(0x270e1d0) 'Statement' = 'drop table if exists foo'
1 - STORE= 1 at /usr/local/lib64/perl5/DBD/ODBC.pm line 433 via at
./odbctest.pl line 16
SQLExecDirect drop table if exists foo
SQLExecDirect = 1
This means the ODBC API SQLExecDirect was called and it returned 1
(SQL_SUCCESS_WITH_INFO). According to the ODBC spec, when a driver returns
SQL_SUCCESS_WITH_INFO there should be a diagnostic available to tell us what
the informational state is.
!!dbd_error2(err_rc=1, what=Execute immediate success with info,
handles=(2727670,2727c60,28f6480)
** No error found 1 **
but we called the ODBC API SQLError and DBD::ODBC thinks there was none.
!! ERROR: 1 'Unable to fetch information about the error' (err#1)
- do= -1 at ./odbctest.pl line 16
so it is telling you, SQLExecute returned SQL_SUCCESS_WITH_INFO and then there
was no error - which is an error.
DBD::ODBC::db do failed: Unable to fetch information about the
error at ./odbctest.pl line 16.
- DESTROY for DBD::ODBC::db (DBI::db=HASH(0x270e1d0)~INNER) thr#249b010
Issuing rollback() due to DESTROY without explicit disconnect() of
DBD::ODBC::db handle
driver=vertica;database=verticadb;server=localhost at ./odbctest.pl
line 16.
ERROR: 1 'Unable to fetch information about the error' (err#1)
- DESTROY= undef at ./odbctest.pl line 16 via at ./odbctest.pl line 16
--cut--
If I comment out begin_work and commit, there is no error:
--cut--
DBI::db=HASH(0x24ef420) trace level set to 0x0/5 (DBI @ 0x0/0) in
DBI 1.630-ithread (pid 29782)
- do for DBD::ODBC::db (DBI::db=HASH(0x24ef4f8)~0x24ef420 'drop
table if exists foo') thr#24bd010
1 - STORE for DBD::ODBC::db (DBI::db=HASH(0x24ef420)~INNER
'Statement' 'drop table if exists foo') thr#24bd010
!!DBD::ODBC unsupported attribute passed (Statement)
STORE DBI::db=HASH(0x24ef420) 'Statement' = 'drop table if exists foo'
1 - STORE= 1 at /usr/local/lib64/perl5/DBD/ODBC.pm line 433 via at
./odbctest.pl line 16
SQLExecDirect drop table if exists foo
SQLExecDirect = 1
!!dbd_error2(err_rc=1, what=Execute immediate success with info,
handles=(25e89d0,27492f0,2917f70)
!SQLError(25e89d0,27492f0,2917f70) = (01000, 4185, NOTICE 4185:
Nothing was dropped
)
!! info: '' 'NOTICE 4185: Nothing was dropped
This is the difference between an ODBC API saying it succeeded but with an
issue you should be made aware of (SQL_SUCCESS_WITH_INFO) and I'll tell you
what it is and it succeeded but with an issue and I refuse to tell you what it
is (a bug either in the driver or DBD::ODBC).
In the first case the driver said it succeeded but then against the ODBC API,
would not tell us the informational part. DBD::ODBC posted an error saying this.
In the second case the same happened but the driver told us it succeeded but it
did not really drop
Re: DBD::ODBC fails after Execute immediate success with info
On 06/11/13 12:36, Jan Holčapek wrote:
Hi Martin,
3. now run your basic script and send me /tmp/unixodbc.log
attached is the log file you've requested. Please let me know your
findings, thanks!
--Jan
Hi Jan,
Your log shows:
[ODBC][7270][1383740710.126962][SQLExecDirect.c][240]
Entry:
Statement = 0x2266860
SQL = [drop table if exists foo][length = 24 (SQL_NTS)]
[ODBC][7270][1383740710.130936][SQLExecDirect.c][503]
Exit:[SQL_SUCCESS_WITH_INFO]
Here SQL_SUCCESS_WITH_INFO was returned. ODBC specifies that this state should
only be returned if the driver succeeded in performing your action but perhaps
with some notable event like, I did what you asked but I had to change it
slightly, or in this case, I could not drop the table foo because it does not
exist but the net effect is the same - foo does not exist.
[ODBC][7270][1383740710.131029][SQLError.c][352]
Entry:
Statement = 0x2266860
SQLState = 0x7fff72663d80
Native = 0x7fff72663978
Message Text = 0x7fff72663980
Buffer Length = 1023
Text Len Ptr = 0x7fff7266397e
[ODBC][7270][1383740710.131053][SQLError.c][389]
Exit:[SQL_NO_DATA]
We tried to find out the additional information on the statement handle but it
returned nothing.
[ODBC][7270][1383740710.131079][SQLError.c][434]
Entry:
Connection = 0x2097c60
SQLState = 0x7fff72663d80
Native = 0x7fff72663978
Message Text = 0x7fff72663980
Buffer Length = 1023
Text Len Ptr = 0x7fff7266397e
[ODBC][7270][1383740710.131094][SQLError.c][471]
Exit:[SQL_NO_DATA]
We tried again on the connection handle - same result.
[ODBC][7270][1383740710.131122][SQLError.c][514]
Entry:
Environment = 0x2097670
SQLState = 0x7fff72663d80
Native = 0x7fff72663978
Message Text = 0x7fff72663980
Buffer Length = 1023
Text Len Ptr = 0x7fff7266397e
[ODBC][7270][1383740710.131140][SQLError.c][551]
Exit:[SQL_NO_DATA]
we tried again on the environment handle - same result.
As you can see DBD::ODBC tried very hard to find out what the informational msg
was.
At this point DBD::ODBC considered this an error and issued the error that
SQLExecDirect returned SQL_SUCCESS_WITH_INFO but no informational message could
be found.
I believe this is a bug in your ODBC driver.
However, I took the decision in DBD::ODBC to report this an error and some
might argue differently. In this case it is fairly innocuous as you tried to
drop a table which did not exist and afterwards it does not exist - although
what happens if it was an error in your application and the table should have
existed? In other cases it is more clear cut - what if you tried to set a
statement attribute like a cursor to type A and the driver said, I've set a
cursor, but not to type A, I've set it to type B - an app would likely want to
know this.
I can see it might be inconvenient for you the way it is now as your forced to
do something like:
$h-{RaiseError} = 1;
eval {
$h-do(q/drop table foo/);
};
if ($@ $h-errstr =~ /Unable to fetch information about the error/) {
# assume the drop was ok and the table did not exist
# but that is not really a safe assumption to make
} elsif ($@) {
# it is a real error
}
What should have happened is that the driver returned SQL_SUCCESS_WITH_INFO and an
informational msg was retrieved. execute/do would return success. DBD::ODBC would
have posted an informational msg and if you'd examined $h-err it would contain ''
(the empty string) to indicate an informational msg was available in $h-errstr.
I could be swayed to change this to a warning (which would be less inconvenient
to you since you could disable warnings when you are dropping a table) but I'd
need to be persuaded. This is one of those occasions when I'm damned either
way. If I don't report it as an error I end up debugging peoples ODBC logs only
to tell them their driver is broken and if I do report an error people come
back to me saying why is this an error.
However, to reiterate, I think your driver is broken in the following ways:
1. although it is perfectly reasonable for a driver to behave differently in a
transaction to out of one, yours is inconsistent. In a txn, it reports
SQL_SUCCESS_WITH_INFO and no informational msg, and out it reports
SQL_SUCCESS_WITH_INFO and gives an informational message that your table does
not exist.
2. I'd expect:
drop table foo if not exists;
to be successful, even if foo does not exist - it isn't
Re: DBD::ODBC fails after Execute immediate success with info
Hi Martin,
On Wed, Nov 6, 2013 at 3:05 PM, Martin J. Evans boh...@ntlworld.com wrote:
I believe this is a bug in your ODBC driver.
I kind of expected that. I'll file a bugreport to Vertica Support.
However, I took the decision in DBD::ODBC to report this an error and some
might argue differently. In this case it is fairly innocuous as you tried to
drop a table which did not exist and afterwards it does not exist - although
what happens if it was an error in your application and the table should
have existed? In other cases it is more clear cut - what if you tried to set
a statement attribute like a cursor to type A and the driver said, I've set
a cursor, but not to type A, I've set it to type B - an app would likely
want to know this.
IMO current behavior is correct.
I can see it might be inconvenient for you the way it is now as your forced
to do something like:
$h-{RaiseError} = 1;
eval {
$h-do(q/drop table foo/);
};
if ($@ $h-errstr =~ /Unable to fetch information about the error/) {
# assume the drop was ok and the table did not exist
# but that is not really a safe assumption to make
} elsif ($@) {
# it is a real error
}
I've already adjusted the code with this, I also narrowed the
if-condition with $dbh-state eq 'IM008',
as I'm getting this state along with the error. Not that it's neat,
but allows me to work
around this driver issue for the moment.
BTW I found out IM008 is returned by DBD::ODBC, then checked
http://www.easysoft.com/developer/interfaces/odbc/sqlstate_status_return_codes.html#IM008
and must admit I can't see a relationship between the actual error and
the state.
I could be swayed to change this to a warning (which would be less
inconvenient to you since you could disable warnings when you are dropping a
table) but I'd need to be persuaded. This is one of those occasions when I'm
damned either way. If I don't report it as an error I end up debugging
peoples ODBC logs only to tell them their driver is broken and if I do
report an error people come back to me saying why is this an error.
Agree.
BTW, are you sure your db can actually rollback a drop table, and if it
cannot, then why bother doing it in a txn?
This is due to the code being used to handle data both in PostgreSQL database
(which supports transactional DDL) and Vertica (which does not support that).
We are likely about to consider changing the application logic.
Thank very much for the explanation!
Cheers
Jan
Re: DBD::ODBC fails after Execute immediate success with info
On 06/11/13 14:36, Jan Holčapek wrote:
Hi Martin,
On Wed, Nov 6, 2013 at 3:05 PM, Martin J. Evans boh...@ntlworld.com wrote:
I believe this is a bug in your ODBC driver.
I kind of expected that. I'll file a bugreport to Vertica Support.
good, that was part of what I was hoping to achieve when I did this.
However, I took the decision in DBD::ODBC to report this an error and some
might argue differently. In this case it is fairly innocuous as you tried to
drop a table which did not exist and afterwards it does not exist - although
what happens if it was an error in your application and the table should
have existed? In other cases it is more clear cut - what if you tried to set
a statement attribute like a cursor to type A and the driver said, I've set
a cursor, but not to type A, I've set it to type B - an app would likely
want to know this.
IMO current behavior is correct.
I can see it might be inconvenient for you the way it is now as your forced
to do something like:
$h-{RaiseError} = 1;
eval {
$h-do(q/drop table foo/);
};
if ($@ $h-errstr =~ /Unable to fetch information about the error/) {
# assume the drop was ok and the table did not exist
# but that is not really a safe assumption to make
} elsif ($@) {
# it is a real error
}
I've already adjusted the code with this, I also narrowed the
if-condition with $dbh-state eq 'IM008',
as I'm getting this state along with the error. Not that it's neat,
but allows me to work
around this driver issue for the moment.
BTW I found out IM008 is returned by DBD::ODBC, then checked
http://www.easysoft.com/developer/interfaces/odbc/sqlstate_status_return_codes.html#IM008
and must admit I can't see a relationship between the actual error and
the state.
hah, my own document comes back to haunt me.
You are right, it is down to:
if (!error_found (err_rc != SQL_NO_DATA_FOUND)) {
if (DBIc_TRACE(imp_xxh, DBD_TRACING, 0, 3))
TRACE1(imp_dbh, ** No error found %d **\n, err_rc);
DBIh_SET_ERR_CHAR(
h, imp_xxh, Nullch, 1,
Unable to fetch information about the error, IM008, Nullch);
}
and I'm guessing a cut and paste. DBD::ODBC has to invent a state here and I guess it
should be HY000. If I were you I'd not rely on IM008 as now you've pointed it out I may
change it to HY000 so you might be better looking for IM008|HY000 or No error
found in errstr. I've just added a comment to the source, not to change that string.
I could be swayed to change this to a warning (which would be less
inconvenient to you since you could disable warnings when you are dropping a
table) but I'd need to be persuaded. This is one of those occasions when I'm
damned either way. If I don't report it as an error I end up debugging
peoples ODBC logs only to tell them their driver is broken and if I do
report an error people come back to me saying why is this an error.
Agree.
BTW, are you sure your db can actually rollback a drop table, and if it
cannot, then why bother doing it in a txn?
This is due to the code being used to handle data both in PostgreSQL database
(which supports transactional DDL) and Vertica (which does not support that).
We are likely about to consider changing the application logic.
Thank very much for the explanation!
Cheers
Jan
np.
Martin
RE: Perl DBI Hangs while execute()
There are many things you can do in sqlplus that you cannot do in sql (in this case DBI). What are you trying to run? -Original Message- From: amaresh pothnal [mailto:amaresh.poth...@gmail.com] Sent: Monday, November 19, 2012 3:29 AM To: dbi-users@perl.org Subject: Perl DBI Hangs while execute() Hi All, I am using Perl DBI to connect Oracle Database. The query runs if type from 'sqlplus' manually but hangs when I use the sql inside the script. Getting following error, DBD::Oracle::st execute failed: ORA-36871: (XSFTDSC01) Object %s cannot be used to define a column in a LIMITMAP. (DBD ERROR: OCIStmtExecute) [for Statement And If remove that original query and replace 'select * from dual' and execute the script, I get 'Segementation Fault'. Please help me to solve this. Thanks -- Amaresh P
Re: Perl DBI Hangs while execute()
Thanks For your support. This issue got solved after installation of nls libraries. Thanks Amaresh On Wed, Nov 21, 2012 at 5:53 AM, John Scoles byter...@hotmail.com wrote: Well I would do something like select 1 from dual rather thatn '*' It sounds like your DB coonection string is not correct. Cheers From: amaresh.poth...@gmail.com Date: Mon, 19 Nov 2012 16:59:09 +0530 Subject: Perl DBI Hangs while execute() To: dbi-users@perl.org Hi All, I am using Perl DBI to connect Oracle Database. The query runs if type from 'sqlplus' manually but hangs when I use the sql inside the script. Getting following error, DBD::Oracle::st execute failed: ORA-36871: (XSFTDSC01) Object %s cannot be used to define a column in a LIMITMAP. (DBD ERROR: OCIStmtExecute) [for Statement And If remove that original query and replace 'select * from dual' and execute the script, I get 'Segementation Fault'. Please help me to solve this. Thanks -- Amaresh P
Perl DBI Hangs while execute()
Hi All, I am using Perl DBI to connect Oracle Database. The query runs if type from 'sqlplus' manually but hangs when I use the sql inside the script. Getting following error, DBD::Oracle::st execute failed: ORA-36871: (XSFTDSC01) Object %s cannot be used to define a column in a LIMITMAP. (DBD ERROR: OCIStmtExecute) [for Statement And If remove that original query and replace 'select * from dual' and execute the script, I get 'Segementation Fault'. Please help me to solve this. Thanks -- Amaresh P
RE: Perl DBI Hangs while execute()
Well I would do something like select 1 from dual rather thatn '*' It sounds like your DB coonection string is not correct. Cheers From: amaresh.poth...@gmail.com Date: Mon, 19 Nov 2012 16:59:09 +0530 Subject: Perl DBI Hangs while execute() To: dbi-users@perl.org Hi All, I am using Perl DBI to connect Oracle Database. The query runs if type from 'sqlplus' manually but hangs when I use the sql inside the script. Getting following error, DBD::Oracle::st execute failed: ORA-36871: (XSFTDSC01) Object %s cannot be used to define a column in a LIMITMAP. (DBD ERROR: OCIStmtExecute) [for Statement And If remove that original query and replace 'select * from dual' and execute the script, I get 'Segementation Fault'. Please help me to solve this. Thanks -- Amaresh P
Re: DBD::mysql::st fetchrow_array failed: fetch() without execute()
Hi, Paul, I had a similar problem but determined it is not (or at least, mine was not) a DBI or DBD::mysql issue. I did find the cause, in my case. I'm not sure how this could happen and why it would only affect one item out of thousands, but there was corruption in the database itself (I think, I'm no mysql admin ;). We have not attempted to do any repair, as the db involved is being retired (read only, for reference) with all data moved to another db. And I was able to come up with a work around for my particular case. What I saw was improper formatting of the header line. When I tried to do a select column from ... I would get the error. When I did a select * from ... there would be no error. But the printed header looked like: ++-- |column | ...other headers | ... ++-- It should have been: ++-- |column | ...other headers | ... ++-- I hope this helps. I can help with more details, if you can confirm the problem is the same or similar, and if you can supply a copy of the SQL you're using. On 08/11/2011 11:49 AM, Paul wrote: Did you find a solution to this problem? Cos I'm having the same difficulties. -- Bob McGowan
Re: DBD::mysql::st fetchrow_array failed: fetch() without execute()
Did you find a solution to this problem? Cos I'm having the same difficulties.
execute external SQL scripts file within perl/DBI
I have a SQL routine stored in a file, that I wish to run in between other SQL statements within a perl/DBI script. I wonder, besides to read that file into a prepare statement, is there a way to directly execute that file from within the perl script? On a second thought, the system(mysql -u user -p pass dbname script_file.sql); might be one but is there more simple ways since with a perl/DBI a connection is already established? joe
Re: DBD::mysql::st fetchrow_array failed: fetch() without execute()
Note: I tried latest DBI and DBD::mysql, and the same DBI, DBD::mysql as the old server. the result is the same. and I don't have Apache::DBI enabled. Thanks On 2011/1/13 16:40, Fayland Lam wrote: Hi, here is the background: 1. we have everything fine on Perl v5.8.5 CentOS release 4.3 (Final) 2. the code is from a big Catalyst framework run under mod_perl 2.0.2 (we have DBIx::Class code but all errors are from plain DBI usage now) we upgraded our OS to CentOS release 5.3 (Final) and we have some encoding issue with our Catalyst application. then we upgraded the Perl to 5.10.1 and re-build the mod_perl 2.0.4 but in the new server, we keeping getting DBD::mysql::st fetchrow_array failed: fetch() without execute() or DBD::mysql::db selectrow_array failed: fetch() without execute() we have several MySQL servers and all the errors happens with 5.1.39-community-log MySQL Community Server (GPL) fine with Server version: 5.0.77-log Source distribution I posted the DBI-trace(6) info in pastebin: Correct: http://pastebin.com/XjFfmJYJ FAIL: http://pastebin.com/iTPCMusG 1 - prepare= DBI::st=HASH(0x2acbe173f728) at Report.pm line 963 via at Report.pm line 763 - dbd_st_execute for 2acbf5c22de0 - dbd_st_execute 1 rows - dbd_st_fetch for 2acbf5c22de0, chopblanks 0 fetch() without execute() error 19 recorded: fetch() without execute() !! ERROR: 19 'fetch() without execute()' (err#0) other notes I tried: 1. if I change fetchrow_array on SQL SELECT 1 to $sth-rows 1, nothing will be wrong. 2. if I change selectrow_array to prepare + execute + fetchrow_array, fails (SQL is SELECT COUNT(*) so I can't really do $sth-rows I think) 3. the issue is not happening at the beginning when we restart the Apache. but after few minutes, it happens. keeping having this issues. never gone. my OS is 64bit and Perl 5.10 is built with -des -A ccflags=-fPIC -Dinstallprefix=/usr -Dprefix=/usr -Duseshrplib -Dusethreads -Duseithreads -Duselargefiles -Dinstallusrbinperl so the DBI is ithreads one. (Note old one is ithreads too) DBD::mysql is not from yum install but I did it manually (with yum install mysql-devel then cpanm it) Do anyone here have the same issue before? and share me some thoughts? if you need any more info or anything you want me to try, just let me know. Thanks -- Fayland Lam // http://www.fayland.org/
DBD::mysql::st fetchrow_array failed: fetch() without execute()
Hi, here is the background: 1. we have everything fine on Perl v5.8.5 CentOS release 4.3 (Final) 2. the code is from a big Catalyst framework run under mod_perl 2.0.2 (we have DBIx::Class code but all errors are from plain DBI usage now) we upgraded our OS to CentOS release 5.3 (Final) and we have some encoding issue with our Catalyst application. then we upgraded the Perl to 5.10.1 and re-build the mod_perl 2.0.4 but in the new server, we keeping getting DBD::mysql::st fetchrow_array failed: fetch() without execute() or DBD::mysql::db selectrow_array failed: fetch() without execute() we have several MySQL servers and all the errors happens with 5.1.39-community-log MySQL Community Server (GPL) fine with Server version: 5.0.77-log Source distribution I posted the DBI-trace(6) info in pastebin: Correct: http://pastebin.com/XjFfmJYJ FAIL: http://pastebin.com/iTPCMusG 1 - prepare= DBI::st=HASH(0x2acbe173f728) at Report.pm line 963 via at Report.pm line 763 - dbd_st_execute for 2acbf5c22de0 - dbd_st_execute 1 rows - dbd_st_fetch for 2acbf5c22de0, chopblanks 0 fetch() without execute() error 19 recorded: fetch() without execute() !! ERROR: 19 'fetch() without execute()' (err#0) other notes I tried: 1. if I change fetchrow_array on SQL SELECT 1 to $sth-rows 1, nothing will be wrong. 2. if I change selectrow_array to prepare + execute + fetchrow_array, fails (SQL is SELECT COUNT(*) so I can't really do $sth-rows I think) 3. the issue is not happening at the beginning when we restart the Apache. but after few minutes, it happens. keeping having this issues. never gone. my OS is 64bit and Perl 5.10 is built with -des -A ccflags=-fPIC -Dinstallprefix=/usr -Dprefix=/usr -Duseshrplib -Dusethreads -Duseithreads -Duselargefiles -Dinstallusrbinperl so the DBI is ithreads one. (Note old one is ithreads too) DBD::mysql is not from yum install but I did it manually (with yum install mysql-devel then cpanm it) Do anyone here have the same issue before? and share me some thoughts? if you need any more info or anything you want me to try, just let me know. Thanks -- Fayland Lam // http://www.fayland.org/
Re: DBD::mysql::st fetchrow_array failed: fetch() without execute()
so what does it mean? http://pastebin.com/iTPCMusG 1. - dbd_st_execute 1 rows 2. - dbd_st_fetch for 2acbf5c22de0, chopblanks 0 3. fetch() without execute() error 19 recorded: fetch() without execute() it means it executed OK? $sth-rows will return 1 but we just can't do fetch on it? how is it possible? and the thing is that it works fine at the beginning under mod_perl (Catalyst code) after restart Apache but suddenly full of errors, and all are fetchrow_array, selectrow_array (not DBIx::Class) any thoughts? Thanks On 2011/1/13 16:40, Fayland Lam wrote: Hi, here is the background: 1. we have everything fine on Perl v5.8.5 CentOS release 4.3 (Final) 2. the code is from a big Catalyst framework run under mod_perl 2.0.2 (we have DBIx::Class code but all errors are from plain DBI usage now) we upgraded our OS to CentOS release 5.3 (Final) and we have some encoding issue with our Catalyst application. then we upgraded the Perl to 5.10.1 and re-build the mod_perl 2.0.4 but in the new server, we keeping getting DBD::mysql::st fetchrow_array failed: fetch() without execute() or DBD::mysql::db selectrow_array failed: fetch() without execute() we have several MySQL servers and all the errors happens with 5.1.39-community-log MySQL Community Server (GPL) fine with Server version: 5.0.77-log Source distribution I posted the DBI-trace(6) info in pastebin: Correct: http://pastebin.com/XjFfmJYJ FAIL: http://pastebin.com/iTPCMusG 1 - prepare= DBI::st=HASH(0x2acbe173f728) at Report.pm line 963 via at Report.pm line 763 - dbd_st_execute for 2acbf5c22de0 - dbd_st_execute 1 rows - dbd_st_fetch for 2acbf5c22de0, chopblanks 0 fetch() without execute() error 19 recorded: fetch() without execute() !! ERROR: 19 'fetch() without execute()' (err#0) other notes I tried: 1. if I change fetchrow_array on SQL SELECT 1 to $sth-rows 1, nothing will be wrong. 2. if I change selectrow_array to prepare + execute + fetchrow_array, fails (SQL is SELECT COUNT(*) so I can't really do $sth-rows I think) 3. the issue is not happening at the beginning when we restart the Apache. but after few minutes, it happens. keeping having this issues. never gone. my OS is 64bit and Perl 5.10 is built with -des -A ccflags=-fPIC -Dinstallprefix=/usr -Dprefix=/usr -Duseshrplib -Dusethreads -Duseithreads -Duselargefiles -Dinstallusrbinperl so the DBI is ithreads one. (Note old one is ithreads too) DBD::mysql is not from yum install but I did it manually (with yum install mysql-devel then cpanm it) Do anyone here have the same issue before? and share me some thoughts? if you need any more info or anything you want me to try, just let me know. Thanks -- Fayland Lam // http://www.fayland.org/
Re: There's an error, but $sth-execute returns -1
On 18/10/10 23:05, Tim Bunce wrote: On Sat, Oct 16, 2010 at 11:03:08AM +0100, Martin J. Evans wrote: On 15/10/2010 22:20, Roode, Eric wrote: Hello all, DBI docs says that statement handle method execute() returns undef on error. I have found a situation where that does not hold. Was hoping someone could help me out, maybe there's a workaround. (Tim Bunce, if you see this could you clarify what dbd_st_execute is supposed to return as I could not find the full details in DBI::DBD. It would appear DBD::ODBC returns -2 for error, -1 for rowcount not known and a positive number for rows affected). I see it's not mentioned in the DBI::DBD docs. From Driver.xst: retval = dbd_st_execute(sth, imp_sth); /* remember that dbd_st_execute must return = -2 for error */ if (retval == 0)/* ok with no rows affected */ XST_mPV(0, 0E0); /* (true but zero) */ else if (retval -1) /* -1 == unknown number of rows */ XST_mUNDEF(0); /* = -2 means error*/ else XST_mIV(0, retval); /* typically 1, rowcount or -1 */ The status returned was SQL_SUCCESS_WITH_INFO: !!dbd_error2(err_rc=1, what=st_execute/SQLExecute, handles=(3194c98,2b5a848,30d4cd0) that err_rc=1 which is not an error. If SQLRowCount returns -1 then dbd_st_execute will return -1, that may be a bug but really the code should not have got to this point anyway. The are differences between what DBI documents for the execute method and what DBD::ODBC returns from the dbd_st_execute and I cannot see for instance the -2 (for errors) documented in DBI::DBD so I'm not sure if this is right or wrong (Tim?). It should be documented that dbd_st_execute should return = -2 to indicate an error. Tim. I've updated DBI::DBD to that effect. Martin As far as I can see this looks like a bug in your driver (but I'll try and reproduce here). Surely failing to insert into a column should be an error not success with info (an example of the latter is say an insert which worked but truncated your data). What ODBC Driver manager and driver versions are you using - you can find these from the Administrative tools, data sources or in windows 7 by searching for data sources in the control panel top right. Martin
Re: There's an error, but $sth-execute returns -1
On 18/10/10 22:54, Tim Bunce wrote:
On Mon, Oct 18, 2010 at 04:45:51PM +0100, Martin J. Evans wrote:
State 23000 you are getting is Integrity constraint violation which is an
error but note the text on the end of the error you are getting:
[state was 23000 now 01000]
01000 is a general warning. I don't understand why the now 01000 in the
text of the error.
[state was 23000 now 01000] looks like the behaviour of the set_err
method: http://search.cpan.org/~timb/DBI/DBI.pm#set_err
Of course, I always forget that.
It looks like state 23000 was recorded but then updated to 01000 by a
separate event. But, looking at the internal code for set_err_sv,
I see that message might get appended even if the code then decides not
to change the err value.
You could use something like $h-{HandleSetErr} = sub { warn @_; return 0; }
to see the second event getting recorded.
All drivers I've tried this with report 2 things. The 23000 error for the
insert of a null into a column which does not allow nulls (the tds_error
packet) then a 01000 informational (tds_info) which states the statement has
been terminated.
However, it does not get around the fact that SQLExecute is returning
SQL_SUCCESS_WITH_INFO with the broken driver. DBD::ODBC uses the SQLExecute
return to determine if the execute was successful or not (as per the ODBC docs)
and not the state of any error it sees when recovering details of the errors
and informationals.
I suppose this could be changed but it would need additional logic everywhere
if (SQL_SUCCEEDED(ret)) is called and some way of recording the states it has
seen since the last ODBC call. I'm not keen on doing that and not only because
the driver in this case is broken.
Tim.
So something is a amiss there. Having said that the MS native client reports
the same error but SQLExecute returns SQL_ERROR.
As far as I am concerned (and I've written ODBC drivers and code to ODBC
Drivers for more years than I care to admit) the condition you have hit is
an error and when SQLError is called an error number, state and text is
returned BUT the call to SQLExecute is returning SQL_SUCCESS_WITH_INTO
instead of SQL_ERROR. I've read your comment from the Microsoft guy but I
don't believe it and in any case I have 1 of their drivers which behaves
differently than the one you've got.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
Re: There's an error, but $sth-execute returns -1
On Tue, Oct 19, 2010 at 08:59:19AM +0100, Martin J. Evans wrote:
On 18/10/10 22:54, Tim Bunce wrote:
On Mon, Oct 18, 2010 at 04:45:51PM +0100, Martin J. Evans wrote:
State 23000 you are getting is Integrity constraint violation which is
an error but note the text on the end of the error you are getting:
[state was 23000 now 01000]
01000 is a general warning. I don't understand why the now 01000 in the
text of the error.
[state was 23000 now 01000] looks like the behaviour of the set_err
method: http://search.cpan.org/~timb/DBI/DBI.pm#set_err
Of course, I always forget that.
It looks like state 23000 was recorded but then updated to 01000 by a
separate event. But, looking at the internal code for set_err_sv,
I see that message might get appended even if the code then decides not
to change the err value.
Which is probably a bug.
You could use something like $h-{HandleSetErr} = sub { warn @_; return
0; }
to see the second event getting recorded.
All drivers I've tried this with report 2 things. The 23000 error for the
insert of a null into a column which does not allow nulls (the tds_error
packet) then a 01000 informational (tds_info) which states the statement has
been terminated.
However, it does not get around the fact that SQLExecute is returning
SQL_SUCCESS_WITH_INFO with the broken driver. DBD::ODBC uses the SQLExecute
return to determine if the execute was successful or not (as per the ODBC
docs) and not the state of any error it sees when recovering details of the
errors and informationals.
I suppose this could be changed but it would need additional logic everywhere
if (SQL_SUCCEEDED(ret)) is called and some way of recording the states it
has seen since the last ODBC call. I'm not keen on doing that and not only
because the driver in this case is broken.
I agree.
Tim.
p.s. Thanks for the doc patch.
Tim.
So something is a amiss there. Having said that the MS native client
reports the same error but SQLExecute returns SQL_ERROR.
As far as I am concerned (and I've written ODBC drivers and code to ODBC
Drivers for more years than I care to admit) the condition you have hit is
an error and when SQLError is called an error number, state and text is
returned BUT the call to SQLExecute is returning SQL_SUCCESS_WITH_INTO
instead of SQL_ERROR. I've read your comment from the Microsoft guy but I
don't believe it and in any case I have 1 of their drivers which behaves
differently than the one you've got.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
RE: There's an error, but $sth-execute returns -1
On Tuesday, October 19, 2010 3:59 AM, Martin J. Evans wrote:
All drivers I've tried this with report 2 things. The 23000 error
for the insert of a null into a column which does not allow nulls
(the tds_error packet) then a 01000 informational (tds_info) which
states the statement has been terminated.
However, it does not get around the fact that SQLExecute is
returning SQL_SUCCESS_WITH_INFO with the broken driver. DBD::ODBC
uses the SQLExecute return to determine if the execute was
successful or not (as per the ODBC docs) and not the state of any
error it sees when recovering details of the errors and
informationals.
I suppose this could be changed but it would need additional logic
everywhere if (SQL_SUCCEEDED(ret)) is called and some way of
recording the states it has seen since the last ODBC call. I'm not
keen on doing that and not only because the driver in this case is
broken.
I agree; it hardly seems worthwhile. This is what I put in place
as a workaround. Does it look reasonable?
$retval = $sth-execute(...);
$sqlstate = $handle-state;
if ( (!defined $retval)
|| (defined $sqlstatelength $sqlstate
$sqlstate ne '0'$sqlstate ne 'S1000'
substr($sqlstate,0,2) ne '01')
)
{
...handle error condition
-- Eric
Re: There's an error, but $sth-execute returns -1
On 19/10/2010 18:03, Roode, Eric wrote:
On Tuesday, October 19, 2010 3:59 AM, Martin J. Evans wrote:
All drivers I've tried this with report 2 things. The 23000 error
for the insert of a null into a column which does not allow nulls
(the tds_error packet) then a 01000 informational (tds_info) which
states the statement has been terminated.
However, it does not get around the fact that SQLExecute is
returning SQL_SUCCESS_WITH_INFO with the broken driver. DBD::ODBC
uses the SQLExecute return to determine if the execute was
successful or not (as per the ODBC docs) and not the state of any
error it sees when recovering details of the errors and
informationals.
I suppose this could be changed but it would need additional logic
everywhere if (SQL_SUCCEEDED(ret)) is called and some way of
recording the states it has seen since the last ODBC call. I'm not
keen on doing that and not only because the driver in this case is
broken.
I agree; it hardly seems worthwhile. This is what I put in place
as a workaround. Does it look reasonable?
$retval = $sth-execute(...);
$sqlstate = $handle-state;
if ( (!defined $retval)
|| (defined $sqlstate length $sqlstate
$sqlstate ne '0' $sqlstate ne 'S1000'
substr($sqlstate,0,2) ne '01')
)
{
...handle error condition
-- Eric
Yes.
I'd move that length test to later, just before the substr as it does
not matter until just before the substr. I think you might be mixing
ODBC 2 (e.g. the S1000) and 3 states - you should only get ODBC 3 states
via DBD::ODBC. I'd need to check that and I cannot easily right now.
I think you might be able to do this in a more straight forward way
using odbc_err_handler but I've not thought it through right now.
Martin
RE: There's an error, but $sth-execute returns -1
On Saturday, October 16, 2010 6:03 AM, Martin J. Evans wrote: [...] As far as I can see this looks like a bug in your driver (but I'll try and reproduce here). Surely failing to insert into a column should be an error not success with info (an example of the latter is say an insert which worked but truncated your data). What ODBC Driver manager and driver versions are you using - you can find these from the Administrative tools, data sources or in windows 7 by searching for data sources in the control panel top right. Oh, of course. I forgot to include that information, sorry. I'm using Microsoft's SQLSRV32.SLL, version 6.01.7600.16385, of 2009/07/13. -- Eric
RE: There's an error, but $sth-execute returns -1
On Saturday, October 16, 2010 6:18 AM, Martin J. Evans wrote: On 15/10/2010 22:20, Roode, Eric wrote: Hello all, DBI docs says that statement handle method execute() returns undef on error. I have found a situation where that does not hold. Was hoping someone could help me out, maybe there's a workaround. You need to read further down that entry where it says: If the number of rows affected is not known, then execute returns -1. Admittedly, in your case it looks like an error from the driver error message but it did not return an error so perhaps you stopped when you saw undef for error. I knew perfectly well that -1 means number of rows unknown. I didn't see how that could apply to [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'enabled', table 'feeds'; column does not allow nulls. INSERT fails. (SQL-23000) [state was 23000 now 01000] [Microsoft][ODBC SQL Server Driver] [SQL Server]The statement has been terminated. (SQL-01000) That's just an error, plain and simple. I'm perfectly willing to accept that it may not have been reported as an error, due to a bug in Microsoft's driver or whatever, but to suggest that I didn't read the docs and therefore didn't interpret -1 correctly is ridiculous. -- Eric
Re: There's an error, but $sth-execute returns -1
On 18/10/10 14:02, Roode, Eric wrote: On Saturday, October 16, 2010 6:18 AM, Martin J. Evans wrote: On 15/10/2010 22:20, Roode, Eric wrote: Hello all, DBI docs says that statement handle method execute() returns undef on error. I have found a situation where that does not hold. Was hoping someone could help me out, maybe there's a workaround. You need to read further down that entry where it says: If the number of rows affected is not known, then execute returns -1. Admittedly, in your case it looks like an error from the driver error message but it did not return an error so perhaps you stopped when you saw undef for error. I knew perfectly well that -1 means number of rows unknown. I didn't see how that could apply to [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'enabled', table 'feeds'; column does not allow nulls. INSERT fails. (SQL-23000) [state was 23000 now 01000] [Microsoft][ODBC SQL Server Driver] [SQL Server]The statement has been terminated. (SQL-01000) That's just an error, plain and simple. I'm perfectly willing to accept that it may not have been reported as an error, due to a bug in Microsoft's driver or whatever, but to suggest that I didn't read the docs and therefore didn't interpret -1 correctly is ridiculous. -- Eric I never meant to offend you with that statement and I agreed it was an error. You are posting on a public list and presumably wanting feedback/help. I only know what you've told me, nothing else. Perhaps you might think about that before leaping to the conclusion you seem to have. I spent nearly one hour of my Saturday looking into the problem you reported so obviously I took your report seriously. Perhaps some calm is required. Martin
RE: There's an error, but $sth-execute returns -1
On Monday, October 18, 2010 9:24 AM, Martin J. Evans wrote: I never meant to offend you with that statement and I agreed it was an error. You are posting on a public list and presumably wanting feedback/help. I only know what you've told me, nothing else. Perhaps you might think about that before leaping to the conclusion you seem to have. I spent nearly one hour of my Saturday looking into the problem you reported so obviously I took your report seriously. Perhaps some calm is required. I'm sorry, I didn't mean to fly off the handle. I do appreciate any insight you (or anyone else) can give. I'm just frustrated at what appears to be a bug in the driver (or possibly DBD::ODBC), and am looking for any way to work around it. My apologies, -- Eric
RE: There's an error, but $sth-execute returns -1
On Saturday, October 16, 2010 10:08 AM, Martin J. Evans wrote: When I run your code with a different driver to MS SQL Server it works as I'd expect: [...] print Execute() returned $exec_retval.\n; print 'errstr is', $sth-errstr, '', \n; mar...@bragi:~/svn/dbd-odbc/trunk/rt_data$ perl success_with_info.pl DBD::ODBC::st execute failed: [unixODBC][Easysoft][SQL Server Driver][SQL Server ]Cannot insert the value NULL into column 'enabled', table 'master.dbo.feeds'; column does not allow nulls. INSERT fails. (SQL-23000) [state was 23000 now 01000] [unixODBC][Easysoft][SQL Server Driver][SQL Server]The statement has been terminated. (SQL-01000) at success_with_info.pl line 103. Use of uninitialized value $exec_retval in concatenation (.) or string at success_with_info.pl line 110. Execute() returned . errstr is[unixODBC][Easysoft][SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'enabled', table 'master.dbo.feeds'; column does not allow nulls. INSERT fails. (SQL-23000) [state was 23000 now 01000] [unixODBC][Easysoft][SQL Server Driver][SQL Server]The statement has been terminated. (SQL-01000) mar...@bragi:~/svn/dbd-odbc/trunk/rt_data$ Which as far as I'm concerned is more evidence that your driver is broken. Please send me the driver name, version etc you are using. At the very least I may add a note that it is broken as there is little I might be able to do to work around this. That's interesting -- what driver were you using for that test? (as mentioned elsewhere in this thread, I am using SQLSRV32.DLL, version 6.01.7600.16385, of 2009/07/13). I spoke to a database guy I know at Microsoft; he was of the opinion that since the sproc was *called* successfully, it was not an error condition, and that I need to parse the return info (error message). He wasn't speaking authoritatively though, and I don't know if this is true in general. Do you happen to know what counts as an error condition for SQL Server / ODBC? And what counts as SQL_SUCCESS_WITH_INFO? Thanks, -- Eric
RE: There's an error, but $sth-execute returns -1
On Monday, October 18, 2010 10:21 AM, Eric Roode wrote: [...] I spoke to a database guy I know at Microsoft; he was of the opinion that since the sproc was *called* successfully, it was not an error condition, and that I need to parse the return info (error message). He wasn't speaking authoritatively though, and I don't know if this is true in general. Do you happen to know what counts as an error condition for SQL Server / ODBC? And what counts as SQL_SUCCESS_WITH_INFO? This page: http://msdn.microsoft.com/en-us/library/ms714687%28v=VS.85%29.aspx has some interesting info about the STATE that gets returned. In particular, it shows that the state code 23000 (which is what I got) means Integrity constraint violation. So I suppose if $sth-execute() returns -1, I should also look at $sth-state(). That page also says that if the first two characters of the state are 01, then it's a warning, and the driver should return SQL_SUCCESS_WITH_INFO; and if the first two characters are anything else, it's an error, and the driver should return SQL_ERROR. So that does point to it being a driver bug. Thanks, -- Eric
Re: There's an error, but $sth-execute returns -1
On 18/10/10 14:58, Roode, Eric wrote: On Monday, October 18, 2010 9:24 AM, Martin J. Evans wrote: I never meant to offend you with that statement and I agreed it was an error. You are posting on a public list and presumably wanting feedback/help. I only know what you've told me, nothing else. Perhaps you might think about that before leaping to the conclusion you seem to have. I spent nearly one hour of my Saturday looking into the problem you reported so obviously I took your report seriously. Perhaps some calm is required. I'm sorry, I didn't mean to fly off the handle. I do appreciate any insight you (or anyone else) can give. I'm just frustrated at what appears to be a bug in the driver (or possibly DBD::ODBC), and am looking for any way to work around it. My apologies, -- Eric I was using the Easysoft SQL Server Driver but I also tried with the MS SQL Server Drivers: sqlsrv32.dll 14/04/2008 2000.85.1132.00 sqlncli.dll 14/10/2005 2005.90.1399.00 and the first echoed your result and the second worked (i.e., execute failed). You have a newish driver but it is looking broken to me. Have you tried the native client driver (see above). You could also try calling SQLMoreResults after the execute which appears to succeed until it returns false but ensure RaiseError is on. See odbc_more_results in the pod of DBD::ODBC and t/20SqlServer.t for examples. I only say this is worth trying as normally the final result of a procedure is not known until all of the procedure has finished and you have multiple statements in your proc (but it would be at best a workaround - see below). I tried it myself with the MS SQL Server ODBC Driver above and it did not help. I also checked again the ODBC spec and the only SQL_SUCCESS_WITH_INFO returns are for statements which succeed but may have succeeded for a reason you might like to know e.g., 01003, NULL value eliminated in set function The prepared statement associated with StatementHandle contained a set function (such as AVG, MAX, MIN, and so on), but not the COUNT set function, and NULL argument values were eliminated before the function was applied. (Function returns SQL_SUCCESS_WITH_INFO.) 01004, String data right truncated etc State 23000 you are getting is Integrity constraint violation which is an error but note the text on the end of the error you are getting: [state was 23000 now 01000] 01000 is a general warning. I don't understand why the now 01000 in the text of the error. So something is a amiss there. Having said that the MS native client reports the same error but SQLExecute returns SQL_ERROR. As far as I am concerned (and I've written ODBC drivers and code to ODBC Drivers for more years than I care to admit) the condition you have hit is an error and when SQLError is called an error number, state and text is returned BUT the call to SQLExecute is returning SQL_SUCCESS_WITH_INTO instead of SQL_ERROR. I've read your comment from the Microsoft guy but I don't believe it and in any case I have 1 of their drivers which behaves differently than the one you've got. Martin -- Martin J. Evans Easysoft Limited http://www.easysoft.com
Re: There's an error, but $sth-execute returns -1
On 18/10/10 16:45, Martin J. Evans wrote: On 18/10/10 14:58, Roode, Eric wrote: On Monday, October 18, 2010 9:24 AM, Martin J. Evans wrote: I never meant to offend you with that statement and I agreed it was an error. You are posting on a public list and presumably wanting feedback/help. I only know what you've told me, nothing else. Perhaps you might think about that before leaping to the conclusion you seem to have. I spent nearly one hour of my Saturday looking into the problem you reported so obviously I took your report seriously. Perhaps some calm is required. I'm sorry, I didn't mean to fly off the handle. I do appreciate any insight you (or anyone else) can give. I'm just frustrated at what appears to be a bug in the driver (or possibly DBD::ODBC), and am looking for any way to work around it. My apologies, -- Eric I was using the Easysoft SQL Server Driver but I also tried with the MS SQL Server Drivers: sqlsrv32.dll 14/04/2008 2000.85.1132.00 sqlncli.dll 14/10/2005 2005.90.1399.00 and the first echoed your result and the second worked (i.e., execute failed). You have a newish driver but it is looking broken to me. Have you tried the native client driver (see above). You could also try calling SQLMoreResults after the execute which appears to succeed until it returns false but ensure RaiseError is on. See odbc_more_results in the pod of DBD::ODBC and t/20SqlServer.t for examples. I only say this is worth trying as normally the final result of a procedure is not known until all of the procedure has finished and you have multiple statements in your proc (but it would be at best a workaround - see below). I tried it myself with the MS SQL Server ODBC Driver above and it did not help. I also checked again the ODBC spec and the only SQL_SUCCESS_WITH_INFO returns are for statements which succeed but may have succeeded for a reason you might like to know e.g., 01003, NULL value eliminated in set function The prepared statement associated with StatementHandle contained a set function (such as AVG, MAX, MIN, and so on), but not the COUNT set function, and NULL argument values were eliminated before the function was applied. (Function returns SQL_SUCCESS_WITH_INFO.) 01004, String data right truncated etc State 23000 you are getting is Integrity constraint violation which is an error but note the text on the end of the error you are getting: [state was 23000 now 01000] 01000 is a general warning. I don't understand why the now 01000 in the text of the error. So something is a amiss there. Having said that the MS native client reports the same error but SQLExecute returns SQL_ERROR. As far as I am concerned (and I've written ODBC drivers and code to ODBC Drivers for more years than I care to admit) the condition you have hit is an error and when SQLError is called an error number, state and text is returned BUT the call to SQLExecute is returning SQL_SUCCESS_WITH_INTO instead of SQL_ERROR. I've read your comment from the Microsoft guy but I don't believe it and in any case I have 1 of their drivers which behaves differently than the one you've got. Martin I looked at this some more and I'm even more sure it is a bug. The TDS protocol for your situation returns: tds_error - failed to insert null tds_info - statement has been terminated a load of done_procs the return argument of your procedure containing error in add feed I think the driver is seeing the error but then its idea is overidden by the tds_info packet. Later native client drivers (and our driver) see exactly the same but they acknowledge the tds_error as being the final state and don't override it with the tds_info. BTW, the state returned is 23000 so you might be able to look at that (in $sth-state). I thought you might also be able to look at the return of your procedure but that is not possible. You could also perhaps do something with the odbc_err_handler. Martin -- Martin J. Evans Easysoft Limited http://www.easysoft.com
RE: There's an error, but $sth-execute returns -1
On Monday, October 18, 2010 12:27 PM, Martin J. Evans wrote: [...] I looked at this some more and I'm even more sure it is a bug. The TDS protocol for your situation returns: tds_error - failed to insert null tds_info - statement has been terminated a load of done_procs the return argument of your procedure containing error in add feed I think the driver is seeing the error but then its idea is overidden by the tds_info packet. Later native client drivers (and our driver) see exactly the same but they acknowledge the tds_error as being the final state and don't override it with the tds_info. BTW, the state returned is 23000 so you might be able to look at that (in $sth-state). I thought you might also be able to look at the return of your procedure but that is not possible. You could also perhaps do something with the odbc_err_handler. Thanks, Martin. What is this TDS and where can I learn more about it? -- Eric
Re: There's an error, but $sth-execute returns -1
On 18/10/2010 18:11, Roode, Eric wrote: On Monday, October 18, 2010 12:27 PM, Martin J. Evans wrote: [...] I looked at this some more and I'm even more sure it is a bug. The TDS protocol for your situation returns: tds_error - failed to insert null tds_info - statement has been terminated a load of done_procs the return argument of your procedure containing error in add feed I think the driver is seeing the error but then its idea is overidden by the tds_info packet. Later native client drivers (and our driver) see exactly the same but they acknowledge the tds_error as being the final state and don't override it with the tds_info. BTW, the state returned is 23000 so you might be able to look at that (in $sth-state). I thought you might also be able to look at the return of your procedure but that is not possible. You could also perhaps do something with the odbc_err_handler. Thanks, Martin. What is this TDS and where can I learn more about it? -- Eric TDS (Tabular Data Stream) is the protocol MS SQL Server and Sybase use. It is a proprietary protocol and although I've seen snippets of it documented on the net I don't know where you can go to find it now. You could of course look at the freeTDS project which attempts to duplicate TDS but I imagine that would mean looking at the source. I am however, pretty confident the ODBC driver you (and I) are using is broken. I back this up with: o later MS drivers behave differently and correctly o the state returned is 23000 which is not an informational state o it is just obviously an error o it contradicts MS's own documentation for ODBC I don't see a reasonable workaround I would be happy to apply to DBD::ODBC so it is I'm afraid left to you to workaround it or upgrade to a working driver or get MS to fix your existing driver. Sorry. Martin
Re: There's an error, but $sth-execute returns -1
On Mon, Oct 18, 2010 at 04:45:51PM +0100, Martin J. Evans wrote:
State 23000 you are getting is Integrity constraint violation which is an
error but note the text on the end of the error you are getting:
[state was 23000 now 01000]
01000 is a general warning. I don't understand why the now 01000 in the
text of the error.
[state was 23000 now 01000] looks like the behaviour of the set_err
method: http://search.cpan.org/~timb/DBI/DBI.pm#set_err
It looks like state 23000 was recorded but then updated to 01000 by a
separate event. But, looking at the internal code for set_err_sv,
I see that message might get appended even if the code then decides not
to change the err value.
You could use something like $h-{HandleSetErr} = sub { warn @_; return 0; }
to see the second event getting recorded.
Tim.
So something is a amiss there. Having said that the MS native client reports
the same error but SQLExecute returns SQL_ERROR.
As far as I am concerned (and I've written ODBC drivers and code to ODBC
Drivers for more years than I care to admit) the condition you have hit is an
error and when SQLError is called an error number, state and text is returned
BUT the call to SQLExecute is returning SQL_SUCCESS_WITH_INTO instead of
SQL_ERROR. I've read your comment from the Microsoft guy but I don't believe
it and in any case I have 1 of their drivers which behaves differently than
the one you've got.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
Re: There's an error, but $sth-execute returns -1
On Sat, Oct 16, 2010 at 11:03:08AM +0100, Martin J. Evans wrote: On 15/10/2010 22:20, Roode, Eric wrote: Hello all, DBI docs says that statement handle method execute() returns undef on error. I have found a situation where that does not hold. Was hoping someone could help me out, maybe there's a workaround. (Tim Bunce, if you see this could you clarify what dbd_st_execute is supposed to return as I could not find the full details in DBI::DBD. It would appear DBD::ODBC returns -2 for error, -1 for rowcount not known and a positive number for rows affected). I see it's not mentioned in the DBI::DBD docs. From Driver.xst: retval = dbd_st_execute(sth, imp_sth); /* remember that dbd_st_execute must return = -2 for error */ if (retval == 0)/* ok with no rows affected */ XST_mPV(0, 0E0); /* (true but zero) */ else if (retval -1) /* -1 == unknown number of rows */ XST_mUNDEF(0); /* = -2 means error*/ else XST_mIV(0, retval); /* typically 1, rowcount or -1 */ The status returned was SQL_SUCCESS_WITH_INFO: !!dbd_error2(err_rc=1, what=st_execute/SQLExecute, handles=(3194c98,2b5a848,30d4cd0) that err_rc=1 which is not an error. If SQLRowCount returns -1 then dbd_st_execute will return -1, that may be a bug but really the code should not have got to this point anyway. The are differences between what DBI documents for the execute method and what DBD::ODBC returns from the dbd_st_execute and I cannot see for instance the -2 (for errors) documented in DBI::DBD so I'm not sure if this is right or wrong (Tim?). It should be documented that dbd_st_execute should return = -2 to indicate an error. Tim. As far as I can see this looks like a bug in your driver (but I'll try and reproduce here). Surely failing to insert into a column should be an error not success with info (an example of the latter is say an insert which worked but truncated your data). What ODBC Driver manager and driver versions are you using - you can find these from the Administrative tools, data sources or in windows 7 by searching for data sources in the control panel top right. Martin
Re: There's an error, but $sth-execute returns -1
Roode, Eric schrieb am 18.10.2010 um 13:11 (-0400): What is this TDS and where can I learn more about it? http://en.wikipedia.org/wiki/Tabular_Data_Stream -- Michael Ludwig
Re: There's an error, but $sth-execute returns -1
On 15/10/2010 22:20, Roode, Eric wrote: Hello all, DBI docs says that statement handle method execute() returns undef on error. I have found a situation where that does not hold. Was hoping someone could help me out, maybe there's a workaround. First, I'm running ActiveState Perl 5.10.0 on Windows 7, connecting to a SQL Server 2000 database on a different machine. I have DBI version 1.613, and DBD::ODBC version 1.24. In a nutshell, here is what happens. I connect to the database, using the ODBC driver, and setting RaiseError to 1 and PrintError to 0. I prepare a statement which calls a procedure. I bind parameters to the statement (some input, some output, various types), I call execute(). There's a problem with one of the parameters; because of that, the stored procedure tries to insert a null value into a column that does not accept nulls. So SQL Server gives an error (Cannot insert the value NULL into column 'enabled'), and the DBI system stores this string in errstr. However, the execute() method returns -1, which is what it would return on success. Also, no error is thrown (I have the $sth-execute call wrapped in an eval, and $@ is empty afterward). You can see the definition of the table and the stored procedure at http://nopaste.gamedev.pl/?id=8272. The Perl code that demonstrates the problem is at http://nopaste.gamedev.pl/?id=8273. And the DBD trace output is at http://nopaste.gamedev.pl/?id=8274. I hope someone can shed some light on what's going on here. Should I check errstr and ignore the return value? Thanks in advance. Eric (Tim Bunce, if you see this could you clarify what dbd_st_execute is supposed to return as I could not find the full details in DBI::DBD. It would appear DBD::ODBC returns -2 for error, -1 for rowcount not known and a positive number for rows affected). The status returned was SQL_SUCCESS_WITH_INFO: !!dbd_error2(err_rc=1, what=st_execute/SQLExecute, handles=(3194c98,2b5a848,30d4cd0) that err_rc=1 which is not an error. If SQLRowCount returns -1 then dbd_st_execute will return -1, that may be a bug but really the code should not have got to this point anyway. The are differences between what DBI documents for the execute method and what DBD::ODBC returns from the dbd_st_execute and I cannot see for instance the -2 (for errors) documented in DBI::DBD so I'm not sure if this is right or wrong (Tim?). As far as I can see this looks like a bug in your driver (but I'll try and reproduce here). Surely failing to insert into a column should be an error not success with info (an example of the latter is say an insert which worked but truncated your data). What ODBC Driver manager and driver versions are you using - you can find these from the Administrative tools, data sources or in windows 7 by searching for data sources in the control panel top right. Martin
Re: There's an error, but $sth-execute returns -1
On 15/10/2010 22:20, Roode, Eric wrote: Hello all, DBI docs says that statement handle method execute() returns undef on error. I have found a situation where that does not hold. Was hoping someone could help me out, maybe there's a workaround. You need to read further down that entry where it says: If the number of rows affected is not known, then |execute| returns -1. Admittedly, in your case it looks like an error from the driver error message but it did not return an error so perhaps you stopped when you saw undef for error. First, I'm running ActiveState Perl 5.10.0 on Windows 7, connecting to a SQL Server 2000 database on a different machine. I have DBI version 1.613, and DBD::ODBC version 1.24. In a nutshell, here is what happens. I connect to the database, using the ODBC driver, and setting RaiseError to 1 and PrintError to 0. I prepare a statement which calls a procedure. I bind parameters to the statement (some input, some output, various types), I call execute(). There's a problem with one of the parameters; because of that, the stored procedure tries to insert a null value into a column that does not accept nulls. So SQL Server gives an error (Cannot insert the value NULL into column 'enabled'), and the DBI system stores this string in errstr. However, the execute() method returns -1, which is what it would return on success. Also, no error is thrown (I have the $sth-execute call wrapped in an eval, and $@ is empty afterward). You can see the definition of the table and the stored procedure at http://nopaste.gamedev.pl/?id=8272. The Perl code that demonstrates the problem is at http://nopaste.gamedev.pl/?id=8273. And the DBD trace output is at http://nopaste.gamedev.pl/?id=8274. I hope someone can shed some light on what's going on here. Should I check errstr and ignore the return value? Thanks in advance. Eric Martin
RE: There's an error, but $sth-execute returns -1
Eric take a look at the the procedure declaration: CREATE PROCEDURE api_add_feed (@messageVARCHAR(2000) OUTPUT, @new_id INT OUTPUT, @name VARCHAR(400), @descr VARCHAR(4000), @urlVARCHAR(400), @last_fetch DATETIME, @min_interval INT, @next_fetch DATETIME, @enabledVARCHAR(1), @creatorVARCHAR(50), @create_dateDATETIME ) #now synchronise perls bind_param to the declared parameters of the procedure $sth-bind_param_inout( 1, \$retval, 16, DBI::SQL_INTEGER); # useless statement comment this out $sth-bind_param_inout( 2, \$message, 400, DBI::SQL_VARCHAR);# should be parameter 1 $sth-bind_param_inout( 3, \$new_id, 16, DBI::SQL_INTEGER); # should be parameter 2 $sth-bind_param ( 4, 'Some name',DBI::SQL_VARCHAR); #description should be 'name' parameter 3 $sth-bind_param ( 5, 'Some desc',DBI::SQL_VARCHAR); #description should be 'url' should be parameter 4 $sth-bind_param ( 6, 'Some url', DBI::SQL_VARCHAR); #description should be 'url' should be parameter 5 $sth-bind_param ( 7, undef, DBI::SQL_TIMESTAMP); #description should be 'last_fetch parameter 6 $sth-bind_param ( 8, 3600, DBI::SQL_INTEGER); #should be parameter 7 $sth-bind_param ( 9, undef, DBI::SQL_TIMESTAMP); #description should be 'next_fetch' parameter 8 $sth-bind_param (10, undef, DBI::SQL_VARCHAR); #description should be 'enabled' parameter 9 $sth-bind_param (11, 'Some user',DBI::SQL_VARCHAR); #description should be creator parameter 10 $sth-bind_param (12, '2010-10-15 20:39:33',DBI::SQL_TIMESTAMP); #description should be 'create_date' parameter 11 please follow Martins advice *Mit Freundlichen Gruben* Martin __ Verzicht und Vertraulichkeitanmerkung Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Date: Sat, 16 Oct 2010 11:03:08 +0100 From: martin.ev...@easysoft.com To: ero...@barrack.com CC: dbi-users@perl.org Subject: Re: There's an error, but $sth-execute returns -1 On 15/10/2010 22:20, Roode, Eric wrote: Hello all, DBI docs says that statement handle method execute() returns undef on error. I have found a situation where that does not hold. Was hoping someone could help me out, maybe there's a workaround. First, I'm running ActiveState Perl 5.10.0 on Windows 7, connecting to a SQL Server 2000 database on a different machine. I have DBI version 1.613, and DBD::ODBC version 1.24. In a nutshell, here is what happens. I connect to the database, using the ODBC driver, and setting RaiseError to 1 and PrintError to 0. I prepare a statement which calls a procedure. I bind parameters to the statement (some input, some output, various types), I call execute(). There's a problem with one of the parameters; because of that, the stored procedure tries to insert a null value into a column that does not accept nulls. So SQL Server gives an error (Cannot insert the value NULL into column 'enabled'), and the DBI system stores this string in errstr. However, the execute() method returns -1, which is what it would return on success. Also, no error is thrown (I have the $sth-execute call wrapped in an eval, and $@ is empty afterward). You can see the definition of the table and the stored procedure at http://nopaste.gamedev.pl/?id=8272. The Perl code that demonstrates the problem is at http://nopaste.gamedev.pl/?id=8273. And the DBD trace output is at http://nopaste.gamedev.pl/?id=8274. I hope someone can shed some light on what's going on here. Should I check errstr and ignore the return value? Thanks in advance. Eric (Tim Bunce, if you see this could you clarify what dbd_st_execute is supposed to return as I could not find the full details in DBI::DBD. It would appear DBD::ODBC returns -2 for error, -1 for rowcount not known and a positive number for rows affected). The status returned was SQL_SUCCESS_WITH_INFO: !!dbd_error2(err_rc=1, what=st_execute/SQLExecute, handles
Re: There's an error, but $sth-execute returns -1
On 16/10/2010 14:43, Martin Gainty wrote:
Eric
take a look at the the procedure declaration:
CREATE PROCEDURE api_add_feed (@messageVARCHAR(2000) OUTPUT,
@new_id INT OUTPUT,
@name VARCHAR(400),
@descr VARCHAR(4000),
@urlVARCHAR(400),
@last_fetch DATETIME,
@min_interval INT,
@next_fetch DATETIME,
@enabledVARCHAR(1),
@creatorVARCHAR(50),
@create_dateDATETIME
)
#now synchronise perls bind_param to the declared parameters of the procedure
$sth-bind_param_inout( 1, \$retval, 16, DBI::SQL_INTEGER);
# useless statement comment this out
$sth-bind_param_inout( 2, \$message, 400, DBI::SQL_VARCHAR);# should be
parameter 1
$sth-bind_param_inout( 3, \$new_id, 16, DBI::SQL_INTEGER); # should be
parameter 2
$sth-bind_param ( 4, 'Some name',DBI::SQL_VARCHAR);
#description should be 'name' parameter 3
$sth-bind_param ( 5, 'Some desc',DBI::SQL_VARCHAR);
#description should be 'url' should be parameter 4
$sth-bind_param ( 6, 'Some url', DBI::SQL_VARCHAR);
#description should be 'url' should be parameter 5
$sth-bind_param ( 7, undef, DBI::SQL_TIMESTAMP); #description
should be 'last_fetch parameter 6
$sth-bind_param ( 8, 3600, DBI::SQL_INTEGER); #should
be parameter 7
$sth-bind_param ( 9, undef, DBI::SQL_TIMESTAMP);
#description should be 'next_fetch' parameter 8
$sth-bind_param (10, undef, DBI::SQL_VARCHAR);
#description should be 'enabled' parameter 9
$sth-bind_param (11, 'Some user',DBI::SQL_VARCHAR); #description
should be creator parameter 10
$sth-bind_param (12, '2010-10-15 20:39:33',DBI::SQL_TIMESTAMP);
#description should be 'create_date' parameter 11
please follow Martins advice
*Mit Freundlichen Gruben*
Martin
__
Verzicht und Vertraulichkeitanmerkung
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung.
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung
fuer den Inhalt uebernehmen.
Martin,
I would have thought that would have errored beforehand if the
parameters were mismatched. The procedure in question is called like this:
$sth = $dbh-prepare('{? = call api_add_feed(?,?,?,?,?,?,?,?,?,?,?)}');
so in reality the current param 1 is an output parameter and the
procedure seems to return a value:
RETURN @retval
I'm spending way too much in Oracle at the moment and cannot remember if
MS SQL Server allows parameters to return a value without declaring it
(as you have to in Oracle) but I'd guess they do or it would not have
compiled.
BTW, Eric, trace level 15 (instead of 4) would have been slightly more
informative.
Martin
Date: Sat, 16 Oct 2010 11:03:08 +0100
From: martin.ev...@easysoft.com
To: ero...@barrack.com
CC: dbi-users@perl.org
Subject: Re: There's an error, but $sth-execute returns -1
On 15/10/2010 22:20, Roode, Eric wrote:
Hello all,
DBI docs says that statement handle method execute() returns undef on
error. I have found a situation where that does not hold. Was hoping
someone could help me out, maybe there's a workaround.
First, I'm running ActiveState Perl 5.10.0 on Windows 7, connecting to a
SQL Server 2000 database on a different machine. I have DBI version
1.613, and DBD::ODBC version 1.24.
In a nutshell, here is what happens. I connect to the database, using
the ODBC driver, and setting RaiseError to 1 and PrintError to 0. I
prepare a statement which calls a procedure. I bind parameters to the
statement (some input, some output, various types), I call execute().
There's a problem with one of the parameters; because of that, the
stored procedure tries to insert a null value into a column that does
not accept nulls. So SQL Server gives an error (Cannot insert the
value NULL into column 'enabled'), and the DBI system stores this
string in errstr. However, the execute() method returns -1, which is
what it would return on success. Also, no error is thrown (I have the
$sth-execute call wrapped in an eval, and $@ is empty afterward).
You can see the definition of the table and the stored procedure at
http://nopaste.gamedev.pl/?id=8272.
The Perl code that demonstrates the problem
Re: There's an error, but $sth-execute returns -1
On 16/10/2010 11:03, Martin J. Evans wrote:
On 15/10/2010 22:20, Roode, Eric wrote:
Hello all,
DBI docs says that statement handle method execute() returns undef on
error. I have found a situation where that does not hold. Was hoping
someone could help me out, maybe there's a workaround.
First, I'm running ActiveState Perl 5.10.0 on Windows 7, connecting to a
SQL Server 2000 database on a different machine. I have DBI version
1.613, and DBD::ODBC version 1.24.
In a nutshell, here is what happens. I connect to the database, using
the ODBC driver, and setting RaiseError to 1 and PrintError to 0. I
prepare a statement which calls a procedure. I bind parameters to the
statement (some input, some output, various types), I call execute().
There's a problem with one of the parameters; because of that, the
stored procedure tries to insert a null value into a column that does
not accept nulls. So SQL Server gives an error (Cannot insert the
value NULL into column 'enabled'), and the DBI system stores this
string in errstr. However, the execute() method returns -1, which is
what it would return on success. Also, no error is thrown (I have the
$sth-execute call wrapped in an eval, and $@ is empty afterward).
You can see the definition of the table and the stored procedure at
http://nopaste.gamedev.pl/?id=8272.
The Perl code that demonstrates the problem is at
http://nopaste.gamedev.pl/?id=8273.
And the DBD trace output is at http://nopaste.gamedev.pl/?id=8274.
I hope someone can shed some light on what's going on here. Should I
check errstr and ignore the return value? Thanks in advance.
Eric
(Tim Bunce, if you see this could you clarify what dbd_st_execute is
supposed to return as I could not find the full details in DBI::DBD.
It would appear DBD::ODBC returns -2 for error, -1 for rowcount not
known and a positive number for rows affected).
The status returned was SQL_SUCCESS_WITH_INFO:
!!dbd_error2(err_rc=1, what=st_execute/SQLExecute,
handles=(3194c98,2b5a848,30d4cd0)
that err_rc=1 which is not an error.
If SQLRowCount returns -1 then dbd_st_execute will return -1, that may
be a bug but really the code should not have got to this point anyway.
The are differences between what DBI documents for the execute method
and what DBD::ODBC returns from the dbd_st_execute and I cannot see
for instance the -2 (for errors) documented in DBI::DBD so I'm not
sure if this is right or wrong (Tim?).
As far as I can see this looks like a bug in your driver (but I'll try
and reproduce here). Surely failing to insert into a column should be
an error not success with info (an example of the latter is say an
insert which worked but truncated your data). What ODBC Driver manager
and driver versions are you using - you can find these from the
Administrative tools, data sources or in windows 7 by searching for
data sources in the control panel top right.
Martin
When I run your code with a different driver to MS SQL Server it works
as I'd expect:
use DBI;
use strict;
use warnings;
my $h = DBI-connect('dbi:ODBC:xx', 'xx', 'xx);
eval {$h-do(q/drop table feeds/);};
eval {$h-do(q/drop procedure api_add_feed/);};
my $table='EOT';
CREATE TABLE feeds
(
id INT IDENTITY PRIMARY KEY,
name VARCHAR(400) NOT NULL,
descr VARCHAR(4000) NOT NULL,
urlVARCHAR(800) NOT NULL,
last_fetch DATETIME,
min_interval INT,
next_fetch DATETIME,
enabledCHAR(1) NOT NULL DEFAULT 'N',
creatorVARCHAR(100),
create_dateDATETIME NOT NULL,
lastup DATETIME NOT NULL,
CONSTRAINT feed_url_uniq UNIQUE (url),
CONSTRAINT feed_name_uniq UNIQUE (name)
)
EOT
$h-do($table);
my $proc='EOT';
CREATE PROCEDURE api_add_feed (@messageVARCHAR(2000) OUTPUT,
@new_id INT OUTPUT,
@name VARCHAR(400),
@descr VARCHAR(4000),
@urlVARCHAR(400),
@last_fetch DATETIME,
@min_interval INT,
@next_fetch DATETIME,
@enabledVARCHAR(1),
@creatorVARCHAR(50),
@create_dateDATETIME
)
AS
DECLARE @proc VARCHAR(50), @err INT, @loc INT, @ctxt VARCHAR(100),
@retval INT
SELECT @proc='add_feed', @err=0, @retval=0, @message='No status yet.'
INSERT INTO feeds
(name, descr, url, last_fetch, min_interval, next_fetch, enabled,
creator, create_date, lastup)
VALUES
(@name, @descr, @url, @last_fetch, @min_interval, @next_fetch,
@enabled,
@creator, @create_date, GETDATE())
SELECT @err=@@ERROR, @loc=1, @ctxt='during insert into [feeds
There's an error, but $sth-execute returns -1
Hello all, DBI docs says that statement handle method execute() returns undef on error. I have found a situation where that does not hold. Was hoping someone could help me out, maybe there's a workaround. First, I'm running ActiveState Perl 5.10.0 on Windows 7, connecting to a SQL Server 2000 database on a different machine. I have DBI version 1.613, and DBD::ODBC version 1.24. In a nutshell, here is what happens. I connect to the database, using the ODBC driver, and setting RaiseError to 1 and PrintError to 0. I prepare a statement which calls a procedure. I bind parameters to the statement (some input, some output, various types), I call execute(). There's a problem with one of the parameters; because of that, the stored procedure tries to insert a null value into a column that does not accept nulls. So SQL Server gives an error (Cannot insert the value NULL into column 'enabled'), and the DBI system stores this string in errstr. However, the execute() method returns -1, which is what it would return on success. Also, no error is thrown (I have the $sth-execute call wrapped in an eval, and $@ is empty afterward). You can see the definition of the table and the stored procedure at http://nopaste.gamedev.pl/?id=8272. The Perl code that demonstrates the problem is at http://nopaste.gamedev.pl/?id=8273. And the DBD trace output is at http://nopaste.gamedev.pl/?id=8274. I hope someone can shed some light on what's going on here. Should I check errstr and ignore the return value? Thanks in advance. Eric
Re: DBD::Pg::st execute failed: function requires at least protocol version 3.0
Yasser Shakoor wrote:
Hi all
I am trying the same code on 2 different machines on one machine it is
running ok and on the other machine it is giving strange kind of error,
i have tried every thing but was not able to sort thois out so i thought
to take help from you people.
#!/usr/bin/perl
#
# This script will produce daily web access statistic reports
#
#
use DBI;
# if today is the first of the month then don't run :
$day = `date +%d`;
if ($day==01) {
exit(0);
} else {
# Connect to database
$conn =
DBI-connect(dbi:Pg:dbname=webstatinfo;host=database.webstar.co.uk,
anila, bdjx14eq);
# test to see if connecting!!
if ( !defined $conn ) {
die Cannot connect to database!\n;
}
print (I have connected to the database webstatinfo\n);
# Obtain all the data from the database and put into a series of arrays
# @domain1 is an array containing all the data for the first domain in
# the postgres database, @domain2 for the second domain etc..
print (GETTING DATA FROM DATABASE\n);
$querystring=(select output_filepath, hostname, logfile from
loginfo);
$result = $conn-prepare($querystring);
if ( !defined $result ) {
die Cannot prepare statement: $DBI::errstr\n;
}
$result-execute;
$i=0;
while ( @row = $result-fetchrow()) {
$this_domain = domain$i;
@$this_domain = @row;
print $this_doma...@$this_domain\n;
$i++;
}
$numweb = $i;
$result-finish;
$conn-disconnect;
The out put on the machine where i am getting the error is as below:
I have connected to the database webstatinfo
GETTING DATA FROM DATABASE
DBD::Pg::st execute failed: function requires at least protocol version
3.0 at ./analogscriptdaily line 34.
DBD::Pg::st fetchrow failed: no statement executing
at ./analogscriptdaily line 37.
There is no problem in connecting to the database the line number on
which error is coming up is:
$result-execute;
i have tried uninstalling the DBI and DBD module and installing it from
cpan but still getting the same error any help in this matter will be
appreciated.
I don't really use postgres but I'd search for function requires at
least protocol version 3.0 which threw up for me:
http://www.mail-archive.com/pgsql-hack...@postgresql.org/msg43720.html
http://archives.postgresql.org/pgsql-hackers/2004-10/msg00133.php
and I'd guess upgrading your postgres client libraries may help
but that is just a guess.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
Re: DBD::Pg::st execute failed: function requires at least protocol version 3.0
Hi Martin
Thanks for your help the code is working with the older version of
clients on the other machine and i have tried
with downgrading and upgrading the postgres DBI and DBD but it didn't
worked.
The same code is working on the other machine with the older DBD and DDI
driver.
I have tried all the things but as i am new with the perl i don't know
exactly what is the problem.
Regards,
Yasser
On Mon, 2010-01-04 at 09:02 +, Martin Evans wrote:
Yasser Shakoor wrote:
Hi all
I am trying the same code on 2 different machines on one machine it is
running ok and on the other machine it is giving strange kind of error,
i have tried every thing but was not able to sort thois out so i thought
to take help from you people.
#!/usr/bin/perl
#
# This script will produce daily web access statistic reports
#
#
use DBI;
# if today is the first of the month then don't run :
$day = `date +%d`;
if ($day==01) {
exit(0);
} else {
# Connect to database
$conn =
DBI-connect(dbi:Pg:dbname=webstatinfo;host=database.webstar.co.uk,
anila, bdjx14eq);
# test to see if connecting!!
if ( !defined $conn ) {
die Cannot connect to database!\n;
}
print (I have connected to the database webstatinfo\n);
# Obtain all the data from the database and put into a series of arrays
# @domain1 is an array containing all the data for the first domain in
# the postgres database, @domain2 for the second domain etc..
print (GETTING DATA FROM DATABASE\n);
$querystring=(select output_filepath, hostname, logfile from
loginfo);
$result = $conn-prepare($querystring);
if ( !defined $result ) {
die Cannot prepare statement: $DBI::errstr\n;
}
$result-execute;
$i=0;
while ( @row = $result-fetchrow()) {
$this_domain = domain$i;
@$this_domain = @row;
print $this_doma...@$this_domain\n;
$i++;
}
$numweb = $i;
$result-finish;
$conn-disconnect;
The out put on the machine where i am getting the error is as below:
I have connected to the database webstatinfo
GETTING DATA FROM DATABASE
DBD::Pg::st execute failed: function requires at least protocol version
3.0 at ./analogscriptdaily line 34.
DBD::Pg::st fetchrow failed: no statement executing
at ./analogscriptdaily line 37.
There is no problem in connecting to the database the line number on
which error is coming up is:
$result-execute;
i have tried uninstalling the DBI and DBD module and installing it from
cpan but still getting the same error any help in this matter will be
appreciated.
I don't really use postgres but I'd search for function requires at
least protocol version 3.0 which threw up for me:
http://www.mail-archive.com/pgsql-hack...@postgresql.org/msg43720.html
http://archives.postgresql.org/pgsql-hackers/2004-10/msg00133.php
and I'd guess upgrading your postgres client libraries may help
but that is just a guess.
Martin
Re: DBD::Pg::st execute failed: function requires at least protocol version 3.0
Yasser Shakoor wrote:
Hi Martin
Thanks for your help the code is working with the older version of
clients on the other machine and i have tried
with downgrading and upgrading the postgres DBI and DBD but it didn't
worked.
The same code is working on the other machine with the older DBD and DDI
driver.
I have tried all the things but as i am new with the perl i don't know
exactly what is the problem.
Regards,
Yasser
As I said, I don't use postgres much but I still cannot tell from your
response if you have compared the versions of the postgres client
library - libpq.so (I think). I believe the change to output the error
you are seeing is in that library although I've not checked myself.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
On Mon, 2010-01-04 at 09:02 +, Martin Evans wrote:
Yasser Shakoor wrote:
Hi all
I am trying the same code on 2 different machines on one machine it is
running ok and on the other machine it is giving strange kind of error,
i have tried every thing but was not able to sort thois out so i thought
to take help from you people.
#!/usr/bin/perl
#
# This script will produce daily web access statistic reports
#
#
use DBI;
# if today is the first of the month then don't run :
$day = `date +%d`;
if ($day==01) {
exit(0);
} else {
# Connect to database
$conn =
DBI-connect(dbi:Pg:dbname=webstatinfo;host=database.webstar.co.uk,
anila, bdjx14eq);
# test to see if connecting!!
if ( !defined $conn ) {
die Cannot connect to database!\n;
}
print (I have connected to the database webstatinfo\n);
# Obtain all the data from the database and put into a series of arrays
# @domain1 is an array containing all the data for the first domain in
# the postgres database, @domain2 for the second domain etc..
print (GETTING DATA FROM DATABASE\n);
$querystring=(select output_filepath, hostname, logfile from
loginfo);
$result = $conn-prepare($querystring);
if ( !defined $result ) {
die Cannot prepare statement: $DBI::errstr\n;
}
$result-execute;
$i=0;
while ( @row = $result-fetchrow()) {
$this_domain = domain$i;
@$this_domain = @row;
print $this_doma...@$this_domain\n;
$i++;
}
$numweb = $i;
$result-finish;
$conn-disconnect;
The out put on the machine where i am getting the error is as below:
I have connected to the database webstatinfo
GETTING DATA FROM DATABASE
DBD::Pg::st execute failed: function requires at least protocol version
3.0 at ./analogscriptdaily line 34.
DBD::Pg::st fetchrow failed: no statement executing
at ./analogscriptdaily line 37.
There is no problem in connecting to the database the line number on
which error is coming up is:
$result-execute;
i have tried uninstalling the DBI and DBD module and installing it from
cpan but still getting the same error any help in this matter will be
appreciated.
I don't really use postgres but I'd search for function requires at
least protocol version 3.0 which threw up for me:
http://www.mail-archive.com/pgsql-hack...@postgresql.org/msg43720.html
http://archives.postgresql.org/pgsql-hackers/2004-10/msg00133.php
and I'd guess upgrading your postgres client libraries may help
but that is just a guess.
Martin
DBD::Pg::st execute failed: function requires at least protocol version 3.0
Hi all
I am trying the same code on 2 different machines on one machine it is
running ok and on the other machine it is giving strange kind of error,
i have tried every thing but was not able to sort thois out so i thought
to take help from you people.
#!/usr/bin/perl
#
# This script will produce daily web access statistic reports
#
#
use DBI;
# if today is the first of the month then don't run :
$day = `date +%d`;
if ($day==01) {
exit(0);
} else {
# Connect to database
$conn =
DBI-connect(dbi:Pg:dbname=webstatinfo;host=database.webstar.co.uk,
anila, bdjx14eq);
# test to see if connecting!!
if ( !defined $conn ) {
die Cannot connect to database!\n;
}
print (I have connected to the database webstatinfo\n);
# Obtain all the data from the database and put into a series of arrays
# @domain1 is an array containing all the data for the first domain in
# the postgres database, @domain2 for the second domain etc..
print (GETTING DATA FROM DATABASE\n);
$querystring=(select output_filepath, hostname, logfile from
loginfo);
$result = $conn-prepare($querystring);
if ( !defined $result ) {
die Cannot prepare statement: $DBI::errstr\n;
}
$result-execute;
$i=0;
while ( @row = $result-fetchrow()) {
$this_domain = domain$i;
@$this_domain = @row;
print $this_doma...@$this_domain\n;
$i++;
}
$numweb = $i;
$result-finish;
$conn-disconnect;
The out put on the machine where i am getting the error is as below:
I have connected to the database webstatinfo
GETTING DATA FROM DATABASE
DBD::Pg::st execute failed: function requires at least protocol version
3.0 at ./analogscriptdaily line 34.
DBD::Pg::st fetchrow failed: no statement executing
at ./analogscriptdaily line 37.
There is no problem in connecting to the database the line number on
which error is coming up is:
$result-execute;
i have tried uninstalling the DBI and DBD module and installing it from
cpan but still getting the same error any help in this matter will be
appreciated.
Re: Specify an array in $sth-execute() ?
John Scoles wrote: Depends on the DBD driver. I'm not sure that is true John. I believe execute_array is implemented in DBI for drivers which don't have their own execute_array. It may not be as fast but it is there. Which one are you using? cheers Martin Denis BUCHER wrote: Hello, Does someone knows if it's possible to specify an array when executing a prepared statement ? $sth-execute(%array_data) instead of : $sth-execute($array_data[firstname],array_data[lastname]) Thanks a lot for any help ! Denis
Re: Specify an array in $sth-execute() ?
I think you want is the |execute_array| #___top command
however you can not use it like this.
It would be more like this
SQL='Insert into aTable (Y,X) values (?,?)
my $tuples = $sth-execute_array( { ArrayTupleStatus = \my
@tuple_status }, \...@y_values, \...@x_values );
In your case below you want to bind a single parameter to an array.
You might have to use |bind_param_array| #___top for that
cheers
Denis BUCHER wrote:
Tim Bunce a écrit :
Does someone knows if it's possible to specify an array when executing a
prepared statement ?
The question seems confused...
Yes was difficult to explain ;-)
$sth-execute(%array_data)
%array_data isn't an array, it's a hash.
See http://perldoc.perl.org/perldata.html
Yes absolutely, associative array i.e. hash.
I didn't developped in perl for a while...
instead of :
$sth-execute($array_data[firstname],array_data[lastname])
Square brackets are used to access array elements, but in that case the
value in the square brackets must be an integer.
Oh yes sorry...
It looks like you're trying to use a hash. For that you'd use curly
braces, like this:
$sth-execute( $hash_data{firstname}, $hash_data{lastname} );
Using 'hash slice' syntax (http://perldoc.perl.org/perldata.html#Slices)
you can shorten to:
$sth-execute( @hash_data{firstname, lastname} );
Ok I didn't know hash slice syntax, that's very interesting...
What I want is to get the whole hash, therefore maybe what I want to do
is this ?
$sth-execute( @hash_data );
In fact I should explain what I am trying to do :
#SELECT FROM database 1 and INSERT INTO database 2...
while (@row = $sth_source-fetchrow_array())
{
@array_data = @row;
# INSERT
$sth_pgsql-execute(@array_data) or die ;
}
What do you think ?
Denis
Re: Specify an array in $sth-execute() ?
Hello,
If I understand correctlyy execute_array is to do many INSERT at once
AND to contain all colums ? It seem it could could what I want, then...
Example from http://search.cpan.org/~timb/DBI/DBI.pm#bind_param_array
$dbh-{RaiseError} = 1;# save having to check each method call
$sth = $dbh-prepare(INSERT INTO staff (first_name, last_name, dept)
VALUES(?, ?, ?));
$sth-bind_param_array(1, [ 'John', 'Mary', 'Tim' ]);
$sth-bind_param_array(2, [ 'Booth', 'Todd', 'Robinson' ]);
$sth-bind_param_array(3, SALES); # scalar will be reused for each row
$sth-execute_array( { ArrayTupleStatus = \my @tuple_status } );
Looks interesting but I think I found a better solution using my code...
See next email
Denis
John Scoles a écrit :
I think you want is the |execute_array| #___top command
however you can not use it like this.
It would be more like this
SQL='Insert into aTable (Y,X) values (?,?)
my $tuples = $sth-execute_array( { ArrayTupleStatus = \my
@tuple_status }, \...@y_values, \...@x_values );
In your case below you want to bind a single parameter to an array.
You might have to use |bind_param_array| #___top for that
cheers
Denis BUCHER wrote:
Tim Bunce a écrit :
Does someone knows if it's possible to specify an array when
executing a
prepared statement ?
The question seems confused...
Yes was difficult to explain ;-)
$sth-execute(%array_data)
%array_data isn't an array, it's a hash.
See http://perldoc.perl.org/perldata.html
Yes absolutely, associative array i.e. hash.
I didn't developped in perl for a while...
instead of :
$sth-execute($array_data[firstname],array_data[lastname])
Square brackets are used to access array elements, but in that case the
value in the square brackets must be an integer.
Oh yes sorry...
It looks like you're trying to use a hash. For that you'd use curly
braces, like this:
$sth-execute( $hash_data{firstname}, $hash_data{lastname} );
Using 'hash slice' syntax (http://perldoc.perl.org/perldata.html#Slices)
you can shorten to:
$sth-execute( @hash_data{firstname, lastname} );
Ok I didn't know hash slice syntax, that's very interesting...
What I want is to get the whole hash, therefore maybe what I want to do
is this ?
$sth-execute( @hash_data );
In fact I should explain what I am trying to do :
#SELECT FROM database 1 and INSERT INTO database 2...
while (@row = $sth_source-fetchrow_array())
{
@array_data = @row;
# INSERT
$sth_pgsql-execute(@array_data) or die ;
}
What do you think ?
Denis
Denis Bucher
--
Denis Bucher Horus Digital Solutions sàrl Each problem has a solution
___
Tél. +41-22-8000625 Fax: +41-22-8000622 www.hsolutions.ch
[SOLVED] Re: Specify an array in $sth-execute() ?
Hello everyone,
Thank to the help of everyone on the list, more especially the links you
gave me, I found the solution. Having not done perl for a long time, I
was doing the right logic but with the wrong notation.
This is my final working code :
$dbh_as400 = DBI-connect... (source)
$dbh_pgsql = DBI-connect... (dest)
$sth_as400 = $dbh_as400-prepare...
$sth_as400-execute...
do {
my @row;
my @array_data;
my $sth_pgsql = $dbh_pgsql-prepare...
# fetch each row in array
while (@row = $sth_as400-fetchrow_array())
{
@array_data = @row;
$sth_pgsql-execute(@array_data)...
}
} while ($sth_as400-{odbc_more_results});
This code is working !!!
I had only to change 3 times the '@' character : @array_data = @row; and
$sth_pgsql-execute(@array_data)...
Denis
Denis BUCHER a écrit :
Hello,
If I understand correctlyy execute_array is to do many INSERT at once
AND to contain all colums ? It seem it could could what I want, then...
Example from http://search.cpan.org/~timb/DBI/DBI.pm#bind_param_array
$dbh-{RaiseError} = 1;# save having to check each method call
$sth = $dbh-prepare(INSERT INTO staff (first_name, last_name, dept)
VALUES(?, ?, ?));
$sth-bind_param_array(1, [ 'John', 'Mary', 'Tim' ]);
$sth-bind_param_array(2, [ 'Booth', 'Todd', 'Robinson' ]);
$sth-bind_param_array(3, SALES); # scalar will be reused for each row
$sth-execute_array( { ArrayTupleStatus = \my @tuple_status } );
Looks interesting but I think I found a better solution using my code...
See next email
Denis
John Scoles a écrit :
I think you want is the |execute_array| #___top command
however you can not use it like this.
It would be more like this
SQL='Insert into aTable (Y,X) values (?,?)
my $tuples = $sth-execute_array( { ArrayTupleStatus = \my
@tuple_status }, \...@y_values, \...@x_values );
In your case below you want to bind a single parameter to an array.
You might have to use |bind_param_array| #___top for that
cheers
Denis BUCHER wrote:
Tim Bunce a écrit :
Does someone knows if it's possible to specify an array when
executing a
prepared statement ?
The question seems confused...
Yes was difficult to explain ;-)
$sth-execute(%array_data)
%array_data isn't an array, it's a hash.
See http://perldoc.perl.org/perldata.html
Yes absolutely, associative array i.e. hash.
I didn't developped in perl for a while...
instead of :
$sth-execute($array_data[firstname],array_data[lastname])
Square brackets are used to access array elements, but in that case the
value in the square brackets must be an integer.
Oh yes sorry...
It looks like you're trying to use a hash. For that you'd use curly
braces, like this:
$sth-execute( $hash_data{firstname}, $hash_data{lastname} );
Using 'hash slice' syntax (http://perldoc.perl.org/perldata.html#Slices)
you can shorten to:
$sth-execute( @hash_data{firstname, lastname} );
Ok I didn't know hash slice syntax, that's very interesting...
What I want is to get the whole hash, therefore maybe what I want to do
is this ?
$sth-execute( @hash_data );
In fact I should explain what I am trying to do :
#SELECT FROM database 1 and INSERT INTO database 2...
while (@row = $sth_source-fetchrow_array())
{
@array_data = @row;
# INSERT
$sth_pgsql-execute(@array_data) or die ;
}
What do you think ?
Re: Specify an array in $sth-execute() ?
Depends on the DBD driver. Which one are you using? cheers Denis BUCHER wrote: Hello, Does someone knows if it's possible to specify an array when executing a prepared statement ? $sth-execute(%array_data) instead of : $sth-execute($array_data[firstname],array_data[lastname]) Thanks a lot for any help ! Denis
Re: Specify an array in $sth-execute() ?
On Wed, Aug 05, 2009 at 08:19:42PM +0200, Denis BUCHER wrote:
Hello,
Does someone knows if it's possible to specify an array when executing a
prepared statement ?
The question seems confused...
$sth-execute(%array_data)
%array_data isn't an array, it's a hash.
See http://perldoc.perl.org/perldata.html
instead of :
$sth-execute($array_data[firstname],array_data[lastname])
Square brackets are used to access array elements, but in that case the
value in the square brackets must be an integer.
It looks like you're trying to use a hash. For that you'd use curly
braces, like this:
$sth-execute( $hash_data{firstname}, $hash_data{lastname} );
Using 'hash slice' syntax (http://perldoc.perl.org/perldata.html#Slices)
you can shorten to:
$sth-execute( @hash_data{firstname, lastname} );
Tim.
Re: Specify an array in $sth-execute() ?
I'm using postgresql (Pg) Denis John Scoles a écrit : Depends on the DBD driver. Which one are you using? cheers Denis BUCHER wrote: Hello, Does someone knows if it's possible to specify an array when executing a prepared statement ? $sth-execute(%array_data) instead of : $sth-execute($array_data[firstname],array_data[lastname]) Thanks a lot for any help ! Denis
Re: $DBI::neat_maxlen and execute() and debugging
On Thu, May 07, 2009 at 10:03:41PM -0700, Bill Moseley wrote: Hi Tim, On Fri, May 08, 2009 at 12:59:03AM +0100, Tim Bunce wrote: It might be more efficient/effective to use callbacks to enable tracing in just the calls you're interested in. Callbacks are (still) undocumented, but the test file is pretty clear: http://cpansearch.perl.org/src/TIMB/DBI-1.608/t/70callbacks.t I was looking at the callbacks a few days ago. I wasn't clear what all was available, but more importantly I couldn't see if it was possible to also get the execute parameters. I assume it's not possible with the callbacks. Is that correct? The callbacks can not only see the params, they can change them. There are examples in the 70callbacks.t file. Tim.
Re: $DBI::neat_maxlen and execute() and debugging
On Fri, May 08, 2009 at 09:48:42AM +0100, Tim Bunce wrote:
The callbacks can not only see the params, they can change them.
There are examples in the 70callbacks.t file.
I suspect I'm missing something obvious.
Do you mean the section?
my $attr;
eval { $dbh-quote_identifier('foo','bar', $attr) };
is $called{quote_identifier}, 1;
ok $@, 'quote_identifier callback caused fatal error';
is ref $attr, 'HASH', 'param modified by callback - not recommended!';
I'm calling prepare and execute in the script below. What I'm after
is printing out both the statement AND the bind parameters passed to
execute.
When running the script below I see:
callback [FETCH]
callback [STORE]
callback [FETCH]
callback [connected]
callback [STORE]
callback [prepare]
Fetched 1
I initially didn't think I'd see methods called on statement handles,
but if I can see what's passed to execute that would be great.
#!/usr/bin/perl
use warnings;
use strict;
use DBI;
use Data::Dumper;
my %callbacks = map {
$_ = sub {
warn callback [$_]\n;
# warn Dumper \...@_;
return;
},
} qw/ prepare prepare_cached execute quote_identifier * /;
my $dbh = DBI-connect( 'dbi:Pg:', '', '',
{
RaiseError = 1,
AutoCommit = 1,
Callbacks = \%callbacks,
},
);
my $sth = $dbh-prepare( 'SELECT * from foo where id = ?' );
$sth-execute( 1 );
my $rows = $sth-rows;
print Fetched $rows\n;
--
Bill Moseley.
mose...@hank.org
Sent from my iMutt
$DBI::neat_maxlen and execute() and debugging
I can set $DBI::neat_maxlen to have DBI trace output show the complete statements, but execute still is truncated. execute(1, 2, ...)= '0E0' at dbi_neat.pl line 91 Is there an equivalent neat setting to show all the bind variables? Also, I want to hook into the trace to be able to selectively trace some statements based on inspecting what caller() returns. Does DBI have any existing hooks for doing this? What I want to see is everything passed to the database. I can see this in the database logs but I want to filter and add extra info based on caller(). Thanks, -- Bill Moseley mose...@hank.org Sent from my iMutt
Re: $DBI::neat_maxlen and execute() and debugging
On Thu, May 07, 2009 at 10:08:52AM -0700, Bill Moseley wrote: I can set $DBI::neat_maxlen to have DBI trace output show the complete statements, but execute still is truncated. execute(1, 2, ...)= '0E0' at dbi_neat.pl line 91 Is there an equivalent neat setting to show all the bind variables? I'd guess you're using trace level 1, so you're only seeing the returns from method calls. Trace level 2 will show the entry to the methods and include all the arguments. (Note that $DBI::neat_maxlen only affects the length of individual strings, not the number of arguments output.) Also, I want to hook into the trace to be able to selectively trace some statements based on inspecting what caller() returns. Does DBI have any existing hooks for doing this? What I want to see is everything passed to the database. I can see this in the database logs but I want to filter and add extra info based on caller(). The trace is written directly to a filehandle. The docs include an example of using PerlIO layers to gain more control: http://search.cpan.org/~timb/DBI/DBI.pm#Tracing_to_Layered_Filehandles It might be more efficient/effective to use callbacks to enable tracing in just the calls you're interested in. Callbacks are (still) undocumented, but the test file is pretty clear: http://cpansearch.perl.org/src/TIMB/DBI-1.608/t/70callbacks.t Tim.
Re: $DBI::neat_maxlen and execute() and debugging
Hi Tim, On Fri, May 08, 2009 at 12:59:03AM +0100, Tim Bunce wrote: It might be more efficient/effective to use callbacks to enable tracing in just the calls you're interested in. Callbacks are (still) undocumented, but the test file is pretty clear: http://cpansearch.perl.org/src/TIMB/DBI-1.608/t/70callbacks.t I was looking at the callbacks a few days ago. I wasn't clear what all was available, but more importantly I couldn't see if it was possible to also get the execute parameters. I assume it's not possible with the callbacks. Is that correct? Thanks, -- Bill Moseley. mose...@hank.org Sent from my iMutt
Solved: Perl goes away during a MySQL execute call, with NO messages.
For the record in the hope that somebody may find this helpful. I finally figured out the problem was simply the wait_timeout value was too small ( 10 seconds). The Problem: Perl would die after a “prepare” call and before execute would return a DBI trace ended with “my_login skip connect”. No error messages were sent to the application or system error log. The rather strange part was that it would stop on different transactions, given different data sets. But usually the same transaction for a given data set. Background: This took weeks to track down, in part because it was running on a hosted system. There was no access to the mysql error log so I don't know if that would have offered a clue. The hosting service claimed they had only upgraded the Perl version and had not touched anything else. The application ran fine on in-house systems so we could not duplicate the problem except on the hosted system. Lessons Learned: In a hosted environment make sure you have a record of the MySQL version, and the system variable settings. The host service can change these at any time, and may not even be aware of it. When a working system stops working, the first thing I would now do is check the working system variables, against the current system variables. P.S. Thanks to Clive for his Red Hat/Perl post. In the middle of all this I switched hosting services to one that uses CentOS, and I can't believe how slow it is. __ Yahoo! Canada Toolbar: Search from anywhere on the web, and bookmark your favourite sites. Download it now at http://ca.toolbar.yahoo.com.
Re: Solved: Perl goes away during a MySQL execute call, with NO messages.
Hi Ian P.S. Thanks to Clive for his Red Hat/Perl post. In the middle of all this I switched hosting services to one that uses CentOS, and I can't believe how slow it is. Hmmm. I realize this is not a CentOS-oriented list, but can you tell us more about this speed issue? I ask because my web hosting company offers CentOS-based VPS. I haven't adopted it yet, and comments such as yours scare me... -- Ron Savage [EMAIL PROTECTED] http://savage.net.au/index.html
Re: Solved: Perl goes away during a MySQL execute call, with NO messages.
Ron, This has been the talk of the Perl world for the past few weeks. Here's a place you can go as a starting point. http://perlbuzz.com/2008/08/red-hats-patch-slows-down-overloading-in-perl.html Cheers, - Richard On Tue, Sep 9, 2008 at 7:03 PM, Ron Savage [EMAIL PROTECTED] wrote: Hi Ian P.S. Thanks to Clive for his Red Hat/Perl post. In the middle of all this I switched hosting services to one that uses CentOS, and I can't believe how slow it is. Hmmm. I realize this is not a CentOS-oriented list, but can you tell us more about this speed issue? I ask because my web hosting company offers CentOS-based VPS. I haven't adopted it yet, and comments such as yours scare me... -- Ron Savage [EMAIL PROTECTED] http://savage.net.au/index.html
Re: Solved: Perl goes away during a MySQL execute call, with NO messages.
Hi Richard On Tue, 2008-09-09 at 19:37 -0400, Richard Dice wrote: Ron, This has been the talk of the Perl world for the past few weeks. Here's a place you can go as a starting point. http://perlbuzz.com/2008/08/red-hats-patch-slows-down-overloading-in-perl.html Ahh. $many x $thanx; -- Ron Savage [EMAIL PROTECTED] http://savage.net.au/index.html
Perl goes away during a MySQL execute call, with no messages
Code runs fine on my home system Windows Perl 5.8 Code was running fine on a hosted system, then the hosting service upgraded the Perl version from 5.3 to 5.7 and it stopped running. We tried version 5.3 and 5.8 - it still did not work on that server. After 3 weeks to trying to isolate the problem we gave up and moved it to another server (that had not been upgraded). Worked fine. They have now upgraded the that system, same problem. I have tried removing huge chunks of code, split processes in two and still get the problem. Execution termination floats around from one execute to another as the data set changes or if I remove code. Here is the DBI trace (the first line is my debug code) CSDB_Sites.pm::getSiteLocation:297: after prepare:SELECT city, state, country from sites where site='http://www.nipissingu.ca/' ; siteQH:DBI::st=HASH(0xa7ac35c) reerse/public_html/cgi-bin/ExtractJob.pm line 280 - prepare for DBD::mysql::db (DBI::db=HASH(0xa6d3aa8)~0xa6d3b8c 'SELECT city, state, country from sites where site='http://www.nipissingu.ca/' ;') thr#9c49008 New 'DBI::st' (for DBD::mysql::st, parent=DBI::db=HASH(0xa6d3b8c), id=undef) dbih_setup_handle(DBI::st=HASH(0xa7ac35c)=DBI::st=HASH(0xa79ac64), DBD::mysql::st, a820834, Null!) dbih_make_com(DBI::db=HASH(0xa6d3b8c), a721018, DBD::mysql::st, 252, 0) thr#9c49008 dbih_setup_attrib(DBI::st=HASH(0xa79ac64), Err, DBI::db=HASH(0xa6d3b8c)) SCALAR(0x9e715ac) (already defined) dbih_setup_attrib(DBI::st=HASH(0xa79ac64), State, DBI::db=HASH(0xa6d3b8c)) SCALAR(0x9e7160c) (already defined) dbih_setup_attrib(DBI::st=HASH(0xa79ac64), Errstr, DBI::db=HASH(0xa6d3b8c)) SCALAR(0x9e715dc) (already defined) dbih_setup_attrib(DBI::st=HASH(0xa79ac64), TraceLevel, DBI::db=HASH(0xa6d3b8c)) 0 (already defined) dbih_setup_attrib(DBI::st=HASH(0xa79ac64), FetchHashKeyName, DBI::db=HASH(0xa6d3b8c)) 'NAME' (already defined) dbih_setup_attrib(DBI::st=HASH(0xa79ac64), HandleSetErr, DBI::db=HASH(0xa6d3b8c)) undef (not defined) dbih_setup_attrib(DBI::st=HASH(0xa79ac64), HandleError, DBI::db=HASH(0xa6d3b8c)) undef (not defined) dbih_setup_attrib(DBI::st=HASH(0xa79ac64), ReadOnly, DBI::db=HASH(0xa6d3b8c)) undef (not defined) dbih_setup_attrib(DBI::st=HASH(0xa79ac64), Profile, DBI::db=HASH(0xa6d3b8c)) undef (not defined) - dbd_st_prepare MYSQL_VERSION_ID 40122, SQL statement: SELECT city, state, country from sites where site='http://www.nipissingu.ca/' ; - dbd_st_free_result_sets - dbd_st_free_result_sets RC -1 - dbd_st_free_result_sets count_params statement SELECT city, state, country from sites where site='http://www.nipissingu.ca/' ; - dbd_st_prepare - prepare= DBI::st=HASH(0xa7ac35c) at /home/careerse/public_html/cgi-bin/CSDB_Sites.pm line 297 via at /home/careerse/public_html/cgi-bin/ExtractJob.pm line 280 execute DISPATCH (DBI::st=HASH(0xa7ac35c) rc1/1 @1 g0 ima1041 pid#17112) at /home/careerse/public_html/cgi-bin/CSDB_Sites.pm line 298 via at /home/careerse/public_html/cgi-bin/ExtractJob.pm line 280 - execute for DBD::mysql::st (DBI::st=HASH(0xa7ac35c)~0xa79ac64) thr#9c49008 - dbd_st_execute for 0a820aec - dbd_st_free_result_sets - dbd_st_free_result_sets RC -1 - dbd_st_free_result_sets mysql_st_internal_execute MYSQL_VERSION_ID 40122 parse_params statement SELECT city, state, country from sites where site='http://www.nipissingu.ca/' ; my_login skip connect Given the same data set it fails in the same place mostly. Different data sets fail on different MySQL queries. Often, as in this case, they are fairly simple selects. They tend to fail near the end of the program. The last line of the trace is always my_login skip connect Perl v 5.8.8 MySQL v 4.1.22-standard Any advice on how I should proceed in figuring this out? Thanks, ian __ Looking for the perfect gift? Give the gift of Flickr! http://www.flickr.com/gift/
avoiding array argument passing to execute
On Tue, June 24, 2008 10:17 pm, Tim_Bunce via RT - [EMAIL PROTECTED] wrote: You should avoid using an array to pass arguments to execute(). Why is that? I use it extensively, is it a problem waiting to happen? John
How to execute a perl script at remote server
Hi All,
I have written a Perl script, which deals with the ftp ing
(transferring) the file to remote server and executing a Perl script
(AUT) at remote m/c.
The script is as below and here I have 2 doubts, so please help me to
resolve this.
1) I am getting the message like could not transfer the file to
server but it is ftp ing the flie to remote m/c, and I can see that
file ( root1.txt ) there.
2) And I want to run the perl script (AUT) at remote m/c depending
on the data we sent, so could you please let me know how can I achieve
this.
Is it possible for me to do system(test.pl), If I am at SFTP mode.
Please see comment line in the code below.
#!/usr/bin/perl -w
use strict;
use warnings;
use Net::SFTP;
my $server=A.BY.C.D;
my $user=roserag;
my $password=[EMAIL PROTECTED];
my %args = (user = $user, password = $password, ssh_args = []);
$args{debug} = 1;
$args{user} = root;
my $file=local.txt;
my $rfile = root1.txt;
my $sftp=Net::SFTP-new($server, %args) or die could not open
connection to $server\n;
$sftp-put($file,$rfile) or die could not transfer the file to server
\n;
# system(test.pl);Is this command work out.
exit;
[EMAIL PROTECTED] Examples]$ perl sftp3.pl
testbrix.wipro.com: Reading configuration data /home/gaurav/.ssh/config
testbrix.wipro.com: Reading configuration data /etc/ssh_config
...
...
testbrix.wipro.com: sftp: In write loop, got 510 offset 0
testbrix.wipro.com: sftp: Sent message T:10 I:2
testbrix.wipro.com: sftp: Sent message T:4 I:3
could not transfer the file to server
But I can see the file the file at server.
1,1 All
Regards,
Mustafa
Please do not print this email unless it is absolutely necessary.
The information contained in this electronic message and any attachments to
this message are intended for the exclusive use of the addressee(s) and may
contain proprietary, confidential or privileged information. If you are not the
intended recipient, you should not disseminate, distribute or copy this e-mail.
Please notify the sender immediately and destroy all copies of this message and
any attachments.
WARNING: Computer viruses can be transmitted via email. The recipient should
check this email and any attachments for the presence of viruses. The company
accepts no liability for any damage caused by any virus transmitted by this
email.
www.wipro.com
RE: How to execute a perl script at remote server
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 28, 2008 8:04 AM To: dbi-users@perl.org Subject: How to execute a perl script at remote server Hi All, You would be better served to search the Perlmonks list http://perlmonks.org/ They have all kinds of slick tricks for this kind of stuff. -- Stephen Carville [EMAIL PROTECTED] Systems Engineer Land America 1.626.667.1450 X1326 # That which does not kill us often hurts us a lot.
execute() failing with: Not an error!
After hours of googling and testing I'm down to you guys as my last
resort. I have an issue that results in execute() failing with the
error:
not an error(21) at dbdimp.c line 376
The test script below brings out the problem, everywhere except my
development environment. I get the same problem if I
use prepare() instead of prepare_cached().
Would really appreciate it if someone can duplicate this, especially
against a target other than DBD::SQLite (that's all I'm able to try at
the moment). Any ideas before I start submitting bugs to RT?
Cheers,
Mark.
1. Debian GNU/Linux (i686), DBI v1.53, Perl v5.8.8, DBD::SQLite v1.14
=
1..3
ok 1 - insert id 1
DBD::SQLite::st execute failed: PRIMARY KEY must be unique(19) at dbdimp.c line
403 at x.pl line 32.
ok 2 - insert duplicate id 1
ok 3 - insert id 2
closing dbh with active statement handles at x.pl line 46.
2. Windows XP, DBI v1.601, Perl v5.10.0 (Strawberry), DBD::SQLite v1.14
===
1..3
ok 1 - insert id 1
DBD::SQLite::st execute failed: PRIMARY KEY must be unique(19) at dbdimp.c line
403 at x.pl line 32.
ok 2 - insert duplicate id 1
DBD::SQLite::st execute failed: not an error(21) at dbdimp.c line 376 at x.pl
line 40.
not ok 3 - insert id 2
# Failed test 'insert id 2'
# at x.pl line 41.
# Looks like you failed 1 test of 3.
3. The Test Case
#!/usr/bin/perl
BEGIN {$ENV{DBI_TRACE} = 0;}
use strict;
use warnings;
use Test::More tests = 3;
use DBI;
my $dbh;
my $sth;
my $res;
unlink('test.db');
$dbh = DBI-connect('dbi:SQLite:test.db');
$dbh-{RaiseError} = 0;
$dbh-do('
CREATE TABLE artists (
id INTEGER,
name VARCHAR(32),
PRIMARY KEY(id)
)
');
$sth = $dbh-prepare_cached('INSERT INTO artists(id,name) VALUES(?,?)');
$res = $sth-execute(1,'one');
ok($res, 'insert id 1');
$sth-finish;
$dbh-begin_work;
$sth = $dbh-prepare_cached('INSERT INTO artists(id,name) VALUES(?,?)');
$res = $sth-execute(1,'one');
ok(!$res, 'insert duplicate id 1');
$dbh-commit if($res);
$dbh-rollback if(!$res);
$sth-finish;
$dbh-begin_work;
$sth = $dbh-prepare_cached('INSERT INTO artists(id,name) VALUES(?,?)');
$res = $sth-execute(2, 'two'); # = This fails: not an error
ok($res, 'insert id 2');
$dbh-commit if($res);
$dbh-rollback if(!$res);
$sth-finish;
$dbh-disconnect;
exit;
--
Mark Lawrence
Re: execute() failing with: Not an error!
On Wed Apr 02, 2008 at 11:55:53AM +0200, Mark Lawrence wrote:
$dbh-begin_work;
$sth = $dbh-prepare_cached('INSERT INTO artists(id,name) VALUES(?,?)');
$res = $sth-execute(1,'one');
ok(!$res, 'insert duplicate id 1');
$dbh-commit if($res);
$dbh-rollback if(!$res);
$sth-finish;
Yes, I also know that the transaction stuff is not necessary here, but I
was using transactions in my code and just duplicated here. The failure
also occurs with autocommit...
Mark.
--
Mark Lawrence
Re: execute() failing with: Not an error!
On Wed, Apr 02, 2008 at 11:55:53AM +0200, Mark Lawrence wrote:
After hours of googling and testing I'm down to you guys as my last
resort. I have an issue that results in execute() failing with the
error:
not an error(21) at dbdimp.c line 376
The test script below brings out the problem, everywhere except my
development environment. I get the same problem if I
use prepare() instead of prepare_cached().
Would really appreciate it if someone can duplicate this, especially
against a target other than DBD::SQLite (that's all I'm able to try at
the moment). Any ideas before I start submitting bugs to RT?
I believe it's an error from DBD::SQLite
Google http://www.google.com/search?q=sqlite+error+%2B21 suggests
http://www.sqlite.org/cvstrac/wiki?p=LibraryRoutineCalledOutOfSequence
Tim.
Cheers,
Mark.
1. Debian GNU/Linux (i686), DBI v1.53, Perl v5.8.8, DBD::SQLite v1.14
=
1..3
ok 1 - insert id 1
DBD::SQLite::st execute failed: PRIMARY KEY must be unique(19) at dbdimp.c
line 403 at x.pl line 32.
ok 2 - insert duplicate id 1
ok 3 - insert id 2
closing dbh with active statement handles at x.pl line 46.
2. Windows XP, DBI v1.601, Perl v5.10.0 (Strawberry), DBD::SQLite v1.14
===
1..3
ok 1 - insert id 1
DBD::SQLite::st execute failed: PRIMARY KEY must be unique(19) at dbdimp.c
line 403 at x.pl line 32.
ok 2 - insert duplicate id 1
DBD::SQLite::st execute failed: not an error(21) at dbdimp.c line 376 at x.pl
line 40.
not ok 3 - insert id 2
# Failed test 'insert id 2'
# at x.pl line 41.
# Looks like you failed 1 test of 3.
3. The Test Case
#!/usr/bin/perl
BEGIN {$ENV{DBI_TRACE} = 0;}
use strict;
use warnings;
use Test::More tests = 3;
use DBI;
my $dbh;
my $sth;
my $res;
unlink('test.db');
$dbh = DBI-connect('dbi:SQLite:test.db');
$dbh-{RaiseError} = 0;
$dbh-do('
CREATE TABLE artists (
id INTEGER,
name VARCHAR(32),
PRIMARY KEY(id)
)
');
$sth = $dbh-prepare_cached('INSERT INTO artists(id,name) VALUES(?,?)');
$res = $sth-execute(1,'one');
ok($res, 'insert id 1');
$sth-finish;
$dbh-begin_work;
$sth = $dbh-prepare_cached('INSERT INTO artists(id,name) VALUES(?,?)');
$res = $sth-execute(1,'one');
ok(!$res, 'insert duplicate id 1');
$dbh-commit if($res);
$dbh-rollback if(!$res);
$sth-finish;
$dbh-begin_work;
$sth = $dbh-prepare_cached('INSERT INTO artists(id,name) VALUES(?,?)');
$res = $sth-execute(2, 'two'); # = This fails: not an error
ok($res, 'insert id 2');
$dbh-commit if($res);
$dbh-rollback if(!$res);
$sth-finish;
$dbh-disconnect;
exit;
--
Mark Lawrence
Re: execute() failing with: Not an error!
On Wed Apr 02, 2008 at 04:53:36PM +0100, Tim Bunce wrote: On Wed, Apr 02, 2008 at 11:55:53AM +0200, Mark Lawrence wrote: After hours of googling and testing I'm down to you guys as my last resort. I have an issue that results in execute() failing with the error: not an error(21) at dbdimp.c line 376 The test script below brings out the problem, everywhere except my development environment. I get the same problem if I use prepare() instead of prepare_cached(). Would really appreciate it if someone can duplicate this, especially against a target other than DBD::SQLite (that's all I'm able to try at the moment). Any ideas before I start submitting bugs to RT? I believe it's an error from DBD::SQLite Google http://www.google.com/search?q=sqlite+error+%2B21 suggests http://www.sqlite.org/cvstrac/wiki?p=LibraryRoutineCalledOutOfSequence And/or maybe http://rt.cpan.org/Ticket/Display.html?id=9792 and http://rt.cpan.org/Ticket/Display.html?id=30558. With the one-line patch to DBD::SQLite that's in ticket 30558 things work again for me. I guess Debian had patched this long ago so I didn't see the failure until the CPAN testers started sending in reports. What an extremely useful service to module authors... Although until Matt releases a new version I'm kinda stuck. Can't go round patching everybodys machines. Mark. -- Mark Lawrence
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)
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
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)
[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
Re: Extremely poor execute performance
Andy Baumhauer wrote:
Martin,
Thank you so much for the spot on reply. The work you saved with that
reply is greatly appreciated.
Glad to hear it. resmg:wait quantum caught me some time ago when a
complicated query was taking over 8s every now and again and yet running
it outside of the application in sqlplus took less than .1s. It must now
be one of the most optimised queries we have as we spent some time on it
before we realised the remgr was adding an 8s wait every now and again
because of the total work the application had done.
Martin
I checked the resource groups with this statement:
COL initial_rsrc_consumer_group FORMAT A24 HEADING
'Resource|Consumer|Group'
COL username FORMAT A12 HEADING 'User Name'
SELECT
initial_rsrc_consumer_group,
username
FROM dba_users
ORDER BY 1,2
;
The user was in the DEFAULT_CONSUMER_GROUP. I issued the following
commands as the sysdba to move the user to the SYS_GROUP, and the
problem went away (I'm the only user of this database, and it exists
for conversion of data.
EXEC
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('SOMEORACLEUSER',
'SYS_GROUP', TRUE);
EXEC
DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP('SOMEORACLEUSER',
'SYS_GROUP');
I hope this helps someone else in the future.
Andy Baumhauer
Re: Extremely poor execute performance
Andy Baumhauer wrote: Running DBI 1.58 and DBD::Oracle 1.19 with Oracle 10g (10.2) on Fedora 7 64 bit OS: Copying a table from one tablespace to another tablespace (two $dbh handles). The table has 56 fields. I've simplified the process to: fetch row from first dbh execute row to second dbh commit every 100 rows The table contains 1.9 million rows -- I copied 20,000 here and used DBI::Profile to get these results: DBI::Profile: 130.809944s 93.63% (40001 calls) test_tie.pl @ 2007-09-14 15:18:40 'DESTROY' = 'DBD::Oracle::st::DESTROY' = 0.75s 'err' = 'DBD::Oracle::st::err' = 0.026046s / 1 = 0.01s avg (first 0.01s, min 0.00s, max 0.21s) 'DBD::_::common::err' = 0.10s 'execute' = 'DBD::Oracle::st::execute' = 130.783813s / 2 = 0.006539s avg (first 0.004013s, min 0.000390s, max 29.99s) Basically, most executes finish in milliseconds, but randomly (different every run), the execute holds for 30 seconds, almost dead on. I can't figure out why, but the the delay when it halts (doesn't map up with the commits either) looks like some kind of timer. I haven't noticed this problem on tables with fewer columns. Any ideas? Andy What resource manager group is your user in? Long waits for apparently no reason (in the middle of a lot of work) are often the resource manager doing quantum wait. If you run apex (the web app) you can often see the resmgr:wait quantum events with a time under the monitor-wait page (otherwise you'll need to look up the query do select the waits yourself). There are literally hundreds of other waits too but I'd guess you are hitting a quantum wait. Martin
Re: Extremely poor execute performance
Martin,
Thank you so much for the spot on reply. The work you saved with that
reply is greatly appreciated.
I checked the resource groups with this statement:
COL initial_rsrc_consumer_group FORMAT A24 HEADING 'Resource|Consumer|Group'
COL username FORMAT A12 HEADING 'User Name'
SELECT
initial_rsrc_consumer_group,
username
FROM dba_users
ORDER BY 1,2
;
The user was in the DEFAULT_CONSUMER_GROUP. I issued the following
commands as the sysdba to move the user to the SYS_GROUP, and the
problem went away (I'm the only user of this database, and it exists for
conversion of data.
EXEC
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('SOMEORACLEUSER',
'SYS_GROUP', TRUE);
EXEC DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP('SOMEORACLEUSER',
'SYS_GROUP');
I hope this helps someone else in the future.
Andy Baumhauer
Extremely poor execute performance
Running DBI 1.58 and DBD::Oracle 1.19 with Oracle 10g (10.2) on Fedora 7 64 bit OS: Copying a table from one tablespace to another tablespace (two $dbh handles). The table has 56 fields. I've simplified the process to: fetch row from first dbh execute row to second dbh commit every 100 rows The table contains 1.9 million rows -- I copied 20,000 here and used DBI::Profile to get these results: DBI::Profile: 130.809944s 93.63% (40001 calls) test_tie.pl @ 2007-09-14 15:18:40 'DESTROY' = 'DBD::Oracle::st::DESTROY' = 0.75s 'err' = 'DBD::Oracle::st::err' = 0.026046s / 1 = 0.01s avg (first 0.01s, min 0.00s, max 0.21s) 'DBD::_::common::err' = 0.10s 'execute' = 'DBD::Oracle::st::execute' = 130.783813s / 2 = 0.006539s avg (first 0.004013s, min 0.000390s, max 29.99s) Basically, most executes finish in milliseconds, but randomly (different every run), the execute holds for 30 seconds, almost dead on. I can't figure out why, but the the delay when it halts (doesn't map up with the commits either) looks like some kind of timer. I haven't noticed this problem on tables with fewer columns. Any ideas? Andy
Balasan: RE: Balasan: RE: Balasan: RE: Balasan: RE: can't execute use $database
Hi, Garrett.
I am sorry because I reply this respond late. I am
just recovered from long sick. I had to take bed rest.
Well, I thank you so much for your help. Your thread
solved my problem.
Now, my code run as expected. It dumps the data
dictionary of the each database of my MSSQL.
Now I am doing the GUI with Perl/Tk before to the
further steps (user friendly installer, MS Office and
OpenOffice Macro Integration etc).
Could you tell me which mailing-list for Perl/Tk what
still active? I found a Perl/Tk mailing-list in
yahoogroups and joined it. But the mail-list only
content dirty disgusting porno everyday. Meine
Guete
Please refer me to a good Perl/Tk mailing-list. I
wanna be a perlmonk.
--- Garrett, Philip (MAN-Corporate)
[EMAIL PROTECTED] wrote:
DBI::ADO is not a module. The module is DBD::ADO.
But you don't need
to load it -- DBI will load it for you.
use DBI;
# DBI automatically loads ADO
my $dbh = DBI-connect('dbi:ADO:...',...);
-Original Message-
From: Patrix Diradja
[mailto:[EMAIL PROTECTED]
Sent: Thursday, February 08, 2007 10:54 AM
To: dbi-users@perl.org
Subject: Balasan: RE: Balasan: RE: Balasan: RE:
can't execute use
$database
Dear Garrett it still doesn't work.
Here is the error message:
Can't locate DBI/ADO.pm in @INC (@INC contains:
C:\Program
Files\ActiveState Perl Dev Kit 6.0\lib\
C:/Perl/site/lib C:/Perl/lib .)
at cldump.pl line 8.
BEGIN failed--compilation aborted at cldump.pl line
8.
Please tell me furthermore.
here is my code:
#
use strict;
use DBI::ADO;
use Win32::OLE;
use Win32::OLE::Const 'Microsoft ActiveX Data
Objects';
use warnings;
my $uname=sa;
my $pword=penguin;
my $host=127.0.0.1;
my @bd4l=(FinanCore);
#if (@ARGV){
#my @[EMAIL PROTECTED];
#}else{
#my @bd4l=(AprovaApp1);
#}
print I am dumping... @bd4l\n;
foreach my $elemenbd4l(@bd4l){
my $dsn = Provider=SQLNCLI;
. Server=$host;
. Database=$elemenbd4l;;
my $dbh1 = DBI-connect(dbi:ADO:$dsn, $uname,
$pword) or die
$DBI::errstr;
Garrett, Philip (MAN-Corporate)
[EMAIL PROTECTED] wrote:
Patrix Diradja wrote:
Yeah, that's the problem Garrett.
I usually use that way to connect to ms msql.
But now, I want that my perl code can work with
several databases.
So I need somewhat like the Connection String
which I can modify
(forming string) and than just does
$dbh-connect( for me). and
does $dbh-close before doing again connect to
another database ($dbh-connect).
Ok, try this: (found on www.connectionstrings.com)
my $connstr = Provider=SQLNCLI;
. Server=$server;
. Database=$database;;
my $dbh =
DBI-connect(dbi:ADO:$connstr,$user,$pass)
|| die $DBI::errstr;
If you are using SQL Server 2005 Express, you'll
need to add \EXPRESS
after the server name like this:
Server=$server\\EXPRESS;
Philip
-
Kunjungi halaman depan Yahoo! Indonesia yang baru!
Sekarang dengan penyimpanan 1GB
http://id.mail.yahoo.com/
Balasan: RE: Balasan: RE: Balasan: RE: can't execute use $database
Dear Garrett it still doesn't work.
Here is the error message:
Can't locate DBI/ADO.pm in @INC (@INC contains: C:\Program Files\ActiveState
Perl Dev Kit 6.0\lib\ C:/Perl/site/lib C:/Perl/lib .) at cldump.pl line 8.
BEGIN failed--compilation aborted at cldump.pl line 8.
Please tell me furthermore.
here is my code:
#
use strict;
use DBI::ADO;
use Win32::OLE;
use Win32::OLE::Const 'Microsoft ActiveX Data Objects';
use warnings;
my $uname=sa;
my $pword=penguin;
my $host=127.0.0.1;
my @bd4l=(FinanCore);
#if (@ARGV){
#my @[EMAIL PROTECTED];
#}else{
#my @bd4l=(AprovaApp1);
#}
print I am dumping... @bd4l\n;
foreach my $elemenbd4l(@bd4l){
my $dsn = Provider=SQLNCLI;
. Server=$host;
. Database=$elemenbd4l;;
my $dbh1 = DBI-connect(dbi:ADO:$dsn, $uname, $pword) or die $DBI::errstr;
Garrett, Philip (MAN-Corporate) [EMAIL PROTECTED] wrote:
Patrix Diradja wrote:
Yeah, that's the problem Garrett.
I usually use that way to connect to ms msql.
But now, I want that my perl code can work with several databases.
So I need somewhat like the Connection String which I can modify
(forming string) and than just does $dbh-connect( for me). and does
$dbh-close before doing again connect to
another database ($dbh-connect).
Ok, try this: (found on www.connectionstrings.com)
my $connstr = Provider=SQLNCLI;
. Server=$server;
. Database=$database;;
my $dbh = DBI-connect(dbi:ADO:$connstr,$user,$pass)
|| die $DBI::errstr;
If you are using SQL Server 2005 Express, you'll need to add \EXPRESS
after the server name like this:
Server=$server\\EXPRESS;
Philip
-
Kunjungi halaman depan Yahoo! Indonesia yang baru!
RE: Balasan: RE: Balasan: RE: Balasan: RE: can't execute use $database
DBI::ADO is not a module. The module is DBD::ADO. But you don't need
to load it -- DBI will load it for you.
use DBI;
# DBI automatically loads ADO
my $dbh = DBI-connect('dbi:ADO:...',...);
-Original Message-
From: Patrix Diradja [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 08, 2007 10:54 AM
To: dbi-users@perl.org
Subject: Balasan: RE: Balasan: RE: Balasan: RE: can't execute use
$database
Dear Garrett it still doesn't work.
Here is the error message:
Can't locate DBI/ADO.pm in @INC (@INC contains: C:\Program
Files\ActiveState Perl Dev Kit 6.0\lib\ C:/Perl/site/lib C:/Perl/lib .)
at cldump.pl line 8.
BEGIN failed--compilation aborted at cldump.pl line 8.
Please tell me furthermore.
here is my code:
#
use strict;
use DBI::ADO;
use Win32::OLE;
use Win32::OLE::Const 'Microsoft ActiveX Data Objects';
use warnings;
my $uname=sa;
my $pword=penguin;
my $host=127.0.0.1;
my @bd4l=(FinanCore);
#if (@ARGV){
#my @[EMAIL PROTECTED];
#}else{
#my @bd4l=(AprovaApp1);
#}
print I am dumping... @bd4l\n;
foreach my $elemenbd4l(@bd4l){
my $dsn = Provider=SQLNCLI;
. Server=$host;
. Database=$elemenbd4l;;
my $dbh1 = DBI-connect(dbi:ADO:$dsn, $uname, $pword) or die
$DBI::errstr;
Garrett, Philip (MAN-Corporate) [EMAIL PROTECTED] wrote:
Patrix Diradja wrote:
Yeah, that's the problem Garrett.
I usually use that way to connect to ms msql.
But now, I want that my perl code can work with several databases.
So I need somewhat like the Connection String which I can modify
(forming string) and than just does $dbh-connect( for me). and
does $dbh-close before doing again connect to
another database ($dbh-connect).
Ok, try this: (found on www.connectionstrings.com)
my $connstr = Provider=SQLNCLI;
. Server=$server;
. Database=$database;;
my $dbh = DBI-connect(dbi:ADO:$connstr,$user,$pass)
|| die $DBI::errstr;
If you are using SQL Server 2005 Express, you'll need to add \EXPRESS
after the server name like this:
Server=$server\\EXPRESS;
Philip
-
Kunjungi halaman depan Yahoo! Indonesia yang baru!
RE: can't execute use $database
Patrix Diradja wrote: Dear my friends, I wonder why my perl can not execute query use $database. Sorry, I don't know for sure, but my guess is that the use database command is a *client* command, and not valid SQL. Generally speaking, when you want to connect to a specific database, it should be part of your DSN. You would specify the database name in the Windows ODBC manager. Or, if you're using connect strings, you'd specify it there. Your code indicates that, at some point, you will want to connect to several different databases using the same DSN. Rather than trying to force the same $dbh to connect to a different database, why not disconnect and then reconnect to the new database? Regards, Philip
Balasan: RE: can't execute use $database
Dear my friend, Charles.
I Use Perl on Windows Vista, MS SQL Server 2005, DBI, ADO.
I create 'myperl' as DSN from ODBC Administration Tools.
Here is my connection string:
my $dbh1 = DBI-connect(dbi:ADO:database=gua;host=127.0.0.1;port=1433,
$uname, $pword)
|| die Could not open SQL connection.
But why the connection is failed?
Here is the error message:
DBI connect('database=gua;host=127.0.0.1;port=1433','sa',...) failed: Can't
Open Connection 'database=gua;host=127.0.0.1;port=1433'
Please tell me why.
Garrett, Philip (MAN-Corporate) [EMAIL PROTECTED] wrote:
Patrix Diradja wrote:
Dear my friends,
I wonder why my perl can not execute query use $database.
Sorry, I don't know for sure, but my guess is that the use
command is a *client* command, and not valid SQL.
Generally speaking, when you want to connect to a specific database, it
should be part of your DSN. You would specify the database name in the
Windows ODBC manager. Or, if you're using connect strings, you'd
specify it there.
Your code indicates that, at some point, you will want to connect to
several different databases using the same DSN. Rather than trying to
force the same $dbh to connect to a different database, why not
disconnect and then reconnect to the new database?
Regards,
Philip
-
Kunjungi halaman depan Yahoo! Indonesia yang baru!
Balasan: RE: can't execute use $database
Dear my friend, Garret.
I Use Perl on Windows Vista, MS SQL Server 2005, DBI, ADO.
I create 'myperl' as DSN from ODBC Administration Tools.
Here is my connection string:
my $dbh1 = DBI-connect(dbi:ADO:database=gua;host=127.0.0.1;port=1433,
$uname, $pword)
|| die Could not open SQL connection.
But why the connection is failed?
Here is the error message:
DBI connect('database=gua;host=127.0.0.1;port=1433','sa',...) failed: Can't
Open Connection 'database=gua;host=127.0.0.1;port=1433'
Please tell me why.
Garrett, Philip (MAN-Corporate) [EMAIL PROTECTED] wrote:
Patrix Diradja wrote:
Dear my friends,
I wonder why my perl can not execute query use $database.
Sorry, I don't know for sure, but my guess is that the use
command is a *client* command, and not valid SQL.
Generally speaking, when you want to connect to a specific database, it
should be part of your DSN. You would specify the database name in the
Windows ODBC manager. Or, if you're using connect strings, you'd
specify it there.
Your code indicates that, at some point, you will want to connect to
several different databases using the same DSN. Rather than trying to
force the same $dbh to connect to a different database, why not
disconnect and then reconnect to the new database?
Regards,
Philip
-
Sekarang dengan penyimpanan 1GB
http://id.mail.yahoo.com/
