pete loveall

I am using the JDBC SP3 driver. My tables use varchar columns (no Unicode
due to way data is received). I have set
SendStringParametersAsUnicode=false. Everything works well except...

Updating a varchar column with byte values sometimes fails. The code does a
SELECT, then an updateRow() if the data is found. Below is the two batches
sent by JDBC to the SQL Server (happens with both SelectMethod=Cursor and
SelectMethod=Direct, I used the SelectMethod=Direct batches since they are
simpler to read).

One thing I find very wierd: why does the UPDATE have all the extra stuff
added to the WHERE clause? Seems like a waste and probably what is killing
the update.

exec sp_executesql N'SELECT * FROM APRSPosits WHERE callsignSSID=@P1', N'@P1
varchar(8000) ', 'VK3JTM-9'

exec sp_executesql N'UPDATE APRSPosits set "ReportTime" = @P1 , "Latitude" =
@P2 , "Longitude" = @P3 , "Course" = @P4 , "Speed" = @P5 , "Altitude" = @P6
, "Packet" = @P7 , "Icon" = @P8 WHERE "CallsignSSID" = @P9 and "ReportTime"
= @P10 and "Latitude" = @P11 and "Longitude" = @P12 and "Course" = @P13 and
"Speed" = @P14 and "Altitude" = @P15 and "Packet" = @P16 and "Icon" = @P17
', N'@P1 datetime ,@P2 float ,@P3 float ,@P4 smallint ,@P5 int ,@P6 int ,@P7
varbinary(8000) ,@P8 varchar(8000) ,@P9 varchar(8000) ,@P10 datetime ,@P11
float ,@P12 float ,@P13 int ,@P14 int ,@P15 int ,@P16 varchar(8000) ,@P17
varchar(8000) ', 'Aug 2 2004 1:34:32:153PM', -3.707516666666667e+001,
1.427748333333333e+002, 148, 45, 715,
0x564B334A544D2D393E5357503455312C564B335250472D32 2C564B335242552D312A2C7141
6F2C564B334A464B3A27464A4D6F7B4C522F5D22363B7D6D69 74737562697368692020636861
6C6C656E676572, '/R', 'VK3JTM-9', 'Aug 2 2004
1:33:31:370PM', -3.707233333333334e+001, 1.427733333333333e+002, 6, 0,
1860758, 'VK3JTM-9>SWP4S4,VK3RPG-2,VK3RBU-1*,qAo,VK3JFK:''FJDl
"R/]"6S`¨´f`F}mitsubishi challenger', '/R'

I guess this is a bug report. This does work properly with the Sun
JDBC-ODBC bridge.

Pete Loveall
AME Corp.

pete loveall

It appears that the JDBC driver does not support updateable Resultsets. It
tries to emulate them by using a text UPDATE statement. This would explain
why apostrophes and other special characters in updates breaks the drivers.
Guess I will use the jTDS driver which is also free, and properly supports
PreparedStatements and updateable ResultSets.

Pete Loveall
AME Corp.

pete loveall

This driver does not implement prepared statements in a compliant manner.
Instead of using parameterized operations where there is no conversion of
strings, the driver converts all parameters to string values for use in text
SELECT, INSERT, and UPDATE statements. Very ugly and non-compliant. This
prevents proper storage and retrieval of strings with apostrophes, using
updateBytes() for varchar fields, etc.

The jTDS driver works properly with PreparedStatement constructs and I will
stick with that.

Pete Loveall