Respond to bcp Prompts

Unless you supplied bcp with the -P parameter, it prompts you for your password when you copy data in or out using the -n (native format) or -c (character format) parameters.

If you do not supply either the -n, -c or -f formatfile parameter, bcp prompts you for information for each field in the table or view.

• Each prompt displays a default value, in brackets, which you can accept by pressing Return. The prompts include:

  • The file storage type, which can be character or any valid Adaptive Server datatype
  • The prefix length, which is an integer indicating the length in bytes of the following data
  • The storage length of the data in the file for non-NULL fields
  • The field terminator, which can be any character string
  • (Windows) Scale and precision for numeric and decimal datatypes

  The row terminator is the field terminator of the last field in the table, view, or file.

• The bracketed defaults represent reasonable values for the datatypes of the field in question. For the most efficient use of space when copying out to a file:

  • Use the default prompts
  • Copy all data in the datatypes defined by their table
  • Use prefixes as indicated
  • Do not use terminators
  • Accept the default lengths

  The bcp prompts, defaults, and the possible alternate user responses are:

  Defaults and User Responses for bcp Prompts

  Prompt	Default Provided	Possible User Response
  File Storage Type	Use database storage type for most fields except: char for varchar binary for varbinary	char to create or read a human-readable file; any Adaptive Server datatype where implicit conversion is supported.
  Prefix Length	0 for fields defined with char datatype (not storage type) and all fixed-length datatypes 1 for most other datatypes 2 for binary and varbinary saved as char 4 for text and image	0 if no prefix is desired; otherwise, defaults are recommended.
  Storage Length	For char and varchar, use defined length. For binary and varbinary saved as char, use double the defined length. For all other datatypes, use maximum length needed to avoid truncation or data overflow.	Default values, or greater, are recommended.
  Field or Row Terminator	None	Up to 30 characters, or one of the following: \t – tab \n – newline \r – carriage return \0 – null terminator \ – backslash

• bcp can copy data out to a file either as its native (database) datatype, or as any datatype for which implicit conversion is supported. bcp copies user-defined datatypes as their base datatype or as any datatype for which implicit conversion is supported. See dbconvert in the Open Client DB-Library/C Reference Manual.
  Note: Be careful when you copy data from different versions of Adaptive Server, because not all versions support the same datatypes.
• A prefix length is a 1-byte, 2-byte, or 4-byte integer that represents the length of each data value in bytes. It immediately precedes the data value in the host file.
• Be sure that fields defined in the database as char, nchar, and binary are always padded with spaces (null bytes for binary) to the full length defined in the database. timestamp data is treated as binary(8).
• If data in varchar and varbinary fields is longer than the length you specify for copy out, bcp silently truncates the data in the file at the specified length.
• A field terminator string can be up to 30 characters long. The most common terminators are a tab (entered as “\t” and used for all columns except the last one), and a newline (entered as “\n” and used for the last field in a row). Other terminators are: “\0” (the null terminator), “\” (backslash), and “\r” (Return). When choosing a terminator, be sure that its pattern does not appear in any of your character data. For example, if you use tab terminators with a string that contains a tab, bcp cannot identify which tab represents the end of the string. bcp always looks for the first possible terminator, in this case, it will find the wrong one.
• When a terminator or prefix is present, it affects the actual length of data transferred. If the length of an entry being copied out to a file is smaller than the storage length, it is followed immediately by the terminator, or the prefix for the next field. The entry is not padded to the full storage length (char, nchar, and binary data is returned from Adaptive Server already padded to the full length).
• When copying in from a file, data is transferred until either the number of bytes indicated in the “Length” prompt has been copied, or the terminator is encountered. Once a number of bytes equal to the specified length has been transferred, the rest of the data is flushed until the terminator is encountered. When no terminator is used, the table storage length is strictly observed.
• These tables show the interaction of prefix lengths, terminators, and field length on the information in the file. “P” indicates the prefix in the stored table; “T” indicates the terminator; and dashes, “--”, show appended spaces. “...” indicates that the pattern repeats for each field. The field length is 8 for each column, and “string” represents the 6-character field each time.

  Adaptive Server char data

  	Prefix length 0	Prefix length 1, 2, or 4
  No terminator	string--string--	Pstring--Pstring--
  Terminator	string--Tstring--T	Pstring--TPstring--T

  Other datatypes converted to char storage

  	Prefix length 0	Prefix length 1, 2 or 4
  No terminator	string--string--	PstringPstring
  Terminator	stringTstringT	PstringTPstringT

• The file storage type and length of a column do not have to be the same as the type and length of the column in the database table. However, if types and formats copied in are incompatible with the structure of the database table, the copy fails.
• File storage length generally indicates the maximum amount of data to be transferred for the column, excluding terminators and prefixes.
• When copying data into a table, bcp observes any defaults defined for columns and user-defined datatypes. However, bcp ignores rules to load data at the fastest possible speed.
• Because bcp considers any data column that can contain null values to be variable length, use either a length prefix or terminator to denote the length of each data row.
• Data written to a host file in its native format preserves all of its precision. datetime and float values preserve all of their precision even when they are converted to character format. Adaptive Server stores money values to a precision of one ten-thousandth of a monetary unit. However, when money values are converted to character format, their character format values are recorded only to the nearest two places.
• Before copying data in character format from a file into a database table, check the datatype entry rules in the Datatypes in Reference Manual: Building Blocks. Character data that is copied into the database with bcp must conform to those rules. Dates in the undelimited (yy)yymmdd format may result in overflow errors if the year is not specified first.
• When you send host data files to sites that use terminals different from your own, inform them of the datafile_charset that you used to create the files.