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
source to share
No one has answered this question yet
See similar questions:
or similar: