Unable to retrive the chinese characters from oracle db using DBI:DBD

Hi,

We are inserting chinese characters into the database using datadeploy, While doing datadeploy the values inserted in the database is perfect and is getting rendered on the site as aspected. We are trying to retrive this value back from the database using a perl file where we are using the DBI Smiley Very Happy

BD module to do so, while retriving back the data from the database i get junk characters.I have tried encoding and also decoding but that does not help.

What can be the propbable cause for the data getting corrupted?

Thanks & Regards

Manish Mohan

Find more posts tagged with

Comments

iwovGraduate

Probable cause based on what you have specified: Your Perl script is not handling the encoding properly.

If you need further assistance, provide more details on what you have done, possibly with your code/sample data, environment info, versions, etc.

manish777

Hi,
We are using a windows 2003 server.
Intially we tried utf-8 encoding, but still the problem continued. So at the moment we have removed that part of the code.

Let me explain the complete scenario to you:

1. We have a template where we have a field called product name and depending on the product selected using formapi a callserver is done to a cgi file where a db connection is established and the all the feature values are stroed in the pointer array and returned back to the formapi from where it is populated in the DCR.

I am attaching the code snippet for the cgi and the javascript for your reference:

CGI FIle

#!f:/iw-home/iw-perl/bin/iwperl -w

BEGIN{
# set [and export] vars
$ENV{'SystemRoot'}='C:\WINDOWS';
$ENV{'Path'}='F:\oracle\ora92\bin;';
};

# Include all the required perl modules
use DBI: Smiley Very Happy

BD; # Used to excecute the database access commands.
use TeamSite::Config; # Used to invoke generic teamsite provided methods.
use Log::Log4perl qw(:easy); # USed for logging purposes.
use CGI; # Used to get request parameters.
use HTML::Entities;
use Encode;
use utf8;

# -------------------------------------------------------------------------------------
# START - Getting parameters from the request
# -------------------------------------------------------------------------------------
#Turn buffering off.
$| = 1;
# Create an instance of the CGI PM.
my $in = new CGI;
# Get the product ID from the request.
my $productID = $in->param('productID'); # Product ID.
# -------------------------------------------------------------------------------------
# END - Getting parameters from the request
# -------------------------------------------------------------------------------------

print "Content-type: text/html\n\n";

# -------------------------------------------------------------------------------------
# START - Set the Environment Variables Here
# -------------------------------------------------------------------------------------
$ENV{'SystemRoot'}='C:\WINDOWS'; # This is essential for the program to work in the TeamSite environment.
# -------------------------------------------------------------------------------------
# END - Set the Environment Variables Here
# -------------------------------------------------------------------------------------

# -------------------------------------------------------------------------------------
# START - Initializing the variables
# -------------------------------------------------------------------------------------
#IWHOME
my $iwhome = TeamSite::Config::iwgethome();

# Initial configuration for logging purposes.
Log::Log4perl::init($iwhome.'/iw-perl/vendor/lib/Log/log4perl.conf');
my $logger = Log::Log4perl->get_logger($iwhome.'/local/bin/wartsila/fetchProductDetails.ipl');

# String to be sent back to the DCT.
# This string has all the option tags showing all the categories from the database.
my $productsDetailsFromDB = '';

# -------------------------------------------------------------------------------------
# END - Initializing the variables
# -------------------------------------------------------------------------------------

# -------------------------------------------------------------------------------------
# START initializing the database
# -------------------------------------------------------------------------------------
#Database query to fetch catogory values
my $PRODUCT_DETAILS = "select * from tbl_ProductDetails where Product_ID = " . $productID;

my $username="scott";
my $password="tiger";
my $sid="devliv_10.118.70.233";
my $connectString="dbi Smiley Surprised

racle:";
$connectString="$connectString$sid";
my $connectString = DBI->connect($connectString,$username,$password) or die "Couldn't connect to database: " . DBI->errstr;

# -------------------------------------------------------------------------------------
# START - Exceute the query and obtaing results
# -------------------------------------------------------------------------------------
# Creating and executing query to get product details
my $query = $connectString->prepare($PRODUCT_DETAILS) || dienice("Unable to prepare query to get product details");
$query->execute() || dienice("Unable to get product details from the database");
# -------------------------------------------------------------------------------------
# END - Exceute the query and obtaing results
# -------------------------------------------------------------------------------------

# ------------------------------------------------------------------------------------------
# START - Processing the results
# Iterating thru the array thru pointers to get all the information it.
# This loop also creates the string which must be inserted in the HTML snippet.
# This string basically adds all the values to the JS array variable "productDetails".
# This information is displyed in the various product related fields in the DCT.
# ------------------------------------------------------------------------------------------
while ( @row = $query->fetchrow_array )
{
$row[4] =~ s! !!g;
$logger->debug("Rows obtained from the database: Inside the while loop for processing ".$row[0]);
$productsDetailsFromDB .= "productDetails[0] = \"$row[1]\";\nproductDetails[1] = \"$row[2]\";\nproductDetails[2] = \"$row[3]\";\nproductDetails[3] = \"$row[5]\";\nproductDetails[4] = \"$row[6]\";\nproductDetails[5]= \"$row[7]\";\nproductDetails[6] = \"$row[8]\";\nproductDetails[7] = \"$row[9]\";\nproductDetails[8] = \"$row[10]\";\nproductDetails[9] = \"$row[11]\";\nproductDetails[10] = \"$row[12]\";\nproductDetails[11] = \"$row[13]\";\nproductDetails[12] = \"$row[14]\";\nproductDetails[13]=\"$row[4]\";\n";
}

# If the number of rows returned from the DB is 0, then show empty fields on the DCT.
$logger->debug('Number of rows obtained from the database: '.$query->rows);
if($query->rows == 0){
$productsDetailsFromDB .= "productDetails[0] = \"\";\nproductDetails[1] = \"\";\nproductDetails[2] = \"\";\nproductDetails[3] = \"\";\nproductDetails[4] = \"\";\nproductDetails[5]= \"\";\nproductDetails[6] = \"\";\nproductDetails[7] = \"\";\nproductDetails[8] = \"\";\nproductDetails[9] = \"\";\nproductDetails[10] = \"\";\nproductDetails[11] = \"\";\nproductDetails[12] = \"\";\n";
}
$logger->debug('productsOptions string which shall be inserted in the Javascript&colon; '.$productsDetailsFromDB);
# -------------------------------------------------------------------------------------
# END - Processing the results
# -------------------------------------------------------------------------------------

# ------------------------------------------------------------------------------------------
# Creating a HTML snippet which shall be used by the productAdminFormAPI.js file.
# ------------------------------------------------------------------------------------------
print <

<script language="javascript">

var productDetails = new Array();
// Set up the options object for a locations.
$productsDetailsFromDB

</script>

EOT

exit;

Javascript file

function populateProductDetails(productDetails){
//Populate all the product details fields in the DCT.
IWDatacapture.getItem("/productAdmin/productDescription").setValue(productDetails[0]);
IWDatacapture.getItem("/productAdmin/productImage").setValue(productDetails[1]);
IWDatacapture.getItem("/productAdmin/altText").setValue(productDetails[2]);
IWDatacapture.getItem("/productAdmin/featureList/feature_1").setValue(productDetails[3]);
IWDatacapture.getItem("/productAdmin/featureList/feature_2").setValue(productDetails[4]);
IWDatacapture.getItem("/productAdmin/featureList/feature_3").setValue(productDetails[5]);
IWDatacapture.getItem("/productAdmin/featureList/feature_4").setValue(productDetails[6]);
IWDatacapture.getItem("/productAdmin/featureList/feature_5").setValue(productDetails[7]);
IWDatacapture.getItem("/productAdmin/featureList/feature_6").setValue(productDetails[8]);
IWDatacapture.getItem("/productAdmin/featureList/feature_7").setValue(productDetails[9]);
IWDatacapture.getItem("/productAdmin/featureList/feature_8").setValue(productDetails[10]);
IWDatacapture.getItem("/productAdmin/featureList/feature_9").setValue(productDetails[11]);
IWDatacapture.getItem("/productAdmin/featureList/feature_10").setValue(productDetails[12]);
if(productDetails[13] == 'y'){
IWDatacapture.getItem("/productAdmin/comparable").setValue(0);
IWDatacapture.getItem("/productAdmin/featureList").setVisible(true);
}

if(productDetails[13] == 'n'){
IWDatacapture.getItem("/productAdmin/comparable").setValue(1);
IWDatacapture.getItem("/productAdmin/featureList").setVisible(false);
}
}

Still if you think that you require something else do let me know.

Regards

Manish