Kewin Björk Nielsen Kewin Björk Nielsen - 10 days ago 7
Java Question

How to set a null table-type parameter when calling a stored procedure with JTDS

How to set the parameter

@p_ItemShelfList [file].[udtt_ItemShelfPair] READONLY

correctly as with

cs.setNull(3, Types.NULL);


When I try that I get a


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


conn class

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

It seems that SQL Server does not like receiving a NULL value for a table-type parameter. Even calling the stored procedure from SSMS like this

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

throws an error

Operand type clash: void type is incompatible with udtt_ItemShelfPair

However, it does accept an empty table as in the following example that uses "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));
}

If you need to stick with jTDS then an alternative approach would be to use something like this:

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();
}
Comments