Creating Candlestick Chart in iReport / Jaspersoft / Jasper Report

This blog will talk about how to create candlestick chart in Jaspersoft.


  • Jaspersoft(any version)
  • iReport tool(design)
  • d/b softwares (eg.MySql)
  • JAVA
  • Eclipse(if require)



The candlestick techniques we use today originated in the style of technical charting used by the Japanese for over 100 years before the West developed the bar and point-and-figure analysis systems. In the 1700s, a Japanese man named Homma, a trader in the futures market, discovered that, although there was a link between price and the supply and demand of rice, the markets were strongly influenced by the emotions of traders.



In order to create a candlestick chart, you must have a data set that contains open, high, low and close values for each time period you want to display. The hollow or filled portion of the candlestick is called “the body” (also referred to as “the real body”). The long thin lines above and below the body represent the high/low range and are called “shadows” (also referred to as “wicks” and “tails”). The high is marked by the top of the upper shadow and the low by the bottom of the lower shadow.

Candlestick chart Jaspersoft iReport



1.      Create report in i-report designer,  select Blank A4 size report from ireport designer.
File > New > Blank A4
(here eg.DemoOfCandlestickchart->NEXT->FINISH)

2.    Delete all band except summary band

3.    Goto-> Palette window tool->Select chart->Select MultiAxis chart->Select TimeSeries chart->Ok

4.    Right Click on Multiaxis chart->Select Add Exist Chart->Select Candlestick chart->Ok
(add two candlestick chart into multiaxis chart)

While writing query  keep in mind following things:
•    For input values to Candlestick chart  we require 5  values for each chart
High value

Low value
Open value
Close value
Volume value





avg(0) as  avg,

MAX(0) as max,

MIN(0) as min,

STDDEV(0) as std_dev,

“dummy” as _lable,

 1-1-1111   as _date,

as  abc  from dual


· Add  new Dataset ->Write query->add it

· Goto -> Report Inspector->Summary band-> Select candlestick chart (first)

->Right click on it ->Select chart data->goto chart data

Note:-  Generally creating single candlestick chart  first candlestick High-close value  are same and second chart Low-close value are same

Candlestick chart Jaspersoft iReport 2


5. Similarly as mentioned above instruction  set data for another chart .

Candlestick chart Jaspersoft iReport 3


Things we need to find out before installing Jasperserver in AWS EC2 / Linux.

1)      Identifying whether your CPU is a 64bit or not. Use below command to find it

uname –m

Our CPU is a 64 bit.


2)      Identify whether Jasperserver default port 8080 is open or not? Below command helps you to identify it.

netstat -an | grep 8080| grep LISTEN


If nothing has not returned, then your port 8080 is free to use.

Installing Jasperserver

3)      Created a folder Helical and Jasperserver under home.



4)      Downloaded Jasperserver 30-days trial from here

File name:


Command used to download:



If you face an Error saying “ -bash: wget: command not found.” Then it means wget package is not installed. Installed wget package using


yum install wget


Repeat wget command again.


5)      Downloaded “” under the location “/home/Helical/Jasperserver”.


Apply chmod 777 for the downloaded file

It was confirmed that port 8080 is not in use with any other process. Thus i am going forward with Jasperserver installation as


Results of each step:

A)     Welcome to the JasperReports Server 5.5 Setup Wizard and its License Agreement. Do as it says and finals asks you to accept the license or not.

Press [Enter] to continue :

Reading the agreement

Accepting the License (y/n): Y

B)      It asks you to select the option of installation (Install All or Custom). If it is for the first time Installation select first option.

Please choose an install option below:

[1] Install All Components and Samples  (requires disk space of: 1.3 GB)

[2] Custom Install

Please choose an option [1] : 1

Jasper installation AWS 3

C)     It asks you to select a folder to install. By default it takes “/opt/jasperreports-server-5.5”

Jasper installation AWS 2

Press Enter simple Instead saying Yes(Y).

D)     It asks you to confirm the Installation by Continuing. Say (Y)

Do you want to continue? [Y/n]: Y

Jasper installation AWS

Start Your Jasperserver from the place you have installed it. Here the Jasperserver installed path is “/home/Helical/Jasperserver/y”.

Starting Jasperserver:

You can start the jasperserver in any one of the way sh start (or)  ./ start

Check the Status of the Service:

sh status

Once again the check the port number 8080 is engaged or not.

By default jasperserver uses Postgres Database thus from this installation even postgres DB will also be available on Port Number 5432 in your machine. No need to install it explicitly.

Location of Postgres: “/home/Helical/Jasperserver/y/postgresql/bin”

Now you must be able to access the Jasperserver from “ipaddress:8080/jasperserver-pro”. If not able to access it Check whether 8080 is configured to access TCP/IP incoming and outgoing in iptables.

Edit iptables as root user using vi /etc/sysconfig/iptables


Add a rule saying


-A INPUT -p tcp -m state –state NEW -m tcp –dport 8080 -j ACCEPT


Now you must be able to access the Jasperserver.

How to Enable HTTPS in Apache



What Is HTTP:


The Hypertext Transfer Protocol (HTTP) is an application protocol for distributed, collaborative, hypermedia information systems. HTTP is the foundation of data communication for the World Wide Web. Hypertext is structured text that uses logical links (hyperlinks) between nodes containing text. HTTP is the protocol to exchange or transfer hypertext. HTTP functions as a request-response protocol in the client-server computing model. A web browser, for example, may be the client and an application running on a computer hosting a web site may be the server. The client submits an HTTP request message to the server. The server, which provides resources such as HTML files and other content, or performs other functions on behalf of the client, returns a response message to the client. The response contains completion status information about the request and may also contain requested content in its message body.


What Is HTTPS:


Hypertext Transfer Protocol Secure (HTTPS) is a communications protocol for secure communication over a computer network, with especially wide deployment on the Internet. Technically, it is not a protocol in and of itself; rather, it is the result of simply layering the Hypertext Transfer Protocol (HTTP) on top of the SSL/TLS protocol, thus adding the security capabilities of SSL/TLS to standard HTTP communications. HTTPS signals the browser to use an added encryption layer of SSL/TLS to protect the traffic.


What Is SSL:


Secure Socket Layer (SSL) is a protocol that provides security for communications between client and server by implementing encrypted data and certificate-based authentication. Technically, the term “SSL” now refers to the Transport Layer Security (TLS) protocol, which is based on the original SSL specification. SSL is one of the most common ways of integrating secure communication on the internet, as it is a mature protocol that is well-supported by every major browser and a number of well-respected organizations provide third party SSL authentication services.


SSL use Encryption. Before transmitting any data, the sender encrypts its message, and the receiver must in turn decrypt the message before processing it. The encryption and decryption is accomplished through a method called “public key encryption”. In order for public key encryption to provide secure communication, one more more of the communicating parties must have some way of proving to the other that they are, in fact, who they claim to be. SSL provides this proof by requiring that one or more of the parties present a digital certificate into the initial negotiation of the connection, prior to the transmission of any encrypted data. This process is called “handshaking.”


To ensure that the certificate is a valid proof of identity, SSL contacts a trusted third party server specified in the certificate, called a Certificate Authority (CA). A Certificate Authority is a trusted company that agrees to vouch for the identity of a site, usually for a fee. Generally, the more widely the CA is known as a reputable organization, the more they will charge you per year to verify your site’s identity.




When To Use SSL With Tomcat


The most common reason you’d need to use Tomcat to handle SSL connections would be if you are running Tomcat as a stand-alone web server.


In other words, if you’re fronting Tomcat with a web server and using it only as an application server or Tomcat servlet container, in most cases you should let the web server function as a proxy for all SSL requests.


Why? Because all that decryption, encryption, and handshaking isn’t free – actually, it’s not just “not free,” it’s quite CPU-intensive, and it significantly slows down the speed of transmission. In other words, if you’re already using a web server to serve your static content, you’re better off letting it handle all that, freeing up your Tomcat server to focus on its specialty – quickly generating dynamic content, and allowing it to get that data to your web server as quickly as possible, in cleartext.


Configuring Tomcat To Use SSL


Setting up SSL for Tomcat can be divided into two main tasks:


  1. creating a functional keystore, and
  2. configuring the Tomcat connectors and applications.




PART I – The Keystore


Step 1 – Creating the Keystore


The keys Tomcat will use for SSL transactions are stored in a password-protected file called, creatively, the “keystore.” The first step to enabling SSL on your server is to create and edit this file. You can create this file in one of two ways – by importing an existing key into the keystore, or by creating an entirely new key.


A program called keytool, which is included with your JDK, will do the actual work of creating your new keystore. To create a new keystore using this program, enter the following command at the command-line:


$JAVA_HOME/bin/keytool -genkey -alias [youralias] -keyalg RSA -keystore [/preferred/keystore/path]





Use an [alias] and [path] of your choice.




C:\Program Files\Java\jdk1.6.0_21\bin> keytool.exe –genkey  –alias localhost  –keyalg RSA  -keystore  c:\localhostgeneratedkey


Next, keytool will ask you to enter the password you want to use for the keystore. Again, choose whatever you like (but don’t forget it).


After you choose the keystore password, you will enter the information required for the Certificate, such as your company and your name. Make sure this information is accurate, as you will have to submit this file to the Certificate Authority of your choice to obtain a certificate.


The last thing keytool will ask you to specify is the key password, which is the password specific to this specific certificate. Rather than enter anything at this prompt, just press ENTER.


This will cause keytool to set the key password to a value equivalent to the keystore password. Matching passwords are REQUIRED for Tomcat to access the certificate. If you choose two different passwords, any attempts to access the keystore will result in a crash (so don’t do it).


Congratulations – if you followed the directions correctly, you should now have a usable keystore file named [youralias], located in the directory you chose.


If you entered as given example, you should now have a usable keystore file named localhostgeneratedkey located in C:\.


Step 2 – Creating the Certificate Signing Request


Now that you’ve created your keystore, it’s time to create a file called the Certificate Signing Request, or CSR, which will be used by the Certificate Authority of your choice to generate the Certificate SSL will present to other parties during the handshake.


You can use the keytool to create this file, as well. To do so, enter the following at the command line:




$JAVA_HOME/bin/keytool -certreq -keyalg RSA -alias [youralias] -file [yourcertificatname].csr -keystore [path/to/your/keystore]






C:\Program Files (x86)\Java\jdk1.6.0_21\bin>keytool.exe -certreq -keyalg RSA -alias localhost -file c:\ localhostcertreq.csr  -keystore c:\ localhostgeneratedkey


If you follow the instructions correctly, keytool will create a file called yourcertificatename.csr, which you can submit to the CA you’ve chosen via the process they provide on their website. Using this file, they will generate a custom certificate for your server, which you can download according to the instructions they provide on their website.


Step 3 – Installing Your New Certificate


SSL verifies the authenticity of a site’s certificate by using something called a “chain of trust,” which basically means that during the handshake, SSL initiates an additional handshake with the Certificate Authority specified in your site’s certificate, to verify that you haven’t simply made up your own CA.


In order to “anchor” your certificate’s chain of trust, you have to download an additional certificate, called a “Root Certificate,” from your CA, and then import both this certificate and your site’s new certificate into your keystore. Your CA should provide information about obtaining a Root Certificate on their website.


Once you’ve downloaded both your own Certificate and the Root certificate provided by your CA, import them into your keystore with the following commands, replacing the [placeholders]:




keytool -import -alias root -keystore [path/to/your/keystore] -trustcacerts -file [path/to/the/root_certificate]


To import the Root Certificate






C:\Program Files\Java\jdk1.6.0_21\bin>keytool.exe -import -alias root -keystore  c:\ localhostgeneratedkey  -trustcacerts -file c:\rootcert.crt


To import your new Certificate –




keytool -import -alias [youralias] -keystore [path/to/your/keystore] -file [path/to/your_keystore]







C:\Program Files\Java\jdk1.6.0_21\bin>keytool.exe -import -alias localhost -file c:\sitecert.crt”


Do everything right? Then pat yourself on the back – you are now the proud owner of a functional, certified keystore.


PART II – Configuring Tomcat to use SSL


Now that we have a functional keystore populated with valid certificates, it’s time to configure Tomcat to use SSL. First, we’ll configure Tomcat’s SSL connectors, and then we’ll specify which webapps we want to use SSL by default.


Step 1 – Configuring Tomcat’s SSL Connectors


Tomcat’s global Connector options are configured in Tomcat’s main configuration file, “$CATALINA_BASE/conf/server.xml”, so you should open this file now. The Connectors we are looking for connect on port 8443 by default, so search for this port, until you come across an entry that looks like this:


<!– Define a SSL HTTP/1.1 Connector on port 8443


This connector uses the JSSE configuration, when using APR, the


connector should be using the OpenSSL style configuration


described in the APR documentation –>




<Connector port=”8443″ protocol=”HTTP/1.1″ SSLEnabled=”true”


maxThreads=”150″ scheme=”https” secure=”true”


clientAuth=”false” sslProtocol=”TLS”/>




You’ll notice that the comment enclosing this connector talks about a choice between APR and JSSE configurations. This refers to the implementation of SSL you are intending to use. JSSE, which is Tomcat’s default configuration, is supported by default, and included in all JDKs after version 1.4. So if you don’t even know what APR is, you only need to uncomment this entry, and add some additional information to allow Tomcat to find your keystore:




<Connector port=”8443″ maxThreads=”150″ scheme=”https” secure=”true” SSLEnabled=”true” keystoreFile=”path/to/your/keystore” keystorePass=”YourKeystorePassword” clientAuth=”false” keyAlias=”yourAlias” sslProtocol=”TLS”/>









<Connector port=”8443″ protocol=”HTTP/1.1″ SSLEnabled=”true”  maxThreads=”150″ scheme=”https” secure=”true”  clientAuth=”false”  sslProtocol=”TLS” keystoreFile=c:\ localhostgeneratedkey keystorePass=”localhost”/>




<Connector port=”8443″ scheme=”https” secure=”true” SSLEnabled=”true” SSLCertificateFile=”/path/to/your/certificate.crt” SSLCertificateKeyFile=”/path/to/your/keyfile” SSLPassword=”YourKeystorePassword” SSLCertificateChainFile=”path/to/your/root/certificate” keyAlias=”yourAlias” SSLProtocol=”TLSv1″/>


If, on the other hand, you know that using the Apache Portable Runtime (APR), also known as Tomcat’s “native library,” is by far the best practice to follow, especially when using Tomcat as a standalone web server (which you probably are), and have already installed it on your server, then you’ll need to alter this entry as follows to allow Tomcat to use APR’s OpenSSL implementation in place of JSSE, or trying to use SSL will generate an error:







Notice that if you are using APR, the “SSLCertificateFile” and “SSLCertificateKey”-type attributes are used in place of the keystoreFile attribute. For more information on the differences between using APR in place of JSSE, consult Apache’s Tomcat APR Documentation.


Restart Tomcat. Once you’re up and running again, test your configuration by connecting to a secure page, using a URL such as https://[yourhost]:8443. If you followed the directions correctly, you should be able to view the page over a secure HTTPS connection!






Step 2 – Limiting SSL Usage


Enabling SSL in Tomcat’s server.xml file causes all files to be run both as secure and insecure pages, which can cause unnecessary server load. You can choose which applications offer SSL connections on a per-application basis by adding the following <security-constraint> element to the application’s WEB-INF/web.xml file:





















This configuration allows you to set SSL options for all an application’s pages in one place. For example, to disable SSL for all your application’s pages, change “CONFIDENTIAL” to “NONE”.






Follow These Steps:


  1. Open the Command Prompt and browse to [apache 2.2 path]/bin.
  2. Enter openssl req -config ../conf/openssl.cnf -new -out foo.csr -keyout foo.pem




C:\Apache24\bin>openssl req -config ../conf/openssl.cnf -new -out c:\localhost.csr -keyout c:\localhost.pem


When you run the final command, a certificate request is generated. The following is an example of a certification request:


Country Name (2 letter code) [AU]: US


State or Province Name (full name)[Some-State]: California


Locality name (eg, city) []: Redwood Shores


Organization Name (eg, company) [Internet Widgits Pty Ltd}: Oracle


Organizational Unit Name (eg, section) []: EITQA


Common Name (eg, YOUR name) []


Email Address []: [email protected]




Please enter the following “extra” attributes to be sent with your certification request:


A challenge password []:


An optional company name []:




  1. To Genearate a key:




C:\Apache24\bin>openssl rsa -in c:\localhost.pem -out c:\localhost.key




Be sure to take note of the following:


  • These commands create two files: localhost.pem  (Encrypted File) and localhost.csr (certificate request).
  • For Common Name, include the FULL name of the HOST and DOMAIN you are running the command on.
  • Remember the password you enter. This password is used every time Oracle HTTP Server is started.


  1. Send the Certification Request. In the CSR area, paste the certification request from localhost.csr  file.


  • When you receive the certificate, paste it into a file named portalcert.crt.


Be sure that you get the Root Trial CA certificate by going to the URL mentioned in the Certificate Authority email. Export that certificate from the browser to a file named rootcacert.crt.


  • Copy the following in appropriate directories:


  • Certificate file portalcert.crt into the …\Apache\Apache\conf\ssl.crt directory.
  • localhost.key  file into the …\Apache\Apache\conf\ssl.key directory.
  • Root Trial CA file rootcacert.crt into the …\Apache\Apache\conf\ssl.crt directory.




You can create a self-signed Certificate to taste HTTPS in Apache.




C:\Apache24\bin> openssl x509 -in c:\ localhost.csr  -out c:\ localhost.crt  -req -signkey c:\ localhost.key” -days 7300


Here, “localhost.crt” is a self-signed certificate, and 7300 is validity date of certificate (7300 Days = 20years).


Modify httpd.conf File to Enable SSL


  • Add Listen 443
  • Uncomment out LoadModule ssl_module modules/
  • Certificate Entries






SSLEngine on


SSLProtocol “TLSv1”


SSLCertificateKeyFile “c: /localhost.key”


SSLCertificateFile “c: localhost.crt”



For CA :



        SSLCACertificateFile conf\ssl.crt


                    SSLCACertificateFile conf\ssl.crt\rootcacert.crt




  • Restart Apache.




SSL Entries in Virtual Host




<VirtualHost *:443>


SSLEngine on


SSLProxyEngine on


SSLProtocol “TLSv1”


SSLCertificateKeyFile “/path/of/your/keyfile”


SSLCertificateFile “/path/of/your/certificate/file”


SSLCACertificateFile "/path/of/your/CAcertificate/file


SSLCACertificateFile "/path/of/your/CARootcertificate/file




# if you are using proxy, Proxy code here




Creating Jasper Reports In (iReport) Using XML As Data Source & Publishing It to Jasper Server

This blog will teach how to create Jasper Reports using iReport, the data source to be used is XML and then publish it on Jasper servcer

  1. Run iReport; it will open showing a Welcome Window, as shown in the following screenshot:

Jasperreport XML data source

2.  DataBase (DataSource ) Connection :

a.)  Click On Datasource button

Jasperreport XML data source 2

b) Click On new button

Jasperreport XML data source 3

C) Select XML datasource

Jasperreport XML data source 4

d) A new window named XML file datasource will open, as in the following

Jasperreport XML data source 5

e) Enter AddressBook (you can give the name of your choice also) as the name for your new connection for the XML datasource in the text box beside the Name text field  & Click the Browse button to browse to the addressbook.xml file located in the E:/ in my system. Select the Use the report XPath expression when filling the report option in the XML file datasource window,

Jasperreport XML data source 5

f) Now click on “TEST” for testing the connection

3. Creating The Report  :  Open a new report and then go to “query browser”,choose the query language from drop down list as “xpath.” The text area below the Query language drop-down list will split into two parts. The left part  shows the actual XPath expression, whereas the   right part shows XML nodes

Jasperreport XML data source 7

4. In the XML nodes right click on the name child of the person node,select “add node as field”,similarly for phone child of person node also.

Jasperreport XML data source 8

5. Double-click on the Fields node in the Report Inspector window on the left of your report. The Fields node will expand to show the fields name, phone.

Jasperreport XML data source Query

Drag-and-drop the name and phone field from the Fields node into the Detail section of your report.

6. Switch to the Preview tab and you will see your report The result should be similar to the below

Jasperreport XML data source 10

7. Publishing the report to the jasper server

a) Creating data adapter :

a.)    Creating Data Adapter :  To use flat files as a datasource (like  XML  files) you need to use DataAdapters in Jasperserver instead of regular datasources. We can create them manually in the server (it’s just an XML file). Data Adapter is a way to define a source for Jasper Reports using a   configuration file,(act as the bridge between data source and jasper reports while publishing the jasper report on Jasper Server).


Jasperreport XML data source 11

<filename>repo:/reports/interactive/xmlData</filename>  – > here xmlData is the name of the xml    file(addressbook.xml) which we have to add again to the Jasper Server.(I have added this file in this location “repo:/reports/interactive/”.

b) We need to add XML datasource to server also

c) Adding xmlAdapter To The Properties attribute of Report.

Property Name :

Property Value : repo:/reports/interactive/xmlAdapter


Note: By default xpath or xpath2 language will not come in jasper server,for this you need to do three things…

  1. Copy jasperreports-extensions-3.5.3.jar from /ireport/modules/ext to JasperReports Server/jasperserver-pro/WEB-INF/lib/jasperreports-extensions-3.5.3.jar
  2. Add the following line to /jasperserver-pro/WEB-INF/classes/


  1. Restart Tomcat

Best practices to be followed while developing jasper report using ireport / jasper studio

Software used:  i-report / jasper studio, jasperserver

A)First before creating report keep in mind following things:

  1. Set page (report) properties

Eg:-Page height,width,left-right margin, orientation


2.Set Same properties for palette elements  like text,static

Eg:-Font style,size,horizontal –vertical alignment,

Position type, stretch type.


B)check  jasperserver version comp ability, between where you are developing and where you would be deploying.  

Steps:-In  i-report  tool

Goto – tool menu->Options->ireport>General>compability->select version

C) While report uploading on to jasperserver repository

1. Check input control and datasource.

2. Usually make one folder on jasperserver  as resources. In that create your input control as well as datasource

3. For adding subreport onto server make subreportJRXML


D) For importing and exporting  report from jasperserver

1.JasperReports server should be stopped when using the import and export utilities. This is very important for the import utility to avoid issues with caches, configuration, and security.

2.All command line options start with two dashes (–).

3. You must specify either a directory or a zip file to export to or import from.

4. Make sure the output location specified for an export is writable to the user running the command.


Use the command : Export and Import Utility in Jasper Report Server

Before Importing,  go into buildomatic directory of jasper Location: C:\Program Files\jasperreports-server-5.0\buildomatic


For importing JS data :-

Windows: js-import –input-zip(space) <Filename>
Ex: js-import –input-zip(space)”E:Work Space\Unified\Unified Reports\<file name>”


Update Command

js-import –input-zip(space)”E:Work Space\Unified\Unified Reports\<file name>” –update


For Exporting Jasper Reports:

Exporting project folder from jasper community server


1)  Goto PuTTY

Give username and password


2) Go to the location of jasperserver cp 5.0.0(or any version)


[email protected]:cd /opt/jasperreports-server-cp-5.0.0


3) Navigate to buildomatic folder


[email protected]:cd /opt/jasperreports-server-cp-5.0.0/cd buildomatic


4) Execute file with the destination path.


Syntax :

[email protected]:cd /opt/jasperreports-server-cp-5.0.0/buildomatic#./js-export <location of the folder in jasper server> –output-zip <location of exporting folder>/<>

How to resolve Jasperserver version compatability problem when migrating reports from one version to another

This blog will talk about how to make the report compatible with another version where it is being deployed.

Software used :- I-report, jasperserver (any version)


Step1) Open Your  report(.jrxml) in i-report

Jasper Version compatibility issue

step2)Goto Tool menu->options ->ireport menu->general->compatability

Jasper Version compatibility issue2

Step 3)select version which you want of jasperserver.

Here eg: jasperreport 4.1.3

Step4)goto (.jrxml) file of report  and make some  little change like set band height whatever you want and save it and update to server-repository navigator.

Step 5)check on your jasper server

How to create Backup using C,Best Practices while creating CSV or txt file using C

How to create   Backup  using c?

Backup   is  the  process  of  taking  the  backup of  Data  and  saving it on another location  for future Reference. This process includes

  1. Creating   CSV files or txt files  of   data on
    1. Daily basis
    2. Hourly basis.
    3. By particular Date.
  2. Making zip or creating   tar for all created the files.
  3. Uploading created tar file on server or on ftp or on our drive
  4. Again we have to download that .tar file in our application.
  5. Unzip that file to get all CSV/TXT file.
  6. Read all the Data from CSV/TXT file.


How to create CSV/TXT  File?

To create CSV/TXT File by using C –language, first we have to decide

  1. What  is  the  content  of  CSV  file.(Table Values)
  2. From where  we can  get those content.(In our case Database)
  3. Where we are going to store these files.(Output file Location)
  4. What is the Name of Our CSV File?(CSV file Name)

So  in  our Case to get the data from Database we have to do,

  1. Open  the Database.

int open = openDB(&db, (char *)conf->dbPath, conf->dbConnectionTimeout);

  1. Check  or validate  that  Db is  opened  or  not?

If( open != 0){   closeDb();           }

  1. Create your  SQL Query to get the data from Tables.

sprintf(query,”select * from <table-name> where <condition>”);

  1. Do  File Handling , create  the file and variable.


char *writeData=NULL;

writeData = calloc(1024,sizeof(char));

  1. Fire the SQL Query on Specific Tables and get the value in variable.

result = executeQuery((char *)query, &db, &stmt);



  1. Do the Formatting while Data is appending in variable.

if(col >= cols -1)








  1. Then check file is present or not , if not then create it .





             printf(“File (%s) created!\n”,filePath);



  1. Lastly write the value of variable in File.

status = appendFile(filePath, &writeData);

  1. Release the memory of variable.


  1. Finalize your Statement.


  1. Close the Database.



Flow of  creation.

  1. Read the value of DB Path, Opfilepath, log path from pushagentconfig file.
  2. Read   the value   from backlogconfig  file  ,Date and Time, FTP related information.
  3. Append both into single variable.
  4. According to that variable  fire query on database to get the timestamp
  5. After that according to that Starting timestamp   and ending timestamp.
  6. Then we are reading the type ,
  7. According to  given type we assign the value of interval variable day(86400),Hour(3600),

(last-start) timestamp

  1. After getting all the detail run the  for loop from start to end timestamp. by   assigning the interval.
  2. Append the   opFilepath, date and filename.
  3. Prepare the query and call  the  function Creating CSV  by passing Filename, Cols, stmt and length
  4. Then CreatingCSV   function called, in that function we declare one variable with some memory and appended all the data’s in that variable.
  5. Write the formatted content   of variable in file.
  6. Releasing the memory. By   this way one table data come into one csv   file with header.
  7. Again   for next table new filename, query will be created and the same process is   going on.
  8. After every single File creation, we are finalizing the statement making the query-variable content to null to reuse it.
  9. Lastly closing the Database.

Exporting project folder from jasper community server

Exporting project folder from jasper community server


1) PuTTY
Give username and password

2) Go to the location of jasperserver cp 5.0.0(or any version)
[email protected]:cd /opt/jasperreports-server-cp-5.0.0

3) Navigate to buildomatic folder
[email protected]:cd /opt/jasperreports-server-cp-5.0.0/cd buildomatic

4) Execute file with the destination path.

Syntax :
[email protected]:cd /opt/jasperreports-server-cp-5.0.0/buildomatic#./js-export <location of the folder in jasper server> –output-zip <location of exporting folder>/<>

[email protected]:/opt/jasperreports-server-cp-5.0.0/buildomatic# ./ /reports/Demos_Sadakar –output-zip /home/sadakar/delete/

Using CE setup
Using Bundled version of Java
First resource path:/opt/jasperreports-server-cp-5.0.0/buildomatic/conf_source/ieCe/applicationContext-search.xml
Started to load resources
Resource name: applicationContext.xml
Resource name: applicationContext-cascade.xml
Resource name: applicationContext-data-snapshots.xml
Resource name: applicationContext-events-logging.xml
Resource name: applicationContext-export-config.xml
Resource name: applicationContext-export-import.xml
Resource name: applicationContext-logging.xml
Resource name: applicationContext-olap-connection.xml
Resource name: applicationContext-report-scheduling.xml
Resource name: applicationContext-search.xml
Resource name: applicationContext-security.xml
Resource name: applicationContext-themes.xml
Resource name: applicationContext-virtual-data-source.xml
Creating ActionModelService object.
Creating action model infrastructure.

Helical IT Solutions

Pentaho 5.0.1 CE integration with MySQL 5.0.1 CE (Windows or Linux family)

Pentaho 5.0.1 CE integration with MySQL 5.0.1 CE (Windows or Linux )


  1. Creating databases
  2. Modifying configuration files
  3. Stopping HSQL db start up

Creating databases

Command to execute the scripting files

mysql>source  D:\ biserver-ce\data\mysql5\create_jcr_mysql.sql

Similarly execute the remaining .sql files(i.e, execute create_quartz_mysql.sql and create_repository_mysql.sql)

  1. Check the databases created using show databases command on MySQL command prompt.


Modifying configuration files


Edit the file pentaho-solutions\system\

Original code






Modified code






  2. hibernate-settings.xml

Edit the file pentaho-solutions\system\hibernate\hibernate-settings.xml.

Original code


Modified code



3. mysql5.hibernate.cfg.xml

Location of the file: pentaho-solutions\system\hibernate\mysql5.hibernate.cfg.xml

Do need to change any code in this file.. Just need to check everything is perfect or not

<property name="connection.driver_class">com.mysql.jdbc.Driver</property>

<property name="connection.url">jdbc:mysql://localhost:3306/hibernate</property>

<property name="dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>

<property name="connection.username">hibuser</property>

<property name="connection.password">password</property>


Location of the file: pentaho-solutions\system\quartz\

Original Code

org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.PostgreSQLDelegate

Modified Code

org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate

5. context.xml

Location of the file: tomcat\webapps\pentaho\META-INF\context.xml

Original Code

<Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource"

factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"

maxWait="10000" username="hibuser" password="password"

driverClassName="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/hibernate"

validationQuery="select count(*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES" />


<Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"

factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"

maxWait="10000" username="pentaho_user" password="password"

driverClassName="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/quartz"

validationQuery="select count(*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES"/>

Modified Code

<Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource"

factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"

maxWait="10000" username="hibuser" password="password"

driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/hibernate"

validationQuery="select 1" />


<Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"

factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"

maxWait="10000" username="pentaho_user" password="password"

driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/quartz"

validationQuery="select 1"/>

 Imp Note:

Delete pentaho.xml file in below location



Pentaho creates  on startup pentaho.xml as a copy of context.xml

6. repository.xml

Location of the file: pentaho-solutions\system\jackrabbit\repository.xml.

Comment this code means (<! – – every thing here – -> )

Active means: Remove comment

i)                    FileSystem part

Comment this code


<param name=”path” value=”${rep.home}/repository”/>


Active this code


<param name="driver" value="com.mysql.jdbc.Driver"/>

<param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>

<param name="user" value="jcr_user"/>

<param name="password" value="password"/>

<param name="schema" value="mysql"/>

<param name="schemaObjectPrefix" value="fs_repos_"/>


ii)                  DataStore part

Comment this code


Active this code

<DataStore class="">

   <param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>

   <param name="user" value="jcr_user"/>

   <param name="password" value="password"/>

   <param name="databaseType" value="mysql"/>

   <param name="driver" value="com.mysql.jdbc.Driver"/>

   <param name="minRecordLength" value="1024"/>

   <param name="maxConnections" value="3"/>

   <param name="copyWhenReading" value="true"/>

   <param name="tablePrefix" value=""/>

   <param name="schemaObjectPrefix" value="ds_repos_"/>


iii)                Security part in the FileSystem Workspace part

Comment this code


<param name=”path” value=”${wsp.home}”/>


   Active this code


<param name="driver" value="com.mysql.jdbc.Driver"/>

<param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>

<param name="user" value="jcr_user"/>

<param name="password" value="password"/>

<param name="schema" value="mysql"/>

<param name="schemaObjectPrefix" value="fs_ws_"/>


iv)                PersistenceManager part

Comment this code


<param name=”url” value=”jdbc:h2:${wsp.home}/db”/>

<param name=”schemaObjectPrefix” value=”${}_”/>


Active this code


<param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>

<param name="user" value="jcr_user" />

<param name="password" value="password" />

<param name="schema" value="mysql"/>

<param name="schemaObjectPrefix" value="${}_pm_ws_"/>


v)                  FileSystem Versioning part

Comment this code


<param name=”path” value=”${rep.home}/version” />



Active this code


<param name="driver" value="com.mysql.jdbc.Driver"/>

<param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>

<param name="user" value="jcr_user"/>

<param name="password" value="password"/>

<param name="schema" value="mysql"/>

<param name="schemaObjectPrefix" value="fs_ver_"/>


vi)                PersistenceManager Versioning part


Comment this code:



<param name=”url” value=”jdbc:h2:${rep.home}/version/db”/>

<param name=”schemaObjectPrefix” value=”version_”/>


Active this code:


<param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>

<param name="user" value="jcr_user" />

<param name="password" value="password" />

<param name="schema" value="mysql"/>

<param name="schemaObjectPrefix" value="pm_ver_"/>


Stopping HSQL db start up

In web.xml file

Comment or delete this code (Commenting is preferable)




<param-value>[email protected]/../data/hsqldb/sampledata,[email protected]/../data/hsqldb/hibernate,[email protected]/../data/hsqldb/quartz</param-value>




Also comment this code








You have done with integrating pentaho 5.0.1 CE with Mysql 5.5

Now login to the Pentaho server .

URL:  http://localhost:8080/pentaho

Username/Password : Admin/password


  • You will not find any sample working. B’z you have not installed sample data.
  • Example available in pentaho are developed on sample data so you need to execute and give the new sample data base connections(for this you need to execute .sql file of sample data).

Helical IT Solutions

Logging using Talend

Logging using Talend

Introduction: In this article, we will discuss about different methods of logging in Talend Open Studio. Talend is one of the widely used open source data integration tool in the market. Talend mainly uses three types of logging

  1. Statistics – Job execution statistics at component and job level
  2. Error – Job level errors, warning and exceptions
  3. Meter logging – Data flow details inside job

Best approach for logging is at project level. To enable project level logging, In Talend Open Studio, go to File, Project properties and enable or disable check boxes to start default logging at project level. See the screen shot below.

talend logging 1

If you enable the logging at project level, then every new job created will inherit these settings. There are more settings and options to do if you enable project level logging. See below screenshot.

talend logging 2

You can decide it to log the information to Console/File/Database. In case if you select any of File/Database or both options, then need to set few more default parameters like

talend logging 3

For file names, you can pre-or post fixes the file name with Talend Date Time stamp function. Or else it will write into the same file for every execution and flush out earlier data. In case of databases, you can have existing created database. This scenario does not work when you don’t have any database on the target server this scenario fails. For general JDBC, you need to provide above parameters, else if you select any database such as MySQL, then provide username, password and other required parameter values.

If you enable project level logging, then there is no need to separately use all these components say, tLogCatcher to Log the errors, tFlowMeter to catch the data flow and tStatCatcher to catch the Errors, flow and statistics log. Talend throws the errors or exceptions whenever it occurs and displays its complete trace on the console. tLogCatcher if used with the help of tDie or tWarn, would catch those messages and can be redirected to the required database / file based on the requirement. In order to do this we need to take care of all above components, implement and test the job.

Advantage with this approach is that you get the brief error information in logs table which is automatically created by Talend. In addition to this information Talend also prints its error log trace on console. And the negative side of this is that the console trace is not stored in the log table.

Problem: In both above approaches, Talend does not store or redirect its console trace to database or file.