1

I'm trying to replace the old mssql connection with pdo on the MuOnline CMS website. This cms uses sql-server database of the game in this database has a warehouse table with items column which is varbinary (7680), it is different according to the version of the game.

//varbinary lenght
if($mvcore['db_season'] >= '9'){
    $cvbins = '7680'; 
} elseif($mvcore['db_season'] == '1') {
    $cvbins = '1200'; 
} else {
    $cvbins = '3840'; 
};

Тhis is my attempt to retrieve the information from the column.

$mvcorex->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
$sth = $mvcorex->prepare("SELECT cast(items AS varbinary(".$cvbins.")) as [items] FROM [warehouse] WHERE [accountid]=:name");
$sth->bindParam(":name", $passnums);
$sth->execute();
$sqll = $sth->errorInfo();
$charItems = $sth->fetch(PDO::FETCH_ASSOC);

The problem here is that if I choose $mvcore['db_season'] to be equal to 9 I get a Fatal error: Invalid sql_display_size in

George
  • 11
  • 1
  • Can you work with it as a string instead of varbinary? I know historically the PDO driver hasn't played nice with varbinary data. – Xedni Mar 20 '18 at 16:16
  • Hello, I got the same error as `cast(items AS BINARY(".$cvbins."))` – George Mar 20 '18 at 16:32
  • I use SQL server 2017 – George Mar 20 '18 at 16:34
  • my php is 7.1.15 – George Mar 20 '18 at 16:40
  • Please try [`as varbinary('max')`](https://stackoverflow.com/questions/40889137/php-sqlsrv-library-handling-of-varbinary-data-limits) and [`as varbinary`](https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql#syntax) without the length because it is optional. – user10089632 Mar 20 '18 at 17:14
  • Works with all lengths `varbinary(max), varbinary(3840)`...etc but has a problem with 7680. – George Mar 20 '18 at 17:27
  • I do not think it shows me all the information in items if i use varbinary (max) or other than 7860. if i use varbinary(max) output start with 017 but in items colummn start with 0x017 – George Mar 20 '18 at 17:36
  • The leading 0x is not part of the value it is an indication that the value is binary and not string. Double check if you're using the right syntax. – user10089632 Mar 20 '18 at 17:43
  • Sorry for spam. If i hang them like strings with strlen item data returns 15362 length but fetch aoutput returns only 2400 length if i use varbinary(max) – George Mar 20 '18 at 17:48
  • I apologize I was trying to help but I don't hav SQLserver nor am I and expert with it, but the result that you just gave me is contradicted – user10089632 Mar 20 '18 at 17:53
  • and it's endorse further that the first is interpreted as binary and the second as text – user10089632 Mar 20 '18 at 17:54
  • I appreciate your help. I just can not solve this problem for quite some time – George Mar 20 '18 at 18:07
  • Try to compare the lengths of both value but this time using the SQL `DATALENGTH()` function. – user10089632 Mar 20 '18 at 18:21
  • I try to change driver now i get this `Array ( [0] => Array ( [items] => x�N���������..........etc ) )` – George Mar 20 '18 at 18:39
  • That's normal you are trying to display a `BINARY`. Now what is the question you said that varbinary('max') worked, and it's not probable that it will change your data – user10089632 Mar 20 '18 at 18:45
  • Now I see, use this query to get both lengths and compare them : `SELECT DATALENGTH(cast(items AS varbinary('max'))) as [length 1], DATALENGTH(cast(items AS varbinary(3840))) as [length 2] FROM [warehouse] WHERE [accountid]=:name` – user10089632 Mar 20 '18 at 19:01

0 Answers0