How to insert the special character in DB?

Question

I'm working on Ubuntu and I'm using DBeaver for DB access. I'm trying to insert the Latin character into a database (MSSQL) and it throws an error. But if I insert the same special character with DBeaver, it is not throwing any error and it is working. I have already seen this question. But I haven't got any idea.

It is throwing the following error:

DBD::ODBC::st execute failed: [unixODBC][FreeTDS][SQL Server]Error converting characters into server's character set. Some character(s) could not be converted (SQL-HY000) at expert.fi_review.pl line 243.

And data in db is J�rjest�myyr�. But the actual data is Järjestömyyrä.

Sample code:

my ($dsn,$dbh);
&DB_Connect;

$insert_query = "INSERT INTO table_name (name) values(N'$name')";
my $sth = $dbh->prepare($insert_query);
$sth->execute() or $DB_Error=$DBI::errstr;

sub DB_Connect
{
    $dsn = "dbi:ODBC:driver={SQL Server};Server=$Server_name,$port;database=$Database_name;driver=FreeTDS;tds_version=8.0;";
    reconnect: $dbh = DBI->connect($dsn, $db_user_id, $db_pwd ,{AutoCommit => 1}) or goto reconnect;
    $dbh-> {'LongTruncOk'} = 1;
    $dbh-> {'LongReadLen'} = 90000;
}

Answer 1

Hmm. I added iconv support to FreeTDS and in some sense am responsible for that error message. I have three questions and one suggestion.

1. What is the encoding of the target column, table_name.name? Does that encoding support the characters you're trying to insert? I bet not.

2. Does the client charset in freetds.conf match the character set reflected in locale(1)?

3. Are you sure you're not using the old TDS protocol version 4.1?

FreeTDS converts SQL text to UTF-16. When you interpolate the data into the SQL insert statement, FreeTDS converts the whole statement, data and all. If the client encoding is incorrectly described, the data can't be converted. If the server encoding can't represent the character, the data can't be converted. And if the protocol is ancient, there's no support for Unicode in sight.

It's hard to tell from your error message, but it looks to me like the client side succeeded and the server side failed. That is, FreeTDS correctly converted the data to UTF-16, but the server could not insert the data into the name column, because it's varchar, not nvarchar, and the "code page" for the database (or column, if specified) is not one that can represent those characters.

My suggestion is to use TDS_DUMP and join the FreeTDS mailing list. The TDS_DUMP log will answer all the questions I posed. And you'll get better support on the mailing list, because it's specialized.

Well you may ask why DBeaver seems to work. I don't know; I never heard of it until now. I can tell you that there are ways to insert data (for example, with parameterized queries in ODBC) where the server simply inserts the data verbatim. Strangely enough, it doesn't verify that they are valid for the declared encoding.

I've helped many people with problems like that. The database is set up for, say, CP-1252, but the data aren't correctly encoded. The application can insert mis-encoded data, and later retrieve them and render them correctly, slipping under the radar as it were. But let the DBA try to query the data or inspect them with normal admin tools, and they look funny. The server assumes the data in its database are encoded per the database definition. When that's not the case hilarity ensues!

If that's your case, you can easily test for it. Use DBeaver to insert the data. Use the SQL Server admin application, or good old isql, to retrieve it on Windows. Odds are you will not be amused.