solidDB Help : Programming : solidDB ODBC API : Constructing an application
  
Constructing an application
This topic provides two examples of C-language source code for applications: an example that uses static SQL functions to create a table, add data to it, and select the inserted data; and another example of interactive, ad-hoc query processing.
Microsoft provides two types of header files, one for ASCII data and the other for Unicode data. The following example can use either of the Microsoft ODBC header files.
Static SQL example
The following example constructs SQL statements within the application.
/***************************************************************
  Sample Name: Example1.c
  Author : UNICOM Systems, Inc.

  Location : CONSTRUCTING AN APPLICATION
              Programmer Guide
  Purpose : Sample example that uses static SQL functions to
              create a table, add data to it, and select the
              inserted data.
***************************************************************/
#if (defined(SS_UNIX) || defined(SS_LINUX))
#include <solidodbc3.h>
#else
#include <windows.h>
#endif
#include <stdio.h>
#include <test_assert.h>
#define MAX_NAME_LEN 50
#define MAX_STMT_LEN 100
/**************************************************************
  Function Name: PrintError
  Purpose......: To Display the error associated with
                 the handle
***************************************************************/ SQLINTEGER PrintError(SQLSMALLINT handleType,SQLHANDLE handle)
{
    SQLRETURN rc = SQL_ERROR;
    SQLWCHAR sqlState[6];
    SQLWCHAR eMsg[SQL_MAX_MESSAGE_LENGTH];
    SQLINTEGER nError;
    rc = SQLGetDiagRecW(handleType, handle, 1,
         (SQLWCHAR *)&sqlState, (SQLINTEGER *)&nError,
         (SQLWCHAR *)&eMsg, 255, NULL);
    if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO) {
         printf("\n\t Error:%ls\n",eMsg);
    }
    return(SQL_ERROR);
}
/**************************************************************   Function Name: DrawLine
  Purpose      : To Draw a specified charcter (chr) for specified
                 number of times (len)
**************************************************************/
void DrawLine(SQLINTEGER len, SQLCHAR chr)
{
    printf("\n");
    while(len > 0) {
      printf("%c",chr);
      len--;
    }
    printf("\n");
}
/***************************************************************
  Function Name: example1
  Purpose     : Connect to the specified data source and execute
                 the set of SQL Statements
***************************************************************/
SQLINTEGER example1(SQLCHAR *server, SQLCHAR *uid, SQLCHAR *pwd)
{
    SQLHENV henv;
    SQLHDBC hdbc;
    SQLHSTMT hstmt;
    SQLRETURN rc;
    SQLINTEGER id;
    SQLWCHAR drop[MAX_STMT_LEN];
    SQLCHAR name[MAX_NAME_LEN+1];
    SQLWCHAR create[MAX_STMT_LEN];
    SQLWCHAR insert[MAX_STMT_LEN];
    SQLWCHAR select[MAX_STMT_LEN];
    SQLINTEGER namelen;
    /* Allocate environment and connection handles. */
    /* Connect to the data source. */
    /* Allocate a statement handle. */
    rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE,
         &henv);
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
         return(PrintError(SQL_HANDLE_ENV,henv));
    rc = SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,
         (SQLPOINTER)SQL_OV_ODBC3,SQL_NTS);
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
         return(PrintError(SQL_HANDLE_ENV, henv));
    rc = SQLAllocHandle(SQL_HANDLE_DBC,henv,&hdbc);
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
         return(PrintError(SQL_HANDLE_ENV, henv));
    rc = SQLConnect(hdbc, server, SQL_NTS, uid, SQL_NTS,
         pwd, SQL_NTS);
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
         return(PrintError(SQL_HANDLE_DBC, hdbc));
    rc = SQLAllocHandle(SQL_HANDLE_STMT,hdbc,&hstmt);
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
        return(PrintError(SQL_HANDLE_DBC, hdbc));
    /* drop table 'nameid' if exists, else continue*/
    wcscpy(drop, L"DROP TABLE NAMEID");
    printf("\n%ls", drop);
    DrawLine(wcslen(drop), '-');
    rc = SQLExecDirectW(hstmt, drop, SQL_NTS);
    if (rc == SQL_ERROR) {
        PrintError(SQL_HANDLE_STMT, hstmt);
        }
    /* commit work*/
    rc = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
        return(PrintError(SQL_HANDLE_DBC, hdbc));
    /* create the table nameid(id integer,name varchar(50))*/
    wcscpy(create,
        L"CREATE TABLE NAMEID(ID INT,NAME VARCHAR(50))");
    printf("\n%ls",create);
    DrawLine(wcslen(create),'-');
    rc = SQLExecDirectW(hstmt,create,SQL_NTS);
    if (rc == SQL_ERROR)
        return(PrintError(SQL_HANDLE_STMT,hstmt));
    /* commit work*/
    rc = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
        return(PrintError(SQL_HANDLE_DBC, hdbc));
    /* insert data through parameters*/
    wcscpy(insert, L"INSERT INTO NAMEID VALUES(?,?)");
    printf("\n%ls", insert);
    DrawLine(wcslen(insert), '-');
    rc = SQLPrepareW(hstmt, insert, SQL_NTS);
    if (rc == SQL_ERROR)
        return(PrintError(SQL_HANDLE_STMT,hstmt));
    /* integer(id) data binding*/
    rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,
        SQL_C_LONG, SQL_INTEGER, 0, 0, &id, 0, NULL);
    if (rc !=SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
        return(PrintError(SQL_HANDLE_DBC, hdbc));
    /* char(name) data binding*/
    rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT,
        SQL_C_CHAR, SQL_VARCHAR, 0, 0, &name,
        sizeof(name), NULL);
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
        return(PrintError(SQL_HANDLE_DBC, hdbc));
    id = 100;
    strcpy(name, "SOLID");
    rc = SQLExecute(hstmt);
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
        return(PrintError(SQL_HANDLE_DBC, hdbc));
    /* commit work*/
    rc = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
        return(PrintError(SQL_HANDLE_DBC, hdbc));
    /* free the statement buffers*/
    rc = SQLFreeStmt(hstmt, SQL_RESET_PARAMS);
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
        return(PrintError(SQL_HANDLE_STMT, hstmt));
    rc = SQLFreeStmt(hstmt, SQL_CLOSE);
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
        return(PrintError(SQL_HANDLE_STMT, hstmt));
    /* select data from the table nameid*/
    wcscpy(select, L"SELECT * FROM NAMEID");
    printf("\n%ls", select);
    DrawLine(wcslen(select), '-');
    rc = SQLExecDirectW(hstmt, select, SQL_NTS);
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
         return(PrintError(SQL_HANDLE_DBC, hdbc));
    /* bind buffers for output data*/ id = 0; strcpy(name, "");
    rc = SQLBindCol(hstmt, 1, SQL_C_LONG, &id, 0, NULL);
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
        return(PrintError(SQL_HANDLE_DBC, hdbc));
    rc = SQLBindCol(hstmt, 2, SQL_C_CHAR, &name,
        sizeof(name), &namelen);
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
        return(PrintError(SQL_HANDLE_DBC, hdbc));
    rc = SQLFetch(hstmt);
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
        return(PrintError(SQL_HANDLE_DBC, hdbc));
    printf("\n Data ID :%d", id);
    printf("\n Data Name :%s(%d)\n", name, namelen);
    rc = SQLFetch(hstmt); assert(rc == SQL_NO_DATA);
    /* free the statement buffers*/
    rc = SQLFreeStmt(hstmt, SQL_UNBIND);
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
        return(PrintError(SQL_HANDLE_STMT, hstmt));
    rc = SQLFreeStmt(hstmt, SQL_CLOSE);
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
        return(PrintError(SQL_HANDLE_STMT,hstmt));
    /* Free the statement handle. */
    rc = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
        return(PrintError(SQL_HANDLE_STMT, hstmt));
    /* Disconnect from the data source. */
    rc = SQLDisconnect(hdbc);
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
        return(PrintError(SQL_HANDLE_DBC, hdbc));
    /* Free the connection handle. */
    rc = SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
        return(PrintError(SQL_HANDLE_DBC, hdbc));
    /* Free the environment handle. */
    rc = SQLFreeHandle(SQL_HANDLE_ENV,henv);
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
        return(PrintError(SQL_HANDLE_ENV, henv));
    return(0);
}
/***************************************************************
  Function Name: main
  Purpose      : To Control all operations
***************************************************************/
void main(SQLINTEGER argc, SQLCHAR *argv[])
{
  puts("\n\t SOLID ODBC Driver 3.51:");
  puts("\n\t -Usage of static SQL functions");
  puts("\n\t ==============================");
  if (argc != 4){
    puts("USAGE: Example1 <DSN name> <username> <passwd>");
    exit(0);
    }
  else {
    example1(argv[1], argv[2], argv[3]);
  }
}
Interactive ad hoc query example
The following example illustrates how an application can determine the nature of the result set prior to retrieving the results.
/***************************************************************   Sample Name : Example2.c(ad-hoc query processing)
  Author      : UNICOM Systems, Inc.
  Location    : CONSTRUCTING AN APPLICATION-Programmer Guide
  Purpose     : To illustrate how an application determines the
                nature of the result set prior to retrieving
                results.
***************************************************************/
#if (defined(SS_UNIX) || defined(SS_LINUX))
#include <solidodbc3.h>
#else
#include <windows.h>
#endif
#include <stdio.h>
#ifndef TRUE
#define TRUE 1
#endif
#define MAXCOLS 100
#define MAX_DATA_LEN 255
SQLHENV henv;
SQLHDBC hdbc;
SQLHSTMT hstmt;
/***************************************************************
Function Name : PrintError
Purpose       : To Display the error associated with the handle
***************************************************************/
SQLINTEGER PrintError(SQLSMALLINT handleType, SQLHANDLE handle)
{
  SQLRETURN rc = SQL_ERROR;
  SQLCHAR sqlState[6];
  SQLCHAR eMsg[SQL_MAX_MESSAGE_LENGTH];
  SQLINTEGER nError;
    rc = SQLGetDiagRec(handleType, handle, 1,
      (SQLCHAR *)&sqlState, (SQLINTEGER *)&nError,
      (SQLCHAR *)&eMsg, 255, NULL);
    if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO) {
         printf("\n\t Error:%s\n",eMsg);
    }
    return(SQL_ERROR);
/************************************************************** Function Name: DrawLine
Purpose    : To Draw a specified character (line) for specified
               number of times (len) **************************************************************/ void DrawLine(SQLINTEGER len, SQLCHAR line)
{
  printf("\n");
  while(len > 0) {
    printf("%c",line);
    len--;
  }
  printf("\n");
}
/***************************************************************
Function Name: example2
Purpose   : Connect to the specified data source and execute
               the given SQL statement. ***************************************************************/ SQLINTEGER example2(SQLCHAR *sqlstr)
{
  SQLINTEGER i;
  SQLCHAR colname[32];
  SQLSMALLINT coltype;
  SQLSMALLINT colnamelen;
  SQLSMALLINT nullable;
  SQLINTEGER collen[MAXCOLS];
  SQLSMALLINT scale;
  SQLINTEGER outlen[MAXCOLS];
  SQLCHAR data[MAXCOLS][MAX_DATA_LEN];
  SQLSMALLINT nresultcols;
  SQLINTEGER rowcount, nRowCount=0, lineLength=0;
  SQLRETURN rc;
printf("\n%s",sqlstr);
DrawLine(strlen(sqlstr),'=');
/* Execute the SQL statement. */
rc = SQLExecDirect(hstmt, sqlstr, SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) return(PrintError(SQL_HANDLE_STMT, hstmt));
/* See what kind of statement it was. If there are */
/* no result columns, the statement is not a SELECT */
/* statement. If the number of affected rows is */
/* greater than 0, the statement was probably an */
/* UPDATE, INSERT, or DELETE statement, so print */
/* the number of affected rows. If the number of */
/* affected rows is 0, the statement is probably a */
/* DDL statement, so print that the operation was */
/* successful and commit it. */
rc = SQLNumResultCols(hstmt, &nresultcols);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
    return(PrintError(SQL_HANDLE_STMT, hstmt));
if (nresultcols == 0) {
  rc = SQLRowCount(hstmt, &rowcount);
  if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
    return(PrintError(SQL_HANDLE_STMT, hstmt));
  }
  if (rowcount > 0 ) {
    printf("%ld rows affected.\n", rowcount);
  }
  else {
    printf("Operation successful.\n");
  }
  rc = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
  if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
      return(PrintError(SQL_HANDLE_DBC, hdbc));
  }
/* Otherwise, display the column names of the result */
/* set and use the display_size() function to */
/* compute the length needed by each data type. */
/* Next, bind the columns and specify all data will */
/* be converted to char. Finally, fetch and print */
/* each row, printing truncation messages as */
/* necessary. */
else {
  for (i = 0; i < nresultcols; i++) {
    rc = SQLDescribeCol(hstmt, i + 1, colname,
         (SQLSMALLINT)sizeof(colname),
         &colnamelen, &coltype, &collen[i],
         &scale, &nullable);
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO){
         return(PrintError(SQL_HANDLE_STMT, hstmt));
    }
    /* print column names */
    printf("%s\t", colname);
    rc = SQLBindCol(hstmt, i + 1, SQL_C_CHAR,
         data[i], sizeof(data[i]), &outlen[i]);
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO){
         return(PrintError(SQL_HANDLE_STMT, hstmt));
         }
    lineLength += 6 + strlen(colname);
    }
    DrawLine(lineLength-6,'-');
    while (TRUE) {
      rc = SQLFetch(hstmt);
      if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO){
          nRowCount++;
          for (i = 0; i < nresultcols; i++) {
            if (outlen[i] == SQL_NULL_DATA) {
              strcpy((char *)data[i], "NULL");
              }
            printf("%s\t",data[i]);
            }
          printf("\n");
          }
       else {
         if (rc == SQL_ERROR)
           PrintError(SQL_HANDLE_STMT, hstmt);
         break;
         }
       }
     printf("\n\tTotal Rows:%d\n", nRowCount);
     }
  SQLFreeStmt(hstmt, SQL_UNBIND);
  SQLFreeStmt(hstmt, SQL_CLOSE);
  return(0);
}
/***************************************************************
Function Name: main
Purpose      : To Control all operations
***************************************************************/
int __cdecl main(SQLINTEGER argc, SQLCHAR *argv[])
{
  SQLRETURN rc;
  printf("\n\t SOLID ODBC Driver 3.51-Interactive");
  printf("\n\t ad-hoc Query Processing");
  printf("\n\t ==================================\n");

  if (argc != 4) {
    puts("USAGE: Example2 <DSN name> <username> <passwd>");
    exit(0);
  }
  /* Allocate environment and connection handles. */
  /* Connect to the data source. */
  /* Allocate a statement handle. */
  rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
  if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
      return(PrintError(SQL_HANDLE_ENV, henv));
  rc = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
       (SQLPOINTER)SQL_OV_ODBC3, SQL_NTS);
  if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
       return(PrintError(SQL_HANDLE_ENV, henv));
  rc = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
  if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
       return(PrintError(SQL_HANDLE_ENV, henv));
  printf("\n Connecting to %s\n ", argv[1]);
  rc = SQLConnect(hdbc, argv[1], SQL_NTS, argv[2], SQL_NTS,
       argv[3], SQL_NTS);
  if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
       return(PrintError(SQL_HANDLE_DBC, hdbc));
  rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
  if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
       return(PrintError(SQL_HANDLE_DBC, hdbc));
  /* execute the following SQL statements */
  example2("SELECT * FROM SYS_TABLES");
  example2("DROP TABLE TEST_TAB");
  example2("CREATE TABLE TEST_TAB(F1 INT, F2 VARCHAR)");
  example2("INSERT INTO TEST_TAB VALUES(10, 'SOLID')");
  example2("INSERT INTO TEST_TAB VALUES(20, 'MVP')");
  example2("UPDATE TEST_TAB SET F2='UPDATED' WHERE F1 = 20");
  example2("SELECT * FROM TEST_TAB");
  /* Free the statement handle. */
  rc = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
  if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
      return(PrintError(SQL_HANDLE_STMT, hstmt));
  /* Disconnect from the data source. */
  rc =   SQLDisconnect(hdbc);
  if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
      return(PrintError(SQL_HANDLE_DBC, hdbc));
  /* Free the connection handle. */
  rc = SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
  if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
      return(PrintError(SQL_HANDLE_DBC, hdbc));
  /* Free the environment handle. */
  rc = SQLFreeHandle(SQL_HANDLE_ENV, henv);
  if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
      return(PrintError(SQL_HANDLE_ENV, henv));
  return(0);
}
Go up to
solidDB ODBC API