How to install Rserve package on R

Rserve is a TCP/IP server which allows other programs to use facilities of R (see www.r-project.org) from various languages without the need to initialize R or link against R library.

You can download Rserve from http://rosuda.org/Rserve/ 
Otherwise start R and type 
>install.packages("Rserve", type="source")  and select  any CRAN mirror .

.........
..........

  problem copying .\Rserve.exe to C:\Program Files\R\R-2.13.2\library\Rserve\libs\x64\Rserve.exe: Permission denied
** R
** inst
** preparing package for lazy loading
** help
*** installing help indices
** building package indices ...
** testing if installed package can be loaded

* DONE (Rserve)

The downloaded packages are in
        ‘C:\Users\sharad\AppData\Local\Temp\Rtmp6FzWvP\downloaded_packages’


This shows Rserve package has installed completely.
Go to C:\Program Files\R\R-2.13.2\library\Rserve\libs\x64\Rserve.exe  path and copy Rserve.exe file to
                C:\Program Files\R\R-2.13.2\bin
after copying Rserve.exe file just check whether Rserve is running or not by doubble clicking on Rserve.exe file.
        If it runs successfully then it ok otherwise it will ask for R.dll file which is inside
the C:\Program Files\R\R-2.13.2\bin\x64 directory  to     
                                    C:\Program Files\R\R-2.13.2\bin
 and run again Rserve.exe, if it again ask any file just copy that file from 
                     C:\Program Files\R\R-2.13.2\bin\x64      to   
                           C:\Program Files\R\R-2.13.2\bin
Now Rserve.exe should run properly.

How to start Rserve:-

1:-You can start Rserve from R , just type:

>library("Rserve")
>Rserve()

2:- Other way to stat Rserve through command prompt

            R CMD Rserve

Access SQL Server database from Excel

Open an Excel sheet

Enter Server Name and Log on credentials.   

 

Select database and table name.

                                                                                                               

Finish

 

OK

 

SQL Server- Import rows from CSV/txt files to an existing SQL Server table using Bulk Insert

In this post I am explaining about Bulk Insert utility in SQL Server, Using bulk insert we can import CSV (comma separated value) or txt file data to an existing table of SQL Server.

For Example:-

create table student
(
row_id int ,
fname varchar(15),
lname varchar(15)
);


Create a CSV file or a txt file with the following content and save it to "C:\SQL_Server\test.txt" location :-

1, Adoms ,Mcknight
2,Joe,Smith
3,Joe,Doe
4,Alex,Smith

Open a notepad and copy this statement and save as a batch (.bat) file

sqlcmd -U sa -P root -S "myhome-pc\my" -d test -q " BULK INSERT students FROM 'c:\SQL_Server\test.txt' WITH (FIELDTERMINATOR = ',' , ROWTERMINATOR = '\n')  select * from students"   -o  C:\SQL_Server\output.txt 

 Where
          -U  = User name
          -P   = password
          -d   = Database name
          -q  = Query


 You can see help for sqlcmd-
Sqlcdm -?

test.txt is a file name from where I want to insert rows in table students.
Output.txt file name which will automatically generate when we run .bat file in C:/SQL_Server directory. 
                Select * from students _results is the output query whose result will show on output.txt file.
We can run directly .bat file by double click or from command prompt.

How to take the dump of MySQL database?

For Windows :-

Open a notepad and copy

            C:\"Program Files"\MySQL\"MySQL Server 5.5"\bin\mysqldump -u root -p  --add-drop-table database_name > database_backup.sql

 and save as a batch(.bat) file (check whether your mysql path is right or not)
         
               Where U= user name of the databas (eg: root)
                            P= password of database
                             database_name = is a database of which you want to take backup
                             database_backup.sql= is a backup file of that database

 To run this batch file just click on the batch file it will create a .sql file
For Linux server:-

                                 mysqldump -u root -proot  --all-databases --port=3306  | gzip >  backup_database-`date +%Y-%m-%d`.sql.gz


and save as a .sh file and to run this file type
                                  sh file_name.sh
it will create a .sql.gz file with current data string.
 

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

http://biostat.mc.vanderbilt.edu/wiki/main/RMySQL/RMySQL_0.8-0.tar.gz

              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

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.