Informix DataBlade API Programmer's Manual Executing SQL Statements

When mi_get_result() returns the MI_ROWS statement status, the query is executed and a cursor is opened. If the query returned rows, the rows are in the open cursor. In the DataBlade API, each query row has two parts:

• The row descriptor is the data-description portion, which contains information such as row size and column data types.
• The row structure is the data portion, which is the data that the query returns.

A one-to-one correspondence occurs between row descriptors and rows. They have basically the same structure. However, a row descriptor needs to change on a row-to-row basis for jagged rows. For more information, see Obtaining Jagged Rows.

To retrieve the row of query data, take the following steps:

1. Get a row descriptor for a query row.
2. Get the number of columns from the row descriptor.
3. Retrieve query rows, one row at a time.
4. For every query row, get the value of any desired column.

A row descriptor (MI_ROW_DESC) contains information about the columns in a row. For example, the row descriptor for the following query would contain two columns, order_num and order_date:

SELECT order_num, order_date FROM orders
WHERE ship_date > "07/15/98"

To obtain a row descriptor for a row, use one of the DataBlade API functions in the following table.

These functions allocate the memory for the row descriptor that they allocate. To free this row descriptor, complete the query. (For more information, see Completing Execution.) To obtain a row descriptor for the query rows, use the mi_get_row_desc_without_row() function.

Once you have a row descriptor for the row, you can obtain information about the columns with the row-descriptor access functions, which Figure 5-11 on page 5-24 shows. For each column in an SQL statement, you can obtain information about the column (such as its data type) from the row descriptor.

You can use the mi_column_count() function to determine how many columns are in the row. The number of columns in the row descriptor is the number of columns that the query retrieves. Use this value to control the number of times to call the mi_value() or mi_value_by_name() function. Each call to mi_value() or mi_value_by_name() passes back one column value from the row structure to the DataBlade API module. For more information, see Obtaining Column Values.

After a query executes, a cursor holds the query rows. The function that actually obtains the rows from a cursor is mi_next_row(), as follows:

• Obtain access to the current row.
• Execute the mi_next_row() function in a loop that iterates for each query row.

For a sample function that shows one way to use mi_next_row() to retrieve query rows, see Example: get_data() Function.

The mi_next_row() function accesses rows in the cursor that is associated with the current statement. Because a current statement is associated with a connection, you must pass a connection descriptor into mi_next_row() to identify the cursor to access. From this cursor, mi_next_row() obtains the current row. The current row is the row in the cursor that the cursor position identifies. Each time mi_next_row() retrieves a row, this cursor position moves by one. One cursor per connection is current and within this cursor, only one row at a time is current.

The mi_next_row() function returns the current row as a row structure (MI_ROW structure). Each row structure stores the column values from a single query row. Both the row structure and the row descriptor have the same number of columns. You can obtain column values from the row structure with the mi_value() or mi_value_by_name() function. For more information, see Obtaining Column Values.

The mi_next_row() function is usually the middle loop of row-retrieval code. In the mi_next_row() loop, each call to mi_next_row() returns one query row. This query row is the current row only until the next iteration of the loop, when mi_next_row() retrieves another row from the cursor. This loop terminates when mi_next_row() returns a NULL-valued pointer and its error argument is zero (0). These conditions indicate either that no more rows exist in the cursor or that the cursor is empty. Think of the mi_next_row() loop as an iteration over the matching rows of the query.

The contents of a row structure become invalid as soon as you fetch a new row into it with mi_next_row(). If you want to save the row values that you obtain with mi_value() or mi_value_by_name(), copy the values that these functions pass back.

Tip: If your DataBlade API module requires simultaneous access to several rows at a time, you can use a save set to hold rows. Save sets are useful for comparing or processing multiple rows. For more information, see Using Save Sets.

The mi_next_row() function allocates memory for the row structure that it returns. To free this row structure, you must complete the query. For more information, see Completing Execution.

As long as rows remain to be retrieved from the cursor, the mi_get_result() function returns a statement status of MI_ROWS. Therefore, you cannot exit the mi_get_result() loop until one of the following actions occurs:

• The mi_next_row() loop continues until no more rows exist in the cursor. That is, mi_next_row() returns a NULL-valued pointer.
• You terminate the mi_get_result() loop prematurely with a call to mi_query_finish() or mi_query_interrupt().

When the mi_next_row() function retrieves a query row from the cursor, it returns this row in a row structure. The DataBlade API provides the following functions to get actual column values from a row structure.

Use mi_value() or mi_value_by_name() to retrieve columns from the current row as follows:

• Execute the mi_value() or mi_value_by_name() function in a loop that iterates for each desired column value.
• Interpret the value status that these functions return to correctly access the column value.

The final section, Example: get_data() Function, contains sample code that shows one way to use mi_value() to get column values.

The mi_value() or mi_value_by_name() function is usually called in the innermost loop of row-retrieval code. In the column-value loop, mi_value() or mi_value_by_name() retrieves a column value from the current row. This loop terminates when a value is retrieved for every column in the row (or every column your DataBlade API module needs to access). You can obtain the number of columns in a row with the mi_column_count() function.

The mi_value() and mi_value_by_name() functions access the row structure for the current row. The current row is in the cursor that is associated with the current statement. Because a current statement is associated with a connection, you must pass a connection descriptor into mi_value() or mi_value_by_name() to identify the row to access.

These functions pass back the column value as an MI_DATUM value. The format of this value depends on whether the control mode for the query data is text or binary representation. Each of the DataBlade API statement-execution functions indicates the control mode for query data. For more information, see Control Modes for Query Data and Determining Control Mode for Query Data.

To obtain this column value, your DataBlade API module must perform the the following steps:

• Declare a value buffer to hold the column value that mi_value() or mi_value_by_name() passes back.
• Obtain the column value from the value buffer, based on the value status that mi_value() or mi_value_by_name() returns.

To obtain the column value, you must pass in a pointer to a value buffer as an argument to mi_value() or mi_value_by_name(). The value buffer is the place that these functions put the column value that they retrieve from the current row. Both mi_value() and mi_value_by_name() represent a column value as a pointer to an MI_DATUM.

You can declare the value buffer in either of the following ways:

• If you know the data type of the column value, declare the value buffer of this data type.

Declare the value buffer as a pointer to the column data type, regardless of whether the data type is passed by reference or by value.

• If you do not know the data type of the column value, declare the value buffer as the MI_DATUM data type.

Your code can dynamically determine column type with the mi_column_type_id() or mi_column_typedesc() function. You can then convert (or cast) the MI_DATUM value to the data type that you need.

The mi_value() and mi_value_by_name() functions allocate memory for the value buffer. However, this memory is only valid until a new SQL statement executes or until the query completes. In addition, the DataBlade API might overwrite the value-buffer data in any of the following cases:

• The mi_next_row() function is called on the same connection.
• A call to mi_row_create() uses the row descriptor.
If you need to save the value-buffer data for later use, you must create your own copy of the data in the value buffer.

Tip: If your DataBlade API module requires simultaneous access to several rows at a time, you can use a save set to hold rows. Save sets are useful for comparing or processing multiple rows. For more information, see Using Save Sets.

The mi_value() and mi_value_by_name() functions return a value status, which identifies how to interpret the column value that these functions pass back. The following table shows the kinds of column values that these functions can identify.

You can use a switch statement based on these value-status constants to determine how to handle the column value.

The mi_value() and mi_value_by_name() functions return the MI_NORMAL_VALUE value status for a column with any data type other than a row type or collection. Therefore, these functions return MI_NORMAL_VALUE for columns that have a built-in data type, smart large object, opaque type, or distinct type.

When the mi_value() or mi_value_by_name() function returns MI_NORMAL_VALUE, the contents of the MI_DATUM that holds the column value depends on whether the control mode for the query data is text or binary representation, as follows:

• Text representation: the MI_DATUM contains a pointer to a null-terminated string, which has the text representation of the column value.
• Binary representation: the MI_DATUM is a value whose interpretation depends on the passing mechanism used, as follows:
• When mi_value() or mi_value_by_name() passes back a column value to a C UDR, it can pass the MI_DATUM by reference or by value, depending on the data type of the column value.
• When mi_value() or mi_value_by_name() passes back a column value to a client LIBMI application, it always passes the MI_DATUM by reference. Even for values that can be passed by value in a C UDR (such as INTEGER), these functions return the column value by reference.

For a list of the text and binary representations of built-in, opaque, and distinct data types, see Figure 8-4 on page 8-13. For more information on the passing mechanism for an MI_DATUM value, see The MI_DATUM Value.

Important: The difference in behavior of mi_value() and mi_value_by_name() between C UDRs and client LIBMI applications means that row-retrieval code is not completely portable between these two types of DataBlade API modules. When you move your DataBlade API code from one of these uses to another, you must change the row-retrieval code to use the appropriate passing mechanism for column values that mi_value() or mi_value_by_name() returns. Column Values Passed Back to a C UDR

Within a C UDR, the value buffer that mi_value() or mi_value_by_name() fills can contain either of the following values:

• For data types that are passed by value, the value buffer contains the actual column value.
• For data types that are passed by reference, the value buffer contains a pointer to this column value.

Tip: The value buffer also contains a pointer to the column value if the return status of mi_value() or mi_value_by_name() is MI_ROW_VALUE or MI_COLLECTION_VALUE. For more information, see Obtaining Row Values and Obtaining Collection Values.

Therefore, within your C UDR, you cannot assume what the MI_DATUM value contains without checking its data type (or length).

If the mi_value() or mi_value_by_name() function passes back an MI_DATUM that contains a data type smaller than the size of an MI_DATUM, the DataBlade API cast promotes the smaller value to the size of MI_DATUM. (For more information, see MI_DATUM in a C UDR). If you now want to pass a pointer to the data value in the MI_DATUM, you might have problems on Windows NT platforms if you passed the address of the MI_DATUM, as the following pseudocode shows:

MI_DATUM datum;
mi_integer length;
mi_char bool;
mi_short small;
mi_int large;
void* pointer;

switch ( mi_value( ..., &datum, &length ) )
	{
	....
	} /* end switch */
/* Assume that 'datum' contains a BOOLEAN value 
** (which uses only one byte of the MI_DATUM). 
** Pass the address of the actual data to some one 
** other function 
** YOU CANNOT ALWAYS DO THIS! 
**                 my_func( &datum, length ); 
** This address might point to the wrong byte! */

The preceding code fragment works if datum always contains a pointer to a column value, or contains data the size of MI_DATUM. However, it might not work on some computer architectures for data that is smaller than MI_DATUM (such as mi_boolean).

To convert the MI_DATUM into a pointer to the data value, you must be sure that the address points to the starting position of the cast-promoted data. The following code fragment determines what the MI_DATUM value in datum contains and then correctly copies the value and obtains its address, based on the length of datum:

MI_ROW_DESC *row_desc;
MI_ROW *row;
MI_DATUM datum;
mi_integer length;
mi_boolean *bool;
mi_smallint *small_int;
mi_integer *full_int;
mi_date *date_val;
mi_string *col_type_name;
void *ptr_to_value;
...
switch ( mi_value(row, i, col_id, &datum, &length) )
	{
	...
	case MI_NORMAL_VALUE:
		col_type_name =
			mi_type_typename(
				mi_column_typedesc(row_desc, i));

/* To obtain the datum value and its address, you have to:
**          1. Check for possible pass-by-value values.
**          2. Assume that datum is pass-by-reference value. */
		switch( length )
			{
		/*    Case 1: Assume that a length of one byte means
		**                    that 'datum' contains a BOOLEAN value. */
			case 1:
				bool = (mi_boolean) datum;
				ptr_to_value = &bool;
				break;

		/*    Case 2: Assume that a length of two bytes means
		**                    that 'datum' contains a SMALLINT value */
			case 2:
				small_int = (mi_smallint) datum;
				ptr_to_value = &small_int;
				break;

		/*    Case 4: Assume that a length of four bytes means
		**                    that 'datum' contains an INTEGER or DATE
		**                    value */
			case 4:
				if ( stcopy(col_type_name, "date") == 0 )
					{
					date_val = (mi_date) datum;
					ptr_to_value = &date_val;
					}
				else /* data type is INTEGER */
					{
					full_int = (mi_integer) datum;
					ptr_to_value = &full_int;
					}
				break;

		/*    Default Case: Assume that any for any other
		**                              lengths, 'datum' contains a pointer
		**                                to the value. */
			default:
				ptr_to_value = &datum;
				break;
	} /* end switch */

my_func( ptr_to_value );

However, the preceding code fragment only handles built-in data types that are passed by value. It is not written to handle all possible user-defined types (such as small fixed-length opaque types) because these do not have unique lengths.

In a C UDR, if the data type of the column value can fit into an MI_DATUM, the value buffer contains the actual column value. If you know the data types of the column values, the value buffers you declare to hold the column values must be declared as pointers to their data type. For example, the code fragment declares the value buffer that holds a SMALLINT value can be declared as follows:

mi_integer *small_int_ptr;

After the call to mi_value(), the C UDR must cast the contents of the value buffer from a pointer variable (as small_int_ptr is declared) to the actual data type. For the SMALLINT value, the code can perform the following cast to create a copy of the column value:

small_int = (mi_smallint) small_int_ptr;

This cast is necessary only for column values whose data types are passed by value because the contents of the MI_DATUM is the actual column value, not a pointer to this data type.

You can use the mi_type_byvalue() function to determine the passing mechanism of the column value that mi_value() passes back, as the following code fragment shows:

row_desc = mi_get_row_desc_without_row(conn);
...
switch ( mi_value(row, i, col_id, &datum, &length) )
	{
	...
	case MI_NORMAL_VALUE:

		if ( mi_type_byname(mi_column_typedesc(row_desc, i))
				== MI_TRUE )
			{
			/* handle pass-by-value data types */;

The mi_type_byvalue() function helps you determine if a one-, two-, or four-byte value is actually passed by value. This function is useful in determine the passing mechanism of fixed-length opaque data types.

Column Values Passed Back to a Client LIBMI Application

The mi_value() and mi_value_by_name() functions pass back by reference column values for all data types. Therefore, the contents of the returned MI_DATUM is always a pointer to the actual value, never the value itself. Even column values whose data type can fit into an MI_DATUM are passed by reference. For example, a SMALLINT value could have the same value-buffer declaration as it would in a C UDR, as follows:

mi_integer *small_int_ptr;

However, unlike a C UDR, the column value in the value buffer does not require a cast to create a copy:

small_int = *small_int_ptr;

In a database, smart large objects are in columns with the data type CLOB or BLOB. A smart-large-object column contains an LO handle that describes the smart large object, including the location of its data in an sbspace. This LO handle does not contain the actual smart-large-object data.

When a query retrieves a smart large object (a BLOB or CLOB column), the mi_value() and mi_value_by_name() functions return the MI_NORMAL_VALUE value status. For a BLOB or CLOB column, the MI_DATUM that these functions pass back contains the LO handle for the smart large object. The control mode of the query data determines whether this LO handle is in text or binary representation, as follows.

When query data is in binary representation, the mi_value() and mi_value_by_name() functions pass back the LO handle by reference. Regardless of whether you obtain a smart large object in a C UDR or a client LIBMI application, the MI_DATUM that these functions pass back contains a pointer to an LO handle (MI_LO_HANDLE*).

To make a copy of the LO handle within your DataBlade API module, you can copy the contents of the value buffer, as follows:

MI_LO_HANDLE *blob_col, my_LO_hdl;
...
switch ( mi_value(row, i, col_id, &blob_col, &length) )
	{
	...
	case MI_NORMAL_VALUE:

		my_LO_hdl = *blob_col;

To obtain the smart-large-object data, use the binary representation of the LO handle with the functions of the smart-large-object interface. The smart-large-object interface allows you to access smart-large-object data through its LO handle. You access the smart-large-object data with read, write, and seek operations similar to an operating-system file.

The following code fragment implements the get_smart_large_object() function, which reads smart-large-object data in 4,000-byte chunks:

#define BUFSIZE 4000;

mi_integer get_smart_large_object(conn, LO_hndl)
	MI_CONNECTION *conn;
	MI_LO_HANDLE *LO_hndl;
{
	MI_LO_FD LO_fd;
	mi_char read_buf[BUFSIZE];

/* Open the selected smart large object */
LO_fd = mi_lo_open(conn, LO_hndl, MI_LO_RDONLY);
if ( LO_fd == MI_ERROR )

	/* handle error */
	return (-1);
else
	{
	while ( mi_lo_read(conn, LO_fd, read_buf, BUFSIZE) 
			!= MI_ERROR )
		{
		/* perform processing on smart-large-object data */
		...
		}
	mi_lo_close(conn, LO_fd);
	return ( 0 );
	}
}

For a description of the smart-large-object interface, see Chapter 6, Using Smart Large Objects.

The mi_value() and mi_value_by_name() functions return the MI_NULL_VALUE value status for a column that contains the SQL NULL value. These functions return MI_NULL_VALUE for columns of any data type. When the mi_value() or mi_value_by_name() function returns MI_NULL_VALUE, the contents of the MI_DATUM that these functions pass back depends on whether the control mode for the query data is text or binary representation, as the following table shows.

Obtaining Row Values

The mi_value() and mi_value_by_name() functions return the MI_ROW_VALUE value status for a column that meets either of the following conditions:

• The column has a row type (a named or unnamed row type) as its data type.
• The item being selected is a correlation variable that represents an entire row.

A correlation variable is used in a select list when jagged rows are selected from a supertable in an inheritance hierarchy, as in the query:

SELECT p FROM parent p

When the mi_value() or mi_value_by_name() function returns MI_ROW_VALUE, the MI_DATUM that these functions pass back contains a pointer to the row structure, regardless of whether the query data is in binary or text representation. You can extract the individual values from the row structure by passing the returned MI_ROW pointer to mi_value() or mi_value_by_name() for each value you need to retrieve.

The mi_value() and mi_value_by_name() functions can return the MI_ROW_VALUE value status for a column with a row data type: unnamed or named. The contents of the MI_DATUM that these functions pass back is a pointer to a row structure that contains the fields of the row type. The format of the field values depends on whether the control mode for the query data is text or binary representation, as the following table shows.

For a list of the text and binary representations of data types, see Figure 8-4 on page 8-13.

You can extract the individual field value from the row type by passing the returned MI_ROW pointer to mi_value() or mi_value_by_name() for each field value you need to retrieve.

The get_data() function (see page 8-75) calls the get_row_data() function for an mi_value() return value of MI_ROW_VALUE. This function takes the pointer to a row structure as an argument and uses mi_value() on it to obtain field values in text representation.

mi_integer get_row_data(row)
	MI_ROW *row;
{
	mi_integer numflds, fldlen;
	MI_ROW_DESC *rowdesc;
	mi_integer i;
	char *fldname, *fldval;
	mi_boolean is_nested_row;

/* Get row descriptor */
	rowdesc = mi_get_row_desc(row);

/* Get number of fields in row type */
	numflds = mi_column_count(rowdesc);

/* Display the field names of the row type */
	for ( i=0; i < numflds; i++ )
		{
		fldname = mi_column_name(rowdesc, i);
		DPRINTF("trc_class, 11, ("%s\t", fldname));
		}
	DPRINTF("trc_class", 11, ("\n"));

/* Get field values for each field of row type */
	for ( i=0, i < numflds; i++ )
		{
		is_nested_row = MI_FALSE;
		switch( mi_value(row, i, &fldval, &fldlen) )
			{
			case MI_ERROR:
				...

			case MI_NULL_VALUE:
				fldval = "NULL";
				break;

			case MI_NORMAL_VALUE:
				break;

			case MI_ROW_VALUE:
			/* have nested row type - make recursive call */
				is_nested_row = MI_TRUE;
				get_row_data((MI_ROW *)fldval);
				break;

			default:
				...

			}
		if ( is_nested_row == MI_FALSE )
			DPRINTF("trc_class", 11, ("%s\t", fldval));
		}
	return (0);
}

When all the rows that a query retrieves are not the same type and length, the rows are called jagged rows. Jagged rows occur as a result of a query that requests all the rows in a supertable and all its subtables using the syntax:

SELECT correlation_variable 

FROM table_name correlation_variable

In the preceding query, table_name represents a supertable in an inheritance hierarchy. Suppose you create the following schema in which the table parent has one column, child has two columns, and grandchild has three columns:

CREATE TABLE parent OF TYPE parent_t (num1 INTEGER);
INSERT INTO parent VALUES (10);

CREATE TABLE child OF TYPE child_t (num2 SMALLFLOAT) 
	UNDER parent;
INSERT INTO child VALUES (20, 3.5);

CREATE TABLE grandchild OF TYPE grandchild_t (name TEXT)
	UNDER child;
INSERT INTO grandchild VALUES (30, 7.8, 'gundrun');

The following SELECT statement queries the parent supertable:

SELECT p FROM parent p

This query returns the following three jagged rows:

The DataBlade API indicates that a query returned a jagged row as follows:

• The mi_value() or mi_value_by_name() function returns a value status of MI_ROW_VALUE.
• The contents of the MI_DATUM that holds the retrieved column is a pointer to a row structure.

The format of the columns depends on whether the control mode for the query data is text or binary representation, as the following table shows.

For a list of the text and binary representations of data types, see Figure 8-4 on page 8-13.

To retrieve jagged rows, follow these steps:

• Use the mi_get_row_desc() function to get a row descriptor for each row structure that mi_value() or mi_value_by_name() obtains.
• Use the mi_column_count() function with the row descriptor to get a column count for each row that mi_next_row() retrieves.
• Retrieve the individual components of the row within an inner column-value loop.

For a collection, the value that the mi_value() and mi_value_by_name() functions return depends on whether the control mode for the query data is text or binary representation, as the following table shows.

In a DataBlade API module, a collection can be created in either of the following ways:

• A column has a collection type (SET, LIST, or MULTISET) as its data type
• An item being selected is a collection subquery, which represents a collection.

When the control mode of the query data is text representation, the mi_value() or mi_value_by_name() function returns a value status of MI_NORMAL_VALUE for a collection column. The value buffer contains the text representation of the column.

For example, suppose that a query selects the set_col column, which is defined as Figure 8-17 shows.

Figure 8-17 A Sample Collection Column

If the set_col column contains a SET with the values of 3, 5, and 7, the value buffer contains the following string after mi_value() or mi_value_by_name() executes:

"SET{3          ,5          ,7          }"

For a description of collection text representation, see Collection Text Representation.

When the control mode of the query data is in binary representation, the mi_value() or mi_value_by_name() function returns a value status of MI_COLLECTION_VALUE for a collection column. The value buffer contains a pointer to the collection structure for the collection. You can extract the individual elements from the collection structure with the DataBlade API collection functions, as follows:

• The mi_collection_open() function opens the collection that the collection structure describes.
• The mi_collection_fetch() functions fetches the elements from the collection. The element that mi_collection_fetch() obtains is in its binary representation.
• The mi_collection_close() function closes the collection that the collection structure describes.

For more information on the use of the DataBlade API collection functions, see Collections.

For the collection column that Figure 8-17 on page 8-73 defines, the following code fragment handles the MI_COLLECTION_VALUE value status that mi_value() or mi_value_by_name() returns for a collection column in binary representation:

switch( mi_value(row, i, &colval, &collen) )
	{
	...
	case MI_COLLECTION_VALUE:
		if ( (colldesc = mi_collection_open(conn,
				(MI_COLLECTION *)colval) != NULL )
			{
			while ( mi_collection_fetch(conn, colldesc, 
					MI_CURSOR_NEXT, 0, (MI_DATUM *)&elmtval,
					&elmtlen) != MI_END_OF_DATA )
				{
				int_val = (mi_integer)elmtval;
				DPRINTF("trc_class", 11, 
					("Element value=%d\n", int_val));
				}
			}
		break;

The get_data() function retrieves data from a query that mi_exec() sends to the database server. This example makes the following assumptions:

• The query data is in text representation because the original call to mi_exec() in the send_statement() function (see page 8-15) specifies the MI_QUERY_NORMAL control flag.
• All the rows are of the same type and therefore share the same row descriptor (that is, no jagged rows).

The code for the get_data() function follows:

/*
** FUNCTION: get_data()
** PURPOSE: Gets rows that a query returns.
**
** CALLED BY: get_results() , see page  8-52.
**/
#include "mi.h"

mi_integer get_data( MI_CONNECTION * conn )
{
	MI_ROW          *row = NULL;
	MI_ROW_DESC     *rowdesc;
	mi_integer      error;
	mi_integer      numcols;
	mi_integer      i;
	mi_string       *colname;
	mi_integer      collen;
	mi_string       *colval;
	mi_integer      is_nested_row;

 
/* Get the row descriptor for the current statement */
	rowdesc = mi_get_row_desc_without_row(conn);

/* Get the number of columns in the row */

	numcols = mi_column_count(rowdesc);
 
/* Obtain the column names from the row desriptor */
	i = 0;
	while( i < numcols )
		{
		colname = mi_column_name(rowdesc, i);
		DPRINTF("trc_class", 11, ("    %s\t", colname));

		i++;
		}
	DPRINTF("trc_class", 11,("\n\n"));

/* For each retrieved row: */
	while ( NULL != (row = mi_next_row(conn, &error)) )
		{
		/* For each column */
		for ( i = 0; i < numcols; i++ )
			{
			is_nested_row = MI_FALSE;

			/* Initialize column value and length */
			colval = NULL;
			collen = 0;

			/* Put the column value in colval */
			switch( mi_value(row, i, &colval, &collen) )
				{
				case MI_ERROR:
					mi_db_error_raise(conn, MI_EXCEPTION,
				"\nCannot get column value (mi_value)\n" );

				case MI_NULL_VALUE:
					colval = "NULL";
					break;

				case MI_NORMAL_VALUE:
					break;

				case MI_ROW_VALUE:
					is_nested_row = MI_TRUE;
					get_rowtype_data((MI_ROW *) colval);
					break;

				default:
					mi_db_error_raise(conn, MI_EXCEPTION,
						"\nUnknown value (mi_value)\n" );
					return( -1 );
				} 	 /* end switch */

			if ( is_nested_row )
				{
				/* process row type */
				}
			else
				{
				/* Print the column value */
				DPRINTF("trc_class", 11, ("    %s\t", colval));
				}

			} /* end for */

		DPRINTF("trc_class", 11, ("\n"));
		} /* end while */

	if ( MI_ERROR == error )
		{
		DPRINTF("trc_class", 11, ("\nReached last row\n"));
		}

	DPRINTF("trc_class", 11, ("\n"));

	return(1);
}

The get_data() function calls mi_get_row_desc_without_row() to obtain the row descriptor and mi_column_count() to obtain the number of columns. It then calls mi_column_name() in a for loop to obtain and print the names of the columns in the row descriptor.

The get_data() function assumes it is called from within a C UDR. It uses the DPRINTF statement, which is part of the DataBlade API tracing feature and is available only to C UDRs. The first DPRINTF statement sends the name of each retrieved column to a trace file when the trace level is 11 or higher. Another DPRINT statement sends the column value to the trace file. For more information on tracing, see Using Tracing.

For the get_data() function to execute in a client LIBMI application, it would need to replace the DPRINTF statement with a client-side output function such as printf() or fprintf(). The following code fragment uses the fprintf() function to display the names of retrieved columns:

	while( i < numcols )
		{
		colname = mi_column_name(rowdesc, i);
		fprintf(stderr, "%s\t", colname);

		i++;
		}
		fprintf(stderr, "\n\n");

All occurrences of DPRINT would need to be replaced by appropriate client-side output functions.

In the outer loop, mi_next_row() obtains every row, and in an inner loop mi_value() obtains every value in the row. The pointer returned in the value buffer is not valid after the next call to mi_value(). If the data were needed for later use, you would need to copy the data in the value buffer into a previously defined variable.

The get_data() function retrieves column data that is in text representation. The return values of the mi_value() function handle the text representations as follows:

• For the MI_NORMAL_VALUE return value, get_data() breaks out of the switch statement.

It does not need to perform any special handling based on column data type because values for all data types have the same data type: they are all null-terminated strings. Therefore, the colval variable that is passed into mi_value() is declared as a pointer to an mi_string. After mi_value() completes, colval points to the null-terminated string for the column value.

• For the MI_NULL_VALUE return value, get_data() assigns the string "NULL" as the column value.

The mi_value() function does not assign a null-terminated string to a column value when the column contains the SQL NULL value. Therefore, get_data() explicitly sets the column value to hold the null-terminated string: "NULL".

• For the MI_ROW_VALUE return value, get_data() calls the get_row_data() function to handle the row structure.

The get_row_data() function (see page 8-68) obtains values from the row structure in their text representation.

• The get_data() function does not handle the MI_COLLECTION_VALUE return status.

The mi_value() function returns the MI_NORMAL_VALUE value status for a collection column when the query is in text representation. For sample code that handles a collection column when query data is in binary mode, see A Collection in Binary Representation.