Debian/PHP Connect to MS SQL Server

Se connecter à un serveur MSSQL avec PDO, ODBC et FreeTDS

Sur votre debian installer:

apt-get install freetds-bin freetds-common tdsodbc odbcinst php5-odbc unixodbc

cp /usr/share/doc/freetds-common/examples/odbcinst.ini /etc/odbcinst.ini

Exemple de script PHP:

<?php

try {
    $db = new PDO('odbc:Driver=FreeTDS; Server=192.168.1.2; Port=1433; Database=test; UID=root; PWD=toor;');
}
catch(PDOException $e) {

    die("Unable to open database.<br>Error message:<br><br>$e.");
}

echo '<h1>Successfully connected!</h1>';

$query = 'SELECT * FROM user;';
$statement = $db->prepare($query);
$statement->execute();
$result = $statement->fetchAll(PDO::FETCH_NUM);

print_r( $result );

Source: secure.kitserve.org.uk

Exporter des grosses quantités de données en CSV

Pour éviter l’erreur : Fatal error: Allowed memory size of 1073741824 bytes exhausted (tried to allocate 43 bytes) in /var/www/* on line 60

set_time_limit(0);

header("Content-type: application/csv");
header("Content-Disposition: attachment; filename=file.csv");
header("Pragma: no-cache");
header("Expires: 0");

mysql_connect("localhost", "root", "");
mysql_select_db("big");
$result = mysql_unbuffered_query("SELECT * FROM `post`;");

while ($row = mysql_fetch_assoc($result)) {
    echo implode(';',$row) ."\n";
    flush();
}

mysql-unbuffered-query

avec PDO

$db = new PDO(...);
$db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

$posts = $db->query('SELECT * FROM `post`;');

/*
PDO::FETCH_ASSOC
PDO::FETCH_OBJ
PDO::FETCH_BOTH
*/


 while ($row = $posts->fetch(PDO::FETCH_ASSOC)) {
      echo implode(';',$row ) . "\n";
   }

PDO::MYSQL_ATTR_USE_BUFFERED_QUERY