Hello Friends,

There are few days that we don’t have new posts around here… We are pretty busy in the business in the past days!!!

But, here we are with a new post that we, personally, think will be really helpful to those who like “deep” programming in ODI.

I hope you all enjoy it!

When it comes to ODI sooner or later we might have to deal with Jython and some one who have used Java would find that easy ,but not for every one . Considering that we thought of writing this post in understanding how to deal with Jython in relation to ODI . We are not going to teach you Jython 🙂

Generally most of the uses of Jython in ODI is either to capture and raise error in the operator, run OS command and do the appropriate if and else condition , reading and processing records for RDBMS , File processing based on different logic and so on.

When we use jython like java we need to import different modules , Example

import os
import re

we can also import java libraries something like you have seen few of the past codes

import java.lang.sql as sql
import java.lang.io as io

Lets look on few codes which we might use more and more.

Reading , Writing , Re-writing the file

There are different format in jython to process files, but we personally like this format
srcfile=open(‘file_name’,’r’)
Here in the above syntax, we can change the parameters of file by

r – only for reading
r+ – re writing into the same file
w – writing to a new file

When we use W (writing) on an existing file the whole file gets flushed or cleared out,so be careful when pointing to any file.

There are again different format to read the lines of the Files either complete or line by line.
We can write codes using the list format to process records into the memory but if we are dealing many millions of records calling and storing into memory and processing it can throw memory error something like  java.lang.memory exception . Keeping that in mind , we  write codes to read line by line and process accordingly.

Reading Line by Line

srcfile=open('file_name','r')
first_line=srcfile.readline() - Reading First Line
second_line=srcfile.readline() - Reading Second Line

This way when we need to read just one line we can use to readline() command . The pointer of the line keeps moving one line to next line.

Lets say if we want to keep reading all the lines

srcfile=open('file_name','r')
line=srcfile.readline()
i=0
while line:
    print line
    i+=1
    line=srcfile.readline()

print 'Total Number of Lines', i
srcfile.close()

This above simple program reads all the line one by and incremented i for each line it read, so that at the last we are also print the value of ‘i ,denoting the number of lines in File.

Let me cover step by step

srcfile=open('file_name','r')
# opening the File in Read only mode
line=srcfile.readline()
# Reading the First Line
i=0
while line:
# while loop started to read the lines one by one
    print line
    # printing the line . Please note that when ever you write codes under while or for loop ,you need to write after a tab space else the codes wont get  executed.
    i+=1
    # increment the value of i by 1
    line=srcfile.readline()
    #reading the next line of the File

print 'Total Number of Lines', i
# print the value of i
srcfile.close()
#Always close the file which you have open for read , write or any other mode else the file is open by memory in jython and unnecessary data is lying in its #memory.

Reading all the Lines at one shot

srcfile=open('file_name','r')
lines=srcfile.readlines()
print lines
srcfile.close()

Here all the lines of the Files are read a list and stored in the memory error, so in case if we have huge record it can throw memory . To solve either go for Record by record processing which can be a little time consuming or increase the Java memory to handle the processing.

Here  readlines() reads all the lines of the source file and stores in the form of array and pass it to variable lines.

Writing the data into File.

There are different ways we can write the records into File .

If you are writing a string , you can use the write(string )  to write into file

output_write=open('<file_name>','w')
str1='This is a sample program'
output_write.write(str1)
output_write.close()

In case you are planning to write multiple lines say array or list, you can use the writelines()

source_file=open('<file_name>','r')
output_write=open('<file_name>','w')
read_lines=source_file.readlines()
output_write.writelines(read_lines)
source_file.close()
output_write.close()

You can also use the java BufferedWriter.

import java.io as io
writer=io.BufferedWriter(io.OutputStreamWriter(io.FileOutputStream('<file_name_to_be_writtne>')))
writer.write('string or content')
writer.flush()
writer.close()

One of the fastest way to write into File is to store data  into Variable and write them once rather than in a while or for loop that way the overall time is reduced . This idea is great when you running your codes in Server and as we have high memory to process  and store larger records

seek

If you ever want to go to the First line of the file use seek(0) command , so in the above program if i want to go to first line it will be

srcfile.seek(0)  #( seek(zero))

t and n

t moves the pointer to include a tab space
n move the pointer to next line

Concatenate strings

str1='abcd'
str2='efgh'
print str1+str2
#output abcdefgh

Using + we can concatenate two strings .

str1='abcd'
str2=25
print str1+str2
#output Error - TypeError: __add__ nor __radd__ defined for these operands

Reason being we can’t concatenate string and a number

solution 1 - define number in the format of string so - str2='25'
(or)
solution 2 - Convert it into string while printing - str(str2) ,so print str1+str(str2)


to convert from anything to string use str()  function .

Replace

to replace any string  or character with another string or character

str1='123'
str2='456'
action='dist;item'
long_string='ds;sfsdf,sfsdf//'
print str1.replace(str1,str2)
# output  456
print str1.replace('1','7')
# output 723
print action.replace(';',',')
# output dist,item
#you can also have multiple replace at the same time
print long_string.replace(';','').replace(',','').replace('/','')
# output dssfsdfsfsdf

Arrays

Arrays are fun way to handle string and number.To declare a null array use this syntax

str1=['action','item','123']
print str1[0]
# arrays always starts with 0 and so on
# output action
print str1[2]
#output 123

To declare any as null array to this  str1=[]

Converting Arrays to string

str1=['action','item','123']
print ''.join(map(string.strip,str1))
# using this syntax we can convert the array to string
#output actionitem123

Converting String to Array

str1='this is a string'
print list(str)
#output ['t', 'h', 'i', 's', ' ', 'i', 's', ' ', 'a', ' ', 's', 't', 'r', 'i', 'n', 'g']

Converting characters into Array

str1='this is a string'
list1=str1.split(' ')
print list1
#output ['this','is','a','string']
print list1[0]
#output -  this
print list1[1]
#output - is

this split command is useful when you wish to split the string into words and fetch the required on as appropriate.

Raise

Raise is a great command to raise an error and we can use raise to publish the required error into the Operator.Example

raise " This is the following Error " , string
raise str1

Capturing position in String

#first step import module
str='12345'
# assigning value to String
print str[:-1]
# print command is used to print in the output
## when we use -1 it means it starts from the end of the string
## output 1234 as end is 5 and so backwards leave 1 character.
print str[:-2]
## so leave 2 position from end , so output 123
print str[0]
# 0 is treated as the first position
print str[0:3]
#expected output 123
print str[2:4]
#expected output 34

String search

There are some great high level search we can do used regex but here i will be showing you here simple search method using string.find

str1=' This is a program '
print string.find(str1,"is")
if string.find(str1,'is')> 0:
    print str1
else:
    print ' Not available '

# output - This is a program

whenever string.find , finds the particular string in it refers to the position and that’s the reason I use > 0 .

Another simple and efficient search is using Regular Expression (re) .

import re
str1='This is a program'
if  re.search("is",str1):
print ' Match found'
else:
print ' Not Found'

Please look into link for more way to handle simple and complete expression methods and functions.

http://www.jython.org/docs/library/re.html

OS.system

os module helps us to do Operating system based activity either in windows or unix.
os.system run an operating system command fetches the error if fails. If the process is successful then 0 else the required error message .
ODI  have the OS Command and OdiOSCommand to do pretty much the same work without writing any jython
codes .

Lets take another example

import os
if os.system ("copy test.py test1.py"  ) == 0 :
     print "success"
else:
     print " failure "

For os.system  the 0 (zero) is always marked by success and rest of the return codes by failure or error.

Look at this documentation link for os  –   http://www.jython.org/docs/library/os.html

AND  and OR condition

For and condition use this & (and symbol)
OR condition use this | (pipe symbol )

For example

str1= 'Example1'
str2='Example2'

if (( str1 =='Example1' ) & (str2 == 'Example2')):
     print  ' And works '
#output And works
if (( str1 =='Example1' ) | (str2 == 'Example1')):
    print ' OR works '
#output OR works
if ((( str1 =='Example1' ) & (str2 == 'Example3')) | (str2 == 'Example1')):
   print  ' AND and OR combination does not works '
else:
   print  ' AND and OR combination works  '
#output - AND and OR combination works

IF condition

General format
if  (condition )
results
elif (condition)
results
else
results

Ex

str1='Examples'
if str1 == 'Example' :
   print ' The String is Example '
elif str1 =='example' :
   print ' The String is example'
else:
   print ' The str1 is neither example (nor) Example '

[ Note – there is always a tab space after if condition ]

While Condition

While condition is great when we need to process something one by one and execute something till the end.

Example

File C:output.txt

abc
dba
dsg

file_open=open('C:output.txt','r')
line =file_open.readline()
while line:
    print line
    line =file_open.readline()

[ Note – there is always a tab space after while condition , till the condition is complete ]

Jython + SQL

We also use Jython to play with SQL and write the result either in File , Operator etc. To use the SQL we use the Java libraries.

import java.lang.* as lang
import java.sql.* as sql

When it comes to Jython+SQL the logic , format is pretty much the same as Java  , so in case you find an example with Java you can modify with few simple syntax change and write the codes for Jython.

The most efficient way is to use the sunopsis API getJDBCConnection for connecting to Source system using Java or Jython. This way we don’t have to worry about providing the driver parameters or hard coding them since they can change from the environment to environment. In case if you ever need to get particular info about driver , url or user you can use API getInfo()

Initially create the connection to the Source System

sourceConnection = odiRef.getJDBCConnection("SRC")

Here in Command on source we select the required technology and associated schema.

sqlstring = sourceConnection.createStatement()

Next step is to create the statement for execution .

sqlstmt="SELECT 'Errored Interface t- '||SUBSTR(ORIGIN,INSTR(ORIGIN,')')+1,
LENGTH(ORIGIN))||'nError Message tt- '||ERR_MESS||'nNo of Errored Records t- '||
ERR_COUNT AS OUTPUT FROM  ODI_TEMP.SNP_CHECK_TAB WHERE TRUNC(CHECK_DATE)=TRUNC(SYSDATE)"

In sqlstmt we define the complete select , insert, update string and pass it to this variable. This way we can call it multiple time and if we have to modify , we can change it at one place.
Here we have used t to have the tab space between the column and the output and n to have the next result in second line this way the result can be more organized. For an output example Please visit this link –http://odiexperts.com/error-records-log

result=sqlstring.executeQuery(sqlstmt)

To execute a select query use executeQuery(sqlstmt) and to insert /update query use executeUpdate(sqlstmt) .

while result.next():

In order for you to read and process the select query we tend read it records using a while loop and finally you can use getInt , getString and fetch the required records . Please look into the Java documentation for other APIs.

Also you can other similar example related to Jython + SQL

http://odiexperts.com/displaying-sql-results-in-operator
http://odiexperts.com/writing-sql-counts-using-jython

Looking for Editor to learn and practise Jython.
1. Eclipse [ Plugins such as Pydev , JyDT ]
2. Jython.bat

Jython Sources and Documentation

If you are looking for Documentation on Jython , the best resource would be to look into official documentation of jython

http://www.jython.org/docs/ , 

http://www.jython.org/docs/library/ [ Very Important Link ]

[ Note : The links are for Jython 2.5 and so you might find functions and methods which might not be still supported as ODI 10g is on 2.1  ]

Also you might not find so many codes or example for Jython , but you can refer to Python documentation and example too , as Jython is Java + Python .

You can find Python documentation here  http://python.org/doc/

also look into the Oracle documentation ( http://download.oracle.com/docs/cd/E15985_01/doc.10136/reference/DIJQR.pdf ) there  are some good example covered .

Jython not recognizing Windows 7 as NT

Please look into this link http://odiexperts.com/jython-os-bug-in-windows-7

Handling  spaces in path

While passing the OS system path especially in windows we have issue with spaces causing the error Path does not exists or File not Found etc . In order to solve that , Please have the spaces between folder name between double parenthesis. Say for example

import os
ospath=(r'C:Program" "FilesMicrosoft" "SQL" "Server\100\Tools\Binn\bcp')
if os.system(ospath)  > 0:
    raise 'BCP Unload Failes'

Program File is – Program” “File ( Program<double_quote><space><double_quote>File).
Microsoft SQL Server is Microsoft” “SQL” “Server

\ in windows path

Its good to use the two slashes in windows path , reason being some time some of the combination yields values which are understood by Jython differently  , say for example

c:numberten

In this example the n and t would be understood by Jython as next line and tab space causing to read the line wrong  and would throw errors like file not found or path does not exists etc ,so the correct way to write is (ie using double slash)

C:\number\ten

Hope this should provide beginners a guide towards the world of Jython+ODI .
We have covered few codes of Jython and  might update this post in future too so that we have consolidated Jython guide  at one place.

[ Note: There are numerous other ways to do the above codes in different way , you can explore more by looking into Jython books and documentation. ]

Keep looking into the odiexperts for more tips and tricks .