Wednesday 15 April 2015

Creating Oracle JNDI or Actian Pervasive(PSQL) database JNDI connections in Jasper Server 6.X

Software : 
Oracle 11g , Jasper Server 6.0

Aim : To run the stored procedures. Below are the possibilities that may stop you to run the reports developed using SP.
1) Read Only databases when running SP reports in Jasper Server.
2) No Query executor for PLSQL(Not discussed in this post).

UPDATED with new content  :  16th April 2015Creating Actian Pervasive(PSQL) database JNDI connection in Jasper Server. 


Oracle JNDI connection creation : 

Step 1 : jdbc driver 

Copy the ojdb6.jar file to lib folder of tomact
Location of lib : C:\Jaspersoft\jasperreports-server-6.0\apache-tomcat\lib

NOTE: jdbc driver differs from Oracle version to version. Choose the suitable one in your case.

Step 2 :Add below lines of jdbc resource code in context.xml file

File location : 
C:\Jaspersoft\jasperreports-server-6.0\apache-tomcat\webapps\jasperserver-pro\META-INF

    <Resource name="jdbc/oracledev" auth="Container" type="javax.sql.DataSource"
        maxActive="100" maxIdle="30" maxWait="10000"
        username="myusername" password="mypassword" 
        driverClassName="oracle.jdbc.OracleDriver"
        validationQuery="SELECT 1 FROM DUAL"
        testOnBorrow="true"
        url="jdbc:oracle:thin:@171.13.0.34:1523/mySID"

        factory="com.jaspersoft.jasperserver.tomcat.jndi.JSBasicDataSourceFactory"/>

 IMP NOTE : 
I've got errors while giving url string  ( after oracle Port there should be forward slash but not colon).
For example :

Correct url string : url="jdbc:oracle:thin:@171.13.0.34:1523/mySID"
Wrong url string : url="jdbc:oracle:thin:@171.13.0.34:1523:mySID"

Explained at : 
http://community.jaspersoft.com/questions/851811/error-while-creating-oracle-jndi-connection-jasper-server#comment-811806

Step 3 :  Refer the resource added in step 2 in web.xml file 

File llocation : 
C:\Jaspersoft\jasperreports-server-6.0\apache-tomcat\webapps\jasperserver-pro\WEB-INF

  <resource-ref>
    <description>Excers DEV database</description>
    <res-ref-name>jdbc/oracledev</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
  </resource-ref>

Step 4 : 

Do all the changes from step-1 to step-3 by stopping the server.
Start or Restart the jasper server.

Create a folder and add a Data source of type JNDI and give name . In this example it is jdbc/oracledev and then click on Test button.

That's all we have to do to connect jdbc as JNDI in jasper server.


Actian Pervasive(PSQL) database JNDI creation in Jasper Server 

I'd like to share how I created a JNDI connection for pervasive db in Jasper Server.

1) Add pervasive database jar files to tomcat lib folder. 
Location of lib folder : C:\Jaspersoft\jasperreports-server-5.6.1\apache-tomcat\lib
JDBC diver file names : i) jpscs.jar ii) pvjdbc2.jar and iii) pvjdbc2x.jar 
NOTE : Add all the 3 jar files to the lib folder. (these 3 jar files are the JDBC drivers for pervasive db). 

2) Add resource code in context.xml file 
C:\Jaspersoft\jasperreports-server-6.0\apache-tomcat\webapps\jasperserver-pro\META-INF\context.xml

<Resource name="jdbc/pervasiveDB" auth="Container" type="javax.sql.DataSource"
        maxActive="100" maxIdle="30" maxWait="10000"
        username="myUserName" password="myPassword" 
        driverClassName="com.pervasive.jdbc.v2.Driver"
        validationQuery="SELECT 1"
        testOnBorrow="true"
        url="jdbc:pervasive://11.0.1.11:1283/pervasiveDB"
        factory="com.jaspersoft.jasperserver.tomcat.jndi.JSBasicDataSourceFactory"/>

3) Refer the above created resource in web.xml file (Optional)

C:\Jaspersoft\jasperreports-server-6.0\apache-tomcat\webapps\jasperserver-pro\WEB-INF\web.xml
<resource-ref>
    <description>Pervasive pervasiveDB database</description>
    <res-ref-name>jdbc/pervasiveDB</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
  </resource-ref>

4) Restart the Jasper Server
5) Create a datasource of type JNDI in Jasper Server by providing the JNDI name as jdbc/pervasiveDB and test the connection. 

References Pervasive db: 


Refer these links :
http://community.jaspersoft.com/questions/525672/postgresql-jasper-reporting-server-error

http://community.jaspersoft.com/wiki/how-add-new-jndi-connection-tomcat

 http://community.jaspersoft.com/wiki/jndi-datasource-update-trouble-cannot-create-jdbc-driver-class-connect-url-null

https://community.jaspersoft.com/wiki/connecting-jasperreports-server-your-database

 http://www.microdeveloper.com/html/JNDI_Orcl_Tomcat1p.html

http://community.jaspersoft.com/wiki/data-source-troubleshooting

http://stackoverflow.com/questions/18192521/ora-12505-tnslistener-does-not-currently-know-of-sid-given-in-connect-descript


Cheers..!!!

Update : URL string properties : 
http://commons.apache.org/proper/commons-dbcp/configuration.html

at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)