unit1.cpp

通过oci实现的oracle查询分析器功能和可以 尤其对BLOB字段的度曲

//---------------------------------------------------------------------------

#include <vcl.h>
#pragma hdrstop

#include "Unit1.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma resource "*.dfm"
TForm1 *Form1;
//---------------------------------------------------------------------------
__fastcall TForm1::TForm1(TComponent* Owner)
        : TForm(Owner)
{
}
//---------------------------------------------------------------------------
/* Copyright (c) 2001, Oracle Corporation.  All rights reserved.  */

/*

   NAME
     cdemosc.c - OCI demo program for scrollable cursor.

   DESCRIPTION
     An example program which reads employee records from table empo.
     SQL> describe empo;
     Name                      Null?    Type
     ------------------------- -------- ---------------
     EMPNO                              NUMBER
     ENAME                              CHAR(5)
     ADDR                               EMPADDR
     ECOLL                              EVARRAY
     SQL> describe empaddr;
     Name                      Null?    Type
     ------------------------- -------- ---------------
     STATE                              CHAR(2)
     ZIP                                NUMBER

   EXPORT FUNCTION(S)
     <external functions defined for use outside package - one-line descriptions>

   INTERNAL FUNCTION(S)
     <other external functions defined - one-line descriptions>

   STATIC FUNCTION(S)
     <static functions defined - one-line descriptions>

   NOTES
     Dependent Files:
       cdemosc.sql  - SQL script to be run before execution.

   MODIFIED   (MM/DD/YY)
   ani         04/30/01 - Merged ani_ocidemo
   ani         04/24/01 - Creation

*/

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

#ifndef OCI_ORACLE
#include <oci.h>
#endif

typedef struct address
{
  OCIString * state ;
  OCINumber zip;
} address ;

typedef struct null_address
{
  sb2 null_state ;
  sb2 null_zip;
} null_address ;

#define MAXROWS 100
#define MAX_ENAMELEN 20

static text *username = (text *) "SJXT";
static text *password = (text *) "SJXT";

/* Define SQL statements to be used in program. */
static text *selemp = (text *)" Select 姓名,图像数据 from CZRK_SJ_BCSJ";

static sword    empno[MAXROWS] ;
 //text empname[MAXROWS][MAX_ENAMELEN];

//OCILobLocator  * blob[MAXROWS];
 text empname[MAX_ENAMELEN];

OCILobLocator  * blob;

 address * empaddr [MAXROWS] ;
 null_address * indaddr [MAXROWS] ;
 ub4 rc[MAXROWS] ;
 OCIColl * evarray[MAXROWS] ;
 OCIEnv *envhp;
 OCIError *errhp;
 OCISvcCtx *svchp;
  OCIServer *srvhp;
 sword status;
 OCIStmt *stmthp;
 sword status1;
 OCISession *authp = (OCISession *) 0;

  OCIType * addr_tdo ;
  OCIType * addr_tdo2 ;
  ub4 empaddrsz ;
  OCIDefine *defn1p = (OCIDefine *) 0;
  OCIDefine *defn2p = (OCIDefine *) 0;
  OCIDefine *defn3p = (OCIDefine *) 0;
  OCIDefine *defn4p = (OCIDefine *) 0;
  OCIDefine *defn5p = (OCIDefine *) 0;
  int num ;

  ub4 prefetch = 100 ;
                
/*---------------------------------------------------------------------------
                     STATIC FUNCTION DECLARATIONS 
  ---------------------------------------------------------------------------*/

static void checkerr(OCIError *errhp, sword status);
static void checkprint(OCIError *errhp, sword status, ub4 nrows );
static void cleanup(void);
static void myprint (ub4);
void redblob(OCILobLocator  * blob1,TStream* Stream);
void redblob(OCILobLocator  * blob1,TStream* Stream)
{
  ub4             blobdatalen;
  ub4             amtp;
  status = OCILobGetLength( svchp, errhp, blob1, (ub4*)&blobdatalen );
  if( blobdatalen == 0 || status < 0 )
    return ;
  char* value = (char*)malloc( blobdatalen );
  amtp = blobdatalen ;
  status = OCILobRead( svchp, errhp, blob1, &amtp,1,value,blobdatalen,0,0,0,(ub1) SQLCS_IMPLICIT );
  if( status != OCI_SUCCESS )
  {
     free( value );
     throw Exception("BLOB字段读错误");
     //return 1;
  }

  Stream->Write(value , blobdatalen);
  free( value );
}
/*check fetch status and print rows upon success*/
void checkprint(OCIError *errhp, sword status,ub4 nrows)

{
  checkerr(errhp,status);
  // if (status != OCI_ERROR && status != OCI_NO_DATA)
  //  myprint(nrows);
  if (status != OCI_ERROR )
    myprint(nrows);
}


/*check status and print error information */
void checkerr(OCIError *errhp,sword status)

{
  text errbuf[512];
  sb4 errcode = 0;

  switch (status)
  {
  case OCI_SUCCESS:
    break;
  case OCI_SUCCESS_WITH_INFO:
      throw Exception("Error - OCI_SUCCESS_WITH_INFO\n");

    break;
  case OCI_NEED_DATA:
     throw Exception("Error - OCI_NEED_DATA\n");
    break;
  case OCI_NO_DATA:
     //throw Exception("Error - OCI_NODATA\n");
    break;
  case OCI_ERROR:
    (void) OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL, &errcode,
                        errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
     throw Exception((char *)errbuf);
    break;
  case OCI_INVALID_HANDLE:
    throw Exception("Error - OCI_INVALID_HANDLE\n");
    break;
  case OCI_STILL_EXECUTING:
      throw Exception("Error - OCI_STILL_EXECUTE\n");
    break;
  case OCI_CONTINUE:
   throw Exception("Error - OCI_CONTINUE\n");
    break;
  default:
    break;
  }
}



void cleanup()
{
  if (envhp)
    (void) OCIHandleFree((dvoid *) envhp, OCI_HTYPE_ENV);
  return;
}


void myfflush()
{
  eb1 buf[50];

  fgets((char *) buf, 50, stdin);
}



void myprint (ub4 nrows)
{
  int i, j, num, cp, rc, amount ;
  sb4 colsz;
  void * elem ;  
  ub4 sz = sizeof(cp) ;
  boolean exist = FALSE;  
  char dd[600];
  checkerr(errhp, OCIAttrGet((CONST void *) stmthp, OCI_HTYPE_STMT, 
                             (void *) & cp, (ub4 *)  & sz, 
                             OCI_ATTR_CURRENT_POSITION, errhp));
  checkerr(errhp, OCIAttrGet((CONST void *) stmthp, OCI_HTYPE_STMT,
                             (void *) & rc, (ub4 *)  & sz, 
                       OCI_ATTR_ROW_COUNT, errhp));
  sprintf(dd,"******** Current position, Row Count = %d, %d ******** \n", cp, rc);
  Form1->Memo1->Lines->Add(dd);
  Form1->Edit2->Text=dd;

  for (i =0 ; i < nrows ; i++ )
  {
     
     Form1->Memo2->Lines->Add((char*)&empname[i]);
     TMemoryStream *stream= new TMemoryStream();
            TJPEGImage *Jpg= new TJPEGImage();
           stream->Clear();
           redblob(blob,stream);
           if (stream->Size>0 )
           {
           stream->Seek(0,0);
           Jpg->LoadFromStream(stream);
           Form1->Image1->Picture->Bitmap->Assign(Jpg);
           Form1->Image1->Visible = true;
           }
           Application->ProcessMessages();

           delete  stream;
           delete  Jpg;
  }
}



void __fastcall TForm1::Button1Click(TObject *Sender)
{


   
  (void) OCIInitialize((ub4) OCI_DEFAULT | OCI_OBJECT, (dvoid *)0,
                       (dvoid * (*)(dvoid *, size_t)) 0,
                       (dvoid * (*)(dvoid *, dvoid *, size_t))0,
                       (void (*)(dvoid *, dvoid *)) 0 );

  (void) OCIEnvInit( (OCIEnv **) &envhp, OCI_DEFAULT, (size_t) 0,
                     (dvoid **) 0 );

  (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &errhp, OCI_HTYPE_ERROR,
                   (size_t) 0, (dvoid **) 0);

  /* server contexts */
  (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &srvhp, OCI_HTYPE_SERVER,
                   (size_t) 0, (dvoid **) 0);

  (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &svchp, OCI_HTYPE_SVCCTX,
                   (size_t) 0, (dvoid **) 0);

  (void) OCIServerAttach( srvhp, errhp, (text *)"SJXT", (sb4) strlen("SJXT"), 0);

  /* set attribute server context in the service context */
  (void) OCIAttrSet( (dvoid *) svchp, OCI_HTYPE_SVCCTX, (dvoid *)srvhp,
                     (ub4) 0, OCI_ATTR_SERVER, (OCIError *) errhp);

  (void) OCIHandleAlloc((dvoid *) envhp, (dvoid **)&authp,
                        (ub4) OCI_HTYPE_SESSION, (size_t) 0, (dvoid **) 0);

  (void) OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION,
                 (dvoid *) username, (ub4) strlen((char *)username),
                 (ub4) OCI_ATTR_USERNAME, errhp);

  (void) OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION,
                 (dvoid *) password, (ub4) strlen((char *)password),
                 (ub4) OCI_ATTR_PASSWORD, errhp);

  checkerr(errhp, OCISessionBegin ( svchp,  errhp, authp, OCI_CRED_RDBMS,
                          (ub4) OCI_DEFAULT));

  (void) OCIAttrSet((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX,
                   (dvoid *) authp, (ub4) 0,
                   (ub4) OCI_ATTR_SESSION, errhp);

  checkerr(errhp, OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &stmthp,
           OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));
//for (int i=0;i<MAXROWS ;i++)
//{
  checkerr(errhp,OCIDescriptorAlloc( (dvoid*) envhp, (dvoid**) &(blob),(ub4) OCI_DTYPE_LOB, (size_t)0,(dvoid**)0 ) ) ;
//}



  /**** SCROLLABLE RESULT SET *****/

  /* Retrieve the employee records */
  checkerr(errhp, OCIStmtPrepare(stmthp, errhp, selemp,
                                (ub4) strlen((char *) selemp),
                                (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
  (void) OCIAttrSet((dvoid *) stmthp, (ub4) OCI_HTYPE_STMT,
                 (dvoid *) & prefetch, 0,
                 (ub4) OCI_ATTR_PREFETCH_ROWS, errhp);

  /* bind the input variable */

  checkerr(errhp, OCIDefineByPos(stmthp, &defn1p, errhp, 1, (dvoid **) empname,
                   (sword) MAX_ENAMELEN, SQLT_STR, (dvoid *) 0, (ub2 *)0,
                   (ub2 *)0, OCI_DEFAULT));
  checkerr(errhp, OCIDefineByPos(stmthp, &defn2p, errhp, 2, (dvoid*) &(blob),
                   (sb4)-1, SQLT_BLOB, (dvoid *) 0, (ub2 *)0,
                   (ub2 *)0, OCI_DEFAULT));


  checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 0, (ub4)
                                 0,  (CONST OCISnapshot *)
                                 NULL, (OCISnapshot *) NULL,
                                 OCI_STMT_SCROLLABLE_READONLY ));

 checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 1,
                               OCI_FETCH_NEXT, (sb4) 0, OCI_DEFAULT),1);
  checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 1,
                                OCI_FETCH_PRIOR, (sb4) 0, OCI_DEFAULT),1);

   checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 1,
                               OCI_FETCH_NEXT, (sb4) 0, OCI_DEFAULT),1);


 checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 1,
                               OCI_FETCH_NEXT, (sb4) 0, OCI_DEFAULT),1);

 checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 1,
                               OCI_FETCH_NEXT, (sb4) 0, OCI_DEFAULT),1);
checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 1,
                               OCI_FETCH_NEXT, (sb4) 0, OCI_DEFAULT),1);
checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 1,
                               OCI_FETCH_NEXT, (sb4) 0, OCI_DEFAULT),1);
checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 1,
                               OCI_FETCH_NEXT, (sb4) 0, OCI_DEFAULT),1);
checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 1,
                               OCI_FETCH_NEXT, (sb4) 0, OCI_DEFAULT),1);
 checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 1, 
                               OCI_FETCH_FIRST, (sb4) 0, OCI_DEFAULT),1);





 /* checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 3,
                               OCI_FETCH_ABSOLUTE, (sb4) 14, OCI_DEFAULT),3);

  checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 3,
                               OCI_FETCH_RELATIVE, (sb4) 0, OCI_DEFAULT),3);

  checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 2, 
                               OCI_FETCH_ABSOLUTE, (sb4) 9, OCI_DEFAULT),2);

  checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 1, 
                               OCI_FETCH_LAST, (sb4) 0, OCI_DEFAULT),1);



  checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 1,
                               OCI_FETCH_LAST, (sb4) 0, OCI_DEFAULT),1);

  checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 1,
                               OCI_FETCH_FIRST, (sb4) 0, OCI_DEFAULT),1);

  checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 1, 
                               OCI_FETCH_ABSOLUTE, (sb4) 15, OCI_DEFAULT),1);

  checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 1,
                               OCI_FETCH_NEXT, (sb4) 0, OCI_DEFAULT),2);

  //checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 1,
   //                            OCI_FETCH_PRIOR, (sb4) 0, OCI_DEFAULT),1);
  //
 // checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 3,
  //                             OCI_FETCH_RELATIVE, (sb4) -2, OCI_DEFAULT),3);
  
  checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 1, 
                               OCI_FETCH_NEXT, (sb4) 0, OCI_DEFAULT),2);

  checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 1, 
                               OCI_FETCH_FIRST, (sb4) 0, OCI_DEFAULT),1);

  /* cancel the statement handle - and free resources on client and server */
//  checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 0,
  //                             OCI_FETCH_NEXT, (sb4) 0, OCI_DEFAULT),0);
 //
  /* this should result in some error */
  //checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 1,
 //                               OCI_FETCH_NEXT, (sb4) 0, OCI_DEFAULT),1);

  /* re-execute in the non-scrollable mode */
 // checkprint(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 3, (ub4)
     //                            0,  (CONST OCISnapshot *)
     //                            NULL, (OCISnapshot *) NULL,
     //                            0),3);

  /* this should result in error */
 // checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 1,
  //                              OCI_FETCH_ABSOLUTE, (sb4) 4, OCI_DEFAULT),1);
  cleanup() ;

}
//---------------------------------------------------------------------------