Thread: Placing array in temp table

  1. #1
    Registered User
    Join Date
    Apr 2008
    Posts
    115

    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;

  2. #2
    Hurry Slowly vart's Avatar
    Join Date
    Oct 2006
    Location
    Rishon LeZion, Israel
    Posts
    6,788
    you know - there is such thing as indentation
    All problems in computer science can be solved by another level of indirection,
    except for the problem of too many layers of indirection.
    – David J. Wheeler

  3. #3
    Frequently Quite Prolix dwks's Avatar
    Join Date
    Apr 2005
    Location
    Canada
    Posts
    8,057
    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.
    dwk

    Seek and ye shall find. quaere et invenies.

    "Simplicity does not precede complexity, but follows it." -- Alan Perlis
    "Testing can only prove the presence of bugs, not their absence." -- Edsger Dijkstra
    "The only real mistake is the one from which we learn nothing." -- John Powell


    Other boards: DaniWeb, TPS
    Unofficial Wiki FAQ: cpwiki.sf.net

    My website: http://dwks.theprogrammingsite.com/
    Projects: codeform, xuni, atlantis, nort, etc.

  4. #4
    Registered User
    Join Date
    Apr 2008
    Posts
    115

    Thanks for that reminder

    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*/

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. Replies: 2
    Last Post: 07-11-2008, 07:39 AM
  2. progarm doesnt compile
    By kashifk in forum Linux Programming
    Replies: 2
    Last Post: 10-25-2003, 05:54 PM
  3. extra word printing
    By kashifk in forum C++ Programming
    Replies: 2
    Last Post: 10-25-2003, 04:03 PM
  4. Need help
    By duty11694 in forum C Programming
    Replies: 6
    Last Post: 11-17-2002, 03:23 PM
  5. Help with an Array
    By omalleys in forum C Programming
    Replies: 1
    Last Post: 07-01-2002, 08:31 AM