String truncation when binding varchar(max)

[david-puglielli]

When binding output parameters as varchar(max) or nvarchar(max), a string truncation error message may result. This does not happen if the argument is not 'max'. This problem is seen on both Windows and Linux.

<?php 
$server = "myserver";
$connectionInfo = array( "Database"=>"$databaseName", "UID"=>"$uid", "PWD"=>"$pwd");

$tableName = "datatypes";
$columnNames = array( "c1","c2" );

$conn = sqlsrv_connect($server, $connectionInfo);

for ($k = 1; $k <= 6; $k++)
{
    $sqlType = GetSqlType($k);
    $dataType = "[$columnNames[0]] int, [$columnNames[1]] $sqlType";
    
    $sql = "CREATE TABLE [$tableName] ($dataType)";
    $stmt1 = sqlsrv_query($conn, $sql);
    
    $data = "LongStringForTesting";
    
    $dataValues = array($k, $data);
    
    $tsql = "INSERT INTO [$tableName] ($columnNames[0], $columnNames[1]) VALUES (?, ?)";    
    $stmt2 = sqlsrv_query( $conn, $tsql, $dataValues );

    ExecProc($conn, $tableName, $columnNames, $k, $data, $sqlType);
    
    $stmt3 = sqlsrv_query($conn, "DROP TABLE [$tableName]");
    sqlsrv_free_stmt($stmt1);
    sqlsrv_free_stmt($stmt2);
    sqlsrv_free_stmt($stmt3);
}

sqlsrv_close($conn);


function ExecProc($conn, $tableName, $columnNames, $k, $data, $sqlType)
{
    $phpDriverType = GetDriverType($k, strlen($data));
    
    $spArgs = "@p1 int, @p2 $sqlType OUTPUT";
    $spCode = "SET @p2 = ( SELECT c2 FROM $tableName WHERE c1 = @p1 )";
    $procName = "testBindOutSp";

    $stmt1 = sqlsrv_query($conn, "CREATE PROC [$procName] ($spArgs) AS BEGIN $spCode END");
    sqlsrv_free_stmt($stmt1);
    
    $callArgs = "?, ?";
    
    // Data to initialize $callResult variable. This variable should be different than inserted data in the table
    $initData = "ShortString";
    $callResult = $initData;
    
    $params = array( array( $k, SQLSRV_PARAM_IN ), 
                     array( &$callResult, SQLSRV_PARAM_OUT, null, $phpDriverType ));

    echo "\nData Type: ".$sqlType."\n";
    echo "Table data:          ".$data."\nInitial data:        ".$initData."\nInitial call result: ".$callResult."\n";
    
    $stmt2 = sqlsrv_query($conn, "{ CALL [$procName] ($callArgs)}", $params);
    
    // $callResult should be updated to the value in the table
    echo "New call result:     ".$callResult."\n\n";
    
    if($stmt2 === false)
    {
        var_dump( sqlsrv_errors());
    }
    else
    {
        sqlsrv_free_stmt($stmt2);
    }
    
    $stmt3 = sqlsrv_query($conn, "DROP PROC [$procName]");
    sqlsrv_free_stmt($stmt3);
}

function GetSqlType($k)
{
    switch ($k)
    {
        case 1:  return ("char(512)");
        case 2:  return ("varchar(512)");
        case 3:  return ("varchar(max)");
        case 4:  return ("nchar(512)");
        case 5:  return ("nvarchar(512)");
        case 6:  return ("nvarchar(max)");
        default: break;
    }
    return ("udt");
}

function GetDriverType($k, $dataSize)
{
    switch ($k)
    {
        case 1:  return (SQLSRV_SQLTYPE_CHAR($dataSize));
        case 2:  return (SQLSRV_SQLTYPE_VARCHAR($dataSize));
        case 3:  return (SQLSRV_SQLTYPE_VARCHAR('max'));
        case 4:  return (SQLSRV_SQLTYPE_NCHAR($dataSize));
        case 5:  return (SQLSRV_SQLTYPE_NVARCHAR($dataSize));
        case 6:  return (SQLSRV_SQLTYPE_NVARCHAR('max'));
        default: break;
    }
    return (SQLSRV_SQLTYPE_UDT);
}
?>

The expected output is

Data Type: char(512)
Table data:          LongStringForTesting
Initial data:        ShortString
Initial call result: ShortString
New call result:     LongStringForTesting


Data Type: varchar(512)
Table data:          LongStringForTesting
Initial data:        ShortString
Initial call result: ShortString
New call result:     LongStringForTesting


Data Type: varchar(max)
Table data:          LongStringForTesting
Initial data:        ShortString
Initial call result: ShortString
New call result:     LongStringForTesting

Data Type: nchar(512)
Table data:          LongStringForTesting
Initial data:        ShortString
Initial call result: ShortString
New call result:     LongStringForTesting


Data Type: nvarchar(512)
Table data:          LongStringForTesting
Initial data:        ShortString
Initial call result: ShortString
New call result:     LongStringForTesting


Data Type: nvarchar(max)
Table data:          LongStringForTesting
Initial data:        ShortString
Initial call result: ShortString
New call result:     LongStringForTesting

The actual output:

Data Type: char(512)
Table data:          LongStringForTesting
Initial data:        ShortString
Initial call result: ShortString
New call result:     LongStringForTesting


Data Type: varchar(512)
Table data:          LongStringForTesting
Initial data:        ShortString
Initial call result: ShortString
New call result:     LongStringForTesting


Data Type: varchar(max)
Table data:          LongStringForTesting
Initial data:        ShortString
Initial call result: ShortString
New call result:     LongStringF

array(1) {
  [0]=>
  array(6) {
    [0]=>
    string(5) "01004"
    ["SQLSTATE"]=>
    string(5) "01004"
    [1]=>
    int(0)
    ["code"]=>
    int(0)
    [2]=>
    string(71) "[Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation"
    ["message"]=>
    string(71) "[Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation"
  }
}

Data Type: nchar(512)
Table data:          LongStringForTesting
Initial data:        ShortString
Initial call result: ShortString
New call result:     LongStringForTesting


Data Type: nvarchar(512)
Table data:          LongStringForTesting
Initial data:        ShortString
Initial call result: ShortString
New call result:     LongStringForTesting


Data Type: nvarchar(max)
Table data:          LongStringForTesting
Initial data:        ShortString
Initial call result: ShortString
New call result:     LongStringF

array(1) {
  [0]=>
  array(6) {
    [0]=>
    string(5) "01004"
    ["SQLSTATE"]=>
    string(5) "01004"
    [1]=>
    int(0)
    ["code"]=>
    int(0)
    [2]=>
    string(71) "[Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation"
    ["message"]=>
    string(71) "[Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation"
  }
}

[yitam]

Using a similar repro script, with text type the script failed with an ODBC error as follows:

[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid parameter 2 (''): Data type 0x23 is a deprecated large object, or LOB, but is marked as output parameter. Deprecated types are not supported as output parameters. Use current large object types instead.

Similar case with ntext type

To repro, just use the followings for the above scenario:

function GetSqlType($k)
{
    switch ($k)
    {
        case 1:  return ("text");
        case 2:  return ("ntext");
        default: break;
    }
    return ("udt");
}

function GetDriverType($k, $dataSize)
{
    switch ($k)
    {
        case 1:  return (SQLSRV_SQLTYPE_TEXT);
        case 2:  return (SQLSRV_SQLTYPE_NTEXT);

        default: break;
    }
    return (SQLSRV_SQLTYPE_UDT);
}

[Hadis-Knj]

@yitam does this issue sill exist?

[shemi]

There is a solution to this problem?
There is a workaround?

[yitam]

Hello @shemi
There are actually two problems that are somewhat related, so which one did you refer to? If it's the original issue, it has been resolved. If you currently experience a similar problem, please create a new issue with your repro steps / scripts / env and link your new issue with this one. Thanks!