setting an access (mdb file) datasource

1,199 views
Skip to first unread message

Pierre Larde

unread,
Jan 19, 2016, 1:15:59 PM1/19/16
to Lucee
I tried to set an access (mdb file) datasource.
I have set a datasource in the Windows ODBC panel
Then I declared a datasouce in Lucee with JDBC-ODBC bridge
then I gave a name and the database name,
(I do not know what is the database name ? is that the ODBC created name)
And whatever database name I gave , i had this error :

can't find class [sun.jdbc.odbc.JdbcOdbcDriver] for jdbc driver, check if driver (jar file) is inside lib folder (cannot load class through its string name, because no definition for the class with the specified name [sun.jdbc.odbc.JdbcOdbcDriver] could be found)

It seems the JdbcOdbcDriver files are not found ?

Thanks for help.
Pierre.

ADK

unread,
Jan 19, 2016, 9:07:16 PM1/19/16
to Lucee
Which version of Java are you using? Support for the JDBC-ODBC Bridge was removed in version 1.8 so you need to be on 1.7 or earlier...

Milan Marković

unread,
Jan 20, 2016, 5:22:56 AM1/20/16
to Lucee
Try it UCanAccess – pure Java JDBC Driver implementation which allows java developers and jdbc client programs to read/write Microsoft Access database (.mdb and .accdb files). No ODBC neededhttp://sourceforge.net/projects/ucanaccess/

Pierre Larde

unread,
Jan 20, 2016, 1:29:25 PM1/20/16
to Lucee
Java version installed is 1.8.
I will not return to 1.7 now.
I will try to find a solution with UcanAccess
Thanks.

Pierre Larde

unread,
Jan 20, 2016, 1:33:46 PM1/20/16
to Lucee
Could you help me in implementing this UCanAccess pack.
Where to put the files, and what to do.
UCanAcces have 1 jar file and a lib with 4 jar files.
What to do with ? Should they go into Java, or Tomcat or Lucee.
And then how to declare a datasource in Lucee admin ?

Thanks a lot for assistance.
Pierre.

Milan Marković

unread,
Jan 20, 2016, 3:13:22 PM1/20/16
to Lucee
For Railo/Lucee
Put all the JAR files from the UCanAccess zip file into c:/lucee/lib or (C:/railo/lib).
remove if then exixt  hypersonic-hsqldb.jar (old hsqldb driver).

First method
In server - admin - Services - Datasource
create new datasource:
name: datasource:name
class: net.ucanaccess.jdbc.UcanaccessDriver
Connection String: jdbc:ucanaccess://C:/path_to_zour_site_root/base/name_of_access_base.accdb (or *.mdb)

Second method

Create Applicaton.cfc in site root, and put this:
component output="false"{

this.name = "application-name"; // name of the application

this.datasources["datasource_name"] = { class: 'net.ucanaccess.jdbc.UcanaccessDriver' , connectionString: 'jdbc:ucanaccess://path_to_zour_site_root/base/name_of_access_base.accd' };
 
}

Test 
Create test.cfm and put in:
<cfprocessingdirective pageencoding="utf-8" suppresswhitespace="yes">

<cfdbinfo datasource="#
datasource_name#"
        name="name_of_access_base"
        table="
<name_of_any_table_in_access_base>"
        type="tables"
/>
<cfdump var="#name_of_access_base" label="base" expand="yes" />
</cfprocessingdirective>

and tray: localhost:8888/root_of_site/test.cfm

I hope I helped

Plarts

unread,
Jan 21, 2016, 6:58:57 AM1/21/16
to lu...@googlegroups.com

Thanks a lot for help.

No success for the moment.

I have located the jar files in c:\Program Files\Lucee\

That’s where all lucee jar files are,

And I found there : hypersonic-hsqldb.jar

Which I renamed to : hypersonic-hsqldb_old.jar (so not usable)

 

I left the files like :

ucanaccess-3.0.3.1.jar and its lib as sub-directory

 

When I declared the datasource in Lucee (first method), I got this message :

Could not initialize class net.ucanaccess.jdbc.UcanaccessDriver

 

Do you think some files are not located correctly ?

Thanks for any advise.

Pierre.

 

De : lu...@googlegroups.com [mailto:lu...@googlegroups.com] De la part de Milan Markovic
Envoyé : mercredi 20 janvier 2016 21:13
À : Lucee
Objet : [Lucee] Re: setting an access (mdb file) datasource

--
Love Lucee? Become a supporter and be part of the Lucee project today! - http://lucee.org/supporters/become-a-supporter.html
---
You received this message because you are subscribed to a topic in the Google Groups "Lucee" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/lucee/gA3NuJLWxvo/unsubscribe.
To unsubscribe from this group and all its topics, send an email to lucee+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/01b54bc1-0e49-4467-b1ec-06bda240f25f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.




Avast logo

L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel antivirus Avast.
www.avast.com


Milan Marković

unread,
Jan 21, 2016, 2:23:30 PM1/21/16
to Lucee
Hi Pierre,

put all jar files:
  • ucanaccess-3.0.3.1.jar
  • jackcess-2.0.0.jar
  • commons-lang-2.4.jar
  • commons-logging-1.0.4.jar
  • hsqldb.jar
in your lucee install folder  c:\Program Files\Lucee\lib\.

Rename hypersonic-hsqldb.jar to hypersonic-hsqldb.jar.old.or remove it.
Restart lucee server and check.

Milan


Nando Breiter

unread,
Jan 21, 2016, 4:05:05 PM1/21/16
to lu...@googlegroups.com
Pierre,

It may be better to remove the old jar completely, or at least change the extension of the old file.



Aria Media Sagl
+41 (0)76 303 4477 cell
skype: ariamedia

--
Love Lucee? Become a supporter and be part of the Lucee project today! - http://lucee.org/supporters/become-a-supporter.html
---
You received this message because you are subscribed to the Google Groups "Lucee" group.
To unsubscribe from this group and stop receiving emails from it, send an email to lucee+un...@googlegroups.com.

To post to this group, send email to lu...@googlegroups.com.

Pierre Larde

unread,
Jan 22, 2016, 6:01:43 AM1/22/16
to Lucee
Well,
I stopped Tomcat
I removed "hypersonic.hsqldb.jar"
All ucanaccess jar files are in : c:\program files\lucee\lib
I restart Tomcat (as Windows service)
Then, when going to Lucee admin, I got this error message (about hsqldb)
Etat HTTP 500 - cannot load class through its string name, because no definition for the class with the specified name [org.hsqldb.jdbcDriver] could be found
Tomcat wants this driver ?

May be some declaration line to modifiy in Tomcat config files ?

Thanks for any advise,
Pierre.

Milan Marković

unread,
Jan 22, 2016, 6:27:12 AM1/22/16
to Lucee
Are you sure you're all jar copied to the folder where it was old hypersonic-hsqldb.jar?

Milan

Plarts

unread,
Jan 22, 2016, 7:35:12 AM1/22/16
to lu...@googlegroups.com

OK, sorry,

I did not notice the new “hsqldb.jar”,

Make sense , it must replace the old one at same place.

So all ucanaccess jar files are in c:\program files\lucee  (not in a lib dir)

I restarted Tomcat, OK

I declared the datasource,

AND IT WORKS , GREAT.

I will able to use Lucee, now,

Last thing to check , is the search engine.

(all my sites use Access, small sites with few users or information systems,

Also I may have many access mdb files for a single site, for different functions)

Access is simple and very flexible. For the moment, I never have the obligation to upgrade to MySQL or MSSql.

Thanks a lot, Pierre.

 

De : lu...@googlegroups.com [mailto:lu...@googlegroups.com] De la part de Milan Markovic
Envoyé : vendredi 22 janvier 2016 12:27


À : Lucee
Objet : [Lucee] Re: setting an access (mdb file) datasource

 

Are you sure you're all jar copied to the folder where it was old hypersonic-hsqldb.jar?

Milan

--

Love Lucee? Become a supporter and be part of the Lucee project today! - http://lucee.org/supporters/become-a-supporter.html
---

You received this message because you are subscribed to a topic in the Google Groups "Lucee" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/lucee/gA3NuJLWxvo/unsubscribe.
To unsubscribe from this group and all its topics, send an email to lucee+un...@googlegroups.com.


To post to this group, send email to lu...@googlegroups.com.


For more options, visit https://groups.google.com/d/optout.

Pierre Larde

unread,
Jan 31, 2016, 3:53:55 PM1/31/16
to Lucee
Using Ucanaccess driver to work with mdb files :
most of the queries work,
I just took a site from Coldfusion to Lucee (no change of code)
And I got this Ucanaccess error message :

Lucee 4.5.2.018 Error (database)
MessageUCAExc:::3.0.3.1 Cannot write indexes of this type due to unsupported collating sort order SortOrder[1036(0)] for text index (Db=nadinefrance.mdb;Table=famille;Index=0)
SQL
update famille
set nb_prod=3
where id_fam=10
DatabaseNameUcanaccess for access db(Jet) using hasqldb
DatabaseVersionV2000 [VERSION_4]
DriverNameUcanaccess
DriverVersion

3.0.3.1





The SQL is very simple :


update famille
set nb_prod=3
where id_fam=10 


Thanks for any help.
Pierre

Milan Marković

unread,
Jan 31, 2016, 6:36:30 PM1/31/16
to Lucee
Hi Pierre,

  • 1. Perform a "Compact and Repair Database" on the database, and try again.
if dont work:
  • 2. You must change collation mode to general on the database, and again compact and repair

--
Love Lucee? Become a supporter and be part of the Lucee project today! - http://lucee.org/supporters/become-a-supporter.html
---
You received this message because you are subscribed to a topic in the Google Groups "Lucee" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/lucee/gA3NuJLWxvo/unsubscribe.
To unsubscribe from this group and all its topics, send an email to lucee+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.

Plarts

unread,
Feb 1, 2016, 7:18:14 AM2/1/16
to lu...@googlegroups.com

Thanks for your answer,

Compact and repair database did not solve the problem.

 

I found the problem by modifying the field type from :

“Numeric long integer”   to  “Numeric integer”  (not long)

This is OK for the 32000 maximum value (I do not need more).

 

Could you help on “collation mode to general on the database” , I never used this.

What is this concept, Is that to do with field types ?

Also, for information, in spite of the error message,

The SQL was been done, the update was done, but the process stopped with the error.

 

Thanks again for assistance.

Pierre.

 

 

De : lu...@googlegroups.com [mailto:lu...@googlegroups.com] De la part de Milan Markovic
Envoyé : lundi 1 février 2016 00:36
À : Lucee
Objet : Re: [Lucee] Re: setting an access (mdb file) datasource


For more options, visit https://groups.google.com/d/optout.

Milan Marković

unread,
Feb 1, 2016, 3:55:44 PM2/1/16
to Lucee

Pierre Larde

unread,
Feb 1, 2016, 4:03:22 PM2/1/16
to Lucee
I get again the same error message :
MessageUCAExc:::3.0.3.1 Cannot write indexes of this type due to unsupported collating sort order SortOrder[1036(0)] for text index (Db=nadinefrance.mdb;Table=famille;Index=0)
SQL
insert into famille
(apellation_fam,description_fam,active_fam,order_fam,famille_fam,date_crea)
values ('Ppp','Ppp',1,17,0,{d '2016-02-01'})
DatabaseNameUcanaccess for access db(Jet) using hasqldb
DatabaseVersionV2000 [VERSION_4]
DriverNameUcanaccess
DriverVersion3.0.3.1
DatasourceC013_nadinefrance

Stacktrace ...



The Error Occurred in
C:\Tomcat\webapps\ROOT\sites\nadinefrance\admin_photo\create_famille_save.cfm: line 60
58: (apellation_fam,description_fam,active_fam,order_fam,famille_fam,date_crea)
59: values ('#form.fam_name#','#form.fam_des#',#act#,#next_order#,#parent#,#createodbcdate(now())#)
60: </cfquery>
61:
62: <cfquery name="get_max_id_fam" datasource="#pres_datasource#">

I did change all numeric long integer fields to numeric integer.
This solved the problem, the first time.
But now I get it again.
I do not see what I can do.

Thank you far any advise, help, support in using the Ucanaccess driver.
(this problem appear in update and insert SQL)


To unsubscribe from this group and all its topics, send an email to lucee+unsubscribe@googlegroups.com.


To post to this group, send email to lu...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/abc048a8-2a87-4ce9-a119-6a90d9e6d90d%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
Love Lucee? Become a supporter and be part of the Lucee project today! - http://lucee.org/supporters/become-a-supporter.html
---
You received this message because you are subscribed to a topic in the Google Groups "Lucee" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/lucee/gA3NuJLWxvo/unsubscribe.

To unsubscribe from this group and all its topics, send an email to lucee+unsubscribe@googlegroups.com.

Pierre Larde

unread,
Feb 1, 2016, 5:22:25 PM2/1/16
to Lucee
Thanks for these links.
Problem is solved :

I did modify in Access "options" : the sort order from "Français" to "general"
Then compact the database.
(I did it in the original version .mdb , 2002)

And all queries are working (update and insert)

So the change of field type from "long integer" to "integer" was not the solution.
Also, depending of the Acces version , the "options" button is difficult to find.
(diiferent places with different versions)

Pierre Larde

unread,
Jun 7, 2016, 9:58:01 AM6/7/16
to Lucee
Hi Milan
I have upgraded Lucee v4.5 to Lucee v5.
So, all jar files have been replaced by a single lucee.jar file in the lucee/lib folder.

Then the ucanacees jar files do not exist anymore.

I tried to put the 2 files : ucanaccess-3.0.3.1.jar  and  hsqldb.jar
in lucee/lib

Then the error message changed and is :
Message       com.healthmarketscience.jackcess.util.ErrorHandler
Cause

java.lang.ClassNotFoundException


Could you guide me for what can be done to retrieve
me access datasource with Lucee v5.

Thanks for your help.
Pierre.

Milan Marković

unread,
Jun 7, 2016, 10:18:15 AM6/7/16
to Lucee
Hi Pierre,
put all jar files from ucanaccess.zip:
  • ucanaccess-3.0.3.1.jar
  • jackcess-2.0.0.jar
  • commons-lang-2.4.jar
  • commons-logging-1.0.4.jar
  • hsqldb.jar
in your lucee lib folder.

Pierre Larde

unread,
Jun 7, 2016, 10:26:46 AM6/7/16
to Lucee

Thanks a lot,

Yes with all files, it does work.

Great.

I will check more deeply tomorrow.

Thanks again,

Pierre.

Daniel Yataco

unread,
Nov 12, 2016, 7:50:59 PM11/12/16
to Lucee
I'm just getting time out, any idea?
Reply all
Reply to author
Forward
0 new messages