Jump to content

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


Eiolon
 Share

Recommended Posts

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; ?>
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by mac_gyver
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.