Hi,
My financial database has stopped working and I don't have any idea why.
When creating a view, I get a syntax error saying a column can't be found
even though I can see it there. It worked fine last time I ran it and
after a lot of playing round I can't get it working again.
I hacked out the code into a test class, which also fails. The code is:
public class DBTst
{
public static void main(String[] args) throws SQLException
{
//JdbcConnectionPool cp = JdbcConnectionPool.create("jdbc:h2:mem:db1",
"sa", "");
// Set up the database.
try (Connection cn = DriverManager.getConnection("jdbc:h2:mem:db1",
"sa", ""))
{
// Make sure not autocommit.
cn.setAutoCommit(false);
// Create DB structure.
//
// Create the tables.
try (Statement st = cn.createStatement())
{
// Organisations table.
st.execute("CREATE TABLE TB_ORG ( ID IDENTITY, NAME VARCHAR NOT
NULL, ABBREV VARCHAR NOT NULL, TYPE INTEGER NOT NULL)");
// Activity table.
st.execute("CREATE TABLE TB_ACTIVITY ( ID IDENTITY (35), NAME
VARCHAR NOT NULL, CUSTREF VARCHAR NOT NULL, ORGID INTEGER NOT NULL, FOREIGN
KEY (ORGID) REFERENCES TB_ORG(ID))");
// Invoice table.
st.execute("CREATE TABLE TB_INVOICE (ID IDENTITY(114), DESC VARCHAR
NOT NULL, YEARREL INTEGER NOT NULL, RAISED DATE NOT NULL, DUE DATE NOT
NULL, JURISID INTEGER NOT NULL, ACTID INTEGER NOT NULL, FOREIGN KEY (ACTID)
REFERENCES TB_ACTIVITY(ID) )");
// Event line table.
st.execute("CREATE TABLE TB_EVENTLINE(ID IDENTITY, DESC VARCHAR NOT
NULL, QUANTITY NUMERIC(100,2) NOT NULL, RATE NUMERIC(100,2) NOT NULL,
VATVAL NUMERIC(100,2))");
// Invoice line table.
st.execute("CREATE TABLE TB_INVOICETRANSACTIONLINE (INVID INTEGER
NOT NULL, EVLID INTEGER NOT NULL, FOREIGN KEY (INVID) REFERENCES
TB_INVOICE(ID), FOREIGN KEY (EVLID) REFERENCES TB_EVENTLINE(ID))");
//setupQueries
//VIEWS
//Invoice lines
st.execute("CREATE VIEW VW_INVOICELINES AS SELECT A.INVID AS
INV_ID, A.EVLID AS EVLID, B.DESC AS DESC, B.QUANTITY AS QUANTITY, B.RATE AS
RATE, ROUND(B.QUANTITY * B.RATE, 2) AS NETTOT, B.VATVAL AS VATVAL, FROM
TB_INVOICETRANSACTIONLINE A INNER JOIN TB_EVENTLINE B ON A.EVLID = B.ID");
System.out.println("Invoice lines view:");
ResultSet executeQuery = st.executeQuery("SELECT * FROM
VW_INVOICELINES ");
printResults(executeQuery);
System.out.println("Invoice table:");
executeQuery = st.executeQuery("SELECT * FROM TB_INVOICE ");
printResults(executeQuery);
System.out.println("Activity table:");
executeQuery = st.executeQuery("SELECT * FROM TB_ACTIVITY ");
printResults(executeQuery);
System.out.println("Organisations table:");
executeQuery = st.executeQuery("SELECT * FROM TB_ORG ");
printResults(executeQuery);
//Invoice summary
System.out.println("Invoice summary view:");
st.execute("CREATE VIEW VW_INVOICESUMMARY AS SELECT IL.INV_ID,
ROUND(SUM(IL.QUANTITY * IL.RATE), 2) AS NETTOT, ROUND(SUM(IL.VATVAL), 2) AS
VATTOT, ROUND(SUM(IL.QUANTITY * IL.RATE) + SUM(IL.VATVAL), 2) AS GROSSTOT,
B.DESC AS DESC, B.YEARREL AS RELEVANT_YEAR, B.RAISED AS RAISED, B.DUE AS
DUE, B.JURISID AS JURISID, C.ID AS ACTID, C.NAME AS ACTNAME, D.ID AS ORGID,
D.NAME AS ORGNAME FROM VW_INVOICELINES IL INNER JOIN TB_INVOICE B INNER
JOIN TB_ACTIVITY C INNER JOIN TB_ORG D ON IL.INV_ID = B.ID AND B.ACTID =
C.ID AND C.ORGID = D.ID GROUP BY IL.INV_ID");
// Commit.
cn.commit();
}
}
}
public static void printResults(ResultSet rs) throws SQLException
{
StringBuilder sb = new StringBuilder();
// Top line - the column names.
for(int i = 0; i < rs.getMetaData().getColumnCount(); i++)
{
if (i > 0) sb.append('\t');
sb.append(rs.getMetaData().getColumnName(i + 1));
}
//Output.
System.out.println(sb.toString());
//Clear buffer.
sb.setLength(0);
//Lines.
while(rs.next())
{
for(int i = 0; i < rs.getMetaData().getColumnCount(); i++)
{
if (i > 0) sb.append('\t');
sb.append(rs.getObject(i + 1));
}
//Output.
System.out.println(sb.toString());
//Clear buffer.
sb.setLength(0);
}
}
}
The output is:
Invoice lines view:
INV_ID EVLID DESC QUANTITY RATE NETTOT VATVAL
Invoice table:
ID DESC YEARREL RAISED DUE JURISID ACTID
Activity table:
ID NAME CUSTREF ORGID
Organisations table:
ID NAME ABBREV TYPE
Invoice summary view:
Exception in thread "main" org.h2.jdbc.JdbcSQLSyntaxErrorException: Column
"IL.INV_ID" not found; SQL statement:
CREATE VIEW VW_INVOICESUMMARY AS SELECT IL.INV_ID, ROUND(SUM(IL.QUANTITY *
IL.RATE), 2) AS NETTOT, ROUND(SUM(IL.VATVAL), 2) AS VATTOT,
ROUND(SUM(IL.QUANTITY * IL.RATE) + SUM(IL.VATVAL), 2) AS GROSSTOT, B.DESC
AS DESC, B.YEARREL AS RELEVANT_YEAR, B.RAISED AS RAISED, B.DUE AS DUE,
B.JURISID AS JURISID, C.ID AS ACTID, C.NAME AS ACTNAME, D.ID AS ORGID,
D.NAME AS ORGNAME FROM VW_INVOICELINES IL INNER JOIN TB_INVOICE B INNER
JOIN TB_ACTIVITY C INNER JOIN TB_ORG D ON IL.INV_ID = B.ID AND B.ACTID =
C.ID AND C.ORGID = D.ID GROUP BY IL.INV_ID [42122-214]
I've really got no idea what's happening. Any help would be appreciated.
Regards,
Jim
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/ce4ed6fa-6241-45c4-a3b7-df81d0cb4bc5n%40googlegroups.com.
