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: OCI/Blobs/Oracle 8 oddness

Re: OCI/Blobs/Oracle 8 oddness

From: mark tomlinson <marktoml_at_gdi.net>
Date: 1998/04/29
Message-ID: <354a6a27.421724738@newshost.us.oracle.com>

Here you go...(and yes it is a bit silly);

/*******************************************************************
Use the following SQL to create the required table  on the database:
	CREATE TABLE MY_BLOB_TABLE (C1 NUMBER(4), C2 BLOB);

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>

#define DATA_SIZE 5000

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

void main()
{

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

FILE *input, *output;

ub4 fsize;
sword result;
char *data_in, *data_out;
ub4 remainingSize = 0;
ub4 amtp;
int n;
int lineSpaces = 0;

text *mySql = (text *) "Insert into My_Blob_Table values (1, 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));

/********************************************************************/
/* 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");
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 = 1 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");
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("d:\\head.bmp","rb")) == NULL ) if( (input = fopen("d:\\body.txt","rb")) == NULL ) { printf( "The file failed to open\n" ); exit(0); } fsize = filelength(fileno(input)); //get total file size printf("The total size fo the file is: %d\n", fsize); data_in = (char *)malloc(DATA_SIZE); //initial memory memset(data_in, 0, DATA_SIZE); fread(data_in, 1, DATA_SIZE, input); //get first chunk
remainingSize = fsize;
amtp = fsize;
/* The next few lines created a simulated progress bar */ lineSpaces = fsize/DATA_SIZE;
if (lineSpaces > 12)
	{
	printf("Start");
	for (n = 5;n < (lineSpaces - 5); n++)
		{
		printf("-");
		}
	printf("Finish\n");
	putchar(1);
	}

/* 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);
		fread(data_in,  1, DATA_SIZE, input); //get next chunk
		if (lineSpaces > 12) 
			putchar(1);//Displays progress
		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);
	fread(data_in,  1, remainingSize, input); 
	if (lineSpaces > 12)
		putchar(1); //Displays progress
	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%d 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 */
/********************************************************************/
printf("Fetching BLOB from the database\n"); checkerr(p_err, OCILobGetLength(p_svc, p_err, blob, &fsize)); //if( (output = fopen("f:\\DatabaseOut.bmp","wb")) == NULL ) if( (output = fopen("f:\\body.txt","wb")) == NULL )

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

	exit(0);
	}
amtp = fsize;
printf("	%d bytes will should be fetched\n", fsize);
data_out = (char *)malloc(DATA_SIZE);
memset(data_out, 0, DATA_SIZE);
remainingSize = fsize;
lineSpaces = fsize/DATA_SIZE;
/* The next few lines created a simulated progress bar */ if (lineSpaces > 12)
	{
	printf("Start");
	for (n = 5;n < (lineSpaces - 5); n++)
		{
		printf("-");
		}
	printf("Finish\n");
	putchar(1);
	}
result = OCILobRead(p_svc, p_err, blob, 
					&amtp, (ub4) 1, data_out, 
					(DATA_SIZE > fsize) ? fsize
:DATA_SIZE, 
					(DATA_SIZE > fsize) ?
OCI_ONE_PIECE : OCI_FIRST_PIECE,
					(dvoid *) 0, 
					NULL, (ub2) 0, (ub1) 0);

fwrite(data_out, ((DATA_SIZE > fsize) ? DATA_SIZE : fsize), 1, output);
if (result == OCI_NEED_DATA)
	{
	remainingSize -= DATA_SIZE;
    while (remainingSize > DATA_SIZE)
		{
		memset(data_out, 0, DATA_SIZE);
		if (lineSpaces > 12)
			putchar(1); //Displays progress
		if ((result = OCILobRead(p_svc, p_err, blob, 
							&amtp, (ub4)
1, data_out, 
							DATA_SIZE, 

OCI_NEXT_PIECE,
							(dvoid *) 0, 
							NULL, (ub2) 0,
(ub1) 0)) != OCI_NEED_DATA)
			{
			checkerr(p_err, result);
			}
		fwrite(data_out,  DATA_SIZE, 1, output); //get first
chunk
		remainingSize -= DATA_SIZE;
		}
	//printf("OCILobWrite OCI_LAST_PIECE\n");
	memset(data_out, 0, DATA_SIZE);
	if (lineSpaces > 12)
		putchar(1);//Displays progress
	checkerr(p_err, OCILobRead(p_svc, p_err, blob, 
						&amtp, (ub4) 1,
data_out, 
						remainingSize, 
						OCI_LAST_PIECE,
						(dvoid *) 0, 
						NULL, (ub2) 0, (ub1)
0));
	fwrite(data_out,  remainingSize, 1, output); 
	}
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"); 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 - %s\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;
  }
} Received on Wed Apr 29 1998 - 00:00:00 CDT

Original text of this message

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