PreparedStatement Disallowed implicit conversion

[Goody]

PreparedStatment seems to have a feature that when setting
a string that is over 4000
during execution it converts the string into NTEXT instead
of VARCHAR leaving me with the exception:

java.sql.SQLException: [Microsoft][SQLServer JDBC
Driver][SQLServer]Disallowed implicit conversion from data
type ntext to data type varchar, table
'testDB.dbo.testtablex', column 'bigstringx'. Use the
CONVERT function to run this query.
at
com.microsoft.jdbc.base.BaseExceptions.createException(Unknown
Source)
at
com.microsoft.jdbc.base.BaseExceptions.getException(Unknown
Source)
at
com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown
Source)
at
com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown
Source)
at
com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unknown
Source)
at
com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown
Source)
at
com.microsoft.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown
Source)
at
com.microsoft.jdbc.base.BaseStatement.commonExecute(Unknown
Source)
at
com.microsoft.jdbc.base.BaseStatement.executeInternal(Unknown
Source)
at
com.microsoft.jdbc.base.BasePreparedStatement.execute(Unknown
Source)
at com.inventa.schema.Test.main(Test.java:71)

For example

PreparedStatement ps = conn.prepareStatement("insert into
testtablex values(?)");
ps.setString(1, bigString);
ps.execute();

The column of the table I am trying to insert into is a
VARCHAR(7500) .

if I insert it like this

PreparedStatement ps = conn.prepareStatement("insert into
testtablex values(?)");
ps.setString(1, bigString.substring(0, 4000));
ps.execute();

it works and if I insert like this it works as well.

PreparedStatement ps = conn.prepareStatement("INSERT INTO
testtablex VALUES (CONVERT(VARCHAR(7500), ?)");
ps.setString(1, bigString);
ps.execute();

Unfortunatly I am looking for a solution where I can change
the behavior of the conversion of strings longer than 4000
to be VARCHAR for the PreparedStatement.

Has any one seen this problem or solved this problem
without switching blobs or ntext?

[Joseph Weinstein]

Goody wrote:

> PreparedStatment seems to have a feature that when setting
> a string that is over 4000
> during execution it converts the string into NTEXT instead
> of VARCHAR leaving me with the exception:
>
> java.sql.SQLException: [Microsoft][SQLServer JDBC
> Driver][SQLServer]Disallowed implicit conversion from data
> type ntext to data type varchar, table
> 'testDB.dbo.testtablex', column 'bigstringx'. Use the
> CONVERT function to run this query.

Hi. Add this property to the connection properties:

p.put("sendStringParametersAsUnicode", "false");

I just ran this code fine:

Properties p = new Properties();
p.put("user", "sa");
p.put("password", "");
p.put("sendStringParametersAsUnicode", "false");

Driver d = new com.microsoft.jdbc.sqlserver.SQLServerDriver();
c = DriverManager.getConnection("jdbc:microsoft:sqlserver://JOE:1433", p);

Statement s = c.createStatement();
try {s.executeUpdate("drop table mytable");}catch(Exception ignore){}

s.executeUpdate("create table mytable ( mt_name varchar(8000))");

PreparedStatement ps = c.prepareStatement("insert into mytable values(?)");

String foo = "";
for (int i = 0; i < 8000;i++) foo += "a";

ps.setString(1, foo );
ps.execute();

Joe Weinstein at BEA

[goody]

Hey,

I really didn't want to have to decompile the microsoft
jar file. I guess I was hoping for somthing a little more
elegant.

[Joseph Weinstein]

goody wrote:

I'm not sure what you mean. That property and it's purpose is
valid. If it's not documented by MS, then it's a documentation
bug. I understand that having to decompile a driver to find it's
features is inelegant, but it only has to be done once if we post
our findings.
I hope that adding the property is elegant enough for you.
It does work, and is precisely for this purpose.

Joe Weinstein at BEA