3

I am creating a WebSite with PHP and HyperFileSQL, based on PDO & ODBC for the connection.
I am using the 7.1.9 release of PHP and working under WampServer.
But I have an issue when retrieving text data with more than 255 characters : the data is truncated and some random characters appear at the end.

I found some posts about this problem, like the following :

So, here is my code :
1) PHP : 

// Configuration de la connexion à la BDD --- Configuration of the connection to the database
$connexion['DSN']       = '{HFSQL}';
$connexion['IPServeur']     = 'XXX';
$connexion['Port']      = 'XXX';
$connexion['NomBDD']        = 'XXX';
$connexion['LoginServeur']  = 'XXX';
$connexion['MDPServeur']    = 'XXX';
$dsn = sprintf('odbc:DRIVER=%s;Server Name=%s;Server Port=%s;Database=%s;UID=%s;PWD=%s;', $connexion['DSN'], $connexion['IPServeur'], $connexion['Port'], $connexion['NomBDD'], $connexion['LoginServeur'], $connexion['MDPServeur']);

try{
    // Connexion à la BDD --- Connection to the database
    $bdd = new PDO($dsn, '', '', array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));

    // Initialisation de la requête --- SQL initialisation
    $sql        = 'SELECT CorpsHTML FROM T_ModeleEmail WHERE T_ModeleEmail.IDModeleEmail = :IDModeleEmail';
    $requete    = $this->getBDD()->prepare($sql);

    // Affectation des paramètres --- Bindings
    $tmpIDModeleEmail = 2;
    $requete->bindValue(':IDModeleEmail', $tmpIDModeleEmail, PDO::PARAM_INT);

    // Exécution de la requête --- SQL execution
    $requete->execute();

    // Récupération du 1er résultat --- Results parsing
    $donnees = $requete->fetch(PDO::FETCH_ASSOC);

    var_dump($donnees);

    // On libère la requête --- Unallocate the request
    $requete->closeCursor();

    // On vérifie qu'on a bien trouvé un modèle email --- email model found
    if(!empty($donnees)){
        // Renvoi de l'objet trouvé après hydratation --- Returns the object found
        return new ModeleEmail($donnees);
    }else{
        return null;
    }
}catch(PDOException $e){
    // Gestion des exceptions : arrête le script s'il y a une erreur --- Issue with PDO
    die('Impossible de se connecter aux données du site. [Code de l\'erreur : '. $e->getCode() . '] Détails : ' . utf8_encode($e -> getMessage()));
}

2) Here is the record that I try to display (8 byte), truncated at the 256th character when displayed : 

<html><head><title>Activation de votre compte</title></head><body>Ceci est un texte supérieur à 255 caractères, et qui n'est par conséquent par récupéré entièrement, bien que ce soit stocké dans un mémo texte ! Cela semble provenir d'un ancien bug de PDO, mais tous les forums ne parlent que de MSSQL Server, et aucun ne parle d'une solution pour PDO ODBC.</body></html>

3) And the result displayed : 

array(1) {      
    ["CorpsHTML"]=> string(370) 
    "<html><head><title>Activation de votre compte</title></head><body>Ceci est un texte sup�rieur � 255 caract�res, et qui n'est par cons�quent par r�cup�r� enti�rement, bien que ce soit stock� dans un m�mo texte ! Cela semble provenir d'un ancien bug de PDO,Zk���-o�����������xj������X7o��(0o������@o���"
}

For information, I found something during my debugs : when I add "bindColumn", the full text seems to be found... :
PHP ("bindColumn" alternative) : 

// Affectation des paramètres
$tmpIDModeleEmail = 2;
$requete->bindValue(':IDModeleEmail', $tmpIDModeleEmail, PDO::PARAM_INT);
$requete->bindColumn('CorpsHTML', $tmp);

// Exécution de la requête
$requete->execute();

// Récupération du 1er résultat
$donnees = $requete->fetch(PDO::FETCH_ASSOC);

var_dump($donnees);
echo 'TMP = '. $tmp;

And the result is... magic... : 

array(1) {
["CorpsHTML"]=>
string(115) " mais tous les forums ne parlent que de MSSQL Server, et aucun ne parle d'une solution pour PDO ODBC.</body></html>"
}

TMP = <html><head><title>Activation de votre compte</title></head><body>Ceci est un texte sup�rieur � 255 caract�res, et qui n'est par cons�quent par r�cup�r� enti�rement, bien que ce soit stock� dans un m�mo texte ! Cela semble provenir d'un ancien bug de PDO,Zk���,o�����������xj������X7o��(0o������@o���

Am I missing something ? Should I consider another option than PDO ? Or should I migrate my database to MSSQL Server ?

Much thanks in advance for your help !
If something is not clear, I will edit my message to clarify it.
Have a good day,
Mickaël

EDIT - SOLUTION Oh well, I found an alternative solution, dirty... but working :
--> Convert the datatype from "TEXT" to "VARCHAR(10000)"
--> Use ODBC instead of PDO & ODBC
--> Configure your DAOs to use ODBC or PDO & ODBC, depending if your table have TEXT fields (where you have to cast them into VARCHAR(XXXX) or not.
I hope that will help those in the same case.
Nevertheless, I'm still regarding to a cleaner solution :)

Mickael
  • 51
  • 4
  • What is the TEXTSIZE in php.ini ? – Saad Suri Apr 24 '18 at 12:47
  • @SaadSuri I have the following lines : [ODBC] TextSize = 2097152 [Pdo] TextSize = 2097152 I heard that with ODBC the textsize is implicit and so we don't even need to define it... :/ – Mickael Apr 24 '18 at 12:59
  • I just edited my post with an alternative solution. Dirty, but working ;) – Mickael Apr 25 '18 at 12:04
  • You should post this solution as an answer and select it as accepted so it will be helpful for another user. And yes it was in my mind to suggest you use ODBC :) – Saad Suri Apr 25 '18 at 12:15

1 Answers1

2

I found an alternative solution, dirty... but working :
--> Convert the datatype from "TEXT" to "VARCHAR(10000)"
--> Use ODBC instead of PDO & ODBC
--> Configure your DAOs to use ODBC or PDO & ODBC, depending if your table have TEXT fields (where you have to cast them into VARCHAR(XXXX) or not.

I hope that will help those in the same case.

Mickael
  • 51
  • 4