Ingres Database Reference
Document #: US-54302,EN
------------------------------------------------------------------------------
Author: Alexander Hamilton
Abstract:
How to perform "in memory" COPY FROM/INTO. This is an UNSUPPORTED feature for
6.3/03 and 6.4 Unix and VMS C only. A SUPPORTED syntax will be added in a
future release supporting all languages.
Interim Support for COPY FROM/INTO Memory
-----------------------------------------
This outlines an INGRES 6.3/03 & 6.4 feature: COPY FROM/INTO PROGRAM. It
is available in C on Unix and VMS only. In a future release, this syntax
will be replaced by a new syntax supported in all languages.
Using COPY FROM/INTO PROGRAM in an application allows you to copy data
from or to memory. It provides the quickest way to bulk load data. The COPY
statement you write differs by declaring a user-coded handler in the COPY
statement WITH clause. On a COPY INTO PROGRAM statement, COPY will call the
user handler passing each row it receives from the dbms. On a COPY FROM
PROGRAM, COPY will call the handler for the next row of data until the handler
indicates that there are no more rows.
The syntax of COPY FROM/INTO PROGRAM is as follows:
ESQL:
EXEC SQL COPY TABLE tabname ([format_string]) FROM|INTO PROGRAM
WITH COPYHANDLER = func_name;
EQUEL:
## COPY tabname ([format_string]) FROM|INTO PROGRAM
WITH COPYHANDLER = func_name;
The COPYHANDLER option on the WITH clause tells INGRES the name of the
function to call to get/put a row of data. 'func_name' must be a user-
defined function. Although you should not declare 'func_name' in an ESQL
declare section or through an EQUEL ## mark, it must be declared in a way
that will satisfy the C compiler.
Other WITH clause options accepted by the COPY FROM/INTO file may be used
on the COPY FROM/INTO PROGRAM statement. (See note on "WITH ERROR = CONTINUE"
below).
All types of row formats are acceptable on COPY FROM/INTO PROGRAM. The
character formats (variable or fixed) are the easiest to program. If
numeric formats are used, remember that numeric entities are not aligned
on any special boundary in the row. This is significant on Unix. On COPY
INTO PROGRAM, a handler on Unix must byte-copy numerics into aligned memory
before assigning the values. ON COPY FROM PROGRAM, the handler must byte
copy numeric formats into the row buffer: they cannot be directly assigned
because of alignment problems. One of the examples below illustrates this.
For fixed length formats, the length and characteristics of the data are
as follows. On a COPY INTO, this describes how the data is formatted by
INGRES into the row buffer handed to the user-defined handler; on a COPY
FROM, the user-defined handler must format the data according to these
formats.
integer 4 bytes
integer with null 5 bytes
float 8 bytes
float with null 9 bytes
character(n) n bytes
character(n) with null n + 1 bytes
character(n) with null('value') n bytes
varchar(n) n + 5 bytes for the count
(char representation)
varchar(n) with null n + 1 + 5 bytes
varchar(n) with null('value') n + 5 bytes
For variable length formats (char(0) and varchar(0)), the data may be of
any length. Varchar(0) is self describing because the data is preceded by
a 5-byte count. A delimiter must be used with char(0).
For bulk copy, the formats for integer, float and character are the same
as fixed length. The varchar format differs in that the count is
represented in internal format, i,e., as a 2-byte integer quantity.
The formats for date and money are internal and are not described here.
It is assumed that a user-defined handler will use a character copy format
for date and a character or float format for money.
The user defined handlers for COPY FROM PROGRAM and COPY INTO PROGRAM each
take three parameters and must return an integer status of 0 to INGRES to
indicate success. If a handler returns a non-zero status, INGRES will
raise an error and abort COPY. (The "on_error = continue" clause does not
apply to reading/writing to a file or a user program -- it applies to
row->tuple and tuple->row conversions in COPY).
The arguments passed by COPY must be declared in the user-defined handler
with the following types:
Argument Type Description
COPY TO PROGRAM
byte_length Pointer to long (Input) Byte length of row
row Pointer to char (Input) A row of data
dummy Pointer to long Currently unused
COPY FROM PROGRAM
byte_length Pointer to long (Input). For fixed length
formats indicates the expected
byte count of the user-supplied
row. For variable length,
indicates the maximum length
of row.
row Pointer to char (Output) Pointer to row buffer
that handler fills.
bytes_used Pointer to long Number of bytes of row filled
by handler. If bytes_used is
set to zero, COPY does not call
handler again. On fixed length
formats, INGRES raises an error
if a non-zero bytes_used is not
equal to byte_length. On
variable length formats a
non-zero bytes_used is ignored.
The following are some examples of handlers coded in C. These examples
show how to move data between host variables and the tuple buffer. The use
of COPY handlers is available in C only.
Example 1
/*
** Copy into program using bulk format
** Table contains:
** integer
** char(20)
** float
** varchar(20)
** The user-defined handler parses the tuple and stores each column in a
** variable; then prints the whole row.
*/
main()
{
int put_row();
exec sql begin declare section;
exec sql end declare section;
exec sql connect dbname;
exec sql drop table t;
/* For test purposes create table and insert three rows of data */
exec sql create table t (col1 integer,
col2 char(20),
col3 float,
col4 varchar(20));
exec sql insert into t values (1,
'this is row one',
1.1,
'row one varchar');
exec sql insert into t values (2,
'this is row two',
2.2,
'row two varchar');
exec sql insert into t values (3,
'this is row three',
3.3,
'row three varchar');
exec sql copy table t () into program with copyhandler = put_row;
exec sql disconnect;
}
int
put_row(byte_length, row, dummy)
int *byte_length;
char *row;
int *dummy;
{
char *fromp, *top; /* Byte pointers for copying row */
int i;
int col1; /* Variables corresponding to columns */
char col2[21];
double col3;
short col4len;
char col4[21];
fromp = row;
/* Read off integer col1 */
top = (char *)&col1;
for (i = 1; i <=sizeof(int); i++)
*top++ = *fromp++;
fromp++; /* Skip null indicator byte */
/* Read off char(20) col2 */
for (i = 0; i < 20; i++)
col2[i] = *fromp++;
col2[20] = '\0';
fromp++; /* Skip null indicator byte */
/* Read off float col3 */
top = (char *)&col3;
for (i = 1; i <= sizeof(double); i++)
*top++ = *fromp++;
fromp++; /* Skip null indicator byte */
/* Read off varchar col4 -- 5-char count first */
top = (char *)&col4len;
for (i = 1; i <=sizeof(short); i++)
*top++ = *fromp++;
/* Read off varchar text col4 according to count */
for (i = 0; i < col4len; i++)
col4[i] = *fromp++;
col4[col4len] = '\0';
/* Skip remaining bytes of varchar(20) */
for (i = 20-col4len; i > 0; i--)
fromp++;
fromp++; /* Skip null indicator byte */
printf ("Row is %d %s %f %d (count) %s\n", col1, col2, col3, col4len,
col4);
/* Number of bytes read accurate? */
if (fromp - row != *byte_length)
return 1;
return 0;
}
Example 2:
/*
** Copy from program using fixed length formats.
** Table contains:
** integer
** char(20)
** float
** date
**
** Copy uses formats:
** integer
** char(20)
** float8
** char(25)
**
** The user-defined handler copies data from a structure into the tuple
** buffer. Because formats are not nullable, the handler does not have
** to place a "null terminator" byte or other null indicator into the
** buffer.
*/
/* Declare some "canned" data */
struct {
int col1;
char col2[21];
double col3;
char col4[25];
} get_data[] = {
{ 1,"this is the 1st row ", 1.1, "21-mar-1991 "},
{ 2,"this is the 2nd row ", 2.2, "today "},
{ 3,"this is row three ", 3.3, "19-apr-91 "},
{ 0, "", 0.0, ""}
};
static int row_num = 0;
main()
{
int get_row();
exec sql begin declare section;
exec sql end declare section;
exec sql connect dbname;
exec sql drop table t;
exec sql create table t (col1 integer,
col2 char(20),
col3 float,
col4 date);
exec sql copy table t (col1=integer,
col2=char(20),
col3=float8,
col4=char(25))
from program with copyhandler = get_row;
exec sql disconnect;
}
int
get_row(byte_length, row, bytes_used)
int *byte_length;
char *row;
int *bytes_used;
{
int i;
char *top = row;
char *fromp;
if (get_data[row_num].col2[0] == '\0')
{
*bytes_used = 0; /* Indicate all rows copied */
return 0;
}
/* Copy integer data a byte at a time */
fromp = (char *)&get_data[row_num].col1;
for (i = 0; i < sizeof(int); i++)
{
*top++ = *fromp++;
}
fromp = get_data[row_num].col2;
/* Copy 20 bytes of char data */
for (i = 0; i < 20; i++)
{
*top++ = *fromp++;
}
fromp = (char *)&get_data[row_num].col3;
/* Copy float data a byte at a time */
for (i = 0; i < sizeof(double); i++)
{
*top++ = *fromp++;
}
fromp = get_data[row_num].col4;
/* Copy 25 bytes of char data */
for (i = 0; i < 25; i++)
{
*top++ = *fromp++;
}
*bytes_used = top - row;
row_num++;
return 0;
}
Example 3
/* Copy from program using variable length formats.
** Table contains:
** integer
** char(20)
** float
** date
** varchar(20)
**
** Copy uses formats:
** char(0)'/'
** varchar(0)'/'
** char(0)'/'
** char(0)'/'
** varchar(0)nl
**
** The user-defined handler copies data from a structure into the row
** buffer a whole row at a time. The fourth row contains null data in
** columns 2,3,4 and 5.
*/
char *get_data[] = {
"1 / 19this is the 1st row / 1.1 /21-mar-1991/ 11first again\n",
"2 / 19this is the 2nd row / 2.2 /today / 12second again\n",
"3 / 19this is the 3rd row / 3.3 /19-apr-91 / 11third again\n",
"4 / 4null /null /null / 4null\n",
""
};
static int row_num = 0;
main()
{
int get_row();
exec sql begin declare section;
exec sql end declare section;
exec sql connect dbname;
exec sql drop table t;
exec sql create table t (col1 integer,
col2 char(20),
col3 float,
col4 date,
col5 varchar(20));
/* Test with some good data */
exec sql copy table t (col1=char(0)'/' with null ('null'),
col2=varchar(0)'/' with null ('null'),
col3=char(0)'/' with null ('null'),
col4=char(0)'/' with null ('null'),
col5=varchar(0)nl with null ('null'))
from program with copyhandler = get_row;
exec sql disconnect;
}
int
get_row(byte_length, row, bytes_used)
int *byte_length;
char *row;
int *bytes_used;
{
int i;
char ** data;
char *top = row;
char *fromp;
if (get_data[row_num][0] == '\0')
{
*bytes_used = 0; /* Indicate all rows copied */
row_num = 0;
return 0;
}
strcpy(row, get_data[row_num]);
*bytes_used = strlen(row);
row_num++;
return 0;
}
Releases affected: 6.4/00(all.all) - Releases not affected:
Errors:
Bugs/SIRS:
Ingres Database Reference
To William's Home Page
© William Yuan 2000
Email William