StoredProcedures

StoredProcedures
Use

CallableStatement using an IN paramater
CallableStatment cStatement = cConn.prepareCall( "{CALL setPlayerName(?)}"; cStatement.setString("John Doe"); cStatement.execute;
 * Taken from Professional Java JDK 5 edition, page 293

CallableStatement using an OUT parameter
CallableStatement cStatement = cConn.prepareCall( "{CALL setPlayerName(?)}"; cStatement.registerOutParameter(1, java.sql.Types.STRING); cStatement.execute; // Retrieve Player's name String sName = cStatement.getString( 1 );
 * Taken from Professional Java JDK 5 edition, page 293

CallableStatement using an INOUT parameter
CallableStatement cStatement = cConn.prepareCall( "{CALL setPlayerName(?)}"; cStatement.setString("John Doe"); cStatement.registerOutParameter(1, java.sql.Types.STRING); cStatement.execute; // Retrieve Player's name String sName = cStatement.getString( 1 );
 * Taken from Professional Java JDK 5 edition, page 293

Output parameter not allowed as argument list prevents use of RPC
When calling a stored procedure that has output parameters, the driver has to call the procedure using a remote procedure call (RPC). Stored procedures should be invoked using the special JDBC call escape syntax. For example,. In this case the driver will be able to use an RPC succesfully as all the parameters are represented by parameter markers. If however parameters are supplied as a mixture of parameter markers and literals, for example, then the driver is unable to use an RPC and therefore cannot return output parameters. In these circumstances the driver raises an exception and execution fails.

It is possible to use mixed parameter lists to call stored procedures that do not have output parameters. In this case the driver will substitute the parameters locally and use a normal  SQL call; however, this mode of execution is less efficient than an RPC.
 * Taken from jTDS FAQ

Varchar error
Using JDBC to populate varchar fields over 4000 chars long.

Get an error along the lines of

java.sql.SQLException: [Microsoft][SQLServer JDBC Driver][SQLServer] Disallowed implicit conversion from data type ntext to data type varchar, table 'myDb.dbo.table1', column 'largeVarchar'. Use the CONVERT function to run this query

This is caused by the driver making strings ( using statement.setString( myString ) ) of sizes over 4000 a nText instead of a VARCHAR.

To avoid this problem the prepared statement should use the CONVERT command

String insert = "INSERT INTO table1 VALUES ( CONVERT( VARCHAR( 10000 ), ? ) )"; PreparedStatement statement = connection.prepareStatement( insert ); statement.setString( 1, myString ) ; statement.executeUpdate;

Error
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer] The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

Example query
SELECT DiscTitle FROM [serverName\\instance].DVDdb.dbo.user WHERE Username = ?

Solution
check the settings for MSDTC on both servers

On Windows 2003
 * go to Component Services -> Computers -> My Computer -> Properties
 * On the MSDTC tab Network DTC Access should be checked
 * Allow Remote Clients should be checked
 * Allow Inbound should be checked
 * No authentication should be set.

On Windows 200
 * as above, except the properties box is different
 * Default Properties tab
 * Default Authentication Level should be none.

Reference

 * Microsoft KB article

jTDS
jTDS is an open source 100% pure Java (type 4) JDBC 3.0 driver for Microsoft SQL Server (6.5, 7, 2000 and 2005) and Sybase (10, 11, 12, 15).

I have been moving over to using this driver as to performs much faster than the Microsoft drivers.

External Link
jTDS