8. SQL Statements : COPY FROM | INTO PROGRAM : Handler Code Examples : COPY INTO PROGRAM Using Bulk Format
 
Share this page                  
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_INGRES (: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);
}