ocidbclass.cpp

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

    break;
     case 14,15:StrCopy(FSQLType,"SQL_EXPLAIN") ;
    break;
     default: StrCopy(FSQLType,"SQL_UNKNOWN");
  }

  if (stricmp(FSQLType,"SQL_SELECT")==0){
      selectinfo=&((*(OCI_CURSOR*)sel_cursor).selinfo) ;
      strcat(SQLStr," for update");
     

  }
  if (stricmp(FSQLType,"SQL_SELECT")!=0)
  {
      status = OCIStmtPrepare( stmthp1, errhp,
				   (text*)SQLStr,(ub4)strlen(SQLStr),
				   (ub4)OCI_NTV_SYNTAX,(ub4)OCI_DEFAULT );

       if( status == OCI_ERROR )
       {
         _OCIErrorGet( DBHandle,tmpbuf );
         strcpy( tmpname, "OCIStmtPrepare()" );
         sprintf( DBErrMsg,"=>调用 %s 错误(%s)",tmpname,tmpbuf);
         throw Exception(DBErrMsg);
         //return status;
       }
  }


  return status;

}
//-----------------------------------------------------------------------------
int TOCIQuery::ExecSQL()
{


  text	    * colname;
  status=0;


  status=BINParamValue();
  if (status!=0)
  {
   throw Exception(DBErrMsg);
   //return status;
  }
  Eof=true;

  if (stricmp(FSQLType,"SQL_UPDATE")==0)
  {
    OCIAttrGet(svchp, (ub4) OCI_HTYPE_STMT, (void *) & RowCount, NULL, OCI_ATTR_ROW_COUNT, errhp) ;
  }

  if (stricmp(FSQLType,"SQL_SELECT")!=0)
  {

       status = OCIStmtExecute( svchp, stmthp1, errhp, (ub4)1, (ub4)
                                 0,  (CONST OCISnapshot *)
                                 NULL, (OCISnapshot *) NULL,
                                 OCI_DEFAULT );
       if( status != OCI_SUCCESS )
       {
          _OCIErrorGet( DBHandle,tmpbuf );
          strcpy( tmpname, "OCIStmtExecute()" );
          sprintf( DBErrMsg,"=>调用 %s 错误(%s)",tmpname,tmpbuf);
          if( stmthp1 != NULL )
		OCIHandleFree( stmthp1 , OCI_HTYPE_STMT );
          throw Exception(DBErrMsg);
          //goto ret;
       }
        int i;
        for (i=0; i<csCount ;i++)
        {
          if (PARAStruct[i].sybz==true && PARAStruct[i].CStype==SQLT_BLOB)
          {
            if (File_write_lob( PARAStruct[i].CSblob, PARAStruct[i].CFilename)==-1)
            {
               if( stmthp1 != NULL )
		OCIHandleFree( stmthp1 , OCI_HTYPE_STMT );
              throw Exception(DBErrMsg);

            //  goto ret;
            }
          }

        }

       OCIAttrGet(svchp, (ub4) OCI_HTYPE_STMT, (void *) & RowCount, NULL, (ub4)OCI_ATTR_PARAM_COUNT, errhp) ;
       OCITransCommit(svchp, errhp, (ub4)0);
  }

  if (stricmp(FSQLType,"SQL_SELECT")==0)
  {
     OCIAttrSet((dvoid *) selecthp, (ub4) OCI_HTYPE_STMT,
                 (dvoid *) & prefetch, 0,
                 (ub4) OCI_ATTR_PREFETCH_ROWS, errhp);
     status =SelectPrepare();
     if( status < 0 )
     {
       goto ret;
     }
     status = OCIStmtExecute( svchp, selecthp, errhp, (ub4)0, (ub4)
                                 0,  (CONST OCISnapshot *)
                                 NULL, (OCISnapshot *) NULL,
                                 OCI_DEFAULT );
     if( status != OCI_SUCCESS )
     {
          _OCIErrorGet( DBHandle,tmpbuf );
          strcpy( tmpname, "OCIStmtExecute()" );
          sprintf( DBErrMsg,"=>调用 %s 错误(%s)",tmpname,tmpbuf);
           if( stmthp1 != NULL )
		OCIHandleFree( stmthp1 , OCI_HTYPE_STMT );
          throw Exception(DBErrMsg);
         // throw Exception(DBErrMsg);
          //goto ret;
     }
     Eof=false;
     Next();
  }

ret:
  if( stmthp1 != NULL )
		OCIHandleFree( stmthp1 , OCI_HTYPE_STMT );
  if (csCount>0)
    for (int h=0;h<100 ;h++)
  {
    PARAStruct[h].CSname="";
    PARAStruct[h].CSbndhp=(OCIBind *) 0;
    PARAStruct[h].CSvalue=NULL;
    PARAStruct[h].sybz=false;
    PARAStruct[h].CStype=-1;
    if (PARAStruct[h].CSblob!=NULL)
    OCIDescriptorFree( (dvoid*)(PARAStruct[h].CSblob), (ub4) OCI_DTYPE_LOB );
    PARAStruct[h].CSblob=NULL;
  }

  return status;

}

//---------------------------------------------------------------------------
int TOCIQuery::Next()
{
	if( (*(OCI_CURSOR*)sel_cursor).selinfo.outputnums != 0 && (*(OCI_CURSOR*)sel_cursor).selinfo.outputinfo != NULL )
	{

		for( int j = 0 ; j < (*(OCI_CURSOR*)sel_cursor).selinfo.outputnums ; j ++ )
		{
                  if ((*(OCI_CURSOR*)sel_cursor).selinfo.outputinfo[j].Value!=NULL)
                   memset((*(OCI_CURSOR*)sel_cursor).selinfo.outputinfo[j].Value,0,sizeof((*(OCI_CURSOR*)sel_cursor).selinfo.outputinfo[j].Value));

		}

	}
       //(*(OCI_CURSOR*)sel_cursor).selinfo.outputnums=0;

    if (Eof==true)
    {
       throw Exception("已到记录尾！");
    }
    int  status1=0,errcode1;
    status1 = OCIStmtFetch2( selecthp,errhp,(ub4) 1,
                               OCI_FETCH_NEXT, (sb4) 0, OCI_DEFAULT);
    //status1 = OCIStmtFetch( selecthp,errhp,1,OCI_FETCH_NEXT,OCI_DEFAULT);
    if( status1 == OCI_ERROR || status1 == 100 )
    {
      Eof=true;
      return -5 ;

    }
    // FreeSelectout( &((*(OCI_CURSOR*)sel_cursor).selinfo));
    return status1;
}
int TOCIQuery::First()
{
    int  status1=0,errcode1;

    status1 = OCIStmtFetch( (*(OCI_CURSOR*)sel_cursor).selhp,(*(OCI_CURSOR*)sel_cursor).DBHandle.errhp,1,OCI_FETCH_FIRST,OCI_DEFAULT);
    if( status1 == OCI_ERROR )
    {
      return -5 ;
    }

    return status1;
}
int TOCIQuery::Prior()
{
    int  status1=0,errcode1;

    status1 = OCIStmtFetch2( (*(OCI_CURSOR*)sel_cursor).selhp,(*(OCI_CURSOR*)sel_cursor).DBHandle.errhp,(ub4)1,OCI_FETCH_PRIOR,(sb4)0,OCI_DEFAULT);
    if( status1 == OCI_ERROR )
    {
      return -5 ;
    }

    return status1;
}
int TOCIQuery::Last()
{
  int status1=0,errcode1;
    status1= OCIStmtFetch2( (*(OCI_CURSOR*)sel_cursor).selhp,(*(OCI_CURSOR*)sel_cursor).DBHandle.errhp,(ub4)1,OCI_FETCH_LAST,(sb4)0,OCI_DEFAULT);
   // status1 = OCIStmtFetch( (*(OCI_CURSOR*)&sel_cursor).selhp,(*(OCI_CURSOR*)&sel_cursor).DBHandle.errhp,1,OCI_FETCH_LAST,OCI_DEFAULT);
    if( status1== OCI_ERROR )
    {
      return -5 ;
    }

    return status1;
}
ub4 file_length(FILE *fp)
{
  fseek(fp, 0, SEEK_END);
  return (ub4) (ftell(fp));
}
//---------------------------------------------------------------------------
int TOCIQuery::File_write_lob( OCILobLocator *lob2, char* FileName)
{
   FILE *fp;
   ub4  filelen = 0;
  fp = fopen(FileName, (const char *) "rb");


  if (!fp)
  {
    sprintf( DBErrMsg,"BLOB流未发现！");
    return -1;
  }
  filelen = file_length(fp);
  ub4   offset = 1;
  ub4   loblen = 0;
  ub1   bufp[MAXBUFLEN];
  ub4   amtp = filelen;
  ub1   piece;
  sword retval;
  int   readval;
  ub4   nbytes;
  ub4   remainder = filelen;
  OCILobGetLength(svchp, errhp, lob2, &loblen);
  fseek(fp, 0, 0);

  if (filelen > MAXBUFLEN)
    nbytes = MAXBUFLEN;
  else
    nbytes = filelen;

  if (fread((void *)bufp, (size_t)nbytes, 1, fp) != 1)
  {
    fclose(fp);
    sprintf( DBErrMsg,"BLOB流读错误！");
    return -1;
  }

  remainder -= nbytes;

  if (remainder == 0)
  {
    retval = OCILobWrite(svchp, errhp, lob2, &amtp, offset, (dvoid *) bufp,
                            (ub4) nbytes, OCI_ONE_PIECE, (dvoid *)0,
                            (sb4 (*)(dvoid *, dvoid *, ub4 *, ub1 *)) 0,
                            (ub2) 0, (ub1) SQLCS_IMPLICIT);
    if ( retval!= OCI_SUCCESS)
    {
      fclose(fp);
      sprintf( DBErrMsg,"BLOB流写错误！");
      return -1;
    }
  }
  else
  {
    if (OCILobWrite(svchp, errhp, lob2, &amtp, offset, (dvoid *) bufp,
                    (ub4) MAXBUFLEN, OCI_FIRST_PIECE, (dvoid *)0,
                    (sb4 (*)(dvoid *, dvoid *, ub4 *, ub1 *)) 0,
                    (ub2) 0, (ub1) SQLCS_IMPLICIT) != OCI_NEED_DATA)
    {
      sprintf( DBErrMsg,"BLOB流写错误！");
      fclose(fp);
      return -1;
    }

    piece = OCI_NEXT_PIECE;

    do
    {
      if (remainder > MAXBUFLEN)
        nbytes = MAXBUFLEN;
      else
      {
        nbytes = remainder;
        piece = OCI_LAST_PIECE;
      }

      if (fread((void *)bufp, (size_t)nbytes, 1, fp) != 1)
      {
        sprintf( DBErrMsg,"BLOB流读错误！");
        piece = OCI_LAST_PIECE;
      }

      retval = OCILobWrite(svchp, errhp, lob2, &amtp, offset, (dvoid *) bufp,
                          (ub4) nbytes, piece, (dvoid *)0,
                          (sb4 (*)(dvoid *, dvoid *, ub4 *, ub1 *)) 0,
                          (ub2) 0, (ub1) SQLCS_IMPLICIT);
      remainder -= nbytes;

    } while (retval == OCI_NEED_DATA && !feof(fp));
  }

  if (retval != OCI_SUCCESS)
  {
    sprintf( DBErrMsg,"BLOB流写错误！");
    fclose(fp);

    return -1;
  }

  (void) OCILobGetLength(svchp, errhp, lob2, &loblen);
   OCILobFlushBuffer(svchp, errhp, lob2, OCI_LOB_BUFFER_NOFREE);
 fclose(fp);

  return 0;
}

void TOCIQuery::stream_write_lob( OCILobLocator *lob2, void* pData, int nLen)
{
  if (OCILobWrite(svchp, errhp, lob2, (unsigned int*)&nLen, 1,
                    (dvoid *) pData, (ub4) nLen, OCI_ONE_PIECE, (dvoid *)0,
                    (sb4 (*)(dvoid *, dvoid *, ub4 *, ub1 *)) 0,
                    (ub2) 0, (ub1) SQLCS_IMPLICIT)!= OCI_NEED_DATA)
  {
    printf("ERROR: OCILobWrite()\n");
    return ;
  }
  OCILobFlushBuffer(svchp, errhp, lob2, OCI_LOB_BUFFER_NOFREE);
  return;
}


int TOCIQuery::GetBLOBFieldByIndexToFile(int idx,char FileName[600])
{
  char erros[200]="";
  if (idx >= (*(OCI_CURSOR*)sel_cursor).selinfo.outputnums)
  {

    StrCat(erros, "字段号:[");
    StrCat(erros, IntToStr(idx).c_str());
    StrCat(erros, "]不存在！");
    throw Exception(erros);
   // return -1;
  }
  if( (*(OCI_CURSOR*)sel_cursor).selinfo.outputinfo[idx].Type != OCI_TYPECODE_BLOB )
  {
    StrCat(erros, "字段号:[");
    StrCat(erros, IntToStr(idx).c_str());
    StrCat(erros, "]为非BLOB字段不适合此函数取值！");
    throw Exception(erros);
    //return -1;
  }
  ub4             blobdatalen;
  ub4             amtp;
  status = OCILobGetLength( DBHandle.svchp, DBHandle.errhp, (*(OCI_CURSOR*)sel_cursor).selinfo.outputinfo[idx].blob, (ub4*)&blobdatalen );
  if( blobdatalen == 0 || status < 0 )
    return 1;
  (*(OCI_CURSOR*)sel_cursor).selinfo.outputinfo[idx].Value = (char*)malloc( blobdatalen );
  amtp = blobdatalen ;
  status = OCILobRead( DBHandle.svchp, DBHandle.errhp, (*(OCI_CURSOR*)sel_cursor).selinfo.outputinfo[idx].blob, &amtp,1,(*(OCI_CURSOR*)sel_cursor).selinfo.outputinfo[idx].Value,blobdatalen,0,0,0,(ub1) SQLCS_IMPLICIT );
  if( status != OCI_SUCCESS )
  {
     free( (*(OCI_CURSOR*)sel_cursor).selinfo.outputinfo[idx].Value );
     (*(OCI_CURSOR*)sel_cursor).selinfo.outputinfo[idx].Value = 0;
     throw Exception("BLOB字段读错误");
    // return -1;
  }

  FILE *fp;
  fp = fopen(FileName, "wb");
  fwrite((*(OCI_CURSOR*)sel_cursor).selinfo.outputinfo[idx].Value , blobdatalen , 1 , fp);
  fclose(fp);
  free( (*(OCI_CURSOR*)sel_cursor).selinfo.outputinfo[idx].Value );
  return 0;
}
int TOCIQuery::GetBLOBFieldByNameToFile(char FieldName[200],char FileName[600])
{
  char erros[200]="";
  for (int i=0;i<(*(OCI_CURSOR*)sel_cursor).selinfo.outputnums;i++)
  {
     if (stricmp((*(OCI_CURSOR*)sel_cursor).selinfo.outputinfo[i].FieldName,FieldName)==0)
     {
       if( (*(OCI_CURSOR*)sel_cursor).selinfo.outputinfo[i].Type == OCI_TYPECODE_BLOB )
       {
          ub4             blobdatalen;
          ub4             amtp;
          status = OCILobGetLength( DBHandle.svchp, DBHandle.errhp, (*(OCI_CURSOR*)sel_cursor).selinfo.outputinfo[i].blob, (ub4*)&blobdatalen );
          if( blobdatalen == 0 || status < 0 )
            return -1;
          (*(OCI_CURSOR*)sel_cursor).selinfo.outputinfo[i].Value = (char*)malloc( blobdatalen );
          amtp = blobdatalen ;
          status = OCILobRead( DBHandle.svchp, DBHandle.errhp, (*(OCI_CURSOR*)sel_cursor).selinfo.outputinfo[i].blob, &amtp,1,(*(OCI_CURSOR*)sel_cursor).selinfo.outputinfo[i].Value,blobdatalen,0,0,0,(ub1) SQLCS_IMPLICIT );
          if( status != OCI_SUCCESS )
          {
             free( (*(OCI_CURSOR*)sel_cursor).selinfo.outputinfo[i].Value );
             (*(OCI_CURSOR*)sel_cursor).selinfo.outputinfo[i].Value = 0;
             throw Exception("BLOB字段读错误");
             //return -1;
          }

          FILE *fp;
          fp = fopen(FileName, "wb");
          fwrite((*(OCI_CURSOR*)sel_cursor).selinfo.outputinfo[i].Value , blobdatalen , 1 , fp);
          fclose(fp);
          free( (*(OCI_CURSOR*)sel_cursor).selinfo.outputinfo[i].Value );
          return 0;
       }
       else
       {
         StrCat(erros, "字段名为：[");
         StrCat(erros, FieldName);
         StrCat(erros, "] 的字段为非BLOB字段不适合此函数取值！");
         throw Exception(erros);
         //return -1;
       }