Skip to content

Instantly share code, notes, and snippets.

@vernondcole
Last active August 29, 2015 14:00
Show Gist options
  • Save vernondcole/11053526 to your computer and use it in GitHub Desktop.
Save vernondcole/11053526 to your computer and use it in GitHub Desktop.
stored procedure test for PEP-249 db-api operation
#
# The following is a snippit from the unit test for adodbapi.
#
# this code is for Microsoft SQL server -- I tried writing stored procedurs for MySQL and PostgreSQL, but failed.
#
# Please reply to me by sending me some actually valid stored procedurse for those engines.
#
# Thanks!
#
# {Yes, I know bare "assert" statements are old hat. The code has worked like that since 2002. I did not write it. Feel free to edit.}
class TestADOwithSQLServer(CommonDBTests):
def setUp(self):
self.conn = config.dbSqlServerconnect(*config.connStrSQLServer[0], **config.connStrSQLServer[1])
self.conn.timeout = 30 # turn timeout back up
self.engine = 'MSSQL'
self.db = config.dbSqlServerconnect
self.remote = config.connStrSQLServer[2]
def tearDown(self):
try:
self.conn.rollback()
except:
pass
try:
self.conn.close()
except:
pass
self.conn=None
def getConnection(self):
return self.conn
def testVariableReturningStoredProcedure(self):
crsr=self.conn.cursor()
spdef= """
CREATE PROCEDURE sp_DeleteMeOnlyForTesting
@theInput varchar(50),
@theOtherInput varchar(50),
@theOutput varchar(100) OUTPUT
AS
SET @theOutput=@theInput+@theOtherInput
"""
try:
crsr.execute("DROP PROCEDURE sp_DeleteMeOnlyForTesting")
self.conn.commit()
except: #Make sure it is empty
pass
crsr.execute(spdef)
retvalues=crsr.callproc('sp_DeleteMeOnlyForTesting',('Dodsworth','Anne',' '))
assert retvalues[0]=='Dodsworth', '%s is not "Dodsworth"'%repr(retvalues[0])
assert retvalues[1]=='Anne','%s is not "Anne"'%repr(retvalues[1])
assert retvalues[2]=='DodsworthAnne','%s is not "DodsworthAnne"'%repr(retvalues[2])
self.conn.rollback()
def testMultipleSetReturn(self):
crsr=self.getCursor()
self.helpCreateAndPopulateTableTemp(crsr) # creates the table xx_<config.tmp> and puts data in it.
spdef= """
CREATE PROCEDURE sp_DeleteMe_OnlyForTesting
AS
SELECT fldData FROM xx_%s ORDER BY fldData ASC
SELECT fldData From xx_%s where fldData = -9999
SELECT fldData FROM xx_%s ORDER BY fldData DESC
""" % (config.tmp, config.tmp, config.tmp)
try:
crsr.execute("DROP PROCEDURE sp_DeleteMe_OnlyForTesting")
self.conn.commit()
except: #Make sure it is empty
pass
crsr.execute(spdef)
retvalues=crsr.callproc('sp_DeleteMe_OnlyForTesting')
row=crsr.fetchone()
self.assertEquals(row[0], 0)
assert crsr.nextset() == True, 'Operation should succeed'
assert not crsr.fetchall(), 'Should be an empty second set'
assert crsr.nextset() == True, 'third set should be present'
rowdesc=crsr.fetchall()
self.assertEquals(rowdesc[0][0],8)
assert crsr.nextset() == None,'No more return sets, should return None'
self.helpRollbackTblTemp()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment