ColdFusion Muse

Tabular Data Stream Error (Who's Using a Cursor Select Method?)

It's not often I see an error that I have simply never seen before. Here's one that some of you may recognize. It happened on a query using SELECT * against a fairly limited table on an MSSQL 2005 database. There wasn't anything unusual about the query (except for the bad form of using the asterisk). Here's the error that was thrown:

rror Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]
The incoming tabular data stream (TDS) remote procedure call (RPC) protocol
stream is incorrect.
Parameter 2 (""""): Data type 0x38 is unknown.
The specific sequence of files included or processed is: ******

The Fix

After scratching my head and consulting my crystal ball I went looking at the "advanced" properties for the data source. What I found was that the "Select Method" for the DB which is usually set to "direct" had been set to "cursor". Setting it back to "direct" cleared up the error. Now, as I understand it, a "cursor" select method (available for MS SQL and Sybase) would allow a developer to work his way through a data set (a cursor) on the DB server - rather than directly selecting all the records into the web server memory. At least that's what I gather.

The problem is that I cannot find any examples of how this is done. One blog post said, "using the cursor select method should only be done by advanced developers". Of course, now I feel like a novice because frankly, I don't know how to do it either. So first, if you see the error above there is an easy fix. Switch from cursor back to the direct select method. Secondly, are there any savvy Muse readers out there with a sample of how a cursor select method might be used? I certainly have enough complex DB code out there to make learning a new technique worth my while :)

Comments
ike's Gravatar Interesting... I dunno... if it causes regular select statements to fail, that would mean you would need 2 DSN's in your app right? One for your regular queries and a separate one for handling cursor-based operations? ... Seems to me like it's just liable to be a big learning curve and not much in the way of a reward at the end...

Granted, I could be way off-base, it could be the wholy grail of database access that nobody ever exploited just out of a lack of awareness, like how most programmers don't use foreign key constraints. :)

I may tinker with it in my dubious spare time... :P

At the moment I've been feverishly trying to get the onTap framework back up onto a public domain (http://on.tapogee.com) and a first version of the plugin service for discovering and installing them within the application (similar to the way you can install extensions directly within Eclipse or Firefox 3). Not to mention finding any and every avenue I can to generate more interest in the framework, like Kay Smoljak's interview series and subtle plugs in the comments on blogs from famous people like Sean Corfield, Mark Kruger or Ray Camden... Did I mention Mark Kruger? ;)
# Posted By ike | 8/14/08 2:09 PM
orangepips's Gravatar Setting the select method on the JDBC driver affects how the SQL is passed to the database. You can see this by running a trace through SQL Server Profiler (under the Performance Tools Menu) and hitting some pages through your browser that have cfquery on them. Then change the JDBC connection setting and do the same again and you will see the same SQL statements being passed differently. So to rephrase this means is that you don't need to write different SQL, but rather the driver sends it differently, based on this setting.
# Posted By orangepips | 8/15/08 9:37 AM
ike's Gravatar So I'm guessing then that this setting only exists to support db servers that are configured in a specific, non-default way?
# Posted By ike | 8/15/08 10:37 AM
Olle Myrberg's Gravatar Thanks for the information, saved my hours of time.
# Posted By Olle Myrberg | 12/4/11 11:23 AM



Blog provided and hosted by CF Webtools. Blog Sofware by Ray Camden.