javasql-serverstored-proceduresjdbcjtds

How to pass a table-valued parameter to a stored procedure using jTDS?


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?


Solution

  • 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