Welcome Guest, Not a member yet? Register   Sign In
Clueless: odbc from Linux to a SQL Server Database

[eluser]Sergio B[/eluser]

For a couple of days now I have been investigating how to connect to a Microsoft SQL Server database with PHP from Linux. After some searching through the web I got the FreeTDS and the unixODBC set up and now I can use the tsql and isql commands to connect to to the SQL Server and I can also do it from a PHP script and retrieve the data:


    $link = odbc_connect("SQLTest", "sa", "Password12345");
        echo "SQL Server connection failed";

    odbc_exec($link, "USE TestDB");
    $result = odbc_exec($link, "SELECT * FROM TestTable");

    echo "<table>";
    echo "<tr><th>TestID</th><th>TestString</th></tr>";

        echo "<tr><td>".odbc_result($result, "TestID")."</td><td>".odbc_result($result, "TestString")."</td></tr>";

    echo "</table>";


Now when it comes to doing it with CodeIgniter I’m clueless. As you can see I have set up a System DSN (SQLTest) to use with the odbc_connect function but I have no idea how to use it with the CodeIgniter Database class. This is the database.php file I tried to use:

$db['default']['hostname'] = "";
$db['default']['username'] = "sa";
$db['default']['password'] = "Password12345";
$db['default']['database'] = "TestDB";
$db['default']['dbdriver'] = "odbc";
$db['default']['dbprefix'] = "";
$db['default']['pconnect'] = FALSE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = "";
$db['default']['char_set'] = "utf8";
$db['default']['dbcollat'] = "utf8_general_ci";

What I got was a nice "Unable to connec to your database server using the provided settings".

I don’t know what the error is or how is CI trying to establish the connection. Any help would be appreciated.

PD: Off course I tried to use the "mssql" setting for "dbdriver" parameter but off course it didn't work which is not strange since there is no php_msssql extension for linux.

I am using CI to connect to a odbc database. You need to configure/set up the odbc connection on the server where CI is. The database name should be the DSN name you made when creating the connection to the database. For example, in my setup I have a Microsoft Access DB file called custom.mdb that's on a workstation computer. I created a ODBC connection on the server called Paintmakr. So I have
$db['default']['database'] = "Paintmakr"; //User DSN name

[eluser]Sergio B[/eluser]
Thanks a lot for your answer pickupman as you suggested I left the hostname blank and put the DSN name in the database parameter and it worked.

I'm really grateful because, trust me, I had no idea how to set up a connection like this. Thanks again.

Theme © iAndrew 2016 - Forum software by © MyBB