DBD::JDBC::db prepare failed error

Hi,

I have a written a perl script to insert some values into ms sql database tables.
I am able to insert values in the table if use hard coded values but it is failing if I read a dcr and insert the values using perl variables.

ERROR: DBD::JDBC::db prepare failed: java.lang.OutOfMemoryError: Java heap space at deploydb_pilot.ipl line 130.

environment: TeamSite 6.7.2 and MS SQL 2005

Code:
[html]

use TeamSite::Config;
use TeamSite: Smiley Very Happy

CRnode;
use TeamSite::XMLnode;

use strict;
use DBI;
use DBD::JDBC;

my $host = TeamSite::Config::hostname();
print "hostname: $host\n";

my $dbh = DBI->connect( "dbi:JDBC:hostname=$host;port=$port;url=$url",$user,$password ) or die print $DBI::errstr;

my $data;
my $dcrlocation=$ARGV[0];
if (open(DCR, "< $dcrlocation"))
{
while () { $data .= $_; }
close(DCR);
}
else
{
print "could not open $dcrlocation";
}

my $rootnode = TeamSite::XMLnode->new($data);
print "rootnode: $rootnode\n";

my $root_val = $rootnode->value();
print "root_val: $root_val \n";

my $pvid = $rootnode->value('pvid');
print "pvid: $pvid \n";

my $lang = $rootnode->value('metadata.language');
print "lang: ##$lang##\n";

my $country = $rootnode->value('metadata.country');
print "country: $country \n";

my $sql = "insert into productdetail values ('us', $lang, '01_7', 'prod_descr', 'prod_short_descr', 'tag_line', 'features','benifits','sys_req')";

print "$sql\n";
my $sth=$dbh->prepare($sql);
$sth->execute();

if( ($dbh->errstr eq "") || ($sth->errstr() eq "") ){
print "inserted row successfully \n";
$sth->finish();
#$dbh->disconnect;

}
else {

print "Error inserting \n";
print "Error inserting to database: Error $DBI::err - $DBI::errstr\n";
$sth->finish();
$dbh->disconnect;
}

[/html]

Please let me know if anybody have any idea?

Find more posts tagged with

Comments

Rinku4TS

java.lang.OutOfMemoryError

I think it is memory problem.

Please tell your system RAM size.

Surender

java.lang.OutOfMemoryError

I think it is memory problem.

Please tell your system RAM size.

# prtconf
System Configuration: Sun Microsystems sun4u
Memory size: 8192 Megabytes

Adam Stoller

Can you verify that the output of

print "$sql\n";

only has the one newline at the end of the string, and no seemingly odd characters embedded within it?

Surender

Can you verify that the output of
print "$sql\n";
only has the one newline at the end of the string, and no seemingly odd characters embedded within it?

This is the sql string output and error message

sql: ###insert into productdetail values ('us', 'en', '01_7', 'prod_descr', 'prod_short_descr', 'tag_line', 'features','benifits','sys_req')###
DBD::JDBC::db prepare failed: java.lang.OutOfMemoryError: Java heap space at deploydb_pilot.ipl line 130.
Can't call method "execute" on an undefined value at deploydb_pilot.ipl line 131.

I didn't see anything strange here.

'en' is the output of $lang variable if I hard code that value it's working fine.

Migrateduser

Try using bound variables and see if it helps. In case it's your escaping that's the issue.

[PHP]
my $sql = "insert into productdetail values ('us', ?, '01_7', 'prod_descr', 'prod_short_descr', 'tag_line', 'features','benifits','sys_req')";

print "$sql\n";
my $sth=$dbh->prepare($sql);
$sth->execute($lang);

[/PHP]

Rick Poulin

Don't forget that java.lang.OutOfMemoryError is a Java error, not a PERL one. Basically, your connector isn't being initialized with enough heap space to handle the amount of data you're retrieving from the DB in your SQL query. Either optimize your query, or find a way to specify a larger cap on heap space through DBD::JDBC. I don't know how you'd do that through that interface, but in a straight Java command line, you'd specify parameters like -Xms=512m and such.

Surender

Hi All,

I tried changing the heap max size to 1024m and also 256m but no luck.

Please also note that I am able to query the table and print the rows successfully. Getting this error while inserting into the table with perl variables in the insert query.

highly appreciate your help.
Thanks.

Migrateduser

Which version of DBD::JDBC are you using and which version of Java?

Here's a link to CPAN http://search.cpan.org/~vizdom/DBD-JDBC-0.70/JDBC.pod which may help you out.

Surender

Hi All,

$DBD::JDBC::VERSION = '0.70';

After a lot of trial and error methods I was able to make it work up to a certain point but still getting some error.

I really need your help here.

[html]

my $dcrlocation = "/default/main/www-symc/english/US/WORKAREA/UNIQUE/templatedata/enterprise/marketingpv/data/145_1.xml";

my $data;
if (open(DCR,"<:utf8", "$dcrlocation"))
{
while () {
$data .= $_;
}
close(DCR);
}
else
{
print "could not open $dcrlocation";
}

my $rootnode = TeamSite: Smiley Very Happy

CRnode->new($data);
my $pvid = $rootnode->value("pvid");
print "pvid: $pvid\n";
my $cntr = $rootnode->value("metadata.country");
print "cntr: $cntr\n";

my $lang = $rootnode->value("metadata.language");
print "lang: $lang\n";

my $prod_descr = $rootnode->value('product_description');
#print "prod_descr: $prod_descr \n";

print "length1 :" .length $prod_descr ,"\n";

my $prod_short_descr = $rootnode->value('product_short_description');
print "prod_short_descr: $prod_short_descr \n";

chomp($prod_short_descr);

print "length2 :" .length $prod_short_descr ,"\n";

my $key_fea_list = $rootnode->get_inner_xml('key_features.copyblock.list')."\n";
#print "key_fea_list: $key_fea_list \n";

my $sth = $dbh->prepare("insert into productdetail values (?, ?, ?, ?, 'prod short description', 'tag linet', 'features list', 'benefits list', 'sys requirements')");
$sth->bind_param(1, $cntr); # placeholders are numbered from 1
$sth->bind_param(2, $lang);
$sth->bind_param(3, $pvid);
$sth->bind_param(4, $prod_descr);

if I comment out the this stmt $sth->bind_param(4, $prod_descr); and hard code the value for product description it is working. But if I bind a variable which parsed from dcr getting the below error.

pvid: 145_1
cntr: us
lang: en
prod_descr: Veritas Storage Foundation for Oracle RAC is not an Oracle RAC alternative, but rather an Oracle RAC enhancement that includes storage management and high availability technologies allowing you to implement robust, manageable, and scalable Oracle Real Application Clusters. The solution delivers the industry's first heterogeneous cluster file system supporting Oracle RAC.
length1 :373
prod_short_descr: Offers a proven solution to help customers deliver and manage highly available Oracle Real Application Clusters (RAC) databases.
length2 :128
DBD::JDBC::st execute failed: java.io.IOException: Unrecognized BER object identifier: [APPLICATION 6 constructed] at deploydb_pilot2.ipl line 77.
Error inserting
Error inserting to database: Error 8 - java.io.IOException: Unrecognized BER object identifier: [APPLICATION 6 constructed]
DBD::JDBC::db disconnect failed: I/O Error Connection reset by peer at deploydb_pilot2.ipl line 90.
connected to database server
DBD::JDBC::db prepare failed: I/O Error Bad file number at deploydb_pilot2.ipl line 101.
Can't call method "execute" on an undefined value at deploydb_pilot2.ipl line 102.

[/html]

any ideas here?
the product description contains up to 500 characters.

thatfish

DBD::JDBC::st execute failed: java.io.IOException: Unrecognized BER object identifier: [APPLICATION 6 constructed] at deploydb_pilot2.ipl line 77.
Error inserting
Error inserting to database: Error 8 - java.io.IOException: Unrecognized BER object identifier: [APPLICATION 6 constructed]
DBD::JDBC::db disconnect failed: I/O Error Connection reset by peer at deploydb_pilot2.ipl line 90.
connected to database server
DBD::JDBC::db prepare failed: I/O Error Bad file number at deploydb_pilot2.ipl line 101.
Can't call method "execute" on an undefined value at deploydb_pilot2.ipl line 102.

The "BER object identifier" looks like it could be an encoding issue.

Can you look at the SQL statement you are building in more detail ?

e.g using Data&colon; Smiley Very Happy

umper (something like the following)


use Data&colon;umper 'Dumper';
$Data&colon;umper::Useqq = 1;
print Dumper $sql;

Also, can you confirm the data types of the columns in your database ?

Surender

Also, can you confirm the data types of the columns in your database ?

Here is the table structure

CREATE TABLE productdetail (
ct varchar(4) NOT NULL,
lg varchar(4) NOT NULL,
pvid varchar(25) NOT NULL,
description varchar(500) NOT NULL,
short_description varchar(200) NOT NULL,
tagline varchar(100) NOT NULL,
features varchar(5000) NOT NULL,
benefits varchar(5000) NOT NULL,
system_requirements varchar(5000) NOT NULL,
constraint productdetail_pri PRIMARY KEY (lg,pvid,ct)
)

Surender

Hi,

If I execute like this it is working.

my $sql = "insert into productdetail values ('us', 'en', '145_1', 'prod descr', 'Offers a proven solution to help customers deliver and manage highly available Oracle Real Application Clusters (RAC) databases.', 'tagline', 'features', 'benefits', 'req')";

my $sth = $dbh->prepare($sql);
$sth->execute;

But the same values are not working if I build the $sql variable and use it in the prepare stmt.

my $sql = "insert into productdetail values ('$cntr', '$lang', '$pvid', 'prod descr', '$prod_short_descr', 'tagline', 'features', 'benefits', 'req')";

my $sth = $dbh->prepare($sql);
$sth->execute;

Appreciate your help.

Thanks.

Migrateduser

Try adding jdbc_character_set=ASCII to your connection string.

my $dbh = DBI->connect("dbi:JDBC:hostname=$host;port=$port;url=$url;jdbc_character_set=ASCII",$user,$password ) or die print $DBI::errstr;