freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

[freetds] A problem with MSSQL type uniqueidentifier

From: Alexander Larkin <sasha AT chuhloma.ru>
To: freetds AT lists.ibiblio.org
Subject: [freetds] A problem with MSSQL type uniqueidentifier
Date: Wed, 01 Jul 2009 15:01:28 +0400

Hi,

Does freetds support uniqueidentifier field?
This is MSSQL specific type for GUID storage.

I get this (assert) error when I'm trying writing to this field (program source test_uniqueidentifier.c attached):

gcc test_uniqueidentifier.c -lsybdb
sasha@lt:~/$ ./a.out
a.out: bcp.c:2474: _bcp_get_col_data: Assertion `converted_data_size > 0' failed.
err_handler: Msg 20050, Level 4err_handler: Attempt to convert data stopped by syntax error in source field

I thougth that conversion from SYBVARCHAR to uniqueidentifier doesn't work, so I should use uniqueidentifier, but there is no such a type in standard FreeTDS header files. Anyway, I found "SYBUNIQUE = 36" in header file ...FreeTDS/freetds-0.83.dev.20090626/include/tds.h (but there is no SYBUNIQUE in sybdb.h or sqldb.h ).

If I use SYBUNIQUE = 36 /* 0x24 */ (defined in tds.h ) and fixed len==16, then this error happens (see test_uniqueidentifier_b.c attached):

gcc test_uniqueidentifier_b.c -lsybdb
sasha@lt:~/$ ./a.out
a.out: test_uniqueidentifier_b.c:54: main: Assertion `fOK == 1' failed.
err_handler: Msg 20107, Level 7err_handler: It is illegal to use BCP terminators with program variables other than SYBCHAR, SYBBINARY, SYBTEXT, or SYBIMAGEАварийный останов

And only if I use length -1 (saying "this is fixed length field like INT is") and type 36, then everything works exelent (see test_uniqueidentifier_2.c attached):

gcc test_uniqueidentifier_2.c -lsybdb
sasha@lt:~/$ ./a.out
Sent rows 1

Also note that prefix/terminator is being ignored for varlen==-1 bcp_bind fields, but it is not clean from documentation ( http://www.freetds.org/reference/a00277.html#ga16 ) when you are reading bcp_bind description.

The testing table I used for uniqueidentifier:

USE [DownloadNotifiesTest]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TestUniqueidentifier](
[testfield] [uniqueidentifier] NULL
) ON [PRIMARY]

GO

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", "TestUniqueidentifier" );

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


    //nvchar
    char* terminator = "";
    char* nvarchar = "ab";
    int fOK = bcp_bind (dbproc, nvarchar, 0, -1, terminator, 1, SYBVARCHAR, 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;							
}

#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", "TestUniqueidentifier" );

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


    char* uniqueidentifier = "1111111111111111";
    int fOK = bcp_bind (dbproc, uniqueidentifier, 0, -1, NULL, 0, 36, 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;							
}

#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", "TestUniqueidentifier" );

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


    //nvchar
    char* terminator = "";
    char* nvarchar = "1111111111111111";
    int fOK = bcp_bind (dbproc, nvarchar, 0, 16, NULL, 0, 36, 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;							
}

[freetds] A problem with MSSQL type uniqueidentifier, Alexander Larkin, 07/01/2009