How do I set up a JDBC connection for Microsoft SQL?

English
#1

I have a Microsoft SQL Server Master Database File from a proprietary program that I need to access using LibreOffice Base.

My research got me this far:

  • I installed a JDBC (Java DataBase Connectivity) driver for Microsoft SQL Server downloaded from Microsoft.
  • I installed a Java Runtime Environment (JRE) downloaded from Java, taking care to choose the 64-bit version.
  • I started the LibreOffice new database wizard to create an ODB (OpenDocument dataBase file) database connected to the Microsoft SQL Server Master Database File, helped by this tutorial: LibreOffice Base: Connect to any kind of database - Prahlad's Knowledge Wiki
  • Step 1 in the Database Wizard was easy: ‘Select “Connect to an existing database” radio button, and then select JDBC from the dropdown’ as instructed in the tutorial.
  • But step 2 ‘Set up JDBC connection’ has me stumped. The writer of the tutorial uses Unix and his example dialogue box is for SQLite. I use Windows and need to connect to Microsoft SQL. What do I enter in the two fields in the dialogue box of step 2?? This is the dialogue box of the tutorial: https://sites.google.com/site/prahladswiki/_/rsrc/1478663140978/tutorials/libreoffice-base-connect-to-any-database/lo_jdbc.png
#2

Have not done this so have no real answers that are tested.
.
Look at → LibreOffice Base: Connecting to MSSQL in Linux
.
Items 9 & 10 may be of help.

#3

Thank you very much for the pointer, which is very relevant, but I still didn’t quite succeed. Items 9 & 10 are:

  1. In the Datasource URL:
    sqlserver://ipaddress:1433;databaseName=DBNAMEHERE
  2. In the JDBC Driver Class:
    com.microsoft.sqlserver.jdbc.SQLServerDriver

Item 10 seems to be the most straightforward: Copy and paste into the second field in the dialogue box. The ‘Test class’ button becomes active, but when I push it, LibreOffice responds that it is unable to load the JDBC driver. Not sure if this is because the first field in the dialogue box is incorrectly filled in. (The ‘Test class’ button becomes active even if the first field is empty.)

Item 9 (the first field in the dialogue box): What IP address am I supposed to enter?? Aren’t I supposed to be able to connect to the locally stored SQL Server Master Database File (.mdf) without some external SQL server?

#4

Only you nows where your server is located. For localhost you may use 127.0.0.1

#5

LibreOffice built-in help provides this example if one is using the MySQL JDBC driver:
«jdbc:mysql://servername/databasename»

  • What string should “mysql” be replaced with when using Microsoft SQL?
  • Should I really enter an IP address when the Microsoft database file is stored locally on the C: drive?
  • Am I correct in believing that I should be able to access the Microsoft database file without having Microsoft SQL server installed? The database file is created by a proprietary program (Mamut) that I will no longer be paying the licence fee for, but I need to retrieve some of the data. There is no proper export functionality in the proprietary program.
#6

No. You do need MS SQL server installed. Base connects to servers not to data files. Base is nothing more than a front end to databases. HSQLDB shipping with Base includes the engine. Some may not be apparent but are there. Access is an example. The server is the engine to feed the data to and from Base.

#7

Hm. Thank you very much for educating me!
.
I do have the processes sqlservr.exe (“SQL Server Windows NT (32-bit)” / “SQL Server (MAMUT)” and sqlwriter.exe (“SQL Server VSS Writer - 64 Bit” / “SQL Server VSS Writer”) running, even without having started Mamut after the last reboot, and one of them locks the database files.
.
I guess I got MS SQL Server as part of Mamut, and chances are that it will keep running even after my Mamut licence expires (at which point Mamut itself will refuse to proceed beyond the opening screen).
.
The location of sqlservr.exe is “C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.MAMUT\MSSQL\Binn”. The location of sqlwriter.exe is “C:\Program Files\Microsoft SQL Server\90\Shared”. Do you have any idea what I should enter for Datasource URL, or how I can find out?
.
Is there an issue with 32-bit vs. 64-bit? I have 64-bit versions of LibreOffice, JDE and presumably also the JDBC driver.

#8

Sorry, but as stated in my opening comment I have not done this with MS SQL. Depending upon the server (documentation helps here) it may be addressed as localhost or address and port and database name. Do not have info on 32 vs 64 bit items for this setup either.

#9

So the first part should be
sqlserver://127.0.0.1:1433;

but only you may know how you/somebody named the database.

Check for help on mamut. They may provide some admin-tool

#10

The named 3 components have to match: all 64bit or all 32. As the connection is over ip+port it should not matter what is on the other side.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.