Thursday, 26 April 2012

How to install RMySQL package on R for Windows 7


1. DOWNLOAD SOFTWARE FROM THE FOLLOWING LINKS:
              a. R2.13.2:  Download R from http://cran.stat.sfu.ca/index.html

  b. RTools 214:  Download RTools from http://cran.cict.fr

  c. RMySQL 0.8-0.tar.gz: Download RMySQL from

              d. MySQL Server 5.0: download it from http://dev.mysql.com
               
              e. RSTUDIO (optional): download it from http://rstudio.org


2. SET THE FOLLOWING ENVIRONMENT VARIABLES

            a. MYSQL_HOME : <drive>/path to MySQL installation folder
                                                  e.g. MYSQL_HOME= C:\Program Files\MySQL\MySQL Server 5.5\         

b. R_HOME: <drive>/path to R installation
                                e.g. R_HOME=C:\Program Files\R\R-2.13.2\

c. PATH: Modify path to accommodate the above variables. Be sure that the following paths are
included in your Windows PATH variable:
<DRIVE>\Rtools\2.14\bin
<DRIVE>\Rtools\2.14\MinGW\bin
<DRIVE>\Rtools\2.14\MinGW64\bin


3.  CREATE FOLDER AND COPY FILES

a. OPT: Create a folder OPT under C:\Program Files\MySQL\MySQL Server 5.5\lib and copy  
libmysql.lib  the above path. Also copy libmysql.dll to <drive>\<path>\R\R-2.14.0\bin\ (64 bit) Or <Drive>\<path>\R\R-2.14.0\bin\i386\ (32 bit) and to C:\Windows\System32.

b. Renviron.site: create or edit a file <DRIVE>\<path>\R\R-2.14.0\etc\Renviron.site and add a
line: MYSQL_HOME =”C:/Program Files/MySQL/MySQL Server 5.5/”
NB: USE FORWARD SLASH AND DOUBLE QUOTES HERE

c. libMySQL.dll: Copy this file to C:\Program Files\R\R-2.13.2\bin\i386 as well as C:\Program
Files\R\R-2.13.2\bin


4. RUN COMMANDS

                a. Install.Packages: Run R GUI by clicking on the R icon on desktop or from Start menu. Type
INSTALL.PACKAGES(“RMySQL”,type=”Sources”). This will download the required software from repositories.


b. Command Prompt: Copy the downloaded zip file (in step 4.a.) and paste it under R
installation folder. Go to start menu and open Command Prompt. Go to the R installation folder and type R CMD INSTALL RMySQL_0.8-0.tar.gz

COMMANDS:
>library(RMySQL)
>drv = dbDriver("MySQL")
 >con = dbConnect(drv,host="localhost",dbname="test",user="root",pass="password")
>album = dbGetQuery(con,statement="select * from t_master")
>album
 Have Fun

Wednesday, 25 April 2012

Connect Ireport to MS SQL Server database

To create a MS SQL Server database in Ireport, you need a sqljdbc driver.

Steps to create MS SQL Server database in Ireport:-

1:- Download sqljdbc (1033\sqljdbc_3.0.1301_enu.exe) driver from
                              http://www.microsoft.com/download/en/details.aspx?id=21599

After downloading driver, extract the sqljdbc4.jar file and paste into this location

                            C:\Program Files \Jaspersoft\iReport-4.0.2\ireport\modules\ext
2:-Start Ireport and go to Tool and then select Optios
                                    


                                            
Click on Class Path tab, click on Add jar tab and browse the sqljdbc4.jar file

                                             

After adding the jar file restart the Ireport.
Now you can create MS SQL Server database in Ireport.