Hi I am using Access-JDBC jars with version 3.2.127. With these jars I am unable to load the data from MS-Access Views(i.e. Queries in Access). The following exception I get if i try to read from the view
java.sql.SQLException: Lexical error: !ACRT,'US7')>0,Mid(tbl_Brand_In_Use!ACRT,InStr(1,tbl_Brand_In_Use!ACRT,'US7'),6),'') AS TerritoryFormula,tbl_Brand_In_Use.* FROM tbl_Brand_In_Use;
at com.hxtt.global.SQLState.SQLException(Unknown Source)
at com.hxtt.sql.c7.for(Unknown Source)
at com.hxtt.sql.eg.new(Unknown Source)
at com.hxtt.a.b.g(Unknown Source)
at com.hxtt.sql.bq.o(Unknown Source)
at com.hxtt.sql.ah.if(Unknown Source)
at com.hxtt.sql.dk.(Unknown Source)
at com.hxtt.sql.em.(Unknown Source)
at com.hxtt.sql.bq.prepareCall(Unknown Source)
at com.hxtt.sql.bq.prepareCall(Unknown Source)
at com.hxtt.sql.bq.prepareCall(Unknown Source)
at com.hxtt.sql.b8.(Unknown Source)
at com.hxtt.sql.access.aw.do(Unknown Source)
at com.hxtt.sql.access.aw.a(Unknown Source)
at com.hxtt.sql.access.r.a(Unknown Source)
at com.hxtt.concurrent.x.a(Unknown Source)
at com.hxtt.sql.access.bc.a(Unknown Source)
at com.hxtt.sql.access.ae.a(Unknown Source)
at com.hxtt.sql.bq.if(Unknown Source)
at com.hxtt.sql.di.a(Unknown Source)
at com.hxtt.sql.di.a(Unknown Source)
at com.hxtt.sql.bq.a(Unknown Source)
at com.hxtt.sql.dk.(Unknown Source)
at com.hxtt.sql.em.(Unknown Source)
at com.hxtt.sql.bq.prepareCall(Unknown Source)
at com.hxtt.sql.bq.prepareCall(Unknown Source)
at com.hxtt.sql.bq.prepareCall(Unknown Source)
at com.hxtt.sql.b8.(Unknown Source)
at com.hxtt.sql.access.aw.do(Unknown Source)
at com.hxtt.sql.access.aw.a(Unknown Source)
at com.hxtt.sql.access.r.a(Unknown Source)
at com.hxtt.concurrent.x.a(Unknown Source)
at com.hxtt.sql.access.bc.a(Unknown Source)
at com.hxtt.sql.access.ae.a(Unknown Source)
at com.hxtt.sql.bq.if(Unknown Source)
at com.hxtt.sql.di.a(Unknown Source)
at com.hxtt.sql.di.a(Unknown Source)
at com.hxtt.sql.bq.a(Unknown Source)
at com.hxtt.sql.bq.a(Unknown Source)
at com.hxtt.sql.ah.a(Unknown Source)
at com.hxtt.sql.ah.a(Unknown Source)
at com.hxtt.sql.ah.executeQuery(Unknown Source)
at com.synygy.dsn.impl.service.DSNServiceImpl.main(DSNServiceImpl.java:743)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:90)
|
>java.sql.SQLException: Lexical error: >!ACRT,'US7')>0,Mid(tbl_Brand_In_Use!ACRT,InStr(1,tbl_Brand_In_Use!ACRT,'US7'),6),'
>') AS TerritoryFormula,tbl_Brand_In_Use.* FROM tbl_Brand_In_Use;
It means that VIEW sql is non't support by HXTT Access. tbl_Brand_In_Use!ACRT should be the invalid sql syntax.
|
But same query works in MS-Access. If i run the query it runs fine. Below is the entire query
SELECT IIf(InStr(1,tbl_Brand_In_Use!ACRT,"US7")>0,Mid(tbl_Brand_In_Use!ACRT,InStr(1,tbl_Brand_In_Use!ACRT,"US7"),6),"") AS TerritoryFormula, tbl_Brand_In_Use.*
FROM tbl_Brand_In_Use;
|
Because MS Access is not using the standard sql. You can send us a mdb sample, then we will check whether to support that unstandard sql.
|
>tbl_Brand_In_Use!ACRT
You can use directly tbl_Brand_In_Use.ACRT or ACRT to replace tbl_Brand_In_Use!ACRT in MS View.
|