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: Brian Bruns <camber AT ais.org>
  • To: TDS Development Group <freetds AT franklin.metalab.unc.edu>
  • Subject: Re: need some help with freebcp
  • Date: Wed, 23 Oct 2002 06:46:27 -0400 (EDT)


Bill,

I don't know if this is helpful, but TDS has a real problem wrt NULL vs.
empty string in varchars. On the outbound side empty strings are
always expressed as a single space.

Brian

On Wed, 23 Oct 2002, Thompson, Bill D (London) wrote:

> 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')
>
>
> ---
> You are currently subscribed to freetds as: [camber AT ais.org]
> To unsubscribe, forward this message to $subst('Email.Unsub')
>





Archive powered by MHonArc 2.6.24.

Top of Page