How to pass sql server table type definition to user as parameter to storage procedure from java
I am facing the following problem
I am using Spring jdbc and the database is SQL Server 2008 R2
I have a datatype in SQL Server
CREATE TYPE [dbo]. [EIMREQUESTLOG] AS TABLE (
[EIMREQUESTID] [bigint] NULL,
[EIMREQUESTDETAILID] [bigint] NULL,
[REQUESTPACKET] [xml] NULL,
[REQUESTHEADER] [xml] NULL
)
and store a procedure using this data type
ALTER procedure [dbo]. [EIMRequestPacket_Log]
- Add the parameters for the stored procedure here
@pEIMRequestLog DBO.EIMREQUESTLOG readonly,
@pRequestTypeId smallint = NULL,
@pIsInternal char (1) = '0'
AS
DECLARE
@vCreated datetime
BEGIN
-------
END
My implementation
GenericStruct.java
import java.sql.SQLException;
import java.sql.Struct;
import java.util.Map;
public class GenericStruct implements Struct
{
private Object [] attributes = null;
private String typeName = null;
/ *
* Constructor
* /
public GenericStruct () {}
public GenericStruct (String name, Object [] obj)
{
typeName = name;
attributes = obj;
}
public String getSQLTypeName ()
{
return typeName;
}
public Object [] getAttributes ()
{
return attributes;
}
public Object [] getAttributes (Map> map) throws SQLException
{
// this class shouldn't be used if there are elements
// that need customized type mapping.
return attributes;
}
public void setAttributes (Object [] objArray)
{
attributes = objArray;
}
public void setSQLTypeName (String name)
{
typeName = name;
}
}
CustomStoredProcedure.java
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.StoredProcedure;
public class CustomStoredProcedure extends StoredProcedure {
public CustomStoredProcedure (DataSource ds, String procName, SqlParameter [] params) {
setDataSource (ds);
setSql (procName);
for (SqlParameter param: params) {
declareParameter (param);
}
compile ();
}
public Map execute (Map inputMap) {
if (inputMap == null) {
inputMap = new HashMap ();
}
return super.execute (inputMap);
}
}
EIMRequestDAOImpl.java
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.SqlReturnResultSet;
import org.springframework.stereotype.Repository;
@Repository ("eimRequestDAO")
public class EimRequestsDAOImpl extends AbstractJpaDAOImpl
implements EimRequestsDAO {
private static Logger logger = Utils.getLogger (EimRequestsDAOImpl.class);
@Autowired (required = true)
private DataSource dataSource;
public String getEimRequestPackageLog (long requestId, int requestDetailId, String xmlPacket, String xmlHeader) {
final String batchNo = "";
Map results = null;
try {
final String PARAM = "@pEIMRequestLog";
Object [] attributes = new Object [] {requestId, requestDetailId, xmlPacket, xmlHeader};
GenericStruct eimRequestLog = new GenericStruct ();
eimRequestLog.setSQLTypeName ("EIMREQUESTLOG");
eimRequestLog.setAttributes (attributes);
Map inputMap = new HashMap ();
inputMap.put (PARAM, eimRequestLog);
results = new CustomStoredProcedure (dataSource,
"EIMRequestPacket_Log", new SqlParameter [] {
new SqlReturnResultSet ("EimRequest",
new RowMapper () {
public String mapRow (ResultSet rs,
int rowNum) throws SQLException {
return rs.getString ("BATCHNO");
}
}),
new SqlParameter (PARAM, Types.STRUCT)})
.execute (inputMap);
} catch (Exception e) {
e.printStackTrace ();
}
return batchNo;
}
}
** TestServiceImpl.java **
@Transactional
public String saveRequest () throws EimTopUpException {
long requestId = 1000;
int requestDetailId = 3;
String xmlPacket = "";
String xmlHeader = "";
return eimRequestDAO.getEimRequestPackageLog (requestId,
requestDetailId, xmlPacket, xmlHeader);
}
but when i call this method it gives the following exception
org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call EIMRequestPacket_Log (?)}]; SQL state [null]; error code [0]; The conversion from UNKNOWN to STRUCT is unsupported .; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: The conversion from UNKNOWN to STRUCT is unsupported.
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate (AbstractFallbackSQLExceptionTranslator.java:83)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate (AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate (AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:1030)
at org.springframework.jdbc.core.JdbcTemplate.call (JdbcTemplate.java:1064)
at org.springframework.jdbc.object.StoredProcedure.execute (StoredProcedure.java:144)
at sun.reflect.NativeMethodAccessorImpl.invoke0 (Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke (NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke (DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke (Method.java:597)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection (AopUtils.java:318)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint (ReflectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed (ReflectiveMethodInvocation.java:150)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke (TransactionInterceptor.java:110)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed (ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke (JdkDynamicAopProxy.java:202)
at $ Proxy12.saveRequest (Unknown Source)
at Test.main (Test.java:36)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The conversion from UNKNOWN to STRUCT is unsupported
I couldn't solve the problem how to pass the sql server table type definition from java to the user.
+3
zaffargachal
source
to share
No one has answered this question yet
See similar questions:
1
or similar:
7
6
4
3
2
1
1
0
0
0