Hello (I use google translator),
I finally got Lucee running on a Windows Server 2019 with IIS.
Now I would like to access Access databases (mdb) with Lucee.
Are there instructions for this?
Do I really need ucanaccess?
How do I install ucanaccess?
I downloaded it but there is no setup file, what next?
Do you want to use Lucee with Access as a Desktop app or for use in a website?
We’ve been using Access for years for websites on a Coldfusion 8-9 server. We now want to switch to Lucee. Coldfusion 8-9 supported Access with no problems. Of course, we want to switch to MySQL soon, but due to time and other reasons, we can’t do that anytime soon. Before our website changes, I test Lucee local.
It’s very possible you won’t get many information around, and my personal advice is pretty likely to not be the one you might like.
You won’t find many users with a helpful solution, because access data bases is not what most of us use in combination with a web app engine.As of today, it’s not a good choice. Last time I’ve connected access with a cfengine was with CF 4.0
My personal advice is migrate that DB to another one (e.g. mariaDB or mySQL) in first place. There are lots of tools around and docs that makes that pretty easy. Probably easier than trying to make a data source connection to a modern web app engine like Lucee work.
As far as I remember, you can connect it via ODBC connector. We had ~15 years ago the mysql odbc connector to a access file as proxy.
Good luck (you will need it)
1 Like
I use Access as DB for more than 20 years, it works fine for small sites,
and it is very simple and flexible.
I just cut and paste my MDB file, and do what ever I want with the MDB on local.
In Lucee , you use ODBC driver : Update datasource connection Other - JDBC Driver (deprecated)
I work on Linux, but before I worked on windows.
Set Class and connection string in the declared datasource.
It is good to me to know other users with Ucanaccess driver.
1 Like
@Pierre_Larde You rock!!! Thank you for sharing!!! Can you also post it as code snippet, so @donald can copy paste it more easily?
Class is
net.ucanaccess.jdbc.UcanaccessDriver
Connection string is
jdbc:ucanaccess:///var/www/C002/database/mydb.mdb (but this is under Linux)
Under windows, I can’t remerber well
It should be something like
jdbc:ucanaccess:C:/inetpub/www/C002/database/mydb.mdb
Thank you, but don’t I have to install ucanaccess somehow? There is no installer on Windows. Was ucanaccess originally included with Lucee?
Donald, while I’m not at a computer to confirm things for you, my understanding is that you can either extract the zip offered at that site, to place its jars into the lucee classpath (which depends on how you install lucee), or there’s been a lucee extension for it.
For more on each, see resources such as:
https://lucee.daemonite.io/t/setting-an-access-mdb-file-datasource/1157/20
https://bitbucket.org/michaelborn_me/ucanaccess/src/master/
Let us know whether either works for you.
I do not remember, how I did to install under Windows.
Anyway, the String should be (windows address)
connectionString: "jdbc:ucanaccess:///C:\Users\me\my\AccessDatabase.mdb
I find this in my docs.
Sure you have to install Ucanaccess,
Or put the right files at the right place in Lucee
I used UCanAccess-5.0.0-bin.zip
5 files are needed
See image file attached and zip file
UCanAccess-5.0.0-bin.zip (3.2 MB)
Ty,
I used carehart’s rex file from this url Bitbucket
Now I can also select ucanaccess and, to be on the safe side, I still copied the jar files to the lib folder.
Under Database I entered the following:
jdbc:ucanaccess:///C:\inetpub\wwwroot\finder\database\finder.mdb
and get the following error message when I click on “new”:
Donald, a couple of things.
First, the file you refer to would not be “mine”. I pointed to two resources on the topic, and you’re referring first to one of them, from Michael Born. And while you refer to a “rex” file, I assume you mean the “lex” file, for that ucanaccess Lucee Extension Michael created.
Second, you say you “still copied the jar files to the lib folder”. That’s NOT what you should have done. I pointed to the TWO different options, from which to pick, saying clearly to do either one or the other. That other was an answer offered here in the forums by Milan (in 2016), which entailed extracting those zips. By doing both, you may be causing a conflict between the two approaches trying to work, tripping over different java classes.
So for now, since you’ve added the extension, please remove those you added (it will be good for you if the screenshot shows what you put there). My assumption is that the extension would NOT have put any files there, that you would now be removing, and instead it loads its java classes from a place managed by the Lucee extension mechanism.
Note that you should then restart Lucee (to cause it to load jars newly, and remove any such java classloading conflicts). Let us know how that goes.
I removed the jar files again and restarted the server. Unfortunately, the error message is exactly the same.
Could you please post the complete stack trace? Looks like it’s cut off at the bottom (61more).
I don’t know where to find the full error report. But I took a look at the application.log in the hope that there was more, but it says exactly the same thing:
“ERROR”,“http-nio-8888-exec-1”,“01/25/2023”,“16:26:12”,“webadmin5.3.9.166”,"com/healthmarketscience/jackcess/util/ErrorHandler;lucee.runtime.exp.NativeException: com/healthmarketscience/jackcess/util/ErrorHandler
at java.lang.Class.getDeclaredConstructors0(Native Method)
at java.lang.Class.privateGetDeclaredConstructors(Unknown Source)
at java.lang.Class.getConstructor0(Unknown Source)
at java.lang.Class.getConstructor(Unknown Source)
at lucee.commons.lang.ClassUtil.newInstance(ClassUtil.java:950)
at lucee.runtime.db.DataSourceSupport._initializeDriver(DataSourceSupport.java:172)
at lucee.runtime.db.DataSourceSupport.initialize(DataSourceSupport.java:164)
at lucee.runtime.db.DataSourceSupport.getConnection(DataSourceSupport.java:103)
at lucee.runtime.tag.Admin._doVerifyDatasource(Admin.java:2936)
at lucee.runtime.tag.Admin.doUpdateDatasource(Admin.java:2712)
at lucee.runtime.tag.Admin._doStartTag(Admin.java:742)
at lucee.runtime.tag.Admin.doStartTag(Admin.java:355)
at services_datasource_create_cfm1334$cf.call(/admin/services.datasource.create.cfm:88)
at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:1054)
at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:946)
at lucee.runtime.PageContextImpl.doInclude(PageContextImpl.java:927)
at services_datasource_cfm414$cf.call(/admin/services.datasource.cfm:108)
at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:1054)
at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:946)
at lucee.runtime.PageContextImpl.doInclude(PageContextImpl.java:927)
at web_cfm$cf.call(/admin/web.cfm:492)
at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:1054)
at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:946)
at lucee.runtime.PageContextImpl.doInclude(PageContextImpl.java:927)
at server_cfm$cf.call(/admin/server.cfm:2)
at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:1054)
at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:946)
at lucee.runtime.listener.ModernAppListener._onRequest(ModernAppListener.java:219)
at lucee.runtime.listener.MixedAppListener.onRequest(MixedAppListener.java:44)
at lucee.runtime.PageContextImpl.execute(PageContextImpl.java:2490)
at lucee.runtime.PageContextImpl._execute(PageContextImpl.java:2476)
at lucee.runtime.PageContextImpl.executeCFML(PageContextImpl.java:2447)
at lucee.runtime.engine.Request.exe(Request.java:45)
at lucee.runtime.engine.CFMLEngineImpl._service(CFMLEngineImpl.java:1198)
at lucee.runtime.engine.CFMLEngineImpl.serviceCFML(CFMLEngineImpl.java:1144)
at lucee.loader.engine.CFMLEngineWrapper.serviceCFML(CFMLEngineWrapper.java:97)
at lucee.loader.servlet.CFMLServlet.service(CFMLServlet.java:51)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:779)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at mod_cfml.core.invoke(core.java:180)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:687)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:360)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:399)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:893)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1789)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Unknown Source)
Caused by: java.lang.NoClassDefFoundError: com/healthmarketscience/jackcess/util/ErrorHandler
… 61 more
Caused by: java.lang.ClassNotFoundException: com.healthmarketscience.jackcess.util.ErrorHandler not found by ucanaccess.4.0.4 [50]
at org.apache.felix.framework.BundleWiringImpl.findClassOrResourceByDelegation(BundleWiringImpl.java:1565)
at org.apache.felix.framework.BundleWiringImpl.access$300(BundleWiringImpl.java:78)
at org.apache.felix.framework.BundleWiringImpl$BundleClassLoader.loadClass(BundleWiringImpl.java:1950)
at java.lang.ClassLoader.loadClass(Unknown Source)
… 61 more
"
1, You are better off converting your access DB to MS sql express, which will import all your data into the tables / databases and have far better performance than access plus lucee supports MSSQL
2. You will need to install the ODBC drivers for your windows version, and you will need to install microsoft office Unable to use the Access ODBC, OLEDB or DAO interfaces outside Office Click-to-Run applications - Office | Microsoft Learn
3. Lastly, you can follow this tutorial if you cant JUST LIVE without access, it is the JAVA connector how to, which you will have to wrap cfcode around
Java JDBC Example Connect to Microsoft Access Database
Ok, it took about 30min to try this, and I was able to make an access database work with an Lucee Express Version 5.3.10.97 on my Windows 10 Lap. You need to translate that to your installed server. But this works!!! If you have populated your Lucee installation with some other files, please restart from scratch. Here is a solution and the steps you need to make:
-
Download Lucee Express Version 5.3.10.97 and unzip it. (If you are installing it to a Windows Server 2019 use the Lucee Installer, but you need to translate to the correct paths/directories of your Lucee installation then. I’m showing it here with the Lucee Express Version).
-
Download the following OSGI compliant JARs from Maven here:
- commons-lang3-3.12.0.jar
- ucanaccess-5.0.0.jar
- jackcess-4.0.4.jar
- hsqldb-2.7.1.jar
- commons-logging-1.2.jar
- Place those downloaded files to your
pathToYourLucee\lucee-express-5.3.10.97\lib.
Let’s say you have a setup like this:
- a database named
database.accdbwith a table namedtable1placed atpathToYourLucee\lucee-express-5.3.10.97\database.accdb, - a webroot served at
pathToYourLucee\lucee-express-5.3.10.97\webapps\ROOT
Then continue with the following steps:
-
remove all files located inside
pathToYourLucee\lucee-express-5.3.10.97\webapps\ROOT -
Create an Application.cfc at
pathToYourLucee\lucee-express-5.3.10.97\webapps\ROOT\Application.cfcwith the following code:
//Application.cfc
component {
this.Name = "MSAccessExample";
this.dataBasePath=expandPath("../../") & "database.accdb";
this.datasources["msAccessDB"] = {
class: "net.ucanaccess.jdbc.UcanaccessDriver",
connectionString: "jdbc:ucanaccess:///" & this.dataBasePath
};
}
- Create an index.cfm with the following code at
pathToYourLucee\lucee-express-5.3.10.97\webapps\ROOT\index.cfmwith the following code:
<!--- index.cfm --->
<cfquery name="myquery" datasource="msAccessDB" >
select * from table1;
</cfquery>
<cfdump var="#myquery#">
- Run the Lucee server from your Lucee Express Version
When you execute the index.cfm you get this:
In case you get lots of errors in your console output:
java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: net.ucanaccess.converters.FunctionsAggregate
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
...
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: net.ucanaccess.converters.FunctionsAggregate
at org.hsqldb.error.Error.error(Unknown Source)
...
WARNING:Function already added: CREATE AGGREGATE FUNCTION last(IN val DECIMAL(100,10), IN flag BOOLEAN, INOUT register DECIMAL(100,10), INOUT counter INT) RETURNS DECIMAL(100,10) NO SQL LANGUAGE JAVA EXTERNAL NAME 'CLASSPATH:net.ucanaccess.converters.FunctionsAggregate.last'
it’s because the the DB makes some sort of issues because of privileges. Didn’t had the time to dig further, but if you change the access DB file to be more permissive for the user running Lucee, this should just work. So on your WIN give the DB file permission for that Lucee user (should be “local service” if you install it on Windows 2019 with the Lucee installer).
Another thing: Just like @Terry_Whitney said, please move to a more convenient database engine ASAP.
2 Likes
Hello and ty.
sry for just getting in touch now. I used the Lucee version 5.3.10.120, so only 1 version after yours, on a Windows Server 2019. www root is “c:\inetpub\wwwroot” because I use the IIS. The websites are also located here. I get the following error message:
Message: " lucee.commons.lang.ClassException: cannot load class through its string name, because no definition for the class with the specified name [net.ucanaccess.jdbc.UcanaccessDriver] could be found caused by (java.lang.ClassNotFoundException:net.ucanaccess.jdbc.UcanaccessDriver not found by lucee.core [49];java.lang.ClassNotFoundException:net.ucanaccess.jdbc.UcanaccessDriver;)"
Stacktrace:
The Error Occurred in
C:\inetpub\wwwroot\index2.cfm: line 3
1:
2: select * from table1;
3:
4:
These are some possibilities that might be happening to you:
- you may have placed the jars to the wrong directory, or
- the jar is missing, or
- the downloaded jar is corrupt
- the jar haven’t got the correct file permissions to be read by the user running lucee, or
- you may need to restart lucee