[SOLVED] PDO with MS SQl Server

[jazzydog]

I'm working with PDO and MS SQL Server.  For some reason, "SELECT * FROM [table][tr][td]" doesn't return any rows.  However,

"SELECT [first col name], [second col name] FROM [table][tr][td]" does work. 

 

"SELECT * FROM [table][tr][td]" does work when connecting to a mysql database using PDO. 

 

Any ideas? Am I missing something really fundamental here?

 

Here's the code:

 

/*********************************************************

SIMPLE SELECT USING PDO COMMANDS AGAINST MYSQL DB -- THIS WORKS.

*********************************************************/

//MYSQL Connection information

$db_myHost = "localhost"; 

$db_myUser= "root";

$db_myPassword = "";

$db_myDatabase = "cdcol";

 

//Instantiate a new PDO object, passing in the connection information to the constructor

$dbPDO = new PDO('mysql:host='.$db_myHost.';dbname='.$db_myDatabase, $db_myUser, $db_myPassword);

 

//Iterate through and display the results

echo '<h2>USING PDO COMMANDS WITH MYSQL DB</h2><p><ul>';

foreach ($dbPDO->query($sql) as $row)

{

      //print_r($row);

echo '<li>'.$row['titel'].'</li>';

}

echo '</ul>';

 

//close the connection

$dbPDO = null;

 

/************************************************************

USING PDO WITH MS SQL SERVER 2000 -- "SELECT *" DOESN'T WORK

************************************************************/

//MSSQL Connection information

$db_myHost = "myserver";

$db_myUser= "testuser";

$db_myPassword = "test";

$db_myDatabase = "northwind";

 

//Instantiate a new PDO object, passing in the connection information to the constructor

try

{

$dbPDO = new PDO('mssql:host='.$db_myHost.';dbname='.$db_myDatabase, $db_myUser, $db_myPassword);

}

catch  (PDOException $e)

{

    echo "Error!: " . $e->getMessage() . "<br/>";

    die();

}

 

//the query

$sql = "SELECT * FROM Employees"; //WEIRD - this does not work

//$sql = "select lastname, firstname from employees"; //Seems you have to specify each column you want.

 

//Iterate through and display the results

echo '<h2>USING PDO COMMANDS WITH MSSQL DB</h2><p>';

echo $sql;

echo '<ul>';

 

$count = $dbPDO->exec($sql);

echo 'count: '.$count; //NO COUNT RETURNED

try

{

$result = $dbPDO->query($sql);

foreach ($result as $row)

{

echo '<li>'.$row['lastname'].'</li>';

}

}

catch (PDOException $e)

{

    print "Error!: " . $e->getMessage() . "<br/>";

    die();

}

 

echo '</ul>';

 

 

//close the connection

$dbPDO = null;

/***********************************************/

 

Thanks for any help you might be able to offer!

[hitman6003]

Try forcing it to throw an exception on error:

 

try
   {
   $dbPDO = new PDO('mssql:host='.$db_myHost.';dbname='.$db_myDatabase, $db_myUser, $db_myPassword);
   $dbPDO->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
   }
catch  (PDOException $e)
   {
    echo "Error!: " . $e->getMessage() . "
";
    die();
   }

[jazzydog]

Thanks for the tip.  That DID expose an exception resulting from the fact that, apparently. PDO doesn't play well with SQL Server's ntext data type -- hence, the query failed.  Changing that to a text data type allowed me to move on to uncover the coding errror: I did not type the field names with the proper case.  Changing $row["lastname"] to $row["LastName"] did the trick.  Seems I'm still paying for getting my first coding experience in Visual Basic...

 

Anyway, problem solved.  Thanks, again.