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 (904790)

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.