Wednesday, 18 November 2015

LJ Pi SQL Server and SAP Crystal Reports

I’m relatively new to SAP Crystal Reports and I thought a good way to get a grasp of how the database connectivity works would be to create my own database and then connect Crystal to it.

Since we were doing some work with Raspberry Pi’s in the office I seized the opportunity and grabbed one. A bit of research later and I discovered that MySQL server can be run on the pi.

I had flashed the Raspberry Pi’s SD card with Raspbian Wheezy. When booted up open up a terminal window and the first thing to do is update the software list that’s available for Raspbian:

sudo apt-get update

The next step involves updating your current software to make sure you have the most up to date versions on there:

sudo apt-get upgrade

Now it’s time to install MySQL server:

sudo apt-get install mysql-server

You will be prompted to confirm the install and after it’s installed you will again be prompted to set a ‘root’ user password.

Another package that is worth installing is php5-mysql:

sudo apt-get install php5-mysql

This will add the MySQL libraries that will allow PHP access.
At this point you are ready to start up your MySQL server by typing:

mysql -p -u root

the –p item is how you request to put in a password and the –u is specifying which user you are wanting to log in as. At this point you will need to remember the password you put in earlier when installing MySQL server.

At this point you should be logged in and ready to create a new database, you can do this by using the following command:


For the purpose of testing I imported a SQL database from a dump .sql file. I was having a few problems importing it so I decided to go the long way round and copy the SQL into the terminal window line by line. Luckily you can do this in bulk and it ticks away loading all the rows in. To do this you will need to specify the database you would like to insert the SQL into:

mysql use my_database_name

You can then open the .sql file in your preferred text editor and copy the whole thing. You can then right click into the terminal window and this will paste it all into it. It will then go about creating the database for you. I don’t know if this will work with very large databases, the one I used comprised of 3 tables and around 4000 rows.
At this point you will need to specify who can access the database, the easiest way to do this is to allow access from all IP address with the following command:

flush privileges;

However if you want to only allow access from one IP address then the best way to do this is:

flush privileges;

To make sure your crystal reports can connect to the MySQL database drivers can be downloaded from the MySQL website:

Once installed go to the ODBC connections and add a new connection, choose the MySQL connector which will then prompt you for the IP address of the Pi and for your MySQL log on details.

Click test connection and then hopefully it will be successful.

The next step is to open Crystal reports and add a new database connection using the ODBC option. If all has gone well the connection you have just set up will be in the list. Once selected click ok and you will be ready to import tables and fields to be able to report from.

The performance of the Raspberry Pi was fairly quick and snappy, I made a few simple reports and found refreshing them fast and efficient. 

Written by:  Luke Johnson, Business Intelligence Support Technician, DSCallards

No comments:

Post a Comment