BCP using ODBC - problem with unique identifier

[mpietrzyk]

Hi guys

I'm having a nasty problem with bulk copying into a table that has
unique identifier column. I'm coding on C++, using ODBC driver.

I'm coping from a file containing UID description like this:
{43B5B3DE-5280-4CBF-B357-D9E57651F0D1}
(I also tried a non-bracket version)

and in the DB table I get:
4233347B-4235-4433-452D-353238302D34

which seems random at first sight, but it is:
[B34{]-[B5]-[D3]-[E-]-[5280-4] - with chars read binary as hex.

and my question is: what the hell?

my code look like this:

if (bcp_init (m_hDbproc,tabl eName, NULL, NULL, DB_IN) == FAIL)
ret = -1;
if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, 16, (LPCBYTE)NULL, 0,
SQLUNIQUEID, colNo) == FAIL){
ret = -1;
}

(I also tried a VARLEN version:)

if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, SQL_VARLEN_DATA ,
(LPCBYTE)delimi ter, 1, SQLVARCHAR, colNo) == FAIL){
ret = -1;
}

and then stuff like sendrow ans save:

if (bcp_sendrow(m_ hDbproc) == FAIL)
return -1;
if (bcp_batch (m_hDbproc) == -1)
return -1;

I also tried specyfiling the column type in the m_hDbproc handle as
SQLUNIQUEID, but either I'm doing something wrong, or this just isn't
the way of a bulk copy samurai:
INT * pValue=new INT;
INT *pLen=new INT;
*pValue=0x24;
bcp_setcolfmt(m _hDbproc,1,BCP_ FMT_TYPE,pValue ,4);
So like, PLEASE help me on this. I need to get this working by last
monday :]

Thanx, M.

[Erland Sommarskog]

(mp*******@auto graf.pl) writes:

I'm having a nasty problem with bulk copying into a table that has
unique identifier column. I'm coding on C++, using ODBC driver.

I'm coping from a file containing UID description like this:
{43B5B3DE-5280-4CBF-B357-D9E57651F0D1}
(I also tried a non-bracket version)

and in the DB table I get:
4233347B-4235-4433-452D-353238302D34

which seems random at first sight, but it is:
[B34{]-[B5]-[D3]-[E-]-[5280-4] - with chars read binary as hex.

and my question is: what the hell?

my code look like this:

if (bcp_init (m_hDbproc,tabl eName, NULL, NULL, DB_IN) == FAIL)
ret = -1;

if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, 16, (LPCBYTE)NULL, 0,
SQLUNIQUEID, colNo) == FAIL){
ret = -1;
}

(I also tried a VARLEN version:)

if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, SQL_VARLEN_DATA ,
(LPCBYTE)delimi ter, 1, SQLVARCHAR, colNo) == FAIL){
ret = -1;
}

First you need to decide in which format is the UID? It if is in text,
you should specify SQLVARCHAR for the data type. Only if you have the
UID as binary, you should specify SQLUNIQUEID.

Even if you use SQLVARCHAR, I don't think SQL_VARLEN_DATA is correct.
It depends on what's in delimiter, but since a GUIO is always 36
characters (without braces), you could just as well specify 36 for the
length.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[tha_mihau]

First you need to decide in which format is the UID? It if is in text,
you should specify SQLVARCHAR for the data type. Only if you have the
UID as binary, you should specify SQLUNIQUEID.

Even if you use SQLVARCHAR, I don't think SQL_VARLEN_DATA is correct.
It depends on what's in delimiter, but since a GUIO is always 36
characters (without braces), you could just as well specify 36 for the
length.

Thanx Erland,

I tried the approaches you mentioned:

1. Using SQLVARCHAR instead of SQLUNIQUEID results in "[Microsoft][ODBC
SQL Server Driver]Invalid character value for cast specification". What
do you meas format of the UID? You mean in the input file? in the input
file it is in "text format", like presented in my first post.
2. Setting length (for SQLUNIQUEID) different then 16 results in FAIL
result in bcp_bind.
Setting different then 16 for SQLVARCHAR still results in
"[Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification" error

Still no good.

[Erland Sommarskog]

tha_mihau (mp*******@auto graf.pl) writes:

1. Using SQLVARCHAR instead of SQLUNIQUEID results in "[Microsoft][ODBC
SQL Server Driver]Invalid character value for cast specification". What
do you meas format of the UID? You mean in the input file? in the input
file it is in "text format", like presented in my first post.

The error message means that the string does not convert to a GUID.
This could be because you have not specified the appropriate length or
delimiter. I would try with 36 in length and no terminator.

2. Setting length (for SQLUNIQUEID) different then 16 results in FAIL
result in bcp_bind.

Since you have text input, you should not use SQLUNIQUEID, unless you
convert the value in your program prior to passing it to BCP.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[tha_mihau]

Erland Sommarskog wrote:

tha_mihau (mp*******@auto graf.pl) writes:

1. Using SQLVARCHAR instead of SQLUNIQUEID results in "[Microsoft][ODBC
SQL Server Driver]Invalid character value for cast specification". What
do you meas format of the UID? You mean in the input file? in the input
file it is in "text format", like presented in my first post.

The error message means that the string does not convert to a GUID.
This could be because you have not specified the appropriate length or
delimiter. I would try with 36 in length and no terminator.

2. Setting length (for SQLUNIQUEID) different then 16 results in FAIL
result in bcp_bind.

Since you have text input, you should not use SQLUNIQUEID, unless you
convert the value in your program prior to passing it to BCP.

Thx, Erland!

Running
bcp_bind (m_hDbproc, (LPCBYTE)data, 0, 36, (LPCBYTE)NULL, 0,
SQLVARCHAR, colNo)
on a file with GUIDs with no braces did the trick.
Works like a charm.

Thanks again, M.