PHP is truncating MSSQL Blob data (4096b), even after setting INI values. Am I missing one?
Instead of setting odbc.defaultlrl
to 0
, try setting it to an actual value instead:
ini_set("odbc.defaultlrl", "100K");
Why are all of my images getting cut off when I pull the binary out of a SQL Server DB?
I finally found the answer. The data coming back to PHP via PDO was in fact being truncated at 64k, thus causing issues.
Alex helped lead me on the right path by suggesting setting TEXTSIZE
to -1
. The rookie mistake that I made was that I did SET TEXTSIZE -1
from Microsoft SSMS, assuming that it would be set globally for all connections, which was not the case. It only set it for the SSMS connection, thus the problem.
However, when I finally did the following in PDO in PHP, that is, set TEXTSIZE
to -1
with the PDO connection and then make the query from PDO, I was able to set TEXTSIZE
for the PDO connection and then get back all the data:
doSqlServerQuery("SET TEXTSIZE -1;");
$results = doSqlServerQuery("SELECT binary-data-image-column AS data
FROM table-name
WHERE key = 95948578934578934;");
//Function definition
function doSqlServerQuery($query, $dbh = null) {
if (!isset($dbh)) {
global $dbh;
}
$stmt = $dbh->prepare($query);
if ($stmt) {
$stmt->execute();
$results = [];
while ($result = $stmt->fetch(PDO::FETCH_ASSOC)) {
$results[] = $result;
}
return $results;
} else {
pO($dbh->errorInfo());
}
}
Issue while handling PNG image from MSSQL database
After quite a while i've found the problema causing the image beeing truncated, in my initial tests i've used a simple
ini_set('mssql.textlimit', '5242880');
ini_set('mssql.textsize', '5242880');
which did not make any impact, thus transfered data was truncated after 60KB... after some research i've found that manually setting
mssql_query("SET TEXTSIZE 5242880");
before the actual mssql_fetch_array query, solved my issue!
thus instructing MSSQL that the TEXTLIMIT & TEXTSIZE will be overwritten insures a complete data transfer between sql and php
Thank you all for your wisdom!
Change max size of odbc_exec() return
According to http://php.net/manual/en/odbc.configuration.php the only variable with 4096 is "odbc.defaultlrl". According to https://stackoverflow.com/a/4500598/4934937, ini_set("odbc.defaultlrl", "100K");
should do the trick.
Trying to use SQL to reconstruct files from an Opererp 7 postgres db bytea (binary data)
Supposedly the pdf is encoded by openerp.
The issue was resolved by using a bit of python code, supplied by Jaime Vasquez on the odoo forum pages.
Answer is here
Related Topics
Restoring a Database from .Bak File on Another Machine
If Exists Statement in SQL to Linq
Mod' Is Not a Recognized Built-In Function Name
How to Send a Query Result in CSV Format
How to Create a SQLite3 Database File Using a SQL Command File
How to Perform a SQL 'Not In' Query Faster
Why Does Connect by Level on a Table Return Extra Rows
Why Is My Left Join Not Returning Nulls
SQL Query of Multi-Member File on As400
How to Get the Employees with Their Managers
What Is The T-Sql Equivalent of MySQL Syntax Limit X, Y
How to Use Group by Based on a Case Statement in Oracle
Transpose/Pivot Distinct Row Attribute as Column and Group Another Attribute