Handler Code Examples
The following examples show how user-defined handlers move data between host variables and the tuple buffer.
Note: Only the C programming language supports COPY handlers.
COPY INTO PROGRAM Using Bulk Format
/*
** 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.
*/
#include <stdio.h>
#include <time.h>
EXEC SQL INCLUDE SQLCA;
void Print_Error();
main()
{
int put_row();
EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL END DECLARE SECTION;
EXEC SQL CONNECT dbname;
if (sqlca.sqlcode != 0)
{
Print_Error();
exit(1);
}
EXEC SQL DROP TABLE t;
EXEC SQL WHENEVER SQLERROR call Print_Error;
/* 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 INSERT INTO t VALUES
(4, 'this is row four', 4.4,'row four varchar');
EXEC SQL COPY TABLE t() INTO PROGRAM WITH COPYHANDLER = put_row;
EXEC SQL DISCONNECT;
printf("\nTerminated successfully\n");
exit(0);
}
int
put_row(byte_length, row, bytes_written)
int *byte_length;
char *row;
int *bytes_written;
{
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] = *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 %d: %s %f %s (%d characters)\n", col1, col2, col3,
col4, col4len);
/* Number of bytes read accurate? */
*bytes_written = fromp - row;
if (*bytes_written != *byte_length)
return 1;
return 0;
}
void Print_Error()
{
EXEC SQL BEGIN DECLARE SECTION;
Char error_buf[2000];
EXEC SQL END DECLARE SECTION;
EXEC SQL INQUIRE_SQL (:error_buf = ERRORTEXT);
printf("\nSQL Error:\n\n%s \n", error_buf);
printf("\nTerminated with Errors \n");
EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("SQL: Exiting Copy into program using bulk format\n");
EXEC SQL ROLLBACK;
EXEC SQL DISCONNECT;
exit(-1);
}
COPY FROM PROGRAM Using Fixed-length Formats
/*
** 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.
*/
#include <stdio.h>
#include <time.h>
EXEC SQL INCLUDE SQLCA;
/* 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 "},
{ 4, " ", 0.0, " "},
{ 0, "", 0.0, ""}
};
static int row_num = 0;
void Print_Error();
main()
{
int get_row();
EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL END DECLARE SECTION;
EXEC SQL CONNECT dbname;
if (sqlca.sqlcode != 0)
{
Print_Error();
exit(1);
}
EXEC SQL DROP TABLE t;
EXEC SQL WHENEVER SQLERROR call Print_Error;
EXEC SQL CREATE TABLE t (col1 INTEGER not null,
col2 CHAR(20) not null,
col3 FLOAT not null,
col4 DATE not null);
EXEC SQL COPY TABLE t (col1=INTEGER,
col2=CHAR(20),
col3=FLOAT8,
col4=CHAR(25))
FROM PROGRAM WITH COPYHANDLER = get_row;
EXEC SQL DISCONNECT;
printf("\nTerminated successfully\n");
exit(0);
}
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;
}
void Print_Error()
{
EXEC SQL BEGIN DECLARE SECTION;
Char error_buf[2000];
EXEC SQL END DECLARE SECTION;
EXEC SQL INQUIRE_SQL (:error_buf = ERRORTEXT);
printf("\nSQL Error:\n\n%s \n", error_buf);
printf("\nTerminated with Errors \n");
EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("SQL: Exiting Copy from program using fixed length formats\n");
EXEC SQL ROLLBACK;
EXEC SQL DISCONNECT;
exit(-1);
}
COPY FROM PROGRAM Using Variable-length Formats
/* 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.
*/
#include <stdio.h>
#include <time.h>
EXEC SQL INCLUDE SQLCA;
char *get_data[] = {
" 1/ 19this is the 1st row/ 1.1/12-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;
void Print_Error();
main()
{
int get_row();
EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL END DECLARE SECTION;
EXEC SQL CONNECT dbname;
if (sqlca.sqlcode != 0)
{
Print_Error();
exit(1);
}
EXEC SQL DROP TABLE t;
EXEC SQL WHENEVER SQLERROR call Print_Error;
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;
printf("\nTerminated successfully\n");
exit(0);
}
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;
}
void Print_Error()
{
EXEC SQL BEGIN DECLARE SECTION;
Char error_buf[2000];
EXEC SQL END DECLARE SECTION;
EXEC SQL INQUIRE_SQL (:error_buf = ERRORTEXT);
printf("\nSQL Error:\n\n%s \n", error_buf);
printf("\nTerminated with Errors \n");
EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("SQL: Exiting Copy from program using variable length formats\n");
EXEC SQL ROLLBACK;
EXEC SQL DISCONNECT;
exit(-1);
}