Hi Monks,

I am working with below environment..

Perl 5.8.9
Windows 2003
SQL Server 2005

I have to read data from a UTF-8 text file and insert it to database. Data is seperated by colon in iput file.

I am performing below steps...
1. Connecting to database and preparing an sql statement
2. Reading the data from a file line by line and decoding it using decode_utf8() method
2. Splitting the data and reading into variables
3. Executing the sql statement
4. Closing database connection

When i insert data without setting $dbh->{odbc_default_bind_type} = 12, It is throwing an error "MicrosoftODBC SQL Server DriverInvalid character value for cast specification (SQL-22018)". So i had set it to 12 and it is working fine. I do have Thai characters in input data file. The issue is when i insert Thai characters, they are getting inserted with some junk characters. When i execute the same query in database manually, it is inserting properly.

In database, fields are with datatypes nvarchar, datetime, int and float.

Below is the code snippet, working fine with out Thai characters... 

#!/usr/bin/perl

use DBI;
use Encode;

my $AbsFileName = "E:\\sample\.txt";

        open (A123, "$AbsFileName");
        my ($DSN,$dbh,$sth);
        $dbh = DBI->connect("dbi:ODBC:DSN=<dsn name>;UID=<username>;PW
+D=<password>") or die "$DBI::errstr\n";
        
        $dbh->{odbc_default_bind_type} = 12; # SQL_VARCHAR
        my $sth = $dbh->prepare(<<SQL) or die "$DBI::errstr\n";
        INSERT INTO BK_imageINfo (CustFld1,CustFld2,CustFld3,CustFld4,
+CustFld5,CustFld6,CustFld7,CustFld8,CustFld9,CustFld10,CustFld11,Cust
+Fld12,Efield1,Efield2,Efield3,Efield4,Efield5)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
SQL

        while (<A123>)
        {
            my $data = decode_utf8( $_ );
            chomp($data);
            my ($cfd1, $cfd2, $cfd3, $cfd4, $cfd5, $cfd6, $cfd7, $cfd8
+, $cfd9, $cfd10, $cfd11, $cfd12$enr1, $enr2, $enr3, $enr4, $enr5) = s
+plit /:/, $data;

            ## $enr1-5 contains Thai characters... In database, Thai c
+har need to be inserted as N'Thai chars'..So prepending with 'N'
            my $enr11 = "N". $dbh->quote( $enr1 );
            my $enr21 = "N". $dbh->quote( $enr2 );
            my $enr31 = "N". $dbh->quote( $enr3 );
            my $enr41 = "N". $dbh->quote( $enr4 );
            my $enr51 = "N". $dbh->quote( $enr5 );
            
            open (ABC, ">>query.txt");
            my $sql = "INSERT INTO BK_imageINfo (CustFld1,CustFld2,Cus
+tFld3,CustFld4,CustFld5,CustFld6,CustFld7,CustFld8,CustFld9,CustFld10
+,CustFld11,CustFld12,Efield1,Efield2,Efield3,Efield4,Efield5) VALUES 
+('$cfd1', '$cfd2', '$cfd3', '$cfd4', '$cfd5', '$cfd6', '$cfd7', '$cfd
+8', '$cfd9', '$cfd10', '$cfd11', '$cfd12', '$enr11', '$enr21', '$enr3
+1', '$enr41', '$enr51')";
            print ABC "\n$sql\n";
            close ABC;
            $sth->execute($cfd1, $cfd2, $cfd3, $cfd4, $cfd5, $cfd6, $c
+fd7, $cfd8, $cfd9, $cfd10, $cfd11, $cfd12, $enr11, $enr21, $enr31, $e
+nr41, $enr51) or die "$DBI::errstr\n";
        }
        close A123;
        #Close the database
        $sth->finish;
        $dbh->disconnect;

[download]

Please help me in inserting Thai characters into SQL Server database?


In reply to Issue while inserting Thai Characters into Database.. by ajaykumarb

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.