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: Where is a newsgroup on OCI ?

Re: Where is a newsgroup on OCI ?

From: mark tomlinson <marktoml_at_gdi.net>
Date: Wed, 13 May 1998 14:48:20 GMT
Message-ID: <355fb22d.594897347@newshost.us.oracle.com> 





They aren't the same...


Here is a BLOB example:


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


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!!!!! I used 



PIEGATES.JPG - your choice may be different...




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 "d:\\orant\\oci80\\include\\oci.h"




/******************************************************************/
/*                  If Linking with version 8.0.3                 */
/******************************************************************/


//#pragma comment(lib, "d:\\orant\\oci80\\lib\\msvc\\ora803.lib")
//static void checkerr(ocierrh *p_err, sword status);


/******************************************************************/
/*                  If Linking with version 8.0.4                 */
/******************************************************************/




#pragma comment(lib, "d:\\orant\\oci80\\lib\\msvc\\oci.lib")


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.jpg","rb")) == NULL )
        {
    printf( "The file failed to open\n" );
        exit(0);
        }
fsize = filelength(fileno(input));              //get total file size
printf("        File Size --> %d\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 = %d\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 = %d\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 = %d\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%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           */
/********************************************************************/


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.jpg","wb")) == NULL )


        {


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

        exit(0);
        }
amtp = fsize;
printf("        Number of bytes to fetch %d\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 = %d\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 = %d\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 = %d\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 - %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 May 13 1998 - 09:48:20 CDT












Original text of this message













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