title: Error: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: ACCOUNTS
body: <p>My program is an accounting manager and I have a hsqldb. This is the main class and I am currently doing tests on the program so inside the main function is the place where I have entered test code to insert a value into my database. This is the App class.</p>
  
  <pre><code>package net.samrickard.apps.accounts;
  
  import javafx.application.Application;
  import javafx.fxml.FXMLLoader;
  import javafx.scene.Parent;
  import javafx.scene.Scene;
  import javafx.stage.Stage;
  import net.samrickard.apps.accounts.database.Database;
  
  public class App extends Application {
  
      @Override
      public void start(Stage primaryStage) throws Exception{
          Parent root = FXMLLoader.load(getClass().getResource("UI.fxml"));
          primaryStage.setTitle("Accounting Manager");
          primaryStage.setMinWidth(1000);
          primaryStage.setMinHeight(600);
          primaryStage.setScene(new Scene(root, 1000, 600));
          primaryStage.setMaximized(true);
          primaryStage.show();
  
      }
  
  
      public static void main(String[] args) {
          Database database;
          database = new Database("ACCOUNTS");
          database.createStatement("INSERT INTO PUBLIC.ACCOUNTS VALUES (DEFAULT, 'SamTest','20102846','Sam',1000000.25)");
          database.disconnectDatabase();
          launch(args);
      }
  }
  </code></pre>
  
  <p>Now this is the database class:</p>
  
  <pre><code>package net.samrickard.apps.accounts.database;
  
  import java.sql.*;
  
  public class Database {
  
      private String dbname;
      public Connection con;
      private String username;
      private String password;
  
      public Database(String dbname){
          connectToDatabase(dbname,"SA","");
      }
  
      /**
       * This is the code to connect to the database. The database must be in the res/database folder
       * @param dbname This is the database name
       * @param username This is the username of the database
       * @param password This is the password of the database
       * @return has the SQL been successful
       */
  
      public boolean connectToDatabase(String dbname, String username, String password){
          this.username=username;
          this.password=password;
          try{
              Connection con = DriverManager.getConnection("jdbc:hsqldb:file:database/"+dbname, username, password);
              this.con = con;
              this.dbname = dbname;
              System.out.println("Connection to database "+dbname+" was successful.");
              return true;
          }catch(SQLException e){
             System.out.println("Error connecting to database "+dbname+". Error: "+e);
             return false;
          }
      }
  
      /**
       * This is the function used to disconnect from a database
       * @return has the SQL been successful
       */
  
      public boolean disconnectDatabase(){
          try{
              con.commit();
              con =     DriverManager.getConnection("jdbc:hsqldb:file:database/"+dbname+";shutdown=true", username, password);
              System.out.println("Disconnection from database "+dbname+" was successful.");
              return true;
          }catch(SQLException e){
              System.out.println("Error disconnecting from database "+dbname+". Error: "+e);
              return false;
          }
      }
  
      /**
       * This is the code to run a SQL statement to the database
       * @param sqlstatement this is the statement that will be run but cannot be a query as the query needs a result set. Use the query function for running a query to get a result set.
       * @return has the SQL been successful
       */
  
      public boolean createStatement(String sqlstatement){
          try{
              Statement statement = con.createStatement();
              statement.execute(sqlstatement);
              System.out.println("Successfully created statement: "+sqlstatement);
              return true;
          }catch (SQLException e) {
              System.out.println("Failed to create and execute statement for following reason: \n"+e);
              return false;
          }
      }
  
      /**
       * This is used to run an SQL query. This function is used to obtain results from a statement.
       * @param sqlQuery this is the Query that will be executed
       * @return has the SQL been successful
       */
  
      public ResultSet createQuery(String sqlQuery){
          ResultSet results;
          try{
              Statement statement = con.createStatement();
              results = statement.executeQuery(sqlQuery);
              System.out.println("Successfully created Query: "+sqlQuery);
              return results;
          }catch (SQLException e) {
              System.out.println("Failed to create and execute query for following reason: \n"+e);
              return null;
          }
      }
  }
  </code></pre>
  
  <p>I also have the script file for the database creation:</p>
  
  <pre><code>SET DATABASE UNIQUE NAME HSQLDB5E3E63937E
  SET DATABASE GC 0
  SET DATABASE DEFAULT RESULT MEMORY ROWS 0
  SET DATABASE EVENT LOG LEVEL 0
  SET DATABASE TRANSACTION CONTROL LOCKS
  SET DATABASE DEFAULT ISOLATION LEVEL READ COMMITTED
  SET DATABASE TRANSACTION ROLLBACK ON CONFLICT TRUE
  SET DATABASE TEXT TABLE DEFAULTS ''
  SET DATABASE SQL NAMES FALSE
  SET DATABASE SQL REFERENCES FALSE
  SET DATABASE SQL SIZE TRUE
  SET DATABASE SQL TYPES FALSE
  SET DATABASE SQL TDC DELETE TRUE
  SET DATABASE SQL TDC UPDATE TRUE
  SET DATABASE SQL TRANSLATE TTI TYPES TRUE
  SET DATABASE SQL TRANSLATE TTI TYPES TRUE
  SET DATABASE SQL CONCAT NULLS TRUE
  SET DATABASE SQL UNIQUE NULLS TRUE
  SET DATABASE SQL CONVERT TRUNCATE TRUE
  SET DATABASE SQL AVG SCALE 0
  SET DATABASE SQL DOUBLE NAN TRUE
  SET FILES WRITE DELAY 500 MILLIS
  SET FILES BACKUP INCREMENT TRUE
  SET FILES CACHE SIZE 10000
  SET FILES CACHE ROWS 50000
  SET FILES SCALE 32
  SET FILES LOB SCALE 32
  SET FILES DEFRAG 0
  SET FILES NIO TRUE
  SET FILES NIO SIZE 256
  SET FILES LOG TRUE
  SET FILES LOG SIZE 50
  ALTER CATALOG PUBLIC RENAME TO APPDATABASECATALOG
  CREATE USER SA PASSWORD DIGEST 'd41d8cd98f00b204e9800998ecf8427e'
  ALTER USER SA SET LOCAL TRUE
  CREATE SCHEMA PUBLIC AUTHORIZATION DBA
  SET SCHEMA PUBLIC
  CREATE MEMORY TABLE PUBLIC.TRANSACTIONS(TRANSACTIONID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,TRANSACTIONDATE DATE NOT NULL,DESCRIPTION VARCHAR(50),ACCOUNTID INTEGER NOT NULL,COSTCENTRE VARCHAR(20) NOT NULL,ISCREDIT BOOLEAN DEFAULT FALSE NOT NULL,TRANSACTIONVALUE DOUBLE DEFAULT 0.00 NOT NULL,TAG VARCHAR(30),LINK INTEGER,CHECKED BOOLEAN DEFAULT FALSE NOT NULL)
  ALTER TABLE PUBLIC.TRANSACTIONS ALTER COLUMN TRANSACTIONID RESTART WITH 0
  CREATE UNIQUE INDEX TRANSACTIONS_TRANSACTIONID_UINDEX ON PUBLIC.TRANSACTIONS(TRANSACTIONID)
  CREATE MEMORY TABLE PUBLIC.ACCOUNTS(ACCOUNTID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,ACCOUNTNAME VARCHAR(15) NOT NULL,ACCOUNTNUMBER VARCHAR(8) NOT NULL,PROVIDER VARCHAR(20),ACCOUNTVALUE DOUBLE DEFAULT 0.00 NOT NULL)
  ALTER TABLE PUBLIC.ACCOUNTS ALTER COLUMN ACCOUNTID RESTART WITH 1
  CREATE UNIQUE INDEX ACCOUNTS_ACCOUNTID_UINDEX ON PUBLIC.ACCOUNTS(ACCOUNTID)
  ALTER TABLE PUBLIC.TRANSACTIONS ADD CONSTRAINT TRANSACTIONS_ACCOUNTS_ACCOUNTID_FK FOREIGN KEY(TRANSACTIONID) REFERENCES PUBLIC.ACCOUNTS(ACCOUNTID)
  ALTER SEQUENCE SYSTEM_LOBS.LOB_ID RESTART WITH 1
  SET DATABASE DEFAULT INITIAL SCHEMA PUBLIC
  GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.SQL_IDENTIFIER TO PUBLIC
  GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.YES_OR_NO TO PUBLIC
  GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.TIME_STAMP TO PUBLIC
  GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CARDINAL_NUMBER TO PUBLIC
  GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CHARACTER_DATA TO PUBLIC
  GRANT DBA TO SA
  SET SCHEMA SYSTEM_LOBS
  INSERT INTO BLOCKS VALUES(0,2147483647,0)
  </code></pre>
  
  <p>now when I run this I get confirmation of the application connecting to the database and disconnecting but in between these the code that is run to insert some values does not successfully insert the new record and I get this error message:</p>
  
  <pre><code>Connection to database ACCOUNTS was successful.
  Failed to create and execute statement for the following reason: 
  java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: ACCOUNTS
  Disconnection from database ACCOUNTS was successful.
  </code></pre>
  
  <p>Also here is the stack trace:'</p>
  
  <pre><code>java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: ACCOUNTS
      at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
      at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
      at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
      at org.hsqldb.jdbc.JDBCStatement.execute(Unknown Source)
      at net.samrickard.apps.accounts.database.Database.createStatement(Database.java:65)
      at net.samrickard.apps.accounts.App.main(App.java:28)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:498)
      at com.sun.javafx.application.LauncherImpl.launchApplicationWithArgs(LauncherImpl.java:389)
      at com.sun.javafx.application.LauncherImpl.launchApplication(LauncherImpl.java:328)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:498)
      at sun.launcher.LauncherHelper$FXHelper.main(LauncherHelper.java:767)
  Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: ACCOUNTS
      at org.hsqldb.error.Error.error(Unknown Source)
      at org.hsqldb.error.Error.error(Unknown Source)
      at org.hsqldb.ParserDQL.readTableName(Unknown Source)
      at org.hsqldb.ParserDQL.readRangeVariableForDataChange(Unknown Source)
      at org.hsqldb.ParserDML.compileInsertStatement(Unknown Source)
      at org.hsqldb.ParserCommand.compilePart(Unknown Source)
      at org.hsqldb.ParserCommand.compileStatements(Unknown Source)
      at org.hsqldb.Session.executeDirectStatement(Unknown Source)
      at org.hsqldb.Session.execute(Unknown Source)
      ... 15 more
  </code></pre>
  
  <p>I have check multiple times that the table does exist. I use IntelliJ idea and this has a database manager and when I run the same SQL directly into the database it is run fine so I know the problem is not the SQL code. </p>