Re: Handling LONG through Pro*C

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 27 May 1998 00:44:28 GMT
Message-ID: <356c6058.5206837@192.86.155.100>

A copy of this was sent to "Alan Pollard" <alanpollard_at_yahoo.com> (if that email address didn't require changing) On 26 May 1998 19:24:49 GMT, you wrote:

>Using Pro*C, I need to process a table containing comms messages, each of
>which has a NUMBER for the length and the message in a LONG.
>Anyone have any elegant/efficient solutions for retrieving this data ?
>I'm using Pro*C 2.2.2 and Oracle 7.3.2.

I don't know if its 'elegant' but it works. The following uses the fact that you can mix pro*c and oci. Using OCI we can piecewise fetch without knowing the length of the long beforehand.

what I do is select out the ROWID of the long field instead of the LONG field itself. Then I call getlong with the rowid and a query to retrieve it, getlong returns the long field in a structure similar to a VARCHAR (leading length and then the data).

You might use it like this ( create table image( name varchar2(255), img_size number, mime_type varchar2(255), image long raw ) )....

...
EXEC SQL DECLARE C1 CURSOR FOR
    SELECT NAME, MIME_TYPE, IMG_SIZE, ROWID

      FROM IMAGE
     WHERE ROWNUM < 10;

    EXEC SQL OPEN C1;
    for(;;)
    {

        EXEC SQL WHENEVER NOTFOUND DO break;
        EXEC SQL FETCH C1 INTO :name, :mime_type, :img_size, :rowid;

        longptr = get_long( "select image from image where rowid = :rid",
                             rowid, 1 );

        if ( longptr == NULL )
        {
            printf( "NULL ptr returned (%s)\n", get_long_emsg() );
            exit(0);
        }
        printf( "longptr is OK, size = %d\n", longptr->len );

        free( longptr );

    }

    EXEC SQL CLOSE C1;

---

Here is the code for getlong:

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

#ifndef ORATYPES_ORACLE

# include <oratypes.h>
#endif

/* LDA and CDA struct declarations */

#include <ocidfn.h>
#ifdef __STDC__
#include <ociapr.h>
#else
#include <ocikpr.h>
#endif
#include "ocidem.h"

#include "getlong.h"

/* contents of getlong.h -------------------------------------

typedef struct TAGmy_long
{

        long                    len;
        unsigned char   arr[1];
}
        my_long;

my_long * get_long( char * sql_statement, char * rowid, int isRaw );

char * get_long_emsg(void);

int get_long_errcd(void);

----------------------------------------- getlong.h    */

static int firstTime = 1;
static Lda_Def lda;
static Cda_Def cda;

my_long * get_long( char * sql_statement, char * rowid, int isRaw ) {

ub1       ucp[32765];
int       UCP_SIZE = sizeof(ucp);
sb2       indp = 0;
ub4       ret_len = 0;
ub2       retl = 0;
ub2       rcode = 0;
int       fetched = 0;
int       rc = 0;

my_long  * returnBuffer = NULL;
int              returnBufferLen = 0;

   if ( firstTime )
   {

      sqllda( &lda );
      rc = oopen( &cda, &lda, NULL, -1, -1, NULL, -1 );
      if ( rc ) return NULL;
      firstTime = 0;

   }

   if (oparse(&cda, sql_statement, -1, 0, 2)) return NULL;

   if ( obndrv( &cda, "RID", -1, rowid, strlen(rowid)+2,

               STRING_TYPE, -1, 0, 0, -1, -1) ) return NULL;

    if (odefin(&cda, 1, ucp, UCP_SIZE, isRaw ? 24 : 8, -1,

               &indp, (text *) 0, 0, -1, &retl, &rcode)) return NULL;

    for( rc = oexfet(&cda, (ub4) 1, 0, 0); !rc; rc = ofetch(&cda) )     {

      for( fetched = 0, ret_len = 1; ret_len; fetched += ret_len )
      {
           if (oflng(&cda, 1, ucp, UCP_SIZE,
                        isRaw ? 24 : 8, &ret_len, fetched )) return NULL;
           if ( ret_len )
           {
               if ( returnBufferLen ) 
               {
                  returnBuffer = 
                     (my_long*)realloc(returnBuffer, returnBufferLen+
                                               UCP_SIZE+1+sizeof(my_long) );
               }
               else
               {
                  returnBuffer = (my_long*)malloc( UCP_SIZE+1+sizeof(my_long) );
                  memset( returnBuffer, 0, UCP_SIZE+1+sizeof(my_long) );
               }
               if ( !returnBuffer ) return NULL;\
               memmove( returnBuffer->arr+returnBufferLen, ucp, ret_len );
               returnBufferLen += UCP_SIZE;
               returnBuffer->len += ret_len;
               returnBuffer->arr[returnBufferLen] = 0;
          }
      }

   }
   return returnBuffer;
}

char * get_long_emsg(void)
{
static char msg[1024];  

   oerhms( &lda, cda.rc, msg, sizeof(msg) );    return msg;
}

int get_long_errcd(void)
{

   return cda.rc;
}  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue May 26 1998 - 19:44:28 CDT