Posts Tagged SQL Server

Install a JDBC driver in SpagoBI

For those who don’t know what is SpagoBI, please read the following (copied from Wikipedia):

SpagoBI is the only entirely Open Source Business Intelligence suite, belonging to the free/open source SpagoWorld initiative, founded and supported by Engineering Group.
SpagoBI supports day-to-day and strategic business, both at the decision-making and operational levels. SpagoBI is a BI suite because it covers the whole range of analytical needs, supporting developers, testers and administrators in their daily activities.

SpagoBI is not very difficult to install but it is a bit more complicated to configure.
One of the problem I had was the following one:

GRAVE: Cannot open connection.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: 
Cannot load JDBC Driver class: com.microsoft.sqlserver.jdbc.SQLServerDriver

This exception is quite clear, it means that SpagoBI cannot load the JDBC driver for SQL Server. Indeed, I was connecting to a SQL Server database from one of my BIRT report.

What you have to do to fix this problem is:

  1. Download the driver JAR file from the Microsoft website: http://www.microsoft.com/download/en/details.aspx?id=2505
  2. Copy the JAR file into the following folder under the SpagoBI installation folder:
    webapps/SpagoBIBirtReportEngine/WEB-INF/platform/plugins/org.eclipse.birt.report.data.oda.jdbc_2.6.1.v20100909/drivers
    Note that the path might differ a little bit depending on the version of SpagoBI.
  3. Restart SpagoBI

, , , , , ,

2 Comments

Convert SQL Server to MySQL

Some time ago, I had to convert a Microsoft SQL Server database to a MySQL database. The main reason was the cost of the SQL Server license for such a small database.

Looking on the web, I found the following open source application:

Name: mssql2mysql
URL: http://sourceforge.net/projects/mssql2mysql/
Description: mssql2mysql is a python script used to create a SQL dump from a Microsoft SQL server that is ready to use with MySQL. Supports Schema and data dumping, including Primary Keys for each table, allows to dump all data or just a small portion of it.

To be honest, this tool wasn’t working as expected but it was a very good start! 🙂

Below is the list of changes I’ve made:

  • Add support for the bool data type;
  • Add support for the datetime data type;
  • Only convert tables and views of the ‘dbo’ owner;
  • Add the test columnas[6]==True on the primary key;
  • Add support for the uniqueidentifier data type;
  • Add support for the tinyint data type (SQL Server is tinyint unsigned by default, but not in MySQL!);
  • Add support for the default column values;
  • Add support for the bit data type (use tinyint instead of bit, go to this page for more information).

To download the amended script, please click on the following link: mssql2mysql.tar.bz2

This script worked perfectly fine for me. However, please note that my interest was focused on converting the database structure but not the content!

, , , , ,

1 Comment