Ask Your Question
0

Using Java ODBC on Base databases

asked 2018-10-28 22:11:47 +0100

UnicornLynn gravatar image

updated 2018-10-28 22:19:54 +0100

EasyTrieve gravatar image

I would like to use Java and ODBC to connect to my .odb database. In Access, I can use a connection string that looks like:

jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=mydb.mdb;DriverID=22;READONLY=false;

However, there is apparently nothing equivalent for Base.

I have been converting from MS Office to LibreOffice, and have had little trouble using Writer and Calc to get equivalent results. So far, however, Base has been fairly disappointing. I have a number of Java applications that use Access .mdb files and would like to convert them to .odb, but this seems to be the impossible dream. Am I wrong?

I realize this question is essentially the same as both https://ask.libreoffice.org/en/questi... and https://ask.libreoffice.org/en/questi.... However, those are 4 and 3 years old respectively and, while it may qualify under the definition of insanity, I was hoping for a different answer now.

Thanks.

edit retag flag offensive close merge delete

Comments

When I moved my Access stuff to GNU/Linux it turned out to be a quite involved process. To ease this transition I first copied my tables from Access to MariaDB. Then I reconfigured Access to point to the tables in MariaDB so I could keep using Access for awhile longer. Now I could use HeidiSQL running under Wine to help with the type changes needed, e.g. Memo to Text, timestamp issues, and yes/no to tinyint, etc. Then I started work on getting LibreOffice to talk to the tables in MariaDB.

EasyTrieve gravatar imageEasyTrieve ( 2018-10-28 22:29:27 +0100 )edit

Some of the notes from this process I left here.

EasyTrieve gravatar imageEasyTrieve ( 2018-10-28 22:30:05 +0100 )edit

Also as I recall, when I tried to do what you are working on, I was never able to get ODBC to work for me. I finally gave up and came at it from a different angle. But that was awhile ago, and some things have been fixed, and I might have missed something back then. In any event, when you finally do make some progress, please leave a bread crumb trail here of what has worked for you. Thanks.

EasyTrieve gravatar imageEasyTrieve ( 2018-10-29 21:44:35 +0100 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2018-11-03 18:41:44 +0100

UnicornLynn gravatar image

I appreciate the responses I have received. Unfortunately, it seems that this is still the impossible dream. I guess I will continue to do as I have been: use MySQL when it is possible to have the server running on the target machine and Access when it isn't. Oh, well...

edit flag offensive delete link more
0

answered 2018-10-29 06:35:38 +0100

Ratslinger gravatar image

Hello,

First know I am not into Java code - just dabbled a bit. Have created a Java application which retrieves data from a database used in Base to generate a report. Did this about 18 months ago.

Now what you are talking about actually has nothing to do with Base but with a database connection. The typical connection is done using JDBC, ODBC or SBDC. With these type of connectors you can use Base to converse with many different databases. The easiest database for people to use is the HSQLDB v1.8 embedded (soon Firebird embedded). These embedded databases may be easy to use but access from external sources is next to impossible. So the next step is to split the database out of the Base enclosure and expose the data to outside sources. This is done using database servers (HSQLDB, MySQL, PostgreSQL, etc) or a process (single user) of running HSQLDB split from the .odb (see -> [Wizard] Create a new 'split' HSQL 2.x database ).

Once you have a database external to the Base .odb file, it is a matter of using the proper connector & string for your connection. I have only used a JDBC connection in Java with this one to an HSQLDB database (applicable portion of code):

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class OldItems {

    private Connection connection;

    public OldItems() {
        try {
            Class.forName("org.hsqldb.jdbcDriver");
            connection = DriverManager.getConnection("jdbc:hsqldb:file:////home/THE_DIRECTORY_NAME/THE_DATABASE_NAME;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false", "SA", null);
            build();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

Have also done this to a MySQL database but it uses a different driver & connection string.

Much of connection information to HSQLDB is found in Chapter 1 of the manual -> Running and Using HyperSQL

Did a quick search for ODBC but found nothing for Java. I'm sure there is some example out there. It should not matter which type of connection you use; just the correct driver and connection string.

Sorry, but this is probably the limit of my Java capabilities for help.

Base is not a database (just as Access isn't) and you need the database to be external to the .odb file for the connection.

edit flag offensive delete link more

Comments

I have used MySQL for Java data, but the issue I have with it for my purposes is that you have to actually have it running on the target machine. If I use Access, I can have the .mdb file available as a resource and use SELECT, INSERT, UPDATE, and DELETE queries, and I would like the same capability for Base. I realize that there needs to be a driver and then the correct connection string, it's just that I can't find such things and I was hoping I was mistaken.

UnicornLynn gravatar imageUnicornLynn ( 2018-10-29 15:11:09 +0100 )edit

Of course you cannot access data in a server database unless the server is up & running. If you want to access data without a server and without Base running, use the split database mentioned in my answer.

You also mention SQL statements. Have them in your code or store them in the database.

Ratslinger gravatar imageRatslinger ( 2018-10-29 18:29:53 +0100 )edit

I currently have a Java class that deals with my Access database, including all the SQL statements I need for my application. When it makes sense to expect the MySQL server to be running on the target machine, I can (and have for some applications) simply change the connection string and continue to use this same class. I was hoping for the same capability for Base, so that I could get completely away from Access.

UnicornLynn gravatar imageUnicornLynn ( 2018-11-03 18:40:55 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-10-28 22:11:47 +0100

Seen: 153 times

Last updated: Nov 03 '18