FreeTDS Development Group

["Thompson, Bill D (London)" <ThompBil AT exchange.uk.ml.com>]

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')