I am trying to connect to a MS Access database using the example of this link^.
The connection is made perfectly. Now I need to list the tables contained in that database by running the following command:
SELECT MSysObjects.Name AS table_name
FROM MSysObjects
WHERE (((Left([Name],1))<>"~")
AND ((Left([Name],4))<>"MSys")
AND ((MSysObjects.Type) In (1,4,6))
AND ((MSysObjects.Flags)=0))
order by MSysObjects.Name
I get the error:
net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.4 user lacks privilege or object not found: MSYSOBJECTS
I have tried to make the same query through other tools and the result is the same error.
(BTW, I also try to "GRANT SELECT ON MSysObjects TO Admin", with same error).
where they mention that it is not necessary to hit the MsSysobjects table, but to execute these methods on the connection:
B4X:
'You don't need to hit the [MSysObjects] table. You can get a list of tables via the DatabaseMetaData#getTables method, e.g.,
try (ResultSet rsMD = connChem.getMetaData().getTables(null, null, null, null)) {
while (rsMD.next()) {
String tblName = rsMD.getString("TABLE_NAME");
System.out.println(tblName);
}
}
My knowledge of java is <= 0, and I don't know how I could translate it to B4J through the object from jSQL library.
Could you help me to implement the solution proposed in B4J?
Thank you very much!
EDIT: my connection string include ;showSchema=true
Ok, in case anyone ever requires it, I found out the right query is "SELECT * FROM information_schema.tables".
It seems ucanaccess actually "copies" the original <database>.mdb to an hsqldb one at connection time.