Using Windows ADO for AS/400 Query/Execution

Okay, so it's not Python running on the iSeries, but it is Python, and it's connecting to an AS/400, so the example is lightly relevant!

One limitation of Python is the ODBC v1.0 spec that's built in. That specification doesn't allow for (1) running a remote stored procedure (or CL program in this example) and (2) retreiving any returned values. This example shows how to do it using the Microsoft ADO API's built into Windows:

• import win32com.client
  from win32com.client import constants

   

• #
  # AS/400 SYSTEM NAME ACCORDING TO ODBC CONFIGURATION
  #

• 
  system = 'MYAS400'
  username = 'MYUSERNAME'
  password = 'MYPASS'
  

• #
  # SET THE DSN SYSTEM/USER VALUES
  #
  

• pythoncom.CoInitialize()
  dsn = 'PROVIDER=IBMDA400.DataSource.1; '
  dsn += 'DATA SOURCE=%(system)s; ' % vars()
  dsn += 'USER ID=%(username)s; PASSWORD=%(password)s' % vars()

•  

• #
  # CREATE THE ADO OBJECT AND SET THE SQL STATEMENT
  #

• sql    = "{{CALL MYLIB.GETPRC(?,?,?,?,?,?)}}"
  params = [ ['in',6],['in',20],['in',12],['in',30],['inout',9],['inout',4] ]

• ADOdb  = win32com.client.Dispatch('ADODB.Connection')
  ADOdb.Open(dsn)
  myCommand = win32com.client.Dispatch('ADODB.Command')
  myCommand.ActiveConnection = ADOdb
  myCommand.CommandText = sql

  myCommand.Prepared = True
  

• ptype = 129

• for each in params:
      val = string.upper(each[0])
      if val in ['IN','INPUT']:
          pdir = constants.adParamInput
      elif val in ['OUT','OUTPUT']:
          pdir = constants.adParamOutput
      else:
          pdir = constants.adParamInputOutput
      plen = each[1]
      newp = myCommand.CreateParameter(pname,ptype,pdir,plen,'')
      myCommand.Parameters.Append(newp)
  

• cmd.Parameters.Item('P0').Value = 'CUSTNAME'
  cmd.Parameters.Item('P1').Value = 'Pocket'
  cmd.Parameters.Item('P2').Value = '112335111224'
  cmd.Parameters.Item('P3').Value = 'Secrets of the Universe'
  cmd.Parameters.Item('P4').Value = '0.0'
  cmd.Parameters.Item('P5').Value = '1'

• #
  # EXECUTE THE PROGRAM AND RETRIEVE THE RESULTS
  #
  

• rs,result = myCommand.Execute()
  price = float(myCommand.Parameters.Item('P4').Value)
  uom = int(myCommand.Parameters.Item('P5').Value)

•  

• #
  # CLOSE THE CONNECTION
  #
  

• ADOdb.Close()