When issuing an extremely simple query which I know should return a single record, I get 1k records. The first record displayed is correct, second item is blank ("") and then the remaining 998 records all show the value “{null}”.
Query sample:
SELECT “myID” FROM “myTable” WHERE “myID” = ‘000000000000123’
The exact same query via MSQuery returns a single record returned.
Is there a setting in Toad that I’m overlooking? Known bug? I assume the ODBC is OK since MSQuery works as expected.
I am using Toad version 2.5.0.913, connecting to a BMC Remedy server v7.1 via their ODBC driver.
Interesting, try clicking on the advanced tab in the connection properties and set “Block cursor size” to 1. Let me know if that fixes the issue. Thanks.
What specifically does that attribute do? Looks like the default changed from 1 to 1000 in Toad 2.5 (on top of the fact that it didn’t appear to be an ODBC option pre-2.5). All I can tell is that the parameter is used to increase performance of data txfers (like a buffer, i assume). Though it doesn’t make much sense to me why if ‘buffer’ > result set would return garbage.
This setting allows TDA to fetch more than one row at a time thus reducing network traffic and increasing performance. The default is in fact 1000 and had been working for all other drivers. I would suggest checking with the vendor of that ODBC driver and see if there are any reported issues when block cursor size is > 1.
Also, just out of curiosity, what does BMC Remedy use for a database back end?
Yep, direct backend queries are faster. User base does not have this access due to IT restrictions so utility has to operate via ODBC.
Follow-up question: Queries which should return a results > block cursor size will appear to never respond or respond w/ junk data. The workaround of changing BCS=1 breaks queries w/ multiple results.
Its all very odd; it looks like a overrun of sorts…
Sample Query: SELECT ID, User FROM MyTable WHERE User = keeena;
Known (expected) # of records: 722
If Block Cursor Size is very small (1), no results are ever returned and the query appears hung. I’m forced to issue a Stop All Processes in Toad.
If Block Cursor Size is moderate (say, 100) that query will get 72200 (again, BCS having a multiplier effect). What was interesting about this test is that the “junk” data listed in this test was not all {null} data. This time the ID field shows random scattered pieces of hard drive paths…
E.g.
ID User
0000012 keeena
\ O r a c l e
C : \ P r o
i c r o s o f t
P e r l \ s i
[…]
0000013 keeena
\ W I N N T \ s
b N e t \ S u b v
F i {null}
I am going to follow up w/ the App/ODBC developer. Just wondering if there is anything to be said from the Quest side? Again, a basic query tool like MSQuery works fine, which is why i think there may be something quirky w/ Toad itself.
I’m only pursuing this here because I want to like Toad and want to justify purchasing licenses.
Based on what I saw from TOAD’s doc, I thought 1 disabled the groupedfetches and >1 enabled it by a factor equal to the value specified.
When I try to set BCS = 0, everything started working fine. ?? Whats odd is that 0 appears to be an invalid value; changing field focus will reset the field value to 1. But this reconnect operation seemed to fix my problem. I think I have to look at it more.
I am pursuing w/ Remedy to see if they know about inability to handle BCS parameter.
