Setting generator to specific value

I”m converting a database and I need to set a generator to a value prior to generating new numbers.

Say the generator is called gtest. I’ve tried the following:

PreparedStatement nrps = csource.prepareStatement("alter sequence gtest restart with 100");

and

PreparedStatement nrps = csource.prepareStatement("set gopdracht to 100");

Option 1 gives me invalid syntax and option 2 gives unkown system variable.

What am I doing wrong?

Answer

I am beginning to suspect you are using a connection to a non-firebird database as there is no Firebird error message “Unknown system variable” (that is however a MySQL error).

I have tested it with Jaybird 2.2.4 on Firebird 2.5 and both ALTER SEQUENCE ... RESTART WITH ... and SET GENERATOR ... TO ... work without error.

I used the following sample program:

private static final String fileName = "D:/data/db/alterseq.fdb";

public static void main(String[] args) throws Exception {
    FBManager manager = new FBManager();
    manager.setCreateOnStart(true);
    manager.setDropOnStop(true);
    manager.setUserName("sysdba");
    manager.setPassword("masterkey");
    manager.setFileName(fileName);
    manager.start();
    try (Connection con = DriverManager.getConnection(
            "jdbc:firebirdsql://localhost/" + fileName, "sysdba", "masterkey")) {
        try (Statement ddlStmt = con.createStatement()) {
            ddlStmt.execute("CREATE SEQUENCE seq_test");
        }
        try (PreparedStatement pstmt = con.prepareStatement(
                "ALTER SEQUENCE seq_test RESTART WITH 100")) {
            pstmt.execute();
        }
        try (PreparedStatement pstmt = con.prepareStatement(
                "SET GENERATOR seq_test TO 200")) {
            pstmt.execute();
        }
    } finally {
        manager.stop();
    }
}