Complete bin.zip distribution for download

[gordthompson]

Suggestion:

Earlier versions of UCanAccess made a complete bin.zip file available for download from SourceForge, e.g., UCanAccess-5.0.1.bin.zip contained

UCanAccess-5.0.1.bin
    lib
        commons-lang3-3.8.1.jar  
        commons-logging-1.2.jar  
        hsqldb-2.5.0.jar  
        jackcess-3.0.1.jar
    licenses
        apache-license-2.0.txt  
        hsqldb_lic.txt  
        hypersonic_lic.txt  
        lgpl-2.1.txt  
        licenses.txt
    loader
        ucanload.jar
    console.bat  
    console.sh  
    copyright.txt
    ucanaccess-5.0.1.jar  
    version.txt

This was very convenient for people who wanted to use UCanAccess without necessarily having a Java project that could take advantage of Maven. One example would be a Python project that could use UCanAccess along with JayDeBeApi as described here. The console.bat and console.sh utilities were also very useful for troubleshooting.

[spannm]

Thanks for the suggestions, @gordthompson.
This should not be too much effort I believe.
I will look into it, or if you have a pull request for this, please send it in.

[spannm]

I've added an assembly to create the binary distribution and script files.
@gordthompson please have a look

[gordthompson]

I think I understand what it is doing, although it's all new to me. Will it include loader/ucanload.jar ?

[spannm]

Unpacking zip:

unzip ucanaccess-5.1.2-SNAPSHOT-bin.zip 
rm ucanaccess-5.1.2-SNAPSHOT-bin.zip
tree

Currently looking like this:

.
├── console.bat
├── console.sh
├── lib
│   ├── hsqldb-2.7.1.jar
│   └── jackcess-5.0.0-SNAPSHOT.jar
├── LICENSE.txt
└── ucanaccess-5.1.2-SNAPSHOT.jar

Will it include loader/ucanload.jar?

Not yet at least. What's this for and still needed? Can't remember seeing those at time of fork.

[gordthompson]

Currently looking like this:

Does the package no longer depend on commons-lang3 and commons-logging?

Will it include loader/ucanload.jar?

Not yet at least. What's this for and still needed? Can't remember seeing those at time of fork.

It's for configuring other apps, e.g.,

https://askubuntu.com/a/519571
https://stackoverflow.com/a/36608661/2144390

[spannm]

Does the package no longer depend on commons-lang3 and commons-logging?

Correct, they're out. UCanAccess now utilizes Java Platform/System Logger, which is part of the JDK since Java 9.

Can you tell whether UcanloadDriver in ucanload.jar is still required today?

[gordthompson]

It looks like Jackcess still depends on commons-lang3 and commons-logging, though

https://github.com/jahlborn/jackcess/blob/22bf8a8642c84fc500f5c66ef59de1f7211f93a3/pom.xml#L247C1-L257C1

Can you tell whether UcanloadDriver in ucanload.jar is still required today?

It would still be useful for the cases I cited above. It discovers the specific versions of the dependencies so a version change for one of those dependencies won't break the configuration of the consuming app (e.g., LibreOffice Base).

[spannm]

Hi Gord,

It looks like Jackcess still depends on commons-lang3 and commons-logging, though

Not for much longer. I've forked jackcess - it has no dependencies other than Apache POI

I'll see whether I can play with LibreOffice and UCanAcces.

[spannm]

Hi @gordthompson,

good news, I am able to read/write Microsoft Access databases from LibreOffice Base using the latest UCanAccess snapshot.
Simply add three jars to the JVM classpath in LibreOffice. Screenshot:

To make this a little more straight-forward I have added a fat-jar assembly to the project, a single jar with all dependencies unpacked i.e. ucanaccess-<version>-fat.jar.
Using this fat jar, there is only one jar to add to the LibreOffice JVM classpath (or other client programs) in order to make JDBC connections to Microsoft Access databases.
What's more, the fat jar is executable with main class net.ucanaccess.console.Main and thus serves as an alternative to the bin.zip distrib and console.sh / console.bat.

spannm@tuxedo ~/projects/github/spannm/ucanaccess $ java -jar target/ucanaccess-5.1.2-SNAPSHOT-fat.jar
Please, enter the full path to the access file (.mdb or .accdb): /tmp/helloworld.accdb
UCanAccess>
UCanAccess version 3.x.x
You are connected!! 
Type quit to exit 

Commands end with ; 

Use:   
   export [--help] [--bom] [-d <delimiter>] [-t <table>] [--big_query_schema <pathToSchemaFile>] [--newlines] <pathToCsv>;
for exporting the result set from the last executed query or a specific table into a .csv file

UCanAccess>

[spannm]

Link to Sonatype snapshot repo

Look for *-fat.jar and *-distrib.zip

[ravenAtSafe]

If the new fat jar were also shaded it would make my life slightly easier 😉

(We currently build a shaded version of ucanaccess in order to eliminate the possibility of jar conflicts.)

[spannm]

Hi @ravenAtSafe,
I am sure we can find a good solution. Note that dependencies have been reduced to a minimum (in the current snapshot which will be released soonish). Possibly potential conflicts are already resolved?
Please open open a new issue and provide a few details. Thanks!

[spannm]

Hi there!

I have implemented changes to create a shaded executable uber jar. The intention is to have this one jar that fulfills all requirements from requests #7 and #8.

The jar is included in the binary distrib. Everything except UCanAccess itself is shaded (prefix shaded.) The start scripts (console.*) are actually no longer required but continue to exist for the time being. This is still work-in-progress (snapshot) and not released yet.

Usage instructions

• Download ucanaccess-5.1.2-SNAPSHOT-distrib.zip
• or add to project by Maven dependency:

<dependency>
  <groupId>io.github.spannm</groupId>
  <artifactId>ucanaccess</artifactId>
  <version>5.1.2-SNAPSHOT</version>
  <classifier>distrib</classifier>
  <type>zip</type>
</dependency>

• Unpack to desired location on your machine

To enter the UCanAccess console

• Run console.sh or console.bat -or-
• double-click the jar -or-
• invoke the software by running java -jar <jarName>

LibreOffice MS Access database access

LibreOffice

• Options > LibreOffice > Advanced > Java options
• Select desired JRE
• Edit classpath by clicking 'Class Path...'
• Add ucanaccess-5.1.2-SNAPSHOT-uber.jar to classpath from your download/install location
• Enter advanced options if needed, confirm and exit dialog

LibreOffice Base

• In wizard select to connect to an existing database (JDBC)
• Enter database url without jdbc: prefix: ucanaccess://full_path_to_database e.g. jdbc:ucanaccess:///home/spannm/projects/github/spannm/ucanaccess/src/test/resources/testdbs/accessLike.mdb
• Enter fully qualified UCanAccess driver class name net.ucanaccess.jdbc.UcanaccessDriver
• Test by clicking 'Test class'
• Proceed in wizard, confirm and save file
• Ready to use your MS Access database in Base

[spannm]

Hi @gordthompson, could you review the solution whether it covers your requirements? Thx

[gordthompson]

Hi @spannm . I just tested Python/JayDeBeApi using code adapted from here and it works. I did get a couple of warnings

$ python3 jdb_test.py 
Apr. 23, 2024 8:34:18 A.M. net.ucanaccess.jdbc.UcanaccessConnection logStatementWarning
WARNING: Please use a simple statement (not a PreparedStatement)to execute DDL (e.g.,CREATE TABLE)
Apr. 23, 2024 8:34:19 A.M. net.ucanaccess.jdbc.UcanaccessConnection logStatementWarning
WARNING: Please use a simple statement (not a PreparedStatement)to execute DDL (e.g.,CREATE TABLE)
(1, 'Gord')

but they are probably just a JayDeBeApi thing, so I'm satisfied. Thanks!

[spannm]

Hi @gordthompson,

thanks for testing.

These warnings are issued whenever client code calls Connection.prepareStatement but UCanAccess thinks it should really be using Connection.createStatement. I will make the warning more explicit by appending the sql i.e.:
Please use a simple statement (not a PreparedStatement) to execute DDL (e.g. CREATE TABLE): {sql}

You could then investigate where JayDeBeApi issues the calls in question and send them a patch.

The distrib zip and shaded fat jar will be part of the upcoming release.

cheers!
Markus

[gordthompson]

You could then investigate where JayDeBeApi issues the calls in question and send them a patch.

Someone did that over 7 years ago, but nothing came of it. :(

baztian/jaydebeapi#29

I'm not too concerned since it's just a warning. (And personally I no longer officially support Access, anyway.)

[ghomem]

Hello,

I am interested in trying UCanAccess with Libreoffice base.

Are you still planning to release a binary distribution?

[ghomem]

This seems to be the latest build available:

https://s01.oss.sonatype.org/content/repositories/snapshots/io/github/spannm/ucanaccess/5.1.2-SNAPSHOT/ucanaccess-5.1.2-20241029.072454-27-distrib.zip

but I am not sure whether this what we are after. Also, I don't find a fat jar.

An oficial github release would be excellent :-)

[gordthompson]

@ghomem - Did you notice the instructions here? #7 (comment)

@spannm - I agree that an official release would be helpful. I have a number of old SO posts that could be updated to include a new official download location.

[ghomem]

@ghomem - Did you notice the instructions here? #7 (comment)

Yes I did, but the mentioned file does not exist anymore. And the file I managed to cherry pick (two comments above) does not work.

@spannm - I agree that an official release would be helpful. I have a number of old SO posts that could be updated to include a new official download location.

I can test on Ubuntu 24.04 + Libreoffice Base.

[gordthompson]

I just downloaded

https://s01.oss.sonatype.org/content/repositories/snapshots/io/github/spannm/ucanaccess/5.1.2-SNAPSHOT/ucanaccess-5.1.2-20241029.072454-27-distrib.zip

and I can see "ucanaccess-5.1.2-SNAPSHOT-uber.jar" in there.

[ghomem]

I just downloaded

https://s01.oss.sonatype.org/content/repositories/snapshots/io/github/spannm/ucanaccess/5.1.2-SNAPSHOT/ucanaccess-5.1.2-20241029.072454-27-distrib.zip

and I can see "ucanaccess-5.1.2-SNAPSHOT-uber.jar" in there.

With that particular file you linked and adding "ucanaccess-5.1.2-SNAPSHOT-uber.jar" to the classpath I was able to load the tables on Libreoffice.

[ghomem]

It seems to work, at lest for this use case. Would you consider oficializing a release to assign a more formal nature to this file?

Thank you for the support.

[spannm]

Hi @ghomem, hi @gordthompson

a release is overdue, I've been wanting to release the software for weeks but didn't get around to it yet. You know how it is :-
I'll get to it very soon - promised.

[ghomem]

Hi @ghomem, hi @gordthompson

a release is overdue, I've been wanting to release the software for weeks but didn't get around to it yet. You know how it is :- I'll get to it very soon - promised.

Thank you for your work!

[spannm]

Fixed in release 5.1.2

[ghomem]

Hi @spannm ,

I don't find in

https://github.com/spannm/ucanaccess/releases/tag/ucanaccess-5.1.2

a link to the distribution binary.

What am I missing?

[spannm]

Hi @ghomem,

I don't find in
https://github.com/spannm/ucanaccess/releases/tag/ucanaccess-5.1.2
a link to the distribution binary.

You can now find the release artifacts in Maven Central.
I've also added them to the release page for convenience (I don't have an automated workflow for this (yet)).

cheers & happy halloween

[ghomem]

@spannm

Thank you for the efforts. Now I see the files in the release page, which is very convinient.

Best regards and happy halloween