07/03/2018

PHP - SQLSERVER

Le but est de se connecter à une base de données SQL SERVER avec PHP

Installation

Could not find driver

sudo apt install php-odbc

SQLSTATE[01000] SQLDriverConnect: 0 [unixODBC][Driver Manager]Can't open lib 'FreeTDS' : file not found

sudo apt install freetds-dev unixodbc-dev tdsodbc

Ajouter le fichier /etc/odbcinst.ini

[freetds]
Description = MS SQL database access with Free TDS
#Driver      = /usr/lib/i386-linux-gnu/odbc/libtdsodbc.so
Driver      = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup       = /usr/lib/i386-linux-gnu/odbc/libtdsS.so
UsageCount  = 1

Utilisation

<?php

//$cn = new PDO("sqlsrv:server=127.0.0.1\SQLEXPRESS;Database=xxxx", "xxx", "xxxx");

$server = "10.135.3.120\LDSDPRD";
$port = "xxxx";
$user = "xxxx";
$password = "xxxx";

// $DB = new PDO('odbc:DRIVER={FreeTDS};Server='.$server.'; Port='.$port.';Database=LDSD; UID='.$user.';PWD='.$password, '','',array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION ));

try{
    //$db = new \PDO('dblib:host='.$server.';dbname=LDSD', $user, $password);
    //$db->setAttribute(\PDO::DBLIB_ATTR_STRINGIFY_UNIQUEIDENTIFIER, true); 
    $db = new PDO('odbc:DRIVER={FreeTDS};Server='.$server.'; Port='.$port.';Database=LDSD; UID='.$user.';PWD='.$password, '','',array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION ));
    //$cn = new PDO("odbc:Driver={FreeTDS};Server=xxxx\SQLEXPRESS;Database=LDSDPRD; Uid=xxxx;Pwd=xxxx;");
	//$db = new PDO("dblib:host=mssql;dbname=LDSD", "$user","$password");
 }catch(PDOException $e){
    echo $e->getmessage();
 }

$field = 'pm_guid';

$sql = "SELECT top 10 $field FROM LDSD.dbo.pm_process;";

$result = $db->query($sql);

foreach ($result as $row)
{
    //echo utf8_encode($row['pm_title']) . '<br>';
    echo $row[$field] . '<br>';
    
}

Other

Enregistrer une connexion : https://stackoverflow.com/questions/12031897/missing-libtdsodbc-so-in-freetds-dev-mssql-on-ubuntu

Probleme de GUID

L’UID stocké est sous la forme :

8B93D364-0870-4BCD-AD5F-FE56755D163F

et moi j’obtient la forme hexadecimale

64d3938b7008cd4bad5ffe56755d163f

Apres avoir realiser la requete je le remet en uid

// From binary to hexadecimal
function getId($string)
{
    return strtoupper(preg_replace('/([a-zA-Z0-9]{8})([a-zA-Z0-9]{4})([a-zA-Z0-9]{4})([a-zA-Z0-9]{4})([a-zA-Z0-9]{12})/', '$1-$2-$3-$4-$5', bin2hex($string)));
}

// From hexadecimal do binary
function generateId($string)
{
    return hex2bin(str_replace('-', '', $string));
}