Member Login

Username
Password
Forget Password
New Sign Up
Search Forum

Buy Support
Incidents

If you can't find your answer in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us private or confidential information:

Click Here
If you can't login and post questions or you are having trouble viewing forum posts:
 Click Here
Callback
Support

If you live in USA, UK, Canada, Australia or New Zealand, you can leave us details on your question and request us to call you back and discuss them with you personally  (charges apply).

Click Here
 
Buy Support
Incidents
If you can't find your answer in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us private or confidential information:
Click Here

Forum : QuickBooks 2007 SQL works QuickBooks 2008 sql not workingSearch Forum

Forum Home > QODBC - ODBC Driver for QuickBooks > QODBC SQL Support Forum

 New Topic 
 
 Post Reply 
[1]  
 QuickBooks 2007 SQL works QuickBooks 2008 sql not working 
 Author   Message 
  mwissbaum 
  
 Group: Members 
 Posts: 5 
 Joined: 2007-02-21 
 Profile
 Posted : 2008-01-17 16:03:10

To all.  This SQL statement works in 2007 Premier but does not work in 2008 Premier.

select fullname, salesorpurchaseprice as price, listid, description from item where isactive <> 0 and {fn LCASE(FullName)} in ('b','bb','bt','sar','sbr')

I tested with the vb demo and the function is the issue.  The function will work before the where clause in 2008 but not as part of the where clause.

Please advise.

Thanks,

Mike

 

 
Michael D. Wissbaum 
 
  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2008-01-18 09:00:36

I don't understand what the problem or error is here. The SQL statement itself runs fine using QuickBooks 2008 for me.

 

  Top 
  mwissbaum 
  
 Group: Members 
 Posts: 5 
 Joined: 2007-02-21 
 Profile
 Posted : 2008-01-18 12:01:30

Tom,

Sorry for the confusion.  I didn't adequately explain the situation.  The query executes but fails to return rows.  Rows exist that meet the criteria.  Using the 2007 and QODBC 7 versions I get results from this company file (different machine).  If I remove the function the query returns rows.  However, the customer entered their data in lower, upper, and mixed case (not consistent).  Using the lcase statement to make the fullname lower case and using lower case in the predicates of the where clause would ensure matches could be found.  Would logs images of versions etc. be useful?  I couldn't see a way to attache a file in the forum.  FYI using the function in the select portion seems to work correctly, just not in the where clause.

Thanks again,

 

 
Michael D. Wissbaum 
 
  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2008-01-18 12:27:09

Again, there's no problem that I can see, if I use the correct values from my ITEM table, I get the correct results:-

 

  Top 
  mwissbaum 
  
 Group: Members 
 Posts: 5 
 Joined: 2007-02-21 
 Profile
 Posted : 2008-01-19 02:23:49

Tom,

I copied and re-converted the qbw from 2007 version to 2008.  I then ran the tests again.  I still cannot use the function in the where clause.  Here is what I see in logs:

2008-01-17 17:20:51 QODBC Ver: 8.00.00.240 *********************************************************************************************************************

IsAService: False

Error Code: 1

SQLOptimize_ExecuteNonQuery prepare:

CREATE TABLE "item" AS SELECT "item"."fullname" AS "FQALIAS_1" FROM "Item" WHERE ((CAST("item"."isactive" AS INTEGER) <> 0) AND (LOWER, "item"."fullname") IN ('b', 'bb')))

2008-01-17 17:20:51 QODBC Ver: 8.00.00.240 *********************************************************************************************************************

IsAService: False

Error Code: -1

SQLOptimize_SQLOpen create table:

CREATE TABLE "item" AS SELECT "item"."fullname" AS "FQALIAS_1" FROM "Item" WHERE ((CAST("item"."isactive" AS INTEGER) <> 0) AND (LOWER, "item"."fullname") IN ('b', 'bb')))

20080117.171920 I 1580 RequestProcessor ========= Started Connection =========

20080117.171920 I 1580 RequestProcessor Request Processor, QBXMLRP2 v7.0

20080117.171920 I 1580 RequestProcessor Connection opened by app named 'FLEXquarters QODBC'

20080117.171920 I 1580 CertVerifier The Authenticode signature validated OK.

20080117.171920 I 1580 RequestProcessor Opening the file in the DoNotCare mode.

20080117.171921 I 2916 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 1580).

20080117.171921 I 2916 SpecVersion Current version of qbXML in use: 7.0

20080117.171921 I 2916 QBSDKMsgSetHandler QUERY: Host

20080117.171921 I 2916 QBSDKMsgSetHandler Request 1 completed successfully.

20080117.171921 I 2916 QBSDKMsgSetHandler QUERY: Company

20080117.171921 I 2916 QBSDKMsgSetHandler Request 2 completed successfully.

20080117.171921 I 2916 QBSDKMsgSetHandler QUERY: DataExtension Definition

20080117.171921 I 2916 QBSDKMsgSetHandler Request 3 completed successfully.

20080117.171921 I 2916 MsgSetHandler Finished.

20080117.171921 I 2916 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 1580), ret = 0.

20080117.171924 I 2916 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 1580).

20080117.171924 I 2916 SpecVersion Current version of qbXML in use: 7.0

20080117.171924 I 2916 QBSDKMsgSetHandler QUERY: Customer Job

20080117.171924 I 2916 CustomerStorage::DoQuery Setting iterator chunk size to 00000500

20080117.171924 I 2916 QBSDKMsgSetHandler Request 1 completed successfully.

20080117.171924 I 2916 MsgSetHandler Finished.

20080117.171924 I 2916 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 1580), ret = 0.

20080117.171925 I 2916 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 1580).

20080117.171925 I 2916 SpecVersion Current version of qbXML in use: 7.0

20080117.171925 I 2916 QBSDKMsgSetHandler QUERY: List Delete

20080117.171925 I 2916 QBSDKMsgSetHandler Request 1 completed successfully.

20080117.171925 I 2916 MsgSetHandler Finished.

20080117.171925 I 2916 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 1580), ret = 0.

20080117.172050 I 2916 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 1580).

20080117.172050 I 2916 SpecVersion Current version of qbXML in use: 7.0

20080117.172050 I 2916 QBSDKMsgSetHandler QUERY: Item

20080117.172050 I 2916 ItemsStorage::DoQuery Setting iterator chunk size to 00000500

20080117.172050 E 2916 ItemServiceStorage::BuildTheRetObject This feature is not enabled or not available in this version of QuickBooks. HRESULT=0x80040527

20080117.172050 I 2916 QBSDKMsgSetHandler Request 1 completed successfully.

20080117.172050 I 2916 MsgSetHandler Finished.

20080117.172050 I 2916 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 1580), ret = 0.

ISAMNextRecord

0x0030CA00 [ISAM]

0x070A4008 [item]

Optimizer Passthrough: TRUE

Optimized SQL 1: SELECT "item"."fullname" AS "FQALIAS_1" FROM "Item" WHERE ((CAST("item"."isactive" AS INTEGER) <> 0) AND (LOWER, "item"."fullname") IN ('b', 'bb')))

Optimized SQL 2: SELECT "ROWID", "FQALIAS_1" FROM "Item"

Optimize SQL:

SELECT "item"."fullname" AS "FQALIAS_1" FROM "Item" WHERE ((CAST("item"."isacti

ve" AS INTEGER) <> 0) AND (LOWER, "item"."fullname") IN ('b', 'bb')))

Optimize SQL2:

SELECT "ROWID", "FQALIAS_1" FROM "Item"

2008-01-17 17:20:51 QODBC Ver: 8.00.00.240 *********************************************************************************************************************

IsAService: False

Error Code: 1

SQLOptimize_ExecuteNonQuery prepare:

CREATE TABLE "item" AS SELECT "item"."fullname" AS "FQALIAS_1" FROM "Item" WHERE ((CAST("item"."isactive" AS INTEGER) <> 0) AND (LOWER, "item"."fullname") IN ('b', 'bb')))

2008-01-17 17:20:51 QODBC Ver: 8.00.00.240 *********************************************************************************************************************

IsAService: False

Error Code: -1

SQLOptimize_SQLOpen create table:

CREATE TABLE "item" AS SELECT "item"."fullname" AS "FQALIAS_1" FROM "Item" WHERE ((CAST("item"."isactive" AS INTEGER) <> 0) AND (LOWER, "item"."fullname") IN ('b', 'bb')))

ISAM_EOF S0000 S0000 [QODBC] No data found

ISAMNextRecordExit

I don't get this line if I don't use the function:

20080117.172050 E 2916 ItemServiceStorage::BuildTheRetObject This feature is not enabled or not available in this version of QuickBooks. HRESULT=0x80040527

In this test 2008 environment I am using a trial version of QODBC and a not for resale version of Quickbooks.  Could either of these be a factor?  Also, I am running this environment on Server 2003 R2 64bit.

I wish mine worked like yours, then it wouldn't be a mystery.

Thanks,

 

 

 
Michael D. Wissbaum 
 
  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2008-01-19 07:59:26
Sorry, we don't support any 64bit environments. 

  Top 
 New Topic 
 
 Post Reply 
[1]  

Jump to