java - 使用 JTDS 调用存储过程时如何设置空表类型参数

Question

如何设置参数@p_ItemShelfList [file].[udtt_ItemShelfPair] READONLY与

一样正确

cs.setNull(3, Types.NULL);

当我尝试时，我得到了

Operand type clash: nvarchar is incompatible with udtt_ItemShelfPair

           protected String doInBackground(String... params) {

        if (DBcard.trim().equals("") || DBshelf.trim().equals(""))
            z = getString(R.string.Invalid_Credentials);
        else {
            try {
                Connection con = connectionClass.CONN();
                if (con == null) {
                    z = getString(R.string.Forbindelses_fejl);
                } else {
                    String itemcard = DBcard;
                    {
                        if (itemcard.substring(0, 1).startsWith("K")) {
                            itemcard = itemcard.substring(1);
                        } else {
                            itemcard = itemcard;//.substring(0));
                        }
                    }
                    String itemshelf = DBshelf;
                    {
                        if (itemshelf.substring(0, 1).startsWith("R")) {
                            itemshelf = "" + itemshelf.substring(1);
                        } else {
                            itemshelf = "" + itemshelf;//.substring(0));
                        }
                    }
                    String doerTicket;
                    doerTicket = setingPreferences.getString("doerTicket", "");
                    String sql = "{call dbo.usp_assignPartToShelf(?,?,?,?,?)}";
                    try (CallableStatement s = con.prepareCall(sql)) {
                        s.setString(1, itemshelf);
                        s.setString(2, itemcard);

                        SQLServerDataTable dt = new SQLServerDataTable();
                        dt.addColumnMetadata("ItemNumber", Types.INTEGER);
                        dt.addColumnMetadata("ShelfNumber", Types.INTEGER);
                        ((SQLServerCallableStatement) s).setStructured(3, "dbo.udtt_ItemShelfPair", dt);

                        s.registerOutParameter(4, Types.INTEGER);
                        s.setString(5, doerTicket);

                        boolean hasResultSet = s.execute();
                        if (hasResultSet) {
                            try (ResultSet rs = s.getResultSet()) {
                                rs.next();
                                System.out.printf("ResultSet data: %s%n", rs.getString(1));
                            }
                        }
                        System.out.printf("Output parameter data: %d%n", s.getInt(4));
                    }
                }
            } catch (Exception ex) {
                isSuccess = false;
                z = getString(R.string.Exceptions) + "L2)";
                Log.e("MYAPP", "exception", ex);
            }
        }
        return z;
    }
}

ALTER PROCEDURE [file].[usp_assignPartToShelf]
    @p_ItemNumber VARCHAR ( 20 ) = NULL
    , @p_ShelfNumber NVARCHAR ( 100 ) = NULL
    , @p_ItemShelfList [file].[udtt_ItemShelfPair] READONLY
    , @p_UpdatedItems INT = 0 OUTPUT
    , @p_DoerTicket VARCHAR ( 200 )
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @doerUserID INT
            , @doerCompanyID INT
    EXEC system.usp_validateAuthenticationTicket @p_Ticket = @p_DoerTicket
                                                , @p_UserID = @doerUserID OUTPUT
                                                , @p_CompanyID = @doerCompanyID OUTPUT

    DECLARE @res INT
            , @id INT

    SET @p_UpdatedItems = 0

    IF ( EXISTS ( SELECT TOP 1 1
                FROM @p_ItemShelfList ) )
    BEGIN
        DECLARE cur_ISL CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
        FOR SELECT i.ID, ISNULL ( ti.ShelfNumber, '' )AS ShelfNumber
            FROM @p_ItemShelfList AS ti
                INNER JOIN [file].Item AS i ON ( ti.ItemNumber = i.ItemNumber )
                                            AND ( i.Type IN ( 'P', 'N' ) )
            WHERE ( i.Status < 100 ) --100: Reserved

        OPEN cur_ISL

        FETCH NEXT FROM cur_ISL 
        INTO @id, @p_ShelfNumber

        WHILE ( @@FETCH_STATUS = 0 )
        BEGIN
            PRINT @id
            EXEC @res = [file].usp_iudPart @p_ID = @id
                                        , @p_ShelfNumber = @p_ShelfNumber
                                        , @p_DoerTicket = @p_DoerTicket
            PRINT @res
            IF ( @res <> 0 )
                BREAK;

            SET @p_UpdatedItems += 1

            FETCH NEXT FROM cur_ISL 
            INTO @id, @p_ShelfNumber
        END

        CLOSE cur_ISL;
        DEALLOCATE cur_ISL;
    END
    ELSE
    BEGIN
        SELECT @id = i.ID
        FROM [file].Item AS i 
        WHERE i.Company_ID = @doerCompanyID
            AND i.ItemNumber = @p_ItemNumber
            AND ( i.Type IN ( 'P', 'N' ) )

        IF ( @@ROWCOUNT <> 1 )
        BEGIN
            --RAISERROR ( 'DBException_InvalidPartNumber', 16, 1 )
            RETURN 10
        END

        EXEC @res = [file].usp_iudPart @p_ID = @id
                                    , @p_ShelfNumber = @p_ShelfNumber
                                    , @p_DoerTicket = @p_DoerTicket


        SET @p_UpdatedItems += 1
    END

    RETURN @res 
END

连接类

 import android.annotation.SuppressLint;
    import android.app.Activity;
    import android.content.Context;
    import android.content.SharedPreferences;
    import android.os.StrictMode;
    import android.util.Log;
    import java.sql.SQLException;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import com.microsoft.sqlserver.jdbc.SQLServerDriver;
    /**
     * Created by kewin on 07-07-2016.
     */
    public class ConnectionClass {
        Context context;
        private SharedPreferences setingPreferences;
        String ip;
        String classs = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
        String db;
        String un;
        String password;
        public ConnectionClass (Context context)
        {
            this.context = context;
        }
        @SuppressLint("NewApi")
        public Connection CONN() {
            setingPreferences = context.getSharedPreferences("Settings", Activity.MODE_PRIVATE);
            ip = setingPreferences.getString("server", "");
            db = setingPreferences.getString("db", "");
            un = setingPreferences.getString("dbuser", "");
            password = setingPreferences.getString("dbpass", "");
            StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder()
                    .permitAll().build();
            StrictMode.setThreadPolicy(policy);
            Connection conn = null;
            String ConnURL = null;
            try {

                Class.forName(classs);
                ConnURL = "jdbc:sqlserver://" + ip + ";"
                        + "databaseName=" + db + ";user=" + un + ";password="
                        + password + ";";
                conn = DriverManager.getConnection(ConnURL);
            } catch (SQLException se) {
                Log.e("ERRO", se.getMessage());
            } catch (ClassNotFoundException e) {
                Log.e("ERRO", e.getMessage());
            } catch (Exception e) {
                Log.e("ERRO", e.getMessage());
            }
            return conn;
        }
    }

Answer 1

SQL Server 似乎不喜欢接收表类型参数的 NULL 值。甚至像这样从 SSMS 调用存储过程

EXEC [dbo].[usp_assignPartToShelf] @p_ItemShelfList = NULL

抛出错误

Operand type clash: void type is incompatible with udtt_ItemShelfPair

但是，它确实接受空表，如以下使用“Microsoft JDBC Driver 6.0 for SQL Server”的示例所示

String sql = "{call dbo.usp_assignPartToShelf(?,?,?,?,?)}";
try (CallableStatement s = conn.prepareCall(sql)) {
    s.setString(1, "testItemNumber");
    s.setString(2, "testShelfNumber");

    SQLServerDataTable dt = new SQLServerDataTable();
    dt.addColumnMetadata("ItemNumber", Types.INTEGER);
    dt.addColumnMetadata("ShelfNumber", Types.INTEGER);
    ((SQLServerCallableStatement) s).setStructured(3, "dbo.udtt_ItemShelfPair", dt);

    s.registerOutParameter(4, Types.INTEGER);
    s.setString(5, "testDoerTicket");

    boolean hasResultSet = s.execute();
    if (hasResultSet) {
        try (ResultSet rs = s.getResultSet()) {
            rs.next();
            System.out.printf("ResultSet data: %s%n", rs.getString(1));
        }
    }
    System.out.printf("Output parameter data: %d%n", s.getInt(4));
}

如果您需要坚持使用 jTDS，那么另一种方法是使用 PreparedStatement 来运行一些 T-SQL，其中使用 EXEC 语句，只需省略 @p_ItemShelfList 来自参数列表，如下所示:

String sql = 
        "SET NOCOUNT ON; " +
        "DECLARE @upd INT; " +
        "EXEC dbo.usp_assignPartToShelf " + 
                "@p_ItemNumber=?, " + 
                "@p_ShelfNumber=?, " + 
                "@p_UpdatedItems=@upd OUTPUT, " + 
                "@p_DoerTicket=?;" +
        "SELECT @upd AS UpdatedItems;";
try (PreparedStatement s = conn.prepareStatement(sql)) {
    s.setString(1, "testItemNumber");
    s.setString(2, "testShelfNumber");
    s.setString(3, "testDoerTicket");
    ResultSet rs = s.executeQuery();  // above T-SQL always returns at least one ResultSet
    rs.next();
    System.out.println(rs.getString(1));  // display something from ResultSet
    if (s.getMoreResults()) {
        System.out.printf(
                "INFO:%n" + 
                "  A second ResultSet was found.%n" + 
                "  The previous ResultSet was returned by the stored procedure.%n" + 
                "  Getting next ResultSet ...%n", 
                "");
        rs = s.getResultSet();
        rs.next();
        System.out.println(rs.getString(1));
    }
    rs.close();
}