How to pass the values of user defined datatype variable to MS SQL from java using spring jdbc store procedure

Question

I have the below MS SQL store procedure with the user defined variable (@Location

CREATE PROCEDURE [Organization].[Organization_Insert]   
(  
 @OrganizationID NVARCHAR(256),  
 @Location Locationtype ReadOnly
)

@Location has following attributes: OrganizationSubID, LocationCode

Am using the below java class to invoke the store procedure,

class OrganizationInsertProcedure extends StoredProcedure {

  private final String[] outputParameters = new String[] {OUTPUT};

  public PlanActivityInsertProcedure(DataSource dataSource) {
    super(dataSource, "Organization_Insert");

    declareParameter(new SqlParameter("@OrganizationID", Types.NVARCHAR));
    declareParameter(new SqlParameter("@Location", Types.ARRAY, "Locationtype"));

    compile();
  }

Here, my question is, how to construct the @Location variable from java and pass it to the MS SQL database. (am using sqljdbc4.jar driver to connect the database)

I whole day Googled and tried many implementations and nothing paid off.

Please someone shed some lights on this...

Answer 1

(I am going to assume that you have declared Locationtype as a table variable. The use of ReadOnly on the stored procedure's parameter hints at this.)

According to this post on the SQL Server forums, the Microsoft SQL Server JDBC driver doesn't currently support table variables. So, it seems you have to build up a string of T-SQL which declares a table variable, inserts data into the table and then executes the stored procedure.

In 'plain' JDBC, i.e. without using Spring, the code to do this looks something like the following:

    int numRows = /* number of rows in table variable */;

    StringBuilder sqlBuilder = new StringBuilder("DECLARE @Location AS LocationType;");
    for (int i = 0; i < numRows; ++i) {
        sqlBuilder.append(
            "INSERT INTO @Location (OrganizationSubID, LocationCode) VALUES (?, ?);");
    }

    sqlBuilder.append("EXEC [Organization].[Organization_Insert] ?, @Location;");

    PreparedStatement stmt = connection.prepareStatement(sqlBuilder.toString());
    for (int i = 0; i < numRows; ++i) {
        stmt.setString(i * 2 + 1, /* OrganizationSubID for row i */);
        stmt.setString(i * 2 + 2, /* LocationCode for row i  */);
    }

    stmt.setString(numRows * 2 + 1, /* Organization ID */);

    ResultSet resultSet = stmt.executeQuery();
    resultSet.close();

(I found it more helpful to call executeQuery() on the PreparedStatement than execute(). I didn't have your sproc code, so I made Organization_Insert raise an error whose message contained the number of rows in the table variable. Using executeQuery() was necessary to get this error message thrown in a SQLException: with execute() no exception was thrown.)