CODEWITHSUNDEEP
javascriptsql-serverutf-8
mathB
mathB

Reputation: 634

Inserting UTF-8 data into SQL Server 2012

I have an SQL Server 2012 DB where a table contains 2 columns of UTF-8 data (non-English. Tamil language to be specific). The data type that I set for those columns are nvarchar(500). I get input (the UTF-8 data) from a jsp page (input type as text). I can insert the values of that text box into the sql server successfully, but when I went to see the value, it is like this "&#2...1;" I've also made sure to add N before the column value. Here's the code:

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = java.sql.DriverManager.getConnection(url, id, pass);

String addbks = "insert into something.dbo." + Table + " (ID, BookName, Author, Owner, OwnerEmpID, Status, Reservation, Genre, StatusID) values (NEXT VALUE FOR " + Seq + ", N'" + BookName + "', N'" + AuthorName + "', '" + Name + "', '" + user2 + "', 'Available', NULL, '" + Genre + "', '1')";

Where BookName and AuthorName are the two columns that we talked about. I have also converted the BookName and AuthorName into UTF-8 format - here's the code:

BookName = new String(BookName.getBytes("ISO-8859-1"),"UTF-8");
AuthorName = new String(AuthorName.getBytes("ISO-8859-1"),"UTF-8");

Below the Body tag, I also have the following:

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>

I still get special characters in the DB rather than the actual data. How can I achive this?

EDIT: For the record, if I insert the data directly in SQL Server, it works just fine. Here's the query:

select BookName, Author from tam_hist where bookname like N'%தமிழ்%'

Result: தண்ணீர்

Upvotes: 2

Views: 4036

Answers (1)

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14097

So after our little chat in comment section, I understood that your collaction needs to be changed in your table, this is potential query to do that:

ALTER TABLE tam_hist
ALTER COLUMN bookname NVARCHAR(500) COLLATE Indic_General_90_BIN;

I'm using this collation as it's suggested on this site.

If you need/want to change collation on whole database, not on specific columns, please read this link.

This is basic replication of your issue:

DECLARE @Test TABLE ( bookName NVARCHAR(500) COLLATE Indic_General_90_BIN)

INSERT INTO @Test (bookName)
VALUES (N'தமிழ்')

SELECT *
FROM @Test
WHERE bookName LIKE N'%தமிழ்%'

For me, it returns expected results. Perhaps there's issue with your SQL Statement? As I see you're concatenating string to create query, perhaps using perpared statements would fix your issue?

Upvotes: 1

Related Questions