Quickbooks PHP Dev Kit Web Connector SalesRep Import

Question

I am using the excellent Quickbooks PHP Dev Kit provided by Consolibyte and I am doing the sales rep import into a database. The code below is what I have for the request xml. The Quickbooks file has over 3000 sales reps and response xml is invalid. I know I can use the metaData to get the count and use MaxReturned to get limited records. by not sure if there in iterator?

I am passing the xml to mssql database and parsing it there. The problem is when the xml is invalid, it causes the sqlserver to hang as query is running - so I need to somehow the validate the xml before passing it on to sql.

Any help would be appreciated.

$xml = '<?xml version="1.0" encoding="utf-8"?>
            <?qbxml version="' . $version . '"?>
            <QBXML>
                <QBXMLMsgsRq onError="stopOnError">
                    <SalesRepQueryRq>
                        <ActiveStatus>ActiveOnly</ActiveStatus>
                        <FromModifiedDate>' . $last . '</FromModifiedDate>
                        <IncludeRetElement>ListID</IncludeRetElement>
                        <IncludeRetElement>SalesRepEntityRef</IncludeRetElement>
                    </SalesRepQueryRq>
                </QBXMLMsgsRq>
            </QBXML>';

    return $xml;

----updated with code below--- @keith - thanks for taking the time to respond. below is what I have for request/response functions

   //sales rep list import request
function _quickbooks_salesrep_import_request($requestID, $user, $action, $ID, $extra, &$err, $last_action_time, $last_actionident_time, $version, $locale)
{
    $last = _quickbooks_get_last_run($user, $action); //get last run time
    _quickbooks_set_last_run($user, $action); //set current run

    // Build the request
    $xml = '<?xml version="1.0" encoding="utf-8"?>
            <?qbxml version="' . $version . '"?>
            <QBXML>
                <QBXMLMsgsRq onError="stopOnError">
                    <SalesRepQueryRq>
                        <ActiveStatus>ActiveOnly</ActiveStatus>
                        <FromModifiedDate>' . $last . '</FromModifiedDate>
                        <IncludeRetElement>ListID</IncludeRetElement>
                        <IncludeRetElement>SalesRepEntityRef</IncludeRetElement>
                    </SalesRepQueryRq>
                </QBXMLMsgsRq>
            </QBXML>';

    return $xml;
}

//sales rep list import response
function _quickbooks_salesrep_import_response ($requestID, $user, $action, $ID, $extra, &$err, $last_action_time, $last_actionident_time, $xml, $idents)
{
      global $conn;
      $sql = '{call dbo.spQBAddUpdateSalesRep (?)}';
      $params = array($xml);
      $stmt = dbQuery($sql,$conn,$params);
      dbCloseQuery($stmt);
    //QuickBooks_Utilities::log(QB_QUICKBOOKS_DSN, 'rep xml - ' . $xml);
    return true;
}

Here's what I have in the stored procedure where I pass the xml

@strXML XML --- this is passed as parameter

BEGIN TRY
BEGIN TRANSACTION

UPDATE dbo.qb_salesrep SET
             TimeCreated = SUB.TimeCreated
            ,TimeModified = SUB.TimeModified
            ,EditSequence = SUB.EditSequence
            ,Initial = SUB.Initial
            ,IsActive = SUB.IsActive
            ,SalesRepEntityRef_ListID = SUB.SalesRepEntityRef_ListID
            ,SalesRepEntityRef_FullName = SUB.SalesRepEntityRef_FullName
        FROM
            (SELECT
                 ListID = x.c.value('ListID[1]','VARCHAR(36)')
                ,TimeCreated = x.c.value('TimeCreated[1]','DATETIME')
                ,TimeModified = x.c.value('TimeModified[1]','DATETIME')
                ,EditSequence = x.c.value('EditSequence[1]','VARCHAR(16)')
                ,Initial = x.c.value('Initial[1]','VARCHAR(5)')
                ,IsActive = x.c.value('IsActive[1]','BIT')
                ,SalesRepEntityRef_ListID = x.c.value('SalesRepEntityRef[1]/ListID[1]','VARCHAR(36)')
                ,SalesRepEntityRef_FullName = x.c.value('SalesRepEntityRef[1]/FullName[1]','VARCHAR(41)')
            FROM
                @strXML.nodes('QBXML/QBXMLMsgsRs/SalesRepQueryRs/SalesRepRet') x(c)) AS SUB
        WHERE
            dbo.qb_salesrep.ListID = SUB.ListID 

INSERT INTO dbo.qb_salesrep (
                 ListID
                ,TimeCreated
                ,TimeModified
                ,EditSequence
                ,Initial
                ,IsActive
                ,SalesRepEntityRef_ListID
                ,SalesRepEntityRef_FullName
            )
            SELECT
                 ListID = x.c.value('ListID[1]','VARCHAR(36)')
                ,TimeCreated = x.c.value('TimeCreated[1]','DATETIME')
                ,TimeModified = x.c.value('TimeModified[1]','DATETIME')
                ,EditSequence = x.c.value('EditSequence[1]','VARCHAR(16)')
                ,Initial = x.c.value('Initial[1]','VARCHAR(5)')
                ,IsActive = x.c.value('IsActive[1]','BIT')
                ,SalesRepEntityRef_ListID = x.c.value('SalesRepEntityRef[1]/ListID[1]','VARCHAR(36)')
                ,SalesRepEntityRef_FullName = x.c.value('SalesRepEntityRef[1]/FullName[1]','VARCHAR(41)')
            FROM
                @strXML.nodes('QBXML/QBXMLMsgsRs/SalesRepQueryRs/SalesRepRet') x(c)
            WHERE
                x.c.value('ListID[1]','VARCHAR(36)') NOT IN (SELECT ListID FROM dbo.qb_salesrep) 

COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
RETURN
END CATCH

--so whats happening is response returned from quickbooks is not well formed xml - its cut off in the end . I am guessing because of too much data in xml. So when this is passed over to sql stored procedure - it locks up on insert/update because its not well formed xml. So I was hoping to validate xml somehow and be able to retrieve small chunks.

I got the total record count using the metaData in SalesRepQueryRq. If i set MaxReturned to 50 in request, everything works without an issue.

Here's the message from web connector

Description: QBWC1042: ReceiveResponseXML failed Error message: The operation has timed out See QWCLog for more details. Remember to turn logging on.

I used QUICKBOOKS_LOG_DEVELOP and the last lines are receiveResponseXML()

Incoming XML response: <?xml version="1.0" ?> <QBXML> <QBXMLMsgsRs> <SalesRepQueryRs requestID="6" statusCode="0" statusSeverity="Info" statusMessage="Status OK"> <SalesRepRet> <ListID>31A0000-1193779738</ListID> <SalesRepEntityRef> <ListID>60F0002-1193780133</ListID> <FullName>*BRANDT&apos;S</FullName> </SalesRepEntityRef> </SalesRepRet>
.....
.....
</Sales

This is how the xml ends

Post your code. Post the logs. Post the EXACT error message. I suspect you are hugely misinterpreting what's actually happening, but you didn't really post enough information for anyone to help you. — Keith Palmer Jr., Jan 27 '17 at 03:06

score 1 · Accepted Answer · answered Jan 28 '17 at 20:40

--so whats happening is response returned from quickbooks is not well formed xml

No, that is not what's happening. The XML QuickBooks is returning is totally fine.

I am guessing because of too much data in xml.

Your problem is related to there being a lot of data... but it's not actually related to invalid XML from QuickBooks.

So when this is passed over to sql stored procedure - it locks up on insert/update because its not well formed xml.

No, that's not why things are blowing up.

So I was hoping to validate xml somehow and be able to retrieve small chunks.

No need to validate the XML. The XML QuickBooks is returning is perfectly valid.

However, breaking up the result into smaller chunks is a good idea.

If i set MaxReturned to 50 in request, everything works without an issue.

This is a big hint. It's not related to invalid XML... your problem is related to how many results are being returned in the XML.

Description: QBWC1042: ReceiveResponseXML failed Error message: The operation has timed out See QWCLog for more details. Remember to turn logging on.

Now we're at the important bit!

This is the key:

The operation has timed out

Unfortunately, you didn't post the rest of the Web Connector log file, so we didn't get the rest of the really helpful information that would have been helpful.

The Web Connector has a hard-coded, 2-minute timeout. If you had posted the rest of the logs, I think we'd see that the timestamps in the Web Connector log show a 2-minute gap between when the Web Connector started sending you data, and when the connection was closed and the error shows up in the logs.

I bet if you benchmarked your code, you'd also see that your code takes more than 2 minutes to process the response from QuickBooks.

I used QUICKBOOKS_LOG_DEVELOP and the last lines are receiveResponseXML()

I'm assuming you're pulling this from the quickbooks_log SQL table. I bet if you checked your column type, you'd see that the max length on the column just cut off the XML because it couldn't fit all the data into the column.

by not sure if there in iterator?

At which point you can look at the documentation:

https://developer-static.intuit.com/qbsdk-current/common/newosr/index.html

And easily determine that no, iterators are not supported for SalesRepQueryRq.

However, you can fake an iterator by querying for chunks of data. For example, use a NameRangeFilter:

<NameRangeFilter> <!-- optional -->
<FromName >STRTYPE</FromName> <!-- optional -->
<ToName >STRTYPE</ToName> <!-- optional -->
</NameRangeFilter>

To retrieve everything from Aa to Az. Then do the same for Ba to Bz. Then for Ca to Cz, etc. etc. etc. You'll get all the data eventually, but in smaller chunks.

TLDR: Look at the rest of your logs. Next time, post the entire relevant section from the logs. More than likely your code is taking too long and timing out the Web Connectors 2-minute timeout.

Thank you Keith! you were right web connector was timing out - I will implement the iterator using the name range filter to get small subsets of data. — user1185775, Jan 30 '17 at 21:54

score 0 · Answer 2 · edited May 23 '17 at 11:53

Well, this works in SQL-Server:

declare @xml xml=
   '<?xml version="1.0" encoding="utf-8"?>
    <?qbxml version="3"?>
    <QBXML>
        <QBXMLMsgsRq onError="stopOnError">
            <SalesRepQueryRq>
                <ActiveStatus>ActiveOnly</ActiveStatus>
                <FromModifiedDate>2017-01-27T08:30:15</FromModifiedDate>
                <IncludeRetElement>ListID</IncludeRetElement>
                <IncludeRetElement>SalesRepEntityRef</IncludeRetElement>
            </SalesRepQueryRq>
        </QBXMLMsgsRq>
    </QBXML>';
select @xml;

How are you passing this over to MS-Sql-Server? Here's a related answer, which explains possible sources of troubles with encoding and paramter types.

Hint: Let the parameter in SQL-Server be of type NVARCHAR(MAX). I'd start to pass the XML without the <? ... ?> (processing instructions). Cut the first lines off, let this start with the <QBXML>. And make sure that the date you stuff into . $ last . is ISO8601 (2017-01-27T08:30:15).

Within SQL Server you can assign the passed in string to an XML-typed variable.

thanks for response - I pass the xml as parameter to stored procedure - its XML datatype `DECLARE @strXML XML` Is there a way to make sure before I use to make insert/updates in MSSQL - I am using SQL SERVER 2008 R2 — user1185775, Jan 28 '17 at 05:28

Quickbooks PHP Dev Kit Web Connector SalesRep Import

2 Answers2