Multiple SQL statements in a script fail using JDBC

[Andrew-5]

Derby gives error messages when I try to send multiple SQL statements in
a script to a JDBC statement execute command.

JAVA CODE SNIPPIT
//************************
connection conn - getConnection();

String sql = "create table user
(
    k_user_id             INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY
(START WITH 1, INCREMENT BY 1),
    username              VARCHAR(32)           not null,
    passval               VARCHAR(32)           not null,
    primary key (k_user_id)
);
CREATE INDEX idx_user_id ON user (k_user_id);"


// Execute the SQL

                Statement stmt = conn.createStatement();
  //              rset = stmt.executeQuery(sql);
                pf = stmt.execute(sql);


// END OF SNIPPIT
//************************

During the execute, an exception is thown.

However, when the statements (CREATE TABLE, CREATE INDEX) are executed
individually, everything is OK.

How can I give Derby a list of SQL statements from a script and it will
execute them, other than me parsing out SQL statement one prior to
calling the statement.execute().

I would like the database creation to be embedded into this Java
application.  Is there a utility to execute these scripts using a Java
Connection Object?

Also, what is the Derby SQL term used to COMMIT a transaction, it is not
"COMMIT"


Thanks,
Andrew

[Bernt M. Johnsen]

>>>>>>>>>>>> Andrew wrote (2005-05-24 11:23:07):
> [snip]
> How can I give Derby a list of SQL statements from a script and it will
> execute them, other than me parsing out SQL statement one prior to
> calling the statement.execute().

From JDBC you can't. You have either to execute the statements one by
one, or use the batch, but still tou have to add the statements one by
one to the batch (statement.addBatch() and statement.executeBatch()).

> I would like the database creation to be embedded into this Java
> application.  Is there a utility to execute these scripts using a Java
> Connection Object?

Not through a connection object. You can, however, consider using the
tool ij to execute a script of SQL statements.

> Also, what is the Derby SQL term used to COMMIT a transaction, it is not
> "COMMIT"

In JDBC: connection.commit();
In ij: COMMIT;

--
Bernt Marius Johnsen, Database Technology Group,
Sun Microsystems, Trondheim, Norway

[David Van Couvering-2]

Note that you can invoke ij from a Java app, just call
org.apache.derby.tools.ij.Main with your list of arguments.

David

Bernt M. Johnsen wrote:

>>>>>>>>>>>>>Andrew wrote (2005-05-24 11:23:07):
>>
>>[snip]
>>How can I give Derby a list of SQL statements from a script and it will
>>execute them, other than me parsing out SQL statement one prior to
>>calling the statement.execute().
>
>
> From JDBC you can't. You have either to execute the statements one by
> one, or use the batch, but still tou have to add the statements one by
> one to the batch (statement.addBatch() and statement.executeBatch()).
>
>
>>I would like the database creation to be embedded into this Java
>>application.  Is there a utility to execute these scripts using a Java
>>Connection Object?
>
>
> Not through a connection object. You can, however, consider using the
> tool ij to execute a script of SQL statements.
>
>
>>Also, what is the Derby SQL term used to COMMIT a transaction, it is not
>>"COMMIT"
>
>
> In JDBC: connection.commit();
> In ij: COMMIT;
>