BCP assertion failure if a NOT NULL default datetime column value is not provided

[jmuehlner]

FreeTDS v1.3.16

I have a SQL Server table like the following:

CREATE TABLE [Test] (
    TestID [int],
    TestDate [datetime] NOT NULL DEFAULT GETDATE()
);

I want to insert values into it using BCP, but without providing a value for TestDate:

bcp_init(dbproc, "Test", NULL, NULL, DB_IN);
bcp_bind(dbproc, (BYTE* ) "1", 0, 1, (BYTE *) "", 0, SYBTEXT, 1);
bcp_sendrow(dbproc);

At this point, I get this assertion failure:
data.c:953: tds_generic_put: Assertion `curcol->column_varint_size' failed.

If I remove the NOT NULL constraint from the table, the BCP insert works correctly.

NOTE: I can always insert into the table without issue using a regular INSERT statement:
INSERT INTO Test(TestID) values("1");

EDIT: The same error occurs regardless of whether a default value is provided for the column, and (as mentioned by @blieusong below) also happens if a NULL is explicitly provided for the column.

[blieusong]

Not too sure, but at least you should fix the bcp_bind. You are trying to put a string in an integer field

Maybe

int test_id = 1;

bcp_init(dbproc, "Test", NULL, NULL, DB_IN);
bcp_bind(dbproc, (BYTE *)&test_id, 0, -1, NULL, 0, SYBINT4, 1);
bcp_sendrow(dbproc);

[jmuehlner]

Is binding a host string field to a remote numeric field not ok? The docs don't say anything about the types having to match, and it's always worked as expected when I've done it in the past.

In any case, I tested that change and it makes no difference to this problem. I'm still getting the data.c:953: tds_generic_put: Assertion `curcol->column_varint_size' failed. assertion.

[blieusong]

You are absolutely right... my (huge) mistake. I was just using that and have assumed the types should be identical.

I tried the first solution you mentioned with the official DB-library on a Sybase ASE server and it worked. The solution I propose obviously works too. I tried the same with freetds and it fails on bcp_sendrow()

[blieusong]

I again ran the same test as yours but by explicitely stating that the second column has a zero length (which should be understood as NULL) and it fails in the same way. It makes sense when we look at the source code in data.c:

if (curcol->column_cur_size < 0) {
	tdsdump_log(TDS_DBG_INFO1, "tds_generic_put: null param\n");
	switch (curcol->column_varint_size) {
	case 5:
		tds_put_int(tds, 0);
		break;
	case 4:
		if ((bcp7 || !IS_TDS7_PLUS(tds->conn)) && is_blob_type(curcol->on_server.column_type))
			tds_put_byte(tds, 0);
		else
			tds_put_int(tds, -1);
		break;
	case 2:
		tds_put_smallint(tds, -1);
		break;
	case 8:
		tds_put_int8(tds, -1);
		break;
	default:
		assert(curcol->column_varint_size);
		/* FIXME not good for SYBLONGBINARY/SYBLONGCHAR (still not supported) */
		tds_put_byte(tds, 0);
		break;
	}
	return TDS_SUCCESS;
}

Maybe the handling of NULL values is at fault here. Not sure why the error is not the same with Sybase.

[jmuehlner]

That makes me wonder which other column types are affected. I bet it's not just datetime.

EDIT:
I just tested with an int column in my table with the same results as with the datetime type.
The varchar type, however, does not exhibit this problem. I'm betting this issue might be common to any fixed-length datatype.

[fziglio]

It would be nice to understand what Microsoft bcp utility will do. You are trying to insert a NULL value with a type that does not support NULL so the code is bailing out (not saying this is the best way to do it, at least error should be detected and reported).

[jmuehlner]

It looks to me like the microsoft bcp utility offers a way to do bulk imports where any unspecified values will use the default, which is what I would like to do as well.

https://learn.microsoft.com/en-us/sql/relational-databases/import-export/keep-nulls-or-use-default-values-during-bulk-import-sql-server?view=sql-server-ver16#using-bcp-and-using-default-values-without-a-format-file

In fact, it looks like that's the default behavior - a flag has to be added to treat empty fields as explicitly NULL:
https://learn.microsoft.com/en-us/sql/relational-databases/import-export/keep-nulls-or-use-default-values-during-bulk-import-sql-server?view=sql-server-ver16#using-bcp-and-keeping-null-values-without-a-format-file

[fziglio]

I was more curious on what at protocol level is happening. Does the protocol change or BCP is still used? It a NULL type used instead of a no-NULL type? For fixed value for Sybase the default value is extracted and used during BCP but in case of DEFAULT GETDATE() the default is generated by the server.

[blieusong]

The Sybase DB-lib documentation, which I use, specifies that setting bcp_bind's variable length to zero means the value is NULL. This works fine with the official library but not with FreeTDS's bcp_bind (on an ASE 15.7 server). I haven't been able to check why.