BCP using ODBC - problem with unique identifier

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 bi
nary as hex.
and my question is: what the hell?
my code look like this:
if (bcp_init (m_hDbproc,tableName, 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)delimiter, 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,p
Value,4);
So like, PLEASE help me on this. I need to get this working by last
monday :]
Thanx, M.(mpietrzyk@.autograf.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,tableName, 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)delimiter, 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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||
> 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][O
DBC
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.|||tha_mihau (mpietrzyk@.autograf.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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx