[ psqlodbc-Bugs-1000601 ] Strange different behaviour of with SQLColAttribute with SQL_DESC_OCTET or SQL_DESC_LENGHT

Bugs item #1000601, was opened at 2006-03-30 10:47
You can respond by visiting:
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1000601&group_id=1000125

Category: Interface (example)
Group: None
>Status: Closed
Resolution: None
Priority: 3
Submitted By: Nobody (None)
Assigned to: Hiroshi Inoue (hinoue)
Summary: Strange different behaviour of with SQLColAttribute with SQL_DESC_OCTET or SQL_DESC_LENGHT

Initial Comment:
Dear ladies and gentlemen I stumbeled upon the following problelm
PSQL 8.1.x, (Unicode)
System Windows 2003 Server
I have a database test with the following table
t2 (i_val integer)

I run a query against this table and try to retrieve informaton about the result.
Here's the code (bare all error handling) now

#include <stdio.h>
#include <windows.h>
#include <winnls.h>
#include <sql.h>
#include <sqlext.h>
#include <assert.h>
#include "odb.h"
enum {SOME_LIMIT=512};

int main(void){
char * dsn = "DSN=t2_psql";
enum {BUF_SIZE=512};
SQLHENV henv;
SQLHDBC hdbc;
SQLHSTMT hstmt;
SQLRETURN rc;
SQLINTEGER data_size;
SQLCHAR con_out[BUF_SIZE];
SQLSMALLINT con_out_count;

rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
assert(SUCCEEDED(rc));
rc = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
assert(SUCCEEDED(rc));
rc = SQLAllocHandle (SQL_HANDLE_DBC, henv, &hdbc);
assert(SUCCEEDED(rc));
rc = SQLDriverConnect(hdbc, NULL, dsn, SQL_NTS,
con_out, sizeof(con_out), &con_out_count, SQL_DRIVER_COMPLETE);
assert(SUCCEEDED(rc));
rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
assert(SUCCEEDED(rc));
rc = SQLExecDirect(hstmt, (SQLCHAR*) "select * from t2", SQL_NTS);
assert(SUCCEEDED(rc));
rc = SQLColAttribute(hstmt, 1, SQL_DESC_OCTET_LENGTH, NULL, 0, NULL, &con_out_count);
assert(SUCCEEDED(rc));
printf("con_out_count = %d\n", con_out_count);
/* con_out_count = -1;
rc = SQLColAttribute(hstmt, 1, SQL_DESC_LENGTH, NULL, 0, NULL, &con_out_count);
printf("con_out_count = %d\n", con_out_count);
*/
SQLCloseCursor(hstmt);
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
rc = SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
return 0;
}

If I run SQLColAttribute with SQL_DESC_OCTET_LENGTH I got as size for the
first field an integer! 0

But if I use SQL_DESC_LENGTH I got 4. Of course here the 4 is correct AFAIKT.

This difference leads to a crash in an application in which I try to fetch the information for a generic SQL call.

Now is this an error in my usage? Is this correct behaviour or is it a bug in
psqlODBC?

Regards
Friedrich

----------------------------------------------------------------------

Comment By: Friedrich Dominicus (frido)
Date: 2006-04-04 15:24

Message:
Yeah it seems that it the case however how am I supposed to find out how much bytes I have to allocate? I wrote this now for my "internal" use:
static unsigned long calculate_byte_len (SQLHSTMT hstmt,
SQLSMALLINT col_num,
SQLSMALLINT sql_type){

unsigned long result = -1;
SQLRETURN rc;
SETEXCEPTIONS(exc);
PRECONDITION((NULL != hstmt), exc);
PRECONDITION(col_num > 0, exc);

switch(sql_type){
case SQL_CHAR:
case SQL_VARCHAR:
case SQL_LONGVARCHAR:
case SQL_WCHAR:
case SQL_WVARCHAR:
case SQL_WLONGVARCHAR:
/** \todo check if SQL_BINARY types must be added here also */
rc = SQLColAttribute(hstmt, col_num, SQL_DESC_OCTET_LENGTH,
NULL, 0, NULL, &result);
break;
default:
rc = SQLColAttribute(hstmt, col_num, SQL_DESC_LENGTH,
NULL, 0, NULL, &result);
break;
}
CHECK(SQL_SUCCEEDED(rc), exc);
return result;
}

Is that the "right" way?

If yes how can it be possible that the
example in the MSDN library named
'Using SQLBind'
second half ever can work.

AFAIKT this code does not work that way and never could

Regards
Friedrich

----------------------------------------------------------------------

Comment By: Hiroshi Inoue (hinoue)
Date: 2006-04-04 11:17

Message:
Return to the previous page(Column Size, Decimal Digits, Transfer Octet Length, and Display Size
ODBC Programmer's Reference) and you can see the
following in the middle of the page.

Similarly, the values for transfer octet length do not come from SQL_DESC_LENGTH. They come from the SQL_DESC_OCTET_LENGTH of a field of a descriptor for all character and binary types. There is no descriptor field that holds this information for other types.

regards,
Hiroshi Inoue

----------------------------------------------------------------------

Comment By: Friedrich Dominicus (frido)
Date: 2006-04-04 08:06

Message:
Ok let us assume that you are correct on this now
what does this mean then:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbctransfer_octet_length.asp

How am I supposed to get the reliably if not with
SQL_DESC_OCTET_LENGTH?

Regards
Friedrich

----------------------------------------------------------------------

Comment By: Hiroshi Inoue (hinoue)
Date: 2006-04-04 00:51

Message:
> Ok let us assume OCTEC_LENGTH has to work that way.
> Why does _LENGTH exist then also?

ISTM SQL_DESC_OCTET_LENGTH exists to compensate
for the ambiguity of SQL_DESC_LENGTH for char
or binary fields.
As for numeric(int, bigint, float, decimal etc)
data types, I'm not sure if the driver returns
even the SQL_DESC_LENGTH info correctly. The
spec doesn't seem clear about it.

regards,
Hiroshi Inoue

regards,
Hiroshi Inoue

----------------------------------------------------------------------

Comment By: Friedrich Dominicus (frido)
Date: 2006-04-03 16:10

Message:
Ok let us assume OCTEC_LENGTH has to work that way.
Why does _LENGTH exist then also?

I just can use OCTET_LENGTH on "String" types, but if I run an ad-hoc query I do not know which type I actually retrieve.

Now I can use LENGTH and get a proper value but I have to check the type also to get the proper amount for allocation.

So I have to test the type.
If I know it's "string" I can use OCTET_LENGTH and get the proper amount needed for xy char or wchar_t types or I have to use LENGT and carry out the muliplicaton myself.

That sounds unrreasonable to me

Regards
Friedrich

----------------------------------------------------------------------

Comment By: Hiroshi Inoue (hinoue)
Date: 2006-04-01 02:52

Message:
The spec doesn't seem to require to return integer
length for SQL_DESC_OCTET_LENGTH though I'm not completely sure.

----------------------------------------------------------------------

You can respond by visiting:
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1000601&group_id=1000125