Execute a DDL batch

[Spuches Sperlinga]

Hi, thank you for your commitment in UCanAccess project, very useful for me.
I need some help in trying to execute a DDL batch with some statements, but it seems some keyword is not recognized.
My Java code is the following:
...
Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
String database = "jdbc:ucanaccess://" + folder + File.separator + "example.mdb";
connection = DriverManager.getConnection(database);
createIndexes();
...

private void createIndexes() {
Statement ps = connection.createStatement();
ps.addBatch(connection.nativeSQL("DROP INDEX idx_t_gestore_id ON t_gestore;"));
ps.addBatch(connection.nativeSQL("DROP INDEX idx_t_gestore_cfpi ON t_gestore;"));
ps.addBatch(connection.nativeSQL("CREATE UNIQUE INDEX idx_t_gestore_id ON t_gestore (t_gestore_id);"));
ps.addBatch(connection.nativeSQL("CREATE INDEX idx_t_gestore_cfpi ON t_gestore (t_gestore_cfpi) WITH IGNORE NULL;"));
int[] res = ps.executeBatch();
....
}

When I execute this batch script I receive the following error:
net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.6 unexpected token: ON
and if I comment the "DROP" statements I receive:
net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.6 unexpected token: WITH

What's wrong with these statements? I think it would be accepted from Access.
Any help would be appreciated, thanks.

[Gord Thompson]

UCanAccess is currently unable to support DDL to create or drop indexes because Jackcess does not support such operations (ref: here).

[Spuches Sperlinga]

Thank you so much Gord, for your quick answer.

So ... I can't use "CREATE INDEX" to solve my problem. Do you think is there any other approch I can follow to cut down the execution time of queries with inner join?

In my project I have to execute this query:

"SELECT t_impianto_id FROM t_impianto I inner join t_gestore G on I.t_impianto_gestore = G.t_gestore_id where I.t_impianto_kind = ? and G.t_gestore_cfpi = ?"

and it takes 12-16 seconds each time is executed! Definitely too much!

Is there any other approch beyond the "different-query form" approch?
Thank you for your time.

[Gord Thompson]

If your application is running on Windows then you could use the approach described here to perform the DROP INDEX and CREATE INDEX operations. Does your code really drop those indexes and then immediately re-create them? If so, why?

[Spuches Sperlinga]

You are right. Actually that is a short path to be sure to create the indexes with the properties I want; anyway the "drop" part could be removed. Unfortunately my application can run not only on Windows so your suggested approch is not applicable, but i found it very interesting.
However, regarding my problem, I found an acceptable solution splitting the above query in two queries, for which I give a pseudo code:
...
int id1 = "SELECT G.t_gestore_id from t_gestore G where G.t_gestore_cfpi = ?"
...
int id2 = "SELECT I.t_impianto_id FROM t_impianto I where I.t_impianto_kind = ? and I.t_impianto_gestore = id1"
...
Now the execution time is dramatically reduced to 10 milliseconds!!
Thanks once again.