What is RSS?
Re: Problem with DBD::Oracle ora_auto_lob not working unsubscibi me plz - unsubsicrib mi please
Hello Guest		   
  • Login
• Register…
• Start blog		   • Who, Where, When
• What can I do?
• What to Read?		   • Polls
• Avatars
• Interests		   • Cities and Countries
• Random blog
• Users search		   • Search
• Games
• Tests
• QAIX		   • Сообщества
• Talxy Chat
• Horoscope
• Online		  
Зарегистрируйся!
QAIX > Perl web-programming > Re: Problem with DBD::Oracle ora_auto_lob not working unsubscibi me plz - unsubsicrib mi please 19 July 2008 20:10:56
  Recent blog posts: 
How to make a Christmas photo…
Every Christmas people take a off from their busy schedule. This is the time they can enjoy with their…

Car web design, motorbike…
If you are looking for Car website design, Auto web design, bike web design, or anything similar our…

The Best Video Converter-a…
iOrgSoft Inc. has released the powerful Video Converter - iOrgSoft Video Converter 1.6.0, the latest…

Photo DVD Maker New Website…
AnvSoft Inc., the multimedia software developer, unveils a new website…

How to rip DVD movie, convert…
It is no doubt that PSP is an excellent game player. However, it is not playing games all the time; you…

World’s Most Unusual Christmas…
This Christmas, don’t go with that ho-hum, run-of-the-tree-far­­m tree your family always gets…

Facebook account cracker
facebook account cracker (50 GBP) Before you're told what this tool does, it's worth noting that…

BlackBerry Storm review…
As BlackBerry fans, you can’t miss the BlackBerry Storm. At Nov 6th, 2008, the word on the street is…

Review on Any DVD Cloner
Have you ever had a DVD Movie collection broken or lost? Have you ever wanted to backup movies but…

All recruitment agencys come…
What is RCCSS? RCCSS is a new unique peice of software which will scan millions of major sites and…

  They have birthday today: 
akram mellice
Daffy little vampire
  Forums:   
    topic feeds

• QAIX  all topics, blogs and comments

• PHP web-programming

• Perl web-programming

• ASP web-programming

• Visual Basic Programming

• .Net Development

• C/C++ Programming

• Java Programming

• Javascript web-programming

• Apache HTTP Server

• Web-design

• Web-hosting, dedicated, colocation

• HTML web-programming

• XML development

• General Web-programming

• Content Management Systems

• ColdFusion

• Search Engine Optimization

• Hardware

• Software, Computer Help

• MySQL database development

• PostgreSQL database development

• MS Access database development

• Oracle database development

• MS SQL database development

• Informix database development

• Buy / Sell

• Open discussion

• Anonymous
  Discuss: 
Rakudo bug reports still go to…
Some of you may have seen the announcements on the Parrot lists that Parrot will be starting to use…

Stop sql Injection attacks and…
HI there, not long ago I found a website explaining a feature of a dbi-addon (or catalyst-addon) which…

Parrot 0.8.1 "Tio…
On behalf of the Parrot team, I'm proud to announce Parrot 0.8.1 "Tio Richie." [1] Parrot…

Problem with uploader
I'm trying to make a CGI script to upload files remotely onto my server, to get around my uni blocking…

what is the best / latest .CGI…
what is the best / latest .CGI to handle safe form parsing? Lou
  Recent forum topics: 
Rakudo bug reports still go to…
Some of you may have seen the announcements on the Parrot lists that Parrot will be starting to use…

Stop sql Injection attacks and…
HI there, not long ago I found a website explaining a feature of a dbi-addon (or catalyst-addon) which…

[JCA/JBoss] - getOutputStream()…
Hi Guru, My project was developed by using JDeveloper10g. I have deployed the EAR file to…
  Recent forum comments:
RE: Macro: Substitution font and…
Not a macro approach, but after you have the data in the page you could just use Edit Select All in…

RE: Macro: Substitution font and…
You wouldn't want to use points to size your characters on the web page. Use pixels, em, en, ex…

RE: Problem with uploader
Matthew Whipple wrote: If the open failed then wouldn't the die kick in and at least say something in…

RE: [JCA/JBoss] - getOutputStream()…
Put Your Java Code in Servlet. It will work fine Try to avoid scriplet in jsp

RE: ASP Access Recordset Paging
Thanks a lot! You've been really helpful. What eventually solved the problem was moving the DB from…

RE: ASP Access Recordset Paging
e.l. wrote: You are not using odbc, so you should have removed those groups from the crosspost. I…

RE: ASP Access Recordset Paging
Thanks for the quick reply. 1. I use MS Office Access 2003 (11.8166.8202) SP3 2. The code of my…

RE: ASP Access Recordset Paging
e.l. wrote: We can't answer without seeing: 1. your database type and version 2. your connection string…

RE: ASP Access Recordset Paging
Hi, I'm having a different problem, but the same error message (ADODB.Recordset (0x800A0CB3) ). I…

RE: how to retrieve CheckBox value…
Evertjan. wrote: More like .Count…
  Moderators:
 Administrator
 Protorioris

Re: Problem with DBD::Oracle ora_auto_lob not working unsubscibi me plz - unsubsicrib mi please

Jobst M Ller 19 July 2008 20:10:56
 "Martin Evans" <martin.evans@easys­oft.com>, "dbi-users" <dbi-users@perl.org­>
unsubscibi me plz - unsubsicrib mi please <unsubscribe-dbi-us­ers@perl.org> how to



-----Urspr ngliche Nachricht-----
Von: "John Scoles" <scoles@pythian.com­>
Gesendet: 18.07.08 19:16:35
An: Martin Evans <martin.evans@easys­oft.com>
CC: dbi-users <dbi-users@perl.org­>
Betreff: Re: Problem with DBD::Oracle ora_auto_lob not working


Hi Martin I found the bug (well actually an omission) and fixed it so
now It will return the data you expect.
I committed the changes to Trunk
http://svn.perl.org­/modules/dbd-oracle/­trunk so if you will try the code
from there and see if it works for you now.
Seem the not all the attributes for the SP were not being passed along
so in actual fact it was only need the addition of 6 lines of code. As
is normal with anything to do with DBD::Oracle (well actully C in
general) it took 2 hours to find out where to put those lines fro the 2
min change.
cheers
John Scoles
Martin Evans wrote:
John Scoles wrote:
Ouch nasty one Martin.
But I think that DBD oracle is doing the correct thing here.
Well that is what I'm after clarification on. The pod for ora_auto_lob
says "retrieves the contents of the CLOB or BLOB column in most
circumstances". What are the circumstances it does /not/ retrieve the
clob/blob data and returns a lob locator instead?
I think when you bury the 'SELECT x from martin; ' in the SP like
this you are essentially only ever going to get a lob locater back as
that is what you are asking for.
I don't understand your comment here. I never asked for a lob locator,
I asked for the contents of column 'x' and if I run this select in
Perl I get the clob contents back (presumably because ora_auto_lob is
on by default and works on normal statement handles). It is only when
I run the select in an oracle procedure and return the cursor which
gets magicked into a DBI statement handle and then run a fetch on that
statement that I get a lob locator.
Of couser you could use all the neat lob function in DBD Oracle on it
like 'ora_lob_read', 'ora_lob_write' etc, which of course kind of
defeats the purpose of this exercise.
Actually, I do not mind running ora_lob_read to get my data back from
the lob locator (although I'd prefer ora_auto_lob did it for me) but
my data is utf8 and the pod says:
"Warning: Currently multi-byte character set issues have not been
fully worked out. So these methods may not do what you expect if
either the Perl data is utf8 or the CLOB is a multi-byte character set
(including uft8). The current behaviour in these situations may not be
correct and is subject to change."
which suggested to me that you can't get utf8 data back from
ora_lob_read. What I am actually trying to do is hide the SQL and any
schema from the perl by placing it in the database in a package, I'm
not worried how I get the data out when the procedure is called.
If you are using DBD:ORacle 1.21 you would try using the
ora_type=>SQLT_CHR or ora_pers_lob.
I did try using ora_pers_lob but it made no difference.
I going to release a new version of the code next week 1.22 that have
a number of LOB improvements that might help.
Excellent, I look forward to that.
I will see if I can recreate you problem today right not I am little
busy with real work.
Thanks. I have added to my test case a little to actually write utf8
data and get it back - it would seem that the pod warning with respect
to utf8 data not coming back from ora_lob_read /may/ be out of date.
See new code example below.
use DBI (data_diff);
use warnings;
use strict;
use Data::Dumper;
use DBD::Oracle qw(:­ora_types);
use Encode;
use charnames ':full';
my $h = DBI->connect('dbi:O­racle:XE', 'xxx', 'xxx');
eval {$h->do(q/drop table martin/);};
$h->do(q/create table martin (x clob)/);
my $data = "\x{263a}xxx" . chr(0x05d0) . "\N{ARABIC LETTER ALEF}";
print "Length of input data: " . length($data) . "\n";
$h->do(q/insert into martin values (?)/, undef, $data);
my $createproc = << 'EOT';
CREATE OR REPLACE PROCEDURE p_martin(pc OUT SYS_REFCURSOR) AS
l_cursor SYS_REFCURSOR;
BEGIN
OPEN l_cursor FOR
SELECT x from martin;
pc := l_cursor;
END;
EOT
$h->do($createproc)­;
my $s = $h->prepare(
q/begin p_martin(?); end;/, {ora_auto_lob => 1});
my $sth;
$s->bind_param_inou­t(1, \$sth, 50000,
{ora_type => ORA_RSET});
$s->execute;
my ($lobl) = $sth->fetchrow;
print Dumper($lobl);
my $length = $h->ora_lob_length(­$lobl);
print "lob length: $length\n";
my $cdata = $h->ora_lob_read($l­obl, 1, $length);
print data_diff($data, $cdata);
which prints:
Length of input data: 6
$VAR1 = bless( do{\(my $o = 142150356)}, 'OCILobLocatorPtr' );
lob length: 6
and does not print anything for data_diff suggesting to me the input
and output data are the same.
Martin


___________________­____________________­____________________­__________
Der WEB.DE SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen!
http://smartsurfer.­web.de/?mc=100071&di­stributionid=0000000­00066


Add comment
Alexander Foken 19 July 2008 20:10:56 [ permanent link ]
 Wer lesen kann, ist klar im Vorteil:

List-Post: <mailto:dbi-users@p­erl.org>
List-Help: <mailto:dbi-users-h­elp@perl.org>
List-Unsubscribe: <mailto:dbi-users-u­nsubscribe@perl.org>­
List-Subscribe: <mailto:dbi-users-s­ubscribe@perl.org>
List-Id: <dbi-users.perl.org­>


Alexander

On 19.07.2008 17:53, jobst m ller wrote:
"Martin Evans" <martin.evans@easys­oft.com>, "dbi-users" <dbi-users@perl.org­>
unsubscibi me plz - unsubsicrib mi please <unsubscribe-dbi-us­ers@perl.org> how to
-----Urspr ngliche Nachricht-----
Von: "John Scoles" <scoles@pythian.com­>
Gesendet: 18.07.08 19:16:35
An: Martin Evans <martin.evans@easys­oft.com>
CC: dbi-users <dbi-users@perl.org­>
Betreff: Re: Problem with DBD::Oracle ora_auto_lob not working
Hi Martin I found the bug (well actually an omission) and fixed it so
now It will return the data you expect.
I committed the changes to Trunk
http://svn.perl.org­/modules/dbd-oracle/­trunk so if you will try the code
from there and see if it works for you now.
Seem the not all the attributes for the SP were not being passed along
so in actual fact it was only need the addition of 6 lines of code. As
is normal with anything to do with DBD::Oracle (well actully C in
general) it took 2 hours to find out where to put those lines fro the 2
min change.
cheers
John Scoles
Martin Evans wrote:
John Scoles wrote:
Ouch nasty one Martin.
But I think that DBD oracle is doing the correct thing here.
Well that is what I'm after clarification on. The pod for ora_auto_lob
says "retrieves the contents of the CLOB or BLOB column in most
circumstances". What are the circumstances it does /not/ retrieve the
clob/blob data and returns a lob locator instead?
I think when you bury the 'SELECT x from martin; ' in the SP like
this you are essentially only ever going to get a lob locater back as
that is what you are asking for.
I don't understand your comment here. I never asked for a lob locator,
I asked for the contents of column 'x' and if I run this select in
Perl I get the clob contents back (presumably because ora_auto_lob is
on by default and works on normal statement handles). It is only when
I run the select in an oracle procedure and return the cursor which
gets magicked into a DBI statement handle and then run a fetch on that
statement that I get a lob locator.
Of couser you could use all the neat lob function in DBD Oracle on it
like 'ora_lob_read', 'ora_lob_write' etc, which of course kind of
defeats the purpose of this exercise.
Actually, I do not mind running ora_lob_read to get my data back from
the lob locator (although I'd prefer ora_auto_lob did it for me) but
my data is utf8 and the pod says:
"Warning: Currently multi-byte character set issues have not been
fully worked out. So these methods may not do what you expect if
either the Perl data is utf8 or the CLOB is a multi-byte character set
(including uft8). The current behaviour in these situations may not be
correct and is subject to change."
which suggested to me that you can't get utf8 data back from
ora_lob_read. What I am actually trying to do is hide the SQL and any
schema from the perl by placing it in the database in a package, I'm
not worried how I get the data out when the procedure is called.
If you are using DBD:ORacle 1.21 you would try using the
ora_type=>SQLT_CHR or ora_pers_lob.
I did try using ora_pers_lob but it made no difference.
I going to release a new version of the code next week 1.22 that have
a number of LOB improvements that might help.
Excellent, I look forward to that.
I will see if I can recreate you problem today right not I am little
busy with real work.
Thanks. I have added to my test case a little to actually write utf8
data and get it back - it would seem that the pod warning with respect
to utf8 data not coming back from ora_lob_read /may/ be out of date.
See new code example below.
use DBI (data_diff);
use warnings;
use strict;
use Data::Dumper;
use DBD::Oracle qw(:­ora_types);
use Encode;
use charnames ':full';
my $h = DBI->connect('dbi:O­racle:XE', 'xxx', 'xxx');
eval {$h->do(q/drop table martin/);};
$h->do(q/create table martin (x clob)/);
my $data = "\x{263a}xxx" . chr(0x05d0) . "\N{ARABIC LETTER ALEF}";
print "Length of input data: " . length($data) . "\n";
$h->do(q/insert into martin values (?)/, undef, $data);
my $createproc = << 'EOT';
CREATE OR REPLACE PROCEDURE p_martin(pc OUT SYS_REFCURSOR) AS
l_cursor SYS_REFCURSOR;
BEGIN
OPEN l_cursor FOR
SELECT x from martin;
pc := l_cursor;
END;
EOT
$h->do($createproc)­;
my $s = $h->prepare(
q/begin p_martin(?); end;/, {ora_auto_lob => 1});
my $sth;
$s->bind_param_inou­t(1, \$sth, 50000,
{ora_type => ORA_RSET});
$s->execute;
my ($lobl) = $sth->fetchrow;
print Dumper($lobl);
my $length = $h->ora_lob_length(­$lobl);
print "lob length: $length\n";
my $cdata = $h->ora_lob_read($l­obl, 1, $length);
print data_diff($data, $cdata);
which prints:
Length of input data: 6
$VAR1 = bless( do{\(my $o = 142150356)}, 'OCILobLocatorPtr' );
lob length: 6
and does not print anything for data_diff suggesting to me the input
and output data are the same.
Martin
___________________­____________________­____________________­__________
Der WEB.DE SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen!
http://smartsurfer.­web.de/?mc=100071&di­stributionid=0000000­00066


--
Alexander Foken
mailto:alexander@fo­ken.de http://www.foken.de­/alexander/


Add comment
 

Add new comment

As:
Login:  Password:  
 
 
  
 
Пожалуйста, относитесь к собеседникам уважительно, не используйте нецензурные слова, не злоупотребляйте заглавными буквами, не публикуйте рекламу и объявления о купле/продаже, а также материалы нарушающие сетевой этикет или УК РФ.

QAIX > Perl web-programming > Re: Problem with DBD::Oracle ora_auto_lob not working unsubscibi me plz - unsubsicrib mi please 19 July 2008 20:10:56
see also:
Library cache
LANG and NLS_LANG for utf8
Oracle's shopping spree
пройди тесты:
see also:
wcorto@gmail.com
HOW TO PRINT CENTER OF THE ROW FOR…
Intellexer Spellchecker SDK
  Copyright © 2001—2008 QAIX
Idea: Miсhael Monashev
Помощь и задать вопросы можно в сообществе support.qaix.com.
Сообщения об ошибках оставляем в сообществе bugs.qaix.com.
Предложения и комментарии пишем в сообществе suggest.qaix.com.
Информация для родителей.
Write us at:
If you would like to report an abuse of our service, such as a spam message, please .