Skip to Content.
Sympa Menu

freetds - [freetds] Bulk insert problem with nvarchar(n) where n=[odd number]

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Alexander Larkin <sasha AT chuhloma.ru>
  • To: freetds AT lists.ibiblio.org
  • Subject: [freetds] Bulk insert problem with nvarchar(n) where n=[odd number]
  • Date: Wed, 01 Jul 2009 12:41:47 +0400

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;							
}



Archive powered by MHonArc 2.6.24.

Top of Page