Commits

Anonymous committed ac265da

Added preliminary Cursor.callproc() support for database procedures. Only supports dbprocs with no parms and no results.

  • Participants
  • Parent commits 394c687

Comments (0)

Files changed (2)

dbapi/dbapi/generic.py

 # 2007-10-11 carsten Added DBNull handling
 # 2009-12-09 clach04 Added date support; date only, not just datetime (for input bind parameters)
 # 2010-12-31 clach04 Added decimal support (for input bind parameters)
+# 2010-12-31 clach04 Added preliminary Cursor.callproc() support for database procedures. Does not support parameters, or results (return codes, rows, etc.)
 
 import clr
 clr.AddReference('System.Data')
 from System import Array, Object, DBNull
 from System.Data.Common import DbConnectionStringBuilder
 
+import common
+
 def _load_type(assembly, typename):
     import clr
     clr.AddReference(assembly)
             self.reader.Close()
             self.reader = None
     
+    def callproc(self, procname, parameters=None):
+        ## add , parameter_names=None ??? like execute extension below?
+        self._reset()
+        command = self.connection.connection.CreateCommand()
+        command.CommandText = '{call %s }' % procname ## TODO params... TODO return code
+        if self.connection.transaction:
+            command.Transaction = self.connection.transaction
+        
+        if parameters is not None:
+            raise common.NotSupportedError('Parameters to callproc not implemented.')
+        
+        command.ExecuteNonQuery()
+        
+        result = None
+        self.description = None
+        return result
+    
     def execute(self, operation, parameters=None, parameter_names=None):
         # print "adonet2dbapi: executing '" + str(operation) + "' with '" + str(parameters) + "'"
         

dbapi/demo_ingres_dbproc.py

+#!/usr/bin/env python
+# -*- coding: ascii -*-
+# vim:ts=4:sw=4:softtabstop=4:smarttab:expandtab
+#
+"""Usage, assuming defualt install location:
+
+    "C:\Program Files\IronPython 2.6 for .NET 4.0\ipy.exe" demo_ingres.py
+    "C:\Program Files\IronPython 2.6\ipy.exe" demo_ingres.py
+
+"""
+
+import datetime
+from decimal import Decimal
+
+import dbapi
+
+import ingres as db
+
+def simple_select(c, sql_query, bind_params=None):
+    """where c is a cursor"""
+    print sql_query
+    if bind_params is None:
+        c.execute(sql_query)
+    else:
+        print bind_params
+        c.execute(sql_query, bind_params)
+    if c.description is not None:
+        # We have a SELECT statement
+        print c.description
+        row = c.fetchone()
+        while row:
+            print row
+            row = c.fetchone()
+    print ''
+
+
+connection_function = db.connect
+
+
+connectstr = 'Host=YOUR_SERVERNAME;Database=iidbdb;Port=II7;User ID=YOUR_USERNAME;Password=YOUR_OS_PASSWORD'
+connectstr = 'nclach04'  # database needs to be writable for this demo/test
+conn = connection_function(connectstr)
+
+c = conn.cursor()
+
+
+# for now leave these here as sanity checks/tests
+
+simple_select(c, 'select * from iidbconstants')
+
+simple_select(c, "select ingresdate('now') from iidbconstants")
+
+bind_params = (datetime.date(2001, 02, 03),)
+simple_select(c, 'select ? from iidbconstants', bind_params)
+
+bind_params = (datetime.datetime(2001, 02, 03, 04, 05, 06),)
+simple_select(c, 'select ? from iidbconstants', bind_params)
+
+simple_select(c, 'select decimal(123.45, 30, 4) from iidbconstants')
+
+bind_params = (Decimal('1234567891234567891234567891.234'),)  # SQL type: decimal(31, 3)
+sql_text = "select ? from iidbconstants"
+simple_select(c, sql_text, bind_params)
+
+def dumb_drop(obj_name, obj_type='table'):
+    """NOTE1 schema name is expected to be in obj_name (or current schema used)
+    NOTE2 delim'd id's need to be pre-delimited!"""
+    sql_query = 'drop %s %s' % (obj_type, obj_name)
+    try:
+        c.execute(sql_query)
+    except SystemError, info:
+        ## eeeks crappy exception support.....
+        print 'FIXME exceptions broken...'
+        pass
+
+
+dumb_drop('tbl_test_dbp')
+sql_query = '''create table tbl_test_dbp (
+    col1 varchar(12)
+)
+'''
+print sql_query
+c.execute(sql_query)
+
+dbproc_name = 'dbp_noparms'
+dumb_drop(dbproc_name, 'procedure')
+print 'about to issue dbproc DDL'
+
+sql_query = '''create procedure DBPROC_NAME_MARKER as
+        begin
+            INSERT INTO tbl_test_dbp (col1) values ('no parms');
+        end
+'''
+sql_query = sql_query.replace('DBPROC_NAME_MARKER', dbproc_name)  ## Avoid % (and {{}}) replacements
+print sql_query
+c.execute(sql_query)
+
+
+simple_select(c, 'select * from tbl_test_dbp')
+
+sql_query = "execute procedure DBPROC_NAME_MARKER ".replace('DBPROC_NAME_MARKER', dbproc_name)
+print sql_query
+c.execute(sql_query)
+
+simple_select(c, 'select * from tbl_test_dbp')
+
+
+bind_params = None
+dbproc_result = c.callproc(dbproc_name, bind_params)
+print 'dbproc_result ', dbproc_result 
+
+simple_select(c, 'select * from tbl_test_dbp')
+
+dbproc_name = 'dbp_vc12'
+dumb_drop(dbproc_name, 'procedure')
+print 'about to issue dbproc DDL'
+
+sql_query = '''create procedure DBPROC_NAME_MARKER (a varchar(12)) as
+        begin
+            INSERT INTO tbl_test_dbp (col1) values (a);
+            
+            a = NULL;
+            a = '5';
+            a = 'hello world!';
+        end
+'''
+sql_query = sql_query.replace('DBPROC_NAME_MARKER', dbproc_name)  ## Avoid % (and {{}}) replacements
+print sql_query
+c.execute(sql_query)
+
+simple_select(c, 'select * from tbl_test_dbp')
+
+sql_query = "execute procedure DBPROC_NAME_MARKER (a='test?')".replace('DBPROC_NAME_MARKER', dbproc_name)
+print sql_query
+c.execute(sql_query)
+
+simple_select(c, 'select * from tbl_test_dbp')
+
+bind_params = ('callproc!',)
+dbproc_result = c.callproc(dbproc_name, bind_params)
+print 'dbproc_result ', dbproc_result 
+
+simple_select(c, 'select * from tbl_test_dbp')