473,326 Members | 2,192 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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

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.

Jul 10 '06 #1
4 7838
(mp*******@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, es****@sommarskog.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
Jul 10 '06 #2
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.

Jul 11 '06 #3
tha_mihau (mp*******@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, es****@sommarskog.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
Jul 11 '06 #4

Erland Sommarskog wrote:
tha_mihau (mp*******@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.
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.

Jul 12 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
by: Graham | last post by:
I need a SQL Server or ODBC package for Python. Can anyone help please? Have search the Python packages under Database and there is no reference to either SQL Server or ODBC. Thanks Graham
112
by: Andy | last post by:
Hi All! We are doing new development for SQL Server 2000 and also moving from SQL 7.0 to SQL Server 2000. What are cons and pros for using IDENTITY property as PK in SQL SERVER 2000? Please,...
31
by: Robert Brown | last post by:
Let's say I have a type hierarchy: (just an example) the general entity customer: CREATE TABLE customer(customer_id int, customer_name varchar(250), customer_type int) three specific...
0
by: Lokkju | last post by:
I am pretty much lost here - I am trying to create a managed c++ wrapper for this dll, so that I can use it from c#/vb.net, however, it does not conform to any standard style of coding I have seen....
4
by: deancarstens | last post by:
Hi, This is a tougher one, but I'm quite sure someone will have a solution for this. Of course, a last minute thing thrown at me by my boss. I have a unique identifier consisting of regions,...
3
by: RGow | last post by:
Hi all, I need to get a unique identifier for tables created in DB2 v8.1. I can't use the table name because I want to use the identifier as part of a trigger name and the table names are...
53
by: Hexman | last post by:
Hello All, I'd like your comments on the code below. The sub does exactly what I want it to do but I don't feel that it is solid as all. It seems like I'm using some VB6 code, .Net2003 code,...
11
by: funky | last post by:
hello, I've got a big problem ad i'm not able to resolve it. We have a server running oracle 10g version 10.1.0. We usually use access as front end and connect database tables for data extraction....
3
by: Salad | last post by:
Is it the rule that any table that you want to use via ODBC must be a UNIQUE record? And if you want any speed associated with the table it should be indexed? I was having some difficulty...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.