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





All Articles