Home

	Expand Search

Utility Guide

Chapter 3 Using bcp to Transfer Data to and from Adaptive Server

Changing the defaults: interactive bcp

Changing the defaults: interactive bcp

If you do not specify native (-n) or character (-c) format, bcp prompts you interactively for:

• The file storage type

• The prefix length

• The terminator for each column of data to be copied

• A field length for fields that are to be stored as char or binary

The default values for these prompts produce the same results as using the native format and provide a simple means for copying data out of a database for later reloading into Adaptive Server.

If you are copying data to or from Adaptive Server for use with other programs, base your answers to the prompts on the format required by the other software.

These four prompts provide an extremely flexible system that allows you either to read a file from other software or to create a file that requires little or no editing to conform to many other data formats.

The following sections discuss these prompts and the way they interact to affect the data.

Responding to bcp prompts

When you copy data in or out using the -n (native format) or -c (character format) parameters, bcp prompts you only for your password, unless you supplied it with the -P parameter. 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 NT) Scale and precision for numeric and decimal data types

  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

  Table 3-3 shows the bcp prompts, defaults, and the possible alternate user responses:

  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

File storage type

The file storage type prompt offers you choices about how to store the data in the file. You can copy data into a file as:

• Its database table type,

• A character string, or

• Any datatype for which implicit conversion is supported.

  bcp copies user-defined datatypes as their base types.

Table 3-4 shows the default storage type for each Adaptive Server datatype and the abbreviations that are acceptable to bcp.

• For the most compact storage, use the default value.

• For character files, use char.

• Keep in mind that the date storage type is the Adaptive Server internal storage format of datetime, not the host operating system format of the date.

• timestamp data is treated as binary(8).

In Table 3-4, brackets [ ] indicate that you can use the initial character or the beginning characters of the word. For example, for "bit" you can use "b," "bi," or "bit."

To display this list while using bcp interactively, type a question mark (?) in response to the prompt "Enter the file storage type".

The suggested values that appear in the prompts are the defaults. Remember that your response determines how the data is stored in the output file; you need not indicate the column's type in the database table.

bcp fails if you enter a type that is not either implicitly convertible or char. For example, you may not be able to use smallint for int data (you may get overflow errors), but you can use int for smallint.

When storing noncharacter datatypes as their database types, bcp writes the data to the file in Adaptive Server's internal data representation format for the host operating system, rather than in human-readable form.

Before copying data that is in character format from a file into a database table, check the datatype entry rules in the Sybase Adaptive Server Enterprise Reference Manual. Character data copied into the database with bcp must conform to those rules. Note especially that 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.

Prefix length

By default, bcp precedes each field that has a variable storage length with a string of one or more bytes indicating the length of the field. This prefix enables the most compact file storage.

The default values in the prompts indicate the most efficient prefix length:

• For fixed-length fields, the prefix length should be 0.

• For fields of 255 bytes or less, the default prefix length is 1.

• For text or image datatypes, the default prefix length is 4.

• For binary and varbinary datatypes that are being converted to char storage types, the default prefix length is 2, since each byte of table data requires 2 bytes of file storage.

• For binary, varbinary, and image data, use even numbers for the prefix and length. This requirement maintains consistency with Adaptive Server, which stores data as an even number of hexadecimal digits.

• For any data column that permits null values, use a prefix length, other than 0, or a terminator to denote the length of each row's data. bcp considers such columns, including columns with integer datatypes that might ordinarily be considered fixed-length columns, to be of variable length.

• For data with no prefix before its column, use a prefix length of 0.

A prefix length is a 1-, 2-, or 4-byte integer that represents the length of each data value in bytes. It immediately precedes the data value in the host file.

Unless you supply a terminator, bcp pads each stored field with spaces to the full length specified at the next prompt, "length."

Because prefix lengths consist of native format integers, the resulting host file contains nonprintable characters. The nature of these characters could prevent you from printing the host file or from transmitting it through a communications program that cannot handle non-human-readable characters.

For more information about prefix lengths, see Table 3-9.

Field length

In almost all cases, use the bcp default value for the storage length while copying data out.

The terms "length" and "storage length" in this section refer to the operating system file, not to Adaptive Server field lengths.

• If you are creating a file to reload into Adaptive Server, the default prefixes and length keep the storage space needed to a minimum.

• If you are creating a human-readable file, the default length prevents the truncation of data or the creation of overflow errors that cause bcp to fail.

Because you can change the default length by supplying another value, you must be familiar with the data to transfer. If you are copying character data in from other software, examine the source file carefully before choosing length values.

If the storage type is noncharacter, bcp stores the data in the operating system's native data representation and does not prompt for a length.

When bcp converts noncharacter data to character storage, it suggests a default field length that is large enough to store the data without truncating datetime data or causing an overflow of numeric data.

• The default lengths are the number of bytes needed to display the longest value for the Adaptive Server datatype. Table 3-5 lists the default field lengths for data conversion to character storage.

  Default field lengths for noncharacter to character datatypes

  Datatype	Default size
  int	12 bytes
  smallint	6 bytes
  tinyint	3 bytes
  float	25 bytes
  money	24 bytes
  bit	1 byte
  datetime	26 bytes
  smalldatetime	26 bytes
  real	25 bytes
  smallmoney	24 bytes

• If you specify a field length that is too short for numeric data when copying data out, bcp prints an overflow message and does not copy the data.

• The default length for binary and varbinary fields is twice the length defined for the column, since each byte of the field requires 2 bytes of file storage.

• If you accept the default storage length, the actual amount of storage space allocated depends on whether or not you specify a prefix length and terminators.

  • If you specify a prefix length of 1, 2, or 4, bcp uses a storage space of the actual length of the data, plus the length of the prefix, plus any terminators.

  • If you specify a prefix length of 0 and no terminator, bcp allocates the maximum amount of space shown in the prompt, which is the maximum space that may be needed for the datatype in question. In other words, bcp treats the field as if it were fixed length to determine where one field ends and the next begins.

    For example, if the field is defined as varchar(30), bcp uses 30 bytes for each value, even if some of the values are only 1 character long.

• Fields defined in the database as char, nchar, and binary, and those that do not permit null values, 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 the varchar and varbinary fields is longer than the length specified for copy out, bcp silently truncates the data in the file at the specified length.

• bcp does not know how large any one data value will be before copying all the data, so it always pads char datatypes to their full specified length.

• 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. If the 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 that can be transferred for the column, excluding terminators and/or prefixes.

• When copying data into a table, bcp observes any defaults defined for columns and user-defined datatypes. However, bcp ignores rules in order to load data at the fastest possible speed.

• bcp considers any data column that can contain a null value to be variable length, so use either a length prefix or a terminator to denote the length of each row of data.

• The file storage type and length of a column need not be the same as the type and length of the column in the database table. (If types and formats copied in are incompatible with the structure of the database table, the copy fails.)

Field and row terminators

You can use a terminator to mark the end of a column or row, separating one from the next. The default is no terminator.

• Field terminators separate table columns.

• A row terminator is a field terminator for the last field in the row of the table or file.

Terminators are very useful for dealing with character data because you can choose human-readable terminators. The bcp character option, which uses tabs between each column with a newline terminator at the end of each row, is an example of using terminators that enhance the readability of a data file.

When you prepare data for use with other programs, and when you want to use bcp to prepare tabular data, supply your own terminators. The available terminators are:

• Tabs, indicated by

  \t

• New lines, indicated by

  \n

• Carriage returns, indicated by

  \r

• Backslash, indicated by

  \

• Null terminators (no visible terminator), indicated by

  \0

• Any printable character, for example, *, A, t, |

• Strings of up to 10 printable characters, including some or all of the terminators listed above (for example, **\t**, end, !!!!!!!!!!, and \t--\n)

Control characters (ASCII 0-25) cannot be printed.

Choose terminators with patterns that do not appear in any of the data.

For example, using a tab terminator with a string of data that also contains a tab creates an ambiguity: which tab represents the end of the string? bcp always looks for the first possible terminator, which in this case would be incorrect, since the first tab it would encounter would be the one that is part of the data string.

Data in native format can also conflict with terminators. Given a column that contains a 4-byte integer in native format, if the values of these integers are not strictly limited, it will be impossible to choose a terminator that is guaranteed not to appear inside the data. Use bcp's native format option for data in native format.

"No terminator" is different from a "null terminator," which is an invisible, but real, character.

• 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, because bcp always looks for the first possible terminator.

  For example, if you used tab terminators with a string that contained a tab, bcp would not be able to identify which tab represents the end of the string. bcp always looks for the first possible terminator, so, in this example it would find the wrong one.

  A terminator or prefix affects the actual length of data transferred:

  When a terminator or prefix is present, it affects the length of data transferred. If the length of an entry being copied out to a file is less than the storage length, it is immediately followed 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 bcp is 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 the 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.

• Fields stored as char (except char, nchar, and binary fields) instead of their database datatypes take less file storage space with the default length and prefix or a terminator. bcp can use either a terminator or a prefix to determine the most efficient use of storage space. bcp suggests the maximum amount of storage space required for each field as the default. For char or varchar data, bcp accepts any length.

• Table 3-6 and Table 3-7 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. An ellipsis (...) indicates that the pattern repeats for each field. The field length is 8 bytes for each column; "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.