Set long problem

[mhajduk]

I have a problem with tquery. I am trying to convert a paradox table to oracle the table has a blob field which contains an image. I cannot seem to set the tquery variable with the image data. i have attempted the following;

Setvariable('Imagedata',table1.fieldbyname('Imagedata').asvariant);

this only copies junk and gives translation errors. I have tried to stream the blob to a memory buffer and then use setlongvariable but this copies junk and also gives translation errors on some records. What is the correct way to do this.

Thanks
Mike

 

[mwlang]

I haven't tried working with BLOBs and CLOBs, but did see a SetComplexVariable in on-line help the other day. Perhaps that is your ticket.

 

[mhajduk]

Thanks, I found that i made a mistake and set the field to long instead of longraw. Now the procedure works but i get a oracle error hex value invalid on line 2 when i execute the sql. I am assigning the value to the variable using the setvariable procedure.
( i did a test with toracledataset with the assign function an it works ok so its not bad data). What am i doing wrong. I really need answers by thursday, have a big conversion of 4 gig database that needs this to work. Help!

 

[Marco Kalter]

This indicates that you are passing long raw (or raw) data as a string variable. This is interpreted by the Oracle Server as hexadecimal data. You should use the otLongRaw variable data type instead.

See "Example - Long & Long Raw" in the help file or User's Guide for more details.

------------------
Marco Kalter
Allround Automations

 

[mhajduk]

Thanks for the quick reply. I am using the otlongraw variable. I am assigning the value as follows;

setvariable('IMAGEDATA',table1.fieldbyname('Imagedata').value);

with IMAGEDATA in the tquery defined as otlongraw. What am i doing wrong!

 

[mhajduk]

Sorry i didnot have the otlongraw, but now oracle returns a error message

ora-01460 unimplemented or unreasonable conversion request.

what the heck is that, am i still setting the long raw incorrectly.

 

[mhajduk]

Did some more playing and found that the error only comes up when the long raw data is very large like 93k. Is there some buffer setting that has to be increase.

awating your reply.
thanks
Mike Hajduk

 

[Marco Kalter]

Can you show us the Oracle table definition and the SQL statement that inserts the record? I get the feeling that we are missing some relevant information here.

------------------
Marco Kalter
Allround Automations

 

[mhajduk]

CREATE TABLE SYSTEM.MUGBLOB (
RECORDID NUMBER(10, 0),
RECORDLINK NUMBER(*),
MAINLINK NUMBER(*),
IMAGEORDER NUMBER(*),
IMAGENUMBER NUMBER(*),
DATETAKEN DATE,
TIMETAKEN DATE,
FPDATE DATE,
AFISNUMBER VARCHAR2(30),
FPSNUMBER VARCHAR2(30),
PICREG VARCHAR2(30),
FPREG VARCHAR2(30),
XSETTAKEN VARCHAR2(1),
XSETDATE DATE,
XSETREG VARCHAR2(30),
IMAGEDATA LONG RAW
)
TABLESPACE MUGPIC1
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
STORAGE (INITIAL 2949120
NEXT 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 50
FREELISTS 1
FREELIST GROUPS 1)
---------

INSERT INTO MUGBLOB (RECORDID,RECORDLINK,MAINLINK,IMAGEORDER,IMAGENUMBER,
DATETAKEN,TIMETAKEN,FPDATE,AFISNUMBER,FPSNUMBER,PICREC,FPREG,XSETTAKEN,
XSETDATE,XSETREG,IMAGEDATA)
VALUES RECORDID,:RECORDLINK,:MAINLINK,:IMAGEORDER,:IMAGENUMBER,

ATETAKEN,:TIMETAKEN,:FPDATE,:AFISNUMBER,:FPSNUMBER,ICREC,:FPREG,:XSETTAKEN,
:XSETDATE,:XSETREG,:IMAGEDATA)

-----------

NOTE :RECCORDID CONTAINS 'MUGBLOBSEQ.NEXTVAL'
ALL VARABLES ARE ASIGNED USING SETVARABLE('VARIABLENAME',TABLE1.FIELDBYNAME('FIELDNAME').VALUE);

It seems that if the picture data assigned to the imagedata varable is over 32k the error is displayed. it has no problem loading pictures
below 32k.

hope you can help

thanks
Mike Hajduk

 

[Marco Kalter]

Okay. The IMAGEDATA column is a LONG RAW, so if the :IMAGEDATA variable is of type otLongRaw, then the only thing I can think of that would cause an ORA-01460 would be a PL/SQL Block.

If the insert statement is embedded in a PL/SQL Block, then the variables are PL/SQL variables, which are limited to 32KB. Could this be the case? If so, change your code so that only the insert statement as posted above is used as the SQL text.

------------------
Marco Kalter
Allround Automations

 

[mhajduk]

I was, thanks for the help

M Hajduk

 

[mhajduk]

I was, thanks for the help

M Hajduk