Full text search

ODBC piece-wise data insertion and retrieval

Introduction: ODBC data manipulation

Sample

  local SQL_Handle_type EnvironmentHandle
  local SQL_Handle_type ConnectionHandle
  local SQL_Handle_type StatementHandle
  local SQL_Array_type MidiFile
  local SQL_Array_type MidiFileParam
  local SQL_Array_type MidiFileInd
  local counter CounterSizeInBytes initial {4}
  local stream Block
  local counter BlockSize
  local counter ParamCount
  local counter RetCode
  local stream OutputFile

  SQLSetArraySize( MidiFile, 32, 1 )
  SQLSetArraySize( MidiFileInd, 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 Midi (EmpID, MidiFile) values (1, ?)",
                  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_BINARY, SQL_LONGVARBINARY,
                  65536, 0, MidiFile, 0, MidiFileInd
          )
  output "Binding variables to parameters - "
  do when RetCode != SQL_SUCCESS
     output "failed%n"
     halt with 1
  else
     output "passed%n"
  done

  ; notify that data will be provided at execution time
  SQLSetArrayAsCounter( MidiFileInd, 0, SQL_DATA_AT_EXEC )

  set RetCode to SQLExecute(StatementHandle)
  do when ( RetCode = SQL_NO_DATA ) or ( RetCode = SQL_NEED_DATA )

          set RetCode to SQLParamData(StatementHandle)
     do when ( RetCode = SQL_NO_DATA ) or ( RetCode = SQL_NEED_DATA )

        repeat scan binary-mode file "input.mid"
           ; write file in 4K blocks
           match (any {1 to 4096}) => TempBlock
              set Block to "%x(TempBlock)"
              set BlockSize to (length of Block)
              set RetCode to SQLPutData(StatementHandle, Block, BlockSize )
              do when RetCode != SQL_SUCCESS
                 output "Piece-wise insertion failed%n"
              done
        again

        set RetCode to SQLParamData(StatementHandle)
        output "Calling SQLParamData to end piece-wise insertion - "
        do when RetCode != SQL_SUCCESS
           output "failed%n"
           halt with 1
        else
           output "passed%n"
        done
     done
  done

  set RetCode to SQLCloseCursor( StatementHandle )
  output "Closing the open cursor - "
  do when RetCode != SQL_SUCCESS
     output "failed%n"
     halt with 1
  else
     output "passed%n"
  done

  set RetCode to SQLExecDirect
     (  StatementHandle,
        "select MidiFile from Midi where EmpID = 1",
        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 SQLFetch( StatementHandle )
  output "Fetching data to the cursor - "
  do when RetCode != SQL_SUCCESS
     output "failed%n"
     halt with 1
  else
     output "passed%n"
  done

  open OutputFile with binary-mode as file "output.mid"
  using output as OutputFile
    repeat
       set RetCode to SQLGetData
          ( StatementHandle,
            1,
            SQL_C_BINARY,
            Block,
            2048,
            BlockSize )

       do when RetCode != SQL_Success
         output "SQLGetData read failed %n"
         halt with 1
       done

       do when BlockSize > 0
         put output Block
       else
         exit
       done
    again
  close OutputFile

  set RetCode to SQLCloseCursor( StatementHandle )
  output "Closing the open cursor - "
  do when RetCode != SQL_SUCCESS
     output "failed%n"
     halt with 1
  else
     output "passed%n"
  done

  set RetCode to SQLExecDirect
     (  StatementHandle,
        "delete from Midi where EmpID = 1",
        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 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.