Knowledgebase
[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 Error. See 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 latest version V323 or above by clicking here.

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

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

For Example:

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

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

If your table contains a lot of data, then please use date filter in your query as 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 QODBC latest version you should able to run 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).