Skip to Content.
Sympa Menu

freetds - Re: need some help with freebcp

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Thompson, Bill D (London)" <ThompBil AT exchange.uk.ml.com>
  • To: "'TDS Development Group'" <freetds AT franklin.oit.unc.edu>
  • Subject: Re: need some help with freebcp
  • Date: Wed, 23 Oct 2002 07:46:38 +0100


Hi John,

I have managed to reproduce your problem.

I defined the table you describe:

create table xxbcp
( col1 numeric(10,0),
col2 datetime,
col3 varchar(255),
col4 float,
col5 float,
col6 float,
col7 int)

I created a file of data using cut'n'paste from your mail.
I used <tab> column separators (as that is what is required with the -c
format of bcp) .
I noticed the data for the varchar(255) column was missing so I put 2 <tab>s
between the datetime value and the 1st float value (to indicate an empty
field).

Hey Presto! I got the same results.

I thought it might be the datetime format you used, so I changed it to one I
know works, but that made no difference.

Next I put a value into the varchar(255) column data, and It started
working!

So, It looks like we have a problem with empty column data.

Can you reproduce my results, and confirm I'm on the right track ?

I'll look into it in the meantime


Bill
> -----Original Message-----
> From: Wilson, John [SMTP:John.Wilson AT savvis.net]
> Sent: Wednesday, October 23, 2002 5:04 AM
> To: TDS Development Group
> Subject: [freetds] Re: need some help with freebcp
>
> James,
> Thanks for the response. I guess I would have called it a
> char or string type, but I understand.
>
> The bcp command below is working now writing to a Sybase DB (ASE 12.0).
> I'm
> trying to use FreeTDS to replace the underlying libs and still use our
> existing Perl code with little modification to target the new MS SQL
> Server
> 2000 DB.
>
> In regards to your question below, I had intended to use the DBD::Sybase
> but
> then found that Sybperl
> supported CTlib. I stubbed out the method in CTlib.xs that used
> blk_describe() and then I was able to get the code to compile.
>
> The source I have uses Sybase::CTlib, so this was a simpler way to get
> done.
> This source creates a bcp command string and executes it like an exec
> function vs doing it programmatically. However, right now the freebcp is
> not working.
>
> As I mentioned below, it would seem to me that this simple text should be
> able to be inserted into the new MS SQL Server 2000 DB. However the
> freebcp
> man page states that the -e option is not current supported, thus I'm not
> getting any error output, with the exception of the tdsdump.txt file. The
> latter file will take some time for me to decode since I assume this is
> raw
> TDS responses.
>
> Another note in the man page is:
> Currently, there is no support for text data types in
> freebcp, when SQL Server 2000 is the target database.
>
> But above this it mentions using TDS 8.0 with SQL Server 2000. So I'm not
> sure what's right here.
> Right now to be compatible with FreeTDS, I have compiled the source with
> TDSVER 7.0. In addition, the columns are numeric(10,0), datetime,
> varchar(255), float()x3, and int. This *should* work.
>
> I looking at both Sybase and MS description of the bcp command options, I
> think I have everything right. I'm also writting to an empty database,
> with
> the fields setup by our DBA. Not sure if this would be an issue though.
>
> After reading the MS and Sybase web pages on bcp command line syntax, I
> think what's missing the the input format, mainly the bcp.fmt file. I'm
> not
> sure where this file belongs wrt freebcp though... I assume it will be in
> the freetds install path or ~/usr/local/etc. I'll have to look for
> tomorrow.
>
> thanks for your help,
> jd wilson
>
> -----Original Message-----
> From: James K. Lowden [mailto:jklowden AT schemamania.org]
> Sent: Tuesday, October 22, 2002 19:57
> To: TDS Development Group
> Subject: [freetds] Re: need some help with freebcp
>
> On Tue, 22 Oct 2002 17:17:19 -0500, "Wilson, John"
> <John.Wilson AT savvis.net> wrote:
> >
> > The comments in the freebcp man page say that freebcp does not support
> > "text" data types. when the target DB is SQL Server 2000.
>
> Hi John,
>
> "text" there refers to the server's column's datatype. As distinguished
> from int, varchar, float, etc.
>
> > It would seem to me that freebcp should support reading in a text file
> > like this:
> >
> >
> > 50004014 10/8/2002 17:31:34 895897.0000
> > 895897.0000 895897.0000 1
> > 50004015 10/8/2002 17:31:34 352936.0000
> > 352936.0000 352936.0000 1
>
> Yes.
>
> > I'm getting this response:
> > ___________________________________________________________________
> > $ freebcp DATUM.dbo.datum in test1.bcp -c -S RTDBASESQL -U dsm_user -P
> > passnotshown -e bcp.err
> > Starting copy...
> >
> > 0 rows copied.
> > ___________________________________________________________________
> > The data is not being copied into the table.
> >
> > I took a look at the tdsdump.txt file... it was not very helpful, but I
> > may have to dig into the protocol...
>
> Have a look at bcp.err. That should show you any rows that failed, and
> why.
>
> > I looked for an option that provided a verbose output and could not find
> > one.
>
> Verbose is on by default. It's also off by default. ;)
>
> We emulate Sybase's utility's behavior fairly closely, I believe.
>
> > If someone could help out, it would be appreciated. I would think if
> > there was a problem with the data format it would have reported an
> > error?
>
> If you don't specify an error file, you don't get much feedback, it's
> true. You *did* specify an error file (bcp.err). In it, you should find
> information about every row that failed to load. If you don't, that would
> be a bug.
>
> Are you over the hump with DBD::Sybase?
>
> Regards,
>
> --jkl
>
> ---
> You are currently subscribed to freetds as: [john.wilson AT savvis.net]
> To unsubscribe, forward this message to
> $subst('Email.Unsub')
>
> ---
> You are currently subscribed to freetds as: [thompbil AT exchange.uk.ml.com]
> To unsubscribe, forward this message to
> $subst('Email.Unsub')





Archive powered by MHonArc 2.6.24.

Top of Page