Re: Looking for ways to fetch large (> 1MB) long raw data using OCI

From: stuart coupe <stuart_at_easysoft.com>
Date: Wed, 26 Sep 2001 15:50:14 +0100
Message-ID: <3BB1EB26.96C6CDF1@easysoft.com>

Jim Monte wrote:

> Hi,
>
> I am having difficulty fetching large BLOB (long raw) of unknown size
> from an Oracle 8.1.5 database using OCI. Wondering if there is a
> recommended method of doing this. Any success stories, sample codes,
> pointers will be invaluable in getting me out of this mess.
>
> Thanks in advance.
>
> Jim

I have been using this on 816/817. Hope it is of use.

Cheers,
Stuart.

---

#include <oci.h>
#include <ocidfn.h>
#include <stdio.h>
#include <stdlib.h>

#define DATA_SIZE 500000

/*----------------------------------------------------------------------------*/

void error( sword ret, OCIError* oci_err, char* func , int lineno ) {
  text msgbuf[100];
  sb4 errcode = 0;

  fprintf(stdout,"ORACLE error in %s at %d\n", func, lineno ) ;   switch ( ret )
  {
    case OCI_INVALID_HANDLE :

      printf ( "Invalid handle\n" ) ;
      break ;
    case OCI_NEED_DATA :
      printf ( "Needed Data.\n" ) ;
      return ;
    default :
      if ( ! OCIErrorGet ((dvoid *) oci_err, (ub4) 1, (text *) NULL,
&errcode,
                    msgbuf, (ub4) sizeof(msgbuf), (ub4)
OCI_HTYPE_ERROR))
      {
        fprintf(stdout,"ERROR CODE = %d\n", errcode);
        fprintf(stdout,"%s\n", msgbuf);
      }

  }
  exit(1);
}

/*----------------------------------------------------------------------------*/int

main ( int argc, char* argv[] )
{
  int i = 0 ;

  sword                 ret ;
  sb2                   ind ;
  OCIError*             oci_err ;
  OCIEnv*               oci_env ;
  OCISvcCtx*            oci_svc ;
  OCIStmt*              oci_stmt ;
  char data             [ DATA_SIZE ] ;
  OCIDefine*            oci_define ;
  OCIParam*             param ;
  ub4                   num_cols ;
  char                  file_name [ 100 ] ;
  FILE*                 fp ;

  char *DB  ="oracle.elf" ;

  char* UID ="system" ;
  char* PWD ="manager" ;

  char* sql = argv[1] ;

  OCIInitialize( OCI_DEFAULT, 0, 0, 0, 0 );

  OCIEnvInit( &oci_env, OCI_DEFAULT, 0, 0 );

  ret=OCIHandleAlloc( oci_env, (dvoid**)&oci_err, OCI_HTYPE_ERROR, 0, 0 );
  if (ret) error ( ret, oci_err, "OCIHandleAlloc", __LINE__ ) ;

  ret=OCILogon( oci_env,oci_err,&oci_svc,
                UID,strlen(UID), PWD,strlen(PWD), DB, strlen(DB) ) ;
  if (ret) error ( ret, oci_err, "OCILogon", __LINE__ ) ;

  ret=OCIHandleAlloc( oci_env, (dvoid**)&oci_stmt, OCI_HTYPE_STMT, 0, 0 );
  if (ret) error ( ret, oci_err, "OCIHandleAlloc", __LINE__ ) ;

  printf ( "\nSQL: %s\n", sql ) ;
  ret=OCIStmtPrepare( oci_stmt, oci_err, sql,

                strlen(sql), OCI_NTV_SYNTAX, OCI_DEFAULT) ;

  if (ret) error ( ret, oci_err, "OCIStmtPrepare", __LINE__ ) ;

  ret=OCIDefineByPos( oci_stmt, &oci_define, oci_err, 1, 0, (sb4) SB4MAXVAL,
        SQLT_LNG,(dvoid *)NULL, (ub2*)NULL, (ub2*)0, (ub4) OCI_DYNAMIC_FETCH) ;
  if (ret) error ( ret, oci_err, "OCIDefineByPos", __LINE__ ) ;

  ret =
OCIStmtExecute(oci_svc,oci_stmt,oci_err,0,0,0,0,OCI_COMMIT_ON_SUCCESS);   if ( ret == -1 ) error ( ret, oci_err, "OCIStmtExecute", __LINE__ ) ;

  while ( 1 )
  {
    int total_size = 0 ;
    ub4 data_length = 1000 ;
    ret = OCIStmtFetch( oci_stmt, oci_err, 1, OCI_FETCH_NEXT, OCI_DEFAULT);
    if ( ret && ( ret != OCI_NEED_DATA) )     {

      error ( ret, oci_err, "OCIStmtFetch", __LINE__ ) ;
      break ;

    }
    do
    {

      sb2 ind ;
      ub2  return_code ;
      ub1 piece , in_outp ;
      ub4 type, iterp, idxp ;

      ret = OCIStmtGetPieceInfo(oci_stmt, oci_err, &oci_define,
                  &type, &in_outp, &iterp, &idxp, &piece );
      if (ret ) error ( ret, oci_err, "OCIStmtGetPieceInfo", __LINE__ )
;
      ret = OCIStmtSetPieceInfo ( oci_define, OCI_HTYPE_DEFINE, oci_err,

                  ((char*)data)+total_size, &data_length,piece, NULL,
NULL);
      if (ret ) error ( ret, oci_err, "OCIStmtSetPieceInfo", __LINE__ )
;
      ret = OCIStmtFetch( oci_stmt, oci_err, 1, OCI_FETCH_NEXT,
OCI_DEFAULT);
      if (ret && ( ret != OCI_NEED_DATA ) )
                error ( ret, oci_err, "OCIStmtFetch", __LINE__ ) ;

      total_size += data_length ;

    } while ( ret == OCI_SUCCESS_WITH_INFO || ret == OCI_NEED_DATA);     printf ( "\n\n Total Size %d\n\n", total_size ) ;     sprintf ( file_name, "%d.out", i++ ) ;     fp = fopen( file_name , "wb") ;
    fwrite( data, 1, total_size , fp);
    fclose ( fp ) ;

  }

  ret=OCIHandleFree( oci_stmt, OCI_HTYPE_STMT ) ;   if (ret) error ( ret, oci_err, "OCIHandleFree", __LINE__ ) ;

  ret=OCILogoff( oci_svc, oci_err ) ;
  if (ret) error ( ret, oci_err, "OCILogoff", __LINE__ ) ;

  ret=OCIHandleFree( oci_err, OCI_HTYPE_ERROR ) ;   if (ret) error ( ret, oci_err, "OCIHandleFree", __LINE__ ) ;

  ret=OCIHandleFree( oci_env, OCI_HTYPE_ENV ) ;   if (ret) error ( ret, oci_err, "OCIHandleFree", __LINE__ ) ;

  printf ( "\nFinished\n" ) ;

} Received on Wed Sep 26 2001 - 09:50:14 CDT