Database connection from WLST offline/Jython

This blog post objective

There are many monitoring scripts were developed by me in previous posts in this blog you might have seen in the above menu, we will have some wild requirement which uses pure Jython SQL module which will be full capable of 

• 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 
2. Jython Book 1.0 Documentation on database 
3. Inspiring DB connection from WLST