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:
CREATE DATABASE MY_DATABASE_NAME;
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_nameYou
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:
GRANT ALL PRIVILEGES ON MY_DATABASE_NAME.* TO 'MY_USERNAME'@'%' IDENTIFIED BY 'MY_PASSWORD';flush privileges;
However if you want to only allow access from one IP address
then the best way to do this is:
GRANT ALL PRIVILEGES ON MY_DATABASE_NAME.* TO 'MY_USERNAME'@'12.34.56.78' IDENTIFIED BY 'MY_PASSWORD';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.
Visit: www.thebusinessobjectspeople.com.
Written by: Luke Johnson, Business Intelligence Support Technician, DSCallards
No comments:
Post a Comment