Category: LibreOffice Base

LibreOffice Base: Connecting to MSSQL in Linux

First, download the SQL JDBC Driver from Microsoft.

  1. Extract the JDBC drivers somewhere. There’s a series of folders and a Jar file that we’ll be getting.
  2. Once you’ve downloaded that, open LibreOffice Calc or Writer.
  3. Go to Options->AdvancedScreenshot from 2015-02-23 13:38:37
  4. Click Class Path on the right and choose Add Archive from below:Screenshot from 2015-02-23 13:38:46
  5. Navigate to your file and choose sqljdbc4.jar
  6. Restart LO and you should be ready to connect.
  7. Open LO Base
  8. Choose Connect to an Existing Database, Type is JDBCScreenshot from 2015-02-23 13:48:01
  9. In the Datasource URL:
    1. sqlserver://ipaddress:1433;databaseName=DBNAMEHERE
  10. In the JDBC Driver Class:
    1. com.microsoft.sqlserver.jdbc.SQLServerDriver
  11. Enter your credentials and test, you should be good to go!

SQL Join Command Resulting in Duplicate Rows

If you find your JOIN command returning duplicate values and you’ve eliminated the usual suspects, check to see if the table being joined has a primary key. I had a 4 table JOIN statement where everything was fine when I did the first 3 JOINs, but once I added the 4th table, the results started returning multiple duplicate values. It turned out that table was missing a primary key.

After adding a primary key to the table, the query began functioning as expected.

Working Around LibreOffice Base’s DATE_SUB & DATE_ADD Limitation

Much to my surprise, LO Base can’t pass the DATE_SUB and DATE_ADD query unless you choose to “Run SQL Query Directly.” The problem with this is that a) you can no longer modify the query in Design View, and b) user input prompts no longer work (:prompt type items).

So, I came up with a messy workaround, which works using the SUBDATE function. It seems the problem with DATE_SUB( ‘2014-08-01’, INTERVAL 1 MONTH) is the “INTERVAL 1 MONTH” part. SUBDATE allows entering a number of days to subtract or the INTERVAL statement. Use the INTERVAL statement and it breaks, days = no problem.

Here’s my example to for a BETWEEN command to return the last day of last month and for the begin date, the first day 3 months ago:

	DATE_FORMAT(SUBDATE( DATE_FORMAT( MAKEDATE( :Yr, ( :Mon ) * 28 ), '%Y-%m-01' ),30*3), '%Y-%m-01') AS `Begin Date`,
 
	DATE_FORMAT(Last_Day(SUBDATE( DATE_FORMAT( MAKEDATE( :Yr, ( :Mon ) * 28 ), '%Y-%m-01' ),30)), '%Y-%m-%d') AS `End Date`,

The above prompts the user to enter the year (:Yr, can be 14 or 2014) and the month (:Mon, enter number). 30 seemed like a safe number to subtract. It basically gets the rough date of the correct month, which is then reformatted to be the 1st of the month and end of the month.

For my use, this is part of a multiple query, meaning if I want to run the report for last month (September 2014 as of this writing), another query is using the :Yr and :Mon to create a BETWEEN statement that looks like:

BETWEEN {D '2014-09-01' } AND {D '2014-09-30' }

Then the second query, using the “hacked” subdate function, is returning results between 2014-06-01 and 2014-08-31.

Just to recap:

  • Query 1 prompts user for the month and year they want to run the report.
  • Query 1 takes that, turns it into a begin and end date for a BETWEEN statement and outputs that to something like `Current_Month_Column`
  • Query 2 automatically grabs the same input, pipes the input to create a begin date and and end date of the month prior to the month returned for Query 1.

Hopefully that will help others running into the same issue.