Toad World® Forums

Simple query in Toad using ODBC returns junk / {null} data


#1

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.


#2

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.


#3

That did fix my issue, thank you!

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.

I noted that a value of 0 also works.


#4

Glad to hear your issue is resolved.

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?


#5

I have 2 Remedy servers using different back end DBs: 1 on Oracle and the other on SQL Server. Both exhibit the issue…


#6

I think you would get better performance if you connected directly to Oracle and SQL Server using our database specific connections and not ODBC.

Debbie


#7

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. :slight_smile:


#8

And I appeared to have answered my own question…

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.

Message was edited by: me!


#9

Glad to hear it is working for you now.

You might want to mention that changing that value in TDA causes it to set the SQL_ATTR_ROW_ARRAY_SIZE statement attribute when executing queries.

Let me know how it goes.

Thanks.