Executemany corrupts string data on Unix/macOS systems

[bewithgaurav]

Describe the bug

The executemany method corrupts string data on Unix and macOS systems, causing strings to be either truncated or returned as garbage data. This occurs due to encoding mismatch in batch parameter binding where UTF-32 strings are directly copied to UTF-16 buffers without proper conversion.

To reproduce

import mssql_python as sql

conn = sql.connect("your_connection_string")
cursor = conn.cursor()

cursor.execute("CREATE TABLE #test_strings (id INT, text NVARCHAR(100))")

# Test data with various string types
test_data = [
    (1, "Hello World"),
    (2, "Test String"),
    (3, "Unicode: café"),
    (4, "")  # Empty string
]

# Use executemany - this corrupts data on Unix/macOS
cursor.executemany("INSERT INTO #test_strings VALUES (?, ?)", test_data)

# Retrieve and check for corruption
cursor.execute("SELECT id, text FROM #test_strings ORDER BY id")
results = cursor.fetchall()

for expected, actual in zip(test_data, results):
    print(f"Expected: {expected[1]}")
    print(f"Actual: {actual[1]}")
    print(f"Match: {expected[1] == actual[1]}")
    print("---")

Expected behavior

Strings should be stored and retrieved exactly as inserted, without any corruption or truncation. The executemany method should handle string encoding consistently with the execute method.

Further technical details

Python version: 3.10+
SQL Server version: SQL Server 2019/2022
Operating system: Linux, macOS (Windows works correctly)

Additional context

• Single parameter execute method works correctly on all platforms
• The issue only affects executemany batch operations on Unix-based systems
• Corruption occurs during the parameter binding phase due to UTF-32 to UTF-16 conversion issues
• This affects any application using batch string operations on non-Windows platforms

[bewithgaurav]

Closing since we've released this in 0.10.0