Passing a user-defined table type to a SQL Server stored procedure using JDBC
We have this User-Defined-Table type in MS SQL Server:
CREATE TYPE [dbo].[INITVALS_MSG] AS TABLE(
[SDate] [decimal](8, 0) NOT NULL,
[EDate] [decimal](8, 0) NOT NULL,
[PlantCode] [nvarchar](10) NOT NULL,
[LoadType] [nchar](8) NOT NULL,
[Asset] [bigint] NOT NULL
)
and a stored procedure that takes this table as input:
ALTER PROCEDURE [dbo].[RegisterInitAssets]
@initmsg INITVALS_MSG ReadOnly
AS
BEGIN
...
Now I need to call this procedure from java. Can this be done? does jdbc support this?
Any ideas or hints are greatly appreciated!
-------- EDIT I โโhave a corresponding class in java for this type:
public class DBInitialAsset {
private Integer sDate;
private Integer eDate;
private String plantCode;
private String loadType;
private Integer asset;
public DBInitialAsset() {
}
...
}
+1
source to share
2 answers
Yes, it is now possible. Version 6.0 of the Microsoft JDBC Driver for SQL Server added support for table parameters.
The following code example demonstrates how
- use an object
SQLServerDataTable
to store the table data that needs to be passed and - call the method
SQLServerCallableStatement#setStructured
to pass this table to the stored procedure.
SQLServerDataTable sourceDataTable = new SQLServerDataTable();
sourceDataTable.addColumnMetadata("SDate", java.sql.Types.DECIMAL);
sourceDataTable.addColumnMetadata("EDate", java.sql.Types.DECIMAL);
sourceDataTable.addColumnMetadata("PlantCode", java.sql.Types.NVARCHAR);
sourceDataTable.addColumnMetadata("LoadType", java.sql.Types.NCHAR);
sourceDataTable.addColumnMetadata("Asset", java.sql.Types.BIGINT);
// sample data
sourceDataTable.addRow(123, 234, "Plant1", "Type1", 123234);
sourceDataTable.addRow(456, 789, "Plant2", "Type2", 456789);
try (CallableStatement cs = conn.prepareCall("{CALL dbo.RegisterInitAssets (?)}")) {
((SQLServerCallableStatement) cs).setStructured(1, "dbo.INITVALS_MSG", sourceDataTable);
boolean resultSetReturned = cs.execute();
if (resultSetReturned) {
try (ResultSet rs = cs.getResultSet()) {
rs.next();
System.out.println(rs.getInt(1));
}
}
}
See the following MSDN article for details:
+1
source to share
It's actually quite simple, try it like this :-)
connection = dataSource.getConnection();
CallableStatement statement = connection.prepareCall("{? = call dbo.RegisterInitAssets(?)}");
statement.registerOutParameter(1, OracleTypes.CURSOR);//you can skip this if procedure won't return anything
statement.setObject(2, new InitvalsMsg()); //I hope you some kind of representation of this table in your project
statement.execute();
ResultSet set = (ResultSet) statement.getObject(1);//skip it too if its not returning anything
0
source to share