I need to call a stored procedure using JTDS jdbc driver.
But this stored procedure needs a struct type as a parameter. The Jtds CallableStatement does not have a method that set a struct, so I've tryied cast to a SQLServerCallableStatement and set the structure I want. The code I use is below
Connection con = ds.getConnection();
CallableStatement call = con.prepareCall("{call getErrosSKF ?,?,?,?}");
call.setString("agrupador", agrupador);
call.setDate(2, new Date(dataInicio.getTimeInMillis()));
call.setDate(3, new Date(dataFim.getTimeInMillis()));
SQLServerDataTable areas = new SQLServerDataTable();
areas.addColumnMetadata("id",Types.INTEGER);
for(Integer a : skfIdAreas) {
areas.addRow(a);
}
// generate an exception
((SQLServerCallableStatement) call).setStructured(4, "erroSKFTable", areas);
But I can't cast because its generate an exception saying that
The JtdsCallableStatement
cannot be cast to SqlServerCallableStatemen
.
Is there an alternative I can use?
Support for table-valued parameters (TVPs) is a fairly recent addition to Microsoft's JDBC Driver for SQL Server, and in fact, according to ...
System Requirements for the JDBC Driver
... none of the currently available JDBC drivers from Microsoft will work with JRE_6. You could try and find an ancient JDBC driver from Microsoft, but it wouldn't directly support TVPs anyway, so you might as well stick with jTDS.
Now jTDS doesn't directly support TVPs either, but you can accomplish your task by
Example:
Given an existing reference table named [phonetic] ...
letter word
------ -------
A Alfa
B Bravo
C Charlie
...
Y Yankee
Z Zulu
... a user-defined table type ...
CREATE TYPE [dbo].[LetterListTableType] AS TABLE(
[seq] [int] NOT NULL,
[letter] [varchar](1) NOT NULL,
PRIMARY KEY CLUSTERED
(
[seq] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
... and a stored procedure that takes that table type as a parameter ...
CREATE PROCEDURE [dbo].[GetPhonetic]
@letters dbo.LetterListTableType READONLY
AS
BEGIN
SET NOCOUNT ON;
SELECT l.seq, l.letter, p.word
FROM phonetic p INNER JOIN @letters l on p.letter = l.letter
ORDER BY l.seq;
END
... the following Java code ...
// 0. create some test data
String[] letters = new String[] { "G", "o", "r", "d" };
//
// 1. create temporary table
Statement s = conn.createStatement();
s.executeUpdate("CREATE TABLE #tmp (seq INT IDENTITY PRIMARY KEY, letter VARCHAR(1))");
//
// 2. populate temporary table with letters to look up
PreparedStatement ps = conn.prepareStatement("INSERT INTO #tmp (letter) VALUES (?)");
for (String letter : letters) {
ps.setString(1, letter);
ps.addBatch();
}
ps.executeBatch();
//
// 3. use anonymous code block to pass table-valued parameter to stored procedure
String sql =
"SET NOCOUNT ON; "
+ "DECLARE @tvp dbo.LetterListTableType; "
+ "INSERT INTO @tvp (seq, letter) SELECT seq, letter FROM #tmp; "
+ "EXEC dbo.GetPhonetic @tvp; ";
ResultSet rs = s.executeQuery(sql);
while (rs.next()) {
System.out.printf("%s -> %s%n", rs.getString("letter"), rs.getString("word"));
}
... produces
G -> Golf
o -> Oscar
r -> Romeo
d -> Delta