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 

JMS Module with ConnectionFactory and Queue configuration using WLST

After almost three and half years again revisited to the JMS module script. This time the project needs are quite different. Where the Oracle WebLogic domains classified environments  but they are standalone server domains. That is only AdminServer will be there in the domain and that would be target for the JMS module, JMS Destinations.

Lets begin the experimenting now, the prerequisites for this are:

1. A WebLogic Domain configured with single AdminServer
2. AdminServer should be up and RUNNING
3. To execute the WLST script required PATH, alias should be defined in the profile as shown below:

export MW_HOME=/u01/app/oracle/fmw
export WL_HOME=$MW_HOME/wls/wlserver
export USER_MEM_ARGS="-Djava.security.egd=file:/dev/./urandom"
alias wlst="$MW_HOME/oracle_common/common/bin/wlst.sh -skipWLSModuleScanning"


You can use this freshly created alias wlst at any directory to invoke WLST shell.  The option -skipWLSModuleScanning is easy, faster learnt while working on docker containers and simple way to use.


4. Execute the configure JMS Servers
5. The WLST Script for JMS configurations and all frequently changing values are moved into the properties file.

#========================================
# WLST Script purpose: Configuring JMS Module
# Author: Pavan Devarakonda
# Update date: 3rd Aug 2017
#========================================
from java.util import Properties
from java.io import FileInputStream
from java.io import File
from java.io import FileOutputStream
from java import io
from java.lang import Exception
from java.lang import Throwable
import os.path
import sys

envproperty=""
if (len(sys.argv) > 1):
        envproperty=sys.argv[1]
else:
        print "Environment Property file not specified"
        sys.exit(2)

propInputStream=FileInputStream(envproperty)
configProps=Properties()
configProps.load(propInputStream)

##########################################
# Create JMS Moudle will take the 
# arguments as name, subdeployment name
# target can be on admin or managed server or cluster
##########################################
def createJMSModule(jms_module_name, adm_name, subdeployment_name):
        cd('/JMSServers')
        jmssrvlist=ls(returnMap='true')
        print jmssrvlist
        cd('/')
        module = create(jms_module_name, "JMSSystemResource")
        #cluster = getMBean("Clusters/"+cluster_target_name)
        #module.addTarget(cluster)
        #adm_name=get('AdminServerName')
        adm=getMBean("Servers/"+adm_name)
        module.addTarget(adm)
        cd('/SystemResources/'+jms_module_name)

        module.createSubDeployment(subdeployment_name)
        cd('/SystemResources/'+jms_module_name+'/SubDeployments/'+subdeployment_name)
        list=[]
        for j in jmssrvlist:
                s='com.bea:Name='+j+',Type=JMSServer'
                list.append(ObjectName(str(s)))
        set('Targets',jarray.array(list, ObjectName))


def getJMSModulePath(jms_module_name):
        jms_module_path = "/JMSSystemResources/"+jms_module_name+"/JMSResource/"+jms_module_name
        return jms_module_path

def createJMSTEMP(jms_module_name,jms_temp_name):
        jms_module_path= getJMSModulePath(jms_module_name)
        cd(jms_module_path)
        cmo.createTemplate(jms_temp_name)
        cd(jms_module_path+'/Templates/'+jms_temp_name)
        cmo.setMaximumMessageSize(20)

##########################################
# JMS Queu configuration function 
# arguments are : JMS module name, Queue jndiname
# Queue name, jndi name hu
##########################################
def createJMSQ(jms_module_name,jndi,jms_queue_name):
        jms_module_path = getJMSModulePath(jms_module_name)
        cd(jms_module_path)
        cmo.createQueue(jms_queue_name)
        cd(jms_module_path+'/Queues/'+jms_queue_name)
        cmo.setJNDIName(jndi)
        cmo.setSubDeploymentName(subdeployment_name)

adminUser=configProps.get("adminUser")
adminPassword=configProps.get("adminPassword")
adminURL=configProps.get("adminURL")

connect(adminUser,adminPassword,adminURL)
#adm_name=get('AdminServerName')
adm_name=ls('Servers',returnMap='true')[0]
print adm_name
edit()
startEdit()

##########################################
#   JMS CONFIGURATION## 
##########################################
total_conf=configProps.get("total_conf")
tot_djmsm=configProps.get("total_default_jms_module")
#subdeployment_name=configProps.get("subdeployment_name")

a=1
while(a <= int(tot_djmsm)):
        i=int(a)
        jms_mod_name=configProps.get("jms_mod_name"+ str(i))
        #cluster=configProps.get("jms_mod_target"+ str(i))
        subdeployment_name=configProps.get("subdeployment_name"+ str(i))
        createJMSModule(jms_mod_name,adm_name,subdeployment_name)
        total_q=configProps.get("total_queue"+str(i))
        j=1
        while(j <= int(total_q)):
                queue_name=configProps.get("queue_name"+ str(i)+str(j))
                queue_jndi=configProps.get("queue_jndi"+ str(i)+str(j))
                createJMSQ(jms_mod_name,queue_jndi,queue_name)
                j = j + 1
        i=i+1
        a = a+1
save()
activate(block="true")
disconnect() 

Now see this is a sample of properties file that could help you to build the JMS Module, be read by the WLST script at the run time:

###################################################
# JMS SUBDEPLOY CONFIGURATION
###################################################
total_subdply=2
total_default_jms_module=2
total_conf=0
subdeployment_name1=DemoJMSFAServer1
subdeployment_name2=DemoJMSFAServer2

###################################################
# JMS MODULE CONFIGURATION
###################################################
jms_mod_name1=Demo-SystemModule1
jms_mod_name2=Demo-SystemModule2

###################################################
# JMS CONNECTION FACTORY CONFIGURATION
###################################################
conf_jndi1=demoCF
conf_name1=jms/demoCF

###################################################
#   JMS QUEUE CONFIGURATION
###################################################

total_queue1=2
queue_name11=Q1
queue_jndi11=Q1

queue_name12= BQ1
queue_jndi12= BQ1


total_queue2=2
queue_name21=Q2
queue_jndi21=Q2

queue_name22= BQ2
queue_jndi22= BQ2

#========== ADMIN DETAILS =========================
adminUser=weblogic
adminPassword=welcome1
adminURL=t3://192.168.33.100:8100

output

$ wlst jms_module.py jms_module.properties

let's see what happen when you apply this logic on your project? Did you notice any errors? Please write back 🔙 with your error screen shot. 

How do you know everything went well? Open the WebLogic Administration console to check the JMS Module Configuration has successfully created a new JMS resource or not.

You may be intrested to learn more WLST scripting for JMS you can also visit the Uniform Distributed Queue configuration post. Thanks for being with us in this post, Please write to us your errors and exceptions when you run this script.