Enter a File into a hsqldb Blob-Filed

Help
2012-11-04
2014-01-19
  • Hi Community

    I keep getting an error message, when using the method to store a file into a database within a BLOB-Field:

    String name = connection.name;
            String server = connection.server;
            String username = connection.username;
            String password = connection.password;
            File driverFile = connection.driverFile;
            FileInputStream fis = new FileInputStream(driverFile);
    
            /*
             * Method to convert a file input stream into a database BLOB-File-Type.
             * Source: http://www.java2s.com/Code/Java/Database-SQL-JDBC/StoreBLOBsdataintodatabase.htm
             */
            con = internalFunctions.openCon(DB_URL, DB_USER, DB_PASSWD);
            con.setAutoCommit(false);
            String sql = "INSERT INTO T_DBCONNECTION (Name, Server, Username, Password, DriverFile) "
                    + "VALUES (?, ?, ?, ?, ?)";
            PreparedStatement stmt = con.prepareStatement(sql);
            stmt.setString(1, name);
            stmt.setString(2, server);
            stmt.setString(3, username);
            stmt.setString(4, password);
            stmt.setBinaryStream(5, fis, driverFile.length());
            stmt.executeUpdate();
            con.commit();
            fis.close();
    

    Error I get:
    java.sql.SQLDataException: data exception: string data, right truncation

    What I'm doing wrong? I really tried hard and different ways to enter data into this blob field, but nothing worked for me.

    Thank you very much

     
  • Fred Toussi
    Fred Toussi
    2012-11-04

    Please report your CREATE table statement for this table.

    Please use the latest release or snapshot version of HSQLDB and report the version you are using.

     
  • Thank you, Fredt. I used this one:

    CREATE TABLE T_DBCONNECTION
    (
        PK_DBConnection INT IDENTITY,
        Name CHAR(25) NOT NULL,
        Server CHAR(25) NOT NULL,
        Username CHAR(25) NOT NULL,
        Password CHAR(25) NOT NULL,
        DriverFile BINARY VARYING (2)
    );
    

    Please note, that I also tried it with

    DriverFile BLOB
    

    HSQLDB-Version at 2.2.9. Should be the latest, shouldn't it?

    Thanks a lot

     
  • Same mistake when I was trying

    DriverFile BLOB
    
     
  • Fred Toussi
    Fred Toussi
    2012-11-04

    BINARY VARYING (2) means a binary field with a maximum length of 2 bytes.

    BLOB is a blob field with a maximum length of 16 MB. This should work. Try the latest snapshot jar (the one dated 2012-11-01) from the web site if it doesn't work with 2.2.9.

    http://www.hsqldb.org/repos/org/hsqldb/hsqldb/SNAPSHOT/

     
  • Hi Fredt
    Thanks for your fast reply. I keep getting the String-Error even with the newest snapshot:

    java.sql.SQLDataException: data exception: string data, right truncation
        at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
        at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
        at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(Unknown Source)
        at org.hsqldb.jdbc.JDBCPreparedStatement.execute(Unknown Source)
    

    Any other idea? I also tried it no once with the

    stmt.setBlob(5, fis, (int)driverFile.length
    

    and didn't work too.

    Is the procedure generally right and is it a specific problem with the execution with the hsqldb.jar or is this a strange type of code to enter binary files like this into the table? What are your best practices hereby?

    Thank you very much

     
  • Fred Toussi
    Fred Toussi
    2012-11-05

    There are two possible reasons why it doesn't work.

    1. Your old table definition with BINARY VARYING(2) is still there and does not get replaced with BLOB
    2. The file that you are inserting is longer than 16 MB

    You can use BLOB(1G) in the defintion to increase the max blob size to 1 gigabytes.

    An alternative method to insert is stmt.setBlob(5, fis);

    I am attaching a test in the next message.

     
  • Fred Toussi
    Fred Toussi
    2012-11-05

    public static void main(String[] args)
        throws FileNotFoundException, SQLException, IOException,
               ClassNotFoundException {
            String          name       = "Any Name";
            String          server     = "A Server Name";
            String          username   = "Some user";
            String          password   = "a_password";
            File            driverFile = new File("test.csv");
            FileInputStream fis        = new FileInputStream(driverFile);
            Class.forName("org.hsqldb.jdbcDriver");
            Connection con =
                DriverManager.getConnection("jdbc:hsqldb:file:testxyz", "sa", "");
            con.setAutoCommit(false);
            Statement st = con.createStatement();
            st.execute("DROP TABLE T_DBCONNECTION IF EXISTS");
            st.execute(
                "CREATE TABLE T_DBCONNECTION ( PK_DBConnection INT IDENTITY, Name CHAR(25) NOT NULL, Server CHAR(25) NOT NULL, Username CHAR(25) NOT NULL, Password CHAR(25) NOT NULL, DriverFile BLOB(1G))");
            String sql =
                "INSERT INTO T_DBCONNECTION (Name, Server, Username, Password, DriverFile) "
                + "VALUES (?, ?, ?, ?, ?)";
            PreparedStatement stmt = con.prepareStatement(sql);
            stmt.setString(1, name);
            stmt.setString(2, server);
            stmt.setString(3, username);
            stmt.setString(4, password);
            stmt.setBinaryStream(5, fis, driverFile.length());
            stmt.executeUpdate();
            con.commit();
            fis.close();
        }

     
  • Fredt,
    Thanks a lot for your deeper investigation including a sample code. The code provided by you puzzles me: It does not  show off with an error message but if I take a look with the HSQLDB Database Manager and make a

    SELECT * FROM "PUBLIC"."T_DBCONNECTION"
    

    , there is nothing in the database. So it seems to execute the code without adding any line into the db.
    Do you have an idea? Did the above code work on your site as an example?

    The code to create a file and use it then for pushing it into the database basically is right like this:

    File driverFile = new File("C:\\FileStore\\img\\picture.gif");
    FileInputStream fis = new FileInputStream(driverFile);
    // SQL-Statements preparing the string...
    stmt.setBinaryStream(5, fis, (int)driverFile.length());
    

    isn't it?
    Thank you for your time and patience.
    Axylo

     
  • Fred Toussi
    Fred Toussi
    2012-11-05

    If you want the results to be persisted, execute a SHUTDOWN after commit.

            con.commit();
            st.execute("SHUTDOWN");
            fis.close();
    
     
  • Hi Fredt

    Thank you - it works now in an isolated project with a separated database. But when I try to include it into my project, it still gives me the error:

    java.sql.SQLDataException: data exception: string data, right truncation
    

    Code, I use:

    public void addSetting(dbConnection connection) throws IOException,
                SQLException, ClassNotFoundException {
            /*
             * Following the post of Alex Schwerzmann on
             * http://sourceforge.net/projects
             * /hsqldb/forums/forum/73674/topic/6125700/index/page/1
             */
            String name = connection.name;
            String server = connection.server;
            String username = connection.username;
            String password = connection.password;
            String driverPath = connection.driverFilePath.toString();
            System.out.println(driverPath);
            File driverFile = new File(driverPath);
            FileInputStream fis = new FileInputStream(driverFile);
            Class.forName("org.hsqldb.jdbcDriver");
            Connection con = DriverManager
                    .getConnection(
                            "jdbc:hsqldb:file:C:\\ERP2XML\\05-eclipse-project\\hsqldb\\data\\ERP2XML",
                            "SA", "3RP2XML");
            con.setAutoCommit(false);
            Statement st = con.createStatement();
            String sql = "INSERT INTO T_DBCONNECTION (Name, Server, Username, Password, DriverFile) "
                    + "VALUES (?, ?, ?, ?, ?)";
            PreparedStatement stmt = con.prepareStatement(sql);
            stmt.setString(1, name);
            stmt.setString(2, server);
            stmt.setString(3, username);
            stmt.setString(4, password);
            stmt.setBinaryStream(5, fis, driverFile.length());
            stmt.executeUpdate();
            con.commit();
            st.execute("SHUTDOWN");
            fis.close();
            System.out.println("done");
        }
    

    I decided to start a test whith giving the arguments manually:

    public void addSetting(dbConnection connection) throws IOException,
                SQLException, ClassNotFoundException {
            /*
             * Following the post of Alex Schwerzmann on
             * http://sourceforge.net/projects
             * /hsqldb/forums/forum/73674/topic/6125700/index/page/1
             */
            String name = connection.name;
            String server = connection.server;
            String username = connection.username;
            String password = connection.password;
            File driverFile = new File(connection.driverFilePath);
            FileInputStream fis = new FileInputStream(driverFile);
            Class.forName("org.hsqldb.jdbcDriver");
            Connection con = DriverManager.getConnection(
                    "jdbc:hsqldb:file:C:\\ERP2XML\\05-eclipse-project\\hsqldb\\data\\ERP2XML", "SA", "3RP2XML");
            con.setAutoCommit(false);
            Statement st = con.createStatement();
            String sql = "INSERT INTO T_DBCONNECTION (Name, Server, Username, Password, DriverFile) "
                    + "VALUES (?, ?, ?, ?, ?)";
            PreparedStatement stmt = con.prepareStatement(sql);
             name = "Any Name";
             server = "A Server Name";
             username = "Some user";
             password = "a_password";
             driverFile = new File(
                    "C:\\ERP2XML\\05-eclipse-project\\hsqldb\\lib\\servlet-2_3-fcs-classfiles.zip");
             fis = new FileInputStream(driverFile);
    
            stmt.setString(1, name);
            stmt.setString(2, server);
            stmt.setString(3, username);
            stmt.setString(4, password);
            stmt.setBinaryStream(5, fis, driverFile.length());
            stmt.executeUpdate();
            con.commit();
            st.execute("SHUTDOWN");
            fis.close();
            System.out.println("done");
        }
    

    So before executing the database statements, I'm basically overwriting everything I received from my own class which has been overgiven as a parameter for

    public void addSetting(dbConnection connection)
    

    Constructor for dbConnection:

    public dbConnection(String name, String server, String username,
                String plainPassword, String driverFile) throws GeneralSecurityException {
            this.name = name;
            this.server = server;
            this.username = username;
            this.password = AESencrp.encrypt(plainPassword);
            this.driverFilePath = driverFile;
        }
    

    Can you explain that? I really don't see the reason why it works when giving hardcoded parameters instead of getting them out of the GUI. When I'm obligated to code them manually in the java code, it's worthless to me, as I want the user to enter the infos.

    Thank you very much for another round on that
    Axylo

     
  • Forgot to mention, that it obviously works when using the hard coded parameters for the stmt.set…-Statements…

     
  • Fred Toussi
    Fred Toussi
    2012-11-05

    Alex, only you can explain the cause by checking your database and the data you are attempting to insert.

    Check the .script file for the database to make sure you have got a BLOB field. Check the length of various strings that you insert into your table to make sure they are shorter than the column sizes.

    Another way of improving your development effort is using an HSQLDB Server and set silent=false to see on the console what is sent to the database.

     
  • GOT IT!!! Sorry, my mistake was, that another input (connection.server) was including a ";" which it didn't seem to like. Sorry for the work I caused you! It all works now like a charm!!!

     
  • Great support, even for fools like me!!!! THANK YOU

     
  • Fred Toussi
    Fred Toussi
    2012-11-05

    OK. Remember the SHUTDOWN is normally executed when your application finishes accessing the database. Committed data is saved every .5 sec and you can change this by refering to the Guide.