I created a dbproc returns cursor. There I joined two tables. No matter these two tables have UNICODE/NON-UNICODE mixed or not I can't create a valid dbproc from python though I can drop and recreate the dbproc with exactly same script in SQL Studio. Also I can't see any error message in SQL Studio for the dbproc I created from python though I can't execute it. If I tried to execute this dbproc created from python in SQL Studio it would return an error: General error;-4024.
If I created the dbproc within SQL Studio even without UNICODE the output from the python is still weird though it's okay with 'SELECT ... FROM ...'.
Here I attached the sample code to create the error.
Thanks,
Christine Ke
#**************************************************************** # # sapdbBug.py - to create a simple case to report sapdb bug # # Written by Christine Ke # #****************************************************************
import sys import sapdb # to access sapdb SQL import sapdb.loader # to access the Loader
# # Initial db connection # user = 'TEST' pwd = 'TEST' dbname = 'SDC' host = ''
#---------------------------------------------------------------- # # CreateTable - create a table # # Input: sTableName - name of table # sScript - the script to create the table # #----------------------------------------------------------------
def createTable(sTableName, sScript):
#
# drop table if exists
#
try:
session.cmd('DROP TABLE %s' %sTableName)
except sapdb.loader.LoaderError, err:
pass # Table not exists #
# create the table
#
try:
session.cmd(sScript)
print 'TABLE', sTableName, 'CREATED!'
except sapdb.loader.LoaderError, err:
print 'TABLE', sTableName, 'FAIL!\n', err#---------------------------------------------------------------- # # CreateDbProc - create a dbproc # # Input: sDbProcName - name of dbproc # sScript - the script to create the dbproc # #----------------------------------------------------------------
def createDbProc(sDbProcName, sScript):
#
# drop dbproc if exists
#
try:
session.cmd('DROP DBPROC %s' %sDbProcName)
except sapdb.loader.LoaderError, err:
pass # Table not exists #
# create the table
#
try:
session.cmd(sScript)
print 'DBPROC', sDbProcName, 'CREATED!'
except sapdb.loader.LoaderError, err:
print 'DBPROC', sDbProcName, 'FAIL!\n', err#---------------------------------------------------------------- # # testDbProc - test the existed dbproc # # Input: sScript - the script to test the dbproc # #----------------------------------------------------------------
def testDbProc(sScript):
#
# execute the script
#
try:
rows = session.sqlX(sScript)print sScript
#
# print out rows
#
for row in rows:
print row except sapdb.SQLError, err:
print err.message # error message from db# # Initial tables name and scripts # tplTables = ( 'tblTest1', 'tblTest2', 'tblTest3' )
#
# tableName : script
#
dctTables = {
'tblTest1' : """
CREATE TABLE tblTest1(
tblTest1ID FIXED(12, 0) PRIMARY KEY,
description VARCHAR(100) NOT NULL)""",
'tblTest2' : """
CREATE TABLE tblTest2(
tblTest2ID FIXED(12, 0) PRIMARY KEY,
tblTest1ID FIXED(12, 0) NULL,
Foreign Key(tblTest1ID) References tblTest1(tblTest1ID))""",
'tblTest3' : """
CREATE TABLE tblTest3(
tblTest3ID FIXED(12, 0) PRIMARY KEY,
tblTest1ID FIXED(12, 0) NULL,
unicodeDesc VARCHAR(100) UNICODE NOT NULL,
Foreign Key(tblTest1ID) References tblTest1(tblTest1ID))"""
}
#
# Initial DbProcs name and scripts - DbProc created sequence sensitive
#
tplDbProcs = (
'dataLoad',
'dataLoadUnicode'
)#
# dbprocname : script
#
dctDbProcs = {
"dataLoad" : """
CREATE DBPROC dataLoad (
IN pnRange Fixed(12, 0))
RETURNS CURSOR AS DECLARE :$CURSOR CURSOR FOR
SELECT t1.tblTest1ID, t2.tblTest2ID, t1.description
FROM test.tblTest1 t1, test.tblTest2 t2
WHERE t1.tblTest1ID > :pnRange
AND t1.tblTest1ID = t2.tblTest1ID
ORDER BY t1.tblTest1ID;
""",
"dataLoadUnicode" : """
CREATE DBPROC dataLoadUnicode (
IN pnRange Fixed(12, 0))
RETURNS CURSOR AS DECLARE :$CURSOR CURSOR FOR
SELECT t1.tblTest1ID, t3.tblTest3ID, t1.description, t3.unicodeDesc
FROM test.tblTest1 t1, test.tblTest3 t3
WHERE t1.tblTest1ID > :pnRange
AND t1.tblTest1ID = t3.tblTest1ID
ORDER BY t1.tblTest1ID;
"""}#
#
#
tplTestData = (
"""INSERT INTO tblTest1(tblTest1ID, Description)
VALUES(1, 'Hello world')""",
"""INSERT INTO tblTest1(tblTest1ID, Description)
VALUES(2, 'Goodbye world')""",
"""INSERT INTO tblTest1(tblTest1ID, Description)
VALUES(3, 'Sorry')""",
"""INSERT INTO tblTest2(tblTest2ID, tblTest1ID)
VALUES(1, 1)""",
"""INSERT INTO tblTest2(tblTest2ID, tblTest1ID)
VALUES(2, 1)""",
"""INSERT INTO tblTest2(tblTest2ID, tblTest1ID)
VALUES(3, 2)""",
"""INSERT INTO tblTest3(tblTest3ID, tblTest1ID, unicodeDesc)
VALUES(1, 1, 'Hello world')""",
"""INSERT INTO tblTest3(tblTest3ID, tblTest1ID, unicodeDesc)
VALUES(2, 1, 'Goodbye world')""",
"""INSERT INTO tblTest3(tblTest3ID, tblTest1ID, unicodeDesc)
VALUES(3, 2, 'Sorry')""",)
# # Test DbProc # tplTestDbProcs = ( "CALL dataLoad(0)", "CALL dataLoadUnicode(0)" )
session = sapdb.loader.Loader (host, dbname) # connect to db
session.cmd ('use user %s %s' % (user, pwd)) # connectprint 'CONNECTED'
# # create tables # print "=" * 20, "CREATE TABLES", "=" * 20
for tbl in tplTables: # create tables in the sequence defined in tplTables
print dctTables[tbl]
createTable(tbl, dctTables[tbl]) # create table
# # create dbProcs # print "=" * 20, "CREATE DBPROCS", "=" * 20
for dbProc in tplDbProcs: # create dbprocs in the sequence defined in tplDbProcs
print dctDbProcs[dbProc]
createDbProc(dbProc, dctDbProcs[dbProc]) # create dbProc
# # add test data # print "=" * 20, "TEST DATA", "=" * 20
for td in tplTestData:
try:
session.cmd(td) # add test data
session.cmd("COMMIT")
print td, "...DONE\n"
except sapdb.loader.LoaderError, err:
print td, "\n", err, "\n"del session
session = sapdb.connect (user, pwd, dbname, host)
#
# test join tables without unicode
#
print "=" * 20, "TEST JOIN WITHOUT UNICODE", "=" * 20
rows = session.sql("""
SELECT t1.tblTest1ID, t2.tblTest2ID, t1.description
FROM test.tblTest1 t1, test.tblTest2 t2
WHERE t1.tblTest1ID > 0
AND t1.tblTest1ID = t2.tblTest1ID
ORDER BY t1.tblTest1ID""")for row in rows: print row
#
# test join tables mix with unicode and nonunicode
#
print "=" * 15, "TEST JOIN MIX WITH UNICODE AND NONUNIDOCE", "=" * 15
rows = session.sql("""
SELECT t1.tblTest1ID, t3.tblTest3ID, t1.description, t3.unicodeDesc
FROM test.tblTest1 t1, test.tblTest3 t3
WHERE t1.tblTest1ID > 0
AND t1.tblTest1ID = t3.tblTest1ID
ORDER BY t1.tblTest1ID""")for row in rows: print row
# # test dbprocs # print "=" * 20, "TEST DBPROC", "=" * 20
for tdp in tplTestDbProcs: testDbProc(tdp)
print "=" * 20, "IT'S DONE", "=" * 20
session = None # disconnect
_________________________________________________________________
Protect your inbox from harmful viruses with new ninemsn Premium. Click here http://ninemsn.com.au/premium/landing.asp
-- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
