freetds AT lists.ibiblio.org
Subject: FreeTDS Development Group
List archive
Re: [freetds] Are LOBs supported? Two additional bytes are fetched for text column.
- From: Sebastien FLAESCH <sf AT 4js.com>
- To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
- Subject: Re: [freetds] Are LOBs supported? Two additional bytes are fetched for text column.
- Date: Wed, 09 Jan 2008 11:11:05 +0100
Hello,
Here is a new version of the odbctest17.c sample...
Now the program uses SQL_LEN_DATA_AT_EXEC() macro for SQLBindParameter().
But still uses SQL_DATA_AT_EXEC for SQLBindCol() (I suppose this is
supported).
I think there is a problem when fetching text data:
By inserting 10000 bytes, then fetching, I get 2 additional bytes for the
text column.
I checked the values inserted in the database with:
select datalength(t), datalength(b) from tt
returns:
0 10000 10000
1 10000 10000
So to me the insert is ok.
Try the following:
./odbctest17.bin dsn user pswd 2
where 2 is the number of rows to be inserted.
Output gives:
>> insert... 0
>> SQLParamData: ptr = 0xbff4b990 rcode = 99
>> param 0xbff4b990: total bytes written = 10000
>> SQLParamData: ptr = 0xbff4b990 rcode = 99
>> param 0xbff4b990: total bytes written = 10000
>> SQLParamData: ptr = 0xbff4b990 rcode = 0
>> insert... 1
>> SQLParamData: ptr = 0xbff4b990 rcode = 99
>> param 0xbff4b990: total bytes written = 10000
>> SQLParamData: ptr = 0xbff4b990 rcode = 99
>> param 0xbff4b990: total bytes written = 10000
>> SQLParamData: ptr = 0xbff4b990 rcode = 0
>> fetch... 0 rcode = 0
>> readBlob field 1
>> step 1: 4096 bytes read...
>> step 2: 4096 bytes read...
>> step 3: 1810 bytes read...
>> total bytes read = 10002 <--- should be 10000
>> readBlob field 2
>> step 1: 4096 bytes read...
>> step 2: 4096 bytes read...
>> step 3: 1808 bytes read...
>> total bytes read = 10000
>> fetch... 1 rcode = 0
>> readBlob field 1
>> step 1: 4096 bytes read...
>> step 2: 4096 bytes read...
>> step 3: 1810 bytes read...
>> total bytes read = 10002 <--- should be 10000
>> readBlob field 2
>> step 1: 4096 bytes read...
>> step 2: 4096 bytes read...
>> step 3: 1808 bytes read...
>> total bytes read = 10000
/* Testing large objects */
static const char * g_dbname = "msvtest1_nc";
static const char * g_usernm = "msvuser";
static const char * g_passwd = "fourjs";
#ifdef _WIN32
#include <WINDOWS.H>
#endif
#include <stdio.h>
#include <sql.h>
#include <sqlext.h>
#define CHECK_RCODE(t,h,m) \
if ( rcode != SQL_SUCCESS && rcode != SQL_SUCCESS_WITH_INFO && rcode != SQL_NO_DATA && rcode != SQL_NEED_DATA ) { \
fprintf(stderr,"Error %d at: %s\n",rcode,m); \
getErrorInfo(t,h); \
exit(1); \
}
void getErrorInfo(SQLSMALLINT sqlhdltype, SQLHANDLE sqlhandle)
{
SQLRETURN rcode = 0;
SQLCHAR sqlstate[SQL_SQLSTATE_SIZE + 1];
SQLINTEGER naterror = 0;
SQLCHAR msgtext[SQL_MAX_MESSAGE_LENGTH + 1];
SQLSMALLINT msgtextl = 0;
int ifxerror = 0;
rcode = SQLGetDiagRec((SQLSMALLINT) sqlhdltype,
(SQLHANDLE) sqlhandle,
(SQLSMALLINT) 1,
(SQLCHAR *) sqlstate,
(SQLINTEGER *) & naterror,
(SQLCHAR *) msgtext,
(SQLSMALLINT) sizeof(msgtext),
(SQLSMALLINT *) & msgtextl);
fprintf(stderr, "Diagnostic info:\n");
fprintf(stderr, " SQL State: %s\n", (char *) sqlstate);
fprintf(stderr, " SQL code : %d\n", (int) naterror);
fprintf(stderr, " Message : %s\n", (char *) msgtext);
}
static SQLHENV m_henv;
static SQLHDBC m_hdbc;
static SQLRETURN rcode;
int readBlob(SQLHSTMT *stmth, SQLUSMALLINT pos)
{
char buf[4096];
SQLLEN len, total = 0;
int i = 0;
fprintf(stderr,">> readBlob field %d\n", pos);
while (1) {
i++;
rcode = SQLGetData(stmth, pos, SQL_C_BINARY,
(SQLPOINTER) buf, (SQLINTEGER) sizeof(buf),
&len);
if (!SQL_SUCCEEDED(rcode) || len <= 0)
break;
if (len > (SQLLEN) sizeof(buf))
len = (SQLLEN) sizeof(buf);
fprintf(stderr,">> step %d: %d bytes read...\n", i, len);
total += len;
}
fprintf(stderr,">> total bytes read = %d \n", total);
return rcode;
}
main(int argc,char **argv)
{
SQLHSTMT m_hstmt;
int i,p;
#define NBYTES 10000
int key; SQLINTEGER vind0;
char buf1[NBYTES]; SQLINTEGER vind1;
char buf2[NBYTES]; SQLINTEGER vind2;
const char * dbname;
const char * usernm;
const char * passwd;
int cnt;
if (argc == 5) {
dbname = argv[1];
usernm = argv[2];
passwd = argv[3];
cnt = atoi(argv[4]);
} else {
dbname = g_dbname;
usernm = g_usernm;
passwd = g_passwd;
cnt = 10;
}
rcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &m_henv);
CHECK_RCODE(SQL_HANDLE_ENV,NULL,"SQLAllocHandle EnvH");
rcode = SQLSetEnvAttr(m_henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_UINTEGER);
CHECK_RCODE(SQL_HANDLE_ENV,m_henv,"ODBC V3");
rcode = SQLAllocHandle(SQL_HANDLE_DBC, (SQLHANDLE) m_henv, (SQLHANDLE *) &m_hdbc);
CHECK_RCODE(SQL_HANDLE_ENV,m_henv,"SQLAllocHandle DbcH");
rcode = SQLConnect((SQLHANDLE) m_hdbc,
(SQLCHAR *) dbname,
(SQLINTEGER) SQL_NTS,
(SQLCHAR *) usernm,
(SQLINTEGER) SQL_NTS,
(SQLCHAR *) passwd,
(SQLINTEGER) SQL_NTS);
CHECK_RCODE(SQL_HANDLE_DBC,m_hdbc,"SQLConnect");
m_hstmt = NULL;
rcode = SQLAllocHandle(SQL_HANDLE_STMT, m_hdbc, &m_hstmt);
CHECK_RCODE(SQL_HANDLE_DBC,m_hdbc,"SQLAllocHandle StmtH");
rcode = SQLExecDirect(m_hstmt, "drop table tt",SQL_NTS);
rcode = SQLExecDirect(m_hstmt, "create table tt ( k int, t text, b image, v int )",SQL_NTS);
CHECK_RCODE(SQL_HANDLE_DBC,m_hdbc,"Could not create table tt");
rcode = SQLFreeHandle(SQL_HANDLE_STMT, (SQLHANDLE) m_hstmt);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLFreeHandle StmtH");
/* Insert rows ... */
for( i=0; i<cnt; i++ ) {
m_hstmt = NULL;
rcode = SQLAllocHandle(SQL_HANDLE_STMT, m_hdbc, &m_hstmt);
CHECK_RCODE(SQL_HANDLE_DBC,m_hdbc,"SQLAllocHandle StmtH");
rcode = SQLPrepare(m_hstmt, (SQLCHAR *) "insert into tt values ( ?, ?, ?, ? )", SQL_NTS);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLPrepare");
SQLBindParameter(m_hstmt, 1, SQL_PARAM_INPUT,
SQL_C_LONG,
SQL_INTEGER, 0, 0, &key, 0,
&vind0);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLBindParameter 1");
SQLBindParameter(m_hstmt, 2, SQL_PARAM_INPUT,
SQL_C_BINARY,
SQL_LONGVARCHAR, 0x10000000, 0, buf2, 0,
&vind1);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLBindParameter 2");
SQLBindParameter(m_hstmt, 3, SQL_PARAM_INPUT,
SQL_C_BINARY,
SQL_LONGVARBINARY, 0x10000000, 0, buf2, 0,
&vind2);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLBindParameter 3");
SQLBindParameter(m_hstmt, 4, SQL_PARAM_INPUT,
SQL_C_LONG,
SQL_INTEGER, 0, 0, &key, 0,
&vind0);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLBindParameter 4");
key = i;
vind0 = 0;
memset(buf1,'t',NBYTES);
vind1 = SQL_LEN_DATA_AT_EXEC(NBYTES);
memset(buf2,'b',NBYTES);
vind2 = SQL_LEN_DATA_AT_EXEC(NBYTES);
fprintf(stderr,">> insert... %d\n", i);
rcode = SQLExecute(m_hstmt);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLExecute StmtH");
while (rcode == SQL_NEED_DATA) {
char *p;
int r;
rcode = SQLParamData(m_hstmt, (SQLPOINTER)&p);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLParamData StmtH");
fprintf(stderr,">> SQLParamData: ptr = %p rcode = %d\n", (void*) p, rcode);
if (rcode == SQL_NEED_DATA) {
SQLRETURN rcode = SQLPutData(m_hstmt, p, NBYTES);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLPutData StmtH");
fprintf(stderr,">> param %p: total bytes written = %d\n", (void *) p, NBYTES);
}
}
rcode = SQLFreeHandle(SQL_HANDLE_STMT, (SQLHANDLE) m_hstmt);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLFreeHandle StmtH");
}
/* No fetch rows ... */
for( i=0; i<cnt; i++ ) {
m_hstmt = NULL;
rcode = SQLAllocHandle(SQL_HANDLE_STMT, m_hdbc, &m_hstmt);
CHECK_RCODE(SQL_HANDLE_DBC,m_hdbc,"SQLAllocHandle StmtH");
rcode = SQLSetStmtAttr(m_hstmt, SQL_ATTR_CURSOR_SCROLLABLE,
(SQLPOINTER) SQL_NONSCROLLABLE, SQL_IS_UINTEGER);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLSetStmtAttr SQL_ATTR_CURSOR_SCROLLABLE");
rcode = SQLSetStmtAttr(m_hstmt, SQL_ATTR_CURSOR_SENSITIVITY,
(SQLPOINTER) SQL_SENSITIVE, SQL_IS_UINTEGER);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLSetStmtAttr SQL_ATTR_CURSOR_SENSITIVITY");
rcode = SQLPrepare(m_hstmt, (SQLCHAR *) "select t, b, v from tt where k = ?", SQL_NTS);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLPrepare");
SQLBindParameter(m_hstmt, 1, SQL_PARAM_INPUT,
SQL_C_LONG,
SQL_INTEGER, 0, 0, &i, 0,
&vind0);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLBindParameter 1");
SQLBindCol(m_hstmt, 1, SQL_C_BINARY, NULL, 0, &vind1);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLBindCol 2");
SQLBindCol(m_hstmt, 2, SQL_C_BINARY, NULL, 0, &vind2);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLBindCol 3");
SQLBindCol(m_hstmt, 3, SQL_C_LONG, &key, 0, &vind0);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLBindCol 1");
vind0 = 0;
vind1 = SQL_DATA_AT_EXEC;
vind2 = SQL_DATA_AT_EXEC;
rcode = SQLExecute(m_hstmt);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLExecute StmtH");
rcode = SQLFetchScroll(m_hstmt,SQL_FETCH_NEXT,0);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLFetchScroll StmtH");
fprintf(stderr,">> fetch... %d rcode = %d\n", i, rcode);
rcode = readBlob(m_hstmt, 1);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"readBlob 1");
rcode = readBlob(m_hstmt, 2);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"readBlob 2");
rcode = SQLCloseCursor(m_hstmt);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLCloseCursor StmtH");
rcode = SQLFreeHandle(SQL_HANDLE_STMT, (SQLHANDLE) m_hstmt);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLFreeHandle StmtH");
}
rcode = SQLDisconnect(m_hdbc);
CHECK_RCODE(SQL_HANDLE_DBC,m_hdbc,"SQLDisconnect");
rcode = SQLFreeHandle(SQL_HANDLE_DBC, (SQLHANDLE) m_hdbc);
CHECK_RCODE(SQL_HANDLE_DBC,m_hdbc,"SQLFreeHandle DbcH");
rcode = SQLFreeHandle(SQL_HANDLE_ENV, (SQLHANDLE) m_henv);
CHECK_RCODE(SQL_HANDLE_ENV,m_henv,"SQLFreeHandle EnvH");
}
-
Re: [freetds] SQLNumResultCols() is wrong (+1)
, (continued)
- Re: [freetds] SQLNumResultCols() is wrong (+1), Sebastien FLAESCH, 01/10/2008
- [freetds] Are LOBs supported?, Sebastien FLAESCH, 01/08/2008
- Re: [freetds] Are LOBs supported?, ZIGLIO, Frediano, VF-IT, 01/08/2008
- Re: [freetds] Are LOBs supported?, ZIGLIO, Frediano, VF-IT, 01/08/2008
- Re: [freetds] Are LOBs supported?, Sebastien FLAESCH, 01/08/2008
- Re: [freetds] Are LOBs supported?, Sebastien FLAESCH, 01/08/2008
- Re: [freetds] Are LOBs supported?, ZIGLIO, Frediano, VF-IT, 01/08/2008
- Re: [freetds] Are LOBs supported?, Sebastien FLAESCH, 01/08/2008
- Re: [freetds] Are LOBs supported?, Sebastien FLAESCH, 01/08/2008
- Re: [freetds] Are LOBs supported?, Sebastien FLAESCH, 01/09/2008
- Re: [freetds] Are LOBs supported? Two additional bytes are fetched for text column., Sebastien FLAESCH, 01/09/2008
- Re: [freetds] Are LOBs supported? Two additional bytes are fetched for text column., Frediano Ziglio, 01/10/2008
- Re: [freetds] Are LOBs supported? Two additional bytes arefetched for text column., ZIGLIO, Frediano, VF-IT, 01/10/2008
- Re: [freetds] Are LOBs supported? Two additional bytes arefetched for text column., Sebastien FLAESCH, 01/10/2008
- Re: [freetds] Are LOBs supported? Two additional bytes arefetched for text column., ZIGLIO, Frediano, VF-IT, 01/10/2008
- [freetds] SQLCancel() and error diagnostic: Expecting HY008 SQLSTATE, Sebastien FLAESCH, 01/09/2008
- Re: [freetds] SQLCancel() and error diagnostic: Expecting HY008 SQLSTATE, ZIGLIO, Frediano, VF-IT, 01/10/2008
- Re: [freetds] SQLCancel() and error diagnostic: Expecting HY008 SQLSTATE, Frediano Ziglio, 01/13/2008
- Re: [freetds] SQLCancel() and error diagnostic: Expecting HY008 SQLSTATE, James K. Lowden, 01/14/2008
- Re: [freetds] SQLCancel() and error diagnostic: Expecting HY008 SQLSTATE, Sebastien FLAESCH, 01/14/2008
- Re: [freetds] SQLCancel() and error diagnostic: Expecting HY008 SQLSTATE, Sebastien FLAESCH, 01/14/2008
Archive powered by MHonArc 2.6.24.