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_output_with_resultset(self):
with self.connect() as connection:
with connection.cursor() as cursor:
sproc = self.test_output_with_resultset.__name__
with self.stored_procedure(
cursor, sproc, '''
@pOut INT OUTPUT
AS
SET @pOut = 5;
SELECT 10;
'''):
with warnings.catch_warnings(record=True) as warns:
cursor.callproc(
sproc,
(ctds.Parameter(ctds.SqlInt(0), output=True), ))
cursor.callproc(sproc, {
'@pOut':
ctds.Parameter(ctds.SqlInt(0), output=True)
})
self.assertEqual(len(warns), 2)
self.assertEqual([
str(warn.message) for warn in warns
], [
'output parameters are not supported with result sets'
] * len(warns))
self.assertEqual(warns[0].category, ctds.Warning)
def test_sql_conversion_warning(self):
with self.connect(ansi_defaults=False) as connection:
with connection.cursor() as cursor:
sproc = self.test_sql_conversion_warning.__name__
with self.stored_procedure(
cursor, sproc, '''
@pInt TINYINT OUTPUT,
@pBigInt BIGINT OUTPUT
AS
SELECT @pInt = @pInt * 255;
SELECT @pBigInt = @pBigInt - 1000;
'''):
with warnings.catch_warnings(record=True) as warns:
inputs = (
ctds.Parameter(10, output=True),
ctds.Parameter(1234, output=True),
)
outputs = cursor.callproc(sproc, inputs)
self.assertEqual(outputs,
(None, inputs[1].value - 1000))
self.assertEqual(len(warns), 1)
msg = 'Arithmetic overflow occurred.'
self.assertEqual([str(warn.message) for warn in warns],
[msg] * len(warns))
self.assertEqual(warns[0].category, ctds.Warning)
def test_parameter(self):
param1 = ctds.Parameter(b'123', output=True)
self.assertEqual(param1.value, b'123')
self.assertTrue(isinstance(param1, ctds.Parameter))
param2 = ctds.Parameter(b'123')
self.assertEqual(param1.value, b'123')
self.assertEqual(type(param1), type(param2))
self.assertTrue(isinstance(param2, ctds.Parameter))
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_decimal_outofrange(self):
with self.connect() as connection:
with connection.cursor() as cursor:
sproc = self.test_decimal_outofrange.__name__
with self.stored_procedure(
cursor, sproc, '''
@pDecimal DECIMAL(7,3),
@pDecimalOut VARCHAR(MAX) OUTPUT
AS
SET @pDecimalOut = CONVERT(VARCHAR(MAX), @pDecimal);
'''):
for value in (
10**38,
-10**38,
):
inputs = (
Decimal(value),
ctds.Parameter(unicode_('*' * 256), output=True),
)
try:
cursor.callproc(sproc, inputs)
except ctds.DataError as ex:
self.assertEqual(
str(ex),
"Decimal('{0}') out of range".format(value))
else:
self.fail('.callproc() did not fail as expected'
) # pragma: nocover
def test_varchar_null(self):
with self.connect() as connection:
with connection.cursor() as cursor:
sproc = self.test_varchar_null.__name__
with self.stored_procedure(
cursor, sproc, '''
@pVarChar VARCHAR(256),
@pIsNullOut BIT OUTPUT
AS
IF @pVarChar IS NULL
BEGIN
SET @pIsNullOut = 1;
END
ELSE
BEGIN
SET @pIsNullOut = 0;
END
'''):
for value in (None, '', '0', 'one'):
inputs = (
value,
ctds.Parameter(True, output=True),
)
outputs = cursor.callproc(sproc, inputs)
# $future: fix this once supported by FreeTDS
# Currently FreeTDS (really the db-lib API) will
# turn the empty string to NULL
self.assertEqual(outputs[1], inputs[0] is None
or inputs[0] == '')
def test_decimal_output(self):
with self.connect() as connection:
with connection.cursor() as cursor:
sproc = self.test_decimal_output.__name__
with self.stored_procedure(
cursor, sproc, '''
@pString VARCHAR(MAX),
@pDecimalOut DECIMAL(15,5) OUTPUT
AS
SET @pDecimalOut = CONVERT(DECIMAL(15,5), @pString);
'''):
for value in ('1230.456', '123456789.1234', '123',
'1234567890.123456789123456789', None):
inputs = (
value,
ctds.Parameter(ctds.SqlDecimal(value,
precision=15,
scale=5),
output=True),
)
outputs = cursor.callproc(sproc, inputs)
self.assertEqual(id(outputs[0]), id(inputs[0]))
if outputs[0] is not None:
self.assertEqual(
Decimal(outputs[0]).quantize(
Decimal('.00001')), outputs[1])
else:
self.assertEqual(None, outputs[1])
def test_format_named(self):
with self.connect(paramstyle='named') as connection:
with connection.cursor() as cursor:
args = {
unicode_('none'):
None,
'int':
-1234567890,
unicode_('bigint'):
2**45,
'bytes':
ctds.Parameter(b'1234'),
'byte_array':
bytearray('1234', 'ascii'),
'string':
unicode_(
b'hello \'world\' ' +
(b'\xe3\x83\x9b' if self.nchars_supported else b''),
encoding='utf-8'),
'datetime':
datetime(2001, 1, 1, 12, 13, 14, 150 * 1000),
'decimal':
Decimal('123.4567890'),
'money':
Decimal('1000000.4532')
}
query = cursor.executemany(
'''
SELECT
:none AS none,
:int AS int,
CONVERT(BIGINT, :bigint) AS bigint,
:bytes AS bytes,
:byte_array AS bytearray,
:string AS string,
:string AS string_again,
CONVERT(DATETIME, :datetime) AS datetime,
:decimal AS decimal,
CONVERT(MONEY, :money) AS money
''', (args, args))
self.assertEqual(None, query)
while cursor.description:
self.assertEqual([tuple(row) for row in cursor.fetchall()],
[(
args['none'],
args['int'],
args['bigint'],
args['bytes'].value,
bytes(args['byte_array']),
args['string'],
args['string'],
args['datetime'],
args['decimal'],
self.round_money(args['money']),
)])
cursor.nextset()
def test_int(self):
with self.connect() as connection:
with connection.cursor() as cursor:
sproc = self.test_int.__name__
with self.stored_procedure(
cursor, sproc, '''
@pTinyInt TINYINT,
@pSmallInt SMALLINT,
@pInt INT,
@pBigInt BIGINT,
@pTinyIntOut TINYINT OUTPUT,
@pSmallIntOut SMALLINT OUTPUT,
@pIntOut INT OUTPUT,
@pBigIntOut BIGINT OUTPUT
AS
SELECT @pTinyIntOut = @pTinyInt + 1;
SELECT @pSmallIntOut = -1 * @pSmallInt;
SELECT @pIntOut = -1 * @pInt;
SELECT @pBigIntOut = -1 * @pBigInt;
'''):
inputs = (
1,
2**8,
2**15,
2**31,
ctds.Parameter(ctds.SqlTinyInt(None), output=True),
ctds.Parameter(ctds.SqlSmallInt(None), output=True),
ctds.Parameter(ctds.SqlInt(None), output=True),
ctds.Parameter(ctds.SqlBigInt(None), output=True),
)
outputs = cursor.callproc(sproc, inputs)
self.assertRaises(ctds.InterfaceError, cursor.fetchone)
self.assertEqual(id(outputs[0]), id(inputs[0]))
self.assertEqual(id(outputs[1]), id(inputs[1]))
self.assertEqual(id(outputs[2]), id(inputs[2]))
self.assertEqual(id(outputs[3]), id(inputs[3]))
self.assertEqual(outputs[4:], (
outputs[0] + 1,
outputs[1] * -1,
outputs[2] * -1,
outputs[3] * -1,
))
def test_format_numeric(self):
with self.connect() as connection:
with connection.cursor() as cursor:
args = (
None,
-1234567890,
2 ** 45,
ctds.Parameter(b'1234'),
bytearray('1234', 'ascii'),
unicode_(
b'hello \'world\' ' + (b'\xe3\x83\x9b' if self.nchars_supported else b''),
encoding='utf-8'
),
datetime(2001, 1, 1, 12, 13, 14, 150 * 1000),
Decimal('123.4567890'),
Decimal('1000000.4532')
)
query = cursor.execute(
'''
SELECT
:0 AS none,
:1 AS int,
CONVERT(BIGINT, :2) AS bigint,
:3 AS bytes,
:4 AS bytearray,
:5 AS string,
:5 AS string_again,
CONVERT(DATETIME, :6) AS datetime,
:7 AS decimal,
CONVERT(MONEY, :8) AS money
''',
args
)
self.assertEqual(None, query)
self.assertEqual(
tuple(cursor.fetchone()),
(
args[0],
args[1],
args[2],
args[3].value,
bytes(args[4]),
args[5],
args[5],
args[6],
args[7],
self.round_money(args[8]),
)
)
def test_sql_conversion_error(self):
with self.connect(ansi_defaults=True) as connection:
with connection.cursor() as cursor:
sproc = self.test_sql_conversion_warning.__name__
with self.stored_procedure(
cursor, sproc, '''
@pInt TINYINT OUTPUT,
@pBigInt BIGINT OUTPUT
AS
SELECT @pInt = @pInt * 255;
SELECT @pBigInt = @pBigInt - 1000;
'''):
inputs = (
ctds.Parameter(10, output=True),
ctds.Parameter(1234, output=True),
)
try:
cursor.callproc(sproc, inputs)
except ctds.ProgrammingError as ex:
self.assertEqual(
str(ex),
'Arithmetic overflow error for data type tinyint, value = {0}.'
.format(inputs[0].value * 255))
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_decimal(self):
with self.connect() as connection:
with connection.cursor() as cursor:
sproc = self.test_decimal.__name__
with self.stored_procedure(
cursor, sproc, '''
@pDecimal DECIMAL(7,3),
@pDecimalOut VARCHAR(MAX) OUTPUT
AS
SET @pDecimalOut = CONVERT(VARCHAR(MAX), @pDecimal);
'''):
for value, string, truncation in (
(1000.0, '1000.000', False),
(-1000.0, '-1000.000', False),
(9999.999, '9999.999', True),
(-9999.999, '-9999.999', True),
(.999, '0.999', True),
(-.999, '-0.999', True),
(.001, '0.001', True),
(-.001, '-0.001', True),
(0, '0.000', False),
):
inputs = (
Decimal(value if PY27 else str(value)),
ctds.Parameter(unicode_('*' * 256), output=True),
)
with warnings.catch_warnings(record=True) as warns:
outputs = cursor.callproc(sproc, inputs)
if truncation and PY27:
self.assertEqual(len(warns), 1)
msg = "Decimal('{0}') exceeds SQL DECIMAL precision; truncating".format(
inputs[0])
self.assertEqual(
[str(warn.message) for warn in warns],
[msg] * len(warns))
self.assertEqual(warns[0].category,
ctds.Warning)
else:
self.assertEqual(len(warns), 0)
self.assertEqual(id(outputs[0]), id(inputs[0]))
self.assertEqual(string, outputs[1])
self.assertRaises(ctds.InterfaceError,
cursor.fetchone)
def test_string(self):
parameter = unicode_(b'what DB encoding is used? \xc2\xbd',
encoding='utf-8')
for index, param in enumerate((
parameter,
ctds.Parameter(parameter),
)):
with self.connect(autocommit=False) as connection:
try:
with connection.cursor() as cursor:
cursor.execute('''
CREATE TABLE {0}
(
String VARCHAR(1000) COLLATE SQL_Latin1_General_CP1_CI_AS
)
'''.format(self.test_string.__name__))
with warnings.catch_warnings(record=True) as warns:
connection.bulk_insert(self.test_string.__name__,
[(param, )])
# Python 3.4 and lower has an issue recording the same warning multiple times.
# See http://bugs.python.org/issue4180.
if index == 0 or PY35: # pragma: nobranch
self.assertEqual(len(warns), 1)
self.assertEqual([str(warn.message) for warn in warns],
[
'''\
Direct bulk insert of a Python str object may result in unexpected character \
encoding. It is recommended to explicitly encode Python str values for bulk \
insert.\
'''
] * len(warns))
self.assertEqual([warn.category for warn in warns],
[Warning] * len(warns))
with connection.cursor() as cursor:
cursor.execute('SELECT * FROM {0}'.format(
self.test_string.__name__))
self.assertEqual(
[tuple(row) for row in cursor.fetchall()],
[(parameter.encode('utf-8').decode('latin-1'), )])
finally:
connection.rollback()
def test_reuse(self):
with self.connect() as connection:
with connection.cursor() as cursor:
for value in (
None,
123456,
unicode_('hello world'),
b'some bytes',
):
for output in (True, False):
parameter = ctds.Parameter(value, output=output)
for _ in range(0, 2):
# Ignore warnings generated due to output parameters
# used with result sets.
with warnings.catch_warnings(record=True):
cursor.execute(
'''
SELECT :0
''', (parameter, ))
self.assertEqual(
[tuple(row) for row in cursor.fetchall()],
[(value, )])
def test_guid(self):
with self.connect() as connection:
with connection.cursor() as cursor:
sproc = self.test_guid.__name__
with self.stored_procedure(
cursor, sproc, '''
@pIn UNIQUEIDENTIFIER,
@pOut UNIQUEIDENTIFIER OUTPUT
AS
SET @pOut = @pIn;
'''):
for value in (
None,
uuid.uuid1(),
):
inputs = (value,
ctds.Parameter(uuid.uuid1(), output=True))
outputs = cursor.callproc(sproc, inputs)
# FreeTDS doesn't support passing raw GUIDs, so they are convereted to
# VARCHAR. On output, the type is also VARCHAR.
expected = uuid.UUID('{{{0}}}'.format(
outputs[1])) if value is not None else None
self.assertEqual(value, expected)
def _test__cmp__(self, __cmp__, expected, op):
CASES = (
(ctds.Parameter(b'1234'), ctds.Parameter(b'123')),
(ctds.Parameter(b'123'), ctds.Parameter(b'123')),
(ctds.Parameter(b'123'), ctds.Parameter(b'123', output=True)),
(ctds.Parameter(b'123'), ctds.Parameter(b'1234')),
(ctds.Parameter(b'123'), b'123'),
(ctds.Parameter(b'123'), ctds.Parameter(123)),
(ctds.Parameter(b'123'), unicode_('123')),
(ctds.Parameter(b'123'), ctds.SqlBinary(None)),
(ctds.Parameter(b'123'), 123),
(ctds.Parameter(b'123'), None),
)
for ix, args in enumerate(CASES):
operation = '[{0}]: {1} {2} {3}'.format(ix, repr(args[0]), op,
repr(args[1]))
if expected[ix] == TypeError:
try:
result = __cmp__(*args)
except TypeError as ex:
regex = (
r"'{0}' not supported between instances of '[^']+' and '[^']+'"
.format(op) if not PY3 or PY36 else
r'unorderable types: \S+ {0} \S+'.format(op))
self.assertTrue(re.match(regex, str(ex)), ex)
else:
self.fail('{0} did not fail as expected'.format(
operation)) # pragma: nocover
else:
self.assertEqual(__cmp__(*args), expected[ix], operation)
def test_nvarchar(self):
with self.connect() as connection:
with connection.cursor() as cursor:
sproc = self.test_nvarchar.__name__
with self.stored_procedure(
cursor, sproc, '''
@pVarChar NVARCHAR(256),
@pVarCharOut NVARCHAR(256) OUTPUT
AS
SET @pVarCharOut = @pVarChar;
'''):
format_ = (unichr_(191) + unicode_(' 8 ') + unichr_(247) +
unicode_(' 2 = 4 ? {0} {1} {2}'))
snowman = unichr_(9731)
# Python must be built with UCS4 support to test the large codepoints.
catface = (unichr_(128568) if self.UCS4_SUPPORTED else
self.UNICODE_REPLACEMENT)
flower = (unichr_(127802) if self.UCS4_SUPPORTED else
self.UNICODE_REPLACEMENT)
# Older versions of SQL server don't support passing codepoints outside
# of the server's code page. SQL Server defaults to latin-1, so assume
# non-latin-1 codepoints won't be supported.
if not self.use_sp_executesql: # pragma: nocover
catface = unicode_('?')
snowman = unicode_('?')
flower = unicode_('?')
inputs = (
format_.format(snowman, catface, flower),
ctds.Parameter(ctds.SqlVarChar(None, size=256),
output=True),
)
# If the connection supports UTF-16, unicode codepoints outside of the UCS-2
# range are supported and not replaced by ctds.
if self.use_utf16:
outputs = cursor.callproc(sproc, inputs)
self.assertEqual(inputs[0], outputs[1])
else: # pragma: nocover
# The catface is not representable in UCS-2, and therefore is replaced.
with warnings.catch_warnings(record=True) as warns:
outputs = cursor.callproc(sproc, inputs)
if ord(catface) > 2**16:
self.assertEqual(len(warns), 2)
msg = unicode_('Unicode codepoint U+{0:08X} is not representable in UCS-2; replaced with U+{1:04X}') # pylint: disable=line-too-long
self.assertEqual(
[str(warn.message) for warn in warns], [
msg.format(
ord(char),
ord(self.UNICODE_REPLACEMENT))
for char in (catface, flower)
])
self.assertEqual(warns[0].category,
ctds.Warning)
else:
self.assertEqual(len(warns),
0) # pragma: nocover
self.assertEqual(
format_.format(
snowman, self.UNICODE_REPLACEMENT
if self.use_sp_executesql else unicode_('?'),
self.UNICODE_REPLACEMENT
if self.use_sp_executesql else unicode_('?')),
outputs[1])
self.assertEqual(id(inputs[0]), id(outputs[0]))
self.assertNotEqual(id(inputs[1]), id(outputs[1]))
