FreeTDS Development Group

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

OK,

I've found John's bcp problem. 
This can be fixed by the addition of the following 2 lines of code in bcp.c
Could someone do that for me ? Ta.

function _bcp_read_hostfile():

   if (hostcol->term_len > 0
   {
           bytes_read = _bcp_get_term_data(hostfile, hostcol, coldata);
         
           if (bytes_read == -1)
              return FAIL;

           if (collen)
              collen = (bytes_read < collen) ? bytes_read : collen;
           else
              collen = bytes_read;

 +          if (collen == 0)
 +             data_is_null = 1;

           if (hostcol->tab_colnum)
           {
                ....


Bill


> -----Original Message-----
> From: Thompson, Bill D (London) [SMTP:ThompBil AT exchange.uk.ml.com]
> Sent: Wednesday, October 23, 2002 7:47 AM
> To:   TDS Development Group
> Subject:      [freetds] Re: need some help with freebcp
> 
> 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: [thompbil AT exchange.uk.ml.com]
> To unsubscribe, forward this message to
> $subst('Email.Unsub')