peterson@choctaw.csc.ti.com (Bob Peterson) (10/04/89)
Problem summary
===============
I'm having problems with Oracle and Pro*C. I'm using a bit bucket column,
aka "blob" (Oracle's LONG RAW column type), and I want to read and write that
column using Pro*C. I do _not_ want to do any sort of translation on that
column, due to the overhead involved.
My fundamental problem is I can't find in Oracle's documentation (I've even
tried 'strings' against their binaries) how to declare a LONG RAW column such
that Pro*C won't attempt to convert to/from printable ASCII. I'm currently
declaring the LONG RAW column as a Pro*C's VARCHAR type, the only declaration
that seems close to being correct.
Problem details
===============
The following description is cut down from the actual code to reduce
clutter and make the problem clearer. I hope no new errors have snuck in due
to my editing. I cannot directly contact Oracle for a solution since the
person who has the necessary identification numbers is out of touch for the
entire week, attending a local Oracle user group meeting! 8-(
I have a table, defined as shown below, in which I have a column containing
arbitrary byte values. This column, VALUE, will contain from 0 to 65500
bytes. If my value has more than 65500 bytes, which is possible, I'll insert
a second row into the table. This table is accessed using the first two
fields, where the sequence number (SEQNO) increments for each additional piece
of the VALUE column. Think of the VALUE column as containing graphics data,
e.g., the bytes defining a color image.
CREATE TABLE VALUE
(PICNO NUMBER(38) NOT NULL,
SEQNO NUMBER(38) NOT NULL,
VALUE LONG RAW) ;
I also have a header file, included in Oracle's DECLARE SECTION, defining
the C/Pro*C variables. Here is the portion of that file defining the VALUE
table.
/* Table: VALUE */
unsigned int value_picno ; /* PICNO: Picture Number */
unsigned int value_seqno ; /* SEQNO: Sequence Number */
VARCHAR value_value[65500]; /* VALUE: Raw bytes of value */
Note: Oracle's Pro*C preprocessor generates a struct for value_value:
struct {
unsigned short len ;
unsigned char arr[65500] ; }
My INSERT statement looks like this:
EXEC SQL INSERT INTO VALUE ( PICNO, SEQNO, VALUE )
VALUES ( :value_picno, :value_seqno, :value_value ) ;
The setup of the Oracle DECLARE SECTION variables looks something like this,
where "picture" is my structure containing the bytes to be stored:
if (picture->value_length > 0)
memcpy( value_value.arr,
picture->image_buffer,
picture->image_length ) ;
value_value.len = picture->image_length ;
/*==>*/ value_value.len = 0 ; /* BUG BUG BUG BUG BUG */
If I comment out "value_value.len = 0;", then when value_value.len is
greater than zero the above INSERT statement gets the Oracle error message,
"ORA-01465: invalid hex number." This error does not happen when
value_value.len is set to zero. Oracle is paying attention to this length
field.
The SELECT, shown below, gets an error when attempting to fetch any
successfully inserted row, even one with a zero length VALUE column. The
reported error is, "ORA-01460: unimplemented or unreasonable conversion
requested."
If I do not comment out "value_value.len = 0;", i.e., insert no value, then
the INSERTs all work. All SELECTs, however, fail with the same error listed as
above, i.e., ORA-01460, even though there should be no conversion for a zero
length column. Here is the SELECT, assuming appropriate setting for
value_picno and value_seqno:
SELECT VALUE INTO :value_value
FROM VALUE
WHERE PICNO = :value_picno AND SEQNO = :value_seqno ;
It seems to me, _naive_ Oracle user that I am, that there must be some other
way of declaring the VALUE column to Pro*C, e.g., one that recognizes the RAW
nature of the column and suppresses conversions.
Version information
===================
Machine environment: Sun 4/280 running SunOS 4.0.3.
Oracle DBMS: "ORACLE RDBMS V6.0.26.8.1, transaction processing option -
Production"
Pro*C preprocessor: "ORACLE Precompiler: Version 1.3.11 - Production"
Help?! (Of course I have a very rapidly approaching deadline!)
Hardcopy and Electronic Addresses: Office:
Bob Peterson Compuserve: 70235,326 Expressway Site,
Texas Instruments USENET: peterson@csc.ti.com North Building,
P.O. Box 655474, MS238 (214) 995-6080 2nd Floor,
Dallas, Texas, USA 75265 CSC Aisle C3
Hardcopy and Electronic Addresses: Office:
Bob Peterson Compuserve: 70235,326 Expressway Site,
Texas Instruments USENET: peterson@csc.ti.com North Building,