Search This Blog


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:@"
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
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()

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
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')]

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:
We made this change in our file and it has gotten rid of the error.
$ export JAVA_OPTION=''
vagrant@mydev:/vagrant/pybin/reautomation$ wlst $JAVA_OPTION

1. Jython 21 documentation
 2. Jython Book 1.0 Documentation on database
 3. Inspiring DB connection from WLST

Popular Posts