sql server odbc: I'm reading columns from a table, including an image column. The
image column is not bound because I don't yet know the size of
the BLOB. The problem is that I can't get the ODBC driver to
stop reading the unbound column on SQLFetch(). It appears to
read the BLOB, all 50MB worth and then throw it away. Later, on
an SQLGetData(), it reads the BLOB column again. The statement
I'm using for testing is simply "SELECT MyBlob FROM MyTable".
The table has two rows. The first has a 1000 byte BLOB. The
seconw row has the 50MB BLOB.

Books Online says it won't do this if a server cursor is used.
Elsewhere it gives examples of changing statement attributes
which should result in a server cursor. One of the examples:
B. Allocate a statement handle, set a scrollable, sensitive
cursor, and then execute a SELECT
// Set the cursor options and execute the statement.
retcode = SQLSetStmtAttr(hstmt1, SQL_ATTR_CURSOR_SCROLLABLE,
(SQLPOINTER)SQL_SCROLLABLE,
SQL_IS_INTEGER);
retcode = SQLSetStmtAttr(hstmt1, SQL_ATTR_CURSOR_SENSITIVITY,
(SQLPOINTER)SQL_INSENSITIVE,
SQL_IS_INTEGER);
This hasn't changed the behaviour.

Note that we also support Oracle and DB2 databases and they work
as one would expect. Performance for SQL Server really stinks
because it is reading the BLOB data twice. The 50MB BLOB
mentioned above was contrived for my test. I could step over
SQLFetch(), which took a while, and watch the network lights
glow. In production we might actually be reading 10,000-20,000
BLOBS of up to 100KB or so. Using AQTime we can see equal
amounts of time spent in SQLFetch() and SQLGetData().

Setup is:
W2K3 server
XP/SP2 client
MDAC 2.8
VC++ 8
SQL Server ODBC driver version 2000.86.1830.00

I'd really appreciate and suggestions. Thanks,

- Arnie

Hi Arnie,

Welcome to use MSDN Managed Newsgroup!

From your descriptions, I understood you would like to know how to avoid
duplicate reading BLOB data using SQLFetch() / SQLGetData(). If I have
misunderstood your concern, please feel free to point it out.

I am looking into this issue and will keep you updated as soon as possible.
Thank you for your patience and cooperation.


Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

"Michael Cheng [MSFT]" <v-mingqc@online.microsoft.com> wrote in
message news:pN18a089FHA.832@TK2MSFTNGXA02.phx.gbl...
[quoted text, click to view]

Hello Michael,
Yes, your understanding is essentially correct. The BLOB column
is not bound. However, it is read on SQLFetch() and discarded.
I subsequently determine its size by using GetData() with a
length of zero. Then I provide an appropriately sized buffer and
read the BLOB with GetData().

Thanks,
- Arnie

Hi Arnie,

Please check whether using the SQLFetchScroll() function instead of
SQLFetch() will help you avoid this fetching.

Chapter 20. ODBC Programming - Retrieving More Than One Row at a Time
http://www.samspublishing.com/library/content.asp?b=Visual_C_PlusPlus&seqNum
=184

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!


Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

"Michael Cheng [MSFT]" <v-mingqc@online.microsoft.com> wrote in
message news:bVwV77j%23FHA.832@TK2MSFTNGXA02.phx.gbl...
[quoted text, click to view]

Hi Michael,

Thanks for the suggestion. It appears to work. I'll try to make
a long story short. Well, ...

SQL Server 2000 doesn't allow multiple open (nested) queries on
the same connection unless a server cursor is used. We ran into
trouble with this when porting from Oracle and DB2. We support
all three DB engines with a single executable. We determine the
DB type and modify cache settings and SQL statements as required.
I understand that SQL Server 2005 has 'fixed' this, but our
customers haven't gotten to 2005 yet. So, when going to SQL
Server 2000, I used:

SQLSetStmtAttr( hStmt, SQL_ATTR_CURSOR_SCROLLABLE, (SQLPOINTER)
SQL_SCROLLABLE, 0 );

to force a server cursor, even though we're read only, one pass
through the result set(s). Basically, this is open a query, read
results from that query per row and use them in another query.
Not rocket science.

The bottom line is that now that I have a scrollable cursor, I
can 'legally' use SQLFetchScroll() as:
rc = SQLFetchScroll( hStmt, SQL_FETCH_NEXT, 0 );

I'm sure I will have to conditionally code this based upon DB
type. SQL Server is the only one that gets the scrollable
cursor. So, the lingering question is "Why doesn't SQLFetch()
work as advertised?"

Thanks for your help,

- Arnie

Hi Arnie,

Thanks for your patience.

I setup a sample on my side to try to reproduce it on my side as below

if (retcode == SQL_SUCCESS)
{
while (TRUE)
{
retcode = SQLFetch(hstmt);
if (retcode == SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO)
{
return -1;
}
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
{

/* Get data for columns 1, 2*/

SQLGetData(hstmt, 1, SQL_C_CHAR, szName, NAME_LEN, &cbName);
SQLGetData(hstmt, 2, SQL_C_CHAR, szAge, AGE_LEN, &cbAge);

/* Print the row of data */

} else {
break;
}
}
}

I used Profiler to trace the SQL Server and find there is only one trace in
the Profiler. Would you please generate a sample using Northwind database
that will show this kind of double query of the SQL Server when using ODBC
driver?

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!


Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.

This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/

If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

"Michael Cheng [MSFT]" <v-mingqc@online.microsoft.com> wrote in
message news:zSuLO8K$FHA.3764@TK2MSFTNGXA02.phx.gbl...
[quoted text, click to view]

You're welcome. I appreciate your help.

[quoted text, click to view]
I'm supplying sample code below though it doesn't use Northwind.

I have a table (aem_blob) with an integer column and an image
column. It has two rows. The first has (100, 1KB BLOB). The
second row has (200, big_blob). I change the size of the
big_blob based on where I'm testing: From home over the VPN it's
5MB, from work over the LAN it's 50MB. This lets me 'feel'
what's happening as I step over the fetches in the debugger. An
SQLFetch() on the second row takes quite a while.

Please let me know if you need any more info.

// ODBCBLOB.cpp : Defines the entry point for the console
application.

//

#include "stdafx.h"

#include <string>

using namespace std;



int main(int argc, char * argv[])

{

SQLHENV hEnv;

SQLHDBC hDbc;

SQLHSTMT hStmt;

// Allocate an environment handle

SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, & hEnv );

// We want version >= 3.0

SQLSetEnvAttr( hEnv, SQL_ATTR_ODBC_VERSION, (void*) SQL_OV_ODBC3,
0 );

// Allocate a ODBC connection handle

SQLAllocConnect( hEnv, & hDbc );

SQLRETURN rc;

// Connect to the DB

// The default catalog is what we're looking for

rc = SQLConnect( hDbc, (SQLCHAR *) "DVSQLSCB", SQL_NTS,

(SQLCHAR *) "mx", SQL_NTS, (SQLCHAR *) "expert", SQL_NTS );

// Create the statement handle

rc = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, & hStmt );

// Force a server cursor

SQLSetStmtAttr( hStmt, SQL_ATTR_CURSOR_SCROLLABLE,

(SQLPOINTER) SQL_SCROLLABLE, 0 );

// Prepare the statement and build the Fields collection

rc = SQLPrepare( hStmt, (SQLCHAR *) "SELECT aBlob FROM aem_blob",
SQL_NTS );

// Open the query and position to the first row of the result set

rc = SQLExecute( hStmt );

// NOTE: *** The BLOB column is not bound ***

// Read the first row with the small BLOB - instant

// SQLFetchScroll() works properly

rc = SQLFetch( hStmt );

// rc = SQLFetchScroll( hStmt, SQL_FETCH_NEXT, 0 );

// Read the row with the big BLOB - long delay

rc = SQLFetch( hStmt );

// rc = SQLFetchScroll( hStmt, SQL_FETCH_NEXT, 0 );

long valLen = 0;

unsigned char * blob = new unsigned char[5000000];

// Read the data with SQLFetchScroll, read the data AGAIN

// when using SQLFetch().

rc = SQLGetData( hStmt, 1, SQL_C_BINARY,

(SQLPOINTER) ((char *) (blob)),(SQLINTEGER) 5000000,

(SQLLEN *) &valLen );



rc = SQLFreeStmt( hStmt, SQL_CLOSE );

rc = SQLFreeHandle( SQL_HANDLE_DBC, hDbc );

rc = SQLFreeHandle( SQL_HANDLE_ENV, hEnv );

return 0;

}

"Michael Cheng [MSFT]" <v-mingqc@online.microsoft.com> wrote in
message news:UryDRA$$FHA.1240@TK2MSFTNGXA02.phx.gbl...
[quoted text, click to view]

You're more than welcome. I'm sure you have other things to do
as well. I appreciate your help.

- Arnie

Hi Arnie,

The code seems to be OK, I am setuping a table contains BLOB and test on my
side. I will keep you updated as soon as possible about the result.

Thanks for your patience, once more.


Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Hi Arnie,

I have consulted development team and was told SQLFetch/SQLFetchScroll will
return all bound columns in user's buffer. The BLOB column bound before
calling SQLFetch. If not bound, the BLOB data will be read from wire and
thrown away by driver when SQLFetch/SQLFetchScroll is called. Calling
SQLGetData on the BLOB column causes driver to retrieve the BLOB data again
from backend using a server cursor.

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!


Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

"Michael Cheng [MSFT]" <v-mingqc@online.microsoft.com> wrote in
message news:FlxRfiUAGHA.832@TK2MSFTNGXA02.phx.gbl...
[quoted text, click to view]

The description you provided for SQLFetch() is correct. The BLOB
column is not bound. The data is retrieved and discarded.

If I set scrollable cursor and then use SQLFetchScroll() the data
is NOT retrieved on the fetch. I think what we probably need is
clarification in the documentation.

Thanks for your help Michael.

- Arnie

Hi Arnie,

Thanks for you feedback, I have submitted your feeback to our development
and document team via internal way.

In the meantime, you are welcome to send this feeling via the link
http://lab.msdn.microsoft.com/productfeedback/default.aspx

I am sorry for any incevnience it might cause to you. Thank you for your
patience and cooperation, once more


Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.