Microsoft KB Archive/904790

Knowledge Base

The getBytes function incorrectly converts the raw bytes in the varchar data type column to the lower byte of the Unicode representation in the SQL Server 2000 Driver for JDBC

Article ID: 904790

Article Last Modified on 8/19/2005

---

APPLIES TO

• Microsoft SQL Server 2000 Driver for JDBC

---

SYMPTOMS

In Microsoft SQL Server 2000, an array of raw bytes is stored in a varchar data type column. When you use the SELECT statement to query a table, the Microsoft SQL Server 2000 Driver for JDBC processes the request to return a byte array. In the SQL Server 2000 Driver for JDBC, the getBytes function incorrectly converts the raw bytes in the varchar data type column to the lower byte of the Unicode representation.

CAUSE

This problem occurs because the hexadecimal code for the data that is read back from the table becomes 0x1A, 0xCC. The hexadecimal code 0x82 in the 1252 (Latin I) code page corresponds to 201A in Unicode. The hexadecimal code 0x8f does not exist in the 1252 (Latin I) code page. Therefore, the hexadecimal code 0x8f is converted to CCCC in Unicode. Additionally, the SQL Server 2000 Driver for JDBC retrieves the lower byte of each Unicode character and puts the bytes together in the byte array.

WORKAROUND

To work around this problem, explicitly cast the column data as the varbinary data type. Because the SQL Server 2000 Driver for JDBC knows that the column data is the varbinary data type, the SQL Server 2000 Driver for JDBC does not perform the conversion.

MORE INFORMATION

The SQL Server 2000 Driver for JDBC follows these steps:

1. The driver uses the getBytes function to retrieve column data.
2. The driver converts each byte of data to a 2-byte Unicode value according to the associated code page.
3. The driver truncates the lower byte and puts the bytes together to return the byte array.

According to the Java programming language specifications, the getBytes function retrieves a column value in the current row of the result set as a byte array. The bytes represent the raw values that are returned by the SQL Server 2000 Driver for JDBC. Therefore, the conversion performed by the SQL Server 2000 Driver for JDBC is unnecessary and is not correct. Instead, the getBytes function should read the raw bytes and store the raw bytes in the byte array.

Steps to reproduce the problem

1. In SQL Query Analyzer, create a table by using the following code.

   CREATE TABLE [dbo].[BinaryTable]
   (
   [id] [int] IDENTITY (1, 1) NOT NULL ,
   [vchar] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
   )

2. Compile and run the following Java code.

   import java.*;
   import java.sql.*;
   import java.util.*; 
   import java.text.*;
   
   public class repro
   {
       public static void main(String[] args) throws Exception
       {
           // Load the SQL Server 2000 Driver for JDBC.
           Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
   
           // Connect to SQL Server.
           String url = "jdbc:microsoft:sqlserver://<Server>:1433;DatabaseName=<DatabaseName>";
           Connection conn = DriverManager.getConnection(url, "<UserId>", "<PassWd>");
   
           // Reproduce the problem.
           String command = "INSERT INTO BinaryTable(vchar) VALUES(?)";
           byte[] bytes = new byte[] {(byte)0x8f, (byte)0x82};
   
           PreparedStatement ps = conn.prepareStatement(command);
           ps.setBytes(1, bytes);
           ps.executeUpdate();
   
           //String SQL = "SELECT CAST(vchar AS VARBINARY) FROM BinaryTable WHERE id = 1";
           String SQL = "SELECT vchar FROM BinaryTable WHERE id = 1";
           PreparedStatement st = conn.prepareStatement(SQL);
           ResultSet r = st.executeQuery();
   
           ResultSetMetaData rsmd = r.getMetaData();
           String CType= rsmd.getColumnTypeName(1); 
           System.out.println("Column Type:" + CType);
           System.out.println("Dec:");
   
           r.next(); 
   
           // Read bytes from Binary, VarChar, text, image column.
           byte[] arr = r.getBytes(1);
   
           for (int k=0; k<arr.length; k++)
           {
           int val = (arr[k]<0) ? 256+arr[k] : arr[k]; 
           System.out.print(val + " : ");
           Integer intObj = new Integer(val);
           String hexString = intObj.toHexString(val);
           System.out.println(hexString);
           }
   
           // Clean up the objects.
           r.close(); r=null;
           ps.close(); ps=null;
           st.close(); st=null;
           conn.close(); conn=null;
       }
   }   

   Note To use this code sample, replace the following placeholders:

   • Replace Server by using the name of the instance of SQL Server.
   • Replace DatabaseName by using the name of the database.
   • Replace UserId by using your user ID.
   • Replace PassWd by using your password.

REFERENCES

For more information about JDBC, click the following article number to view the article in the Microsoft Knowledge Base:

313100 How to get started with Microsoft JDBC

Keywords: kbtshoot kbprb KB904790

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.