Re: Some progress on Strange selectall_hashref/fetchall_hashref problem with DBD::mysql
I've finally tracked this problem down and I believe it is down to the
code which turns off server_side_prepare if the SQL looks like a
create statement. Of course my statement looks like a create statement
because:
o it is select created_date_time_utc from test
o the code looks for CREATE or create anywhere in the SQL
I fixed it with the bodge on a bodge by ensuring the code looking
for create looks for create (but see below). However, I'd just
point out that it looks to me like
o the code looking for create etc appears to run off the end of the
SQL string whilst attempting to find create, drop etc
because it always looks up to 6 chrs past the current position and
the current position is eventually set to the last chr in the SQL string.
o when MYSQL_VERSION = MULTIPLE_RESULT_SET_VERSION and not doing
server_side_prepare the code uses my_get_fbav() then my_setup_fbav
and the latter appears to do a av = newAV(); then av_store for
each column to extend av. I'm not an XS expert by any means but I
think this would prevent DBI's bind_columns working.
I think it was the combination of the two issues above that caused
the issue I was seeing.
For anyone else using DBD::mysql at the moment I'd seriously avoid
using an columns or tables which contain the strings
create, CREATE, alter, ALTER, drop, DROP, show, SHOW,
call, CALL for now because you are likely to not get your data
back if you bind columns.
I'll happily have a go at a proper patch to fix if someone can explain what
my_get_fbav and my_setup_fbav were written to acheive.
Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com
On 15-Feb-2006 Tim Bunce wrote:
On Tue, Feb 14, 2006 at 06:16:30PM -, Martin J. Evans wrote:
Regarding my posting with a problem using DBI 1.50 and DBD:mysql 3.0002_4
(reprinted below) I wonder if someone who knows DBI and DBD::mysql better
than I can point me in the right direction. I've discovered if I make a
small
change to DBI it works (see commented out lines):
sub fetchall_hashref {
my @row = (undef) x $num_of_fields;
# replace:
# $sth-bind_columns(\(@row));
# while ($sth-fetch) {
# with
while (@row = $sth-fetchrow_array) {
my $ref = $rows;
$ref = $ref-{$row[$_]} ||= {} for @key_indexes;
@[EMAIL PROTECTED] = @row;
}
return $rows;
}
There was a slight error in this posting - I was doing:
selectall_hashred(sql, ['meeting_id', 'race_id'])
Try producing a very small self-contained test case (one that creates and
populates the tables it uses with a small amount of data) so others can
easily investigate.
Tim.
RE: Insights into DBI-connect differences
Capacio, Paula J wrote:
Hello, I'm hoping someone can explain why one of these connect
statements work and the other doesn't. The script sets environment
values as such: $ENV{ORACLE_HOME} = /usr/oracle/product/8.1.7_64;
$ENV{ORACLE_SID} = $db_alias;#$db_alias passed to script via
$ARGV[1]
my $dbh;
Louis wrote:
Paula, print these two variables out before the entry into the logical
test - just to see what they are. Send this back in a reply. Make
this
the case where the else clause should execute, as an example.
print $db_alias =? $ENV{ORACLE_SID}\n;
The code now looks like:
my $dbh;
if ($method =~ /2/) { #this works
$dbh = DBI-connect('', scott/tiger, '', 'Oracle')
or die Connect failed: $DBI::errstr\n;
}else{ #this doesn't
printIt(Just prior to connect...);
printIt($db_alias =? $ENV{ORACLE_SID});
printIt(\$ENV{ORACLE_HOME} to: $ENV{ORACLE_HOME}\n);
$dbh = DBI-connect(dbi:Oracle:$db_alias,'scott','tiger')
or die Connect failed: $DBI::errstr\n;
}
And the new output:
Just prior to connect...
mp3i =? mp3i
$ENV{ORACLE_HOME} to: /usr/oracle/product/8.1.7_64
DBI-connect(mp3i) failed: ORA-12154: TNS:could not resolve service name
(DBD ERROR: OCIServerAttach) at ./OraConn3.pl line 52
Connect failed: ORA-12154: TNS:could not resolve service name (DBD
ERROR: OCIServerAttach)
Does this imply that the first case does not use TNS service names?
The DBI docs state: There is no standard for the text following the
driver name. Each driver is free to use whatever syntax it wants. The
only requirement the DBI makes is that all the information is supplied
in a single string. You must consult the documentation for the drivers
you are using for a description of the syntax they require. The
DBD::Oracle doc doesn't go into detail on the variations and what they
imply. Can anyone provide some insight?
The issue I'm really trying to address is that both connection
variations work on an HP/UX 11.00 server, perl version: 5.006001, DBI
(version 1.20), DBD::Oracle (version 1.12); but on an HP/UX 11.11
server with the same perl and oracle libraries/versions only the first
variation works. I'm trying to determine why and suggest a
resolution.
BTW, I'm a lowly programmer (aka: hacker); I'm not the DBA or the
server sysadmin and the DBA doesn't know perl.
Thanks in advance.
Paula
RE: Insights into DBI-connect differences
Capacio, Paula J [mailto:[EMAIL PROTECTED] wrote:
The code now looks like:
my $dbh;
if ($method =~ /2/) { #this works
$dbh = DBI-connect('', scott/tiger, '', 'Oracle')
or die Connect failed: $DBI::errstr\n;
}else{ #this doesn't
printIt(Just prior to connect...);
printIt($db_alias =? $ENV{ORACLE_SID});
printIt(\$ENV{ORACLE_HOME} to: $ENV{ORACLE_HOME}\n);
$dbh = DBI-connect(dbi:Oracle:$db_alias,'scott','tiger')
or die Connect failed: $DBI::errstr\n;
}
And the new output:
Just prior to connect...
mp3i =? mp3i
$ENV{ORACLE_HOME} to: /usr/oracle/product/8.1.7_64
DBI-connect(mp3i) failed: ORA-12154: TNS:could not resolve service name
(DBD ERROR: OCIServerAttach) at ./OraConn3.pl line 52
Connect failed: ORA-12154: TNS:could not resolve service name (DBD
ERROR: OCIServerAttach)
Have you checked the tnsnames.ora file to make sure mp3i is defined?
Ronald
RE: Insights into DBI-connect differences
Capacio, Paula J [mailto:[EMAIL PROTECTED] wrote:
The code now looks like:
my $dbh;
if ($method =~ /2/) { #this works
$dbh = DBI-connect('', scott/tiger, '', 'Oracle')
or die Connect failed: $DBI::errstr\n;
}else{ #this doesn't
printIt(Just prior to connect...);
printIt($db_alias =? $ENV{ORACLE_SID});
printIt(\$ENV{ORACLE_HOME} to: $ENV{ORACLE_HOME}\n);
$dbh = DBI-connect(dbi:Oracle:$db_alias,'scott','tiger')
or die Connect failed: $DBI::errstr\n;
}
And the new output:
Just prior to connect...
mp3i =? mp3i
$ENV{ORACLE_HOME} to: /usr/oracle/product/8.1.7_64
DBI-connect(mp3i) failed: ORA-12154: TNS:could not resolve service
DBI-name
(DBD ERROR: OCIServerAttach) at ./OraConn3.pl line 52 Connect
failed: ORA-12154: TNS:could not resolve service name (DBD
ERROR: OCIServerAttach)
Ronald J Kimball wrote:
Have you checked the tnsnames.ora file to make sure mp3i is defined?
This is solved: For whatever reason, our tnsnames.ora file is located
in
the /etc directory instead of the /network/admin found via ORACLE_HOME.
Once I added this environment variable it all worked fine:
$ENV{TNS_ADMIN} =/etc;
Thanks all.
