Server-side processing with MSSQL PDO

juniorDeveloper

Hello, I am trying to change the ssp.class.php to use PDO and connect to my MSSQL database. My file server-side.php page looks like this
``` <?php
$table = 'SC.datatables_demo';
$primaryKey = 'id';
$columns = array(
array( 'db' => 'first_name', 'dt' => 0 ),
array( 'db' => 'last_name', 'dt' => 1 ),
array( 'db' => 'position', 'dt' => 2 ),
array( 'db' => 'office', 'dt' => 3 )
);

$sql_details = array(
'user' => 'myusername',
'pass' => 'mypass',
'db' => 'DatabaseDemo',
'host' => '10...**'
);

require( 'ssp.class.php' );

echo json_encode(
SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);

and my ssp.class.php looks like this after change the default one which was using MySQL. I added just the connection part i believe the issue is there.

<?php
class SSP {

static function db ( $conn )
{
    if ( is_array( $conn ) ) {
        return self::sql_connect( $conn );
    }

    return $conn;
}

static function simple ( $request, $conn, $table, $primaryKey, $columns )
{
    $bindings = array();
    $db = self::db( $conn );

    // Build the SQL query string from the request
    $limit = self::limit( $request, $columns );
    $order = self::order( $request, $columns );
    $where = self::filter( $request, $columns, $bindings );

    // Main query to actually get the data
    $data = self::sql_exec( $db, $bindings,
        "SELECT `".implode("`, `", self::pluck($columns, 'db'))."`
         FROM `$table`
         $where
         $order
         $limit"
    );

    // Data set length after filtering
    $resFilterLength = self::sql_exec( $db, $bindings,
        "SELECT COUNT(`{$primaryKey}`)
         FROM   `$table`
         $where"
    );
    $recordsFiltered = $resFilterLength[0][0];

    // Total data set length
    $resTotalLength = self::sql_exec( $db,
        "SELECT COUNT(`{$primaryKey}`)
         FROM   `$table`"
    );
    $recordsTotal = $resTotalLength[0][0];

    /*
     * Output
     */
    return array(
        "draw"            => isset ( $request['draw'] ) ?
            intval( $request['draw'] ) :
            0,
        "recordsTotal"    => intval( $recordsTotal ),
        "recordsFiltered" => intval( $recordsFiltered ),
        "data"            => self::data_output( $columns, $data )
    );
}

static function complex ( $request, $conn, $table, $primaryKey, $columns, $whereResult=null, $whereAll=null )
{
    $bindings = array();
    $db = self::db( $conn );
    $localWhereResult = array();
    $localWhereAll = array();
    $whereAllSql = '';

    // Build the SQL query string from the request
    $limit = self::limit( $request, $columns );
    $order = self::order( $request, $columns );
    $where = self::filter( $request, $columns, $bindings );

    $whereResult = self::_flatten( $whereResult );
    $whereAll = self::_flatten( $whereAll );

    if ( $whereResult ) {
        $where = $where ?
            $where .' AND '.$whereResult :
            'WHERE '.$whereResult;
    }

    if ( $whereAll ) {
        $where = $where ?
            $where .' AND '.$whereAll :
            'WHERE '.$whereAll;

        $whereAllSql = 'WHERE '.$whereAll;
    }

    // Main query to actually get the data
    $data = self::sql_exec( $db, $bindings,
        "SELECT `".implode("`, `", self::pluck($columns, 'db'))."`
         FROM `$table`
         $where
         $order
         $limit"
    );

    // Data set length after filtering
    $resFilterLength = self::sql_exec( $db, $bindings,
        "SELECT COUNT(`{$primaryKey}`)
         FROM   `$table`
         $where"
    );
    $recordsFiltered = $resFilterLength[0][0];

    // Total data set length
    $resTotalLength = self::sql_exec( $db, $bindings,
        "SELECT COUNT(`{$primaryKey}`)
         FROM   `$table` ".
        $whereAllSql
    );
    $recordsTotal = $resTotalLength[0][0];

    /*
     * Output
     */
    return array(
        "draw"            => isset ( $request['draw'] ) ?
            intval( $request['draw'] ) :
            0,
        "recordsTotal"    => intval( $recordsTotal ),
        "recordsFiltered" => intval( $recordsFiltered ),
        "data"            => self::data_output( $columns, $data )
    );
}

static function sql_connect ( $sql_details )
{
    try {
        // $db = @new PDO(
        //  "mysql:host={$sql_details['host']};dbname={$sql_details['db']}",
        //  $sql_details['user'],
        //  $sql_details['pass'],
        //  array( PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION )
        // );
        $db = new PDO("odbc:Driver=ODBC Driver 13 for SQL Server;Server=$sql_details['host'],1456;Database=$sql_details['db'];Port=1456", $sql_details['user'], $sql_details['pass']);
    }
    catch (PDOException $e) {
        self::fatal(
            "An error occurred while connecting to the database. ".
            "The error reported by the server was: ".$e->getMessage()
        );
    }

    return $db;
}

static function sql_exec ( $db, $bindings, $sql=null )
{
    // Argument shifting
    if ( $sql === null ) {
        $sql = $bindings;
    }

    $stmt = $db->prepare( $sql );
    //echo $sql;

    // Bind parameters
    if ( is_array( $bindings ) ) {
        for ( $i=0, $ien=count($bindings) ; $i<$ien ; $i++ ) {
            $binding = $bindings[$i];
            $stmt->bindValue( $binding['key'], $binding['val'], $binding['type'] );
        }
    }

    // Execute
    try {
        $stmt->execute();
    }
    catch (PDOException $e) {
        self::fatal( "An SQL error occurred: ".$e->getMessage() );
    }

    // Return all
    return $stmt->fetchAll( PDO::FETCH_BOTH );
}

static function fatal ( $msg )
{
    echo json_encode( array( 
        "error" => $msg
    ) );

    exit(0);
}

static function bind ( &$a, $val, $type )
{
    $key = ':binding_'.count( $a );

    $a[] = array(
        'key' => $key,
        'val' => $val,
        'type' => $type
    );

    return $key;
}

```

Everytime i load my page I am getting 'no data available in the table'. Any ideas please what i need to change more in ssp.class.php ? Thank you.

allan

I would suggest that you echo out the SQL that your script is generating and check if that matches what you were expecting.

Allan