Pejman Moghadam / Slackware

Slackware 13.1 - Connecting to MS-SQL from linux command line and PHP

Public domain

UnixODBC

cd /usr/src
wget -c http://pmoghadam.com/homepage/Pages/Deposit/Source-packages/unixODBC-2.2.14.tar.gz
tar zxf unixODBC-2.2.14.tar.gz
cd unixODBC-2.2.14
./configure --prefix=/usr/local/mssql/unixODBC --disable-gui
make && make install
if ! grep -q unixODBC /etc/ld.so.conf ; then
  echo "/usr/local/mssql/unixODBC/lib" >> /etc/ld.so.conf
fi
ldconfig

FreeTDS

freetds original download link

cd /usr/src
wget -c http://pmoghadam.com/homepage/Pages/Deposit/Source-packages/freetds-0.82.tar.gz
tar zxf freetds-0.82.tar.gz
cd freetds-0.82
./configure --prefix=/usr/local/mssql/freetds --with-tdsver=8.0 --with-unixodbc=/usr/local/mssql/unixODBC/ --enable-msdblib
make && make install
cp /usr/local/mssql/freetds/etc/freetds.conf{,.bak}
if ! grep -q freetds /etc/ld.so.conf ; then
  echo "/usr/local/mssql/freetds/lib" >> /etc/ld.so.conf
fi
ldconfig

/usr/local/mssql/freetds/etc/freetds.conf

[global]
tds version = 4.2
text size = 64512
[SigmaSystem]
host = 172.16.20.1
port = 1433
tds version = 8.0

/usr/local/mssql/tds.driver.template

[FreeTDS]
Description = v0.82 with protocol v8.0
Driver = /usr/local/mssql/freetds/lib/libtdsodbc.so

/usr/local/mssql/tds.datasource.template

[MSSQLServer]
Driver = FreeTDS
Description = squid accesslog database
Trace = No
Server = 172.16.20.1
Port = 1433
Database = SigmaSystem

ODBC Commands

/usr/local/mssql/unixODBC/bin/odbcinst -i -d -f /usr/local/mssql/tds.driver.template
/usr/local/mssql/unixODBC/bin/odbcinst -i -s -f /usr/local/mssql/tds.datasource.template -l
/usr/local/mssql/unixODBC/bin/isql -v MSSQLServer <username> <password>

ODBC Queries

select name from SigmaSystem..sysobjects where xtype = 'U';
exec sp_columns tbl_AccountInternet_Detail;
help tbl_AccountInternet_Detail;
select * from  tbl_AccInternetDetail  where AccInternetDetail_Code='1000';
select * from  tbl_Account_Internet where AccInternet_Code='1000';
select * from  tbl_Account_Internet where AccInternet_IPAddress = '1054431310';
select * from  tbl_Account_Internet where AccInternet_IPAddress = '1054431310' and year(AccInternet_Date)='2003' and MONTH(AccInternet_Date) ='11' AND DAY(AccInternet_Date)='19';
quit;

PHP

php original download link

cd /usr/src
wget -c "http://pmoghadam.com/homepage/Pages/Deposit/Source-packages/php-5.3.8.tar.bz2"
tar jxf php-5.3.8.tar.bz2
cd php-5.3.8
./configure --prefix=/usr/local/mssql/php --with-mssql=/usr/local/mssql/freetds
make && make install

php script

#!/usr/local/mssql/php/bin/php
<?php
#####################################################################
# Configuragtion
# dbname from freetds.conf
$dbname="SigmaSystem";
$dbuser="sa";
$dbpass="123456";
$debug="1";
#####################################################################

// Connect to MSSQL
if(!$link = mssql_connect($dbname, $dbuser, $dbpass))
  die('Something went wrong while connecting to MSSQL');

// Check MSSQL version
$version = mssql_query('SELECT @@VERSION');
$row = mssql_fetch_array($version);
if($debug == "1") echo $row[0];

// Close the link to MSSQL
mssql_close($link);
?>

BY: Pejman Moghadam
TAG: mssql, php, unixodbc, freetds
DATE: 2011-10-18 15:15:02

Pejman Moghadam / Slackware [ TXT ]