Re: OCI piecewise insertion troubles

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/11/25
Message-ID: <347ceed9.89500855@inet16>#1/1

On Mon, 24 Nov 1997 16:55:13 -0800, Clair Roberts <croberts_at_pangaea.ca> wrote:

>I have been having trouble getting a piecewise insertion to work.
>
>I have followed the examples in the manuals to the tee and not had any
>success. (Most likely because I followed the manuals examples)
>
>The error seems to be when I get down to inserting my last piece of the
>insertion and call the oexec, It always returns me the ORA-03129 next
>piece required message.
>
>Has anybody out there been successfull in performing a Piecewise
>insertion into a long raw filed using PROC and OCI calls??

Here is a complete example that I hope helps (and i know its syntactally correct as I use it all the time....). It loads a file 64k at a time..

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

#include "load.h"

#define DEFER_PARSE 1 /* oparse flags */
#define NATIVE 1
#define VERSION_7 2
#define OCI_MORE_INSERT_PIECES -3129
#define OCI_EXIT_FAILURE 1 /* exit flags */
#define OCI_EXIT_SUCCESS 0

void load_image( char * filename, char * mime_type, char * tname ) {

text  	longbuf[65536];
ub4		len_longbuf = sizeof(longbuf);
ub4		piecesize = sizeof(longbuf);
text	img_size[20];
ub2 col_rcode; /* Column return code */
FILE	* input;

char *context = "context pointer";
ub1 piece;
ub4 iteration;
ub4 plsqltable;
ub1 cont = (ub1)1;
text sqlstmt[1024];

	printf("\nOpening source file %s\n", filename);
	if (!(input=fopen( filename, "rb" )) )
	{
		perror( "fopen: " );
		exit(1);
	}
	fseek( input, 0, SEEK_END );
	sprintf( img_size, "%ld", ftell( input ) );
	fseek( input, 0, SEEK_SET );
	printf( "File is %s bytes...\n", img_size );

	sprintf( sqlstmt, "INSERT INTO %s ( name, mime_type, img_size, image ) \

 VALUES (:name, :mime_type, :img_size, :image )", tname?tname:"image" );

	if (oparse(&cda, sqlstmt, (sb4)-1, 0, (ub4)VERSION_7))
	{
		printf( oerr_cda() );
		exit(1);
	}

	if (obndrv(&cda, (text *)":name", -1, filename, -1,
				SQLT_STR, -1, (sb2 *)0, (ub1 *)0, -1, -1))
	{
		printf( oerr_cda() );
		exit(1);
	}
	if (obndrv(&cda, (text *)":mime_type", -1, mime_type, -1,
				SQLT_STR, -1, (sb2 *)0, (ub1 *)0, -1, -1))
	{
		printf( oerr_cda() );
		exit(1);
	}
	if (obndrv(&cda, (text *)":img_size", -1, img_size, -1,
				SQLT_STR, -1, (sb2 *)0, (ub1 *)0, -1, -1))
	{
		printf( oerr_cda() );
		exit(1);
	}


	if (obindps(&cda, 0, (text *)":image",
				strlen(":image"), (ub1 *)context, 999999999,
				SQLT_LBI, (sword)0, (sb2 *)0,
				(ub2 *)0, &col_rcode, 0, 0, 0, 0,
				0, (ub4 *)0, (text *)0, 0, 0))
	{
		printf( oerr_cda() );
		exit(1);
	}

	while (cont)
	{
		oexec(&cda);
			printf( "return code %d\n", cda.rc );
		switch (-cda.rc)
		{
			case 0: /* operation is finished */
				cont = 0;
			break;

			case OCI_MORE_INSERT_PIECES: /* ORA-03129 was returned */
				if ((len_longbuf = fread(longbuf, 1, len_longbuf, input)) == -1)
				{
					perror( "fread: " );
					exit(1);
				}
				printf( "read %ld\n", len_longbuf );
				if ( ogetpi(&cda, &piece, (dvoid**)&context, 
							&iteration,&plsqltable) )
				{	
					printf( "ogetpi %s\n", oerr_cda() );
					exit(1);
				}
				if (len_longbuf < piecesize) /* last piece? */
				{
					piece = OCI_LAST_PIECE;
					printf( "Setting piece to last piece..\n" );
				}

				if (osetpi(&cda, piece, longbuf, &len_longbuf))
				{	
					printf( "osetpi %s\n", oerr_cda() );
					exit(1);
				}
			break;

			default:
				printf( "return code %d\n", cda.rc );
				printf( oerr_cda() );
				exit(1);
		}
	}
	ocom(&lda); /* Commit the insert */
	fclose(input);


	if (oclose(&cda)) /* close cursor */
	{
		printf( oerr_cda() );
		exit(1);
	}

}  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

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 Nov 25 1997 - 00:00:00 CST