Placing array in temp table

Hi. I spent all of testerday making the code below that selects all the records from the cc_lkup table and places them in array of structs cc_lkup2[]. I have been asked to now place the contents of the array into a temp table called codes. Can someone please help me with a solution. I am really struggling with embeddd SQL and I am waiting for INGRES to call me back with training schedule. The jnl, log_it and err_supv are just functions we use for error handling. Thanks for any help.

-Carl

Code:

---

EXEC SQL DECLARE c1 CURSOR for
SELECT cc, cc_sn, cc_desc, cc_grp
FROM cc_lkup
WHERE cc_grp != ''
ORDER BY cc;

status = err_supv(apl_nm, prog_nm, jnl.blk, stmt_purpose, &retry_cnt,
&err_num, err_txt, &row_count, jnl.val1, jnl.val2, popup_flag);

jnl.blk = 1701;
sprintf(jnl.mesg,"Selecting all records in cc_lkup that contain a group");
sprintf(jnl.grp,"CJ");
sprintf(jnl.mod,"Project.sc");
sprintf(stmt_purpose,"Selecting all records that contain a group");
log_it(log_file, jnl);

if (status != IMCS_DB_SUCCESS)
exit(1);

EXEC SQL OPEN c1;

status = err_supv(apl_nm, prog_nm, jnl.blk, stmt_purpose, &retry_cnt,
&err_num, err_txt, &row_count, jnl.val1, jnl.val2, popup_flag);

jnl.blk = 1702;
sprintf(jnl.mesg,"Opening cursor name c1");
sprintf(jnl.grp,"CJ");
sprintf(jnl.mod,"Project.sc");
sprintf(stmt_purpose,"Opening cursor name c1");
log_it(log_file, jnl);

if (status != IMCS_DB_SUCCESS)
exit(1);

/* While loop to get all records from database table cc_lkup */
while (1)
{
EXEC SQL FETCH c1
INTO :cc_lkup2[y].cc, :cc_lkup2[y].cc_sn, :cc_lkup2[y].cc_desc, :cc_lkup2[y].cc_grp;
status = err_supv(apl_nm, prog_nm, jnl.blk, stmt_purpose, &retry_cnt,
&err_num, err_txt, &row_count, jnl.val1, jnl.val2, popup_flag);
if (row_count ==0 || status != IMCS_DB_SUCCESS)/*row_count value is 1 when array is filling with records and 0 when no records are left*/
break;
y++; /* increment array */
}

/*Write the details below to log when cursor is done finding records*/
/* or if none were found*/
if (row_count ==0)
{
jnl.blk = 1800;
sprintf(jnl.mesg,"Cursor is done placing records in my array");
sprintf(jnl.grp,"CJ");
sprintf(jnl.mod,"Project.sc");
sprintf(stmt_purpose,"Cursor is done placing records in my array");
log_it(log_file, jnl);
}

if (status != IMCS_DB_SUCCESS)
{
jnl.blk = 1805;
sprintf(jnl.mesg,"Cursor failed to place records into the array");
sprintf(jnl.grp,"CJ");
sprintf(jnl.mod,"Project.sc");
sprintf(stmt_purpose,"Cursor failed to place records into the array");
log_it(log_file, jnl);
}

/*Declared in main */
totalrecords = y;/*y holds the total number of records found*/

EXEC SQL CLOSE c1;

---

you know - there is such thing as indentation

It seems to me that you'd be able to build a table out of an array by iterating through the array, with a for loop, say, and using some SQL command or other to insert each item into the table.

Do you know how you would do this in ordinary SQL? That would be the place to start. Once you can do it by hand, then you can try to automate it.

I was tring to make this more complex than it was. Thanks for reminding me to use embedded SQL instead of creating code. Solution is below if anyone cares.

Code:

---

42    /* DROP TABLE THAT WAS CREATED BELOW IN LAST INSTANCE OF THE PROGRAM THAT RAN */
    43    /* IF NOT WHEN EXECUTING PROGRAM IT WILL ERROR SAYING DUPLICATE NAMES */
    44    EXEC SQL DROP TABLE CJCOST;
    45
    46    /* BEGIN CREATION OF CJCOST TABLE AND INSERT ALL RECORDS FROM CC_LKUP */
    47    EXEC SQL CREATE TABLE CJCOST as
    48    SELECT cc, cc_sn, cc_desc, cc_grp
    49    FROM cc_lkup;
    50
    51    jnl.blk = 1600;
    52    sprintf(jnl.mesg,"Placing all records from cc_lkup into CJCOST");
    53    sprintf(jnl.grp,"CJ");
    54    sprintf(jnl.mod,"Project.sc");
    55    sprintf(stmt_purpose,"Placing all records from cc_lkup into CJCOST");
    56    log_it(log_file, jnl);
    57
    58    /* Verify no errors were encountered during table creation */
    59    status = err_supv(apl_nm, prog_nm, jnl.blk, stmt_purpose, &retry_cnt,
    60    &err_num, err_txt, &row_count, jnl.val1, jnl.val2, popup_flag);
    61
    62    /* EXIT PROGRAM IF ERROR CREATING TABLE */
    63    if (status != IMCS_DB_SUCCESS)
    64      exit(1);
    65
    66    EXEC SQL COMMIT;/*IF NO ERROR FOUND ABOVE CREATE THE TABLE PERMENATELY*/
    67    /*NOTE: CURSOR BELOW AND ARRAY ARE DELETED ONCE PRGRAM IS EXITED - TEMP*/
    68
    69    /* BEGINNING OF CURSOR */
    70    EXEC SQL DECLARE c1 CURSOR for
    71    SELECT cc, cc_sn, cc_desc, cc_grp
    72    FROM CJCOST
    73    WHERE cc_grp != ''
    74    ORDER BY cc;
    75
    76    status = err_supv(apl_nm, prog_nm, jnl.blk, stmt_purpose, &retry_cnt,
    77    &err_num, err_txt, &row_count, jnl.val1, jnl.val2, popup_flag);
    78
    79    jnl.blk = 1701;
    80    sprintf(jnl.mesg,"Selecting all records in CJCOST that contain a group");
    81    sprintf(jnl.grp,"CJ");
    82    sprintf(jnl.mod,"Project.sc");
    83    sprintf(stmt_purpose,"Selecting all records that contain a group");
    84    log_it(log_file, jnl);
    85
    86    if (status != IMCS_DB_SUCCESS)
    87    exit(1);
    88
    89    EXEC SQL OPEN c1;
    90
    91    status = err_supv(apl_nm, prog_nm, jnl.blk, stmt_purpose, &retry_cnt,
    92    &err_num, err_txt, &row_count, jnl.val1, jnl.val2, popup_flag);
    93
    94    jnl.blk = 1702;
    95    sprintf(jnl.mesg,"Opening cursor name c1");
    96    sprintf(jnl.grp,"CJ");
    97    sprintf(jnl.mod,"Project.sc");
    98    sprintf(stmt_purpose,"Opening cursor name c1");
    99    log_it(log_file, jnl);
    100
    101    if (status != IMCS_DB_SUCCESS)
    102    exit(1);
    103
    104    /* While loop to get all records from database table CJCOST */
    105    while (1)
    106    {
    107    EXEC SQL FETCH c1
    108    INTO :cc_lkup2[y].cc, :cc_lkup2[y].cc_sn, :cc_lkup2[y].cc_desc, :cc_lkup2[y].cc_grp;
    109    status = err_supv(apl_nm, prog_nm, jnl.blk, stmt_purpose, &retry_cnt,
    110    &err_num, err_txt, &row_count, jnl.val1, jnl.val2, popup_flag);
    111    if (row_count ==0 || status != IMCS_DB_SUCCESS)/*row_count value will be 1 while array is filling with records*/
    112    /* - 0 when no records are left or found*/
    113    break;
    114    y++; /* increment array */
    115    }
    116
    117    /* Write the details below to the journal if err_supv finds error while cursor*/
    118    /* is placing all the records into the array and exit if error found*/
    119    if (status != IMCS_DB_SUCCESS)
    120    {
    121    jnl.blk = 1805;
    122    sprintf(jnl.mesg,"Cursor failed to place records into the array");
    123    sprintf(jnl.grp,"CJ");
    124    sprintf(jnl.mod,"Project.sc");
    125    sprintf(stmt_purpose,"Cursor failed to place records into the array");
    126    log_it(log_file, jnl);
    127    exit(1);
    128    }
    129
    130    /*Write the details below to the journal when cursor is done finding records*/
    131    /* or if none were found*/
    132    if (row_count ==0)
    133    {
    134    jnl.blk = 1800;
    135    sprintf(jnl.mesg,"Cursor is done placing records in my array");
    136    sprintf(jnl.grp,"CJ");
    137    sprintf(jnl.mod,"Project.sc");
    138    sprintf(stmt_purpose,"Cursor is done placing records in my array");
    139    log_it(log_file, jnl);
    140    }
    141
    142    totalrecords = y;/*y holds the total number of records which I need later in the program*/
    143
    144
    145    EXEC SQL CLOSE c1;/*When cursor is closed all values returned by err_supv are reset*/

---