Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 330
  • Last Modified:

INSERT BLOB INTO SQL SERVER USING JBOSS

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
rcmb
Asked:
rcmb
  • 6
  • 6
1 Solution
 
aozarovCommented:
When your code worked with tomcat did you use the same jdbc driver (net.sourceforge.jtds.jdbc.Driver)?
0
 
rcmbAuthor Commented:
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
 
aozarovCommented:
what is your getConnection doing? Does it uses JBoss connection pool or it goes directly via DriverManager.getConnection ?
0
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!

 
rcmbAuthor Commented:
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
 
aozarovCommented:
What is  DBController? is it your facade to Jboss connection pool? can you send the code for connMgr.getConnection ?
0
 
rcmbAuthor Commented:
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
 
rcmbAuthor Commented:
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
 
aozarovCommented:
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
 
rcmbAuthor Commented:
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
 
aozarovCommented:
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
 
rcmbAuthor Commented:
You are a god!!! I cannot believe it was this simple.

Thank you so very much!!!!

RCMB
0
 
aozarovCommented:
:-)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now