SQL STATE 22003: Numeric value out of range with TIME data type

[Navs]

Hi,

I am trying to insert a TIME value in a SQL Server 2008 database using
a simple ODBC C program. I follow the steps below:

1. Connect and Allocate Statement handle
2. Prepare a Statement as:
rc = SQLPrepare(hstm t, "insert into table1 values (?)" ,
SQL_NTS);
(NB: Table1 has only one column of type TIME in SQL Server 2008)
3. Bind the parameter:
rc = SQLBindParamete r(hstmt,1,SQL_P ARAM_INPUT,
SQL_C_BINARY,SQ L_SS_TIME2, 16,7, &buffer,
sizeof(SQL_SS_T IME2_STRUCT ), 0);

(where, buffer is of type SQL_SS_TIME2_ST RUCT and buffer.hour = 12;
buffer.minute=1 2; buffer.second =12; buffer.fraction = 0)

I also tried SQL_C_DEFAULT instead of SQL_C_BINARY and got the same
error.
4. rc = SQLExecute(htsm t)

Appreciate any help to resolve this.

Thanks in Advance,
Navneet

[Erland Sommarskog]

Re: SQL STATE 22003: Numeric value out of range with TIME data type

Navs (navneet.sahay@ gmail.com) writes:

I am trying to insert a TIME value in a SQL Server 2008 database using
a simple ODBC C program. I follow the steps below:
>
1. Connect and Allocate Statement handle
2. Prepare a Statement as:
rc = SQLPrepare(hstm t, "insert into table1 values (?)" ,
SQL_NTS);
(NB: Table1 has only one column of type TIME in SQL Server 2008)
3. Bind the parameter:
rc = SQLBindParamete r(hstmt,1,SQL_P ARAM_INPUT,
SQL_C_BINARY,SQ L_SS_TIME2, 16,7, &buffer,
sizeof(SQL_SS_T IME2_STRUCT ), 0);
>
(where, buffer is of type SQL_SS_TIME2_ST RUCT and buffer.hour = 12;
buffer.minute=1 2; buffer.second =12; buffer.fraction = 0)
>
I also tried SQL_C_DEFAULT instead of SQL_C_BINARY and got the same
error.
4. rc = SQLExecute(htsm t)

ODBC programming is nothing I normally do, but I've been reading the
manual, and it seems to me that the problem is the last parameter. I
this this needs to be address to some place where you have written
sizeof(SQL_SS_T IME2_STRUCT). In the documentatation for
SQLBindParamete r, I found:

Note Application developers are strongly discouraged from specifying
a null pointer for StrLen_or_IndPt r when the data type of the parameter
is SQL_C_BINARY. To ensure that a driver does not unexpectedly truncate
SQL_C_BINARY data, StrLen_or_IndPt r should contain a pointer to a valid
length value.

I also found this note in SQL Server Books Online 2008 on:
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.e n/s10de_2devguide/html/7ac098db-9147-4883-8da9-a58ab24a0d31.ht m

If the byte length of the data does not equal the size of the struct
required by the SQL type, a diagnostic record is generated with
SQLSTATE 22003 and the message "Numeric value out of range".




--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx