8. SQL Statements : COPY FROM | INTO PROGRAM : Handler Code Examples : COPY FROM PROGRAM Using Variable-length Formats
 
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_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 from program using variable length formats\n");
EXEC SQL ROLLBACK;
EXEC SQL DISCONNECT;
 
exit(-1);
}