analytics/informix/extend/spatial.8.22.FC2/examples/odbc/interiorring.c

/**************************************************************************
 *
 * interiorring.c
 *
 * This program demonstrates how to use ODBC to obtain information about
 * spatial data from a database using various Informix Spatial DataBlade
 * functions.
 *
 * The following command will likely compile this source:
 *
 *   cc -o interiorring interiorring.c \
 *        -I$INFORMIXDIR/incl/cli -L$INFORMIXDIR/lib/cli -lifcli -lifdmr
 *
 **************************************************************************/

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <float.h>

#ifdef WINNT
#include <io.h>
#include <windows.h>
#include <conio.h>
#endif

#include <infxcli.h>     /* ODBC typedefs and data structures */
#include "odbcutils.c"   /* ODBC utility functions */

/*
 * Constants, macros, typedefs, data structures, & utility functions
 */
#include "commfuncs.h"
#include "commfuncs.c"


void main (int argc, char **argv)
{
    SQLHDBC       hdbc;
    SQLHENV       henv;
    SQLHSTMT      hstmt;
    char          sql_stmt[100];
    int           rc;
    int           total_perimeter;
    int           num_lakes;
    int           lake_number;
    int           island_id;
    int           lake_perimeter;
    SQLHSTMT      island_cursor;
    SQLHSTMT      lake_cursor;
    SDWORD        pcbvalue1, pcbvalue2;
    SDWORD        id_ind;
    SDWORD        lake_ind;
    SDWORD        length_ind;

    /* Check for the correct number of arguments entered. */
    if (argc < 2)
    {
        printf ("Usage: %s <datasource>\n", argv[0]);
        exit (1);
    }

    /* Connect to the database. */
    server_connect ((UCHAR *) argv[1], &henv, &hdbc);

    /* Allocate memory for the SQL statement handle and
     * associate the statement handle with the connection handle. */
    rc = SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hstmt);
	returncode_check (hdbc, (SQLHSTMT)NULL, rc, "SQLAllocHandle");

    rc = SQLExecDirect (hstmt, (UCHAR *) "DROP TABLE islands", SQL_NTS);
    rc = SQLExecDirect (hstmt, (UCHAR *) "CREATE TABLE islands (id int, land st_polygon)", SQL_NTS);
    returncode_check (NULL, hstmt, rc, "SQLExecDirect");

#if 0
    rc = SQLExecDirect (hstmt, (UCHAR *) "INSERT INTO  islands VALUES (123, '1000 POLYGON ((  2000  0000,  1847  0765,  1414  1414,  0765  1847,  0000  2000, -0765  1847, -1414  1414, -1847  0765, -2000  0000, -1847 -0765, -1414 -1414, -0765 -1847,  0000 -2000,  0765 -1847,  1414 -1414,  1847 -0765,  2000  0000), (1500  1000,  1300  1300,  1000  1500,  0650  1300,  0500  1000,  0646  0646,  1000  0500,  1354  0646,  1500  1000), (-500  1000, -0646  1354, -1000  1500, -1354  1354, -1500  1000, -1354  0646, -1000  500, -0646  0646, -0500  1000),( 0500 -1000,  0354 -0646,  0000 -0500, -0354 -0646, -0500 -1000, -0354 -1354,  0000 -1500,  0354 -1354,  0500 -1000))')", SQL_NTS);
#else
    rc = SQLExecDirect (hstmt, (UCHAR *) "INSERT INTO  islands VALUES (123, '0 POLYGON ((10 10, 10 40,40 40,40 10,10 10),(11 11, 11 17,17 17,17 11,11 11),(21 21, 21 31,31 31,31 21,21 21))')", SQL_NTS);
#endif
    returncode_check (NULL, hstmt, rc, "SQLExecDirect");

#if 0
    rc = SQLExecDirect (hstmt, (UCHAR *) "INSERT INTO  islands VALUES (456, '1000 POLYGON ((  8000  6000,  7847  6765,  7414  7414,  6765  7847,  6000  8000, -6765  7847, -7414  7414, -7847  6765, -8000  6000, -7847 -6765, -7414 -7414, -6765 -7847,  6000 -8000,  6765 -7847,  7414 -7414,  7847 -6765,  8000  6000), (7500  7000,  7300  7300,  7038  7500,  6650  7302,  6500  7080,  6646  6646,  7290  6500,  7354  6646,  7500  7000),( 6500 -7000,  6354 -6646,  6000 -6500, -6354 -6646, -6500 -7000, -6354 -7354,  6000 -7500,  6354 -7354,  6500 -7000))')", SQL_NTS);
#else
    rc = SQLExecDirect (hstmt, (UCHAR *) "INSERT INTO  islands VALUES (456, '0 POLYGON ((100 100, 100 400,400 400,400 100,100 100),(101 101, 101 170,170 170,170 101,101 101))')", SQL_NTS);
#endif
    returncode_check (NULL, hstmt, rc, "SQLExecDirect");

    /* INFORMIX_EXTEST_BEGIN interiorringn */

    /* Prepare and execute the query to get the island IDs and number
       of lakes (interior rings); */
    sprintf(sql_stmt,
            "SELECT id, ST_NumInteriorRing(land) FROM islands");

    /* Allocate memory for the island cursor */
    rc = SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &island_cursor);
	returncode_check (hdbc, (SQLHSTMT)NULL, rc, "SQLAllocHandle");

    rc = SQLExecDirect (island_cursor, (UCHAR *)sql_stmt, SQL_NTS);
    returncode_check (NULL, hstmt, rc, "SQLExecDirect");

    /* Bind the island table's id column to island_id. */
    rc = SQLBindCol (island_cursor, 1, SQL_C_SLONG,
                     &island_id, 0, &id_ind);

    /* Bind the result of ST_NumInteriorRing(land) to num_lakes. */
    rc = SQLBindCol (island_cursor, 2, SQL_C_SLONG,
                     &num_lakes, 0, &lake_ind);

    /* Allocate memory to the SQL statement handle lake_cursor. */
    rc = SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &lake_cursor);
	returncode_check (hdbc, (SQLHSTMT)NULL, rc, "SQLAllocHandle");

    /* Prepare the query to get the length of an interior ring. For
     * efficiency, we only prepare this query once. */
    sprintf (sql_stmt,
             "SELECT ST_Length(ST_InteriorRingN(land, ?))"
             "FROM islands WHERE id = ?");
    rc = SQLPrepare (lake_cursor, (UCHAR *)sql_stmt, SQL_NTS);
    returncode_check (NULL, hstmt, rc, "SQLPrepare");

    /* Bind the lake_number to the first parameter. */
    pcbvalue1 = 0;
    rc = SQLBindParameter (lake_cursor, 1, SQL_PARAM_INPUT, SQL_C_LONG,
                           SQL_INTEGER, 0, 0,
                           &lake_number, 0, &pcbvalue1);
    returncode_check (NULL, hstmt, rc, "SQLBindParameter");

    /* Bind the island_id to the second parameter. */
    pcbvalue2 = 0;
    rc = SQLBindParameter (lake_cursor, 2, SQL_PARAM_INPUT, SQL_C_LONG,
                           SQL_INTEGER, 0, 0,
                           &island_id, 0, &pcbvalue2);
    returncode_check (NULL, hstmt, rc, "SQLBindParameter");

    /* Bind the result of the ST_Length function to lake_perimeter. */
    rc = SQLBindCol (lake_cursor, 1, SQL_C_SLONG,
                     &lake_perimeter, 0, &length_ind);

    /* Outer loop:
     * get the island ids and the number of lakes (interior rings).*/
    while (1)
    {
        /* Fetch an island.*/
        rc = SQLFetch (island_cursor);
        if (rc == SQL_NO_DATA)
            break;
        else 
            returncode_check(NULL, hstmt, rc, "SQLFetch");

        /* Inner loop: for this island,
         * get the perimeter of all its lakes (interior rings). */
        for (total_perimeter = 0,lake_number = 1;
             lake_number <= num_lakes;
             lake_number++)
        {
            rc = SQLExecute (lake_cursor);
			returncode_check (NULL, hstmt, rc, "SQLExecute");
            rc = SQLFetch (lake_cursor);
			if (rc == SQL_NO_DATA)
				break;
			else 
				returncode_check(NULL, hstmt, rc, "SQLFetch");

            total_perimeter += lake_perimeter;
            SQLFreeStmt (lake_cursor, SQL_CLOSE);
        }

        /* Display the island ID and the total perimeter of its lakes.*/
        printf ("Island ID = %d, Total lake perimeter = %d\n",
                island_id,total_perimeter);
    }

    SQLFreeStmt (lake_cursor, SQL_DROP);
    SQLFreeStmt (island_cursor, SQL_DROP);

    /* INFORMIX_EXTEST_END interiorringn */

    SQLFreeStmt (hstmt, SQL_CLOSE);          /* Close the statement handle */
    SQLFreeHandle (SQL_HANDLE_STMT, hstmt);  /* Free the statement handle */
    SQLDisconnect (hdbc);                    /* Close the connection */
    SQLFreeHandle (SQL_HANDLE_DBC, hdbc);    /* Free the database handle */
    SQLFreeHandle (SQL_HANDLE_ENV, henv);    /* Free the ODBC environment */
    printf( "\nTest Complete\n");
}