Re: bcp problems

Hi Bill,

Thank you, I do understand the reason for the below. It's just because by changing the #trans_bcp table definition from datetime to char(30) for field no 10 it makes field no 11 to be populated with a value (not null value) properly. I will revisit the issue later, when I resolve some more pressing issues!! 
Thank you again.

Regards,

Panos.

--- On Wed, 22/6/11, Thompson, William <[email protected]> wrote:

> From: Thompson, William <[email protected]>
> Subject: Re: [freetds] bcp problems
> To: "'FreeTDS Development Group'" <[email protected]>
> Date: Wednesday, 22 June, 2011, 15:52
> Hi Panos,
> 
> This new error looks pretty clear to me. I'm not sure it
> has anything to do with the date field, though.
> The processing of the data in the date field looks as if
> it's going fine...
> 
> bcp.c:3404:_bcp_get_col_data(46eb0, 596c60)
> bcp.c:3521:bufpos = 0 , value =>2<
> bcp.c:3521:bufpos = 1 , value =>8<
> bcp.c:3521:bufpos = 2 , value => <
> bcp.c:3521:bufpos = 3 , value =>A<
> bcp.c:3521:bufpos = 4 , value =>p<
> bcp.c:3521:bufpos = 5 , value =>r<
> bcp.c:3521:bufpos = 6 , value => <
> bcp.c:3521:bufpos = 7 , value =>2<
> bcp.c:3521:bufpos = 8 , value =>0<
> bcp.c:3521:bufpos = 9 , value =>1<
> bcp.c:3521:bufpos = 10, value =>1<
> bcp.c:3463:_bcp_get_col_data bytes_read: 11,
> terminator><
> bcp.c:3469:_bcp_get_col_data collen: 11,
> 
> -- we've read 11 characters "28 Apr 2011"
> 
> dblib.c:2144:dbconvert(46eb0, SYBTEXT, fd2a4d15, 11,
> SYBDATETIME, 589260, 8)
> dblib.c:2286:dbconvert() calling tds_convert
> convert.c:1780:string_to_datetime: current_state = 0
> convert.c:1801:string_to_datetime: is_numeric
> convert.c:1780:string_to_datetime: current_state = 1
> convert.c:2500:store_monthname: 3 Apr
> convert.c:1780:string_to_datetime: current_state = 2
> dblib.c:2289:dbconvert() called tds_convert returned 8
> 
> -- we seem to have converted that to a datetime (which is 8
> bytes)
> 
> bcp.c:3220:parsed column 10, length 8 (not null)
> bcp.c:3286:new_record_size = 56 datalen = 8
> bcp.c:3310:old_record_size = 205 new size = 64
> 
> -- we're now done with that column...
> 
> So we move on to the field following the date field...field
> 11 ?
> 
> bcp.c:3404:_bcp_get_col_data(46eb0, 5970d8)
> bcp.c:3463:_bcp_get_col_data bytes_read: 0, terminator>
> <
> bcp.c:3469:_bcp_get_col_data collen: 0,
> 
> -- the field is empty (length 0, because the field has a
> NULL terminator in the first position
> 
> bcp.c:3478:_bcp_get_col_data Data is NULL
> bcp.c:3220:parsed column 11, length 0 (null)
> 
> -- so we interpret that as a NULL input
> 
> dblib.c:7785:dbperror(46eb0, 20073, 0)
> dblib.c:7838:20073: "Attempt to bulk copy a NULL value into
> a Server column which does not accept null values"
> 
> -- and because the corresponding column on the database is
> defined as NOT NULL, bcp won't can't accept it.
> 
> Are you falling foul of the database setting for
> ANSI_NULL_DEFAULT (see BOL)
> If you set up the table you're bcping into without
> specifying NULL or NOT NULL, it may be defaulting to NOT
> NULL...
> 
> HTH,
> 
> Bill
> 
> 
> 
> -----Original Message-----
> From: [email protected]
> [mailto:[email protected]]
> On Behalf Of Panos Stavroulis
> Sent: 22 June 2011 15:23
> To: FreeTDS Development Group
> Subject: Re: [freetds] bcp problems
> 
> 
> Hi Bill,
> 
> This fix works in principle. It still doesn't work for my
> date field though. Let me explain. I used to have a problem
> with another bcp row later in the code. This field had a
> value of NULL and it was failing for the same reason. I
> could reach that position I changed the #trans_bcp table
> definition column trans_date to be from datetime to char(30
> so it bypassed the date field column. It works now after the
> fix.
> 
> However, if I revert my #trans_bcp column definition to
> datetime then I get a different error see below... The value
> is still NULL when I am expecting it to be not null, so it
> fails.
> 
> Just a general question, when you pass dates what's the
> length I need to define my C structure for all date fields?
> Maybe this is where it goes wrong.
> 
> If the error below tells you something then let me know,
> otherwise, I will do some further tests in case there is
> something else wrong with the code. In a way I have a
> workaround now.
> 
> Thanks very much.
> 
> Panos
> 
> 
> 
> 
> bcp.c:3469:_bcp_get_col_data collen: 11,
> dblib.c:2144:dbconvert(46eb0, SYBCHAR, fd2a4d08, 11,
> SYBCHAR, 4a6b8, 12)
> dblib.c:2177:dbconvert() srctype == desttype
> bcp.c:3220:parsed column 9, length 11 (not null)
> bcp.c:3286:new_record_size = 45 datalen = 11
> bcp.c:3310:old_record_size = 205 new size = 56
> bcp.c:3404:_bcp_get_col_data(46eb0, 596c60)
> bcp.c:3521:bufpos = 0 , value =>2<
> bcp.c:3521:bufpos = 1 , value =>8<
> bcp.c:3521:bufpos = 2 , value => <
> bcp.c:3521:bufpos = 3 , value =>A<
> bcp.c:3521:bufpos = 4 , value =>p<
> bcp.c:3521:bufpos = 5 , value =>r<
> bcp.c:3521:bufpos = 6 , value => <
> bcp.c:3521:bufpos = 7 , value =>2<
> bcp.c:3521:bufpos = 8 , value =>0<
> bcp.c:3521:bufpos = 9 , value =>1<
> bcp.c:3521:bufpos = 10 , value =>1<
> bcp.c:3463:_bcp_get_col_data bytes_read: 11,
> terminator><
> bcp.c:3469:_bcp_get_col_data collen: 11,
> dblib.c:2144:dbconvert(46eb0, SYBTEXT, fd2a4d15, 11,
> SYBDATETIME, 589260, 8)
> dblib.c:2286:dbconvert() calling tds_convert
> convert.c:1780:string_to_datetime: current_state = 0
> convert.c:1801:string_to_datetime: is_numeric
> convert.c:1780:string_to_datetime: current_state = 1
> convert.c:2500:store_monthname: 3 Apr
> convert.c:1780:string_to_datetime: current_state = 2
> dblib.c:2289:dbconvert() called tds_convert returned 8
> bcp.c:3220:parsed column 10, length 8 (not null)
> bcp.c:3286:new_record_size = 56 datalen = 8
> bcp.c:3310:old_record_size = 205 new size = 64
> bcp.c:3404:_bcp_get_col_data(46eb0, 5970d8)
> bcp.c:3463:_bcp_get_col_data bytes_read: 0, terminator>
> <
> bcp.c:3469:_bcp_get_col_data collen: 0,
> bcp.c:3478:_bcp_get_col_data Data is NULL
> bcp.c:3220:parsed column 11, length 0 (null)
> dblib.c:7785:dbperror(46eb0, 20073, 0)
> dblib.c:7838:20073: "Attempt to bulk copy a NULL value into
> a Server column which does not accept null values"
> 00000109 2011/06/22 09:27:52.431 TEMP 16139 SERI 
>                
>            DATABASE
> ERROR:: Attempt to bulk copy a NULL value into a Server
> column which does not accept null values
> 00000110 2011/06/22 09:27:52.431 TEMP 16139 SERI 
>                
>            DATABASE
> ERROR:: Error 0
> dblib.c:7859:"Attempt to bulk copy a NULL value into a
> Server column which does not accept null values", client
> returns 2 (INT_CANCEL)
> bcp.c:3023:bcp_done(46eb0)
> net.c:779:Sending packet
> 
> --- On Wed, 22/6/11, Thompson, William <[email protected]>
> wrote:
> 
> > From: Thompson, William <[email protected]>
> > Subject: Re: [freetds] bcp problems
> > To: "'FreeTDS Development Group'" <[email protected]>
> > Date: Wednesday, 22 June, 2011, 14:00
> > Hi Panos,
> >
> > I must be losing my marbles.
> > I actually solved the same issue in my installation a
> month
> > or so ago, and reported the problem.
> >
> > the fix is in the file src/dblib/bcp.c
> > in function _bcp_get_col_data...
> >
> >     /* read the data, finally */
> >
> >     if (bindcol->bcp_term_len
> > 0) {
> >   /* terminated field */
> >         bytes_read =
> > _bcp_get_term_var(dataptr, (BYTE
> > *)bindcol->bcp_terminator,
> bindcol->bcp_term_len);
> >
> > -       if (collen)
> > +       if (collen > 0)
> >         
>    collen =
> > (bytes_read < collen) ? bytes_read : collen;
> >         else
> >         
>    collen =
> > bytes_read;
> >
> >         if (collen ==
> 0)
> >         
>    data_is_null =
> > 1;
> >     }
> >
> > Hope this works for you.
> >
> > Bill
> >
> >
> >
> >
> > -----Original Message-----
> > From: [email protected]
> > [mailto:[email protected]]
> > On Behalf Of Panos Stavroulis
> > Sent: 22 June 2011 12:40
> > To: FreeTDS Development Group
> > Subject: Re: [freetds] bcp problems
> >
> >
> > Hi Bill,
> >
> > I made a mistake and was printing not the correct
> > character. See below... The terminator is fine. I
> can't
> > actually paste it in  the emai so replaced it
> with
> > blank, but the terminator field is consistent all the
> way to
> > the column where it fails.
> >
> > Any other ideas?
> >
> > Thank you.
> >
> > Panos
> > bcp.c:3521:bufpos = 0 , value =>V<
> > bcp.c:3521:bufpos = 1 , value =>D<
> > bcp.c:3521:bufpos = 2 , value =>A<
> > bcp.c:3521:bufpos = 3 , value =>C<
> > bcp.c:3463:_bcp_get_col_data bytes_read: 4,
> terminator>
> > <
> > bcp.c:3469:_bcp_get_col_data collen: -1,
> > dblib.c:2144:dbconvert(45c80, SYBCHAR, fd2a4fd9, -1,
> > SYBCHAR, 589c40, 4)
> > dblib.c:2177:dbconvert() srctype == desttype
> > bcp.c:3220:parsed column 5, length 4 (not null)
> > bcp.c:3286:new_record_size = 19 datalen = 4
> > bcp.c:3310:old_record_size = 205 new size = 23
> > bcp.c:3404:_bcp_get_col_data(45c80, 5861e0)
> > bcp.c:3521:bufpos = 0 , value =>G<
> > bcp.c:3521:bufpos = 1 , value =>B<
> > bcp.c:3521:bufpos = 2 , value =>P<
> > bcp.c:3463:_bcp_get_col_data bytes_read: 3,
> terminator>
> > <
> > bcp.c:3469:_bcp_get_col_data collen: -1,
> > dblib.c:2144:dbconvert(45c80, SYBCHAR, fd2a4fde, -1,
> > SYBCHAR, 589c50, 3)
> > dblib.c:2177:dbconvert() srctype == desttype
> > bcp.c:3220:parsed column 6, length 3 (not null)
> > bcp.c:3286:new_record_size = 25 datalen = 3
> > bcp.c:3310:old_record_size = 205 new size = 28
> > bcp.c:3404:_bcp_get_col_data(45c80, 586658)
> > bcp.c:3521:bufpos = 0 , value =>2<
> > bcp.c:3521:bufpos = 1 , value =>1<
> > bcp.c:3521:bufpos = 2 , value =>1<
> > bcp.c:3521:bufpos = 3 , value =>0<
> > bcp.c:3521:bufpos = 4 , value =>0<
> > bcp.c:3521:bufpos = 5 , value =>0<
> > bcp.c:3463:_bcp_get_col_data bytes_read: 6,
> terminator>
> > <
> > bcp.c:3469:_bcp_get_col_data collen: -1,
> > dblib.c:2144:dbconvert(45c80, SYBCHAR, fd2a4fe2, -1,
> > SYBCHAR, 4a870, 16)
> > dblib.c:2177:dbconvert() srctype == desttype
> > bcp.c:3220:parsed column 7, length 6 (not null)
> > bcp.c:3286:new_record_size = 30 datalen = 6
> > bcp.c:3310:old_record_size = 205 new size = 36
> > bcp.c:3404:_bcp_get_col_data(45c80, 586ad0)
> > bcp.c:3521:bufpos = 0 , value =>B<
> > bcp.c:3521:bufpos = 1 , value =>O<
> > bcp.c:3521:bufpos = 2 , value =>O<
> > bcp.c:3521:bufpos = 3 , value =>K<
> > bcp.c:3521:bufpos = 4 , value =>1<
> > bcp.c:3463:_bcp_get_col_data bytes_read: 5,
> > terminator>--- On Wed, 22/6/11, Thompson, William
> <[email protected]>
> > wrote:
> >
> > > From: Thompson, William <[email protected]>
> > > Subject: Re: [freetds] bcp problems
> > > To: "'FreeTDS Development Group'" <[email protected]>
> > > Date: Wednesday, 22 June, 2011, 11:25
> > > Hi Panos,
> > >
> > > remember me asking if you were using a
> terminator
> > other
> > > than "\0" ?
> > >
> > > > bcp.c:3463:_bcp_get_col_data bytes_read: 0,
> > > terminator>à<
> > >
> > > Now we just have to figure out how that
> terminator got
> > in
> > > there.
> > >
> > > Bill
> > >
> > >
> >
> ----------------------------------------------------------------------
> > > This message w/attachments (message) is intended
> > solely for
> > > the use of the intended recipient(s) and may
> contain
> > > information that is privileged, confidential or
> > proprietary.
> > > If you are not an intended recipient, please
> notify
> > the
> > > sender, and then please delete and destroy all
> copies
> > and
> > > attachments, and be advised that any review or
> > dissemination
> > > of, or the taking of any action in reliance on,
> the
> > > information contained in or attached to this
> message
> > is
> > > prohibited.
> > > Unless specifically indicated, this message is
> not an
> > offer
> > > to sell or a solicitation of any investment
> products
> > or
> > > other financial product or service, an official
> > confirmation
> > > of any transaction, or an official statement of
> > Sender.
> > > Subject to applicable law, Sender may intercept,
> > monitor,
> > > review and retain e-communications (EC)
> traveling
> > through
> > > its networks/systems and may produce any such EC
> to
> > > regulators, law enforcement, in litigation and
> as
> > required
> > > by law.
> > > The laws of the country of each sender/recipient
> may
> > impact
> > > the handling of EC, and EC may be archived,
> supervised
> > and
> > > produced in countries other than the country in
> which
> > you
> > > are located. This message cannot be guaranteed to
> be
> > secure
> > > or free of errors or viruses.
> > >
> > > References to "Sender" are references to any
> > subsidiary of
> > > Bank of America Corporation. Securities and
> Insurance
> > > Products: * Are Not FDIC Insured * Are Not Bank
> > Guaranteed *
> > > May Lose Value * Are Not a Bank Deposit * Are Not
> a
> > > Condition to Any Banking Service or Activity *
> Are
> > Not
> > > Insured by Any Federal Government Agency.
> Attachments
> > that
> > > are part of this EC may have additional
> important
> > > disclosures and disclaimers, which you should
> read.
> > This
> > > message is subject to terms available at the
> following
> > link:
> > >
> > > http://www.bankofamerica.com/emaildisclaimer. By
> > > messaging with Sender you consent to the
> foregoing.
> > > _______________________________________________
> > > FreeTDS mailing list
> > > [email protected]
> > > http://lists.ibiblio.org/mailman/listinfo/freetds
> > >
> > _______________________________________________
> > FreeTDS mailing list
> > [email protected]
> > http://lists.ibiblio.org/mailman/listinfo/freetds
> >
> >
> ----------------------------------------------------------------------
> > This message w/attachments (message) is intended
> solely for
> > the use of the intended recipient(s) and may contain
> > information that is privileged, confidential or
> proprietary.
> > If you are not an intended recipient, please notify
> the
> > sender, and then please delete and destroy all copies
> and
> > attachments, and be advised that any review or
> dissemination
> > of, or the taking of any action in reliance on, the
> > information contained in or attached to this message
> is
> > prohibited.
> > Unless specifically indicated, this message is not an
> offer
> > to sell or a solicitation of any investment products
> or
> > other financial product or service, an official
> confirmation
> > of any transaction, or an official statement of
> Sender.
> > Subject to applicable law, Sender may intercept,
> monitor,
> > review and retain e-communications (EC) traveling
> through
> > its networks/systems and may produce any such EC to
> > regulators, law enforcement, in litigation and as
> required
> > by law.
> > The laws of the country of each sender/recipient may
> impact
> > the handling of EC, and EC may be archived, supervised
> and
> > produced in countries other than the country in which
> you
> > are located. This message cannot be guaranteed to be
> secure
> > or free of errors or viruses.
> >
> > References to "Sender" are references to any
> subsidiary of
> > Bank of America Corporation. Securities and Insurance
> > Products: * Are Not FDIC Insured * Are Not Bank
> Guaranteed *
> > May Lose Value * Are Not a Bank Deposit * Are Not a
> > Condition to Any Banking Service or Activity * Are
> Not
> > Insured by Any Federal Government Agency. Attachments
> that
> > are part of this EC may have additional important
> > disclosures and disclaimers, which you should read.
> This
> > message is subject to terms available at the following
> link:
> >
> > http://www.bankofamerica.com/emaildisclaimer. By
> > messaging with Sender you consent to the foregoing.
> > _______________________________________________
> > FreeTDS mailing list
> > [email protected]
> > http://lists.ibiblio.org/mailman/listinfo/freetds
> >
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds
> 
> ----------------------------------------------------------------------
> This message w/attachments (message) is intended solely for
> the use of the intended recipient(s) and may contain
> information that is privileged, confidential or proprietary.
> If you are not an intended recipient, please notify the
> sender, and then please delete and destroy all copies and
> attachments, and be advised that any review or dissemination
> of, or the taking of any action in reliance on, the
> information contained in or attached to this message is
> prohibited. 
> Unless specifically indicated, this message is not an offer
> to sell or a solicitation of any investment products or
> other financial product or service, an official confirmation
> of any transaction, or an official statement of Sender.
> Subject to applicable law, Sender may intercept, monitor,
> review and retain e-communications (EC) traveling through
> its networks/systems and may produce any such EC to
> regulators, law enforcement, in litigation and as required
> by law. 
> The laws of the country of each sender/recipient may impact
> the handling of EC, and EC may be archived, supervised and
> produced in countries other than the country in which you
> are located. This message cannot be guaranteed to be secure
> or free of errors or viruses. 
> 
> References to "Sender" are references to any subsidiary of
> Bank of America Corporation. Securities and Insurance
> Products: * Are Not FDIC Insured * Are Not Bank Guaranteed *
> May Lose Value * Are Not a Bank Deposit * Are Not a
> Condition to Any Banking Service or Activity * Are Not
> Insured by Any Federal Government Agency. Attachments that
> are part of this EC may have additional important
> disclosures and disclaimers, which you should read. This
> message is subject to terms available at the following link:
> 
> http://www.bankofamerica.com/emaildisclaimer. By
> messaging with Sender you consent to the foregoing.
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds
>