Results 1 to 9 of 9
  1. #1
    Join Date
    May 2002
    Location
    St. Paul, MN
    Posts
    1

    Post Unanswered: bcp in Sybase 12

    I need help with bcp in a Sybase 12 database.

    Works with bcp in Sybase 11.1 to a same Sybase 12 database but not in Sybase 12 to the Sybase 12 database.

    table structure (looking a db with Microsoft Access)

    prod_id text 11
    plant text 2
    qty_onhand long integer
    units text 3
    ytd_qty long integer
    active_date date/time
    prod_name text 30

    out_actprod.dat
    41260085562AC 0LBS 005/03/2002FRAC PRIMARY C6F14

    actprod_bcp_fmt
    10.0
    7
    1 SYBCHAR 0 11 "" 1 product_id
    2 SYBCHAR 0 2 "" 2 plant
    3 SYBCHAR 0 7 "" 3 qty_onhand
    4 SYBCHAR 0 3 "" 4 units
    5 SYBCHAR 0 8 "" 5 ydt_qty
    6 SYBCHAR 0 10 "" 6 active_date
    7 SYBCHAR 0 30 "" 7 product_nm


    bcp database.table in out_actprod.dat -factprod_bcp.fmt -Uuserid -Ppassword

    error message -

    Internal Message: - Msg 999, Level 11, State 0:
    [generic/ctbcp/blk.c/276] ctbcp:executeBulkCopy(): Both terminator and prefix cannot be null for nullable column 6.

    bcp copy in failed

    Anybody have any ideas?

  2. #2
    Join Date
    Nov 2002
    Location
    New York
    Posts
    1
    Hi,

    I guess, the problem is at 6th row.

    `6 SYBCHAR 0 10 "" 6 active_date`

    active_date column in database should be not null column.

  3. #3
    Join Date
    Sep 2002
    Location
    St Louis, MO
    Posts
    8
    Originally posted by haieswar2k
    Hi,

    I guess, the problem is at 6th row.

    `6 SYBCHAR 0 10 "" 6 active_date`

    active_date column in database should be not null column.
    Sir, You are making a huge assumption that the database can be converted to NOT NULL. If this were the case, the field would probably be NOT NULL already, and this issue wouldn't have come up.

    Can someone give me a fix WITHOUT requiring impossible database changes? IE., the col is nullable because, well, because we have NULLs.

  4. #4
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159

    Re: bcp in Sybase 12

    error message -

    Internal Message: - Msg 999, Level 11, State 0:
    [generic/ctbcp/blk.c/276] ctbcp:executeBulkCopy(): Both terminator and prefix cannot be null for nullable column 6.

    bcp copy in failed

    The error messages suggests the field terminators are null along with the column. Do you need to use the format file or can you get away with using either character (-c) or native format (-n)?

    If you're using the -c you ought to specify field terminators that do not occur in your data. The defaults are , and new line. To change the terminator use the -r and -t parameters; row terminator and field terminator respectively.

  5. #5
    Join Date
    Sep 2002
    Location
    St Louis, MO
    Posts
    8
    Richard,

    Thanks for the reply!

    The input file is positional, not delimited. I would think the entire file could be nulls, along with every col in the db as nullable, and it should still work. I have to use the format file to say what the positions are.


    Thanks again!

    Still confused,
    Mark

  6. #6
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159
    The input file is positional, not delimited.
    If the file is positional, by definition there must be a value in every column. You might associate a dummy value to "NULL", but as far as Sybase is concerned there will be a value.

    If your flat file is going to contain nulls, how are you going to know which columns are null using character positions?

  7. #7
    Join Date
    Sep 2002
    Location
    St Louis, MO
    Posts
    8
    Richard,

    If you consider blanks a value, then yes: every col will have a value. We have been using the same bcp format files and data files for 10 years.

    As to your second question:

    Quite easily. Say Colx is positions 57-59 in the flat file. If positions 57-59 are blanks or spaces, this is null. Been that way for years, we are a little stunned Sybase would change the basic operation of bcp after all this time.



    Thanks,
    Mark

  8. #8
    Join Date
    Sep 2002
    Location
    St Louis, MO
    Posts
    8

    Sybase BCP 12 needs -Q

    Originally posted by richardcrossley
    If the file is positional, by definition there must be a value in every column. You might associate a dummy value to "NULL", but as far as Sybase is concerned there will be a value.

    If your flat file is going to contain nulls, how are you going to know which columns are null using character positions?

    Turns out the answer is -Q on the cmd line of bcp. Not sure why just yet, and not sure why we didn't need them before, but that is definately the fix.


    Mark Godfrey

  9. #9
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159

    Re: Sybase BCP 12 needs -Q

    Originally posted by MGodfrey
    Turns out the answer is -Q on the cmd line of bcp. Not sure why just yet, and not sure why we didn't need them before, but that is definately the fix.


    It says in Sybooks for ASE 12.5:
    -Q
    Provides backward compatibility with bcp version 10.0.4 for copying operations involving nullable columns.

    Richard.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •