How to insert a NULL in a SQL_TYPE_TIMESTAMP column ?

hello,

i'm failing in inserting null value timestamp data type column in mssql server via sqlbindparameter. tried examples below. doing wrong ?

of course, column nullable in database , henv set odbc 3.0:

create table [test_table_rdd_odbcrdd] ( [date_lim] datetime null,
   [sr_recno] numeric (15,0) identity,
   [sr_deleted] char (1)  not null
 )

sqlsetenvattr(henv, sql_attr_odbc_version, (sqlpointer) sql_ov_odbc3, 0);

i've tried:

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

sqlinteger nind = sql_null_data;
sqlreturn res;
res = sqlbindparameter( hstmt, 1, sql_param_input, sql_c_type_timestamp, sql_type_timestamp, sql_timestamp_len, 0, null, 0, &nind );

error diag: return code: -1, state: hy090, description: [microsoft][odbc sql server driver]invalid string or buffer length

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


sqlinteger nind = sql_null_data;
sqlreturn res;

res = sqlbindparameter( hstmt, 1, sql_param_input, sql_c_type_timestamp, sql_type_timestamp, 0, 0, null, 0, &nind );
or
res = sqlbindparameter( hstmt, 1, sql_param_input, sql_c_char, sql_type_timestamp, 0, 0, null, 0, &nind );

error diag: return code: -1, state: hy104, description: [microsoft][odbc sql server driver]invalid precision value

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

sqlinteger nind = sql_null_data;
sqlreturn res;
sql_timestamp_struct astimestamp;

astimestamp.year     = 1900;
astimestamp.month    = 1;
astimestamp.day      = 1;
astimestamp.hour     = 0;
astimestamp.minute   = 0;
astimestamp.second   = 0;
astimestamp.fraction = 0;

res = sqlbindparameter( hstmt, 1, sql_param_input, sql_c_type_timestamp, sql_type_timestamp, sql_timestamp_len, 0, &(astimestamp), 0, &nind );

above gives no error, instead of null writes '1900-01-01 00:00:00.000'.. , if do:

astimestamp.year     = 0;
astimestamp.month    = 0;
astimestamp.day      = 0;

sqlbindparameter works, sqlexecute explodes:
error diag: return code:  -1, state: 22007, description: [microsoft][odbc sql server driver]invalid date format

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

write null value database appreciated.

in advance,

marcelo

that's because of deferred parameter input &nind. if use bindoutsidecurrentfunction(), , nind declared inside function, once leave out of function, nind not exist more, however, need use later in sqlexecute.

below sdk documentation of sqlbindparameter last parameter:

strlen_or_indptr argument

the strlen_or_indptr argument points buffer that, when sqlexecute or sqlexecdirect called, contains 1 of following. (this argument sets sql_desc_octet_length_ptr , sql_desc_indicator_ptr record fields of application parameter pointers.)

so solution if declare nind global variable, work fine.

SQL Server  >  SQL Server Data Access