Module sapdb.sql (Example)

#
# egSQL.py   call as python egSQL.py <user> <pwd> <db> [<host>]
#

import sys

import sapdb.sql

user = sys.argv [1]
pwd = sys.argv [2]
dbname = sys.argv [3]
if len (sys.argv) > 4:
    host = sys.argv [4]
else:
    host = ''

# connect to db
print 'Connecting as user', user, 'to database', dbname
session = sapdb.sql.connect (user, pwd, dbname, host)

# execute simple command
session.sql ('diagnose vtrace flush')

# get one value
stmt = 'select msgtext into ? from messages where msgno = 200'
print "\nGetting single value '%s'" % stmt
result = session.sql (stmt)

print "\tresult: %s, %s" % (result , type (result)) # tuple with one element

# get multiple values
stmt = 'select msgno, msgtext into ?, ? from messages where msgno = 200'
print "\nGetting multiple values '%s'" % stmt
result = session.sql (stmt)

print "\tresult: '%s', %s" % (result , type (result)) # tuple with two elements

# create a result set
stmt = 'select msgno, msgtext from messages where msgno between 200 and 700'
print "\nCreating a result set '%s'" % stmt
result = session.sql (stmt)

print "\tresult: '%s', %s" % (result , type (result)) # should be a SapDB_ResultSet

print '\nshow layout of result set ...'
print "%-18s %-6s (%6s, %2s) %s " % ('Name', 'Type', 'Length', 'Frac', 'In/Out')
print "=" * 55
for name, datatype, typeid, len, frac, mandatory, inout in result.getDescription ():
    print "%-18s %-6s (%6d, %4d) %s " % (name, datatype, len, frac, inout)

print '\nfetching values ...'

row = result.next ()
while row:
    print "\t", row
    row = result.next ()

print '\nfetching again using "for ... in ..."'
for msgno, msgtext in result:
    print "[%6d] %s" % (msgno, msgtext)

# parse/execute
stmt = 'select msgno, msgtext into ?, ? from messages where msgno = ?'
#stmt = 'select * from messages where msgno between ? and ?'
print "\nParsing '%s'" % stmt
prepared = session.prepare (stmt)

print '\nshow layout of parameters ...'
print "%-18s %-6s (%6s, %2s) %s " % ('Name', 'Type', 'Length', 'Frac', 'In/Out')
print "=" * 55
for name, datatype, typeid, len, frac, mandatory, inout in prepared.getDescription ():
    print "%-18s %-6s (%6d, %4d) %s " % (name, datatype, len, frac, inout)

values = [200]
print '\nExecuting with values:', values
result = prepared.execute (values)
print "\t", 'result:', result

print '\nClosing prepared'
prepared = None    # if this was the last reference, then the object is destroyed

# simplified executing with arguments
print "\nparse/execute in one call '%s'" % stmt
result = session.sqlX (stmt, values)
print "\t", 'result:', result

session.release ()
print '\nSession released'




	
	
	

This site is sponsored by SAP AG.