-1

I am using ucanaccess to manage a SQL database in Java. I've done Initial setup using the answer from the question - setting up ucanaccess

After setting up, I am successfully able to Create Tables, Insert into Tables however, I am not able to list the existing tables.

I'm attaching the code that I'm using -

public class Driver {
public static void main(String[] args) {
    try {
        Connection myconnection = DriverManager.getConnection("jdbc:ucanaccess:///usr/local/var/mysql/library/database.accdb");
        Statement stmt = myconnection.createStatement();

        stmt.executeQuery("create table books ( BarCode VARCHAR(255) NOT NULL, title VARCHAR(255) NOT NULL, author VARCHAR(255), PRIMARY kEY (BarCode) );");
        stmt.executeQuery("Insert INTO books VALUES(\"123\", \"Monk\", \"Robin\")");
        ResultSet res = myconnection.getMetaData().getTables(null, null, "books", new String[] {"TABLE"});
        while (res.next()) {
            System.out.println(
                    "   "+res.getString("TABLE_CAT")
                            + ", "+res.getString("TABLE_SCHEM")
                            + ", "+res.getString("TABLE_NAME")
                            + ", "+res.getString("TABLE_TYPE")
                            + ", "+res.getString("REMARKS"));
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}

The part that is listing all the table names is used from the answer - List all the tables

Any help is appreciated.

Edit: I was trying to check if "books" existed in database, hence I had given "books" in tableNamePattern field in search. After pointing out by @Andreas, I've modified it.

maddman
  • 1
  • 1
  • 5
  • 1
    Instead of only trying to list tables named `books`, try listing *all* tables, like your question actually says, by replacing `"books"` with `"%"` – Andreas Oct 28 '18 at 16:19
  • I would guess, that ddl statements like `CREATE TABLE` are not allowed in `executeQuery` - but I'm not 100% sure. – slartidan Oct 28 '18 at 16:41
  • Oh right. You have to use `executeUpdate` instead of `executeQuery` for modification of the database. Good catch by slartidan – UninformedUser Oct 28 '18 at 16:55
  • @AKSW Indeed using **executeQuery** was the main Issue. I changed it will **executeUpdate** and it worked perfectly. – maddman Oct 29 '18 at 18:56
  • @slartidan Thanks to you too! – maddman Oct 29 '18 at 18:57

1 Answers1

0

As pointed out by @AKSW, I should've used executeUpdate instead of executeQuery.

In the Examples here the commands to use different functions is specified for DDL & Queries.

maddman
  • 1
  • 1
  • 5