Tom,

I have written code in Foxpro that will take Custom Pricing Level information from our database and synchronize the PriceLevel and PriceLevelPerItem tables in each of our QB files to it.  I followed your examples elsewhere in the forums (thank you!) on how to add a new PerItem price level and then add consecutive PerItem records to that price level and my code works great.

The problem comes once it starts INSERTing quantities of PriceLevelPerItem records.  I did a sample run with 4000 PerItem custom Prices in one PerItem PriceLevel. On the first INSERTS it takes a second or so to do the INSERT, which is fine.  By the time 500 INSERTS had occurred it was taking 16 seconds per INSERT.  At 700 it was at 22 seconds per INSERT and I stopped it.  If this is linear it will take 5 minutes per INSERT at the end of one PriceLevel. Eventually I will have 20 or more levels with 10,000 PerItem prices each so this is not good!

So my question is, is there a better way to handle it to speed things up???

This is the statement I use right now:

iSQLRetVal = SQLEXEC(cwu_oQODBCConnection.iConnectionHandle,;
                                           "INSERT INTO PriceLevelPerItem (ListID, PriceLevelPerItemItemRefListID, PriceLevelPerItemCustomPrice) " +;
                                           "VALUES " +;
                                           "(?cQBPriceLevelListID, ?cQBItemListID, ?nPerItemCustomPrice)",;
                                           cSQLCursor, aCountInfo)

This is the statement I use for inserting PerItem prices where the PerItem PriceLevel already is established.   The code sets the required variables to INSERT a single PerItem record, INSERTS it, then does another, and so on.

I have the optimizer off for this and I assume the optimizer has nothing to do with INSERT speed.  Is there a way to bulk INSERT from a local table, or possibly batch it???

Thank you as always for your help...

Paul

Why in the world would they require that??????????  So you are telling me that for every insert QODBC has to read the whole PriceLevelPerItem list, add the entry and put it back????

Any possible way around this limitation?

Does it handle updates to PriceLevelPerItem the same way????

Tom,

I am not very familiar with the Intuit qbXML interface, but since you have to read the whole PriceLevel table and put it back to do an Insert, is there any way that I could use the interface directly to write a bunch of Price Level items at once???

Can we pay you guys to modify your code to suit this end???

Paul

Tom,

Could you also place a warning on the Data Layouts section for PriceLevelPerItem indicating what the SDK has you do here and how unusable it will be for any reasonable amount of records?  I just spent a month writing this huge chunk of code and I assumed that if individual records wrote to the system and could be read it would act like all the other tables.  All that code I wrote is worthless until QB gets off their butts and does the interface implementation correctly.  If I would have known I could have tested to see if the speed was workable and would have abandoned the project.  Now I have a bunch of people enraged at me thinking I should have realized this ahead of time.

If you put a warning up it will at least stop others from this fate.

Paul

Actually we do have a batch mode that can help here:

QODBC Batch Stored Procedures

These allow you to start a batch for a given table and all insert/updates will be queued until the sp_batchupdate command is issued. This allows for fewer round trips to QuickBooks which increases performance when doing large transfers or recored from external systems (like FileMaker Pro) to QuickBooks.

The sp_lastinsertid stored procedure will return the ListID/TxnID plus an error message column for every row added to the batch.

Note: Each batch is limited to 500 transactions.

SP_BATCHCLEAR tablename - Clears any cached batch statements for the given table. This is an execute query and does not return a recordset.

Example: sp_batchclear Customer

See: Are there any special stored procedures that can assist me in programming? for more.

Tom,

So if I use batch updates with PriceLevelPerItem will QODBC perform like A or B???

A: QODBC will extract the current set of PriceLevel data from QB and then insert all batched INSERTS (up to 500) at once and THEN write the whole mess back to QB.  (So in this case just one read, multiple inserts into the extracted data, and one writeback of all data)

B: QODBC reads all data, INSERTS ONE PerItem record, and then writes it all back to QB. It repeats this read all - insert one - write all on all batched inserts until done.

I hope your answer is A!

Paul

Tom,

I spent this morning working on batching the PriceLevelPerItem stuff and have run into some dead-ends, I sure hope you can help me here.

ATTEMPT #1:
===========

I used the following commands sent to QODBC.  This is just the core of the code for brevity:

SP_BATCHSTART PriceLevelPerItem

Then I loop through 10 Inserts with different Item ListIDs:

  INSERT INTO PriceLevelPerItem (ListID, PriceLevelPerItemItemRefListID, PriceLevelPerItemCustomPrice) VALUES (?cQBPerItemPriceLevelListID, ?iCurrentItemIDHolder, 99.99000)"

to finish off

SP_BATCHUPATE PriceLevelPerItem

The result I get from this is a Fatal Exception on the "SP_BATCHSTART PriceLevelPerItem" line every time from a file called FQQB32.DLL: "Unhandled win32.exe exception [5944]"

So from that I assumed that since the QB API read and write is actually from PriceLevel and NOT PriceLevelPerItem I then modified it for attempt 2:

ATTEMPT #2:
===========

SP_BATCHSTART PriceLevel

Then I loop through 10 Inserts with different Item ListIDs:

  INSERT INTO PriceLevelPerItem (ListID, PriceLevelPerItemItemRefListID, PriceLevelPerItemCustomPrice) VALUES (?cQBPerItemPriceLevelListID, ?iCurrentItemIDHolder, 99.99000)"

to finish off

SP_BATCHUPATE PriceLevel

No errors on this one and nothing gets written until the SP_BATCHUPDATE is called - just as I would expect.  However, only the very first insert is actually written to the PriceLevelPerItems, all the others are ignored.  If I run it again and start at the second item in the list (the first one got written so I don't want to re-INSERT that) and run through to the last item, again only the first INSERT gets written at the end.

Any ideas on what is happening here????

Thank you,

Paul

The connection cannot be broken during the batch, but to check everything still works correctly, I ran the following sequence using VB Demo. First, I created a created a new PriceLevel and PriceLevelPerItem item (at the same time) by doing:

INSERT INTO PriceLevelPerItem ("Name","IsActive","PriceLevelPerItemItemRefFullName",
"PriceLevelPerItemCustomPrice")
values ('Door Special', 1,'Door Frame',20)

After executing the SQL insert statement a new PriceLevel was created:

SELECT ListID, Name, PriceLevelType from PriceLevel where Name='Door Special'

1 80000006-1197758039 Door Special PerItem

Having created the new PriceLevel and found the ListID, I preformed the following BATCH inserts:

SP_BATCHCLEAR PriceLevelPerItem

SP_BATCHSTART PriceLevelPerItem

INSERT INTO PriceLevelPerItem ("ListID","IsActive","PriceLevelPerItemItemRefFullName",
"PriceLevelPerItemCustomPrice")
values ('80000006-1197758039', 1,'Hardware:Brass hinges',5.50)

INSERT INTO PriceLevelPerItem ("ListID","IsActive","PriceLevelPerItemItemRefFullName",
"PriceLevelPerItemCustomPrice")
values ('80000006-1197758039', 1,'Hardware:Doorknobs Std',35.50)

INSERT INTO PriceLevelPerItem ("ListID","IsActive","PriceLevelPerItemItemRefFullName",
"PriceLevelPerItemCustomPrice")
values ('80000006-1197758039', 1,'Hardware:Lk Doorknobs',85.75)

INSERT INTO PriceLevelPerItem ("ListID","IsActive","PriceLevelPerItemItemRefFullName",
"PriceLevelPerItemCustomPrice")
values ('80000006-1197758039', 1,'Wood Door',195)

SP_BATCHUPDATE PriceLevelPerItem

sp_lastinsertid PriceLevelPerItem

While sp_lastinsertid failed to show any records were added (at least there were no errors), my PriceLevelPerItem items were sucessfully added as can be seen here:

Select * from PriceLevelPerItem where Name='Door Special'

Tom,

The only thing different on my code was that I did not have a SP_BATCHCLEAR command before going ahead.  I put that in but the same situation stands:

EVERY time I send this command:

Foxpro crashes from a C000005 fatal exception from an error thrown from FQQB32.dll.  The batching works perfectly on any other table I have tried it on, but when I put in PriceLevelPerItem it crashes every single time.

Now, after researching this a bit I find the Foxpro has a history of failing with the 0xC0000005 error - it seems to be the catch-all "there was something I didn't like so I am quitting" error for Foxpro.  The only other thing I know is that the QODBC dll FQQB32.dll is somehow causing this but ONLY on SP_BATCHSTART PriceLevelPerItem.

I know you can't debug this stuff for every language and Foxpro is definitely a 'fringe' development system, so is there any course of action we can take here to at least check this out??? For example, can we pay your programmer(s) to spend a few hours to see if there is something unusual in that particular command that could be disturbing Foxpro????

Any suggestions would be greatly appreciated!

Paul

SP_BATCHSTART PriceLevelPerItem doesn't return anything.

Try doing:

* Connect to an ODBC data source
LOCAL nHnd
nHnd = SQLCONNECT ("QuickBooks Data")

* Retrieve data from the remote server and stores it in
* a local data cursor
nResult = SQLEXEC (nHnd, "SP_BATCHSTART PriceLevelPerItem", "QPRICEITEM")

* Close the connection
SQLDISCONNECT(nHnd)

Tom,

I tried the form of the SQLEXEC statement you showed and tried all permutations of optional parameters and retval and I keep getting that same 0xC0000005 fatal error.  Foxpro just doesn't like something about the way SP_BATCHSTART PriceLevelPerItem is handled.

Thanks for the attempts though,

Paul

You are correct, I tested MS Access SQL Pass-Through Queries with both return records set to "Yes" and "No" and get the same exception error:

I then ran the following SQL Statement sequence using VB Demo without a problem:

BatchStart
sp_batchstart InvoiceLine

BatchInsert1
INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID", "FQSaveToCache")
VALUES ('AC0000-1197757899', 'Batch1', '250000-933272656', 'Bin Permit Renovations',
200.00000, 200.00, '20000-999022286', 0) 

BatchInsert2
INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID", "FQSaveToCache")
VALUES ('AC0000-1197757899', 'Batch2', '250000-933272656', 'Bin Permit Renovations',
200.00000, 200.00, '20000-999022286', 0) 

BatchUpdate
sp_batchupdate  InvoiceLine

This problem has been escalated to Level 2 Support with the QODBC Software Engineer under Ticket ID: BR00000015. No resolution time frame is available at this point of time. 

So it appears that the SPT technology is possibly common between Visual Foxpro and Access.  Hopefully it is an easy and quick fix!

Thanks for your help Tom!

The batch insert works like magic!! It's GREAT!

That tiny price level per item window QuickBooks has is so frustrating. With well over a thousand items, going down through that list is tedious.

Just got done entering a bunch of new prices for a new contract. I created a form that lists all the items and by entering a search word, I can filter the list for certain items so I'm not scrolling down through over a thousand items to find the one I'm looking for.

Have I told you lately how much I love QODBC?

Oh, and if you try to insert a price level item that already exists, it doesn't matter! I don't know what happens, if it's ignored or re-written, but nothing bad happened. Woo hoo!