DataBlade API Programmer's Manual

DataBlade API Programmer's Manual
Chapter 2: Using DataBlade API Data Types

Home Contents Index Master Index New Book

Collections

A collection type is a complex data type whose instances are groups of elements of the same data type. The DataBlade API provides the following functions for collection types:

mi_collection_close()
mi_collection_create()
mi_collection_delete()
mi_collection_fetch()
mi_collection_free()
mi_collection_insert()
mi_collection_open()
mi_collection_update()

Chapter 12, "DataBlade API Function Descriptions," describes each function, in alphabetical order.

The following example illustrates the use of these functions on collections.

The SQL code that follows uses the user-defined routine listpos():

create function listpos() returns integer external name
        '$USERFUNCDIR/sql_listpos.udr' language c;

create table tab2 (a int);
insert into tab2 values (1);

select listpos() from tab2;

drop function listpos();
drop table tab2;

close database;

The following C file contains the functions that implement the listpos() user-defined function:

C file (.c) contents:

/*
 *  Function:    Example of mi_collection_*() functions
 *
 *
 *		This function returns an error code, 0 on success.
 *		Results are printed to stdout, which is the window where the
 *		database server was started.
 *		If this poses a problem, convert to fprintf and write to a file.
 *
 *		This example inserts 1,2,3 into a LIST then moves around inside the
 *		list to verify we can access all the values.
 *		The second example uses a character type.
 */

#include <stdio.h>
#include <milib.h>
#include <sqltypes.h>

void dofetch(MI_CONNECTION *conn,
		MI_COLL_DESC *colldesc,
		MI_CURSOR_ACTION action,
		mi_integer type,
		mi_integer jump,
		MI_DATUM expected);

mi_integer
create_collection(
		MI_CONNECTION *conn,
		char *typestring, 
		MI_COLLECTION **retcoll,
		MI_COLL_DESC **retdesc);

mi_integer list_int_test(MI_CONNECTION *conn);
mi_integer list_char_test(MI_CONNECTION *conn);
mi_integer list_float_test(MI_CONNECTION *conn);

FILE *OutFp;
mi_integer
listpos()
{
    MI_CONNECTION *conn;
    mi_integer rc, error;

    conn = mi_open(NULL, NULL, NULL);

    if (conn == NULL)
        return (-1);

    OutFp = fopen("/tmp/listpos.out", "w");
    if (OutFp == NULL)
		return (-100);

    error = 0;
    rc = list_int_test(conn);
    if (rc)
		error = rc;
    list_char_test(conn);
    if (rc)
		error = rc;
    list_float_test(conn);
    if (rc)
		error = rc;

    fclose(OutFp);

    if (!error)
		unlink("/tmp/listpos.out");

    return (rc);
}
mi_integer
list_int_test(MI_CONNECTION *conn)
{
    MI_COLLECTION *list;
    MI_COLL_DESC *colldesc;

    MI_CURSOR_ACTION action;
    mi_integer jump, value, rc;

    rc = create_collection(conn, "list(int not null)", &list, &colldesc);
    if (rc != 0)
		return (rc);

    action = MI_CURSOR_ABSOLUTE;

    /*
     *	Insert three values
     *
     *  Normally one would use an action of MI_CURSOR_NEXT (jump is
     *		ignored), but this is testing inserts at positions.
     */

    value = jump = 1;
    fprintf(OutFp, "insert %d into list @%d\n", value, jump);
    rc = mi_collection_insert(conn, colldesc, (MI_DATUM) value, action, jump);
    if (rc != MI_OK)
		printf ("insert MI_CURSOR_ABSOLUTE %d @ %d failed\n", value, jump);

    value = jump = 2;
    fprintf(OutFp, "insert %d into list @%d\n", value, jump);
    rc = mi_collection_insert(conn, colldesc, (MI_DATUM) value, action, jump);
    if (rc != MI_OK)
		printf ("insert MI_CURSOR_ABSOLUTE %d @ %d failed\n", value, jump);

    value = jump = 3;
    fprintf(OutFp, "insert %d into list @%d\n", value, jump);
    rc = mi_collection_insert(conn, colldesc, (MI_DATUM) value, action, jump);
    if (rc != MI_OK)
		printf ("insert MI_CURSOR_ABSOLUTE %d @ %d failed\n", value, jump);


    /* we use a jump equal to the data value to simplify the test validation */
    dofetch(conn, colldesc, MI_CURSOR_ABSOLUTE, SQLINT, 1, (MI_DATUM) 1);
    dofetch(conn, colldesc, MI_CURSOR_ABSOLUTE, SQLINT, 3, (MI_DATUM) 3);
    dofetch(conn, colldesc, MI_CURSOR_ABSOLUTE, SQLINT, 2, (MI_DATUM) 2);
    dofetch(conn, colldesc, MI_CURSOR_PRIOR, SQLINT, 1, (MI_DATUM) 1);
    dofetch(conn, colldesc, MI_CURSOR_LAST, SQLINT, 3, (MI_DATUM) 3);
    dofetch(conn, colldesc, MI_CURSOR_FIRST, SQLINT, 1, (MI_DATUM) 1);
    dofetch(conn, colldesc, MI_CURSOR_RELATIVE, SQLINT, 2, (MI_DATUM) 3);
    dofetch(conn, colldesc, MI_CURSOR_RELATIVE, SQLINT, -2, (MI_DATUM) 1);

    /* update 1st element to 3. */
    jump=1;
    value=3;
    fprintf(OutFp, "update set value %d @%d\n", value, jump);
    rc = mi_collection_update(conn, colldesc, (MI_DATUM) value, action, jump);
    if (rc != MI_OK)
		fprintf(OutFp, "update @ %d value %d MI_CURSOR_ABSOLUTE failed\n", jump,

		value);


    /* fetch it back */
    dofetch(conn, colldesc, MI_CURSOR_ABSOLUTE, SQLINT, 1, (MI_DATUM) 3);

	mi_collection_close(conn, colldesc);
    mi_collection_free(conn, list);
    return 0;
}
mi_integer
list_float_test(MI_CONNECTION *conn)
{
    MI_COLLECTION *list;
    MI_COLL_DESC *colldesc;

    MI_CURSOR_ACTION action;
    mi_integer jump, value, rc;
    double val1, val2, val3, val4;

    rc = create_collection(conn, "list(float not null)", &list, &colldesc);
    if (rc != 0)
		return (rc);

    action = MI_CURSOR_ABSOLUTE;

    /*
     *		Insert three values: float and double precision datums are passed
     *		by reference.
     */

    val1 = 1.1;
    val2 = -2.2;
    val3 = 3.3;

    jump = 1;
    fprintf(OutFp, "insert %f into list @%d\n", val1, jump);
    rc = mi_collection_insert(conn, colldesc, (MI_DATUM) &val1, action, jump);
    if (rc != MI_OK)
		printf ("insert MI_CURSOR_ABSOLUTE %f @ %d failed\n", val1, jump);

    jump = 2;
    fprintf(OutFp, "insert %f into list @%d\n", val2, jump);
    rc = mi_collection_insert(conn, colldesc, (MI_DATUM) &val2, action, jump);
    if (rc != MI_OK)
		printf ("insert MI_CURSOR_ABSOLUTE %f @ %d failed\n", val2, jump);

    jump = 3;
    fprintf(OutFp, "insert %f into list @%d\n", val3, jump);
    rc = mi_collection_insert(conn, colldesc, (MI_DATUM) &val3, action, jump);
    if (rc != MI_OK)
		printf ("insert MI_CURSOR_ABSOLUTE %f @ %d failed\n", val3, jump);


    /* we use a jump equal to the data value to simplify the test validation */
    dofetch(conn, colldesc, MI_CURSOR_ABSOLUTE, SQLFLOAT, 1, (MI_DATUM) &val1);
    dofetch(conn, colldesc, MI_CURSOR_ABSOLUTE, SQLFLOAT, 3, (MI_DATUM) &val3);
    dofetch(conn, colldesc, MI_CURSOR_ABSOLUTE, SQLFLOAT, 2, (MI_DATUM) &val2);
    dofetch(conn, colldesc, MI_CURSOR_PRIOR, SQLFLOAT, 1, (MI_DATUM) &val1);
    dofetch(conn, colldesc, MI_CURSOR_LAST, SQLFLOAT, 3, (MI_DATUM) &val3);
    dofetch(conn, colldesc, MI_CURSOR_FIRST, SQLFLOAT, 1, (MI_DATUM) &val1);
    dofetch(conn, colldesc, MI_CURSOR_RELATIVE, SQLFLOAT, 2, (MI_DATUM) &val3);
    dofetch(conn, colldesc, MI_CURSOR_RELATIVE, SQLFLOAT, -2, (MI_DATUM) &val1);

    /* update 1st element to 3. */
    jump=1;
    val4=44e-4;
    fprintf(OutFp, "update set value %f @%d\n", val4, jump);
    rc = mi_collection_update(conn, colldesc, (MI_DATUM) &val4, action, jump);
    if (rc != MI_OK)
		fprintf(OutFp, "update @ %d value %f MI_CURSOR_ABSOLUTE failed\n", jump,

		value);


    /* fetch it back */
    dofetch(conn, colldesc, MI_CURSOR_ABSOLUTE, SQLFLOAT, 1, (MI_DATUM) &val4);

    mi_collection_close(conn, colldesc);
    mi_collection_free(conn, list);
    return 0;
}
mi_integer
list_char_test(MI_CONNECTION *conn)
{
    MI_COLLECTION *list;
    MI_COLL_DESC *colldesc;

    MI_CURSOR_ACTION action;
    MI_DATUM val;
    mi_integer retlen, jump, rc;
    mi_lvarchar  *lvc;
    char *buf;
    char *val1, *val2, *val3;

    rc = create_collection(conn, "list(char(10) not null)", &list, &colldesc);
    if (rc != 0)
		return (rc);

    action = MI_CURSOR_ABSOLUTE;

    val1 = "1234567689";
    val2 = "abcdefghij";
    val3 = "three";

	lvc = mi_new_var(10);
	buf = mi_get_vardata(lvc);

    /*
     *		Insert three values
     */

    jump = 1;
    strcpy(buf, val1);
    fprintf(OutFp, "Insert LVARCHAR '%s' into list of varchar @%d\n", buf, jump);
    rc = mi_collection_insert(conn, colldesc, (MI_DATUM)lvc, action, jump);
    if (rc != MI_OK)
		printf ("insert MI_CURSOR_ABSOLUTE @%d failed\n", jump);

    jump = 2;
    strcpy(buf, val2);
    fprintf(OutFp, "Insert LVARCHAR '%s' into list of varchar @%d\n", buf, jump);
    rc = mi_collection_insert(conn, colldesc, (MI_DATUM)lvc, action, jump);
    if (rc != MI_OK)
		printf ("insert MI_CURSOR_ABSOLUTE @%d failed\n", jump);

    jump = 3;
    strcpy(buf, val3);
    fprintf(OutFp, "Insert LVARCHAR '%s' into list of varchar @%d\n", buf, jump);
    rc = mi_collection_insert(conn, colldesc, (MI_DATUM)lvc, action, jump);
    if (rc != MI_OK)
		printf ("insert MI_CURSOR_ABSOLUTE @%d failed\n", jump);

    dofetch(conn, colldesc, MI_CURSOR_ABSOLUTE, SQLCHAR, 1, val1);
    dofetch(conn, colldesc, MI_CURSOR_ABSOLUTE, SQLCHAR, 3, val3);
    dofetch(conn, colldesc, MI_CURSOR_ABSOLUTE, SQLCHAR, 2, val2);
    dofetch(conn, colldesc, MI_CURSOR_PRIOR, SQLCHAR, 1, val1);
    dofetch(conn, colldesc, MI_CURSOR_LAST, SQLCHAR, 3, val3);
    dofetch(conn, colldesc, MI_CURSOR_FIRST, SQLCHAR, 1, val1);
    dofetch(conn, colldesc, MI_CURSOR_RELATIVE, SQLCHAR, 2, val3);
    dofetch(conn, colldesc, MI_CURSOR_RELATIVE, SQLCHAR, -2, val1);


    jump=1;
    strcpy(buf, "mnopqrstuv");
    fprintf(OutFp, "Update LVARCHAR '%s' into list @ %d \n", buf, jump);
    rc = mi_collection_update(conn, colldesc, (MI_DATUM)lvc, action, jump);
    if (rc != MI_OK)
		printf ("update MI_CURSOR_ABSOLUTE @%d with value 3 failed\n");

    /* validate the update */
    dofetch(conn, colldesc, MI_CURSOR_FIRST, SQLCHAR, 1, buf);

    mi_collection_close(conn, colldesc);
    mi_collection_free(conn, list);
    return 0;
}

void
dofetch(MI_CONNECTION *conn,
		MI_COLL_DESC *colldesc,
		MI_CURSOR_ACTION action,
		mi_integer type,
		mi_integer jump,
		MI_DATUM expected)
{
    MI_DATUM val;
    mi_integer retlen, rc;
    char *actionstr, *buf;

    switch (action)
    {
		case MI_CURSOR_NEXT:
			actionstr="MI_CURSOR_NEXT";
			break;
		case MI_CURSOR_PRIOR:
			actionstr="MI_CURSOR_PRIOR";
			break;
		case MI_CURSOR_FIRST:
	 		actionstr="MI_CURSOR_FIRST";
	 		break;
		case MI_CURSOR_LAST:
	 		actionstr="MI_CURSOR_LAST";
	  		break;
		case MI_CURSOR_ABSOLUTE:
	   		actionstr="MI_CURSOR_ABSOLUTE";
	  		break;
		case MI_CURSOR_RELATIVE:
	   		actionstr="MI_CURSOR_RELATIVE";
	  		break;
		default:
	   		actionstr="UNKNOWN";
    }

    fprintf(OutFp, "fetch %s @ jump=%d:", actionstr, jump);

    /* print out what is the expected value */
    switch (type)
    {
		case SQLINT:
	  		fprintf(OutFp, "should get %d: ", expected);
	 		break;
		case SQLCHAR:
	   		fprintf(OutFp, "should get '%s': ", expected);
	   		break;
		case SQLFLOAT:
	   		fprintf(OutFp, "should get %f: ", *(double *) expected);
	   		break;
		default:
	   		fprintf(OutFp, "type not handled: %d\n", type);
    }

    rc = mi_collection_fetch(conn, colldesc, action, jump, &val, &retlen);
    if (rc != MI_OK)
		{
		printf ("%s @%d failed\n", actionstr, jump);
		return;
		}

    switch (type)
    {
		case SQLINT:
	    		if (expected != val)
	 			fprintf(OutFp, "FAILED: got %d\n", val);
	    		else
	        			fprintf(OutFp, "PASSED\n");
	   		break;
		case SQLCHAR:
	   		buf = mi_get_vardata((mi_lvarchar *) val);
	   		if (strcmp(buf, (char *) expected) != 0)
	        			fprintf(OutFp, "FAILED: got %s\n", buf);
	    		else
	        			fprintf(OutFp, "PASSED\n");
	   		break;
		case SQLFLOAT:
	 		if (*(double *) expected != *(double *) val)
	        			fprintf(OutFp, "FAILED: got %f\n", *(double *) val);
	    		else
	        			fprintf(OutFp, "PASSED\n");
	   		break;
		default:
	   		fprintf(OutFp, "type not handled: %d\n", type);
    }
}
/* create a collection of the specified type */
mi_integer
create_collection(
		MI_CONNECTION *conn,
		char *typestring, 
		MI_COLLECTION **retcoll,
		MI_COLL_DESC **retdesc)
{
    MI_TYPEID *typeid;
    MI_COLLECTION *collp;
    MI_COLL_DESC *colldesc;

    typeid = mi_typestring_to_id(conn, typestring);

    if (typeid == NULL)
    {
      fprintf(OutFp, "mi_typestring_to_id FAILED\n");
      return (-50);
    }

    if ((collp = mi_collection_create(conn, typeid)) == NULL)
    {
      fprintf(OutFp, "mi_collection_creat FAILED\n");
      return (-51);
    }

    if ((colldesc = mi_collection_open(conn, collp)) == NULL)
    {
      fprintf(OutFp, "mi_collection_open FAILED\n");
      return -52;
    }
    *retdesc = colldesc;
    *retcoll = collp;
    return 0;
}

DataBlade API Programmer's ManualChapter 2: Using DataBlade API Data Types Home Contents Index Master Index New Book

Collections

DataBlade API Programmer's Manual
Chapter 2: Using DataBlade API Data Types

Home Contents Index Master Index New Book