Re: How to Insert a JPG to a long raw column using OCI functions.

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 17 Aug 1999 11:47:57 GMT
Message-ID: <37ba4aae.89129761@newshost.us.oracle.com>

A copy of this was sent to "Yoann Esnaud" <yesnaud_at_fdgroup.co.uk> (if that email address didn't require changing) On Tue, 17 Aug 1999 09:03:37 +0100, you wrote:

>Hi,
>
>I am trying to insert a JPG file to an oracle Database (in a long raw
>column) using Oracle Call Interface functions.
>
>I open the JPG in binary mode, and store it in a buffer. However when I am
>trying to execute this statement: ocidb.exec ("insert into blob_table values
>(%d,'%s'),rowID, JPG_Buffer) I get an oracle error number 1465
>
>Has anyone did this before?
>
>Cheers for helping me.
>
>
>

You have a ways to go on this one. You are trying to sprintf a JPG into a C string and then execute it. that approach simply will not work.

• character string constants are limited to 2,000 bytes in Oracle7 and 4,000 bytes in Oracle8. You will need to use bind variables to do this as you will only be able to load 1,000 bytes of images in 7.x and 2,000 bytes of images in 8.x
• the JPG probably has a binary zero or a Quote in it somewhere -- that would complete muck up the sql statement (if this approach worked, which it doesn't)
• if you wanted to create an insert statement with constants -- you would have to convert the JPG into a hex stream (2 bytes of character HEX code for each byte in the file). but then you are constrained to very small files (see 1 above)

Here is a sample in v7 OCI that shows how to piecewise write the contents of a FILE from the OS into a LONG RAW. the table I use looks like:

 Name                            Null?    Type
 ------------------------------- -------- ----
 NAME                            NOT NULL VARCHAR2(255)
 MIME_TYPE                                VARCHAR2(30)
 IMG_SIZE                                 NUMBER
 IMAGE                                    LONG RAW

The code is:
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" )) )

        print_error_and_exit( "error opening file" );

    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))

        print_error_and_exit( oerr_cda() );

    if (obndrv(&cda, (text *)":name", -1, filename, -1,

                SQLT_STR, -1, (sb2 *)0, (ub1 *)0, -1, -1))
        print_error_and_exit( oerr_cda() );

    if (obndrv(&cda, (text *)":mime_type", -1, mime_type, -1,
                SQLT_STR, -1, (sb2 *)0, (ub1 *)0, -1, -1))
        print_error_and_exit( oerr_cda() );

    if (obndrv(&cda, (text *)":img_size", -1, img_size, -1,
                SQLT_STR, -1, (sb2 *)0, (ub1 *)0, -1, -1))
        print_error_and_exit( oerr_cda() );

    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))
        print_error_and_exit( oerr_cda() );

    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)
                    print_error_and_exit( "fread failed" );

                if ( ogetpi(&cda, &piece, (dvoid**)&context,
                            &iteration,&plsqltable) )
                    print_error_and_exit( "ogetpi failed" );

                if (len_longbuf < piecesize) /* last piece? */
                {
                    piece = OCI_LAST_PIECE;
                    printf( "Setting piece to last piece..\n" );
                }

                if (osetpi(&cda, piece, longbuf, &len_longbuf))
                    print_error_and_exit( "osetpi failed" );
            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 */

        print_error_and_exit( oerr_cda() ); }

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Aug 17 1999 - 06:47:57 CDT