| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Hey Pro*C and OCI gurus
On Sat, 11 Oct 1997 18:50:41 +0100, Casper Thrane <ct_at_benau.dk> wrote:
>Can I put binary files(pictures) in my database, by using C programming? >If yes, is there a sample I can use.
I'm attaching below how to do it in pro*c, a mix of pro*c and oci, and I'll point to source code examples you can get from the web on doing it in oci.
oci will be a little more flexible, oci in version 7.1 and up allows for piecewise fetches (so you don't need to know ahead of time how much memory to pre-allocated to fetch into) and in 7.3 allows for piecewise inserts/updates as well (so you can load that 500meg file from windows and even read it back out).
The following pro*c sample shows how to insert/select back out a long raw of up to (in this example) 100,000 bytes. By changing the EXEC SQL TYPE statement you can make this as big as you want.
to get source code examples that show you how to piecewise fetch and piecewise insert under just oci, goto http://govt.us.oracle.com/ and follow the link to downloadable utilities. Find the OWA replacement cartridge and grab the source code for it. Once you get the source, you'll find 2 directories of interest: owarepl\src\load and owarepl\src\unload. One directory has a piecewise unloader (you give it a query and it unloads the data to the file system). The other loads a table (my IMAGE table) using piecewise inserts.
/*
typedef struct TAGmy_raw
{
long len; unsigned char arr[1]; } my_raw; /*
EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL TYPE my_raw IS LONG VARRAW(100000) REFERENCE;
my_raw * buffer;
EXEC SQL END DECLARE SECTION;
long size = 100000;
int i;
EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
printf( "EXEC SQL CREATE TABLE TEST_BLOB ( X LONG RAW );\n" );
EXEC SQL CREATE TABLE TEST_BLOB ( X LONG RAW );
/*
* we will allocate a little over 99k of space
*/
buffer = (my_raw *)malloc( size+sizeof(my_raw) );
/*
* fill it up with every possible byte value from 0..254
*/
for( i = 0; i < size; i++ )
buffer->arr[i] = i % 255;
/*
* Just like a varchar, set the length field
*/
buffer->len = size;
printf( "INSERT INTO TEST_BLOB\n" );
EXEC SQL INSERT INTO TEST_BLOB ( X ) values ( :buffer );
/*
* commit the changes
*/
printf("EXEC SQL COMMIT WORK;\n" );
EXEC SQL COMMIT WORK;
/*
* Now, ZERO out the buffer so when we fetch, we know that the buffer
* was NOT equal to what it was on the insert. Just to *prove* that
* the fetch got back the data we inserted
*/
memset( buffer->arr, 0, size );
/*
* Select out the data
*/
printf( " SELECT X INTO :buffer FROM TEST_BLOB;\n" );
EXEC SQL SELECT X
INTO :buffer
FROM TEST_BLOB;
/*
* show what size we got
*/
printf( "The Length = %ld\n", buffer->len );
/*
* Check each character to make sure it is what we inserted
*/
for( i = 0; i < size; i++ )
if ( buffer->arr[i] != i % 255 )
{
printf( "Error! %d != %d\n", buffer->arr[i], i%255 );
break;
}
/*
* delete that table
*/
printf( "EXEC SQL DROP TABLE TEST_BLOB;\n" );
EXEC SQL DROP TABLE TEST_BLOB;
If you don't store the length of the long/raw in the database, then PRO*C cannot tell how long it is. OCI on the other hand can. You can easily mix oci and pro*c together, letting OCI just fetch the long/raw out and doing everything else in pro*c. I'm including a small 'api' for pro*c that lets pro*c get longs (datatype code would change for a long raw is all). You might use it in a pro*c program as follows. Foo is a table with a LONG field in it called 'THETEXT' with an attribute column of 'THE_WHOLE_VIEW'. Instead of fetching the long column in pro*c, we fetch the rowid column that identifies the rowid of the long.
static void process()
{
varchar view_name[100];
varchar rowid[25];
char * cp;
EXEC SQL DECLARE C1 CURSOR FOR
SELECT THE_WHOLE_VIEW, ROWID
FROM FOO
WHERE ROWNUM < 10;
EXEC SQL OPEN C1;
for(;;)
{
EXEC SQL WHENEVER NOTFOUND DO break;
EXEC SQL FETCH C1 INTO :view_name, :rowid;
view_name.arr[view_name.len] = 0;
rowid.arr[rowid.len] = 0;
printf( "%s, %s\n", view_name.arr, rowid.arr );
printf( "View Text = '%s'",
(cp=get_long( "select thetext from foo where rowid = :rid",
rowid.arr ))?cp:"(null)" );
}
EXEC SQL CLOSE C1;
the OCI routines would look like:
#include <stdio.h> #include <string.h> #include <stdlib.h> #ifndef ORATYPES_ORACLE
/* LDA and CDA struct declarations */
#include <ocidfn.h> #ifdef __STDC__ #include <ociapr.h> #else #include <ocikpr.h> #endif #include "ocidem.h"
static int firstTime = 1;
static Lda_Def lda;
static Cda_Def cda;
char * get_long( char * sql_statement, char * rowid ) {
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; char * 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, 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,
8, &ret_len, fetched )) return NULL;
if ( ret_len )
{
if ( returnBufferLen )
{
returnBuffer =
(char*)realloc(returnBuffer,returnBufferLen+UCP_SIZE+1);
}
else
{
returnBuffer = (char*)malloc( UCP_SIZE+1 );
}
if ( !returnBuffer ) return NULL;
memmove( returnBuffer+returnBufferLen, ucp, ret_len );
returnBufferLen += UCP_SIZE;
returnBuffer[returnBufferLen] = 0;
}
}
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
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |