Bdtjtk

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

1. creating a temporary table,


2. populating the temporary table with your data, and then


3. using an anonymous code block to populate a TVP from the temporary table and then call the stored procedure.

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