I'm getting ClassCastException: WrappedPreparedStatementJDK8 cannot be cast to SQLServerPreparedStatement

Question

My environment: Eclipse IDE for Enterprise Java Developers, Version: 2019-09 R (4.13.0)

Server: WildFly 13.0.0.Final

I'm trying to bulk upload the data to Sql Server and the code below works perfectly in my WildFly server with same version but fails in the client's server machine with same wildfly server with java.lang.ClassCastException: org.jboss.jca.adapters.jdbc.jdk8.WrappedPreparedStatementJDK8 cannot be cast to com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement in the statement below:

SQLServerPreparedStatement pStmt = (SQLServerPreparedStatement) con.prepareStatement(execStoredProc);

Here is the full code:

import com.microsoft.sqlserver.jdbc.SQLServerDataTable;
import com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement;

import java.sql.*;
import java.util.ArrayList;

public class DatabaseExtended extends Database {
    public DatabaseExtended(String assetDisposition, String assetDisposition_ds) {
        super(assetDisposition, assetDisposition_ds);
        // TODO Auto-generated constructor stub
    }

    public Object executeEstimateStructured(int iClientID, SQLServerDataTable sourceDataTable) {
        //boolean rs = false;
        ArrayList<String[]> lst = new ArrayList<String[]>();
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection con = DriverManager.getConnection(super.connectionUrl);
            Statement stmt = con.createStatement();
            String execStoredProc = "EXEC UploadEstimate ?,?";
            SQLServerPreparedStatement pStmt = (SQLServerPreparedStatement) con.prepareStatement(execStoredProc);//<--Exception here
            pStmt.setInt(1, iClientID);
            pStmt.setStructured(2, "dbo.UT_Estimate", sourceDataTable);
            ResultSet rs = pStmt.executeQuery();
            int columnCount = rs.getMetaData().getColumnCount();

            while (rs.next()) {

                int i = 1;
                String[] cols = new String[columnCount];
                while (i <= columnCount) {
                    cols[i - 1] = rs.getString(i++);
                }
                lst.add(cols);
            }
            con.close();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        return lst;
    }
}

On googling on this exception, I came across this link that talks about placing the jboss-deployment-structure.xml file in WEB-INF folder with the dependencies mentioned. Below is my jboss-deployment-structure.xml file

<?xml version="1.0" encoding="UTF-8"?>
<jboss-deployment-structure> 
    <deployment>
        <dependencies>
            <module name="org.jboss.ironjacamar.jdbcadapters" slot="main"/>
            <module name="com.microsoft.sqlserver" slot="main"/>
        </dependencies>
    </deployment> 
</jboss-deployment-structure>

I have referenced mssql-jdbc-7.4.1.jre8.jar file in the build path as well as in the Deployment structure so that the dependency is added under web-inf/lib folder.

Now after placing this jboss-deployment-structure.xml file in the web-inf folder and deploying the war file, I get the following exception. I'm not sure if my jboss-deployment-structure.xml is correct and that it is placed in the correct folder or not. Can you please help?

ERROR [io.undertow.request] (default task-5) UT005023: Exception handling request to /AD/control/UploadEstimates: java.lang.IllegalAccessError: tried to access class com.microsoft.sqlserver.jdbc.Util from class com.microsoft.sqlserver.jdbc.SQLServerDataTable
    at com.microsoft.sqlserver.jdbc.SQLServerDataTable.addColumnMetadata(SQLServerDataTable.java:83)
    at com.MyClient.AD.util.UploadEstimateUT.start(UploadEstimateUT.java:66)
    at com.MyClient.AD.util.UploadEstimates.doPost(UploadEstimates.java:107)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
    at io.undertow.servlet.handlers.ServletHandler.handleRequest(ServletHandler.java:74)
    at io.undertow.servlet.handlers.security.ServletSecurityRoleHandler.handleRequest(ServletSecurityRoleHandler.java:62)
    at io.undertow.servlet.handlers.ServletChain$1.handleRequest(ServletChain.java:68)
    at io.undertow.servlet.handlers.ServletDispatchingHandler.handleRequest(ServletDispatchingHandler.java:36)
    at org.wildfly.extension.undertow.security.SecurityContextAssociationHandler.handleRequest(SecurityContextAssociationHandler.java:78)
    at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
    at io.undertow.servlet.handlers.security.SSLInformationAssociationHandler.handleRequest(SSLInformationAssociationHandler.java:132)
    at io.undertow.servlet.handlers.security.ServletAuthenticationCallHandler.handleRequest(ServletAuthenticationCallHandler.java:57)
    at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
    at io.undertow.security.handlers.AbstractConfidentialityHandler.handleRequest(AbstractConfidentialityHandler.java:46)
    at io.undertow.servlet.handlers.security.ServletConfidentialityConstraintHandler.handleRequest(ServletConfidentialityConstraintHandler.java:64)
    at io.undertow.security.handlers.AuthenticationMechanismsHandler.handleRequest(AuthenticationMechanismsHandler.java:60)
    at io.undertow.servlet.handlers.security.CachedAuthenticatedSessionHandler.handleRequest(CachedAuthenticatedSessionHandler.java:77)
    at io.undertow.security.handlers.NotificationReceiverHandler.handleRequest(NotificationReceiverHandler.java:50)
    at io.undertow.security.handlers.AbstractSecurityContextAssociationHandler.handleRequest(AbstractSecurityContextAssociationHandler.java:43)
    at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
    at org.wildfly.extension.undertow.security.jacc.JACCContextIdHandler.handleRequest(JACCContextIdHandler.java:61)
    at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
    at org.wildfly.extension.undertow.deployment.GlobalRequestControllerHandler.handleRequest(GlobalRequestControllerHandler.java:68)
    at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
    at io.undertow.servlet.handlers.ServletInitialHandler.handleFirstRequest(ServletInitialHandler.java:292)
    at io.undertow.servlet.handlers.ServletInitialHandler.access$100(ServletInitialHandler.java:81)
    at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:138)
    at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:135)
    at io.undertow.servlet.core.ServletRequestContextThreadSetupAction$1.call(ServletRequestContextThreadSetupAction.java:48)
    at io.undertow.servlet.core.ContextClassLoaderSetupAction$1.call(ContextClassLoaderSetupAction.java:43)
    at org.wildfly.extension.undertow.security.SecurityContextThreadSetupAction.lambda$create$0(SecurityContextThreadSetupAction.java:105)
    at org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction.lambda$create$0(UndertowDeploymentInfoService.java:1514)
    at org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction.lambda$create$0(UndertowDeploymentInfoService.java:1514)
    at org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction.lambda$create$0(UndertowDeploymentInfoService.java:1514)
    at org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction.lambda$create$0(UndertowDeploymentInfoService.java:1514)
    at io.undertow.servlet.handlers.ServletInitialHandler.dispatchRequest(ServletInitialHandler.java:272)
    at io.undertow.servlet.handlers.ServletInitialHandler.access$000(ServletInitialHandler.java:81)
    at io.undertow.servlet.handlers.ServletInitialHandler$1.handleRequest(ServletInitialHandler.java:104)
    at io.undertow.server.Connectors.executeRootHandler(Connectors.java:360)
    at io.undertow.server.HttpServerExchange$1.run(HttpServerExchange.java:830)
    at org.jboss.threads.ContextClassLoaderSavingRunnable.run(ContextClassLoaderSavingRunnable.java:35)
    at org.jboss.threads.EnhancedQueueExecutor.safeRun(EnhancedQueueExecutor.java:1985)
    at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.doRunTask(EnhancedQueueExecutor.java:1487)
    at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1378)
    at java.lang.Thread.run(Thread.java:748)

Update 16-Mar-2020

This is my updated code using batch statements:

    public String executeInboundEstimateBatchedStoredProc(int iClientID, SQLServerDataTable sourceDataTable) {
        String message="";
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection con = super.getConnection();
            String execStoredProc = "EXEC UploadEstimate ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?";

            PreparedStatement pStmt = con.prepareStatement(execStoredProc);

            Iterator<Entry<Integer, Object[]>> itr1 = sourceDataTable.getIterator();
            while (itr1.hasNext()) {
                Object[] lst=  itr1.next().getValue();              
                int numColumns = lst.length;
                pStmt.setInt(1, iClientID);
                for (int i = 0; i < numColumns; i++) {
                    pStmt.setString(i+2, lst[i].toString());
                }
                // add single insert query
                pStmt.addBatch();
            }

            // Execute batch
            int[] counts = pStmt.executeBatch();

            if (pStmt != null) {
                pStmt.close();
            }
            if (con != null) {
                con.close();
            }
        }
        // Handle any errors that may have occurred.
        catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
            message=e.getMessage();
        }
        return message;
    }

score 0 · Answer 1 · answered Mar 12 '20 at 17:35

0

That's because you shouldn't cast your PreparedStatement but use unwrap. con.prepareStatement(execStoredProc).unwrap(SQLServerPreparedStatement.class);

answered Mar 12 '20 at 17:35

ehsavoie

3,126
1
16
14

I did and removed jboss-deployment-structure.xml, built the war file and now get the following exception: 2020-03-13 08:57:24,950 ERROR [stderr] (default task-4) java.sql.SQLException: IJ031030: Not a wrapper for: com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement 2020-03-13 08:57:24,950 ERROR [stderr] (default task-4) at org.jboss.jca.adapters.jdbc.JBossWrapper.unwrap(JBossWrapper.java:96) – Hemant Mar 13 '20 at 13:32

Sync it · Accepted Answer · 2020-03-12T17:59:24.630

0

U should never have to check the return type of the prepared statement of an connection object. The underlying driver which is platform dependent can provide their own implementation of the prepared statement INTERFACE which is not of the users concern. U should only work with the public methods provided by the interface and not dig any further. In ur case u don't have an error just cast ur statement to the general type. PreparedStatement pStm=con.prepareStatememt(execStoredProc) if ur dealing with an procedure which I assume from your execStoredProc then use con.prepareCall() and store in an Callable Statement type

edited Mar 12 '20 at 17:59

answered Mar 12 '20 at 17:41

Sync it

1,180
2
11
29

Hi R VISHAL, I want to bulk insert records into database and the generic CallableStatment doesn't have setStructured method that I can pass in table. Is there one? – Hemant Mar 13 '20 at 14:09
Yes u can. U first set the parameters for your batch using setXXX methods then use addBatch() which will create a batch of your set parameters. Then call executeBatch () on the statement – Sync it Mar 14 '20 at 18:57
Callable Statement is an subinterface of prepared statement with just extra registerOutXXX methods so the methodology is the same for both – Sync it Mar 14 '20 at 19:05
Hi R VISHAL, Thank you for your directions. I used the default implementation available instead of casting it to SqlServer specific type and then use the addBatch method to accomplish bulk import. PreparedStatement pStmt = con.prepareStatement(execStoredProc); – Hemant Mar 16 '20 at 05:39
Glad I could help u. Just remember that setXXX methods and addBatch() methods saves the values of the statement object to be reused so if u have previous bathes and parameters to discard always use clearbatch() or clearParameters() method before reusing the statement object – Sync it Mar 17 '20 at 19:25

I'm getting ClassCastException: WrappedPreparedStatementJDK8 cannot be cast to SQLServerPreparedStatement

Update 16-Mar-2020

2 Answers2

Linked