Knowledgebase: QODBC
[QODBC-Desktop] Troubleshooting - How to connect to QuickBooks using PDO
Posted by Jack - QODBC Support on 26 December 2017 09:45 AM

Troubleshooting - How to connect to QuickBooks using PDO

Problem Description:

We have a setup connecting a PHP application to QuickBooks with QODBC. This works correctly using odbc_connect.

However, if we try to connect using PDO instead, we get 500 Internal Errors. See the code below:

try{
$conn = new PDO ("odbc:QuickBooks Data QRemote", "", "");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$query = "SELECT * FROM Customer";
#Perform the query

$conn->beginTransaction();
$stmt = $conn->prepare($query);
$stmt->execute();
$conn->commit();
$res = $stmt->fetchAll(PDO::FETCH_ASSOC);

print_r($res);

die(json_encode(array('outcome' => true)));
}
catch(PDOException $ex){
die(json_encode(array('outcome' => false, 'message' => 'Unable to connect')));
}

 

Solution:

To resolve this issue, Please download & install QODBC's latest version, V323 or above, by clicking here.

There is a known issue with the PDO connection when connecting to QRemote using PDO; it does not return the result if you have a lot of records on the table or are fetching all columns (i.e., Select * from TableName).

So instead of selecting all columns using wildcard character *, you need to specify a limited column name & execute the query.

For Example:

Select ListID, FullName, CompanyName, Email, and Balance from the Customer

Select TxnID, CustomerRefFullName, TxnDate, RefNumber, TermsRefFullName, Subtotal, InvoiceLineItemRefFullName, InvoiceLineDesc, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineAmount FROM InvoiceLine

If your table contains a lot of data, please use the date filter in your query below.

For Example:

Select TxnID, CustomerRefFullName, TxnDate, RefNumber, TermsRefFullName, Subtotal, InvoiceLineItemRefFullName, InvoiceLineDesc, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineAmount FROM InvoiceLine TxnDate >= {d'2017-06-01'} and TxnDate <= {d'2017-06-30'}

After installing the latest QODBC version, you should able to run the PDO script.

Please use below sample script.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>QODBC PHP Script To Display SQL Results</title>
</head>
<body topmargin="3" leftmargin="3" marginheight="0" marginwidth="0" bgcolor="#ffffff" link="#000066" vlink="#000000" alink="#0000ff" text="#000000">
<table border="0" border="0" bgcolor="lightgreen" bordercolor="black" cellpadding="0" cellspacing="0">
<tr>
<td>
<table border="2" bordercolor="black" bgcolor="white" cellpadding="5" cellspacing="0">
<caption align="top">QODBC PHP Script To Display SQL Results</caption>
<?php
$conn = new PDO ("odbc:QuickBooks Data QRemote", "", "");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$query = "SELECT TxnID,CustomerRefFullName,TxnDate,RefNumber,BillAddressAddr1,BillAddressAddr2,BillAddressCity,BillAddressState,BillAddressPostalCode,TermsRefFullName,DueDate,Subtotal,InvoiceLineItemRefFullName,InvoiceLineDesc,InvoiceLineQuantity,InvoiceLineRate,InvoiceLineAmount FROM InvoiceLine";
$stmt = $conn->prepare($query);
$stmt->execute();
$colcount = $stmt->columnCount();
foreach ($stmt->fetchall(PDO::FETCH_BOTH) as $row)
{
  echo '<tr>';
  for($i = 0; $i < $colcount;  $i++ ) {
		echo '<td>';
		print_r($row["$i"]);
		echo '</td>';
  }
  echo '</tr>';
}
?>
</table>
</td>
</tr>
</table>
</body>
</html>

 

 

(2 vote(s))
Helpful
Not helpful
Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).