Full text search

Inserting and deleting ODBC records

Introduction: ODBC data manipulation

Sample

  local SQL_Handle_type EnvironmentHandle
  local SQL_Handle_type ConnectionHandle
  local SQL_Handle_type StatementHandle
  local SQL_Array_type EmpName
  local SQL_Array_type EmpID
  local SQL_Array_type Salary
  local SQL_Array_type EmpNameInd
  local SQL_Array_type EmpIDInd
  local SQL_Array_type SalaryInd
  local counter ParamCount
  local counter RowCount
  local counter CounterSizeInBytes initial {4}
  local counter RetCode

  SQLSetArraySize( EmpName, 50, 1 )
  SQLSetArraySize( EmpId,   50, 1 )
  SQLSetArraySize( Salary,  50, 1 )
  SQLSetArraySize( EmpNameInd, CounterSizeInBytes, 1 )
  SQLSetArraySize( EmpIdInd,   CounterSizeInBytes, 1 )
  SQLSetArraySize( SalaryInd,  CounterSizeInBytes, 1 )

  set RetCode to SQLAllocEnv(EnvironmentHandle)
  output "Allocating environment handle - "
  do when RetCode != SQL_SUCCESS
     output "failed%n"
     halt with 1
  else
     output "passed%n"
  done

  set RetCode to SQLAllocHandle
     ( SQL_HANDLE_DBC, EnvironmentHandle, ConnectionHandle )
  output "Allocating connection handle - "
  do when RetCode != SQL_SUCCESS
     output "failed%n"
     halt with 1
  else
     output "passed%n"
  done

  set RetCode to SQLConnect( ConnectionHandle, "omodbc", 20, "", 0, "", 0 )
  output "Connecting to database - "
  do when RetCode != SQL_SUCCESS
     output "failed%n"
     halt with 1
  else
     output "passed%n"
  done

  set RetCode to SQLAllocStmt(ConnectionHandle, StatementHandle)
  output "Allocating statement handle - "
  do when RetCode != SQL_SUCCESS
     output "failed%n"
     halt with 1
  else
     output "passed%n"
  done

  set RetCode to SQLPrepare
          (       StatementHandle,
                  "insert into Employee (EmpName, EmpID, Salary) " ||
                  "values ( ?, ?, ? )",
                  SQL_NTS
          )
  output "Preparing statement - "
  do when RetCode != SQL_SUCCESS
     output "failed%n"
     halt with 1
  else
     output "passed%n"
  done

  set RetCode to SQLNumParams( StatementHandle, ParamCount )
  output "Counting parameters in statement - "
  do when RetCode != SQL_SUCCESS
     output "failed%n"
     halt with 1
  else
     output "passed%n"
     output "- %d(ParamCount)%n"
  done

  set RetCode to SQLBindParameter
          (       StatementHandle, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR,
                  50, 0, EmpName, 50, EmpNameInd
          )
  set RetCode to SQLBindParameter
          (       StatementHandle, 2, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER,
                  50, 0, EmpId, 50, EmpIdInd
          ) when RetCode = SQL_SUCCESS
  set RetCode to SQLBindParameter
          (       StatementHandle, 3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_REAL,
                  9, 9, Salary, 50, SalaryInd
          ) when RetCode = SQL_SUCCESS
  output "Binding variables to parameters - "
  do when RetCode != SQL_SUCCESS
     output "failed%n"
     halt with 1
  else
     output "passed%n"
  done

  SQLSetArrayAsString( EmpName, 0, "Habib" )
  SQLSetArrayAsCounter( EmpNameInd, 0, 5 )
  SQLSetArrayAsCounter( EmpId, 0, 8 )
  SQLSetArrayAsString( Salary, 0, "100001.11" )
  SQLSetArrayAsCounter( SalaryInd, 0, 10 )

  set RetCode to SQLExecute( StatementHandle )
  output "Executing prepared statement - "
  do when RetCode != SQL_SUCCESS
     output "failed%n"
     halt with 1
  else
     output "passed%n"
  done

  set RetCode to SQLRowCount( StatementHandle, RowCount )
  output "Counting inserted records - "
  do when RetCode != SQL_SUCCESS
     output "failed%n"
     halt with 1
  else
     output "passed%n"
     output "- %d(RowCount)%n"
  done

  set RetCode to SQLExecDirect
          (       StatementHandle,
                  "delete from Employee where EmpName='Habib'",
                  SQL_NTS
           )
  output "Combined preparation and execution of a statement - "
  do when RetCode != SQL_SUCCESS
     output "failed%n"
     halt with 1
  else
     output "passed%n"
  done

  set RetCode to SQLRowCount( StatementHandle, RowCount )
  output "Counting deleted records - "
  do when RetCode != SQL_SUCCESS
     output "failed%n"
     halt with 1
  else
     output "passed%n"
     output "- %d(RowCount)%n"
  done

  set RetCode to SQLFreeHandle(SQL_HANDLE_STMT, StatementHandle)
  output "Freeing statement handle resources - "
  do when RetCode != SQL_SUCCESS
     output "failed%n"
     halt with 1
  else
     output "passed%n"
  done

  set RetCode to SQLDisconnect( ConnectionHandle )
  output "Disconnecting from database - "
  do when RetCode != SQL_SUCCESS
     output "failed%n"
     halt with 1
  else
     output "passed%n"
  done

  set RetCode to SQLFreeHandle(SQL_HANDLE_DBC, ConnectionHandle)
  output "Freeing connection handle resources - "
  do when RetCode != SQL_SUCCESS
     output "failed%n"
     halt with 1
  else
     output "passed%n"
  done

  set RetCode to SQLFreeHandle(SQL_HANDLE_ENV, EnvironmentHandle)
  output "Freeing environment handle resources - "
  do when RetCode != SQL_SUCCESS
     output "failed%n"
     halt with 1
  else
     output "passed%n"
  done

----

[CONTENTS] [CONCEPTS] [SYNTAX] [LIBRARIES] [SAMPLES] [ERRORS] [INDEX]

Copyright © OmniMark Technologies Corporation, 1988-1999.