[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 PDOProblem 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>
| |
|