Getting Arabic Text from an MSSQL NVarchar Column

Question

I am creating a library management system, I am trying to add Arabic support to my project, but I ran into difficulties as follows:

• when inserting data into SQL through Query ie Insert into book_info values(25, N'جاوا', N'Author',N'Publisher',2014,N'Subject',50,N'Language','Latest',5) The list of columns in book_info is as follows

  • Book id (int)
  • Book Title (NVarchar (50))
  • Posted by (NVARCHAR (50))
  • Publisher (NVARCHAR (50))
  • Post year (int)
  • Item (NVARCHAR (50))
  • Price (Intermediate)
  • Language (NVARCHAR (50))
  • Edition (NVARCHAR (50))
  • Part (whole)

  Therefore, when I execute this query through my project, SQL Management Studio shows some encoded characters, such as 'مجتبیٰ' instead of Arabic characters, not only in SQL Management Studio , but also when selecting data in project. Note: these values ​​in the request are taken from textfield via .getText() function

• What if we simply skip the first problem by running a query in SQL Management Studio , having done so, the first problem is temporarily resolved, because SQL Management Studio shows Arabic characters when selecting data, but again, when we select it from our software and show it in textfield , receiving data from a ResultSet , like " TextF.setText(ResultS.getNString()) ", it shows " ?????? " in the text field instead of Arabic characters.

Answer 1

Your database definition, query, and SQL instance are great. The problem you are facing is reading a row from a database in java.

I think Java is trying to read your nvarchar text as ASCII characters when they are actually in Unicode. The result is distorted text. I think that when you read text from a database, you need to make sure that you read as Unicode.

If you can post your code, I could probably determine what causes the problems.

Answer 2

After spending hours searching for the cause and consulting with my seniors, I got a solution, in short, I just had to code the incoming result set into UTF-8 (Unicode) encoding, here's how I did it:

Create a column table with nvarchar () as the data type into which you must insert bidirectional text, and then use the following queries to insert, update, select and delete (there is only one column in the example table, i.e. nvarchar () data type )

 INSERT INTO table_name VALUES(N'عربی ABC') SELECT FROM table_name WHERE column_name LIKE N'عربی%' UPDATE table_name SET column_name = N'عرب' WHERE column_name like N'%ABC' DELETE FROM table_name WHERE column_name = N'عرب' 

while in Java there are many ways to connect to SQL and execute queries, such as using the JDBC-ODBC bridge in Windows to connect to SQL, this is how I do it

 Connection cn=null; ResultSet rs=null; PreparedStatement ps=null; String password = *******; String data; public static Connection conn() throws ClassNotFoundException,SQLException { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); String url="jdbc:odbc:library_management"; return DriverManager.getConnection(url,"sa", password ); } // the library_management is the connection name which is made in Windows 'ODBC Data Sources' // Now suppose an event is fired which calls a method given below public void executeQ(){ try { cn = conn(); ps = cn.prepareStatement("SELECT FROM table_name WHERE column_name LIKE N'عربی%'"); rs = ps.executeQuery(); while(rs.next()){ data = rs.getString(1); } } catch (ClassNotFoundException | SQLException ex) { ex.printStackTrace(); } } // this method should work according to the need but since windows default encoding is a non-unicode encoding , therefore it forcibly runs a JAR file on its own encoding so for avoiding this we add a line of code in the main method of the main class of the project. public static void main(String args[]) { System.setProperty("file.encoding","UTF-8"); // Any code } 

Note that to execute any query that does not create a result set, we simply use the PreparedStatement.execute() function

Answer 3

Try using the COLLATE statement as follows:

 CREATE TABLE #book_info ( Book_ID int, Book_Title NVarchar(50) COLLATE Arabic_ci_as, Author NVarchar(50), Publisher NVarchar(50), Publish_Year int, Subject NVarchar(50), Price int, Language NVarchar(50), Edition NVarchar(50), Part int ) Insert into #book_info values(25, N'جاوا', N'Author',N'Publisher',2014,N'Subject',50,N'Language','Latest',5) SELECT * FROM #book_info