/* Copyright © Oracle Corporation 1995.  All Rights Reserved. */
/* 
SQL$CONVERT_LISTS.SC demonstrates how to convert chained lists to indexed 
lists for Rdb/VMS V4.2 or later. 

Before running this program, define RDMS$DIAG_FLAGS to L:

$ DEFINE RDMS$DIAG_FLAGS L

This will prevent the opening of a read only scroll list cursor if the lists 
are chained.  

To build this program, perform the following steps:

1.  Run the program through the SQL precompiler for the C language.

    $ SQL$PRE
    INPUT_FILE> SQL$CONVERT_LISTS/CC/G_FLOAT

2.  Link the program using the following options file (default file type .OPT):

    SQL$USER/LIB
    SYS$LIBRARY:VAXCRTLG/SHARE

    The link command line is:

    $ LINK SQL$CONVERT_LISTS, SYS$INPUT/OPT

3.  Run the program against a Personnel database that has chained lists.
*/

#include <stdio.h> 
#include <descrip.h> 
#include <string.h> 

/* Declare variables and set up descriptor structure to hold error message 
text. */

int	    convert_count = 0;
short       errbuflen;
char        errbuf[ 1024 ];
$DESCRIPTOR (errbufdsc,errbuf);


void lib$stop( void);
long int SQL$GET_ERROR_TEXT();
int check_error( );

/* Include the SQLCA structure to hold error messages. */ 

EXEC SQL INCLUDE SQLCA;

/* Attach to the Personnel database. */                                    

EXEC SQL DECLARE ALIAS FILENAME PERSONNEL_LISTS;

main()
{

/* Set up arrays to temporarily hold the information contained in the lists. */

char    blob[8];
char	emp_id[6];
char	seg1[81];
char	seg2[81];

/* Declare cursors for retrieving and loading list columns: 

Declare table_cursor as a table cursor.  

Declare scroll_cursor as a read only scroll list cursor. This cursor cannot
be opened if the logical RDMS$DIAG_FLAGS is set to L and if lists are chained. 

Declare read_lists_cursor as a read only list cursor that fetches all values for
lists.  

Declare store_lists_cursor as an insert only list cursor that re-inserts all
the values from read_lists_cursor into the database.  

Upon reinsertion, the lists are in indexed format rather than in chained 
format. */


    exec sql declare table_cursor cursor for 
             select employee_id, resume from resumes;
    exec sql declare scroll_cursor read only scroll list cursor for 
             select resume where current of table_cursor;
    exec sql declare read_lists_cursor read only list cursor for 
             select resume where current of table_cursor;
    exec sql declare store_lists_cursor insert only list cursor for 
             select resume where current of table_cursor;

    exec sql open table_cursor;
    if (SQLCA.SQLCODE != 0)
        check_error();

    while(1)
    {
	exec sql fetch table_cursor into :emp_id, :blob;
	if (SQLCA.SQLCODE == 100)
	    break;
	else    
	    if (SQLCA.SQLCODE != 0)
	        check_error();

	exec sql open scroll_cursor;

/* When the logical RDMS$DIAG_FLAGS is defined as "L", the attempt to open the
READ ONLY SCROLL list cursor fails if the lists in the accessed database are
chained. Such an open statement should cause a value of -1 to be placed in the
SQLCODE structure. The program tests for SQLCODE = -1,  and searches for the
error text indicating that the cursor cannot be opened. If this is the error,
the program continues. However, if a different error has caused SQLCODE to be
set to -1, the program aborts.  */


	if (SQLCA.SQLCODE == -1)
	{
	    SQL$GET_ERROR_TEXT( &errbufdsc, &errbuflen );
	    if (strncmp(errbuf,"%SQL-F-CURNOTOPE",16) != 0)
		check_error;

/* Keep a count of the lists that are converted. */

            convert_count++;

/* Open read_lists_cursor to fetch the contents of all lists in the database.*/

	    exec sql open read_lists_cursor;
	    if (SQLCA.SQLCODE != 0)
	        check_error();

/* Open store_lists_cursor to insert the contents of the list back into the 
database, this time as indexed lists. */

	    exec sql open store_lists_cursor;
	    if (SQLCA.SQLCODE != 0)
	        check_error();

/* Temporarily store in array seg1 the values retrieved by read_lists_cursor.*/

	    while (SQLCA.SQLCODE == 0)
	    {
		exec sql fetch read_lists_cursor into :seg1;	
		if (SQLCA.SQLCODE != 0)
		   if (SQLCA.SQLCODE == 100)
			break;
		    else
		        check_error();		

/* Insert the values stored in seg1 into the database using store_lists_cursor.
*/

		exec sql insert into cursor store_lists_cursor values (:seg1);
		if (SQLCA.SQLCODE != 0)
	            check_error();
             }

/* Close all open cursors. */

	     exec sql close read_lists_cursor;
	     exec sql close store_lists_cursor; 
	}
        else
            exec sql close scroll_cursor;
    }
    exec sql close table_cursor;

    exec sql commit;

    printf( "\n %d lists have been successfully converted to indexed format.",
           convert_count);
}

/* Error-handling section. */

check_error( )
{

    if (SQLCA.SQLCODE != 0)
    {
        printf( "SQLCODE = %d\n", SQLCA.SQLCODE );
        SQL$GET_ERROR_TEXT( &errbufdsc, &errbuflen );
        errbuf[ errbuflen ] = 0;
        printf("%s\n", errbuf );
	exec sql rollback;
	lib$stop();
    }
}