Search This Blog

Loading...

Monday, August 31, 2015

Database connection from WLST offline/Jython

This blog post objective

There are many monitoring scripts developed in WLST as per the business needs it varies.

  • You might need the output instantly with nice UI developed in AngularJS,JQuery. 
  • You could use the same logic for connecting the Fusion application schema and get the required information.
  • You can use this DB update logic in many other WLST scripts such as deployment tracking 

My Experiment setup

  1. Vagrant Ubuntu box with Weblogic 11g installed 
  2. Vagrant Ubuntu box with Oracle XE database

Here is some sample snippets which could help you to workout differently!! Hope you like this thought.

Connect to the Oracle DB from WLST


Jython provides zxJDBC module to connect with the various Databases. one among is Oracle! Here I am tried out in the offline WLST.

wls:/offline> from com.ziclix.python.sql import zxJDBC

jdbc_url = "jdbc:oracle:thin:@192.168.33.115:1521:XE"
username = "test"
password = "welcome1"
driver = "oracle.jdbc.xa.client.OracleXADataSource"

conn = zxJDBC.connect(jdbc_url, username, password, driver)
cursor = conn.cursor(1)

Fetching records from DB from WLST

Here is the sample of SQL query which fetch the records from the database into cursor object. We can use two methods to retrieve them: fetchall(), fetchone().
wls:/offline> cursor.execute("select * from app_table")
wls:/offline> print cursor.rowcount
0
wls:/offline> cursor.fetchall()
[(100.0, 'Calendar'), (101.0, 'benfits')]

wls:/offline> cursor.execute("select * from app_table where appname='benfits'")
wls:/offline> cursor.fetchall()
[(101.0, 'benfits')]
wls:/offline> cursor.execute("select count(*) from app_table where appname='benfits'")
wls:/offline> cursor.fetchone()
(1.0,)

Inserting records from WLST

wls:/offline> stmt="INSERT INTO app_table values (?, ?)"
wls:/offline> rs=cursor.executemany(stmt,[103,'secureapp.war'])
wls:/offline> conn.commit()
wls:/offline> cursor.execute("select * from app_table")
wls:/offline> cursor.rowcount
0
wls:/offline> cursor.fetchall()
[(100.0, 'Calendar'), (101.0, 'benfits'), (103.0, 'secureapp.war')]

Update records in Database from WLST

wls:/offline> stmt="UPDATE app_table set appname = 'benefits.war' where appid=101"
wls:/offline> rs=cursor.execute(stmt)
wls:/offline> conn.commit()
wls:/offline> cursor.execute("select * from app_table")
wls:/offline> cursor.fetchall()
[(100.0, 'Calendar'), (101.0, 'benefits.war'), (103.0, 'secureapp.war')]
wls:/offline>

Troubleshooting: DatabaseError: IO Error: Connection reset [SQLCode: 17002], [SQLState: 08006]


To fix this issue Change the setup for your application, so you add the next parameter to the java command: -Djava.security.egd=file:/dev/../dev/urandom
We made this change in our java.security file and it has gotten rid of the error.
$ export JAVA_OPTION='-Djava.security.egd=file:/dev/../dev/urandom'
vagrant@mydev:/vagrant/pybin/reautomation$ wlst $JAVA_OPTION db_utility.py


References:
1. Jython 21 documentation http://www.jython.org/archive/21/docs/zxjdbc.html
 2. Jython Book 1.0 Documentation on database http://www.jython.org/jythonbook/en/1.0/DatabasesAndJython.html
 3. Inspiring DB connection from WLST http://www.javamonamour.org/2012/04/wlst-and-oracle-db-zxjdbc-jython.html

Popular Posts

Followers