Red Hat Database: Programmer's Guide
PrevChapter 6. ODBC InterfaceNext

Code Sample

Initialize.h

#include <stdio.h>
#include <stdlib.h>
#include "iodbc/iodbc.h"
#include "iodbc/isqlext.h"
 
SQLCHAR DSN[10] = "RHDB";
 
// The following should be changed to your userid and password
// and the length of the variable should be the actual length + 1
SQLCHAR DSN_userid[9] = "postgres";
SQLCHAR DSN_password[9] = "postgres";
 
int Initialize(HENV * phenv, HDBC * phdbc, HSTMT * phstmt);
void cleanup(HENV henv, HDBC hdbc, HSTMT hstmt);
 
// Allocate the environment, connection, statement handles
int Initialize(HENV * phenv, HDBC * phdbc, HSTMT * phstmt)
{
  int res;
 
  // Allocate an environment handle
  res = SQLAllocEnv(phenv);
  if (res != SQL_SUCCESS) {
    fprintf(stderr, "Unable to allocate environment handle (ret=%d)\n", res);
    exit(1);
  }
 
  // Allocate a connection handle
  res = SQLAllocConnect(*phenv, phdbc);
  if (res != SQL_SUCCESS) {
    fprintf(stderr, "Unable to allocate connection handle (ret=%d)\n", res);
    cleanup(*phenv, *phdbc, *phstmt);
    exit(1);
  }
 
  // The connection parameters
  printf("Connection Parameters: DSN='%s', UID='%s', PWD='%s'\n", DSN,
    DSN_userid, DSN_password);
 
  // Connect to the database
  res = SQLConnect(*phdbc, DSN, SQL_NTS, DSN_userid, SQL_NTS,
    DSN_password, SQL_NTS);
  if (res != SQL_SUCCESS) {
    fprintf(stderr, "Unable to connect to datasource (ret=%d)\n", res);
    cleanup(*phenv, *phdbc, *phstmt);
    exit(1);
  }
 
  // Allocate a statement handle
  res = SQLAllocStmt(*phdbc, phstmt);
  if (res != SQL_SUCCESS) {
    fprintf(stderr, "Unable to allocate statement handle (ret=%d)\n", res);
    cleanup(*phenv, *phdbc, *phstmt);
    exit(1);
  }
  printf("Handles initialized.\n");
  return 0;
}
 
// Cleanup before exit
void cleanup(HENV henv, HDBC hdbc, HSTMT hstmt)
{
  // Disconnect from the database and free all handles
  SQLFreeStmt(hstmt, SQL_CLOSE);
  SQLDisconnect(hdbc);
  SQLFreeConnect(hdbc);
  SQLFreeEnv(henv);
  return;
}

PrepAndExec.c

#include <stdio.h>
#include <stdlib.h>
#include "iodbc/iodbc.h"
#include "iodbc/isqlext.h"
#include "Initialize.h"
 
int main(int argc, char *argv[])
{
  HENV henv;
  HDBC hdbc;
  HSTMT hstmt;
  SQLCHAR SelectStmt[255];
  int res;
 
  if (argc != 2) {
    fprintf(stderr, "Usage: PrepAndExec <SQL Statement>\n");
    exit(1);
  } else {
    if (strlen (argv[1]) > 254) {
      fprintf(stderr, "SQL statement is longer than 254 characters\n");
      exit(1);
    }
    strncpy((char *) SelectStmt, argv[1], 255);
  }
 
  // Allocate all handles
  res = Initialize(&henv, &hdbc, &hstmt);
  if (res != 0) {
    fprintf(stderr, "Unable to initialize (ret=%d)\n", res);
    exit(1);
  }
 
// Prepare the user provided SQL statement
  res = SQLPrepare(hstmt, SelectStmt, SQL_NTS);
  if (res != SQL_SUCCESS) {
    fprintf(stderr, "Unable to prepare statement (ret=%d)\n", res);
    // Disconnect from the database and free all handles
    cleanup(henv, hdbc, hstmt);
    exit(1);
  }
 
  // Execute the SQL Statement
  res = SQLExecute(hstmt);
  if (res != SQL_SUCCESS) {
    fprintf(stderr, "Unable to execute statement (ret=%d)\n", res);
    // Disconnect from the database and free all handles
    cleanup(henv, hdbc, hstmt);
    exit(1);
  }
  printf("'%s' prepared and executed.\n", SelectStmt);
  cleanup(henv, hdbc, hstmt);
  return 0;
}

ExecDirect.c

#include <stdio.h>
#include <stdlib.h>
#include "iodbc/iodbc.h"
#include "iodbc/isqlext.h"
#include "Initialize.h"
 
int main(int argc, char *argv[])
{
  HENV henv;
  HDBC hdbc;
  HSTMT hstmt;
  SQLCHAR SelectStmt[255];
  int res;
 
  if (argc != 2) {
    fprintf(stderr, "Usage: ExecDirect <SQL Statement>\n");
    exit(1);
  } else {
    if (strlen (argv[1]) > 254) {
      fprintf(stderr, "SQL statement is longer than 254 characters\n");
      exit(1);
    }
    strncpy((char *) SelectStmt, argv[1], 255);
  }
 
  // Allocate all handles
  res = Initialize(&henv, &hdbc, &hstmt);
  if (res != 0) {
    fprintf("Unable to initialize (ret=%d)\n", res);
    exit(1);
  }
 
  // Prepare and execute the user provided SQL statement
  res = SQLExecDirect(hstmt, SelectStmt, SQL_NTS);
  if (res != SQL_SUCCESS) {
    fprintf("Unable to execute statement directly (ret=%d)\n", res);
    // Disconnect from the database and free all handles
    cleanup(henv, hdbc, hstmt);
    exit(1);
  }
  printf("'%s' directly executed.\n", SelectStmt);
  cleanup(henv, hdbc, hstmt);
  return 0;
}

ViewTable.c

#include <stdio.h>
#include <stdlib.h>
#include "iodbc/iodbc.h"
#include "iodbc/isqlext.h"
#include "Initialize.h"
 
int main(int argc, char *argv[])
{
  HENV henv;
  HDBC hdbc;
  HSTMT hstmt;
  SQLCHAR SelectStmt[255], cname1[25], cname2[50];
  int res;
 
  if (argc != 1) {
    fprintf(stderr, "Usage: ViewTable\n");
    exit(1);
  }
 
  // Allocate all handles
  res = Initialize(&henv, &hdbc, &hstmt);
  if (res != 0) {
    fprintf(stderr, "Unable to initialize (ret=%d)\n", res);
    exit(1);
  }
 
  // Create the query
  strcpy((char *) SelectStmt, "SELECT * FROM players");
 
  // Prepare and execute the SQL statement
  res = SQLExecDirect(hstmt, SelectStmt, SQL_NTS);
  if (res != SQL_SUCCESS) {
    fprintf(stderr, "Unable to execute statement directly (ret=%d)\n", res);
    // Disconnect from the database and free all handles
    cleanup(henv, hdbc, hstmt);
    exit(1);
  }
 
  // Bind the columns in the result data set returned to
  // application variables
  res = SQLBindCol(hstmt, 1, SQL_C_CHAR, cname1, sizeof(cname1), NULL);
  if (res != SQL_SUCCESS) {
    fprintf(stderr, "Unable to bind column 1 (ret=%d)\n", res);
    cleanup(henv, hdbc, hstmt);
    exit(1);
  }
  res = SQLBindCol(hstmt, 2, SQL_C_CHAR, cname2, sizeof(cname2), NULL);
  if (res != SQL_SUCCESS) {
    fprintf(stderr, "Unable to bind column 2 (ret=%d)\n", res);
    cleanup(henv, hdbc, hstmt);
    exit(1);
  }
 
  printf("\nContent of players:\n");
  printf("******************\n");
 
  // While there are remaining rows in the result set,
  // retrieve and display them
  res = SQLFetch(hstmt);
  while (res != SQL_NO_DATA_FOUND) {
    if (res != SQL_SUCCESS && res != SQL_SUCCESS_WITH_INFO) {
      fprintf(stderr, "Unable to fetch row (ret=%d)\n", res);
      cleanup(henv, hdbc, hstmt);
      exit(1);
    }
    printf("%s -- %s \n", cname1, cname2);
    res = SQLFetch(hstmt);
  }
  cleanup(henv, hdbc, hstmt);
  return 0;
}

Transact.c

#include <stdio.h>
#include <stdlib.h>
#include "iodbc/iodbc.h"
#include "iodbc/isqlext.h"
#include "Initialize.h"
 
int main(int argc, char *argv[])
{
  HENV henv;
  HDBC hdbc;
  HSTMT hstmt;
  SQLCHAR SelectStmt[255];
  int res, commit = -1;
 
  if (argc != 2) {
    fprintf(stderr, "Usage: Transact <action>\n");
    fprintf(stderr, "action=1 to commit, action=0 to rollback");
    exit(1);
  } else {
    if (atoi(argv[1]) == 1) {
      commit = 1;
    } else if (atoi(argv[1]) == 0) {
      commit = 0;
    } else {
      fprintf(stderr, "Usage: Transact <action>\n");
      fprintf(stderr, "action=1 to commit, action=0 to rollback");
      exit(1);
    }
  }
 
  // Allocate all handles
  res = Initialize(&henv, &hdbc, &hstmt);
  if (res != 0) {
    fprintf(stderr, "Unable to initialize (ret=%d)\n", res);
    exit(1);
  }
 
  // Turn AUTOCOMMIT off so SQL statements can be grouped into a
  // transaction
  res = SQLSetConnectOption(hdbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF);
  if (res != SQL_SUCCESS) {
    fprintf(stderr, "Unable to turn AUTOCOMMIT off (ret=%d)\n", res);
    cleanup(henv, hdbc, hstmt);
    exit(1);
  }
 
  // Create update string
  strcpy((char *) SelectStmt, "INSERT into players VALUES ");
  strcat((char *) SelectStmt, "(\'Tracy McGrady\', \'Orlando Magic\')");
 
  // Execute the SQL statement
  res = SQLExecDirect(hstmt, SelectStmt, SQL_NTS);
  if (res != SQL_SUCCESS) {
    fprintf(stderr, "Unable to directly execute statement (ret=%d)\n", res);
    // Disconnect from the database and free all handles
    cleanup(henv, hdbc, hstmt);
    exit(1);
  }
 
  // Create delete string
  strcpy((char *) SelectStmt, "DELETE FROM players WHERE ");
  strcat((char *) SelectStmt, "name=\'Michael Jordan\'");
 
  // Execute the SQL statement
  res = SQLExecDirect(hstmt, SelectStmt, SQL_NTS);
  if (res != SQL_SUCCESS) {
    fprintf(stderr, "Unable to directly execute statement (ret=%d)\n", res);
    // Disconnect from the database and free all handles
    cleanup(henv, hdbc, hstmt);
    exit(1);
  }
 
  // Commit or rollback the transaction
  if (commit == 1) {
    res = SQLTransact(henv, hdbc, SQL_COMMIT);
    printf("Transaction committed\n", res);
  } else {
    res = SQLTransact(henv, hdbc, SQL_ROLLBACK);
    printf("Transaction rolled back\n", res);
  }
  if (res != SQL_SUCCESS) {
    fprintf(stderr, "Unable to commit the transaction (ret=%d)\n", res);
    cleanup(henv, hdbc, hstmt);
    exit(1);
  }
  cleanup(henv, hdbc, hstmt);
  return 0;
}
PrevHomeNext
Compiling and Running the Sample ApplicationsUpJDBC Interface