Using JDBC in jython

Using JDBC

It can be convenient to use JDBC (Java DataBase Connectivity) to connect to a database from Jython. All Java classes in the CLASSPATH can be directly used in Jython. The following example shows how to use the JDBC API to connect to a database, to run a SQL query and write the result into a file.
The reference documentation for Java is available at http://java.sun.com
import java.sql as sql
import java.lang as lang
def main():
  driver, url, user, passwd = (
    'oracle.jdbc.driver.OracleDriver',
    'jdbc:oracle:thin:@myserver:1521:mysid',
    'myuser',
    'mypasswd')
  ##### Register Driver
  lang.Class.forName(driver)
  
  ##### Create a Connection Object
  myCon = sql.DriverManager.getConnection(url, user, passwd)
  f = open('c:/temp/jdbc_res.txt', 'w')
  try:
    ##### Create a Statement
    myStmt = myCon.createStatement()
    ##### Run a Select Query and get a Result Set
    myRs = myStmt.executeQuery("select TABLE_NAME, OWNER from ALL_TABLES where TABLE_NAME like 'SNP%'")
  
    ##### Loop over the Result Set and print the result in a file
    while(myRs.next()):
      print >> f , "%s\t%s" %(myRs.getString("TABLE_NAME"), myRs.getString("OWNER") )
  finally:
    myCon.close()
    f.close()

### Entry Point of the program      
if __name__ == '__main__':
  main()

It is possible to combine Jython with odiRef API in the Oracle Data Integrator Procedures, for even more flexibility. Instead of hard-coding the parameters to connect to a database in the program, the getInfo method can be used:
import java.sql as sql
import java.lang as lang
def main():
  driver, url, user, passwd = (
    '<%=odiRef.getInfo("DEST_JAVA_DRIVER")%>',
    '<%=odiRef.getInfo("DEST_JAVA_URL")%>',
    '<%=odiRef.getInfo("DEST_USER_NAME")%>',
    '<%=odiRef.getInfo("DEST_PASS")%>')
  ##### Register Driver
  lang.Class.forName(driver)
[...]

Using the Operating System Environment Variables in jython

Using the Operating System Environment Variables

It can be usefull to retrieve the Operating System environment variables. The following examples show how to retrieve this list:
import os
ftrg = open('c:/temp/listenv.txt', 'w')
try:
  envDict = os.environ
  osCurrentDirectory = os.getcwd()
  print >> ftrg, 'Current Directory: %s'  % osCurrentDirectory
  print >> ftrg, '=============================='
  print >> ftrg, 'List of environment variables:'
  print >> ftrg, '=============================='
  for aKey inenvDict.keys():
    print >> ftrg, '%s\t= %s' % (aKey, envDict[aKey])
  print >> ftrg, '=============================='
  print >> ftrg, 'Oracle Data Integrator specific environment variables:'
  print >> ftrg, '=============================='
  for aKey inenvDict.keys():
    ifaKey.startswith('SNP_'):
      print >> ftrg, '%s\t= %s' % (aKey, envDict[aKey])
finally:
  ftrg.close()

To retrieve the value of the USERNAME environment variable, just write:
import os
currentUser = os.environ['USERNAME']

Using FTP in jython

Using FTP

In some environments, it can be useful to use FTP (File Transfer Protocol) to transfer files between heterogeneous systems. Oracle Data Integrator provides an additional Jython module to further integrate FTP.
The following examples show how to use this module:
Pull the *.txt files from /home/odi of the server ftp.myserver.cominto the local directory c:\temp

import snpsftp
ftp = snpsftp.SnpsFTP('ftp.myserver.com', 'mylogin', 'mypasswd')

try:

  ftp.setmode('ASCII')
  ftp.mget('/home/odi', '*.txt', 'c:/temp')
finally:
  ftp.close()

Push the files *.zipfrom C:\odi\lib onto ftp.myserver.comin the remote directory /home/odi/lib

import snpsftp
ftp = snpsftp.SnpsFTP('ftp.myserver.com', 'mylogin', 'mypasswd')
try:
  ftp.setmode('BINARY')
  ftp.mput('C:/odi/lib', '*.zip', '/home/odi/lib')
finally:
  ftp.close()

Using IP sockets in jython

Using IP sockets

IP sockets are used to initiate an IP communication between two processes on the network. Jython greatly simplifies the creation of IP servers (waiting for IP packets) or IP clients (sending IP packets).
The following example shows the implementation of a very basic IP server. It waits for data coming from client software, and writes each received packet into the file c:/temp/socketserver.log. If a server receives the packet STOPSERVER, the server stops:

Server

import socket
import time
HOST = ''
PORT = 9191 # Arbitrary port (not recommended)
LOG_FILE = 'c:/temp/sockserver.log'
mySock = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
mySock.bind((HOST, PORT))
logfile = open(LOG_FILE, 'w')
try:
  print >> logfile, '*** Server started : %s' % time.strftime('%Y-%m-%d %H:%M:%S')
  while 1:
    data, addr = mySock.recvfrom(1024)
    print >> logfile, '%s (%s): %s' % (time.strftime('%Y-%m-%d %H:%M:%S'), addr, data)
    ifdata == 'STOPSERVER':
      print >> logfile, '*** Server shutdown at %s by %s' % (time.strftime('%Y-%m-%d %H:%M:%S'), addr)
      break
finally:
  logfile.close()

Client

The following example can be used ot test the above server. It sends two packets before asking the server to stop.
import socket
import sys
PORT = 9191 # Same port as the server
HOST = 'SERVER_IP_ADDRESS'
mySock = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
mySock.sendto('Hello World !', (HOST, PORT))
mySock.sendto('Do U hear me?', (HOST, PORT))
mySock.sendto('STOPSERVER', (HOST, PORT))

Tuples in jython

Tuples

Tuples are non modifiable object arrays parsed with an index.
A tuple is handled as a series of values separated by commas and within brackets.

·         () is an empty tuple

·         (0,1,2,3) is a 4 elements tuple, indexed from 0 to 3

·         tuple = (0, (1, 2), (4,5,6)) is a tuple that contains other tuples. tuple[1][0] returns 1

Operations on sequences are available for the tuples.