def test_outparam(self):
with self._connect() as con:
cur = con.cursor()
self._outparam_setup(cur)
values = cur.callproc('add_one_out', (1, output(value=1)))
self.assertEqual(len(values), 2, 'expected 2 parameters')
self.assertEqual(values[0], 1, 'input parameter should be unchanged')
self.assertEqual(values[1], 2, 'output parameter should get new values')
values = cur.callproc('add_one_out', (None, output(value=1)))
self.assertEqual(len(values), 2, 'expected 2 parameters')
self.assertEqual(values[0], None, 'input parameter should be unchanged')
self.assertEqual(values[1], None, 'output parameter should get new values')
def test_outparam_null_default(cursor):
with pytest.raises(ValueError):
pytds.output(None, None)
cur = cursor
cur.execute('''
create procedure outparam_null_testproc (@inparam int, @outint int = 8 output, @outstr varchar(max) = 'defstr' output)
as
begin
set nocount on
set @outint = isnull(@outint, -10) + @inparam
set @outstr = isnull(@outstr, 'null') + cast(@inparam as varchar(max))
set @inparam = 8
end
''')
values = cur.callproc('outparam_null_testproc', (1, pytds.output(value=4), pytds.output(value='str')))
assert [1, 5, 'str1'] == values
values = cur.callproc('outparam_null_testproc', (1, pytds.output(value=None, param_type='int'), pytds.output(value=None, param_type='varchar(max)')))
assert [1, -9, 'null1'] == values
values = cur.callproc('outparam_null_testproc', (1, pytds.output(value=pytds.default, param_type='int'), pytds.output(value=pytds.default, param_type='varchar(max)')))
assert [1, 9, 'defstr1'] == values
values = cur.callproc('outparam_null_testproc', (1, pytds.output(value=pytds.default, param_type='bit'), pytds.output(value=pytds.default, param_type='varchar(5)')))
assert [1, 1, 'defst'] == values
values = cur.callproc('outparam_null_testproc', (1, pytds.output(value=pytds.default, param_type=int), pytds.output(value=pytds.default, param_type=str)))
assert [1, 9, 'defstr1'] == values
def test_outparam_null_default(cursor):
with pytest.raises(ValueError):
pytds.output(None, None)
cur = cursor
cur.execute('''
create procedure outparam_null_testproc (@inparam int, @outint int = 8 output, @outstr varchar(max) = 'defstr' output)
as
begin
set nocount on
set @outint = isnull(@outint, -10) + @inparam
set @outstr = isnull(@outstr, 'null') + cast(@inparam as varchar(max))
set @inparam = 8
end
''')
values = cur.callproc('outparam_null_testproc', (1, pytds.output(value=4), pytds.output(value='str')))
assert [1, 5, 'str1'] == values
values = cur.callproc('outparam_null_testproc', (1, pytds.output(value=None, param_type='int'), pytds.output(value=None, param_type='varchar(max)')))
assert [1, -9, 'null1'] == values
values = cur.callproc('outparam_null_testproc', (1, pytds.output(value=pytds.default, param_type='int'), pytds.output(value=pytds.default, param_type='varchar(max)')))
assert [1, 9, 'defstr1'] == values
values = cur.callproc('outparam_null_testproc', (1, pytds.output(value=pytds.default, param_type='bit'), pytds.output(value=pytds.default, param_type='varchar(5)')))
assert [1, 1, 'defst'] == values
values = cur.callproc('outparam_null_testproc', (1, pytds.output(value=pytds.default, param_type=int), pytds.output(value=pytds.default, param_type=str)))
assert [1, 9, 'defstr1'] == values
def test_no_metadata_request(cursor):
cursor._session.submit_rpc(
rpc_name=pytds.tds_base.SP_PREPARE,
params=cursor._session._convert_params(
(pytds.output(param_type=int), '@p1 int', 'select @p1')),
)
cursor._session.process_rpc()
while cursor.nextset():
pass
res = cursor.get_proc_outputs()
handle = res[0]
logger.info("got handle %s", handle)
cursor._session.submit_rpc(
rpc_name=pytds.tds_base.SP_EXECUTE,
params=cursor._session._convert_params((handle, 1)),
)
cursor._session.process_rpc()
cursor._setup_row_factory()
assert cursor.fetchall() == [(1, )]
while cursor.nextset():
pass
cursor._session.submit_rpc(
rpc_name=pytds.tds_base.SP_EXECUTE,
params=cursor._session._convert_params((handle, 2)),
flags=0x02 # no metadata
)
cursor._session.process_rpc()
cursor._setup_row_factory()
# for some reason SQL server still sends metadata back
assert cursor.fetchall() == [(2, )]
while cursor.nextset():
pass
def test_call_proc(self):
with self.conn.cursor() as cur:
val = 45
values = cur.callproc('testproc', (val, default, output(value=1)))
#self.assertEqual(cur.fetchall(), [(val,)])
self.assertEqual(val + 2, values[2])
self.assertEqual(val + 2, cur.get_proc_return_status())
def test_no_metadata_request(cursor):
cursor._session.submit_rpc(
rpc_name=pytds.tds_base.SP_PREPARE,
params=cursor._session._convert_params((pytds.output(param_type=int), '@p1 int', 'select @p1')),
)
cursor._session.process_rpc()
while cursor.nextset():
pass
res = cursor.get_proc_outputs()
handle = res[0]
logger.info("got handle %s", handle)
cursor._session.submit_rpc(
rpc_name=pytds.tds_base.SP_EXECUTE,
params=cursor._session._convert_params((handle, 1)),
)
cursor._session.process_rpc()
cursor._setup_row_factory()
assert cursor.fetchall() == [(1,)]
while cursor.nextset():
pass
cursor._session.submit_rpc(
rpc_name=pytds.tds_base.SP_EXECUTE,
params=cursor._session._convert_params((handle, 2)),
flags=0x02 # no metadata
)
cursor._session.process_rpc()
cursor._setup_row_factory()
# for some reason SQL server still sends metadata back
assert cursor.fetchall() == [(2,)]
while cursor.nextset():
pass
def test_call_proc(self):
with self.conn.cursor() as cur:
val = 45
values = cur.callproc('testproc', (val, default, output(value=1)))
#self.assertEqual(cur.fetchall(), [(val,)])
self.assertEqual(val + 2, values[2])
self.assertEqual(val + 2, cur.get_proc_return_status())
def test_outparam(self):
with self._connect() as con:
cur = con.cursor()
self._outparam_setup(cur)
values = cur.callproc('add_one_out', (1, output(value=1)))
self.assertEqual(len(values), 2, 'expected 2 parameters')
self.assertEqual(values[0], 1,
'input parameter should be unchanged')
self.assertEqual(values[1], 2,
'output parameter should get new values')
values = cur.callproc('add_one_out', (None, output(value=1)))
self.assertEqual(len(values), 2, 'expected 2 parameters')
self.assertEqual(values[0], None,
'input parameter should be unchanged')
self.assertEqual(values[1], None,
'output parameter should get new values')
def test_stored_proc(cursor):
cur = cursor
val = 45
#params = {'@param': val, '@outparam': output(None), '@add': 1}
values = cur.callproc('testproc', (val, pytds.default, pytds.output(value=1)))
#self.assertEqual(cur.fetchall(), [(val,)])
assert val + 2 == values[2]
assert val + 2 == cur.get_proc_return_status()
def test_stored_proc(cursor):
cur = cursor
val = 45
#params = {'@param': val, '@outparam': output(None), '@add': 1}
values = cur.callproc('testproc', (val, pytds.default, pytds.output(value=1)))
#self.assertEqual(cur.fetchall(), [(val,)])
assert val + 2 == values[2]
assert val + 2 == cur.get_proc_return_status()
def test_outparam(self):
con = self._connect()
try:
cur = con.cursor()
self._outparam_setup(cur)
values = cur.callproc('add_one_out',(1,dbapi.output(None, 1)))
self.assertEqual(len(values), 2, 'expected 2 parameters')
self.assertEqual(values[0], 1, 'input parameter should be unchanged')
self.assertEqual(values[1], 2, 'output parameter should get new values')
finally:
con.close()
def test_outparam_and_result_set(cursor):
"""
Test stored procedure which has output parameters and also result set
"""
cur = cursor
logger.info('creating stored procedure')
cur.execute('''
CREATE PROCEDURE P_OutParam_ResultSet(@A INT OUTPUT)
AS BEGIN
SET @A = 3;
SELECT 4 AS C;
SELECT 5 AS C;
END;
''')
logger.info('executing stored procedure')
cur.callproc('P_OutParam_ResultSet', [pytds.output(value=1)])
assert [(4, )] == cur.fetchall()
assert [3] == cur.get_proc_outputs()
logger.info('execurint query after stored procedure')
cur.execute('select 5')
assert [(5, )] == cur.fetchall()
def test_outparam_and_result_set(cursor):
"""
Test stored procedure which has output parameters and also result set
"""
cur = cursor
logger.info('creating stored procedure')
cur.execute('''
CREATE PROCEDURE P_OutParam_ResultSet(@A INT OUTPUT)
AS BEGIN
SET @A = 3;
SELECT 4 AS C;
SELECT 5 AS C;
END;
'''
)
logger.info('executing stored procedure')
cur.callproc('P_OutParam_ResultSet', [pytds.output(value=1)])
assert [(4,)] == cur.fetchall()
assert [3] == cur.get_proc_outputs()
logger.info('execurint query after stored procedure')
cur.execute('select 5')
assert [(5,)] == cur.fetchall()
