FreeTDS Development Group

[Alexander Larkin <sasha AT chuhloma.ru>]

Hi,

I appreciate much your efforts in developing FreeTDS.

I have a bulk insert problem for field with type nvarchar(n) where 
n=[odd number] for MSSQL 2008 (TDS7).
I'm using FreeTDS 0.82, but I tried 0.83 (development) and it is the same.

The problem is MSSQl specific.

Note that for nvarchar(n) the n is amount of symbols where each symbol 
is doubled-char. So for nvarchar(3) the length would be 6 bytes.
The nvarchar(3) doesn't work for me at all (below), but nvarchar(4) is 
Ok, so the problem is that someone (Microsoft? FreeTDS?) set incorrect 
length in TDS protocol (like 3 instead of 6) or interpret such a length 
when it is already set incorrectly. Anyway, the sending data to DB 
itself works fine for nvarchar(4), so the problem is only with max field 
length.

The problem is:

"
msg_handler: Server 'WL-BLADE6', msg_handler: Line 1err_handler: Msg 
4895, Level 16err_handler: General SQL Server error: Check messages from 
the SQL Server
bcp_done() returned error
"

If I change type nvarchar(255) to nvarchar(254) or nvarchar(2), then 
everything works exelent, but if I use nvarchar(1) or nvarchar(201) or 
nvarchar(255), then such an error happens.

How to reproduce a bug:


1. Create table with nvarchar(3)


USE [TableName]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[TestNVarChar](
   [testfield] [nvarchar](3) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO



2. Run my test program (the source code attached):

sasha@lt:~/$ ./test_nvarchar
msg_handler: Msg 4895, Level 16, State 2msg_handler: Server 'WL-BLADE6', 
msg_handler: Line 1msg_handler: Unicode data is odd byte size for column 
1. Should be even byte size.
msg_handler: severity 16 > 10, FATAL SQL errorerr_handler: Msg 4895, 
Level 16err_handler: General SQL Server error: Check messages from the 
SQL ServerSent rows 0


3. I don't change anything in my program, but I change field type from 
nvarchar(3) to nvarchar(4) and then the same program runs well:

sasha@lt:~/$ ./test_nvarchar
Sent rows 1



I guess the problem is that FreeTDS fills incorrectly "max field length" 
when sending NVARCHAR data. It should be "max field length"=6 for field 
nvarchar(3), but FreeTDS set "max field length"=3 and then mssql returns 
error "Unicode data is odd byte size for column".

If I change

int fOK = bcp_bind (dbproc, nvarchar, 0, 2, NULL, 0, SYBVARCHAR, 1);

to

int fOK = bcp_bind (dbproc, nvarchar, 0, 2, NULL, 0, SYBNVARCHAR, 1);
where
SYBNVARCHAR = 103,      /* 0x67 */

then internal FreeTDS error happens:

sasha@alarkin-lt:~/Kaspersky/source/input_statistics/4larkin/bulk_insert$ 
./test_nvarchar
test_nvarchar: bcp.c:2474: _bcp_get_col_data: Assertion 
`converted_data_size > 0' failed.
err_handler: Msg 20053, Level 4err_handler: Requested data conversion 
does not exist


Could you please help me to fix this bug, because my boses tells me that 
I must fix it in someway, because they don't want to change the 
nvarchar(255) to nvarchar(256) in DataBase and possibly they would 
prefer to stretch me in debugging FreeTDS code instead of such a simple 
solution.

Also it is interesting for me: if I would record the successfull sending 
two chars to nvarchar(4) using tcpdump and then replay it for 
nvarchar(3), then will the same bug happen or not? I guess not, because 
the "max field length" would be filled correctly.

Regards,
Sasha.
sasha AT chuhloma.ru
avlarkin AT iname.ru
#include <sqlfront.h>
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include <assert.h>
#include <stdio.h>
#include <sys/types.h>
#include <sys/stat.h>
#include <unistd.h>

#define         SYBNVARCHAR 103

int msg_handler (DBPROCESS *dbproc, DBINT msgno, int msgstate, int severity, char *msgtext, char *srvname, char *procname, int line);
int err_handler (DBPROCESS * dbproc, int severity, int dberr, int oserr, char *dberrstr, char *oserrstr);

int main (void)
{
    LOGINREC  *login;
    DBPROCESS *dbproc;
    
    dbinit ();

    login = dblogin ();

    DBSETLPWD  (login, "password9" );
    DBSETLUSER (login, "Larkin" );
    DBSETLAPP  (login, "testnvarchar" );
    BCP_SETL   (login, 1);

    dbproc = dbopen (login, "WL-BLADE" );
    
    dbuse (dbproc, "DownloadNotifiesTest" );

    dbloginfree (login);

    dberrhandle( err_handler );
    dbmsghandle( msg_handler );

    char cmd[512];
    
    sprintf (cmd, "%s..%s", "DownloadNotifiesTest", "TestNVarChar" );

    if (bcp_init (dbproc, cmd, NULL, NULL, DB_IN) == FAIL)
    {
	    fprintf (stderr, "bcp_init: failed\n");
	    exit    (1);
    }


    //nvchar
    char* nvarchar = "ab";
    int fOK = bcp_bind (dbproc, nvarchar, 0, 2, NULL, 0, SYBNVARCHAR, 1);
    assert (fOK == SUCCEED);

    if( bcp_sendrow ( dbproc ) == FAIL ) {
        printf("Sendrow failed\n");
        return -1;
    }

    int rows_sent = bcp_batch (dbproc);
    if (rows_sent == -1)
    {
	    fprintf (stdout, "bcp_batch: failed\n");
	    exit (1);
    }
    printf( "Sent rows %i\n", rows_sent );

    bcp_done (dbproc);

    dbexit ();
    
    return 0;
}

int err_handler (DBPROCESS * dbproc, int severity, int dberr, int oserr, char *dberrstr, char *oserrstr)
{
    if( dberr ) {							
        printf( "err_handler: Msg %d, Level %d", dberr, severity );
        printf( "err_handler: %s", dberrstr );
    } else {
        printf( "err_handler: DB-LIBRARY error:" );
        printf( "err_handler: %s", dberrstr );
    }

    return INT_CANCEL;
}

int msg_handler (DBPROCESS *dbproc, DBINT msgno, int msgstate, int severity, char *msgtext, char *srvname, char *procname, int line)
{
    if( msgno > 0 ) {
        printf( "msg_handler: Msg %ld, Level %d, State %d", (long) msgno, severity, msgstate );

        if (strlen (srvname) > 0)
		    printf( "msg_handler: Server '%s', ", srvname );
        if (strlen(procname) > 0)
		    printf( "msg_handler: Procedure '%s', ", procname );
        if (line > 0)
            printf( "msg_handler: Line %d", line );
    }

    printf( "msg_handler: %s\n", msgtext );
	
    if( severity > 10 ) {
        printf( "msg_handler: severity %d > 10, FATAL SQL error", severity );
    }

    return 0;							
}