Sybase数据库long binary图片数据PHP读取长度异常问题求助

我折腾了好几天都找不到问题根源。我有一个Sybase表，其中用long binary字段存储JPG图片。用指定SQL查询提取图片时，PHP.ini的memory_limit已经设为4096M。直接在数据库执行查询，ImageData的长度是38924，但通过PHP的ODBC执行查询后，获取到的base64编码后的img字段长度只有4096。相关SQL和PHP代码如下：

查询图片的SQL语句 #

SELECT TOP 1 base64_encode(ImageData) as img, IL.Description As alt
     FROM ProductImage PI
    JOIN ImageLibrary IL
    ON PI.ImageId = IL.ImageId
    WHERE PI.ProductCode = '3894'
ORDER BY PI.ImageOrder 

查询ImageData长度的SQL语句 #

SELECT length(ImageData) as img, IL.Description As alt
     FROM ProductImage PI
    JOIN ImageLibrary IL
    ON PI.ImageId = IL.ImageId
    WHERE PI.ProductCode = '3894'
ORDER BY PI.ImageOrder

PHP代码 #

<?php

    /// GET THE IMAGE FROM IMAGE LIBRARY
    
    $conn = odbc_connect($connect_string,'','');
    $Image='';
    $Alt ='';
    $query='';  
    $query = "SELECT TOP 1 base64_encode(ImageData) as img, IL.Description As alt";
    $query .= " FROM ProductImage PI";
    $query .= " JOIN ImageLibrary IL ";
    $query .= " ON PI.ImageId = IL.ImageId";
    $query .= " WHERE PI.ProductCode = '3894'";
    $query .= " ORDER BY PI.ImageOrder";
    //var_dump ($query);
    $res = odbc_exec($conn,$query)or die("Whoops! I could not run this query " . $query);
    $num_rows = odbc_num_rows($res) & 0xffffffff;     
    $rows = array();
   
    while (odbc_fetch_row($res)) {

      
   $len = strlen(odbc_result($res,"img"));
   

   If ($len > 0){
      $image = odbc_result($res,"img");
      $alt = odbc_result($res,'alt');
    } else {
      $image='No Image binary goes here';
      $alt = 'No Image saved sorry';
     }
  }
 
?>

解决方法 #

1. 避免在SQL中做base64编码，改用PHP处理

数据库端的base64编码结果可能被ODBC驱动截断，建议直接获取原始二进制数据，在PHP中进行编码：

修改后的SQL：

SELECT TOP 1 ImageData, IL.Description As alt
FROM ProductImage PI
JOIN ImageLibrary IL
ON PI.ImageId = IL.ImageId
WHERE PI.ProductCode = '3894'
ORDER BY PI.ImageOrder

修改后的PHP代码：

<?php
/// GET THE IMAGE FROM IMAGE LIBRARY
$conn = odbc_connect($connect_string,'','');
$image='';
$alt ='';
$query = "SELECT TOP 1 ImageData, IL.Description As alt";
$query .= " FROM ProductImage PI";
$query .= " JOIN ImageLibrary IL ";
$query .= " ON PI.ImageId = IL.ImageId";
$query .= " WHERE PI.ProductCode = '3894'";
$query .= " ORDER BY PI.ImageOrder";

$res = odbc_exec($conn,$query)or die("Whoops! I could not run this query " . $query);

// 配置ODBC处理长字段
odbc_binmode($res, ODBC_BINMODE_PASSTHRU);
odbc_longreadlen($res, 0); // 0表示无长度限制

while (odbc_fetch_row($res)) {
    $binaryData = odbc_result($res, "ImageData");
    if ($binaryData !== false && strlen($binaryData) > 0) {
        $image = base64_encode($binaryData);
        $alt = odbc_result($res,'alt');
    } else {
        $image='No Image binary goes here';
        $alt = 'No Image saved sorry';
    }
}
?>

2. 调整ODBC驱动配置

检查Sybase ODBC驱动的设置，找到“最大字符串长度”相关参数，将其调整为大于base64编码后的预期长度（38924字节的二进制数据base64编码后约为51900字节）。

3. 确认PHP ODBC扩展的长字段处理

确保PHP的ODBC扩展正确处理长字段，使用odbc_binmode设置二进制数据传递模式，odbc_longreadlen设置长字段的读取长度限制（设为0表示不限制），这两个函数需要在odbc_exec之后、odbc_fetch_row之前调用。

内容的提问来源于stack exchange，提问作者AmbientGuitar