Integrating BIRT in Eclipse

Download mysql-connector-java-5.1.22-bin.jar and put it in birt3.7/web-inf/lib folder.

Copy the lib jars from /WebViewExample/Web-Inf/lib to my application web-inf/lib directory.
Select all jar files in lib folder and select 'add in build path option' in Eclipse IDE.

Copy the viewer.properties file directly under WEB-INF.

Create the platform folder in my WEB-INF directory in Eclipse project.

Copy webcontent folder from BIRT Runtime to webapp folder in Eclipse project.

Copy server-config.wsdd from birt<version>Runtime/web-inf to Eclipse project WEB-INF folder.


Put test.rptdesign report files directly under the webapp/reportforms folder in Eclipse project.


Add following lines in web.xml of the Eclipse application project:-

<servlet>
<servlet-name>EngineServlet</servlet-name>
<servlet-class>org.eclipse.birt.report.servlet.BirtEngineServlet</servlet-class>
</servlet>
<servlet>
<servlet-name>ViewerServlet</servlet-name>
<servlet-class>org.eclipse.birt.report.servlet.ViewerServlet</servlet-class>
</servlet>

<servlet>
<servlet-name>ImageServlet</servlet-name>
<servlet-class>net.sf.jasperreports.j2ee.servlets.ImageServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>ImageServlet</servlet-name>
<url-pattern>/image</url-pattern>
</servlet-mapping>

<servlet-mapping>
<servlet-name>ViewerServlet</servlet-name>
<url-pattern>/frameset</url-pattern>
</servlet-mapping>

<servlet-mapping>
<servlet-name>ViewerServlet</servlet-name>
<url-pattern>/run</url-pattern>
</servlet-mapping>

<servlet-mapping>
<servlet-name>EngineServlet</servlet-name>
<url-pattern>/preview</url-pattern>
</servlet-mapping>

<servlet-mapping>
<servlet-name>EngineServlet</servlet-name>
<url-pattern>/download</url-pattern>
</servlet-mapping>

<servlet-mapping>
<servlet-name>EngineServlet</servlet-name>
<url-pattern>/parameter</url-pattern>
</servlet-mapping>

<servlet-mapping>
<servlet-name>EngineServlet</servlet-name>
<url-pattern>/output</url-pattern>
</servlet-mapping>

<servlet-mapping>
<servlet-name>EngineServlet</servlet-name>
<url-pattern>/extract</url-pattern>
</servlet-mapping>

Build, Deploy the project and open in browser to test.

Download BIRT from this link:-
http://www.eclipse.org/birt/phoenix/ 

SPRING Roo commands

// open eclipse with spring roo installation
// springsource -> sts-2.9.2.RELEASE -> STS.sh
// script --file /home/meena/Desktop/meena/my_spring_roo/spring_roo_commands.roo


// setting up database provider
jpa setup --provider HIBERNATE --database HYPERSONIC_IN_MEMORY

jpa setup --provider HIBERNATE --database MYSQL --databaseName myDb--hostName 123.1.1.9 --userName user1--password pass@123


// creating class in roo proj
entity jpa --class ~.MyClass

entity jpa --class ~.master.domain.Lov --identifierColumn LOV_PK --identifierField LovPk --table LM_LOV

// declare variables in class
field string --fieldName message --notNull
field boolean --fieldName ActiveYN --column LOV_ACTIVE_YN

// creates .war file of proj
web mvc setup

// building all source files in mypkg
web mvc all --package mypkg


// create class with roo annotations to include toString() automatically for all variables
class --class in.mydomain.Employee --rooAnnotations
field number --fieldName empID --type int --notNull --unique
field string --fieldName empName --notNull

// to give custom name to toString method of a class
// @RooToString(toStringMethod="myTostring")

// to prevent a field from displaying through toString()
// @RooToString(excludeFields={"empName"})

class --class ~.domain.Address --rooAnnotations
field string --fieldName streetName --notNull
field string --fieldName city --notNull
field string --fieldName country --notNull

// to modify instead of recreating or overriding the class definition
focus --class ~.Employee
field other --fieldName empAdd --type in.traccion.Address --notNull
field date --fieldName dateOfBirth --type java.util.Date

// To remove or modify field in class, make changes in IDE

// to create interface
interface --class in.mydomain.MyInterface

// To set up Hibernate as the JPA provider for your application
persistence setup --provider HIBERNATE --database MYSQL --databaseName roodb --username=root password=root

class --class in.traccion.ParentClass1
class --class in.traccion.ChildClass1 --extends ParentClass1

focus --class ~.ParentClass1
field number --fieldName parentid --type int --min 0 --max 1000 --unique
field string --fieldName parentName

focus --class ~.ChildClass1

// roo> database properties list
// database.driverClassName = com.mysql.jdbc.Driver
// database.password = root
// database.url = jdbc:mysql://localhost:3306/roodb
// database.username = root

// to set username of a db
database properties set --key database.username --value root
database properties set --key database.password --value root
database properties set --key database.initialPoolSize --value 10
database properties remove --key database.url
database properties set --key database.modified.url --value jdbc:mysql://localhost:3406/roodb

// Right-click on project in project explorer & select Run as Server

Linux commands

ls -> to list files & directories in a directory
ls -l -> to list files/folders in a directory with permission sets

mkdir <directory_name> -> creates a directory
rmdir <dir> -> removes directory

chmod -R 777 * -> gives all permissions to all files & files within subfolders in a directory

vi <file_name> -> editor to open, edit files in terminal
exit -> to close terminal
rm <file_name> -> removes a file
rm -rf <folder_name> to remove directories that are not empty

mv <file_name> <destination_directory> ->  moves a file from a folder to another
mv <directory> <destination_directory> ->  moves a folder from a folder to another
mv <file1> <file2> <destination_directory> -> moves two files in a folder siultaneously

cd <path> -> changes directory path to a particular location
cd .. -> changes directory path to one folder above

cat <file_name> -> show contents of file

Ctrl + L -> to clear terminal screen

ls -u <user_name> -> lists all processes started by a particular user
kill -9 <pid> -> stops the process with a particular pid

./<app> -> to execute an application

locate <folder_name> -> To find a folder in entire PC

MySQL concatenation

If you want to generate multiple statements for all the tables in MySQL database to insert data imported by Oracle, you can do so by using MySQL concat() with the required information from information_schema database:-

use information_schema;
SHOW TABLES;
desc columns
DESC TABLES;

Say while importing data from .csv files containing data from oracle has some blank values which has to be entered as null value in MySQL database tables, then you have got to check which columns are nullable & accordingly declare set command for those columns for each table in following manner.

mysql> LOAD DATA LOCAL INFILE '/home/../mytbldata.csv' INTO TABLE <db_name>.<tbl_name>
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n' IGNORE 1 LINES
(<col1_name>,<@var1>, <col3_name>,<@var2>)
SET col2_name = IF(@var1=0, NULL, @var1),
col3_name = IF(@var2=0, NULL, @var2)

1. You can check the nullable columns for a particular table by using foll command:-

select column_name, is_nullable from information_Schema.columns
where is_nullable = 'yes' and table_Schema='<db_name>' and table_name='<table_name>';


2.  Generating LOAD Statements without SET for all tables of a database:-
select concat
('load data local infile \'/home/meena/Desktop/meena/', UPPER(table_name), '_DATA.csv\'
into table <db_name>.', table_name,
' fields terminated by \',\' enclosed by \'"\'
lines terminated by \'\\n\' ignore 1 lines;'  )
AS Load_statement
from information_schema.TABLES
where table_schema='<db_name>' ;

3. Now, the Load statements for all tables in database with set for nullable columns in each table.

select concat
("load data local infile
'/home/meena/Desktop/meena/", upper(table_name),
"_DATA.csv' into table ",table_name, "fields terminated by ','
enclosed by '""' lines terminated by '\\n' ignore 1 lines (")
as my_stmt
from information_schema.tables
where table_name = '<table_name>' and table_schema = '<db_name>'
union select concat(if(is_nullable='YES', '@', ''),column_name)
from columns
where table_name = '<table_name>' and table_schema = '<db_name>' and ordinal_position = 1
union
select concat(',',if(is_nullable='YES', '@', ''),column_name)
from columns
where table_name = '<table_name>' and table_schema = '<db_name>' and ordinal_position > 1
union
select ') set '
from dual
union
select concat(column_name, '= if(@', column_name, '=0, null, @', column_name, ')')
from columns
where table_name = '<table_name>' and table_schema = '<db_name>' and is_nullable = 'YES';

#----------------------------------------------------------------------------------------------------------------
We can use group_concat() function also to generate load data statement with set condition for all tables in a database.


select 
concat("load data local infile '/home/meena/Desktop/meena/dummy_file_format/", UPPER(c.table_name)
, "_DATA.csv.txt' into table ", c.table_name, " fields terminated by ','
enclosed by '\"' lines terminated by '\\n' ignore 1 lines ("
      ) as s1, 

group_concat( if(is_nullable="YES", "@", ""),column_name) as s2, 

concat(") set ") as s3,

group_concat(if(is_nullable="YES", 
           concat(column_name,"= if(@",column_name, "=0, null, @", column_name, ") \n") ,
            ''
         )
       ) as s4

from information_Schema.columns c 
where c.table_schema='dummy_db' group by c.table_name;

Master to Master Replication in MySQL server

 *********** MASTER SERVER 1 SETTINGS ************

1. Login to master server 1 machine.
 Open terminal. Login as super user.

2.  Edit mysql configuration file (my.cnf) using vi editor. It is in /etc directory. Set server-id = 1. Basically it should be a unique number in both master servers.

3.  root> service mysqld start;

4.  root> mysql -u<username> -p<password>

5.  mysql> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@ 'ip_addr' IDENTIFIED BY ‘slavepass’;
eg. grant replication slave on *.* to 'm1'@  '%' identified by 'p1';

6.  mysql> show master status\G;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
Note the bin log file name & position. This will be used in master 2 settings.

7.   root> CHANGE MASTER TO MASTER_HOST='IP_ADDR', MASTER_USER='<repl_user>', MASTER_PASSWORD='<password>', MASTER_LOG_FILE='<log_file_name>', MASTER_LOG_POS=<position>;
eg: change master to master_host='129.1.1.19', master_user = 'm2', master_password='p2', master_log_file='mysql-bin.000004', master_log_pos=107;

8.  mysql> SHOW SLAVE STATUS\G;
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 129.1.1.19
                  Master_User: m2
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 200
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 200
              Relay_Log_Space: 410
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 2
1 row in set (0.00 sec)

9. In above step, the value of foll should be as it is shown here for replication to work :-
 Slave_IO_State: Waiting for master to send event
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes

10. The value of foll should be of another master server:-
            Master_Host: 129.1.1.19   (IP of master 2)
            Master_User: m2     (repl user name of master 2)
            Master_Port: 3306    (port no of master 2)
            Master_Log_File: mysql-bin.000006 (log file of master 2)
            Read_Master_Log_Pos: 200   (log file position of master 2)

 *********** MASTER SERVER 2 SETTINGS ************

1. Login to master server 2 machine.
 Open terminal. Login as super user.

2.  Edit mysql configuration file (my.cnf) using vi editor. It is in /etc directory. Set server-id = 2. Basically it should be a unique number in both master servers.

3.  root> service mysqld start;

4.  root> mysql -u<username> -p<password>

5.  mysql> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@ 'ip_addr' IDENTIFIED BY ‘slavepass’;
eg. grant replication slave on *.* to 'm2'@ '%' identified by 'p2';

6.  mysql> show master status\G;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 |      200 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
Note the bin log file name & position. This will be used in master 1 settings.

7.   root> CHANGE MASTER TO MASTER_HOST='IP_ADDR', MASTER_USER='<repl_user>', MASTER_PASSWORD='<password>', MASTER_LOG_FILE='<log_file_name>', MASTER_LOG_POS=<position>;
eg: change master to master_host='129.1.1.42', master_user = 'm1', master_password='p1', master_log_file='mysql-bin.000001', master_log_pos=107;

8.  mysql> SHOW SLAVE STATUS\G;
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 129.1.1.42
                  Master_User: m1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 107
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 200
              Relay_Log_Space: 410
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)

10. In above step, the value of foll should be as it is shown here for replication to work :-
Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

10. The value of foll should be of another master server:-
 Master_Host: 129.1.1.42   (IP of master 1)
 Master_User: m1     (repl user name of master 1)
 Master_Port: 3306    (port no of master 1)
 Master_Log_File: mysql-bin.000001 (log file of master 1)
 Read_Master_Log_Pos: 107   (log file position of master 1)

Creating .jar file

Create the necessary .java program files in a directory.

Employee.java

class Employee
{
 public int EmpID;
 public String EmpName;
 public Employee(int id, String name)
 {
  EmpID = id;
  EmpName = name;
 }
 public String toString()
 {
  return " Employee ID: "+EmpID + "\n Employee Name: "+EmpName;
 }
}

MyClass.java

import java.util.Scanner;
public class MyClass
{
 public static void main(String[] args)
 {
  Scanner sc=new Scanner(System.in);
  System.out.println("\n Enter Emp ID: ");
  int id = sc.nextInt();
  System.out.println("\n Enter Emp Name: ");
  String name = sc.next();
  Employee e = new Employee(id, name);
  System.out.println(e);
 }
}

Compile them using command prompt:-

javac *.java

The .class files are created.

Create manifest file to store the name of class containing main(). The line should end with new line. It can also contain version information.

manifest.txt

Main-Class: MyClass

To create jar file:-

jar cvfm MyJarFile.jar manifest.txt *.class

Jar file is created in same folder.

D:\CORE_JAVA\myjardemo>jar cvfm MyJarFile.jar manifest.txt *.class
added manifest
adding: Employee.class(in = 618) (out= 369)(deflated 40%)
adding: MyClass.class(in = 768) (out= 470)(deflated 38%)


D:\CORE_JAVA\myjardemo>dir
 Volume in drive D is Data
 Volume Serial Number is 1E89-AEA1
 Directory of D:\CORE_JAVA\myjardemo
08/12/2012  02:45 PM    <DIR>          .
08/12/2012  02:45 PM    <DIR>          ..
08/12/2012  02:45 PM               618 Employee.class
08/12/2012  02:24 PM               251 Employee.java
08/12/2012  02:22 PM                21 manifest.txt
08/12/2012  02:45 PM               768 MyClass.class
08/12/2012  02:27 PM               358 MyClass.java
08/12/2012  02:45 PM             1,439 MyJarFile.jar
               6 File(s)          3,455 bytes
               2 Dir(s)  130,187,784,192 bytes free

D:\CORE_JAVA\myjardemo>java -jar MyClass.jar

 Enter Emp ID:
420
 Enter Emp Name:
Gabbar
 Employee ID: 420
 Employee Name: Gabbar
 

IMPORTING DATA FROM MULTIPLE .DSV FILES TO MULTIPLE TABLES IN MySQL db

COUNT OF FILES IN A DIRECTORY:-

root> # ls -l |grep '^-' | wc -l

--------------------------------------------------------------------
TRANSFERING ALL FILENAMES IN A DIRECTORY TO A FILE:-

root> # ls -1 > /home/meena/Desktop/meena/DSV_FILENAMES.txt
--------------------------------------------------------------------

AWK COMMAND TO CREATE STATEMENTS LIKE BELOW FOR EACH TABLE:-

LOAD DATA LOCAL INFILE /home/meena/Desktop/meena/TABLE1.dsv
INTO TABLE TABLE1
FIELDS ENCLOSED BY '"' SEPERATED BY '\t'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

root> #
awk -F: -v  sq="'" -F: -v dq="\"" -F: -v t="\\t" -F: -v n="\\n"
'BEGIN{FS=" ";RS="\n";ORS=";\n\n"}
{print "LOAD DATA LOCAL INFILE " sq "/home/meena/Desktop/meena/" $1 sq " INTO TABLE " $1 " FIELDS ENCLOSED BY " sq dq sq " TERMINATED BY " sq t sq " LINES TERMINATED BY " sq n sq " IGNORE 1 LINES"}'
 /home/meena/Desktop/meena/DSV_FILENAMES.txt
  > /home/meena/Desktop/meena/DSV_IMPORT_STMT.sql

REPLACE ALL
_DATA.dsv FIELDS
WITH
 FIELDS

root> # mysql -uroot -proot  set_foreign_key_checks=0;
root> # mysql -uroot -proot  db_name > /home/meena/Desktop/meena/DSV_IMPORT_STMT.sql
root> # mysql -uroot -proot  set_foreign_key_checks=1;

AWK command in MySQL

To extract particular words in given pattern you can use AWK command of MySQL in following ways:-

Suppose you have a file named as MyOracleConstraints.sql

ALTER TABLE MY_DB.MY_TAB1 MODIFY  MY_COL1 NOT NULL;
ALTER TABLE MY_DB.MY_TAB2 MODIFY  MY_COL2 NOT NULL;

Now to run the above script in MySQL we need to have the datatype also of the columns in the script.
If you have 1000's of such lines, you need a short-cut way of doing it.

If the tables above are already created with their columns in MySQL then you can retrieve the datatypes of all the columns of all tables in your database using foll query:-

mysql> SHOW DATABASES;
mysql> USE information_schema;
mysql> SHOW TABLES;
mysql> DESC COLUMNS;
mysql> SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE FROM information_schema.columns WHERE TABLE_SCHEMA = 'MY_DB'; | awk 'BEGIN{FS=" "}{PRINT $2 "\t" $4 "\t" $5}' > /home/meena/Desktop/meena/MyTabColDT.txt

This gives the data in tab seperated file format:-

MY_DB.MY_TAB1     MY_COL1     varchar(20)
MY_DB.MY_TAB2     MY_COL2     double(2,3)
MY_DB.MY_TAB3     MY_COL3     date

Using awk command you can store the table name, column name & column type in .csv or .txt format in a file.

mysql> awk 'BEGIN{FS=" "} /MODIFY/ {PRINT $3 "\t" $5}'  /home/meena/Desktop/MyOracleConstraints.sql > /home/meena/Desktop/meena/MyTabCol.txt

This will store the data in output file MyTabCol.txt in tab seperated file format:-

MY_DB.MY_TAB1     MY_COL1
MY_DB.MY_TAB2     MY_COL2

Now create a table in MySQL with two columns tbl_name & col_name and then insert MyTabCol.txt data in the table.
Create another table with three columns viz., tbl_name, col_name, data_type & insert MyTabColDT.txt data in it.

Next step is to apply inner join operator in MySQL to retrieve the required matching datatypes alongwith the column & table names in a file.

Master to Slave Replication in MySQL server

This kind of replication is meant for replicating data from the master server to slave server.
Open my.cnf file & type server-id=1. It should be unique number for master server.

On master server open Mysql command prompt & type:

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT TO *.* TO 'slave_user'@ '%' IDENTIFIED BY 'slave_password';

mysql> SHOW MASTER STATUS;

It will show a bin log file name & its position. Note it.

On Slave server machine's mysql terminal:-
mysql> CHANGE MASTER TO MASTER_HOST='IP_OF_MASTER', MASTER_USER='slave_user', MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;

mysql> START SLAVE;

mysql> SHOW SLAVE STATUS\G;

Repeat the steps for each master server.