Monday, February 9, 2009

Linux/PHP with Windows/MSSQLServer Howto

Linux/PHP with Windows/MSSQLServer Howto

Made on Fedora Core 6. Suppose other distros work the same. Good luck!
- Install freetds, the unixODBC-kde gui and php-odbc, for making php work with odbc

[rodolfoap] /root # yum install freetds unixODBC-kde php-odbc

- Connectivity test, port 1433TCP. If you get "1>", ok. Elsewhere, its a firewall, ports, routing, etc. issue.

[rodolfoap] /root # tsql -S 192.168.1.20 -U sa -P sjasdad
locale is "en_US.UTF-8"
locale charset is "UTF-8"
1>

- Create an entry in /etc/freetds.conf:
[192.168.1.20]
host = 192.168.1.20
port = 1433
tds version = 8.0

- Add the entry in /etc/odbcinst.ini
[MSSQLServer]
Description = MSSQLServer
Driver = MSSQLServer
Servername = 192.168.1.20
Database = Bienes
UID = sa
PWD = sjasdad
Port = 1433

- And /etc/odbcinst.ini
[MSSQLServer]
Description =
Driver = /usr/lib/libtdsodbc.so.0
Driver64 =
Setup = /usr/lib/libtdsS.so.1
Setup64 =
UsageCount = 1
CPTimeout =
CPReuse =

- Now, you should get "SQL>"
[rodolfoap] /root # isql -v MSSQLServer sa sjasdad
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
(There you can perform SQL queries)

- The odbc php test page (for my database, of course):
[rodolfoap] /root # cat /var/www/html/odbc.php

<?
$connect = odbc_connect("MSSQLServer", "sa", "sjasdad");
odbc_exec($connect, "use Bienes");
$result = odbc_exec($connect, "SELECT * FROM Personas");
while(odbc_fetch_row($result)){
print(odbc_result($result, "CodPersona").' '.odbc_result($result, "Apellidos") . "<br>\n");
}
odbc_free_result($result);
odbc_close($connect);
?>

- If you want to use mssql functions, install php-mssql from http://remi.collet.free.fr/ , adding the repository and issueing "yum install php-mssq". And then,

[rodolfoap] /root # cat /var/www/html/mssql.php
<?
$connect = mssql_connect("192.168.1.20", "sa", "sjasdad");
mssql_select_db("Bienes", $connect);
$result = mssql_query("SELECT * FROM Personas");
$numRows = mssql_num_rows($result);
echo "<h1>" . $numRows . " Row" . ($numRows == 1 ? "" : "s") . " Returned </h1>";
while($row = mssql_fetch_array($result))
echo $row["IdPersona"] . " - " . $row["Apellidos"];
mssql_free_result($result);
mssql_close($connect);
?>

No comments: