from sqlalchemy.ext import sqlsoup

class SqlSoup(sqlsoup.SqlSoup):
    def begin(self, *args, **kw):
        return sqlsoup.Session.begin(*args, **kw)

    def commit(self, *args, **kw):
        return sqlsoup.Session.commit(*args, **kw)

    def rollback(self, *args, **kw):
        return sqlsoup.Session.rollback(*args, **kw)

    def close(self, *args, **kw):
        return sqlsoup.Session.close(*args, **kw)

    def flush(self, *args, **kw):
        return sqlsoup.Session.flush(*args, **kw)

db = SqlSoup("mssql://username:password@server/databasename")

print "Note that none of the below generates any errors/exceptions."

print "Stored Procedure Test 1"
print "THIS WORKS SUCCESSFULLY AS EXPECTED"
print "This is a short stored procedure that does a database update and returns a single integer value."
print "Note : this stored procedure doesn't have 'COMMIT' appended to it."
print "Executing stored procedure 1: "
print db.bind.execute("SET NOCOUNT ON; DECLARE @retid INT; EXECUTE GetNextID 'BANKS', 'ID', @retid OUTPUT; SELECT @retid; SET NOCOUNT OFF;").fetchall()
db.flush()

print "Stored Procedure Test 2"
print "THIS WORKS SUCCESSFULLY AS EXPECTED IF I APPEND 'COMMIT' TO THE END"
print "This is a rather complicated stored procedure that does inserts"
print "on many different tables. It also executes a number of other stored"
print "procedures. Once the stored procedure is run the value of the claim"
print "it has inserted gets returned and all the relevant database actions"
print "that it performs can be viewed in the database, ie - it works."
print "This only works (from what I can see) because of the 'COMMIT' that"
print " I included at the end of the stored procedure text. If I ommit this"
print "'COMMIT' the stored procedure returns a value - but fails silently."

print "Executing stored procedure 2"
claim1 = db.bind.execute("""
SET NOCOUNT ON;  
DECLARE 
    @claimtype VARCHAR(3), 
    @classcode VARCHAR(3), 
    @groupcodeentity_id INT, 
    @dateofloss VARCHAR(30), 
    @initialstatus_id INT, 
    @peopleadded_id INT, 
    @currency_code VARCHAR(3), 
    @claimant_id INT, 
    @claimant_reference VARCHAR(100), 
    @freightoperator_id INT, 
    @freightoperator_reference VARCHAR(100), 
    @losstype INT, 
    @peoplehandler_id INT, 
    @clientcontract_id INT, 
    @datetobroker DATETIME, 
    @claimdetails VARCHAR(MAX), 
    @id INT;  
EXECUTE 
    net_ClaimAddNew 
          @claimtype = 'FOP' , 
          @classcode = '100' , 
          @groupcodeentity_id = 579 , 
          @dateofloss = '2006-10-18 00:00:00' , 
          @initialstatus_id = 18 , 
          @peopleadded_id = 1246 , 
          @currency_code = 'ZAR' , 
          @claimant_id = NULL , 
          @claimant_reference = NULL , 
          @freightoperator_id = 2613 , 
          @freightoperator_reference = NULL , 
          @losstype = 0 , 
          @peoplehandler_id = 1246 , 
          @clientcontract_id = NULL , 
          @datetobroker = '2009-10-08' , 
          @claimdetails = 'UNKNOWN' , 
          @id = @id OUTPUT  
SELECT @id; COMMIT;
SET NOCOUNT OFF;
                          """)

db.flush()
print " The following claim was added"
for c in claim1.fetchall():
    print c

print "Stored Procedure Test 3"
print "THIS FAILS - why does this fail?!?"
print "This stored procedure is successfully executed and I can view the "
print "values returned from the stored procedure - but after this has run"
print "none of the changes made by the stored procedure (ie inserts and"
print "updates) are found in the database."
print "Executing stored procedure 3"
claim2 = db.bind.execute("""
SET NOCOUNT ON;  
DECLARE 
    @claimtype VARCHAR(3), 
    @classcode VARCHAR(3), 
    @groupcodeentity_id INT, 
    @dateofloss VARCHAR(30), 
    @initialstatus_id INT, 
    @peopleadded_id INT, 
    @currency_code VARCHAR(3), 
    @claimant_id INT, 
    @claimant_reference VARCHAR(100), 
    @freightoperator_id INT, 
    @freightoperator_reference VARCHAR(100), 
    @losstype INT, 
    @peoplehandler_id INT, 
    @clientcontract_id INT, 
    @datetobroker DATETIME, 
    @claimdetails VARCHAR(MAX), 
    @id INT;  
EXECUTE 
    net_ClaimAddNew 
          @claimtype = 'FOP' , 
          @classcode = '100' , 
          @groupcodeentity_id = 579 , 
          @dateofloss = '2006-10-18 00:00:00' , 
          @initialstatus_id = 18 , 
          @peopleadded_id = 1246 , 
          @currency_code = 'ZAR' , 
          @claimant_id = NULL , 
          @claimant_reference = NULL , 
          @freightoperator_id = 2613 , 
          @freightoperator_reference = NULL , 
          @losstype = 0 , 
          @peoplehandler_id = 1246 , 
          @clientcontract_id = NULL , 
          @datetobroker = '2009-10-08' , 
          @claimdetails = 'UNKNOWN' , 
          @id = @id OUTPUT;
SET NOCOUNT OFF;
                          """)
db.flush()
print " The following claim was added"
for c in claim2.fetchall():
    print c

db.commit()
db.close()