analytics/informix/demo/odbcdemo/desc.c

/***************************************************************************
*	Licensed Materials - Property of IBM and/or HCL
*
*	IBM Informix Client-SDK
*
*	(C) Copyright IBM Corporation 1997, 2004 All rights reserved.
*	(c) Copyright HCL Technologies Ltd. 2017.  All Rights Reserved. 
*
*
*
*
*
*  Title:          desc.c
*
*  Description:    To allocate a single descriptor and use it as the ARD
*                  for a SELECT statement and as the APD for an INSERT 
*                  statement.
*                  The data will be selected from the 'customer' table
*                  and inserted into a new table called 'new_cust'
*
***************************************************************************
*/


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

#ifndef NO_WIN32
#include <io.h>
#include <windows.h>
#include <conio.h>
#endif /*NO_WIN32*/

#include "infxcli.h"


#define BUFFER_LEN   10
#define ERRMSG_LEN   200

SQLCHAR   defDsn[] = "odbc_demo";


SQLINTEGER checkError (SQLRETURN       rc,
                SQLSMALLINT     handleType,
				SQLHANDLE       handle,
				SQLCHAR*            errmsg)
{
    SQLRETURN       retcode = SQL_SUCCESS;

    SQLSMALLINT     errNum = 1;
	SQLCHAR		    sqlState[6];
    SQLINTEGER      nativeError;
	SQLCHAR		    errMsg[ERRMSG_LEN];
    SQLSMALLINT     textLengthPtr;
 

    if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO))
    {
        while (retcode != SQL_NO_DATA)
        {
            retcode = SQLGetDiagRec (handleType, handle, errNum, sqlState, &nativeError, errMsg, ERRMSG_LEN, &textLengthPtr);

            if (retcode == SQL_INVALID_HANDLE)
            {
                fprintf (stderr, "checkError function was called with an invalid handle!!\n");
                return 1;
            }

            if ((retcode == SQL_SUCCESS) || (retcode == SQL_SUCCESS_WITH_INFO))
                fprintf (stderr, "ERROR: %d:  %s : %s \n", nativeError, sqlState, errMsg);

            errNum++;
        }

        fprintf (stderr, "%s\n", errmsg);
        return 1;   /* all errors on this handle have been reported */
    }
	else
		return 0;	/* no errors to report */
}




int main (long         argc,
          char*        argv[])
{
	/* Declare variables
    */

    /* Handles */            
	SQLHDBC     hdbc;
    SQLHENV     henv;
    SQLHSTMT    hTableStmt;
    SQLHSTMT    hSelectStmt;
    SQLHSTMT    hInsertStmt;
    SQLHDESC    hdesc;        


    /* Miscellaneous variables */

    SQLCHAR       dsn[20];   /*name of the DSN used for connecting to the database*/
    SQLRETURN   rc = 0;
	SQLINTEGER         i, in;


    SQLCHAR*       createTableStmt = (SQLCHAR *) "CREATE TABLE new_cust(cust_num      INTEGER,\
                                                         fname         VARCHAR(10),\
                                                         lname         VARCHAR(10))";

    SQLCHAR*       selectStmt = (SQLCHAR *) "SELECT cust_num, fname, lname from customer where cust_num < 110";
    SQLCHAR*       insertStmt = (SQLCHAR *) "INSERT INTO new_cust (cust_num, fname, lname) VALUES (?, ?, ?)";

    SQLINTEGER         custnumArray[9];           /*array to hold values of 'cust_num' from table 'customer'*/
    SQLCHAR        fnameArray[9][BUFFER_LEN]; /*array to hold values of 'fname' from table 'customer'*/
    SQLCHAR        lnameArray[9][BUFFER_LEN]; /*array to hold values of 'lname' from table 'customer'*/

    SQLINTEGER     newCustnum;                /*value of 'cust_num' from table 'new_cust'*/
    SQLCHAR        newFname[BUFFER_LEN];      /*value of 'fname' from table 'new_cust'*/
    SQLCHAR        newLname[BUFFER_LEN];      /*value of 'lname' from table 'new_cust'*/

    SQLLEN  cbCustnum = 0, cbFname = SQL_NTS, cbLname = SQL_NTS;
    
    
    /*  STEP 1. Get data source name from command line (or use default)
    **          Allocate the environment handle and set ODBC version
    **          Allocate the connection handle 
    **          Establish the database connection 
    **          Allocate the statement handles
    **          Allocate the descriptor handle
    */


    /* If(dsn is not explicitly passed in as arg) */
    if (argc != 2)
    {
        /* Use default dsn - odbc_demo */
        fprintf (stdout, "\nUsing default DSN : %s\n", defDsn);
        strcpy ((char *)dsn, (char *)defDsn);
    }
    else
    {
        /* Use specified dsn */
        strcpy ((char *)dsn, (char *)argv[1]);
        fprintf (stdout, "\nUsing specified DSN : %s\n", dsn);
    }

    
    /* Allocate the Environment handle */
    rc = SQLAllocHandle (SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
    if (rc != SQL_SUCCESS)
    {
        fprintf (stdout, "Environment Handle Allocation failed\nExiting!!");
        return (1);
    }


    /* Set the ODBC version to 3.0 */
    rc = SQLSetEnvAttr (henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, 0);
    if (checkError (rc, SQL_HANDLE_ENV, henv, (SQLCHAR *) "Error in Step 1 -- SQLSetEnvAttr failed\nExiting!!"))
		return (1);


    /* Allocate the connection handle */
    rc = SQLAllocHandle (SQL_HANDLE_DBC, henv, &hdbc);
    if (checkError (rc, SQL_HANDLE_ENV, henv, (SQLCHAR *) "Error in Step 1 -- Connection Handle Allocation failed\nExiting!!"))
		return (1);


    /* Establish the database connection */
    rc = SQLConnect (hdbc, dsn, SQL_NTS, (SQLCHAR *) "", SQL_NTS, (SQLCHAR *) "", SQL_NTS);
	if (checkError (rc, SQL_HANDLE_DBC, hdbc, (SQLCHAR *) "Error in Step 1 -- SQLConnect failed\nExiting!!"))
		return (1);

    /* Allocate the statement handle for the CREATE TABLE statement */
    rc = SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hTableStmt);
    if (checkError (rc, SQL_HANDLE_DBC, hdbc, (SQLCHAR *) "Error in Step 1 -- Create Table Statement Handle Allocation failed\nExiting!!"))
		return (1);

    /* Allocate the statement handle for the SELECT statement */
    rc = SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hSelectStmt);
    if (checkError (rc, SQL_HANDLE_DBC, hdbc, (SQLCHAR *) "Error in Step 1 -- Select Statement Handle Allocation failed\nExiting!!"))
		return (1);
    
    /* Allocate the statement handle for the INSERT statement */
    rc = SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hInsertStmt);
    if (checkError (rc, SQL_HANDLE_DBC, hdbc, (SQLCHAR *) "Error in Step 1 -- Insert Statement Handle Allocation failed\nExiting!!"))
		return (1);
    
    /* Allocate the descriptor handle */
    rc = SQLAllocHandle (SQL_HANDLE_DESC, hdbc, &hdesc);
    if (checkError (rc, SQL_HANDLE_DBC, hdbc, (SQLCHAR *) "Error in Step 1 -- Insert Statement Handle Allocation failed\nExiting!!"))
		return (1);


	fprintf (stdout, "STEP 1 done...connected to database\n");



    /* STEP 2.  Create the database table 'new_cust' where the data is to be inserted
    */

    /* Execute the SQL statement to create the table 'new_cust' */
    rc = SQLExecDirect (hTableStmt, createTableStmt, SQL_NTS);
    if (checkError (rc, SQL_HANDLE_STMT, hTableStmt, (SQLCHAR *) "Error in Step 2 -- SQLExecDirect failed\n" ))
		goto Exit;

   	
	fprintf (stdout, "STEP 2 done...table new_cust created in the datbase\n");



    /* STEP 3.  For each column, set the following descriptor fields to appropriate values
    **          -- column 'cust_num' : descriptor fields - SQL_DESC_TYPE
    **          -- column 'fname'    : descriptor fields - SQL_DESC_TYPE, SQL_DESC_LENGTH, 
    **                                                     SQL_DESC_OCTET_LENGTH
    **          -- column 'lname'    : descriptor fields - SQL_DESC_TYPE, SQL_DESC_LENGTH, 
    **                                                     SQL_DESC_OCTET_LENGTH
    */

    /* Setting descriptor fields for column - cust_num */
    rc = SQLSetDescField (hdesc, 1, SQL_DESC_TYPE, (SQLPOINTER) SQL_C_SLONG, 0);
    if (checkError (rc, SQL_HANDLE_DESC, hdesc, (SQLCHAR *) "Error in Step 3 -- SQLSetDescField (SQL_DESC_DATA_TYPE) failed for column cust_num\n"))
        goto Exit;


    /* Setting descriptor fields for column - fname */
    rc = SQLSetDescField (hdesc, 2, SQL_DESC_TYPE, (SQLPOINTER) SQL_C_CHAR, 0);
    if (checkError (rc, SQL_HANDLE_DESC, hdesc, (SQLCHAR *) "Error in Step 3 -- SQLSetDescField (SQL_DESC_DATA_TYPE) failed for column fname\n"))
        goto Exit;

    rc = SQLSetDescField (hdesc, 2, SQL_DESC_LENGTH, (SQLPOINTER) BUFFER_LEN, 0);
    if (checkError (rc, SQL_HANDLE_DESC, hdesc, (SQLCHAR *) "Error in Step 3 -- SQLSetDescField (SQL_DESC_LENGTH) failed for column fname\n"))
        goto Exit;

    rc = SQLSetDescField (hdesc, 2, SQL_DESC_OCTET_LENGTH, (SQLPOINTER) BUFFER_LEN, 0);
    if (checkError (rc, SQL_HANDLE_DESC, hdesc, (SQLCHAR *) "Error in Step 3 -- SQLSetDescField (SQL_DESC_OCTET_LENGTH) failed for column fname\n"))
        goto Exit;


    /* Setting descriptor fields for column - lname */
    rc = SQLSetDescField (hdesc, 3, SQL_DESC_TYPE, (SQLPOINTER) SQL_C_CHAR, 0);
    if (checkError (rc, SQL_HANDLE_DESC, hdesc, (SQLCHAR *) "Error in Step 3 -- SQLSetDescField (SQL_DESC_DATA_TYPE) failed for column lname\n"))
        goto Exit;

    rc = SQLSetDescField (hdesc, 3, SQL_DESC_LENGTH, (SQLPOINTER) BUFFER_LEN, 0);
    if (checkError (rc, SQL_HANDLE_DESC, hdesc, (SQLCHAR *) "Error in Step 3 -- SQLSetDescField (SQL_DESC_LENGTH) failed for column lname\n"))
        goto Exit;

    rc = SQLSetDescField (hdesc, 3, SQL_DESC_OCTET_LENGTH, (SQLPOINTER) BUFFER_LEN, 0);
    if (checkError (rc, SQL_HANDLE_DESC, hdesc, (SQLCHAR *) "Error in Step 3 -- SQLSetDescField (SQL_DESC_OCTET_LENGTH) failed for column lname\n"))
        goto Exit;


	fprintf (stdout, "STEP 3 done...descriptor fields set for all columns\nRetrieving data from table 'customer'\n\n");




    /* STEP 4.  Set the explicitly allocated descriptor handle as the ARD for the select 
    **          statement's handle 
    **          Execute the SELECT statement
    **          Set the descriptor fields with the values returned by the SELECT statement
    **          Fetch the results
    **          Close the result set cursor
    */

    /* Set the explicitly allocated descriptor handle as the ARD for the select statement */
    rc = SQLSetStmtAttr (hSelectStmt, SQL_ATTR_APP_ROW_DESC, (SQLPOINTER) hdesc, 0);
    if (checkError (rc, SQL_HANDLE_STMT, hSelectStmt, (SQLCHAR *) "Error in Step 4 -- SQLSetStmtAttr failed\n"))
		goto Exit;

    /* Execute the SELECT statement */
    rc = SQLExecDirect (hSelectStmt, selectStmt, SQL_NTS);
    if (checkError (rc, SQL_HANDLE_STMT, hSelectStmt, (SQLCHAR *) "Error in Step 4 -- SQLExecDirect failed\n"))
    	goto Exit;


    for (i = 0; i < 9; i++)
    {
        /* Set the descriptor fields for the values being returned */
        rc = SQLSetDescField (hdesc, 1, SQL_DESC_DATA_PTR, &(custnumArray[i]), sizeof(custnumArray[i])/*SQL_IS_INTEGER*/);
        if (checkError (rc, SQL_HANDLE_DESC, hdesc, (SQLCHAR *) "Error in Step 4 -- SQLSetDescField failed for column cust_num\n"))
            goto Exit;

        rc = SQLSetDescField (hdesc, 2, SQL_DESC_DATA_PTR, fnameArray[i], BUFFER_LEN);
        if (checkError (rc, SQL_HANDLE_DESC, hdesc, (SQLCHAR *) "Error in Step 4 -- SQLSetDescField failed for column fname\n"))
            goto Exit;
        
        rc = SQLSetDescField (hdesc, 3, SQL_DESC_DATA_PTR, lnameArray[i], BUFFER_LEN);
        if (checkError (rc, SQL_HANDLE_DESC, hdesc, (SQLCHAR *) "Error in Step 4 -- SQLSetDescField failed for column lname\n"))
            goto Exit;

        /* Fetch the results */
        rc = SQLFetch (hSelectStmt);
       	if (rc == SQL_NO_DATA_FOUND)
	        break;
        else if (checkError (rc, SQL_HANDLE_STMT, hSelectStmt, (SQLCHAR *) "Error in Step 4 -- SQLFetch failed\n"))
	    	goto Exit;


        /* Display the results */
        fprintf (stdout, "Row retrieved from table 'customer' is - cust_num=%d, fname=%s, lname=%s\n", 
                 custnumArray[i], fnameArray[i], lnameArray[i]);
   }


    /* Close the result set cursor */
    rc = SQLCloseCursor (hSelectStmt);
    if (checkError (rc, SQL_HANDLE_STMT, hSelectStmt, (SQLCHAR *) "Error in Step 4 -- SQLCloseCursor failed\n"))
		goto Exit;


	fprintf (stdout, "\n\nSTEP 4 done...SELECT statement executed...descriptor fields set with data retrieved\n");



   	/* STEP 5.  Set the explicitly allocated descriptor handle as the APD for the INSERT 
    **          statement's handle 
    **          Bind the input parameters for the INSERT statement
    **          For each row, set the SQL_DESC_DATA_PTR field to the value in the allocated
    **          arrays and execute the INSERT statement
    */

    /* Set the explicitly allocated descriptor handle as the APD for the INSERT statement */
    rc = SQLSetStmtAttr (hInsertStmt, SQL_ATTR_APP_PARAM_DESC, (SQLPOINTER) hdesc, 0);
    if (checkError (rc, SQL_HANDLE_STMT, hInsertStmt, (SQLCHAR *) "Error in Step 5 -- SQLSetStmtAttr failed\n"))
		goto Exit;

    /* Bind the input parameters for the INSERT statement */
    rc = SQLBindParameter (hInsertStmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, 
                           0, 0, &(custnumArray[i]), sizeof(custnumArray[i]), &cbCustnum);
    if (checkError (rc, SQL_HANDLE_STMT, hInsertStmt, (SQLCHAR *) "Error in Step 5 -- SQLBindParameter failed (param 1)\n"))
		goto Exit;

    rc = SQLBindParameter (hInsertStmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, BUFFER_LEN, 
                           0, fnameArray[i], BUFFER_LEN, &cbFname);
    if (checkError (rc, SQL_HANDLE_STMT, hInsertStmt, (SQLCHAR *) "Error in Step 5 -- SQLBindParameter failed (param 2)\n"))
		goto Exit;

    rc = SQLBindParameter (hInsertStmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, BUFFER_LEN, 
                           0, lnameArray[i], BUFFER_LEN,  &cbLname);
    if (checkError (rc, SQL_HANDLE_STMT, hInsertStmt, (SQLCHAR *) "Error in Step 5 -- SQLBindParameter failed (param 3)\n"))
		goto Exit;
    
    /* For each row, set the SQL_DESC_DATA_PTR field and execute the INSERT statement */
    for (i = 0; i < 9; i++)
    {
        rc = SQLSetDescField (hdesc, 1, SQL_DESC_DATA_PTR, &(custnumArray[i]), sizeof(custnumArray[i]));
        if (checkError (rc, SQL_HANDLE_DESC, hdesc, (SQLCHAR *) "Error in Step 5 -- SQLSetDescField failed for column cust_num\n"))
            goto Exit;

        rc = SQLSetDescField (hdesc, 2, SQL_DESC_DATA_PTR, fnameArray[i], BUFFER_LEN);
        if (checkError (rc, SQL_HANDLE_DESC, hdesc, (SQLCHAR *) "Error in Step 5 -- SQLSetDescField failed for column fname\n"))
            goto Exit;
       
        rc = SQLSetDescField (hdesc, 3, SQL_DESC_DATA_PTR, lnameArray[i], BUFFER_LEN);
        if (checkError (rc, SQL_HANDLE_DESC, hdesc, (SQLCHAR *) "Error in Step 5 -- SQLSetDescField failed for column lname\n"))
            goto Exit;

        /* Execute the INSERT statement */
        rc = SQLExecDirect (hInsertStmt, insertStmt, SQL_NTS);
        if (checkError (rc, SQL_HANDLE_STMT, hInsertStmt, (SQLCHAR *) "Error in Step 5 -- SQLExecDirect failed\n"))
	    	goto Exit;
    }


	fprintf (stdout, "STEP 5 done...data inserted into table new_cust\n");
    fprintf (stdout, "\nRetrieving data from table 'new_cust'...\nHit <Enter> to continue...\n");
    in = getchar ();
   	
    
    
    /* STEP 6.  Execute the SELECT statement to retrieve data from 
    **          table 'new_cust'
    **          Bind the result set columns
    **          Fetch and display the data
    */


    /* Execute the SELECT statement */
    rc = SQLExecDirect (hSelectStmt, (SQLCHAR *) "SELECT * FROM new_cust", SQL_NTS);
    if (checkError (rc, SQL_HANDLE_STMT, hSelectStmt, (SQLCHAR *) "Error in Step 6 -- SQLExecDirect failed\n"))
    	goto Exit;

    /* Bind the result set columns */
    rc = SQLBindCol (hSelectStmt, 1, SQL_C_LONG, &newCustnum, sizeof(newCustnum), &cbCustnum);
    if (checkError (rc, SQL_HANDLE_STMT, hSelectStmt, (SQLCHAR *) "Error in Step 6 -- SQLBindCol failed (column 1)\n"))
    	goto Exit;
    
    rc = SQLBindCol (hSelectStmt, 2, SQL_C_CHAR, newFname, BUFFER_LEN, &cbFname);
    if (checkError (rc, SQL_HANDLE_STMT, hSelectStmt, (SQLCHAR *) "Error in Step 6 -- SQLBindCol failed (column 2)\n"))
    	goto Exit;
    
    rc = SQLBindCol (hSelectStmt, 3, SQL_C_CHAR, newLname, BUFFER_LEN, &cbLname);
    if (checkError (rc, SQL_HANDLE_STMT, hSelectStmt, (SQLCHAR *) "Error in Step 6 -- SQLBindCol failed (column 3)\n"))
    	goto Exit;
    
    /* Fetch and display the data retrieved */
    while (1)
    {
        /* Fetch the results */
        rc = SQLFetch (hSelectStmt);
       	if (rc == SQL_NO_DATA_FOUND)
	        break;
        else if (checkError (rc, SQL_HANDLE_STMT, hSelectStmt, (SQLCHAR *) "Error in Step 6 -- SQLFetch failed\n"))
	    	goto Exit;

        /* Display the results */
        fprintf (stdout, "Row retrieved from table 'new_cust' is - cust_num=%d, fname=%s, lname=%s\n", newCustnum, newFname, newLname);
   }


    /* Close the result set cursor */
    rc = SQLCloseCursor (hSelectStmt);
    if (checkError (rc, SQL_HANDLE_STMT, hSelectStmt, (SQLCHAR *) "Error in Step 6 -- SQLCloseCursor failed\n"))
		goto Exit;


	fprintf (stdout, "\n\nSTEP 6 done...SELECT statement executed...data retrieved from table 'new_cust'\n");



    Exit:

    /* CLEANUP: Drop table 'new_cust' from the database
    **          Close the statement handle
    **          Free the statement handle
    **          Disconnect from the datasource
    **          Free the connection and environment handles
    **          Exit
    */

    SQLExecDirect (hTableStmt, (SQLCHAR *) "DROP TABLE new_cust", SQL_NTS);

    /* Close all the statement handles */
    SQLFreeStmt (hTableStmt, SQL_CLOSE);
    SQLFreeStmt (hSelectStmt, SQL_CLOSE);
    SQLFreeStmt (hInsertStmt, SQL_CLOSE);

    /* Free all the statement handles */
    SQLFreeHandle (SQL_HANDLE_STMT, hTableStmt);
    SQLFreeHandle (SQL_HANDLE_STMT, hSelectStmt);
    SQLFreeHandle (SQL_HANDLE_STMT, hInsertStmt);

    /* Free the descriptor handle */
    SQLFreeHandle (SQL_HANDLE_DESC, hdesc);

	/* Disconnect from the data source */
    SQLDisconnect (hdbc);

    /* Free the environment handle and the database connection handle */
    SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
    SQLFreeHandle (SQL_HANDLE_ENV, henv);

    fprintf (stdout,"\n\nHit <Enter> to terminate the program...\n\n");
    in = getchar ();
    return (rc);
}