Question 206450 in PHP Question posted in
Official manual can be found here.

Php – Connecting to mssqlnative

I have a working website running on Unix that connects via VPN to a SQL Server database using dblib.

The connection has been created in years past like this:

$pdo = new PDO('dblib:host=192.168.2.4:1433;dbname=Andover;charset=utf8', 'UserName', 'Password');
$Query= "Select * From tablename";
$Stmt = $pdo->prepare($Query);

The new connection method I’ve been told to use is as follows:

<?php 
$serverName = "192.168.2.4,1433"; //serverNameinstanceName, portNumber (default is 1433) 
$connectionInfo = array( "Database"=>"Andover", "UID"=>"UserName", "PWD"=>"Password"); 
$pdo = sqlsrv_connect( $serverName, $connectionInfo); 
if( $pdo ) { 
     echo "Connection established.<br />"; 
}else{ 
     echo "Connection could not be established.<br />"; 
     die( print_r( sqlsrv_errors(), true)); 
} 
echo "****************EOF************";
?>

And this seems to work, running through without error.

However, when I try to use a table it fails:

$sql= "SELECT * from TableName";
$stmt = $pdo->prepare($sql);
$stmt->execute();

The error is:

Uncaught Error: Call to a member function prepare() on resource

What do I need to do to be able to access the table?

Tags:

2

Answers


  1. 0

    sqlsrv it’s not PDO and have different sintax, first argument it’s resource of connection to MSSQL, that’s why you recieve your error.

    Full working code must be like this:

    $serverName = "192.168.2.4,1433"; //serverNameinstanceName, portNumber (default is 1433) 
$connectionInfo = array( "Database"=>"Andover", "UID"=>"UserName", "PWD"=>"Password"); 
$mssconnect = sqlsrv_connect( $serverName, $connectionInfo); 
if( $pdo ) { 
     echo "Connection established.<br />"; 
}else{ 
     echo "Connection could not be established.<br />"; 
     die( print_r( sqlsrv_errors(), true)); 
} 

$sql= "SELECT * from TableName";
$stmt = sqlsrv_prepare( $mssconnect, $sql);
sqlsrv_execute($stmt)
    Login or Signup to reply.
  2. 0

    You are using the sqlsrv part of the PHP Driver for SQL Server, which is not object oriented, so in this situation you need a call to sqlsrv_query().

    <?php 
$serverName = "192.168.2.4,1433"; //serverNameinstanceName, portNumber (default is 1433) 
$connectionInfo = array( "Database"=>"Andover", "UID"=>"UserName", "PWD"=>"Password"); 
$pdo = sqlsrv_connect( $serverName, $connectionInfo); 
if ($pdo) { 
     echo "Connection established.<br />"; 
} else{ 
    echo "Connection could not be established.<br />"; 
    die( print_r( sqlsrv_errors(), true)); 
} 

$sql  = "SELECT * from TableName";
$stmt = sqlsrv_query($pdo, $sql);
if ($stmt === false) {
    echo "Statement returns error.<br />"; 
    die(print_r(sqlsrv_errors(), true));
}   
while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)){
    echo print_r($row, true);
}

sqlsrv_free_stmt($stmt);
sqlsrv_close($pdo);
?>
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search