Set option to DBI-Connection (handling LOBs with oracle)

One Star

Set option to DBI-Connection (handling LOBs with oracle)

Hello,
if I load data with the component "tOracleInput" I get the following error:
DBD:Smiley Surprisedracle::st fetchrow_array failed: ERROR fetching field 10 of 68. LOB value truncated from 746 to 80. DBI attribute LongReadLen too small and/or LongTruncOk not set at S:\temp\TOS-Win32-r2461-V2.0.0M2\workspace\.Perl\TEST.job_REPLICATE_TD.pl line 817.
I hope I can handle this problem by setting the following option on the conenction:
$dbh_tOracleInput_1->{LongReadLen}=500000;
But: How can I set this option in the generated code?
Tags (1)
Employee

Re: Set option to DBI-Connection (handling LOBs with oracle)

I'm sorry, there is not trick to do this without modifying the source code. But as TOS is opensource and you can modify components sources, you can do it :-)
If you now use a 2.0.0RC1 (you should, it's better than M2), in plugins/org.talend.designer.components.localprovider_2.0.0.RC1_r2928/components/tOracleInput/tOracleInput_begin.perljet, after
    <%=dbuser %>,
<%=dbpass %>,

add
    {
LongReadLen => 500000,
}
One Star

Re: Set option to DBI-Connection (handling LOBs with oracle)

Hi,
I have the same (I think) problem connecting to MSSQL. Error reads "DBD:Smiley SurprisedDBC::st fetchrow_array failed: String data, right truncation (SQL-01004)(DBD: st_fetch/SQLFetch (long truncated DBI attribute LongTruncOk not set and/or LongReadLen too small) err=-1) at C:\Program Files\TOS-2.0.0\workspace\.Perl\MYDRPERL.job_Articles.pl line 633."
I have tried modifying the plugins\org.talend.designer.components.localprovider_2.0.0.r3065\components\tMSSqlInput\tMSSqlInput_begin.perljet file without any success, around line 67 I changed
my $dbh_<%=cid %> = DBI->connect(
getConnectionString(
driver => $desc_<%=cid %>{driver},
dbname => $desc_<%=cid %>{dbname},
dbhost => $desc_<%=cid %>{dbhost},
dbport => $desc_<%=cid %>{dbport},
),
$desc_<%=cid %>{dbuser},
$desc_<%=cid %>{dbpass},
)
or die "can't connect to database";

to
my $dbh_<%=cid %> = DBI->connect(
getConnectionString(
driver => $desc_<%=cid %>{driver},
dbname => $desc_<%=cid %>{dbname},
dbhost => $desc_<%=cid %>{dbhost},
dbport => $desc_<%=cid %>{dbport},
),
$desc_<%=cid %>{dbuser},
$desc_<%=cid %>{dbpass},
{
LongTruncOk => 1,
LongReadLen => 500000
}
)
or die "can't connect to database";

Unfortunately, ... this seems not to have worked.
Any input would be appreciated!
Cheers,
Seb.
One Star

Re: Set option to DBI-Connection (handling LOBs with oracle)

Sorry for the late answer:
I've fixed the problem with the following patch to the file
\plugins\org.talend.designer.components.localprovider_2.1.0.M1_r3483\components\tOracleInput\tOracleInput_begin.perljet
After connecting to oracle I set LongReadLen to 500000. Setting it direct at the connect-function seems not to work:
my $dbh_<%=cid %> = DBI->connect(
sprintf(
'dbiSmiley Surprisedracle:host=%s;sid=%s;port=%s',
<%=dbhost %>,
<%=dbname %>,
<%=dbport %>,
),
<%=dbuser %>,
<%=dbpass %>,
)
or die "can't connect to database";
# FOLLOWING LINE ADDED TO AVOID PROBLEM SELECTING LOBs
$dbh_<%=cid %>->{LongReadLen}= 500000;
my $sth_<%=cid %>;
my $query;
I've created a 1204
Employee

Re: Set option to DBI-Connection (handling LOBs with oracle)

Ryders, have you tried Volker Brehm's tip to set LongReadLen after the connect function call? Does it solve your problem?
One Star

Re: Set option to DBI-Connection (handling LOBs with oracle)

Hey plegall,
Sorry about the late response.. I've been disturbed by other things lately!
For a few releases/updates I've been updating the file with the following code;
my $dbh_<%=cid %> = DBI->connect(
getConnectionString(
driver => $desc_<%=cid %>{driver},
dbname => $desc_<%=cid %>{dbname},
dbhost => $desc_<%=cid %>{dbhost},
dbport => $desc_<%=cid %>{dbport},
),
$desc_<%=cid %>{dbuser},
$desc_<%=cid %>{dbpass},
{
LongTruncOk => 1,
LongReadLen => 500000
}
)

Hence doing pretty much what Volker suggested (mmm... well... as I suggested above!). The trick is that Talend needed a restart, which I though was surprising considering it's bloody perl! but well..! Smiley Wink
I don't mind which every way you want to fix the issue raised bugtracker, as both solution are doing pretty much the same! just pick the more elegant! Smiley Wink
Cheers,
Seb.
One Star

Re: Set option to DBI-Connection (handling LOBs with oracle)

oh.. and the 'or die' bit...!
my $dbh_<%=cid %> = DBI->connect(
getConnectionString(
driver => $desc_<%=cid %>{driver},
dbname => $desc_<%=cid %>{dbname},
dbhost => $desc_<%=cid %>{dbhost},
dbport => $desc_<%=cid %>{dbport},
),
$desc_<%=cid %>{dbuser},
$desc_<%=cid %>{dbpass},
{
LongTruncOk => 1,
LongReadLen => 500000
}
)
or die "can't connect to database";