Re: [Poop-group] Re: new emulation experiment - module on module
Darren Duncan wrote: You're half right. I'll try to explain myself better. Part of what I'm offering to the community is a cross-distro code refactoring of sorts, taking redundant or overlapping functionality and combining it into an elegant shared code base whose improvements can be shared by all. The experiment right now is a practical test of whether the code I already have in the form of SQL::Routine is suitable for use by the refactored code, and if not then to determine what functionality it still needs to gain in order to do that (very little I expect). I think it's an interesting idea. It's nice to get a little perspective on the domain of solutions, which for something as popular as SQL, can often become vast and fragmented. -ofer
DBD::Oracle hangs with NLS_LANG and utf8-flagged bind parameter?
hi -
this behavior is not holding us back as its easy to work around, but its
something that should be documented/clarified/fixed, since it can create
extremely hard to track-down hangs. its also possible that something in our
oracle database is configured wrong, which I would appreciate feedback on
since im the application guy here, not the DBA.
we are running DBD::Oracle against Oracle 9i client libraries. The
character encoding of the database is utf8.
When we enable NLS_LANG=AMERICAN_AMERICA.UTF8, DBI begins returning to us
utf8-flagged perl scalars in rowsets. If you then put one of those
returned scalars into a bind parameter for a prepared statement that is
querying a large table, the query slows down to a super-crawl, as though
indexes have been disabled (or in some cases, it produces an oracle
character-set mismatch error). A less-than-a-second query suddenly takes
like three minutes to complete.
the workaround for this is one of:
1. turn off NLS_LANG. but then, youre not using utf8.
2. turn off the utf8 flag on all scalars being sent as bind parameters. this
can be achieved via utf8::encode or also via the Encode module. but then,
youre not using utf8.
3. use a feature that the DBD man page claims is Rarely needed, without
any other explaination for its importance unless you are already an OCI
guru, which is to set ora_csform to SQLCS_IMPLCIT on each utf8 bind
parameter, as in:
my $options = {};
$options-{ora_csform} = SQLCS_IMPLICIT if utf8::is_utf8($value);
$sth-bind_param($key, $value, $options);
it seems like DBD::Oracle should figure this option out automatically, based
on the fact that NLS_LANG is turned on and the utf8 flag on the parameter
is set. and why is it so disastrous the parameters are mismatched ?
- mike
Re: Problem with bind_param using DBD::mysql
Patrick Galbraith wrote:
On Apr 5, 2005, at 3:01 PM, Tim Bunce wrote:
On Tue, Apr 05, 2005 at 11:46:21AM -0700, Patrick Galbraith wrote:
Steve,
There is a version of DBD::mysql (2.9015) that does support
placeholders in the server, via CVS (or I can package it and send it
to you). I would be glad to give you a version of it to try out, and I
think it may deal with your problem better than the current driver
(which emulates it) the new driver uses the database to handle
placeholders. I have tested this code on numerous SQL statements with
placeholders, and it works great.
That's great. But the behavior when placeholders are emulated[1]
still could be easily improved with a one-line code change.
Tim,
I'll fix this ;)
Tim.
[1] I'm presuming that the latest version still lets you use
emulated placeholders either optionally or when talking to old
servers. Is that right?
Yes, if the version of mysql is less than 4.1.3, it doesn't even
compile in server-side-prepare. With a server 4.1.3, one sets
'mysql_server_prepare=1' in the DSN to enable it, and without it, it
defaults to old behaviour.
Is the looks_like_number() issue solved already in the dev version?
I just checked out the Dev-2_9 branch. It took a bit of hacking around
to get it to build on Win32, and even then a load of tests failed, but
when I re-tried the sample program that I posted previously it worked
fine even without mysql_server_prepare=1 in the DSN. trace() shows
that the SQL statement now contains 1 rather than 1.#INF or '1.#INF':
UPDATE foo SET str = 'one', num = 1 WHERE id = 1
Is this what you expected?
Trying again with mysql_server_prepare=1 in the DSN, the program now
says UPDATE affected 25388176 rows (!), but actually didn't change any
rows. This is presumably not what you expected, but may very well be
due to the crude hacking that I did to make it build.
Do you have a Win32 environment that you could try this out on?
If not, here are the issues that I encountered:
- long long and uint are unknown; I used LONGLONG and UINT instead;
- strncasecmp is unknown; I used strncmp instead;
- dbdimp.h line 257 has a syntax error;
- dbdimp.c contains declarations after code at line 2823.
Attached is a patch of the changes that I made.
The test failures were as follows:
C:\Temp\modules\DBD-mysqlnmake test
Microsoft (R) Program Maintenance Utility Version 6.00.8168.0
Copyright (C) Microsoft Corp 1988-1998. All rights reserved.
C:\perl5\bin\perl.exe -MExtUtils::Command::MM -e
test_harness(0, 'b
lib\lib', 'blib\arch') t/*.t
t/00base...ok
t/10dsnlistok
t/20createdrop.ok
t/30insertfetchok
t/35limit..DBD::mysql::st execute failed: You have an error in
your SQL
syntax; check the manual that corresponds to your MySQL server version
for the r
ight syntax to use near ''20', '50'' at line 1 at t/35limit.t line 106.
DBD::mysql::st fetchall_arrayref failed: fetch() without execute() at
t/35limit.
t line 110.
t/35limit..FAILED tests 107-109
Failed 3/113 tests, 97.35% okay
t/35prepareok
t/40bindparam..ok
t/40blobs..ok
t/40listfields.Use of uninitialized value in numeric eq (==) at
t/40listfiel
ds.t line 132.
t/40listfields.ok
t/40nulls..ok
t/40numrowsok
t/50chopblanks.ok
t/50commit.ok
t/60leaks..skipped
all skipped: $ENV{SLOW_TESTS} is not set or Proc::ProcessTable
not insta
lled
t/ak-dbd...ok 12/90DBD::mysql::st execute failed: You have an
error in y
our SQL syntax; check the manual that corresponds to your MySQL server
version f
or the right syntax to use near 'LISTFIELDS testae' at line 1 at
t/ak-dbd.t line
143.
t/ak-dbd...FAILED test 13
Failed 1/90 tests, 98.89% okay
t/akmisc...ok 56/351DBD::mysql::st execute failed: You have an
error in
your SQL syntax; check the manual that corresponds to your MySQL server
version
for the right syntax to use near 'LISTFIELDS testae' at line 1 at
C:\Temp\module
s\DBD-mysql\blib\lib/Mysql.pm line 175.
Can't call method name on an undefined value at t/akmisc.t line 660.
t/akmisc...dubious
Test returned status 2 (wstat 512, 0x200)
DIED. FAILED tests 73-351
Failed 279/351 tests, 20.51% okay
t/dbdadmin.ok
t/insertid.ok
t/mysqlDBD::mysql::st execute failed: Table 'table01'
already exists
at C:\Temp\modules\DBD-mysql\blib\lib/Mysql.pm line 175.
DBD::mysql::st execute failed: You have an error in your SQL syntax;
check the m
anual that corresponds to your MySQL server version for the right syntax
to use
near 'LISTFIELDS TABLE02' at line 1 at
C:\Temp\modules\DBD-mysql\blib\lib/Mysql.
pm line 175.
Can't call method name on an undefined value at t/mysql.t line 502.
t/mysqldubious
Test returned status 2 (wstat 512, 0x200)
DIED. FAILED tests 43-68
Failed 26/68 tests, 61.76% okay
t/mysql2...ok
Failed
Re: odd DBD::Oracle query hang with NLS_LANG and utf8-flagged bind parameter
On Tue, Apr 05, 2005 at 05:47:00PM -0400, mike bayer wrote:
When we enable NLS_LANG=AMERICAN_AMERICA.UTF8, DBI properly begins
returning utf8-flagged perl scalars in rowsets. If you then put one of
those returned scalars into a bind parameter for a prepared statement
that is querying a large table, the query slows down to a super-crawl, as
though indexes have been disabled
You've not said what your database CHAR and NCHAR charsets are.
(Both are relevant.)
(or in some cases, it produces an oracle character-set mismatch error).
A reproducible test case would help - ideally as a patch to an existing test.
3. use a feature that the DBD man page claims is Rarely needed, without
any other explaination for its importance unless you are already an OCI
guru, which is to set ora_csform to SQLCS_IMPLCIT on each utf8 bind
parameter, as in:
my $options = {};
$options-{ora_csform} = SQLCS_IMPLICIT if utf8::is_utf8($value);
$sth-bind_param($key, $value, $options);
it seems like DBD::Oracle should figure this option out automatically,
based on the fact that NLS_LANG is turned on and the utf8 flag on the
parameter is set.
Yes. And if you enable DBD::Oracle tracing (try trace level 5) you can
try to find out if/why it hasn't.
But looking at the code I can see something that's possibly what's
tripping you up. I've hacked the code and attached a patch - but it's
completely untested (may not even compile). Give it a go and let me know.
If it works then please do me and the world a favor by adding some
tests to the test suite to reproduce the original problem (ie the
test should fail without the patch). Thanks.
and why is it so disastrous when the parameters are mismatched ?
I don't know. Perhaps some Oracle expert will make some suggestions,
but it'll probably be guesswork unless you do use Oracle's tracing
facilities to find out what's really going on inside Oracle.
But sorting out automatic SQLCS_IMPLICIT is the first priority.
Tim.
--- dbdimp.c(revision 514)
+++ dbdimp.c(working copy)
@@ -1326,12 +1323,15 @@
csform = phs-csform;
-if (SvUTF8(phs-sv) !csform) {
- /* try to default the csform to avoid translation through non-unicode */
- /* given Oracle policy that NCHAR==Unicode this should be fine */
- csform = SQLCS_NCHAR;
- /* in some cases this isn't right for LOBs but those are rare and */
- /* the application can use an explicit ora_csform bind attribute. */
+if (!csform SvUTF8(phs-sv)) {
+ /* try to default csform to avoid translation through non-unicode */
+ if (CSFORM_IMPLIES_UTF8(SQLCS_IMPLICIT))/* prefer implicit */
+ csform = SQLCS_IMPLICIT;
+ else if (CSFORM_IMPLIES_UTF8(SQLCS_NCHAR))
+ csform = SQLCS_NCHAR;
+ else if (trace_level) /* leave csform == 0 */
+ PerlIO_printf(DBILOGFP,rebinding %s with UTF8 value but
neither CHAR nor NCHAR are unicode\n,
+ phs-name);
}
if (csform) {
Regarding password-change in DBD::Oracle
Hello (Tim?) I read this article http://www.mail-archive.com/dbi-users@perl.org/msg17703.html regarding password change in Oracle using DBD::Oracle. Are there any news about DBD::Oracle supporting this? Right now i use alter user, but would much rather the OCI funcion for it... Kind ragards: Martin Rix Application Developer TDC Forlag A/S FPIU Rødovrevej 241, D174 2610 Rødovre Tlf. 36 36 15 66 Mobil 23 24 03 56 Fax 36 72 88 53 E-mail [EMAIL PROTECTED] Web tdcforlag.dk
Re: Problem with bind_param using DBD::mysql
Steve,
Thanks so much for your patch! I do need to check out windows issues. I
didn't realise that windows doesn't have LONG LONG. If possible, I'd
like to pick your brain about getting a setup to compile on windows.
I'll talk to the dev team about how to deal with unix calls that aren't
implemented for windows. The one particular call, strcasencmp, is
case-insensitive, so it could be LIMIT or limit, which would fail.
Also, you might've found a bug with how I check to see if the
my_ulonglong returned from mysql_affected_rows is -1 (error).
Again,
thank you,
Patrick
On Apr 6, 2005, at 2:41 AM, Steve Hay wrote:
Patrick Galbraith wrote:
On Apr 5, 2005, at 3:01 PM, Tim Bunce wrote:
On Tue, Apr 05, 2005 at 11:46:21AM -0700, Patrick Galbraith wrote:
Steve,
There is a version of DBD::mysql (2.9015) that does support
placeholders in the server, via CVS (or I can package it and send
it
to you). I would be glad to give you a version of it to try out,
and I
think it may deal with your problem better than the current driver
(which emulates it) the new driver uses the database to handle
placeholders. I have tested this code on numerous SQL statements
with
placeholders, and it works great.
That's great. But the behavior when placeholders are emulated[1]
still could be easily improved with a one-line code change.
Tim,
I'll fix this ;)
Tim.
[1] I'm presuming that the latest version still lets you use
emulated placeholders either optionally or when talking to old
servers. Is that right?
Yes, if the version of mysql is less than 4.1.3, it doesn't even
compile in server-side-prepare. With a server 4.1.3, one sets
'mysql_server_prepare=1' in the DSN to enable it, and without it, it
defaults to old behaviour.
Is the looks_like_number() issue solved already in the dev version?
I just checked out the Dev-2_9 branch. It took a bit of hacking around
to get it to build on Win32, and even then a load of tests failed, but
when I re-tried the sample program that I posted previously it worked
fine even without mysql_server_prepare=1 in the DSN. trace() shows
that the SQL statement now contains 1 rather than 1.#INF or '1.#INF':
UPDATE foo SET str = 'one', num = 1 WHERE id = 1
Is this what you expected?
Trying again with mysql_server_prepare=1 in the DSN, the program now
says UPDATE affected 25388176 rows (!), but actually didn't change
any
rows. This is presumably not what you expected, but may very well be
due to the crude hacking that I did to make it build.
Do you have a Win32 environment that you could try this out on?
If not, here are the issues that I encountered:
- long long and uint are unknown; I used LONGLONG and UINT instead;
- strncasecmp is unknown; I used strncmp instead;
- dbdimp.h line 257 has a syntax error;
- dbdimp.c contains declarations after code at line 2823.
Attached is a patch of the changes that I made.
The test failures were as follows:
C:\Temp\modules\DBD-mysqlnmake test
Microsoft (R) Program Maintenance Utility Version 6.00.8168.0
Copyright (C) Microsoft Corp 1988-1998. All rights reserved.
C:\perl5\bin\perl.exe -MExtUtils::Command::MM -e
test_harness(0, 'b
lib\lib', 'blib\arch') t/*.t
t/00base...ok
t/10dsnlistok
t/20createdrop.ok
t/30insertfetchok
t/35limit..DBD::mysql::st execute failed: You have an error in
your SQL
syntax; check the manual that corresponds to your MySQL server version
for the r
ight syntax to use near ''20', '50'' at line 1 at t/35limit.t line 106.
DBD::mysql::st fetchall_arrayref failed: fetch() without execute() at
t/35limit.
t line 110.
t/35limit..FAILED tests 107-109
Failed 3/113 tests, 97.35% okay
t/35prepareok
t/40bindparam..ok
t/40blobs..ok
t/40listfields.Use of uninitialized value in numeric eq (==) at
t/40listfiel
ds.t line 132.
t/40listfields.ok
t/40nulls..ok
t/40numrowsok
t/50chopblanks.ok
t/50commit.ok
t/60leaks..skipped
all skipped: $ENV{SLOW_TESTS} is not set or Proc::ProcessTable
not insta
lled
t/ak-dbd...ok 12/90DBD::mysql::st execute failed: You have an
error in y
our SQL syntax; check the manual that corresponds to your MySQL server
version f
or the right syntax to use near 'LISTFIELDS testae' at line 1 at
t/ak-dbd.t line
143.
t/ak-dbd...FAILED test 13
Failed 1/90 tests, 98.89% okay
t/akmisc...ok 56/351DBD::mysql::st execute failed: You have an
error in
your SQL syntax; check the manual that corresponds to your MySQL server
version
for the right syntax to use near 'LISTFIELDS testae' at line 1 at
C:\Temp\module
s\DBD-mysql\blib\lib/Mysql.pm line 175.
Can't call method name on an undefined value at t/akmisc.t line 660.
t/akmisc...dubious
Test returned status 2 (wstat 512, 0x200)
DIED. FAILED tests 73-351
Failed 279/351 tests, 20.51% okay
t/dbdadmin.ok
t/insertid.ok
t/mysqlDBD::mysql::st execute failed: Table 'table01'
DBD-Pg 1.40 broken?
Folks, Regarding DBD-Pg version 1.40 - I entered this on the CPAN module review today as I have been able to replicate this problem in three situations and reverting back to an earlier module works fine...another user indicated in a separate post that make test fails with undefined obj errors... My code is unremarkable and works with earlier modules as well as on MySQL and Oracle: $query = qq |INSERT INTO sql_login VALUES (?, ?, ?)|; $sth = $dbh-prepare($query); $sth-execute ($log_name, $log_date, $log_time); $sth-finish(); Here is my entry on CPAN...anyone have any info on this problem? Am I being dense or is this a known issue??? TIA, Tom DBD-Pg (1.40) 1 stars The error str (below) appears to indicate a broken quoter() as when I manually quote instead of relying on variable binding I have no such problem. I have two colleagues who have reported the same difficulty with this version of a module that I have used since Edmund Mergl first offered it to the Postgres community. :-( 1.21 works and so I reverted back. As I am not the first person to report a serious bug I am wondering why 1.40 is the most recent patch level on CPAN (???) Here is the stderr from apache (I have no root access and cannot do much more troubleshooting on this box): DBD::Pg::st execute failed: ERROR: column log_date is of type date but expression is of type character varying HINT: You will need to rewrite or cast the expression. Thomas Good - 2005-04-06 08:25:59 - edit --- Thomas Good e-mail: [EMAIL PROTECTED] Senior Database Administratorphone: (+1) 718.818.5528 Residential Services fax: (+1) 718.818.5056 Behavioral Health Services, SVCMC-NY mobile: (+1) 347.524.5631
Re: Regarding password-change in DBD::Oracle
Patches welcome. Tim. On Wed, Apr 06, 2005 at 02:12:16PM +0200, Martin Rix wrote: Hello (Tim?) I read this article http://www.mail-archive.com/dbi-users@perl.org/msg17703.html regarding password change in Oracle using DBD::Oracle. Are there any news about DBD::Oracle supporting this? Right now i use alter user, but would much rather the OCI funcion for it... Kind ragards: Martin Rix Application Developer TDC Forlag A/S FPIU Rødovrevej 241, D174 2610 Rødovre Tlf. 36 36 15 66 Mobil 23 24 03 56 Fax 36 72 88 53 E-mail [EMAIL PROTECTED] Web tdcforlag.dk
Re: odd DBD::Oracle query hang with NLS_LANG and utf8-flagged bind parameter
okwell it seems that the patch works. relevant bits as follows: output of the nchar test: bash-2.05$ perl t/21nchar.t Database and client versions and character sets: Database 9.2.0.5.0 CHAR set is UTF8 (Unicode), NCHAR set is UTF8 (Unicode) Client 9.2.0.6 NLS_LANG is 'AMERICAN_AMERICA.UTF8', NLS_NCHAR is 'unset' when you run the select statement with trace(5), the relevant table column where bound parameter is compared to looks like: col 49: dbtype 1, scale 0, prec 120, nullok 0, name GAME_ID : dbsize 30, char_used 0, char_size 30, csid 871, csform 1, disize 120 fbh 49: 'GAME_ID' NO null , otype 1- 5, dbsize 30/121, p120.s0 run the test script, no patch, with utf8 scalar as bind param and ora_csform not set on bind param: - bind_param for DBD::Oracle::st (DBI::st=HASH(0x31c44)~0x31cd4 ':game_id' asfasdf HASH(0x31c2c)) bind :game_id == asfasdf (type 0, attribs: HASH(0x31c2c)) rebinding :game_id (is-utf8, ftype 1, csid 0, csform 0, inout 0) bind :game_id == asfasdf (size 7/8/0, ptype 4, otype 1) bind :game_id == 'asfasdf' (size 7/7, otype 1, indp 0, at_exec 1) bind :game_id == asfasdf (in, is-utf8, csid 871-0-871, ftype 1, csform 0-2, maxlen 7, maxdata_size 0) - bind_param= 1 at ./testdb2.pl line 36 run the test script, no patch, with utf8 scalar as bind param and ora_csform set to SQLCS_IMPLICIT : - bind_param for DBD::Oracle::st (DBI::st=HASH(0x324bc)~0x3254c ':game_id' asfasdf HASH(0x324a4)) bind :game_id == asfasdf (type 0, attribs: HASH(0x324a4)) rebinding :game_id (is-utf8, ftype 1, csid 0, csform 1, inout 0) bind :game_id == asfasdf (size 7/8/0, ptype 4, otype 1) bind :game_id == 'asfasdf' (size 7/7, otype 1, indp 0, at_exec 1) bind :game_id == asfasdf (in, is-utf8, csid 871-0-871, ftype 1, csform 1-1, maxlen 7, maxdata_size 0) - bind_param= 1 at ./testdb2.pl line 36 (script hangs due to not-yet-understood csform mismatch issue) now compile with the patch. re-run the test script, without the ora_csform set to SQLCS_IMPLICIT: - bind_param for DBD::Oracle::st (DBI::st=HASH(0x315c0)~0x3173c ':game_id' asfasdf HASH(0x315a8)) bind :game_id == asfasdf (type 0, attribs: HASH(0x315a8)) rebinding :game_id (is-utf8, ftype 1, csid 0, csform 0, inout 0) bind :game_id == asfasdf (size 7/8/0, ptype 4, otype 1) bind :game_id == 'asfasdf' (size 7/7, otype 1, indp 0, at_exec 1) bind :game_id == asfasdf (in, is-utf8, csid 871-0-871, ftype 1, csform 0-1, maxlen 7, maxdata_size 0) - bind_param= 1 at ./testdb2.pl line 39 and then the script works. as far as a test case, so far my two test criterion are a. this specific table on this specific database which hangs for unclear reasons, so thats not a good candidate and b. looking in this trace log and seeing csform 0-1. so whats a good way to verify the OCI_ATTR_CHARSET_FORM of a bind variable ? does it affect the data that gets inserted into a column ? - mike
Re: Problem with bind_param using DBD::mysql
On Wed, 6 Apr 2005 09:33:36 -0700, Patrick Galbraith wrote: Hi Folks Thanks so much for your patch! I do need to check out windows issues. I didn't realise that windows doesn't have LONG LONG. If possible, I'd like to pick your brain about getting a setup to compile on windows. I'll talk to the dev team about how to deal What exactly does it take to compile DBD::mysql under Windows? I run Windows (usually). -- Cheers Ron Savage, [EMAIL PROTECTED] on 7/04/2005 http://savage.net.au/index.html Let the record show: Microsoft is not an Australian company
DBI disconnect Slow Performance
An update to my previous post. Turns out there is nothing wrong with DBI disconnect, at least in my case. In my case, I had misconfigured my Oracle auditing. It was taking a long time to write session summary records when the session was ended, which coincides with the last statement (disconnect) in my Perl scripts. Moral: A correlation is not necessary a causal relation. Darrell Oresky [EMAIL PROTECTED]
Re: odd DBD::Oracle query hang with NLS_LANG and utf8-flagged bind parameter
On Wed, Apr 06, 2005 at 06:11:23PM -0400, mike bayer wrote: okwell it seems that the patch works. Great. Thanks. as far as a test case, so far my two test criterion are a. this specific table on this specific database which hangs for unclear reasons, so thats not a good candidate and b. looking in this trace log and seeing csform 0-1. so whats a good way to verify the OCI_ATTR_CHARSET_FORM of a bind variable ? does it affect the data that gets inserted into a column ? There's no easy way to access that from perl at the moment. Thanks again. Tim.
