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...
(I am going to assume that you have declared
Locationtype
as a table variable. The use ofReadOnly
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:
(I found it more helpful to call
executeQuery()
on the PreparedStatement thanexecute()
. I didn't have your sproc code, so I madeOrganization_Insert
raise an error whose message contained the number of rows in the table variable. UsingexecuteQuery()
was necessary to get this error message thrown in a SQLException: withexecute()
no exception was thrown.)