[Solved] Connect to subsonic database

[jpsh]

First I'll explain what I'm trying to do, I've created an extra column in the user table, I would like to access this column. So I created a jsp page which seems to connect to the database. I'm able to run "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.SYSTEM_TABLES", that runs fine however it doesn't return any tables in the public table schema. When I try to run "SELECT custom_field FROM PUBLIC.USER" or "SELECT custom_field FROM USER" I get the following error:

java.sql.SQLException: Table not found in statement [SELECT custom_field FROM PUBLIC.USER]

here is the code I have

Code: Select all

<%@ page language="java"
      import="java.sql.Connection,
            java.sql.DriverManager,
            java.sql.ResultSet,
            java.sql.Statement" %>
            
<%!
  String title = "Database Test";
  String database = "";
  String sql = "SELECT KEYWORD FROM USER";
  //String sql = "SELECT TABLE_SCHEM, TABLE_NAME FROM INFORMATION_SCHEMA.SYSTEM_TABLES";
  //String sql = "SELECT TABLE_NAME AS KEYWORD FROM INFORMATION_SCHEMA.SYSTEM_TABLEPRIVILEGES ";
  Connection connection = null;
  Statement statement = null;
  ResultSet resultSet = null;
  boolean cont = false;
%>

<%
  try {
   database = "/var/subsonic/db/subsonic.data";

   Class.forName("org.hsqldb.jdbcDriver");
   connection = DriverManager.getConnection("jdbc:hsqldb:file:" + database, "SA", "");
   
   statement = connection.createStatement();
   resultSet = statement.executeQuery(sql);
  }  catch (Exception e) {
    out.print("Unable do make connection database<br />");
    out.print(e);
  }
%>

<div class="data">
   <% while(resultSet.next()){ %>
      <p><%= resultSet.getObject("custom_field") %></p>
   <% } %>
</div>

<%
   resultSet.close();
   statement.close();
   connection.close();
%>



I left the two other queries I tested in but commented out. The jsp page I'm running this on is a custom page I created and added the the web.xml as a servlet here is my code

Code: Select all

   <servlet>
      <servlet-name>customSimple</servlet-name>
      <jsp-file>/WEB-INF/jsp/custom/simple.jsp</jsp-file>
      <init-param>
         <param-name>checkInterval</param-name>
         <param-value>1</param-value>
      </init-param>
      <load-on-startup>1</load-on-startup>
   </servlet>

   <servlet-mapping>
      <servlet-name>customSimple</servlet-name>
      <url-pattern>/simple.get</url-pattern>
   </servlet-mapping>

I'm really unsure why it's unable to find tables in PUBLIC, I'm thinking that maybe the /var/subsonic/db/subsonic.data is not the database file. I'm running subsonic 5.2.1 on ubuntu server 14.04.2 any help would be greatly appreciated.

[jpsh]

I was able to connect to the database. This is what I changed.

for my database (location) before I had

Code: Select all

database = "/var/subsonic/db/subsonic.data"

I was reading up some about HyperSQL and found that it might not have the tables in a file because it can utilize storing info in memory. So I was thing what if it isn't in a file so I just try without using .data extension, I I changed the above line to this

Code: Select all

database = "/var/subsonic/db/subsonic"

Then it worked! So hopefully this will help anyone trying to do anything to the database in subsonic. I would also like to mention that although you can connect to a database inside of a jsp file it is not ideal and considered a bad practice so beware.