PHP PDO ODBC - can connect, can't query data

[Eiolon]

I've been connecting to a database for a number of years now.  The MSSQL server was updated the other day and it can no longer pull the data down.  I connect with PHP PDO ODBC driver.  The connection to the server is good, but nothing is returned.  I can throw the query into my SQL client and bring up the data without a problem.  Any ideas on what I can try?

 

Here is a sample:

<?php
 
$HOSTNAME = '1.1.1.1';
$DATABASE = 'db';
$USERNAME = 'user';
$PASSWORD = 'pass';
 
try {
$dbh = new PDO("odbc:Driver={SQL Server};Server=$HOSTNAME;dbname=$DATABASE", "$USERNAME", "$PASSWORD");
array (PDO::ATTR_PERSISTENT => true);
} catch (PDOException $e) {
echo $e->getMessage();
}
 
$Barcode = '37782555863';
 
$sth = $dbh->prepare("
  SELECT 
    LastActivityDate
  FROM
    Customers
  WHERE
    Barcode = :Barcode
");
$sth->bindParam(':Barcode', $Barcode);
$sth->execute();
 
while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
  $LastActivityDate = $row['LastActivityDate'];
}
 
?>
 
<?php echo $LastActivityDate; ?>

[mac_gyver]

do you have php's error_reporting set to E_ALL and display_errors set to ON, so that php will help you by reporting and displaying any errors it detects.

 

you should also be using exceptions for the pdo statements. you can enable exceptions when you make the connection. add an element to the options array with - 

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION 

this will cause any errors with the pdo statements to throw an exception, which you should let php catch and use its error_reporting, display_errors, log_errors settings to determine what happens with the actual error information.

 

you should also not catch the connection exception and echo the raw error information. if you remove the try/catch block you have now and let php catch the connection exception, it will use its error_reporting, display_errors, log_errors settings to determine what happens with the actual error information.

[Eiolon]

Thanks for all your advice.  I have done all you have suggested.  The only error that returns is the Notice: Undefined variable: $LastActivityDate 

 

No connection errors reported and the company's database I am connecting to does see the server connect without a problem.

[mac_gyver]

that would indicate that the sql query didn't match any row(s) and the while(){} loop to fetch the data was false/skipped.

 

are you sure that particular barcode value exists in the database? any trans-posed digits? double digits? missing leading zeros (and the data type is actually character/text)?

 

is the barcode value in your code from a copy/paste operation? we have seen cases where there are non-printing or non-ascii characters that got copied from elsewhere, that results in a non-match in values. if this could be the case, delete and TYPE the value you are using.

Edited August 23, 2017 by mac_gyver

[ginerjm]

One should do error checking after any kind of  'external' operation.  By this I mean - opening a file, writing to a file, running a query and things that will impact the flow of you script should they fail.  In this case you should be checking that your execute actually executed.  That function returns a value so you should  CHECK IT before proceeding and you should check if any rows were returned as well (see 'rowCount()').

 

This is all just good practice to make you a better programmer.