Sql queries not executed on H2 database

Question

Recently i changed from HSQLDB to H2, changed a bit of code and my queries stopped executing.

I test my SQL code with RazorSQL where i try to access my DB from , bud to my suprise there is no table created,no errors thrown no null pointers , valid sql, db file created - everything seems to be running alright bud no content in database whatsoever.

Here are a crucial parts of my database access/creation.

I use connector class to create connect to database

public class H2DatabaseConnector {

    private Connection connection;
    private Statement statement;
    private ResultSet resultSet;


    public static final String[] PREP_STATEMENTS = new String[]{};

    public static final String DB_FILE_NAME = File.separator + "dboc";
    public static final String DB_DIR = Info.OC_DIR + "oc_database\\";

    static {
        try {
            Class.forName("org.h2.Driver");
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        }
    }

    public H2DatabaseConnector(String username, String password) {
        try {
            openConnection(username, password);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

.....
//data credentials are correct
 private void openConnection(String username, String password) throws SQLException {
        connection = DriverManager.getConnection("jdbc:h2:file:" + DB_DIR + DB_FILE_NAME+";DATABASE_TO_UPPER=false", username, password);
        statement = connection.createStatement();
    }

  public void execute() {
    }

}

And utility class where i execute my sql commands

public class DbUtil {


    public static final void createUsersTable(){
         new H2DatabaseConnector(Info.Db.DB_MAIN_USERNAME,Info.Db.DB_MAIN_PASSWORD) {

            @Override
            public void execute() {
                try {
                    getStatement().execute("CREATE TABLE users(name VARCHAR(255) NOT NULL,password VARCHAR(255) NOT NULL,email VARCHAR(255));");
                    System.out.println("Table Created users");
                    getStatement().executeUpdate("INSERT INTO users VALUES ('sa','sa',NULL);");
                    closeConnection();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }

        }.execute();
    } 

}

Execution is going thru as expected bud no table is created. Why is this happening? There are no errors shown,and sql syntax is correct since when i open database in **RazorSQL and create/insert table there everything works.**

Any ideas? Im literally stuck on this for a whole day.

Answer 1

After further examination of my code : code in original post is working and no change was needed.

Problem that i was facing was a way i tryed to connect to my database from RazorSQL , JDBC URL that i used didnt matched exact URL that i used in my code i ommited ;DATABASE_TO_UPPER=false which in turn let me connect to it bud Schema of db was not changed. After using correct JDBC URL, i was able to see table with data in RazorSQL.

If you have same problem like i did try to use JDBC URL that you use in code, also with options afterwards.

Failed URL

jdbc:h2:file:C:\Users\tomas\.OpenChannel\oc_database\dboc

Correct url

jdbc:h2:file:C:\Users\tomas\.OpenChannel\oc_database\dboc;DATABASE_TO_UPPER=false

I woud have never expected that this coud have been a problem. :D

Answer 2

It's possible that re-using the Statement could be causing you issues, but since I don't have a fully runnable example to go on, it's difficult to be sure...

So, I did this really quick test (using 1.4.182)...

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class Test {

    public static void main(String[] args) {
        try {
            Class.forName("org.h2.Driver");
            try (Connection con = DriverManager.getConnection("jdbc:h2:file:./Test;DATABASE_TO_UPPER=false", "sa", "sa")) {

                try (Statement stmt = con.createStatement()) {
                    stmt.execute("CREATE TABLE if not exists users(name VARCHAR(255) NOT NULL,password VARCHAR(255) NOT NULL,email VARCHAR(255))");
                    con.commit();
                }

                try (PreparedStatement stmt = con.prepareStatement("INSERT INTO users (name, password, email) VALUES (?,?,?)")) {
                    stmt.setString(1, "sa");
                    stmt.setString(2, "sa");
                    stmt.setString(3, null);
                    int rows = stmt.executeUpdate();
                    System.out.println(rows + " where inserted");
                    con.commit();
                }

                try (PreparedStatement stmt = con.prepareStatement("select * from users")) {
                    try (ResultSet rs = stmt.executeQuery()) {
                        while (rs.next()) {
                            String name = rs.getString(1);
                            String password = rs.getString(2);
                            String email = rs.getString(3);
                            System.out.println(name + "; " + password + "; " + email);
                        }
                    }
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

}

Which when run the first time outputs...

1 where inserted
sa; sa; null

When run the second time outputs...

1 where inserted
sa; sa; null
sa; sa; null

Personally, I would isolate each Statement to a single task, in fact, I tend to isolate each Connection as well, but that's because I'm paranoid ;)