?
Solved

INSERT BLOB INTO SQL SERVER USING JBOSS

Posted on 2005-03-31
12
Medium Priority
?
320 Views
Last Modified: 2012-05-05
Hello experts,

Here goes--

To start with this code works fine in tomcat and inserts all data to database including file upload.

I have multi-part form that allows the user to upload a file and write it to the database. The problem I am having is writing to the database when I put the code in my JBoss server. I am not sure if it is driver related or code related. Note: Not using JBoss is not an option!

my driver is: net.sourceforge.jtds.jdbc.Driver

If I remove the blob from the sql statement all writes to the database fine.
(Example:
("INSERT INTO [File] (Fl_Name, Ft_ID, Fl_Description) VALUES ('" + name + "', "+(type==null?def_type:type)+", '" + desc + "')");
)

Here is my code:
String def_type = rs.getString("type");
String type = rs.getString("def_type");
ArrayList sql_params = new ArrayList();
Hashtable p = new Hashtable();
p.put("type", new Integer(Types.BLOB));
p.put("value", src);
sql_params.add(p);
flag = dbcon.execSQL("INSERT INTO [File] " +
            "(Fl_Name, Ft_ID, Fl_Description, Fl_Source) " +
            "VALUES ('" + name + "', "+(type==null?def_type:type)+"," +
                  "'" + desc + "',?)",sql_params);
if (!flag) {
      setErrorMsg("Adding of file failed");
      setErrorDetails("");
}

} catch (Exception e) {
setErrorMsg("SQLError" + e.getMessage());
setErrorDetails(e.getMessage() + " " + sql);
return false;
} finally {
try {
      rs.close();
} catch (Exception ee) {
}
}
return flag;
}

This is the execSQL code from the statement above:
    public boolean execSQL(String sql, ArrayList params){
             boolean blBool = false;
            try      {
            getConnection();
            PreparedStatement prstmt = con.prepareStatement(sql);
            Hashtable item;
            for(int i=0;i<params.size();i++){
                item = (Hashtable)params.get(i);
                Integer itype = (Integer)item.get("type");
                Object ivalue = item.get("value");
                if(itype==null || ivalue==null)continue;
                int type = itype.intValue();
                switch(type){
                    case  Types.BLOB:{
                        ByteArrayInputStream is = new ByteArrayInputStream((byte[])ivalue);
                        prstmt.setBinaryStream(i+1,is,is.available());
                    }
                    default: prstmt.setObject(i+1,item.get("value"),type);
                }
            }
            prstmt.execute();
                 blBool = true;
            } catch(Exception e)      {
                    System.out.println("Exception at" + sql + "     : " + e);
                        return false;
            } finally{
            freeConnection();
        }
            return blBool;
    }

Please help -- I am pulling what little hair on my head I have left out -- I may have to start pulling from other areas :=)

RCMB
0
Comment
Question by:rcmb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 15

Expert Comment

by:aozarov
ID: 13672743
When your code worked with tomcat did you use the same jdbc driver (net.sourceforge.jtds.jdbc.Driver)?
0
 
LVL 12

Author Comment

by:rcmb
ID: 13672762
Yes I did. I have made no changes to the code in JBoss. I just created a war file and placed in my server.

RCMB
0
 
LVL 15

Expert Comment

by:aozarov
ID: 13672876
what is your getConnection doing? Does it uses JBoss connection pool or it goes directly via DriverManager.getConnection ?
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 12

Author Comment

by:rcmb
ID: 13672920
Here is the code for getConnection

    protected void getConnection(){
        if(connMgr == null){
           connMgr = DBController.getInstance();
            }
              //get the name of the connection pool
            String strPoolName  = connMgr.getPoolName();
            //get the connection from the pool
            con = connMgr.getConnection(strPoolName);
            if(con==null){
                  System.out.println("Error in Database connection");
                  return;
        }
    }


I use a db.prop file to set all parameters and a DBController.java to establish the connection.

RCMB
0
 
LVL 15

Expert Comment

by:aozarov
ID: 13672997
What is  DBController? is it your facade to Jboss connection pool? can you send the code for connMgr.getConnection ?
0
 
LVL 12

Author Comment

by:rcmb
ID: 13673110
I am pasting my DBController (actual name of file is DBControllerImpl.java)

Is is my Jboss connection pool. The code for connMgr.getConnection was pasted in my last entry.

Here is DBControllerImpl.java

package mil.navy.snadis.db.model;

import java.io.ByteArrayInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Hashtable;

import mil.navy.snadis.db.api.DBController;


public class DBControllerImpl {
      private Connection con;
      private DBController connMgr;
      String strPoolName       = null;

      public DBControllerImpl (){
        try {
                  //create an object of DBController class.
                  connMgr = DBController.getInstance();
            }catch (Exception e){
                  System.out.println("DB Connection problem");
            }
    }

    protected void getConnection(){
        if(connMgr == null){
           connMgr = DBController.getInstance();
            }
              //get the name of the connection pool
            String strPoolName  = connMgr.getPoolName();
            //get the connection from the pool
            con = connMgr.getConnection(strPoolName);
            if(con==null){
                  System.out.println("Error in Database connection");
                  return;
        }
    }

    protected void freeConnection(){
        if(con!=null && strPoolName!=null)
            try{
                connMgr.freeConnection(strPoolName , con);
            }catch(Exception e){}
    }

    public boolean execSQL(String sql){
             boolean blBool = false;
            try      {
            getConnection();
            Statement st = con.createStatement();
                st.executeUpdate( sql );
                 blBool = true;
            } catch(Exception e)      {
                    System.out.println("Exception at" + sql + "     : " + e);
                        return false;
            } finally{
            freeConnection();
        }
            return blBool;
    }

    public boolean execSQL(String sql, ArrayList params){
             boolean blBool = false;
            try      {
            getConnection();
            PreparedStatement prstmt = con.prepareStatement(sql);
            Hashtable item;
            for(int i=0;i<params.size();i++){
                item = (Hashtable)params.get(i);
                Integer itype = (Integer)item.get("type");
                Object ivalue = item.get("value");
                if(itype==null || ivalue==null)continue;
                int type = itype.intValue();
                switch(type){
                    case  Types.BLOB:{
                        ByteArrayInputStream is = new ByteArrayInputStream((byte[])ivalue);
                        prstmt.setBinaryStream(i+1,is,is.available());
                    }
                    default: prstmt.setObject(i+1,item.get("value"),type);
                }
            }
            prstmt.execute();
                 blBool = true;
            } catch(Exception e)      {
                    System.out.println("Exception at" + sql + "     : " + e);
                        return false;
            } finally{
            freeConnection();
        }
            return blBool;
    }

      public ResultSet execQuery(String queryString ) {
            ResultSet rs = null;
            try      {
                getConnection();
                PreparedStatement pstmt = con.prepareStatement(queryString);
                      rs = pstmt.executeQuery();
            }catch(Exception e)      {
                  System.out.println("Exception at.." + queryString + "     : " + e);
                  return null;
        }finally{
            freeConnection();
        }
            return rs;
      }

/*
    public PreparedStatement getPreparedStatement(String sql_template){
       boolean blBool = true;
            try      {
            if(connMgr == null){
                         connMgr = DBController.getInstance();
                  }
            //get the name of the connection pool.
                  String strPoolName  = connMgr.getPoolName();
                  //get the connection from the pool.
                  con = connMgr.getConnection(strPoolName);
                  if(con==null){
                                    System.out.println("Error in Database connection");
                                    return null;
                  }else{
                        Statement st = con.prepareStatement(sql_template);
                                    blBool = true;
                                    connMgr.freeConnection(strPoolName , con);
                          }
            } catch(Exception e)      {
                    System.out.println("Exception at" + insertString + "     : " + e);
                        return false;
            }
            return blBool;
    }
*/
}
0
 
LVL 12

Author Comment

by:rcmb
ID: 13673122
Here is DBController.java

package mil.navy.snadis.db.api;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Date;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.MissingResourceException;
import java.util.ResourceBundle;
import java.util.StringTokenizer;
import java.util.Vector;


/**
 * This class is a Singleton that provides access to one or many
 * connection pools defined in a Property file. A client gets
 * access to the single instance through the static getInstance()
 * method and can then check-out and check-in connections from a pool.
 * When the client shuts down it should call the release() method
 * to close all open connections and do other clean up.
*/

public class DBController {
    static private DBController instance;       // The single instance
    static private int clients;

    private Vector drivers = new Vector();
    private PrintWriter log;
    private Hashtable pools = new Hashtable();
    private String strPoolName = null;
    private String strUser     = null;
    private String strPassword = null;
    private String strMaxConn = null;
    private String strUrl = null;
      private String strDriverClasses = null;

      private Exception exception = null;

    /**
     * Returns the single instance, creating one if it's the
     * first time this method is called.
     *
     * @return DBController The single instance.
     */
    static synchronized public DBController getInstance() {
        if (instance == null) {
            instance = new DBController();
        }
        clients++;
        return instance;
    }

    /**
     * A private constructor since this is a Singleton
     */
    private DBController() {

             init();

            System.out.println("DBController Started Successfully");
    }

    /**
     * Returns a connection to the named pool.
     *
     * @param name The pool name as defined in the properties file
     * @param con The Connection
     */
    public void freeConnection(String name, Connection con) {
        DBConnectionPool pool = (DBConnectionPool) pools.get(name);
        if (pool != null) {
            pool.freeConnection(con);
        }
    }

    /**
     * Returns an open connection. If no one is available, and the max
     * number of connections has not been reached, a new connection is
     * created.
     *
     * @param name The pool name as defined in the properties file
     * @return Connection The connection or null
     */
    public Connection getConnection(String name) {
            try {
              DBConnectionPool pool = (DBConnectionPool) pools.get(name);
              if (pool != null) {
                  return pool.getConnection();
              }
              else {
                        return null;
                  }
            }
            catch (Exception e) {
                  System.out.println("Error in getting connection from the Pool : " + e);

              return null;
            }
    }

    /**
     * Returns an open connection. If no one is available, and the max
     * number of connections has not been reached, a new connection is
     * created. If the max number has been reached, waits until one
     * is available or the specified time has elapsed.
     *
     * @param name The pool name as defined in the properties file
     * @param time The number of milliseconds to wait
     * @return Connection The connection or null
     */
    public Connection getConnection(String name, long time) {
        try {
                  DBConnectionPool pool = (DBConnectionPool) pools.get(name);
                  if (pool != null) {
                        return pool.getConnection(time);
                  }
                  else {
                        return null;
                  }
            }
            catch (Exception e) {
                  System.out.println("Error in getting connection from the Pool : " + e);

                  return null;
            }
    }

    /**
     * Closes all open connections and deregisters all drivers.
     */
    public synchronized void release() {

        if (--clients != 0) {
            return;
        }

        Enumeration allPools = pools.elements();

        while (allPools.hasMoreElements()) {
            DBConnectionPool pool = (DBConnectionPool) allPools.nextElement();
            pool.release();
        }

        Enumeration allDrivers = drivers.elements();

        while (allDrivers.hasMoreElements()) {

            Driver driver = (Driver) allDrivers.nextElement();

            try {
                DriverManager.deregisterDriver(driver);

                        System.out.println("Deregistered JDBC driver " + driver.getClass().getName());
            }
            catch (SQLException e) {

                        System.out.println("Deregistered JDBC driver " + driver.getClass().getName());

            }
        }
    }

    /**
     * Creates instances of DBConnectionPool based on the properties.
     * A DBConnectionPool can be defined with the following properties:
     * <PRE>
     *       url         The JDBC URL for the database
     *       user        A database user (optional)
     *       password    A database user password (if user specified)
     *       maxconn     The maximal number of connections (optional)
     *  poolname    The connection pool name
     * </PRE>
     *
     * @param props The connection pool properties
     */
    private void createPools() {

            if (strUrl == null) {

                  System.out.println("No URL specified for " + strPoolName);

            }
            int    intMax;
            try {
                  intMax = Integer.valueOf(strMaxConn).intValue();
            }
            catch (NumberFormatException e) {

                  System.out.println("Invalid maxconn value " + strMaxConn + " for " + strPoolName+e);

                  intMax = 0;
            }
            DBConnectionPool pool =      new DBConnectionPool(strPoolName, strUrl, strUser, strPassword, intMax);
            pools.put(strPoolName, pool);


            System.out.println("Initialized pool " + strPoolName);

    }

      /**
       * The getException method returns the exception occured during processing.
       *  @return java.lang.Exception
       */
      public Exception getException() {
        return exception;
    }

    /**
     * Loads properties and initializes the instance with its values.
     */
    private void init() {
        try {
                     //InputStream is = getClass().getResourceAsStream("db.properties");
                  ResourceBundle rsBundle = ResourceBundle.getBundle("db.api.db");
                  strPoolName       = rsBundle.getString("poolname");
                  strUrl                  = rsBundle.getString("url");
                  strUser             = rsBundle.getString("user");
                  strPassword       = rsBundle.getString("password");
                  strMaxConn       = rsBundle.getString("maxconn");
                  strDriverClasses = rsBundle.getString("drivers");
            //Properties dbProps = new Properties();
            //dbProps.load(is);
                  loadDrivers();
                  createPools();
        }
        catch (MissingResourceException e) {
                  this.exception = new IOException("Properties file not found in dbconnection");

                  System.out.println("Can't read the properties file. Make sure db.properties is in the CLASSPATH"+e);

        }
    }

    /**
     * Loads and registers all JDBC drivers. This is done by the
     * DBController, as opposed to the DBConnectionPool,
     * since many pools may share the same driver.
     *
     * @param props The connection pool properties
     */
    private void loadDrivers() {
        StringTokenizer st = new StringTokenizer(strDriverClasses);
        while (st.hasMoreElements()) {
            String driverClassName = st.nextToken().trim();
            try {
                Driver driver = (Driver)
                Class.forName(driverClassName).newInstance();
                DriverManager.registerDriver(driver);
                drivers.addElement(driver);

                        System.out.println("Registered JDBC driver " + driverClassName);
            }
            catch (Exception e) {

                        System.out.println("Can't Registered JDBC driver " + driverClassName+e);
            }
        }
    }

    /**
     * This inner class represents a connection pool. It creates new
     * connections on demand, up to a max number if specified.
     * It also makes sure a connection is still open before it is
     * returned to a client.
     */
    class DBConnectionPool {
        private int checkedOut;
        private Vector freeConnections = new Vector();
        private int maxConn;
        private String name;
        private String password;
        private String URL;
        private String user;

        /**
         * Creates new connection pool.
         *
         * @param name The pool name
         * @param URL The JDBC URL for the database
         * @param user The database user, or null
         * @param password The database user password, or null
         * @param maxConn The maximal number of connections, or 0
         *   for no limit
         */
        public DBConnectionPool(String name, String URL, String user, String password,
                int maxConn) {
            this.name = name;
            this.URL = URL;
            this.user = user;
            this.password = password;
            this.maxConn = maxConn;
        }

        /**
         * Checks in a connection to the pool. Notify other Threads that
         * may be waiting for a connection.
         *
         * @param con The connection to check in
         */
        public synchronized void freeConnection(Connection con) {
            // Put the connection at the end of the Vector
            freeConnections.addElement(con);
            checkedOut--;
            notifyAll();
        }

        /**
         * Checks out a connection from the pool. If no free connection
         * is available, a new connection is created unless the max
         * number of connections has been reached. If a free connection
         * has been closed by the database, it's removed from the pool
         * and this method is called again recursively.
         */
        public synchronized Connection getConnection() {
            Connection con = null;
            if (freeConnections.size() > 0) {
                // Pick the first Connection in the Vector
                // to get round-robin usage
                con = (Connection) freeConnections.firstElement();
                freeConnections.removeElementAt(0);
                try {
                    if (con.isClosed()) {

                        // Try again recursively
                        con = getConnection();
                    }
                }
                catch (SQLException e) {

                    con = getConnection();
                }
            }
            else if (maxConn == 0 || checkedOut < maxConn) {
                con = newConnection();
            }
            if (con != null) {
                checkedOut++;
            }
            return con;
        }

        /**
         * Checks out a connection from the pool. If no free connection
         * is available, a new connection is created unless the max
         * number of connections has been reached. If a free connection
         * has been closed by the database, it's removed from the pool
         * and this method is called again recursively.
         * <P>
         * If no connection is available and the max number has been
         * reached, this method waits the specified time for one to be
         * checked in.
         *
         * @param timeout The timeout value in milliseconds
         */
        public synchronized Connection getConnection(long timeout) {
            long startTime = new Date().getTime();
            Connection con;
            while ((con = getConnection()) == null) {
                try {
                    wait(timeout);
                }
                catch (InterruptedException e) {
                                    //write the status to a log file.
                              //      AdminLogger.fatal("Interrupted while waiting for connection : " , e);
                        }
                if ((new Date().getTime() - startTime) >= timeout) {
                    // Timeout has expired
                    return null;
                }
            }
            return con;
        }

        /**
         * Closes all available connections.
         */
        public synchronized void release() {
            Enumeration allConnections = freeConnections.elements();
            while (allConnections.hasMoreElements()) {
                Connection con = (Connection) allConnections.nextElement();
                try {
                    con.close();

                }
                catch (SQLException e) {

                }
            }
            freeConnections.removeAllElements();
        }

        /**
         * Creates a new connection, using a userid and password
         * if specified.
         */

        private Connection newConnection() {
            Connection con = null;
            try {
                if (user == null) {
                    con = DriverManager.getConnection(URL);
                }
                else {
                    con = DriverManager.getConnection(URL, user, password);
                }


                        System.out.println("Created a new connection in pool " + name);
            }
            catch (SQLException e) {

                              System.out.println("Can't create a new connection for " + URL +e);
                return null;
            }
            return con;
        }
    }

        /**
         * Returns the pool name.
         * @return strPoolName java.lang.String
        */
            public String getPoolName()
            {
                  return strPoolName;
            }

}
0
 
LVL 15

Expert Comment

by:aozarov
ID: 13673511
Ok, the code answers my questions. Basically you are not using JBoss connection pools (but rather built your own).
Your connection to the database is done directly via DriverManager.getConnection.
I don't see any JBoss code here and the way you interact with the driver should not be effected by JBoss (regarding transaction, security, ...)
What is extactly the problem that you see?
Do you get some sort of exception? If so can you send the stackTrace?
0
 
LVL 12

Author Comment

by:rcmb
ID: 13673547
This is the error code I see:

Exception atINSERT INTO [File] (Fl_Name, Ft_ID, Fl_Description, Fl_Source) VALUES ('check_functions.js', 1,'Test of insert function',?)     : java.sql.SQLException: Internal error.  Unrecognized type 2004

Here is the stack trace:

12:22:12,562 INFO  [STDOUT] Registered JDBC driver net.sourceforge.jtds.jdbc.Driver
12:22:12,662 INFO  [STDOUT] Initialized pool techunified
12:22:12,662 INFO  [STDOUT] DBController Started Successfully
12:22:12,712 INFO  [STDOUT] Created a new connection in pool techunified
12:22:33,882 INFO  [STDOUT] Created a new connection in pool techunified
12:22:34,093 INFO  [STDOUT] Created a new connection in pool techunified
12:22:34,093 INFO  [STDOUT] Exception atINSERT INTO [File] (Fl_Name, Ft_ID, Fl_Description, Fl_Source) VALUES ('check_functions.js', 1,'Test of insert function',?)     : java.sql.SQLException: Internal error.  Unrecognized type 2004
0
 
LVL 15

Accepted Solution

by:
aozarov earned 2000 total points
ID: 13673700
Aren't you missing a break here:
 switch(type){
                    case  Types.BLOB:{
                        ByteArrayInputStream is = new ByteArrayInputStream((byte[])ivalue);
                        prstmt.setBinaryStream(i+1,is,is.available());
                    }
                    // I think you want to have a break as shown below
                    break;
                    default: prstmt.setObject(i+1,item.get("value"),type);
                }
0
 
LVL 12

Author Comment

by:rcmb
ID: 13673841
You are a god!!! I cannot believe it was this simple.

Thank you so very much!!!!

RCMB
0
 
LVL 15

Expert Comment

by:aozarov
ID: 13674014
:-)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
Suggested Courses
Course of the Month13 days, 19 hours left to enroll

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question