Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Using LOBs for retrieval of data !!

Re: Using LOBs for retrieval of data !!

From: DriftWood <drift_wood_at_my-deja.com>
Date: Wed, 15 Mar 2000 14:26:44 GMT
Message-ID: <8ao6iu$lak$1@nnrp1.deja.com> 





Here is a BLOB sample, the CLOB isn't really any different other than
the data you will manipulate.


/*******************************************************************


Step 1:




Use the following SQL to create the required table  on the database:


        CREATE TABLE MY_BLOB_TABLE (C1 NUMBER(4), C2 BLOB);


Step 2:




Change filenames, LIBs and prototype before compiling.



Step 3:




Compile and Execute code.



Step 4:




Verify size of BLOB on the database with the following procedure:



SET SERVEROUTPUT ON


CREATE OR REPLACE PROCEDURE Blob_Size (myC1 number)
 IS


    b_LOB1 BLOB;


    bSize NUMBER;


 BEGIN



    SELECT c2 INTO b_LOB1 FROM MY_BLOB_TABLE WHERE C1= myC1;
    bSize := DBMS_LOB.GetLength(b_LOB1);
    DBMS_OUTPUT.PUT_LINE(bSize);


  END;



/



EXECUTE Blob_Size(102);






To simplify the code and limit the number of lines:

	Error checking is minimal
	Hard-coded values are used to exclude using bind variables
	Hard-coded values are used for input/output filenames
	Input Variables are reused for Output

Suggested modifications:
	Pass the following in as command line arguments:
		Field C1 value
		Input filename
		Output filename





Don't forget to change the input/output filenames!!!!!



After each execution of the code, you should delete the record just
entered. This will ensure that you are fetching the correct BLOB data.

*******************************************************************/



#include <stdio.h>


#include <io.h>


#include <stdlib.h>




#include <string.h>


#include <oci.h>





static void checkerr(OCIError *p_err, sword status);



#define DATA_SIZE 32000





void main()


{

OCIEnv			*p_env;
OCIError		*p_err;
OCISvcCtx		*p_svc;
OCIStmt			*p_sql;
OCILobLocator	*blob;
OCILobLocator	*blobget;
OCIDefine		*p_Define = (OCIDefine *) 0;


FILE *input, *output;



ub4 fsize;


sword result;


char *data_in, *data_out;


ub4 remainingSize = 0;


ub4 amtp;


int numwritten =0;


int numread = 0;


text *mySql = (text *) "Insert into My_Blob_Table values (102,
EMPTY_BLOB())";



/********************************************************************/
/*          Initalize and Allocate Handles/Descriptors              */
/********************************************************************/


printf("OCIInitialize\n");


checkerr(p_err, OCIInitialize((ub4) OCI_OBJECT, (dvoid *) 0,


                 (dvoid * (*) ()) 0, (dvoid * (*) ()) 0, (void (*) ())
0));



printf("	OCIEnvInit \n");


checkerr(p_err, OCIEnvInit(&p_env, (ub4) OCI_DEFAULT, (size_t) 0,
(dvoid **)0));



printf("	OCIHandleAlloc error\n");


checkerr(p_err, OCIHandleAlloc(p_env, &p_err, OCI_HTYPE_ERROR, (size_t)
0, (dvoid **) 0));



printf("	OCIHandleAlloc server\n");


checkerr(p_err, OCIHandleAlloc(p_env, &p_svc, OCI_HTYPE_SVCCTX,
(size_t) 0, (dvoid **) 0));



printf("	OCIHandleAlloc statement\n");


checkerr(p_err, OCIHandleAlloc(p_env, &p_sql, OCI_HTYPE_STMT, (size_t)
0, (dvoid **) 0));



printf("	OCIDescriptorAlloc Blob\n");


checkerr(p_err, OCIDescriptorAlloc(p_env, &blob, OCI_DTYPE_LOB,
(size_t) 0, (dvoid **) 0));


checkerr(p_err, OCIDescriptorAlloc(p_env, &blobget, OCI_DTYPE_LOB,
(size_t) 0, (dvoid **) 0));


/********************************************************************/
/*              Insert a record and Initalize the BLOB              */
/********************************************************************/
printf("	OCILogon\n");




checkerr(p_err, OCILogon(p_env, p_err, &p_svc, "SCOTT", 5, "TIGER",
5, "V8", 2));



printf("	OCIStmtPrepare\n");


checkerr(p_err, OCIStmtPrepare(p_sql, p_err, mySql, (ub4) strlen
(mySql),


                 OCI_NTV_SYNTAX, OCI_DEFAULT));


printf("Inserting the record...\n");


printf("	OCIStmtExecute\n\n");


checkerr(p_err, OCIStmtExecute(p_svc, p_sql, p_err, (ub4) 1, (ub4) 0,
(OCISnapshot *)


                          NULL, (OCISnapshot *) NULL, (ub4)


OCI_COMMIT_ON_SUCCESS));



/********************************************************************/
/*              Select For Update to Insert the BLOB                */
/********************************************************************/


mySql = (text *) "Select C2 From MY_BLOB_TABLE Where C1 = 102 For
Update";



printf("Preparing the Update...\n");


printf("	OCIStmtPrepare\n");


checkerr(p_err, OCIStmtPrepare(p_sql, p_err, mySql, (ub4) strlen
(mySql),


                 OCI_NTV_SYNTAX, OCI_DEFAULT));


printf("	OCIDefineByPos\n");


checkerr(p_err, OCIDefineByPos(p_sql, &p_Define, p_err, 1, (dvoid *)
&blob, (sb4) -1,


                        (ub2) SQLT_BLOB, 0, 0, 0, OCI_DEFAULT));



printf("	OCIStmtExecute\n\n");


result = OCIStmtExecute(p_svc, p_sql, p_err, (ub4) 1, (ub4) 0,
(OCISnapshot *) NULL,


                                                (OCISnapshot *) NULL,


(ub4) OCI_DEFAULT);


/********************************************************************/
/*              Start Inserting the BLOB to the database            */
/********************************************************************/
if( (input = fopen("f:\\Piegates.exe","rb")) == NULL )
	{
    printf( "The file failed to open\n" );
	exit(0);
	}
fsize = filelength(fileno(input));		//get total file size
printf("	File Size --> %25d\n", fsize);
data_in = (char *)malloc(DATA_SIZE);	//initial memory
memset(data_in, 0, DATA_SIZE);
numread = fread(data_in,  1, DATA_SIZE, input);	//get first chunk
printf("	Number read = %25d\n", numread);


remainingSize = fsize;


amtp = fsize;


/* Processes the First Piece */


result = OCILobWrite(p_svc, p_err, blob,

				&amtp, (ub4) 1, data_in,
				(DATA_SIZE > fsize) ? fsize :DATA_SIZE,
				(DATA_SIZE > fsize) ? OCI_ONE_PIECE :
OCI_FIRST_PIECE,
				(dvoid *) 0,
				NULL, (ub2) 0, (ub1) 0);
if (result == OCI_NEED_DATA)
	{
	remainingSize -= DATA_SIZE;
    while (remainingSize > DATA_SIZE)
		{
		memset(data_in, 0, DATA_SIZE);
		numread = fread(data_in,  1, DATA_SIZE, input); //get
next chunk
		printf("	Number read = %25d\n", numread);
		if ((result = OCILobWrite(p_svc, p_err, blob,
						&amtp, (ub4) 1,
data_in,
						DATA_SIZE,
						OCI_NEXT_PIECE,
						(dvoid *) 0,
						NULL, (ub2) 0, (ub1)
0)) != OCI_NEED_DATA)
			{
			checkerr(p_err, result);
			}
		remainingSize -= DATA_SIZE;
		}
    /* Processes the Last Piece */
	memset(data_in, 0, DATA_SIZE);
	numread = fread(data_in,  1, remainingSize, input);
	printf("	Number read = %25d\n", numread);
	checkerr(p_err, OCILobWrite(p_svc, p_err, blob,
						&amtp, (ub4) 1,
data_in,
						remainingSize,
						OCI_LAST_PIECE,
						(dvoid *) 0,
						NULL, (ub2) 0, (ub1)
0));
	fsize = -1;
	checkerr(p_err, OCILobGetLength(p_svc, p_err, blob, &fsize));
	printf("\n\n%25d bytes were written to the database.\n\n\n",
fsize);
	}
else if (result != OCI_SUCCESS)
	{
	printf("You got an error\n");
	checkerr(p_err, result);
	}




fclose(input);


/********************************************************************/
/*               End Inserting the BLOB to the database             */
/********************************************************************/

/********************************************************************/
/*              Start Fetching the BLOB from the database           */
/********************************************************************/


mySql = (text *) "Select C2 From MY_BLOB_TABLE Where C1 = 102";



printf("Preparing the Update...\n");


printf("	OCIStmtPrepare\n");


checkerr(p_err, OCIStmtPrepare(p_sql, p_err, mySql, (ub4) strlen
(mySql),


                 OCI_NTV_SYNTAX, OCI_DEFAULT));


printf("	OCIDefineByPos\n");


checkerr(p_err, OCIDefineByPos(p_sql, &p_Define, p_err, 1, (dvoid *)
&blobget, (sb4) -1,


                        (ub2) SQLT_BLOB, 0, 0, 0, OCI_DEFAULT));



printf("	OCIStmtExecute\n");


result = OCIStmtExecute(p_svc, p_sql, p_err, (ub4) 1, (ub4) 0,
(OCISnapshot *) NULL,


                                                (OCISnapshot *) NULL,


(ub4) OCI_DEFAULT);



printf("	Fetching BLOB from the database\n");
amtp=0;


fsize=0;


remainingSize = 0;


checkerr(p_err, OCILobGetLength(p_svc, p_err, blobget, &fsize));



if( (output = fopen("f:\\piegates2.exe","wb")) == NULL )


        {


    printf( "File failed to open\n" );

	exit(0);
	}
amtp = fsize;
printf("	Number of bytes to fetch %25d\n", fsize);


data_out = (char *)malloc(DATA_SIZE);


memset(data_out, 0, DATA_SIZE);


remainingSize = fsize;


amtp=0;


result = OCILobRead(p_svc, p_err, blobget,

					&amtp, (ub4) 1, data_out,
					(fsize < DATA_SIZE) ? fsize :
DATA_SIZE,
					(dvoid *)0,
					NULL, (ub2) 0, (ub1)




SQLCS_IMPLICIT);


numwritten = fwrite(data_out, sizeof(ub1), ((DATA_SIZE > fsize) ?
fsize : DATA_SIZE), output);



printf ("	Number written = %25d\n", numwritten );
if (result == OCI_NEED_DATA)

	{
	remainingSize -= DATA_SIZE;
    while (remainingSize > DATA_SIZE)
		{
		memset(data_out, 0, DATA_SIZE);
		if ((result = OCILobRead(p_svc, p_err, blobget,
							&amtp, (ub4) 1,
data_out,
							DATA_SIZE,
							OCI_NEXT_PIECE,
							(dvoid *) 0,
							NULL, (ub2) 0,
(ub1) 0)) != OCI_NEED_DATA)
			{
			checkerr(p_err, result);
			}
		numwritten = fwrite(data_out,  1, DATA_SIZE,
output); //get first chunk
		printf ("	Number written = %25d\n", numwritten );
		remainingSize -= DATA_SIZE;
		}
	memset(data_out, 0, DATA_SIZE);
	checkerr(p_err, OCILobRead(p_svc, p_err, blobget,
						&amtp, (ub4) 1,
data_out,
						remainingSize,
						OCI_LAST_PIECE,
						(dvoid *) 0,
						NULL, (ub2) 0, (ub1)
0));
	numwritten = fwrite(data_out,  1, remainingSize, output);
	printf ("	Number written = %25d\n", numwritten );
	}
else if (result != OCI_SUCCESS)
	{
	printf("You got an error\n");
	checkerr(p_err, result);
	}




fclose(output);

/********************************************************************/
/*               End Fetching the BLOB from the database            */
/********************************************************************/


printf("\n\nCheck your file to verify the download was successful\n");



/*This is very import*/


(void) OCITransCommit(p_svc, p_err, (ub4)0);



return;


}



static void checkerr(errhp, status)


OCIError *errhp;


sword status;


{


  text errbuf[512];


  ub4 errcode;



  switch (status)


  {


  case OCI_SUCCESS:


    break;


  case OCI_SUCCESS_WITH_INFO:


    printf("Error - OCI_SUCCESS_WITH_INFO\n");
    break;


  case OCI_NEED_DATA:


    printf("Error - OCI_NEED_DATA\n");


    break;


  case OCI_NO_DATA:


    printf("Error - OCI_NO_DATA\n");


    break;


  case OCI_ERROR:


    OCIErrorGet ((dvoid *) errhp, (ub4) 1, (text *) NULL, &errcode,


            errbuf, (ub4) sizeof(errbuf), (ub4) OCI_HTYPE_ERROR);
    printf("Error - %25s\n", errbuf);


        exit(0);


    break;


  case OCI_INVALID_HANDLE:


    printf("Error - OCI_INVALID_HANDLE\n");


        exit(0);


    break;


  case OCI_STILL_EXECUTING:


    printf("Error - OCI_STILL_EXECUTE\n");
    break;


  case OCI_CONTINUE:


    printf("Error - OCI_CONTINUE\n");


    break;


  default:


    break;


  }


}




Sent via Deja.com http://www.deja.com/



Before you buy.
Received on Wed Mar 15 2000 - 08:26:44 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US