We have a crosstab query in Access, that doesn't seem to properly translate in UCanAccess, is there something we need to do to properly run a crosstab:
-- crosstabburialpoints
TRANSFORM Count([Points/burials(JSW)].ARTIFACT) AS CountOfARTIFACT
SELECT [Points/burials(JSW)].FEATURE
FROM [Points/burials(JSW)]
GROUP BY [Points/burials(JSW)].FEATURE
PIVOT [Points/burials(JSW)].MATERIAL;
We get the following error:
~~~
net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.2 unexpected token: TRANSFORM
com.healthmarketscience.jackcess.impl.query.SelectQueryImpl
at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:210)
at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:210)
at org.tdar.db.conversion.converters.AccessDatabaseConverter.lambda$0(AccessDatabaseConverter.java:250)
at org.tdar.db.conversion.converters.AccessDatabaseConverter$$Lambda$50/582930471.accept(Unknown Source)
at java.util.ArrayList.forEach(ArrayList.java:1249)
at org.tdar.db.conversion.converters.AccessDatabaseConverter.dumpData(AccessDatabaseConverter.java:242)
at org.tdar.db.conversion.converters.DatasetConverter$Base.execute(DatasetConverter.java:211)
at org.tdar.core.bean.AbstractIntegrationTestCase.convertDatabase(AbstractIntegrationTestCase.java:1018)
at org.tdar.db.conversion.AccessConverterITCase.testDatabase(AccessConverterITCase.java:46)
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:483)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
at org.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:55)
at org.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:55)
at org.junit.rules.RunRules.evaluate(RunRules.java:20)
at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:252)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:94)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:191)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:678)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)
Caused by: java.sql.SQLSyntaxErrorException: unexpected token: TRANSFORM
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.executeQuery(Unknown Source)
at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:208)
... 41 more
Caused by: org.hsqldb.HsqlException: unexpected token: TRANSFORM
at org.hsqldb.error.Error.parseError(Unknown Source)
at org.hsqldb.ParserBase.unexpectedToken(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)
... 44 more
~~~
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
You aren't doing anything wrong. I can reproduce your issue using UCanAccess 4.0.2: it is unable to parse a TRANSFORM ... PIVOT ... query that is passed as SQL text, but it can execute the exact same SQL statement if it is a saved query in Access (e.g., "SELECT * FROM MySavedCrosstabQuery").
I suspect that this behavior is unintended. If so, then it will probably be fixed in a future release of UCanAccess.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
It's intended, I must rely on a hidden view whose the structure may change anytime.
I can allow this in a online SQL, but it would be much more complicated for online SQL and I'm not sure if it makes sense. Adam, we'll decide and let you know.
Cheers, Marco
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Marco & Gord -- thanksf or the help. I didn't realize you could get the result of query that way. I was using Jackcess to read the SQL of the query out, and then running it via a JDBC query. If I can just do select * from {queryname} for any query, that solves my issue.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Technically speaking, we implement the JDBC (nothing magic helps us) and some code must do the tricky work and implement a pivot technique: in this case the same columns definition and the query structure are variable because they are data dependent. Yes, just let us to know if it works as expected.
Cheers Marco
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Looks like this error hides two other errors. I'm including the MSAccess Database that's causing this (with all of the data truncated). There's also (possibly related, but possibly not) a bug in Jackcess in how it hanldes joins: https://sourceforge.net/p/jackcess/bugs/141/?limit=25#c1ca
Issue 1, table permssions/does not exist:
DEBUG 2017-05-29 07:47:20,687 23837 [main []] (AccessDatabaseConverter.java:243) org.tdar.db.conversion.converters.AccessDatabaseConverter - specsums(JSW) SELECT [] | class com.healthmarketscience.jackcess.impl.query.SelectQueryImpl
DEBUG 2017-05-29 07:47:20,687 23837 [main []] (AccessDatabaseConverter.java:245) org.tdar.db.conversion.converters.AccessDatabaseConverter - SELECT [burnspecs(JSW)].STRATUM, [burnspecs(JSW)].SPECTYPE.LABEL, Sum([burnspecs(JSW)].SPECCOUNT) AS SumOfSPECCOUNT
FROM [burnspecs(JSW)]
GROUP BY [burnspecs(JSW)].STRATUM, [burnspecs(JSW)].SPECTYPE.LABEL;
net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.2 user lacks privilege or object not found: SPECSUMS(JSW)
at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:210)
at org.tdar.db.conversion.converters.AccessDatabaseConverter.lambda$0(AccessDatabaseConverter.java:250)
at java.util.ArrayList.forEach(ArrayList.java:1249)
at org.tdar.db.conversion.converters.AccessDatabaseConverter.dumpData(AccessDatabaseConverter.java:242)
at org.tdar.db.conversion.converters.DatasetConverter$Base.execute(DatasetConverter.java:211)
at org.tdar.core.bean.AbstractIntegrationTestCase.convertDatabase(AbstractIntegrationTestCase.java:1018)
at org.tdar.db.conversion.AccessConverterITCase.testDatabase(AccessConverterITCase.java:47)
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 org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
at org.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:55)
at org.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:55)
at org.junit.rules.RunRules.evaluate(RunRules.java:20)
at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:252)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:94)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:191)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:678)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)
Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: SPECSUMS(JSW)
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.executeQuery(Unknown Source)
at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:208)
... 40 more
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: SPECSUMS(JSW)
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.SchemaManager.getTable(Unknown Source)
at org.hsqldb.ParserDQL.readTableName(Unknown Source)
at org.hsqldb.ParserDQL.readTableOrSubquery(Unknown Source)
at org.hsqldb.ParserDQL.XreadTableReference(Unknown Source)
at org.hsqldb.ParserDQL.XreadFromClause(Unknown Source)
at org.hsqldb.ParserDQL.XreadTableExpression(Unknown Source)
at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source)
at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source)
at org.hsqldb.ParserDQL.compileCursorSpecification(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)
... 43 more
and
Issue 2 NPE:
I think this one is an MSAccess "Report" and not a query
DEBUG 2017-05-29 07:47:20,574 23724 [main []] (AccessDatabaseConverter.java:243) org.tdar.db.conversion.converters.AccessDatabaseConverter - ~sq_rOBSIDIAN SELECT [] | class com.healthmarketscience.jackcess.impl.query.SelectQueryImpl
DEBUG 2017-05-29 07:47:20,574 23724 [main []] (AccessDatabaseConverter.java:245) org.tdar.db.conversion.converters.AccessDatabaseConverter - SELECT DISTINCTROW *
FROM OBSIDIAN;
java.lang.NullPointerException
at net.ucanaccess.converters.SQLConverter.convertIdentifiers(SQLConverter.java:644)
at net.ucanaccess.converters.SQLConverter.convertPartIdentifiers(SQLConverter.java:693)
at net.ucanaccess.converters.SQLConverter.escape(SQLConverter.java:708)
at net.ucanaccess.converters.SQLConverter.convertSQL(SQLConverter.java:442)
at net.ucanaccess.converters.SQLConverter.convertSQL(SQLConverter.java:544)
at net.ucanaccess.jdbc.UcanaccessStatement.convertSQL(UcanaccessStatement.java:57)
at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:206)
at org.tdar.db.conversion.converters.AccessDatabaseConverter.lambda$0(AccessDatabaseConverter.java:250)
at java.util.ArrayList.forEach(ArrayList.java:1249)
at org.tdar.db.conversion.converters.AccessDatabaseConverter.dumpData(AccessDatabaseConverter.java:242)
at org.tdar.db.conversion.converters.DatasetConverter$Base.execute(DatasetConverter.java:211)
at org.tdar.core.bean.AbstractIntegrationTestCase.convertDatabase(AbstractIntegrationTestCase.java:1018)
at org.tdar.db.conversion.AccessConverterITCase.testDatabase(AccessConverterITCase.java:47)
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 org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
at org.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:55)
at org.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:55)
at org.junit.rules.RunRules.evaluate(RunRules.java:20)
at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:252)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:94)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:191)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:678)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)
and that conflicts with the multi-part identifier model used by many other databases (such as HSQLDB): schema.table.column. You should be able to avoid the problem by tweaking the SQL for that saved query (in the Access database) to wrap SPECTYPE.LABEL in square brackets:
Hi Gord,
I'll see if there's a way to do this, but, ideally it'd be nice to not have to rewrite every query for every access database that we're passed. This does feel implementation specific to how UCanAccess handles these queries, is it possible that this is something that you could do within the tool?
Re: issue 2, I'll see if the issue is having "data" in the database that's causing the NPE to fire.
thanks
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi Gord,
Just checking back here. We're still struggling with how to approach this type of problem where UCanAccess says that "user lacks privilege or object not found". We have a number of "queries/views" that are reporting this, and if it's a similiar issue to what's listed above (where the MSAccess SQL is valid, but not valid SQL for HSQLDB), is there a good way to test this?
We're also struggling with how to better approach this, we don't have control over the databases we're being given, so there's no way to check/fix the SQL manually.
2 "productive questions":
1. is there a way to get a more accurate error message that would allow us to track down what's happening with other queries/views in a database (in the one provided the following are 'broken' queries: "DATURA POTS 1" , "FIND DUPLICATES FOR" , "FLOOR FEATURES AND SUBFEATURES" , "SCHOOLHOUSE LITHMATERIAL", "SPECSUMS(JSW)", "SUMMARYTABLE(JSW)") but not all of them suffer from the schema.table.column syntax issue from what I can immediately see.
2. In Jackcess and UCanAccess, there appears to be no way to manipulate the SQL of a MSAccess Stored Query/View to correct the pattern is that correct to your knowledge? this is important as it we need to do 'select * from [Query/ViewName] in order to get the queries to run based on previous advice
thanks again
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
we don't have control over the databases we're being given
If you're looking for a way to import arbitrary databases into tDAR and you're interested in the results of the queries rather than the queries themselves, then you might consider writing a preprocessor that
runs under JRE7,
uses the JDBC-ODBC Bridge and the Access ODBC driver, and
executes make-table queries to dump the query results into a static table.
I wouldn't normally recommend the JDBC-ODBC Bridge because of its poor reputation and deprecated status, but in this case it would simply be sending an SQL statement to the Access Database Engine so you wouldn't likely run into the "typical" JDBC-ODBC Bridge problems, e.g., with Unicode support, incomplete JDBC implementation, etc..
is there a way to get a more accurate error message that would allow us to track down what's happening with other queries/views
immediately after opening the Connection. When I do that with your sample database I see
Cannot load view datura pots 1 : object name already exists: LABEL in statement [CREATE VIEW "DATURA POTS 1" AS select SPECIMEN, SITEGRP.LABEL AS "SITEGRP.LABEL", SPEC.FEATURE AS "SPEC.FEATURE",SUBFEATURE,STRATUM,LEVEL_,COMMENT, FEAT.FEATURE AS "FEAT.FEATURE", STRATYPE.LABEL AS "STRATYPE.LABEL", FEATTYPE.LABEL AS "FEATTYPE.LABEL", FEATCLAS.LABEL AS "FEATCLAS.LABEL",LABEL,LABEL FROM UNITTYPE INNER JOIN (LEVELTYP INNER JOIN (LEVEL_ INNER JOIN (FEATCLAS INNER JOIN (FEATTYPE INNER JOIN (STRATYPE INNER JOIN (STRATUM INNER JOIN (SITEGRP INNER JOIN (FEAT INNER JOIN SPEC ON FEAT.FEATURE = SPEC.FEATURE) ON SITEGRP.CODE = SPEC.SITEGRP) ON (FEAT.FEATURE = STRATUM.FEATURE) AND (STRATUM.STRATUM = SPEC.STRATUM)) ON STRATYPE.CODE = STRATUM.STRATYPE) ON FEATTYPE.CODE = FEAT.FEATTYPE) ON FEATCLAS.CODE = FEAT.FEATCLAS) ON (STRATUM.STRATUM = LEVEL_.STRATUM) AND (FEAT.FEATURE = LEVEL_.FEATURE) AND (LEVEL_.LEVEL_ = SPEC.LEVEL_)) ON LEVELTYP.CODE = LEVEL_.LEVELTYP) ON UNITTYPE.CODE = LEVEL_.UNITTYPE WHERE (((SPEC.SPECIMEN)=21246 Or (SPEC.SPECIMEN)=20666 Or (SPEC.SPECIMEN)=14723 Or (SPEC.SPECIMEN)=27462 Or (SPEC.SPECIMEN)=35566 Or (SPEC.SPECIMEN)=40797 Or (SPEC.SPECIMEN)=13459))]
Cannot load view specsums(JSW) : user lacks privilege or object not found: BURNSPECS(JSW).SPECTYPE.LABEL
Cannot load view summarytable(JSW) cannot load this query
Cannot load view Find duplicates for U_8_24_117 Floor Features with Spec : unexpected token: U_8_24_117 required: AS
Cannot load view schoolhouse lithmaterial : user lacks privilege or object not found: SCREENED SPEC
Cannot load procedure Floor Features and Subfeatures unexpected token: INTO required: FROM in statement [SELECT FEAT.ASM, FEAT.FEATURE, STRATYPE.LABEL, SUBFEAT.SUBFEATURE, SUBTYPE.LABEL, SUBFEAT.STRATUM, SUBFEAT.CREW, SUBFEAT.DATE, SUBFEAT.NORTH, SUBFEAT.EAST, SUBFEAT.LONGAXIS, SUBFEAT.SHORTAXI, SUBFEAT.DEPTH, SUBFEAT.AREA, SUBFEAT.TOPLEVEL, SUBFEAT.TOPBSL, SUBFEAT.BOTLEVEL, SUBFEAT.BOTBSL, SUBFEAT.PLANSHAP, SUBFEAT.PROFSHAP, SUBFEAT.SOILCOLO, SUBFEAT.DISTURB, SUBFEAT.TOPELEV, SUBFEAT.BOTELEV INTO "U_8_24_117 FLOOR FEATURES"
FROM SUBTYPE INNER JOIN (((STRATYPE INNER JOIN (FEATTYPE INNER JOIN (FEAT INNER JOIN STRATUM ON FEAT.FEATURE = STRATUM.FEATURE) ON FEATTYPE.CODE = FEAT.FEATTYPE) ON STRATYPE.CODE = STRATUM.STRATYPE) INNER JOIN FEATCLAS ON FEAT.FEATCLAS = FEATCLAS.CODE) INNER JOIN SUBFEAT ON FEAT.FEATURE = SUBFEAT.FEATURE) ON SUBTYPE.CODE = SUBFEAT.SUBTYPE
WHERE (((FEAT.ASM)='U:8:024') AND ((FEAT.FEATURE)='U:8:024:117') AND ((STRATUM.STRATYPE)=4));]
In Jackcess and UCanAccess, there appears to be no way to manipulate the SQL of a MSAccess Stored Query/View to correct the pattern is that correct to your knowledge?
Yes. Jackcess can reconstitute the SQL statement from the elements stored in the Access [MSysQueries] system table, but it does not support updating that information.
Last edit: Gord Thompson 2018-01-08
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
We have a crosstab query in Access, that doesn't seem to properly translate in UCanAccess, is there something we need to do to properly run a crosstab:
We get the following error:
~~~
net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.2 unexpected token: TRANSFORM
com.healthmarketscience.jackcess.impl.query.SelectQueryImpl
at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:210)
at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:210)
at org.tdar.db.conversion.converters.AccessDatabaseConverter.lambda$0(AccessDatabaseConverter.java:250)
at org.tdar.db.conversion.converters.AccessDatabaseConverter$$Lambda$50/582930471.accept(Unknown Source)
at java.util.ArrayList.forEach(ArrayList.java:1249)
at org.tdar.db.conversion.converters.AccessDatabaseConverter.dumpData(AccessDatabaseConverter.java:242)
at org.tdar.db.conversion.converters.DatasetConverter$Base.execute(DatasetConverter.java:211)
at org.tdar.core.bean.AbstractIntegrationTestCase.convertDatabase(AbstractIntegrationTestCase.java:1018)
at org.tdar.db.conversion.AccessConverterITCase.testDatabase(AccessConverterITCase.java:46)
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:483)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
at org.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:55)
at org.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:55)
at org.junit.rules.RunRules.evaluate(RunRules.java:20)
at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:252)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:94)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:191)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:678)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)
Caused by: java.sql.SQLSyntaxErrorException: unexpected token: TRANSFORM
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.executeQuery(Unknown Source)
at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:208)
... 41 more
Caused by: org.hsqldb.HsqlException: unexpected token: TRANSFORM
at org.hsqldb.error.Error.parseError(Unknown Source)
at org.hsqldb.ParserBase.unexpectedToken(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)
... 44 more
~~~
You aren't doing anything wrong. I can reproduce your issue using UCanAccess 4.0.2: it is unable to parse a
TRANSFORM ... PIVOT ...query that is passed as SQL text, but it can execute the exact same SQL statement if it is a saved query in Access (e.g., "SELECT * FROM MySavedCrosstabQuery").I suspect that this behavior is unintended. If so, then it will probably be fixed in a future release of UCanAccess.
It's intended, I must rely on a hidden view whose the structure may change anytime.
I can allow this in a online SQL, but it would be much more complicated for online SQL and I'm not sure if it makes sense. Adam, we'll decide and let you know.
Cheers, Marco
Marco & Gord -- thanksf or the help. I didn't realize you could get the result of query that way. I was using Jackcess to read the SQL of the query out, and then running it via a JDBC query. If I can just do
select * from {queryname}for any query, that solves my issue.Technically speaking, we implement the JDBC (nothing magic helps us) and some code must do the tricky work and implement a pivot technique: in this case the same columns definition and the query structure are variable because they are data dependent. Yes, just let us to know if it works as expected.
Cheers Marco
Looks like this error hides two other errors. I'm including the MSAccess Database that's causing this (with all of the data truncated). There's also (possibly related, but possibly not) a bug in Jackcess in how it hanldes joins: https://sourceforge.net/p/jackcess/bugs/141/?limit=25#c1ca
Issue 1, table permssions/does not exist:
DEBUG 2017-05-29 07:47:20,687 23837 [main []] (AccessDatabaseConverter.java:243) org.tdar.db.conversion.converters.AccessDatabaseConverter - specsums(JSW) SELECT [] | class com.healthmarketscience.jackcess.impl.query.SelectQueryImpl DEBUG 2017-05-29 07:47:20,687 23837 [main []] (AccessDatabaseConverter.java:245) org.tdar.db.conversion.converters.AccessDatabaseConverter - SELECT [burnspecs(JSW)].STRATUM, [burnspecs(JSW)].SPECTYPE.LABEL, Sum([burnspecs(JSW)].SPECCOUNT) AS SumOfSPECCOUNT FROM [burnspecs(JSW)] GROUP BY [burnspecs(JSW)].STRATUM, [burnspecs(JSW)].SPECTYPE.LABEL; net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.2 user lacks privilege or object not found: SPECSUMS(JSW) at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:210) at org.tdar.db.conversion.converters.AccessDatabaseConverter.lambda$0(AccessDatabaseConverter.java:250) at java.util.ArrayList.forEach(ArrayList.java:1249) at org.tdar.db.conversion.converters.AccessDatabaseConverter.dumpData(AccessDatabaseConverter.java:242) at org.tdar.db.conversion.converters.DatasetConverter$Base.execute(DatasetConverter.java:211) at org.tdar.core.bean.AbstractIntegrationTestCase.convertDatabase(AbstractIntegrationTestCase.java:1018) at org.tdar.db.conversion.AccessConverterITCase.testDatabase(AccessConverterITCase.java:47) 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 org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50) at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12) at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47) at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17) at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26) at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75) at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27) at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86) at org.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:55) at org.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:55) at org.junit.rules.RunRules.evaluate(RunRules.java:20) at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84) at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:252) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:94) at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290) at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71) at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288) at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58) at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268) at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61) at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70) at org.junit.runners.ParentRunner.run(ParentRunner.java:363) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:191) at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86) at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:678) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192) Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: SPECSUMS(JSW) 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.executeQuery(Unknown Source) at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:208) ... 40 more Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: SPECSUMS(JSW) at org.hsqldb.error.Error.error(Unknown Source) at org.hsqldb.error.Error.error(Unknown Source) at org.hsqldb.SchemaManager.getTable(Unknown Source) at org.hsqldb.ParserDQL.readTableName(Unknown Source) at org.hsqldb.ParserDQL.readTableOrSubquery(Unknown Source) at org.hsqldb.ParserDQL.XreadTableReference(Unknown Source) at org.hsqldb.ParserDQL.XreadFromClause(Unknown Source) at org.hsqldb.ParserDQL.XreadTableExpression(Unknown Source) at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source) at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source) at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source) at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source) at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source) at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source) at org.hsqldb.ParserDQL.compileCursorSpecification(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) ... 43 moreand
Issue 2 NPE:
I think this one is an MSAccess "Report" and not a query
DEBUG 2017-05-29 07:47:20,574 23724 [main []] (AccessDatabaseConverter.java:243) org.tdar.db.conversion.converters.AccessDatabaseConverter - ~sq_rOBSIDIAN SELECT [] | class com.healthmarketscience.jackcess.impl.query.SelectQueryImpl DEBUG 2017-05-29 07:47:20,574 23724 [main []] (AccessDatabaseConverter.java:245) org.tdar.db.conversion.converters.AccessDatabaseConverter - SELECT DISTINCTROW * FROM OBSIDIAN; java.lang.NullPointerException at net.ucanaccess.converters.SQLConverter.convertIdentifiers(SQLConverter.java:644) at net.ucanaccess.converters.SQLConverter.convertPartIdentifiers(SQLConverter.java:693) at net.ucanaccess.converters.SQLConverter.escape(SQLConverter.java:708) at net.ucanaccess.converters.SQLConverter.convertSQL(SQLConverter.java:442) at net.ucanaccess.converters.SQLConverter.convertSQL(SQLConverter.java:544) at net.ucanaccess.jdbc.UcanaccessStatement.convertSQL(UcanaccessStatement.java:57) at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:206) at org.tdar.db.conversion.converters.AccessDatabaseConverter.lambda$0(AccessDatabaseConverter.java:250) at java.util.ArrayList.forEach(ArrayList.java:1249) at org.tdar.db.conversion.converters.AccessDatabaseConverter.dumpData(AccessDatabaseConverter.java:242) at org.tdar.db.conversion.converters.DatasetConverter$Base.execute(DatasetConverter.java:211) at org.tdar.core.bean.AbstractIntegrationTestCase.convertDatabase(AbstractIntegrationTestCase.java:1018) at org.tdar.db.conversion.AccessConverterITCase.testDatabase(AccessConverterITCase.java:47) 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 org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50) at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12) at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47) at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17) at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26) at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75) at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27) at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86) at org.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:55) at org.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:55) at org.junit.rules.RunRules.evaluate(RunRules.java:20) at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84) at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:252) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:94) at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290) at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71) at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288) at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58) at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268) at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61) at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70) at org.junit.runners.ParentRunner.run(ParentRunner.java:363) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:191) at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86) at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:678) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)Hi Gord and Marco,
Any additional thoughts on the NPE and the table permissions issues or anything else I can provide to help?
thanks,
adam
re: Issue 1 - The SQL for the query named
specsums(JSW)isand that conflicts with the multi-part identifier model used by many other databases (such as HSQLDB):
schema.table.column. You should be able to avoid the problem by tweaking the SQL for that saved query (in the Access database) to wrapSPECTYPE.LABELin square brackets:Last edit: Gord Thompson 2017-06-21
re: Issue 2 - I am unable to reproduce the issue using the sample file you provided.
Hi Gord,
I'll see if there's a way to do this, but, ideally it'd be nice to not have to rewrite every query for every access database that we're passed. This does feel implementation specific to how UCanAccess handles these queries, is it possible that this is something that you could do within the tool?
Re: issue 2, I'll see if the issue is having "data" in the database that's causing the NPE to fire.
thanks
Hi Gord,
Just checking back here. We're still struggling with how to approach this type of problem where UCanAccess says that "user lacks privilege or object not found". We have a number of "queries/views" that are reporting this, and if it's a similiar issue to what's listed above (where the MSAccess SQL is valid, but not valid SQL for HSQLDB), is there a good way to test this?
We're also struggling with how to better approach this, we don't have control over the databases we're being given, so there's no way to check/fix the SQL manually.
2 "productive questions":
1. is there a way to get a more accurate error message that would allow us to track down what's happening with other queries/views in a database (in the one provided the following are 'broken' queries: "DATURA POTS 1" , "FIND DUPLICATES FOR" , "FLOOR FEATURES AND SUBFEATURES" , "SCHOOLHOUSE LITHMATERIAL", "SPECSUMS(JSW)", "SUMMARYTABLE(JSW)") but not all of them suffer from the
schema.table.columnsyntax issue from what I can immediately see.2. In Jackcess and UCanAccess, there appears to be no way to manipulate the SQL of a MSAccess Stored Query/View to correct the pattern is that correct to your knowledge? this is important as it we need to do '
select * from [Query/ViewName]in order to get the queries to run based on previous advicethanks again
Hey Adam.
If you're looking for a way to import arbitrary databases into tDAR and you're interested in the results of the queries rather than the queries themselves, then you might consider writing a preprocessor that
For example:
I wouldn't normally recommend the JDBC-ODBC Bridge because of its poor reputation and deprecated status, but in this case it would simply be sending an SQL statement to the Access Database Engine so you wouldn't likely run into the "typical" JDBC-ODBC Bridge problems, e.g., with Unicode support, incomplete JDBC implementation, etc..
One option would be to use something like
immediately after opening the Connection. When I do that with your sample database I see
Yes. Jackcess can reconstitute the SQL statement from the elements stored in the Access [MSysQueries] system table, but it does not support updating that information.
Last edit: Gord Thompson 2018-01-08
Hi Gord,
Thanks. We'll take a look at the OBDC/JDBC bridge and other options.
best,
adam