h2 getGeneratedKeys throws exception

Question

I try to put some Data in my H2 database but I'm a total noob in databases so it throws error over error since more than a hour. Normaly I can fix it somehow but now I got a new problem I try to use getGeneratedKeys() first I tried to use AUTO_INCREMENT(1,1) but that didn't works too function but it won't work rigth.

The exception my programm throws is

org.h2.jdbc.JdbcSQLException: Funktion "GETGENERATEDKEYS" nicht gefunden Function "GETGENERATEDKEYS" not found; SQL statement: insert into logTbl values( getGeneratedKeys(),Webservice->startThread0: Thread0) [90022-173]

an my database function looks like this

    public void createTable(String Log) {

    try {
        Class.forName("org.h2.Driver");
    } catch (ClassNotFoundException e) {
        System.err.println("TREIBER FEHLER");
        e.printStackTrace();
    }
    Connection conn = null;
    try {
        conn = DriverManager.getConnection("jdbc:h2:~/DBtest/Logs");

        Statement stat = conn.createStatement();


        stat.execute("CREATE TABLE IF NOT EXISTS logTbl(ID INT PRIMARY KEY, LOG VARCHAR(255))");

        //stat.execute("insert into test values(1, 'Hello')");
        for (int i = 0; i < 20; i++) {
            stat.execute("insert into logTbl values( getGeneratedKeys()," + Log + ")");
        }
            stat.close();
            conn.close();

    } catch (SQLException e) {
        System.err.println("SQL FEHLER");
        e.printStackTrace();
    }
}

hope you can help me to fix my error as I said I'm totaly new and just had some code example as "tutorial" because I don't found a good tutorial

`getGeneratedKeys()` is a Java method of the `PreparedStatement` class, it's **not** a SQL function, so you can't use it inside a SQL statement. [See the JavaDocs](http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getGeneratedKeys%28%29) and the [JDBC tutorial](http://docs.oracle.com/javase/6/docs/technotes/guides/jdbc/getstart/statement.html#1000569) for details — , Jul 20 '15 at 08:20
See also [here](http://stackoverflow.com/a/25214466/330315) and [here](http://stackoverflow.com/a/18337542/330315) — , Jul 20 '15 at 08:27
ahh nice Thank u :D I'll try to understand but it's not that easy — alovaros, Jul 20 '15 at 08:37

score 4 · Accepted Answer · answered Jul 20 '15 at 08:49

If you want to automatically generate primary key values, you need to first change the definition of your table:

CREATE TABLE IF NOT EXISTS logTbl
(
  ID integer AUTO_INCREMENT PRIMARY KEY, 
  LOG VARCHAR(255)
);

You should also use a PreparedStatement rather than concatenating values.

So your Java code would look something like this:

String insert = "insert into logTbl (log) values(?)";
PreparedStatement pstmt = connection.prepareStatement(insert,  Statement.RETURN_GENERATED_KEYS);
pstmt.executeUpdate();
ResultSet rs = pstmt.getGeneratedKeys();
long id = -1;
while (rs.next()) 
{
   rs.getLong(1);
}

It might be that you need to use the overloaded version of prepareStatement() where you supply the column to be returned. Not sure which one works with H2:

prepareStatement(insert, new String[] {"ID"});

Btw: there is nothing "magic" about 255 as the length of a varchar column. There is no performance difference between varchar(500), varchar(20)or varchar(255). You should use the length that you expect you need, not some "magic" limit you think performs better.

Nice thank you :D nearly works I think the last work I can do by myself hehe :D . Ahh oke didn't knew that thougth the 255 is some kinde of maximum in databases haha :D but i work with logs from log4j so I think 100 will suit. — alovaros, Jul 20 '15 at 09:08

h2 getGeneratedKeys throws exception

1 Answers1