Tukukkk

I can't work out why the following program is unable to locate tables in my derby database:

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.util.Properties;

import java.util.logging.Level;

import java.util.logging.Logger;



public class NdConnect

{

  public final static String SETUP_FILE_PATH = "/AppData/Local/NewdawnTest"; 

  private static final String CONNECTION_URL = "jdbc:derby:" + "C:/Users/" + System.getenv("USERNAME") + SETUP_FILE_PATH + "db" + ";create=true";

  private static Connection conn = null;

  private final static Properties dbProperties  = new Properties();

  private static PreparedStatement pstmtSelectTxns;   



  public static void connect() {

    try {  

      conn = DriverManager.getConnection(CONNECTION_URL  + ";create=true", dbProperties);

      pstmtSelectTxns = conn.prepareStatement("SELECT * from TXNS");

      System.out.println("Connected OK to " + CONNECTION_URL);   

    }

    catch (SQLException sqle) {

      System.out.println("SQL exception");

      System.out.println("Connected NOK:The connection URL is " + CONNECTION_URL);

      Logger.getLogger(NdConnect.class.getName()).log(Level.SEVERE, null, sqle);

    } 

  }



  public static void disconnect() 

  {

    try {

      if (conn != null) {

        conn.close();

        conn = null;

        System.out.println("0048 NDC OK:DB closed ");  

      }

    } catch (SQLException sqle) {

      Logger.getLogger(NdConnect.class.getName()).log(Level.SEVERE, null, sqle);

    }

  }



 public static void main(String args)

 {

//   NdConnect nd = new NdConnect();  

   NdConnect.connect();

   NdConnect.disconnect();

//    System.out.println("NdConnect finished");

  }

}

The program throws a SQLSyntaxErrorException:

run:

SQL exception

Connected NOK:The connection URL is jdbc:derby:C:/Users/Administrator/AppData/Local/NewdawnTestdb;create=true

Nov 25, 2018 6:58:42 AM NdConnect connect

0048 NDC OK:DB closed 

SEVERE: null

java.sql.SQLSyntaxErrorException: Table/View 'TXNS' does not exist.

    at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)

    at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)

    at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)

    at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)

    at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)

    at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)

    at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)

    at org.apache.derby.impl.jdbc.EmbedPreparedStatement42.<init>(Unknown Source)

    at org.apache.derby.jdbc.Driver42.newEmbedPreparedStatement(Unknown Source)

    at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)

    at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)

    at NdConnect.connect(NdConnect.java:21)

    at NdConnect.main(NdConnect.java:47)

Caused by: ERROR 42X05: Table/View 'TXNS' does not exist.

    at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)

    at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)

    at org.apache.derby.impl.sql.compile.FromBaseTable.bindTableDescriptor(Unknown Source)

    at org.apache.derby.impl.sql.compile.FromBaseTable.bindNonVTITables(Unknown Source)

    at org.apache.derby.impl.sql.compile.FromList.bindTables(Unknown Source)

    at org.apache.derby.impl.sql.compile.SelectNode.bindNonVTITables(Unknown Source)

    at org.apache.derby.impl.sql.compile.DMLStatementNode.bindTables(Unknown Source)

    at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown Source)

    at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(Unknown Source)

    at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)

    at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)

    at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)

    ... 7 more

However the database and table do exist. I have verified this by creating a connection to the database in Netbeans > Services. When I run this command in Services:

SELECT * from TXNS

the desired table is correctly displayed and the output window returns:

Executed successfully in 0 s.

Fetching resultset took 0.016 s.

Line 1, column 1



Execution finished after 0.254 s, no errors occurred.

Looking at the properties of this connection via Netbeans services shows the following attribute values:

Display name    NewDawn – TEST DB

Database URL    jdbc:derby:C:UsersAdministratorAppDataLocalNewdawnTestdb

Driver          apache_derby_embedded

Driver class    org.apache.derby.jdbc.EmbeddedDriver

This seems to correspond to the setup I have in the java code example so I don't understand why the code doesn't work.

The URL is not the same:

In Netbeans: jdbc:derby:C:UsersAdministratorAppDataLocalNewdawnTestdb

In Java: jdbc:derby:C:/Users/Administrator/AppData/Local/NewdawnTestdb;create=true

Change the CONNECTION_URL line to:

private static final String CONNECTION_URL = "jdbc:derby:" + "C:/Users/" + System.getenv("USERNAME") + SETUP_FILE_PATH + "/db"