RE: Determine actual size of binary column in result set

So, you mean that you want to find out the amount of data inside a column.
However, it seems that there is no good method with SQLBindCol.

For SQL-Server specific application, you may use: "select max(datalength(b))
from test".

For generic application, you may use SQLFetch + SQLGetData to obtain the
maximum length and data. Actually, for a very long column (image datatype),
it is recommended to use SQLGetData since you can get the data in chunk,
instead of pre-allocating a large buffer. However, if the amount of data is
not so large, you may use the datatype "varbinary(2048)".

Ming.
MDAC Team, Microsoft.
====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.




"Howard Rodstein" wrote:

I'm writing a generic application that is supposed to be able to work with
any ODBC-compatible database.

I have created a test database with a binary column. With MySQL I am using
datatype BLOB and in MS SQL Server I am using datatype IMAGE. In my test case
I know that the binary data is 2048 bytes but in general, I will not know the
size of the binary column.

When I fetch data, I first use SQLBindCol to bind each column in the result
set to memory I have allocated for that column. Before I bind to the binary
column, I need to determine how big it is so I can allocate memory. This is
where I am running into a problem.

According to Microsoft's ODBC documentation, I should be able to use the
SQLColAttribute function with a field type of SQL_DESC_LENGTH to find the
ACTUAL data length. From the SQLColAttribute documentation for
SQL_DESC_LENGTH:

A numeric value that is either the maximum or actual character length of a
character string or binary data type. It is the maximum character length for
a fixed-length data type, or the actual character length for a variable-length
data type.

The IMAGE data type is variable-length and therefore
SQLColAttribute(SQL_DESC_LENGTH) should return the actual data length.

However, when running with MS SQL Server 2005 Express Edition with the SQL
Native Client driver, the returned value is 2^31-1 - that is, it is returning
the MAXIMUM length, not the ACTUAL length.

How can I determine the ACTUAL length?

Thanks.


.

Relevant Pages

  • Re: Remote View Boolean Fields
    ... conversion required by DataType property for field is invalid" error. ... converted to a foxpro logical but for whatever reason it doesn't it I can't ... specify logical as a datatype for a boolean datatype in Postgresql. ... VFP's Logical data type is automatically converted to ...
    (microsoft.public.fox.programmer.exchange)
  • Re: Want To Read Float In Record As Is
    ... Float an real are generally referred to as "approximate" datatype. ... But you probably want to look ad NUMERIC or DECIMAL (these are identical in SQL Server). ... What I mean is that no matter what data type I use in variables ...
    (microsoft.public.sqlserver.programming)
  • Re: Getting the data type of a column
    ... What I need is to get is the data type of a column, ... //where vtCriteria is {Empty, Empty, TableName, Empty} ... DataTypeEnum dataType; ... This email account is my spam trap so I ...
    (microsoft.public.data.ado)
  • Re: Datatype conversions from SQL to Access
    ... way data type. ... Just uncheck Allow Nulls so it is 2 way. ... > a datatype in SQL that will convert to Yes/No in Access. ...
    (microsoft.public.access.externaldata)
  • Re: conversion date datatype to long datatype
    ... DateTime data type, which is an eight-byte integer value. ... implicit conversion between the Date and Double data types. ... Do you wanna return long value for any specific reason? ... How to convert DateTime datatype to Long Datatype. ...
    (microsoft.public.dotnet.framework.compactframework)