A problem with MSSQL type uniqueidentifier

Alexander Larkin <[email protected]>
Newsgroups gmane.comp.db.tds.freetds
Message-ID <[email protected]> 
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.
[email protected]
[email protected]

_______________________________________________
FreeTDS mailing list
[email protected]
http://lists.ibiblio.org/mailman/listinfo/freetds
test_uniqueidentifier.c (text/x-csrc, 2.7 KB) 
#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;							
}
test_uniqueidentifier_2.c (text/x-csrc, 2.7 KB) 
#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;							
}
test_uniqueidentifier_b.c (text/x-csrc, 2.7 KB) 
#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;							
}
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.