FreeTDS: Connection to SQL server from Linux Server

mmarroquin

Hi Allan and community. I have a editable table and I need to connect a database in SQL SERVER.

im trying to fix mi issue: https://datatables.net/forums/discussion/27530/how-to-use-dblib

Now, i'm trying to connect my server with my datatables application:

In my php/lib/config.php:

$sql_details = array(
    "type" => "Sqlserver",
    "user" => "username",
    "pass" => "password",
    "host" => "ip-host",
    "port" => "1433",
    "db"   => "plan_vuelo",
    "dsn"  => "mssql"
);

In my Database/Driver/SqlServer/Query.php:

static function connect( $user, $pass='', $host='', $port='', $db='', $dsn='' )
    {
        if ( is_array( $user ) ) {
            $opts = $user;
            $user = $opts['user'];
            $pass = $opts['pass'];
            $port = $opts['port'];
            $host = $opts['host'];
            $db   = $opts['db'];
            $dsn  = isset( $opts['dsn'] ) ? $opts['dsn'] : '';
        }

        if ( $port !== "" ) {
            $port = ",{$port}";
        }

        try {
            $pdo = new PDO(
                "sqlsrv:Server={$host}{$port};Database={$db}".self::dsnPostfix( $dsn ),
                $user,
                $pass,
                array(
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
                )
            );
        } catch (\PDOException $e) {
            // If we can't establish a DB connection then we return a DataTables
            // error.
            echo json_encode( array( 
                "sError" => "An error occurred while connecting to the database ".
                    "'{$db}'. The error reported by the server was: ".$e->getMessage()
            ) );
            exit(0);
        }

        return $pdo;
    }

My FreTDS config:

/etc/freetds/freetds.conf


[mssql]
  host = ip-host
  port = 1433
  tds version = 4.2

/etc/odbcinst.ini


 [FreTDS]
  Description = FreeTDS
  Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
  Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
  client charset = utf-8                                 

/etc/odbcinst.ini


 [mssql]
   Description = FreeTDS
   Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
   Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
   Database = plan_vuelo
   ServerName = mssql
   TDS_Version = 4.2
                           

With my configuration I can create the connection:

My freetds connection works very well:

mmarroquin@mmarroquin-ThinkPad-T430:/var/www$ isql -v mssql username password

SQL> select TOP 1 * from plan_vuelo.dbo.pv1_usuarios
idUsuario    nombreUsuario     claveUsuario             
 1             v_matriz           123                      
SQLRowCount returns 1
1 rows fetched

But When I run my datatables application, the server said me:

{"sError":"An error occurred while connecting to the database 'plan_vuelo'. The error reported by the server was: could not find driver"}

I hope your help. Thanks in advance.

allan

could not find driver

That indicates that the sqlsrv PDO driver that is used in your PDO connection string is not installed. I think you need to update that connection string to match whatever the FreeTDS driver requires.

Allan