Hi
We have recently purchased QODBC, for a single user. But to excute the Query it is taking too many hours. If we have to insert about 10 records into my Quickbook 2005 file it is taking about 20 min. and if we are executing a query for a 90 records after a long time, it times out with a message "Execution Timed out". As an end user we do not feel that this is not normal and not workable response time for this query. We feel it may be problem in my code(Listed Below) or configurations of the qodbc or there may be some patches that we have not installed to over come the performance issues. We need your help…… I am providing a summary below.
Task 1.
We have financial in an Excel Spreadsheet. We are running a PHP Script and connect to Quick books 2005 using the qodbc as the connector / bridge and updating/inserting records in Quick Books 2005.
Task 2.
We are trying to insert 10 records into QB Customer table. The total No# of Records in Customer table in my QB file is only 500 records, and the size of the QB file is 20 MB. This is not to huge data for QODBC to take over 20 mins!!!. We need to insert/update daily 100 customer records and I can’t get even to run because it times out….
Details:
1. In QB, We have to insert/update Customer data base on the Account NO., i.e a field in Payment section of Customer Details. That Account no. is actually a Customer Id for us, since only the Account No. Field is searchable in QB, we have used that Account No field to store customer Id.
2. Example of Customer Data
- CUST[tab] Prof L Solutions (Brian John)[tab] 2 Summit Park Drive[tab] [tab] Independence[tab] OH[tab] 44131[tab] 2 Summit Park Drive[tab] [tab] Independence[tab] OH[tab] 44131[tab] 216-444-4444[tab] 440-888-8888[tab] 216-333-3333[tab] abc@yahoo.com[tab] 4973[tab] Brian John[tab] [tab] Web[tab] Due on receipt[tab] Y[tab] None[tab] [tab] [tab] [tab] [tab] Prof L Solutions [tab] Brian[tab] [tab] John[tab] 440-222-2222[tab] Johnbk[tab] 01/08/2006[tab] [tab] [tab] [tab] [tab] [tab] [tab] [tab] [tab] [tab] [tab] [tab] [tab] Base sales price
- My code (details for qodbc is below)
I need help. I have been trying and I am stuck for quite a few days. I would appreciate any help you can give me on this. Thank you….
My Code for QODBC:
<?
set_time_limit(12000);
/* ----------------------- NETSUITE Data Fetch -----------------*/
function csv_explode($str, $delim = ',', $qual = "\"")
{
$skipchars = array( $qual, "\\" );
$len = strlen($str);
$inside = false;
$word = '';
for ($i = 0; $i < $len; ++$i) {
if ($str[$i]==$delim && !$inside) {
$out[] = $word;
$word = '';
} else if ($inside && in_array($str[$i], $skipchars) && ($i<$len && $str[$i+1]==$qual)) {
$word .= $qual;
++$i;
} else if ($str[$i] == $qual) {
$inside = !$inside;
} else {
$word .= $str[$i];
}
}
$out[] = $word;
return $out;
}
$fcontents1 = file ("C:\\D_MOD.csv");
$oConnect = odbc_connect("QuickBooks Data", "", "");
//$oConnect = 1;
if($oConnect)
{
//echo "<br>Connection done<br>";
for($i=1; $i<15; $i++)
{
$line = trim($fcontents1[$i]);
$csv_str = ereg_replace('"',"\"",$line);
$arv = csv_explode($csv_str);
$q = 0;
$aclen = strlen($arv[1]);
// ZERO setting start
$accountno = $arv[16];
$len = strlen($accountno);
$maxlen = 6 - $len;
$faccountno = $accountno;
//echo "<br>Account : ".$faccountno;
for($loop=0;$loop<$maxlen;$loop++)
{
$faccountno = "0".$faccountno;
}
//echo "<br>Account no in Query : ".$faccountno;
if($arv[1] != "")
{
// Check User Exists or Not STARTS --------------------------
$sSQL_usrcheck= "SELECT * FROM Customer WHERE AccountNumber = '$faccountno'";
print "<br><br><br>I : ".$i." - ".$sSQL_usrcheck;
$oResult_usrcheck = odbc_exec($oConnect,$sSQL_usrcheck);
$RowCnt = 0;
$RowCnt = odbc_num_rows($oResult_usrcheck);
echo "<br>Record With that Account Number : ".$RowCnt;
if($RowCnt)
{
//----------------------- Quickbook Updation Starts here --------------------
$sSQL = "UPDATE Customer
SET
BillAddressAddr1 = '$arv[2]', BillAddressAddr2 = '$arv[3]',
BillAddressAddr3 = '$arv[4]', BillAddressState = '$arv[5]',
BillAddressPostalCode = '$arv[6]', ShipAddressAddr1 = '$arv[7]',
ShipAddressAddr2 = '$arv[8]', ShipAddressAddr3 = '$arv[9]',
ShipAddressState = '$arv[10]',ShipAddressPostalCode = '$arv[11]',
Phone = '$arv[12]', AltPhone = '$arv[13]',
Fax = '$arv[14]', Email = '$arv[15]',
Contact = '$arv[17]', AltContact = '$arv[18]',
TermsRefFullName = '$arv[20]', ResaleNumber = '$arv[24]',
Notes = '$arv[25]', Salutation = '$arv[26]',
CompanyName = '$arv[27]', FirstName = '$arv[28]',
LastName = '$arv[29]',
CustomFieldCUST1 = '$arv[31]', CustomFieldCUST2 = '$arv[32]',
CustomFieldCUST3 = '$arv[33]', CustomFieldCUST4 = '$arv[34]',
CustomFieldCUST5 = '$arv[35]', CustomFieldCUST6 = '$arv[36]',
CustomFieldCUST7 = '$arv[37]'
where AccountNumber = '".$faccountno."'";
print "<br>Update : ".$i." - ".$sSQL;
$oResult = odbc_exec($oConnect, $sSQL);
//----------------------- Quickbook Updation Ends here --------------------
}
else
{
//----------------------- Quickbook Insertion Starts here --------------------
$sSQL = "INSERT INTO Customer
(
Name, AccountNumber,
BillAddressAddr1, BillAddressAddr2,
BillAddressAddr3, BillAddressState,
BillAddressPostalCode, ShipAddressAddr1,
ShipAddressAddr2, ShipAddressAddr3,
ShipAddressState,ShipAddressPostalCode,
Phone, AltPhone,
Fax, Email,
Contact, AltContact,
TermsRefFullName, ResaleNumber,
Notes, Salutation,
CompanyName, FirstName,
LastName
)
values (
'$arv[1]','$faccountno',
'$arv[2]','$arv[3]',
'$arv[4]','$arv[5]',
'$arv[6]','$arv[7]',
'$arv[8]', '$arv[9]',
'$arv[10]','$arv[11]',
'$arv[12]','$arv[13]',
'$arv[14]','$arv[15]',
'$arv[17]', '$arv[18]',
'$arv[20]', '$arv[24]',
'$arv[25]','$arv[26]',
'$arv[27]','$arv[28]',
'$arv[29]'
)";
print "<br>Insert : ".$i." - ".$sSQL;
$oResult = odbc_exec($oConnect, $sSQL);
&nbs | 
