Getting the actual length for columns in a result set

Is there a way to get the length (i.e. number of characters, bytes ...
) in the metadata information for a resultset?
I'm using low level ODBC calls, and my problem is that both
SQLDescribeCol and SQLColAttribute return the column size for the
length. This doesn't sound bad, but when reading the data I need to
have allocated a buffer big enough to hold the data.
More specifically, if I'm using the Text datatype and the result set
contains the text "hello" the size will always = 2^31. Does anyone
know of a low level ODBC call which would return 5, prior to fetching
the data?
Thanks in advance, -geoffThe problem is that the length in the metadata describes the maximum length
of the column. A varchar(5) column will report 5 when you call
SQLDescribeCol(). The metadata for a column is the same for every row in
the table (instance of the column). What you are interested in is the
length of the actual data in the column (or the length of a particular
instance of the column). This information is considered part of the actual
data in the row, rather than part of the column's metadata.
For non-BLOB columns, you typically want to allocate a buffer that can hold
the maximum length of the column (sure there are exceptions). For BLOB
columns you really should use SQLGetData() (rather than SQLBindCol()). If
you call SQLGetData() with a NULL TargetValuePtr parameter, but a valid
StrLen_or_IndPtr parameter, then SQLGetData() will return the length of the
data that is available for the column. Then you can use that value to
allocate a buffer and call SQLGetData() again to read the column.
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
"Geoff" <geoffpollard@.go.com> wrote in message
news:595a2518.0402111842.698363b7@.posting.google.com...
> Is there a way to get the length (i.e. number of characters, bytes ...
> ) in the metadata information for a resultset?
> I'm using low level ODBC calls, and my problem is that both
> SQLDescribeCol and SQLColAttribute return the column size for the
> length. This doesn't sound bad, but when reading the data I need to
> have allocated a buffer big enough to hold the data.
> More specifically, if I'm using the Text datatype and the result set
> contains the text "hello" the size will always = 2^31. Does anyone
> know of a low level ODBC call which would return 5, prior to fetching
> the data?
> Thanks in advance, -geoff|||I suspected there was another way. However, SQLGetData seems to fail
when I pass a NULL TargetValuePtr. The SQLRETURN == -1, and the
SQLGetDiagRec states "Invalid argument value". Here is a code snippet:
SQLRETURN sql_status_code;
SQLINTEGER namelen;
for (int i=0;i<numColumns;i++)
{
// fails
sql_status_code =
SQLGetData(hstmt,i+1,SQL_C_CHAR,NULL,0,&namelen);
// works
SQLCHAR test[255];
sql_status_code =
SQLGetData(hstmt,i+1,SQL_C_CHAR,test,255
,&namelen);
}
Prior to this the following statements returned SQL_SUCCESS:
SQLAllocHandle(SQL_HANDLE_STMT, hdbc, hstmt);
// need to use server-side cursors to handle multiple queries
SQLSetStmtAttr(hstmt,SQL_ATTR_CURSOR_TYP
E,
(SQLPOINTER)SQL_CURSOR_STATIC,SQL_IS_INT
EGER);
SQLPrepare(hstmt, (SQLCHAR*) s_SQL, SQL_NTS);
SQLExecute(hstmt);
for (int i=0;i<numColumns;i++)
SQLDescribeCol(...) //read column names
SQLFetch(hstmt)
--[ here is where I've been calling SQLGetData ]--
Any help would be greatly appreciated. Thanks in advance, -geoff
"Brannon Jones [MS]" <branjo@.nospam.microsoft.com> wrote in message news:<ureN$Ba8DHA.2760@.T
K2MSFTNGP09.phx.gbl>...
> The problem is that the length in the metadata describes the maximum lengt
h
> of the column. A varchar(5) column will report 5 when you call
> SQLDescribeCol(). The metadata for a column is the same for every row in
> the table (instance of the column). What you are interested in is the
> length of the actual data in the column (or the length of a particular
> instance of the column). This information is considered part of the actua
l
> data in the row, rather than part of the column's metadata.
> For non-BLOB columns, you typically want to allocate a buffer that can hol
d
> the maximum length of the column (sure there are exceptions). For BLOB
> columns you really should use SQLGetData() (rather than SQLBindCol()). If
> you call SQLGetData() with a NULL TargetValuePtr parameter, but a valid
> StrLen_or_IndPtr parameter, then SQLGetData() will return the length of th
e
> data that is available for the column. Then you can use that value to
> allocate a buffer and call SQLGetData() again to read the column.
> --
> Brannon Jones
> Developer - MDAC
> This posting is provided "as is" with no warranties and confers no rights.
>
> "Geoff" <geoffpollard@.go.com> wrote in message
> news:595a2518.0402111842.698363b7@.posting.google.com...|||Sorry I was slightly incorrect. You need to pass a valid pointer for
TargetValuePtr, but you pass a zero length. We will return the available
length in the StrLen_or_IndPtr parameter, and the return code will be
SQL_SUCCESS_WITH_INFO (SQLGetDiagRec will return: "[Microsoft][ODBC SQL
Server Driver]String data, right truncation", SQLState: 01004). Then you
allocate the buffer and call again.
Let me know if that doesn't help.
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
"Geoff" <geoffpollard@.go.com> wrote in message
news:595a2518.0402140153.5a248edc@.posting.google.com...
> I suspected there was another way. However, SQLGetData seems to fail
> when I pass a NULL TargetValuePtr. The SQLRETURN == -1, and the
> SQLGetDiagRec states "Invalid argument value". Here is a code snippet:
> SQLRETURN sql_status_code;
> SQLINTEGER namelen;
> for (int i=0;i<numColumns;i++)
> {
> // fails
> sql_status_code =
> SQLGetData(hstmt,i+1,SQL_C_CHAR,NULL,0,&namelen);
> // works
> SQLCHAR test[255];
> sql_status_code =
> SQLGetData(hstmt,i+1,SQL_C_CHAR,test,255
,&namelen);
> }
> Prior to this the following statements returned SQL_SUCCESS:
> SQLAllocHandle(SQL_HANDLE_STMT, hdbc, hstmt);
> // need to use server-side cursors to handle multiple queries
> SQLSetStmtAttr(hstmt,SQL_ATTR_CURSOR_TYP
E,
> (SQLPOINTER)SQL_CURSOR_STATIC,SQL_IS_INT
EGER);
> SQLPrepare(hstmt, (SQLCHAR*) s_SQL, SQL_NTS);
> SQLExecute(hstmt);
> for (int i=0;i<numColumns;i++)
> SQLDescribeCol(...) //read column names
> SQLFetch(hstmt)
> --[ here is where I've been calling SQLGetData ]--
> Any help would be greatly appreciated. Thanks in advance, -geoff
> "Brannon Jones [MS]" <branjo@.nospam.microsoft.com> wrote in message
news:<ureN$Ba8DHA.2760@.TK2MSFTNGP09.phx.gbl>...
length
in
actual
hold
If
the
rights.