dblib connection on GNU/Linux

mmarroquin

Hi Allan and Community!

I've had many problems trying to establish the connection with SQL Server on Windows and PHP Server on Gnu/Linux (Ubuntu).

https://datatables.net/forums/discussion/27877/freetds-connection-to-sql-server-from-linux-server

Now my configuration is the correct.

I'm using FreeTDS to create the connection, and I've done many test.

First I've tested in line command:

With Tsql:

$ tsql -S mssql -U sa -P Radec866
locale is "es_MX.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1> select top 1 * from [plan_vuelo].[dbo].pv1_roles
2> go

nombre                  email   
Vendedor Matriz micke_hp_2.0@hotmail.com    
(1 row affected)
1> 

With Isql:

$ isql -v mssql sa Radec866
| Connected!                                                               
| sql-statement                        
| help [tablename]                      
| quit                                  

SQL> select top 1 nombre, email from pv1_roles

| nombre                                                  email                                             
+---------------------------------------------------+---------------------------------------------------+
| Vendedor Matriz                                   | micke_hp_2.0@hotmail.com                          

SQLRowCount returns 1
1 rows fetched
SQL> 

And Finally I've had to create a connection in a php file:

<?php
  try {
    $hostname = "mssql";
    $port = 1433;
    $dbname = "plan_vuelo";
    $username = "sa";
    $pw = "Radec866";
    $dbh = new PDO ("dblib:host=$hostname:$port;dbname=$dbname","$username","$pw");
  } catch (PDOException $e) {
    echo "Failed to get DB handle: " . $e->getMessage() . "\n";
    exit;
  }
  $stmt = $dbh->prepare("select top 1* from pv1_roles");
  $stmt->execute();
  while ($row = $stmt->fetch()) {
    echo "<pre>";
      print_r($row);
    echo "</pre>";
  }
  unset($dbh); unset($stmt);

<?php
>
```
?>


And the connection is succesfull: 

Array
(
[idRoles] => 1
[0] => 1
[nombre] => Vendedor Matriz
[1] => Vendedor Matriz
[email] => micke_hp_2.0@hotmail.com
[2] => micke_hp_2.0@hotmail.com
)

My hostname is mssql because in my /etc/freetds/freetds.conf I established the name by my host. 
And I can use mssql o my IP-SERVER in my hostname

[mssql]
host = IP-SERVER
port = 1433
tds version = 4.2

At the moment I'm secure that my FreeTDS config it works fine. now I'm trying to configure my DataTable Site.

My /php/lib/config.php

<?php if (!defined('DATATABLES')) exit(); // Ensure being used in DataTables env.

/*
* DB connection script for Editor
* Created by http://editor.datatables.net/generator
*/

// Enable error reporting for debugging (remove for production)
error_reporting(E_ALL);
ini_set('display_errors', '1');

/*
* Edit the following with your database connection options
*/
$sql_details = array(
"type" => "Sqlserver",
"user" => "sa",
"pass" => "Radec866",
"host" => "mssql",
"port" => "1433",
"db" => "plan_vuelo",
"dsn" => ""
);

And My 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(
            "dblib: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}, {$dsn}'. The error reported by the server was: ".$e->getMessage()
        ) );
        exit(0);
    }

    return $pdo;
}

lo siento chicos he posteado este error varias veces, pero ahora estoy seguro que mi configuración es la correcta.

When I'm run my app says me:

Datatables warning: table id=pv1_roles - An error ocurred while connecting to the database 'plan_vuelo'. The error reported by the server was: SQLSTATE[HY000] Unable to connect: Adaptive Server is unavailable or dows not exist (severity) 9
```

I think I'm not passing the correct connection string in my config DataTable.

Friends hope their valuable assistance.

Thanks in advance.

allan

You would probably be best Google for this error rather than posing here to be honest. I haven't tried configuring such a server-side environment as this before, and others in the forum might have, but that won't be the reason why they've come to this forum.

You might want to start here for example.

Allan

ranblay

Just change this line and it will work: "dblib:host={$host}{$port};dbname={$db}".self::dsnPostfix( $dsn ),

I mean use host instead of Server and dbname instead of Database

allan

Thanks for the information!