topic QuickBooks QODBC Driver Hanging in QlikView

QuickBooks QODBC Driver Hanging

asmithids — Sun, 06 Jul 2014 15:47:18 GMT

Hello,

I am using the QODBC.com Tools for QuickBooks driver on QV Designer 11.20, SR6. I setup the QODBC driver (see screen shots 2 & 3). I am able to query data using the VB Demo 32 utility (see screen shot 4). However, when I reload the data into QV, the reload hangs (see screen shot 1). Clicking on "End Here" or "Abort" does not close the reload window. I need to use Task Manager to shutdown QV because QV does not respond at all.

Thank you in advance for any help on this issue.

Re: QuickBooks QODBC Driver Hanging

rbecher — Sun, 06 Jul 2014 18:26:16 GMT

Hi Alec,

maybe it's related to the optimizer which stores data locally. You could try to disable this option (screenshot 3) and run again.

- Ralf

Re: QuickBooks QODBC Driver Hanging

asmithids — Mon, 07 Jul 2014 04:09:37 GMT

Thank you Ralf.

I tried disabling the Optimizer, but that did not resolve the hanging. What doesn't make sense to me is that QV is fetching all of the data in the load script. However, it's just not closing the function when the reload is finished.

Re: QuickBooks QODBC Driver Hanging

Mon, 07 Jul 2014 16:14:59 GMT

hi alec

in situations like these with software programs which are new to us, we typically suggest you use middleware to solve the problem of compatibility and get you up and running as quickly as possible:

http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2570/0/how-to-use-ms-access-as-middleware-with-qodbc

if this does not help get you moving, please post a support ticket at our support board support.qodbc.com

thanks!

Re: QuickBooks QODBC Driver Hanging

Tue, 08 Jul 2014 09:25:20 GMT

Hi Alec,

Please refer

How to use QlikView with QODBC - QODBC Support board

Regards

Raj

Re: QuickBooks QODBC Driver Hanging

asmithids — Tue, 08 Jul 2014 20:25:46 GMT

All,

I got the QODBC working by NOT including all of the fields per table. When I limit the number of fields per table to just want is needed, the reload works and completes without hanging Designer. The interesting thing is that the number of records I am loading from these tables are less than 10k.

I hope this helps others having similar issues.

Re: QuickBooks QODBC Driver Hanging

rbecher — Tue, 08 Jul 2014 22:33:43 GMT

Interesting, so this confirms never to use SELECT * ... ?

Re: QuickBooks QODBC Driver Hanging

Tue, 08 Jul 2014 23:13:26 GMT

Hello Alec,i know you got it resolved but it could be a question of workload on your server ,since you not writing any script .I had encounter something similar today and had to reload and still got the error message 'end script' or 'abort '. My Qlikview 11 application was relaunched before i could close the previous page.Thanks

Re: QuickBooks QODBC Driver Hanging

Wed, 09 Jul 2014 05:33:50 GMT

Hi guys

it's useful to note that although most people assume an ODBC Driver will be

connected directly to a back end database, this is not the case with QODBC

which is a wrapper on the QuickBooks application itself and it's SDK

interface.

The only way to get data in and out of QuickBooks is to follow all of the

accounting rules in the application layer, which is why QODBC exists, to

make this SDK interface more standard and easier to use in various front

end software packages.

Therefore QuickBooks as a source will be not as robust as a back-end

database and certain large queries will strain the memory usage of

QuickBooks which is returning all of this data to us in XML form. Smaller

is better, so using less fields and limiting the date range of single

queries will produce superior results. thanks!

brad

On Wed, Jul 9, 2014 at 7:14 AM, Ola Odusanya <qcwebmaster@qlikview.com>

Re: QuickBooks QODBC Driver Hanging

rbecher — Wed, 09 Jul 2014 07:27:35 GMT

This "Howto" post say nothing to the matter here... They should have point out NOT to use SELECT * .. !!

Re: QuickBooks QODBC Driver Hanging

asmithids — Wed, 09 Jul 2014 11:46:03 GMT

Hi Ralf,

You are correct. Avoid using SELECT * .....

Bradley made the point very clear in his post below.

Thank you Bradley!

Re: QuickBooks QODBC Driver Hanging

asmithids — Wed, 09 Jul 2014 12:06:34 GMT

Hi Ola,

Server workload is possible. For example, I was able to reload all of the fields from the Customer table (i.e. SELECT *...). However, pulling all of the fields from the Invoice table hung up Designer. So the issue is not consistent. I typically only load the fields that I need. However, I was beginning to build the data model and just thought to initially throw in all of the fields before reducing the data model to just the required data. Going forward, I will be mindful of QODBC limitations.

Re: QuickBooks QODBC Driver Hanging

Wed, 09 Jul 2014 13:31:25 GMT

Alec

Technically the lock-ups are Qlik limitations - QODBC can "select *" for

every table as you can see in our VBDEMO test program and in MS Access. Our

goal is to work as well as possible with any tool our customer wishes to

use. thanks!

brad

Re: QuickBooks QODBC Driver Hanging

Mon, 14 Jul 2014 23:23:49 GMT

Hello Alec, so its obviously putting QODBC limitations into consideration as you have mentioned .Many thanks to you all for the great tips and contribution .

Kind Regards

Ola.

Re: QuickBooks QODBC Driver Hanging

Anonymous — Tue, 15 Jul 2014 20:18:31 GMT

Hi Alec,

Could you explain how to script a where clause with QODBC which would help in reducing the data set to just required.

For example I am trying to only load 24 months of data from the SalesOrder table and possibly only the records modified within the last 24h:

SELECT

TxnID,

TimeModified,

CustomerRefListID,

ShipDate

FROM SalesOrder

WHERE ShipDate >= AddMonths(Today(),-24)

AND TimeModified >= Today()-1 ;

I have tried many options but can't figure it out.

Thanks

Re: QuickBooks QODBC Driver Hanging

asmithids — Fri, 18 Jul 2014 19:28:18 GMT

Hi Benoit,

Please try this select statement. I suspect the Today() call is not passing through the QODBC. I would recommend simplifying the WHERE clause to a point that you are getting the desired reduced data.

Select * from SalesOrder Where ShipDate >= {d'2014-01-01'}

If the above select statement returns all shipments on or after 0/1/2014 then try adding back the Today() function.

I would also suggest opening a free support case with QODBC at Select a department - Powered by Kayako Resolve Help Desk Software

Re: QuickBooks QODBC Driver Hanging

Anonymous — Mon, 21 Jul 2014 21:24:38 GMT

Thanks Alec.

I actually figured it out on my own and with the help of this forum:

http://www.qdeveloper.com.au/forum.php?homeinclude=forum&forum_id=1

This is what I was looking for:

SELECT *

FROM SalesOrderLine

// records modified in the last 24h

WHERE TimeModified >= {fn TIMESTAMPADD(SQL_TSI_MINUTE, -1440, {fn NOW()})}

// Items shipped within past 24 months

AND ShipDate >= ({fn CURDATE()}-720);

Cheers,