example2.c

关系型数据库 Postgresql 6.5.2

/* -*- C -*- */
/* The first example illustrates creating a table, adding some data
 * to it, and selecting the inserted data. The second example shows
 * interactive ad hoc query processing.
 *
 * Actual applications include more complete error checking following
 * calls to SQL/CLI routines. That material is omitted from this
 * Appendix for the sake of clarity.
 *
 * This file is adapted for PostgreSQL
 * from the CLI Annex in the SQL98 August 1994 draft standard.
 * Thomas G. Lockhart 1999-06-16
 */

/*
 * B.2  Interactive Query
 *
 * This sample function uses the concise CLI functions to
 * interactively execute a SQL statement supplied as an argument.
 * In the case where the user types a SELECT statement, the function
 * fetches and displays all rows of the result set.
 *
 * This example illustrates the use of GetDiagField() to identify
 * the type of SQL statement executed and, for SQL statements where
 * the row count is defined on all implementations, the use of
 * GetDiagField() to obtain the row count.
 */

/*
 * Sample program - uses concise CLI functions to execute
 * interactively an ad hoc statement.
 */
#include <sqlcli.h>
#include <string.h>
#include <stdlib.h>

#define  MAXCOLS   100

#define  max(a,b) (a>b?a:b)

int print_err(SQLSMALLINT handletype, SQLINTEGER handle);
int build_indicator_message(SQLCHAR *errmsg,
			    SQLPOINTER *data,
			    SQLINTEGER collen,
			    SQLINTEGER *outlen,
			    SQLSMALLINT colnum);

SQLINTEGER display_length(SQLSMALLINT coltype,
			  SQLINTEGER collen,
			  SQLCHAR *colname);

example2(SQLCHAR *server, SQLCHAR *uid, SQLCHAR *authen, SQLCHAR *sqlstr)
{
  int         i;
  SQLHENV     henv;
  SQLHDBC     hdbc;
  SQLHSTMT    hstmt;
  SQLCHAR     errmsg[256];
  SQLCHAR     colname[32];
  SQLSMALLINT coltype;
  SQLSMALLINT colnamelen;
  SQLSMALLINT nullable;
  SQLINTEGER  collen[MAXCOLS];
  SQLSMALLINT scale;
  SQLINTEGER  outlen[MAXCOLS];
  SQLCHAR    *data[MAXCOLS];
  SQLSMALLINT nresultcols;
  SQLINTEGER  rowcount;
  SQLINTEGER  stmttype;
  SQLRETURN   rc;

  /* allocate an environment handle */
  SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);

  /* allocate a connection handle */
  SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);

  /* connect to database */
  if (SQLConnect(hdbc, server, SQL_NTS, uid, SQL_NTS, authen, SQL_NTS)
      != SQL_SUCCESS )
    return(print_err(SQL_HANDLE_DBC, hdbc));

  /* allocate a statement handle */
  SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

  /* execute the SQL statement */
  if (SQLExecDirect(hstmt, sqlstr, SQL_NTS) != SQL_SUCCESS)
    return(print_err(SQL_HANDLE_STMT, hstmt));

  /* see what kind of statement it was */
  SQLGetDiagField(SQL_HANDLE_STMT, hstmt, 0,
		  SQL_DIAG_DYNAMIC_FUNCTION_CODE,
		  (SQLPOINTER)&stmttype, 0, (SQLSMALLINT *)NULL);

  switch (stmttype) {
    /* SELECT statement */
  case SQL_SELECT_CURSOR:
    /* determine number of result columns */
    SQLNumResultCols(hstmt, &nresultcols);
    /* display column names */
    for (i=0; i<nresultcols; i++) {
      SQLDescribeCol(hstmt, i+1, colname, sizeof(colname),
		     &colnamelen, &coltype, &collen[i], &scale, &nullable);

      /* assume there is a display_length function which
	 computes correct length given the data type  */
      collen[i] = display_length(coltype, collen[i], colname);
      (void)printf("%*.*s", collen[i], collen[i], colname);
      /* allocate memory to bind column */
      data[i] = (SQLCHAR *) malloc(collen[i]);

      /* bind columns to program vars, converting all types to CHAR */
      SQLBindCol(hstmt, i+1, SQL_CHAR, data[i], collen[i],
		 &outlen[i]);
    }
    /* display result rows */
    while ((rc=SQLFetch(hstmt))!=SQL_ERROR) {
      errmsg[0] = '\0';
      if (rc ==  SQL_SUCCESS_WITH_INFO) {
	for (i=0; i<nresultcols; i++) {
	  if (outlen[i] ==  SQL_NULL_DATA || outlen[i] >= collen[i])
	    build_indicator_message(errmsg,
                                    (SQLPOINTER *)&data[i], collen[i],
                                    &outlen[i], i);
	  (void)printf("%*.*s ", outlen[i], outlen[i],
		       data[i]);
	} /* for all columns in this row  */
	/* print any truncation messages */
	(void)printf("\n%s", errmsg);
      }
    } /* while rows to fetch */
    SQLClose(hstmt);
    break;

    /* searched DELETE, INSERT or searched UPDATE statement */
  case SQL_DELETE_WHERE:
  case SQL_INSERT:
  case SQL_UPDATE_WHERE:
    /* check rowcount */
    SQLGetDiagField(SQL_HANDLE_STMT, hstmt, 0,
		    SQL_DIAG_ROW_COUNT, (SQLPOINTER)&rowcount, 0,
		    (SQLSMALLINT *)NULL);
    if (SQLEndTran(SQL_HANDLE_ENV, henv, SQL_COMMIT)
	==  SQL_SUCCESS) {
      (void) printf("Operation successful\n");
    }
    else {
      (void) printf("Operation failed\n");
    }
    (void)printf("%ld rows affected\n", rowcount);
    break;

    /* other statements */
  case SQL_ALTER_TABLE:
  case SQL_CREATE_TABLE:
  case SQL_CREATE_VIEW:
  case SQL_DROP_TABLE:
  case SQL_DROP_VIEW:
  case SQL_DYNAMIC_DELETE_CURSOR:
  case SQL_DYNAMIC_UPDATE_CURSOR:
  case SQL_GRANT:
  case SQL_REVOKE:
    if (SQLEndTran(SQL_HANDLE_ENV, henv, SQL_COMMIT)
	==  SQL_SUCCESS) {
      (void) printf("Operation successful\n");
    }
    else {
      (void) printf("Operation failed\n");
    }
    break;

  /* implementation-defined statement */
  default:
    (void)printf("Statement type=%ld\n", stmttype);
    break;
  }

  /* free data buffers */
  for (i=0; i<nresultcols; i++)  {
    (void)free(data[i]);
  }

  /* free statement handle */
  SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
  /* disconnect from database */
  SQLDisconnect(hdbc);
  /* free connection handle */
  SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
  /* free environment handle */
  SQLFreeHandle(SQL_HANDLE_ENV, henv);

  return(0);
}

/***********************************************************
 The following functions are given for completeness, but are
 not relevant for understanding the database processing
 nature of CLI
***********************************************************/

#define MAX_NUM_PRECISION 15
/*#define max length of char string representation of no. as:

  = max(precision) + leading sign + E + exp sign + max exp length
  =  15            + 1            + 1 + 1        + 2
  =  15 + 5
*/
#define MAX_NUM_STRING_SIZE (MAX_NUM_PRECISION + 5)

SQLINTEGER  display_length(SQLSMALLINT coltype, SQLINTEGER collen,
			   SQLCHAR *colname)
{
  switch (coltype) {

  case SQL_VARCHAR:
  case SQL_CHAR:
    return(max(collen,strlen((char *)colname)));
    break;

  case SQL_FLOAT:
  case SQL_DOUBLE:
  case SQL_NUMERIC:
  case SQL_REAL:
  case SQL_DECIMAL:
    return(max(MAX_NUM_STRING_SIZE,strlen((char *)colname)));
    break;

  case SQL_DATETIME:
    return(max(SQL_TIMESTAMP_LEN,strlen((char *)colname)));
    break;

  case SQL_INTEGER:
    return(max(10,strlen((char *)colname)));
    break;

  case SQL_SMALLINT:
    return(max(5,strlen((char *)colname)));
    break;

  default:
    (void)printf("Unknown datatype, %d\n", coltype);
    return(0);
    break;
  }
}

int build_indicator_message(SQLCHAR *errmsg, SQLPOINTER *data,
			    SQLINTEGER collen, SQLINTEGER *outlen, SQLSMALLINT colnum)
{
  if (*outlen ==  SQL_NULL_DATA) {
    (void)strcpy((char *)data, "NULL");
    *outlen=4;
  }
  else {
    sprintf((char *)errmsg+strlen((char *)errmsg),
	    "%d chars truncated, col %d\n", *outlen-collen+1,
	    colnum);
    *outlen=255;
  }
}