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));
}