10

I am using the trial version of QuickBooks Online ODBC (QODBC) driver and configured the DSN named QuickBooks Online Data as well as QuickBooks Online Data QRemote

I have ensured the connection using the test tool and I am able to execute the SQL queries properly using the mentioned test tool and C# code.

Please find some of the example queries that works fine:

select * from Account

select account.TimeCreated,account.TimeModified, TaxRate.ListID from account
left outer join TaxRate on account.ListID = TaxRate.ListID

select AVG(RateValue),SUM(RateValue) from TaxRate

select Top 5 ListID,RateValue from TaxRate

While trying to execute the subqueries in the test tool, I am facing the following issue.

Subquery used:

select ListID from (select ListID, TimeCreated from Account)

Exception: ERROR [42S00] ExecDirect Packet Header - Received Error:10054, An existing connection was forcibly closed by the remote host. This normally results if the peer application on the remote host is suddenly stopped, the host is rebooted, the host or remote network interface is disabled, or the remote host used a "hard close"

ERROR [42S00] Error Invalid Socket. Either socket is closed, or the socket reference is invalid. For more information please visit: qodbc.com/links/invalidsocket

I have tried the suggested solution in the error message, still, the issue is not resolved.

Also, tried the subquery execution in the c# console application as well and faced the same issue.

Anyone, please let me know the possible solution to resolve this issue?

Venkat
  • 2,549
  • 2
  • 28
  • 61
Janakiraman
  • 121
  • 4
  • `select ListID from Account` any reason this won't work? And for the actual issue, Can you paste your repo code to get better visibility, and you can try EF too. @Janakiraman – Sumit raj Mar 31 '22 at 05:17
  • @Sumitraj `select ListId from Account` **should** work, because the `...left outer join...` statement works `on account.ListID = TaxRate.ListID` (according to the examples presented) – user51187286016 Mar 31 '22 at 07:50
  • 1
    ***select ListID from Account*** can execute this query/works fine @Sumitraj – Janakiraman Mar 31 '22 at 12:09

3 Answers3

0

Hopefully this helps:

select tmp.ListID from (select ListID, TimeCreated from account) as tmp

(Not sure about the QODBC syntax, so I went for plain SQL)

Edit:

Could be also that the QODBC system does not support full SQL syntax, so nested selects will just fail (with a very strange error message indeed - but programmers are notoriously bad at writing helpful error messages). You can test this quickly with the simplest nested select possible:

select * from (select 1)

or:

select * from (select 1) as tmp

If both fail, it means the SQL interpreter on the other end of your request is subpar. The workaround would be to just "rephrase" your select statements in a way that avoids this unsupported syntax. I could help with that if I'd know what is the actual request.

user51187286016
  • 256
  • 1
  • 5
0

I'm not 100% sure on this, but this may be that the ODBC backend being used here (QuickBooks Online) only supports a very limited sub-set of the SQL standard.

You can see the query syntax supported here:

What seems relevant is to note that QuickBooks Online does not support subqueries AT ALL.

I wonder if QODBC basically just passes the raw SQL through to QuickBooks... and QuickBooks doesn't support it, so it crashes/errors?

Keith Palmer Jr.
  • 27,666
  • 16
  • 68
  • 105
0

The connection was forcibly closed during the execution of subqueries in the QuickBooks Online ODBC driver connection. This issue could be related to a timeout, network instability, or a problem with the ODBC driver itself. Ensuring the latest driver version, checking network connections, or adjusting timeout settings may resolve the problem.

Muhammad Atif
  • 1