Re: 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
>