def test_binary_output(self):
with self.connect() as connection:
with connection.cursor() as cursor:
sproc = self.test_binary_output.__name__
with self.stored_procedure(
cursor, sproc, '''
@pIn VARBINARY(8),
@pOut VARBINARY(16) OUTPUT
AS
SET @pOut = @pIn + @pIn;
'''):
inputs = (b'\x01\x02\x03\x04',
ctds.Parameter(ctds.SqlVarBinary(None, size=16),
output=True))
outputs = cursor.callproc(sproc, inputs)
self.assertEqual(id(outputs[0]), id(inputs[0]))
self.assertEqual(outputs[1], inputs[0] + inputs[0])
self.assertRaises(ctds.InterfaceError, cursor.fetchone)
# Note: When the output parameter size is less than the actual data,
# SQL Server truncates the data.
inputs = (b'\x01\x02\x03\x04',
ctds.Parameter(ctds.SqlVarBinary(None, size=2),
output=True))
outputs = cursor.callproc(sproc, inputs)
self.assertEqual(id(outputs[0]), id(inputs[0]))
self.assertEqual(outputs[1], inputs[0][:2])
self.assertRaises(ctds.InterfaceError, cursor.fetchone)
def test_binary(self):
with self.connect() as connection:
with connection.cursor() as cursor:
sproc = self.test_binary.__name__
with self.stored_procedure(
cursor, sproc, '''
@pBinary BINARY(8),
@pVarBinary VARBINARY(16),
@pBinaryNone BINARY(1),
@pVarBinaryNone VARBINARY(1)
AS
SELECT
CONVERT(VARCHAR(32), @pBinary, 1),
CONVERT(VARCHAR(32), @pVarBinary, 1),
@pBinaryNone,
@pVarBinaryNone
'''):
inputs = (b'\x01\x02\x03\x04',
bytearray(b'\xde\xad\xbe\xef\xde\xad\xbe\xef'),
ctds.SqlVarBinary(None), ctds.SqlVarBinary(None))
outputs = cursor.callproc(sproc, inputs)
self.assertEqual(id(outputs[0]), id(inputs[0]))
self.assertEqual(id(outputs[1]), id(inputs[1]))
self.assertEqual(tuple(cursor.fetchone()),
(('0x0102030400000000',
'0xDEADBEEFDEADBEEF', None, None)))
input_dict = {
'@pBinaryNone':
ctds.SqlVarBinary(None),
'@pVarBinary':
bytearray(b'\xde\xad\xbe\xef\xde\xad\xbe\xef'),
'@pBinary':
b'\x01\x02\x03\x04',
'@pVarBinaryNone':
ctds.SqlVarBinary(None)
}
outputs = cursor.callproc(sproc, input_dict)
self.assertEqual(id(outputs['@pBinary']),
id(input_dict['@pBinary']))
self.assertEqual(id(outputs['@pVarBinary']),
id(input_dict['@pVarBinary']))
self.assertEqual(tuple(cursor.fetchone()),
(('0x0102030400000000',
'0xDEADBEEFDEADBEEF', None, None)))
def test_varbinary(self):
with self.connect() as connection:
with connection.cursor() as cursor:
for value in (None, b' ', b'1234567890'):
for size in (None, 1, 3, 500):
kwargs = {}
if size is not None:
kwargs['size'] = size
expected_size = size
else:
expected_size = 1 if value is None else max(
1, len(value))
varbinary = ctds.SqlVarBinary(value, **kwargs)
self.assertEqual(id(varbinary.value), id(value))
self.assertEqual(varbinary.size, expected_size)
self.assertEqual(varbinary.tdstype, ctds.VARBINARY)
cursor.execute(
'''
SELECT :0
''', (varbinary, ))
self.assertEqual(
[tuple(row) for row in cursor.fetchall()],
[(value
if value is None else value[0:expected_size], )])
def test___repr__(self):
for parameter, expected in (
(ctds.Parameter(b'123',
output=True), "ctds.Parameter(b'123', output=True)"
if PY3 else "ctds.Parameter('123', output=True)"),
(ctds.Parameter(unicode_('123'), output=False),
"ctds.Parameter('123')" if PY3 else "ctds.Parameter(u'123')"),
(ctds.Parameter(None), "ctds.Parameter(None)"),
(ctds.Parameter(ctds.SqlVarBinary(b'4321', size=10)),
"ctds.Parameter(ctds.SqlVarBinary(b'4321', size=10))" if PY3 else
"ctds.Parameter(ctds.SqlVarBinary('4321', size=10))")):
self.assertEqual(repr(parameter), expected)
def test_dict_outputs(self):
with self.connect() as connection:
with connection.cursor() as cursor:
sproc = self.test_dict.__name__
with self.stored_procedure(
cursor, sproc, '''
@pBigInt BIGINT OUTPUT,
@pVarChar VARCHAR(32) OUTPUT,
@pVarBinary VARBINARY(32) OUTPUT,
@pDateTime DATETIME
AS
SET @pBigInt = @pBigInt * 2;
SET @pVarBinary = CONVERT(VARBINARY(32), @pVarChar);
SET @pVarChar = CONVERT(VARCHAR(32), @pDateTime, 120);
'''):
types = [str]
if not PY3: # pragma: nocover
types.append(unicode_)
for type_ in types:
inputs = {
type_('@pBigInt'):
ctds.Parameter(12345, output=True),
type_('@pVarChar'):
ctds.Parameter(
unicode_('hello world, how\'s it going! '),
output=True),
type_('@pVarBinary'):
ctds.Parameter(ctds.SqlVarBinary(None, size=32),
output=True),
type_('@pDateTime'):
datetime(2011, 11, 5, 12, 12, 12)
}
outputs = cursor.callproc(sproc, inputs)
self.assertNotEqual(id(outputs[type_('@pBigInt')]),
id(inputs[type_('@pBigInt')]))
self.assertNotEqual(id(outputs[type_('@pVarChar')]),
id(inputs[type_('@pVarChar')]))
self.assertNotEqual(id(outputs[type_('@pVarBinary')]),
id(inputs[type_('@pVarBinary')]))
self.assertEqual(id(outputs[type_('@pDateTime')]),
id(inputs[type_('@pDateTime')]))
self.assertEqual(outputs[type_('@pBigInt')],
inputs[type_('@pBigInt')].value * 2)
self.assertEqual(
outputs[type_('@pVarChar')], inputs[type_(
'@pDateTime')].strftime('%Y-%m-%d %H:%M:%S'))
self.assertEqual(
outputs[type_('@pVarBinary')],
bytes(inputs[type_('@pVarChar')].value.encode(
'utf-8')))
def test_wrapper(self):
with self.connect() as connection:
with connection.cursor() as cursor:
for value in (b'1234', unicode_('1234'), None):
wrapper = ctds.SqlVarBinary(value)
self.assertEqual(id(value), id(wrapper.value))
self.assertEqual(wrapper.size,
len(value) if value is not None else 1)
row = self.parameter_type(cursor, wrapper)
self.assertEqual(
row.Type, 'varbinary' if value is not None else None)
expected = wrapper.value.encode() if isinstance(
value, unicode_) else wrapper.value
self.assertEqual(row.Value, expected)
self.assertEqual(row.MaxLength,
len(value) if value is not None else None)
def test_binary_inputoutput(self):
with self.connect() as connection:
with connection.cursor() as cursor:
sproc = self.test_binary_inputoutput.__name__
with self.stored_procedure(
cursor, sproc, '''
@pInOut VARBINARY(16) OUTPUT
AS
SET @pInOut = @pInOut + @pInOut;
'''):
value = b'\x01\x02\x03\x04'
inputs = (ctds.Parameter(ctds.SqlVarBinary(value, size=16),
output=True), )
outputs = cursor.callproc(sproc, inputs)
self.assertEqual(outputs, (value + value, ))
self.assertRaises(ctds.InterfaceError, cursor.fetchone)
def test_size(self):
with self.connect() as connection:
with connection.cursor() as cursor:
for value, size in (
(b'1234', 14),
(b'1234', 1),
(b'*', 5000),
(b'*' * 5000, 5000),
(None, 14),
):
wrapper = ctds.SqlVarBinary(value, size=size)
self.assertEqual(id(value), id(wrapper.value))
self.assertEqual(wrapper.size, size)
row = self.parameter_type(cursor, wrapper)
self.assertEqual(
row.Type, 'varbinary' if value is not None else None)
expected = wrapper.value.encode() if isinstance(
value, unicode_) else wrapper.value
self.assertEqual(
row.Value,
expected[:size] if value is not None else None)
self.assertEqual(row.MaxLength,
size if value is not None else None)
