Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2009
    Posts
    4

    Question Unanswered: how to use BCP command in java

    I am developing code in java. Which needs to write 800,000 records to Sybase. using prepared statements or statement is found very inefficient. So I am planning to create files and trying to use BCP command to copy the fields. But I do not know to implement in java. Can you please help?

  2. #2
    Join Date
    Jun 2009
    Location
    South Africa
    Posts
    33
    Java itself does not support the ASE bulkcopy routines as part of the API ( from what I could see anyways).

    It should be fairly simple to call BCP from within a java app. A quick Google search will indicate that you can use the Runtime object :

    Runtime rt = Runtime.getRuntime();
    Process pr = rt.exec("c:\\helloworld.exe");

    Once you have created the files that contains the data, simply call bcp with the required parameters to insert the data. Bear in mind that you need to properly format your output to "delimit" the data. As a default, column data is seperated using a "tab" character (\t) and depending on your OS, rows are either terminated with a newline character (\n) on the Unix / Linux platforms, or with a Carriage return & Linefeed or "crlf" (\r\n) combination for Windows.

    You can specify your own delimiters, should some of your data perhaps contain a tab or newline, or even crlf characters, as BCP will incorrectly assume that these are delimiters for the columns or even rows, and then generate errors or incorrect data.

    If you run BCP from the command-line with no additional parameters, it will print out a quick help screen on the parameter usage. The most important ones will be the following ones :
    -U [Username]
    -P [Password]
    -S [ASE Server]
    -t [Field or column terminator] ( default "\t" - use own sequence of more than one character to ensure that data is properly terminated )
    -r [Row terminator] ( default is "\n" or "\r\n" - use own sequence of more than one character to ensure rows are properly terminated )
    -e [Error_file_name] ( File into which errors that occur will be logged )
    -T [Maximum_text_or_image_size] ( default size is 32768 {32K} and larger objects will be trimmed. Max is 2Gb )
    -c ( indicates character mode i.e. numeric data will be translated to/from the character representation instead of native - so an unsigned int with a value of 65535 will be represented by the 5 character string "65535" instead of the "native" 4-byte value "0x0000ffff" )

    An example of the recommended minimum command-line for bcp'ing into a server would be :
    bcp [db_name.][owner.]table_name in <full_name_of_data_file> -U <username> -P <password> -S <servername> -t "<character_sequence>" -r "<character_sequence>" -c
    e.g.
    bcp pubs2.dbo.authors in authors.bcp -U sa -P L3tm31n -S ASE15 -t "~|~" -r "~!|" -c

    If your data file looks like this, bcp would successfully insert Bart & Lisa Simpson as authors into the pubs2..authors table. Assuming that the <\t> and the <\n> characters "actual" tab and linefeed characters, this will safely ignore the <\t> and <\n> characters which is part of the data, instead of incorrectly interpreting them as field and row terminators :
    1234~|~Simpson~|~Bart~|~123-555-6789~|~The Simpsons Home<\n>Next to Ned Flanders~|~Springfield~|~NT~|~USA~|~49007~!|
    1235~|~Simpson~|~Lisa~|~123-555-6789~|~Same Place As Bart<\t>Next to Uncle Ned~|~Springfield~|~NT~|~USA~|~49007~!|

    Please note that the last field/column is NOT terminated by a seperate "~|~" sequence, but is followed by the row terminator "~!|".

    hth

  3. #3
    Join Date
    Jun 2009
    Posts
    4
    Thanks a lot

  4. #4
    Join Date
    Jun 2009
    Posts
    4
    Hi
    I tried as explained but it is not writing to data base table. When I use the bcp command in unix it works. Is there any file path specification. I mean file needs to be written to the table must be in any particular directory or some thing. Please let me know.

    Thanks

  5. #5
    Join Date
    Jun 2009
    Posts
    4
    I could able to write to tables. this is the string I used to write to table
    bcp TAbleName TextFilePath-U DatabaseUserId -P Password -S ServerName-c -t \\t -r \\n -c. Now I am looking for how can I get the return code of BCP to a file.

    Thanks

  6. #6
    Join Date
    Jun 2009
    Location
    South Africa
    Posts
    33
    You can use the waitFor() method to wait for the bcp execution to complete and return the exit status of BCP directly to the app :

    int exitVal = pr.waitFor();
    System.out.println("BCP exited with error code "+exitVal);

    If it MUST go to a file, you can write it to any file from the app itself.
    Also bear in mind that an exit status of 0 indicates no error.

Posting Permissions

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