Work Item / Issue Reference

AB#38480

GitHub Issue: #<ISSUE_NUMBER>

Summary

This pull request improves how binary data (Python bytes and bytearray) is handled in the MSSQL Python driver, especially for edge cases like empty values, mixed types, and large binaries. It also significantly expands the test suite to cover these scenarios and documents current driver limitations regarding parameter and fetch buffer sizes.

Binary Data Handling Improvements

• Updated _map_sql_type in cursor.py to always use VARBINARY for Python bytes/bytearray, avoiding storage waste and ensuring correct handling of variable-length data. This removes previous logic that sometimes used fixed-length BINARY and simplifies type mapping.
• Improved sample value selection in _select_best_sample_value to correctly handle columns that contain only binary types (bytes or bytearray).

Test Suite Enhancements for Binary Data

• Fixed and clarified the test_longvarbinary test to expect the correct number of rows and removed assumptions about zero-padding in returned binary data.
• Added new tests covering edge cases:
  • Executemany with binary data and empty byte arrays, ensuring correct insertion and retrieval of empty, regular, and null binary values.
  • Binary data over 8000 bytes, documenting driver limitations (8192 bytes for parameters, 4096 bytes for fetch buffer) and verifying correct error handling and data retrieval for supported sizes.
  • All empty binaries: verifies that multiple empty binary rows are handled and returned as zero-length bytes.
  • Mixing bytes and bytearray types in the same column, confirming consistent storage and retrieval as bytes.
  • Binary columns with mostly small/empty values and one large value, ensuring the large value is handled within driver limits.
  • Table with only NULL and empty binary values, verifying clear distinction between NULL and empty values and correct query results.