StoredProcedures
From Null-pointer
Contents |
StoredProcedures
Use CallableStatment
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, {call sp_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 {call sp_example('test',?)}, 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 "execute procedure" 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();
Cross server query error
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
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.
