Inserting file into Oracle 9i

General discussion forum about all databases other than SqlBase.
Roberto Perez

Inserting file into Oracle 9i

Post by Roberto Perez » 13 Jun 2008, 14:15

Please... Help!

Thank you in advance. I have to do a POC regarding of loading file into Oracle, also created a small table to emulate the final table. I did this in several databases but never in Oracle and this is something that you create one time and never touch it again so the logic that I have to follow is not in my head all the time. The way of loading a file and retrieve it is quite similar in many database just changing few things here and there. Please let me know if I have to ask this in a different group. Below is part of the code used and four ways I have tried. Some of them are giving errors.

ENVIRONMENT: Centura 2.1 and Oracle 9i

-- Table sample to load and retrieve images/files
CREATE TABLE My_Images
( filename varchar2(25) NULL,
filepath varchar2(254) NULL,
fileext varchar2(5) NULL,
filesize integer NULL,
fileimage long raw null,
filetext varchar2(4000) NULL,
image blob NULL );

The following code works fine inserting files into a LONG RAW
Set sPLSQLcommand =
"INSERT INTO My_Images ( filename, filepath, fileext, filesize, fileimage )
VALUES ( :sFile, :sPath, :sExt, :nLength, :sImageBytes )"
If SqlPrepare( hSql, sPLSQLcommand ) = TRUE
If SqlSetLongBindDatatype( 5, 23 ) = TRUE
If SqlExecute( hSql ) = TRUE
Set bCommand = TRUE

But when I am trying to insert the file into a BLOB, I am getting several errors, one of them is ORA:01460 where the action to take is to "remove the requested conversion from the SQL statement", another one is ORA:04161 or ORA:00917, seems like Centura 2.1 router is not able to manage the string as a bytes or as a stream. What I did was to to declare same constants that Centura 3.1 and above are using but still having the same problem,

Number: DBP_LONGBUFFER = 5112
Number: DBP_ORAUSELOB = 5128

Call SqlSetParameterAll( hSql, DBP_LONGBUFFER, 5000000, '', TRUE )
Call SqlSetParameterAll( hSql, DBP_ORAUSELOB, 1, '', TRUE )
Last edited by Roberto Perez on 17 Jun 2008, 17:09, edited 2 times in total.

Roberto Perez

Re: Inserting file into Oracle 9i

Post by Roberto Perez » 18 Jun 2008, 14:23

I found something different today. The IMAGE column is the blob. If I try the INSERT below, everything works fine but there is a limitation. If my string "sHexadecimal" is bigger than 4,000 characters then I am getting the error "ORA-01704".

Set sPLSQLcommand =
"INSERT INTO My_Images ( filename, filepath, fileext, filesize, image )
VALUES ( :sFile, :sPath, :sExt, :nLength, TO_BLOB( :sHexadecimal ) )"

"sHexadecimal" string is holding the hexadecimal values of each bytes from the file, example:

-- UPDATE My_Blob_Table Set ColBlob = TO_BLOB( '41424344454647484950' ) WHERE FileName = 'WhatEverFile.Ext';

The update above will insert the string declared by TO_BLOB and hexadecimal values.

Any input in how to save a file into a blob column in Oracle?

Centura 2.1 and Oracle 9i.

Jeff Luther

Re: Inserting file into Oracle 9i

Post by Jeff Luther » 18 Jun 2008, 16:50

What is your
longbuffer=xxx
value set to in SQL.INI? Usually it's set to 32767 as a default, so not sure why your code truncates at 4,000. Is your var. sImageBytes of type Long String?

(I don't have an Oracle installation so perhaps including a small test case -- sample load, single image file, test app., etc.-- would help T/S repro. or us help further.) 4,000 is a 'funny' number and means that something's getting through, so something is fundamentally working. The truncate, though, tells me that an assumption is or is not being made that cuts your image short.

BTW, I'd be sure to catch the return on your SqlSetParameterAll calls, just in case one if failing. If so, there's another CLUE as to what the problem might be.

Roberto Perez

Re: Inserting file into Oracle 9i

Post by Roberto Perez » 18 Jun 2008, 17:23

Hi Jeff. Thank you for your help.

I am happy because your reply (literately I am jumping). I am attaching a ZIP file containing the APP used as well as an image (rename it to JPG).

Answering your questions:
-- longbuffer=1000000 ; in this case is bigger than 32767
-- my Var is a Long String

Notes:
-- In “On SAM_AppStartup” are global declarations to connect to your DB.
-- Form “frmtest_ORALOB_types” was copied from TD version 4.2 or 5.1 (don’t remember).
-- The code to insert the image or any file is under object “pbInsertImage” (is not a function)

Hopefully is all.
You do not have the required permissions to view the files attached to this post.

Jeff Luther

Re: Inserting file into Oracle 9i

Post by Jeff Luther » 18 Jun 2008, 18:07

Well, I can't run this, so you will need to try a few things. I searched for sImageBytes:
-- gobji21.dll can't be found.

-- what's the nLength value on return from SalFileRead?

-- Why do your call your output fblob a TXT file? It's in binary format, of course.

-- You have Call SalFileWrite( hFile, sImageBytes, nLength )
in the save... Are you SURE you know what the nLength is set to???

-- How about making your testing sample a little simpler?

I would suggest setting a breakpoint, stepping through your code and making sure all your assumptions (like nLength assignment in the Save function) are correct.

Roberto Perez

Re: Inserting file into Oracle 9i

Post by Roberto Perez » 18 Jun 2008, 19:18

Hi Jeff,

-- My answers:
In an old "CenturaPro" article (Get That Picture Back) is mentioned a Centura DLL named "godjxxx.dll" where XXX could be "21" for Centura 2.1 and probably "42" for Centura 4.2 and so on. Inside that DLL is an undocumented function named "GoomGetPicBytes". Do not pay attention on that because I am not using it, you can comment it.

"nLength" value will be the number of bytes read from "SalFileRead" function when I am reading the entire file and is incrementing by 1 when I am reading byte by byte. Since this a test APP, I am reading always 1 million because all my files are smaller than that.

The idea about "fblob.txt" is to prove that whatever "SalFile..." functions are reading is correct. Now, the extension "TXT" was because, in that way, we can open "fblob.txt", copy the content and try to insert directly in Oracle using PL/SQL or TOAD or any other tool that connects to Oracle. By the way, I cannot insert or update because error message ORA:01704. You can comment that part if you want.

Yes, I know exactly the length through "nLength" variable.

My sampler was really simple at the beginning but I was adding functions, table and Multiline and Dialog later because I have not "DecToHex" and "HexToDec" functions, as well as the table and Multiline to verify that I was reading the entire file (that is the reason of TXT extension). Having the bytes represented and hexadecimal was easy to copy them from MLT and try it into Oracle.

Also yes, I have been debugging my APP and sometimes breaks in SqlPrepare (following the current logic) and others in SqlPrepareAndExcute (trying the lines commented).

If you need more information please let me know.

Thanks.

Jeff Luther

Re: Inserting file into Oracle 9i

Post by Jeff Luther » 18 Jun 2008, 19:35

I should have realized the "g" might be a TD dll, I've just never seen any code that included godj before.

As to the other issues, I can't help more as I don't have Oracle. My thoughts were more to give you some ideas, so when you say
"Yes, I know exactly the length through 'nLength' variable" I want to ask: "How?"

There is a reason the file is truncated to 4,000 bytes. It's either 'inside' in the appl. somehow, or 'outside' on the DB (or router, SQL.INI) side. Without running the app, I can't test inside; I can look inside and don't see anything obvious. Without Oracle I can't test outside. Maybe someone else here can donate some time to help.

Roberto Perez

Re: Inserting file into Oracle 9i

Post by Roberto Perez » 18 Jun 2008, 20:06

Jeff, thank for your time, inputs and help.
____________________________________________________________________________________________________

"SalFileRead" from Centura help.
____________________________________________________________________________________________________

nResult = SalFileRead ( hFile, strBuffer, nBufferLength )
____________________________________________________________________________________________________
Reads a buffer of characters from an open file to a string.

Parameters
hFile File Handle. The handle of the open file.
strBuffer Receive String. The string to which the data is read.
NBufferLength Number. The number of bytes to read.

Return Value
nResult is the number of bytes read. On end of file, SalFileRead returns a byte count less than the requested amount.

Example
Actions
! Position to the beginning of the file
Call SalFileSeek ( fhInFile, nFilePos, FILE_SeekBegin )
Loop
Set nCharsRead = SalFileRead ( fhInFile, strBuffer, nRecSize )

See Also
SalFileWrite

Jeff Luther

Re: Inserting file into Oracle 9i

Post by Jeff Luther » 18 Jun 2008, 21:04

Thanks, but my comment is not on SalFileRead, it's on your SaveImageToDisk function where you have:
Call SalFileWrite( hFile, sImageBytes, nLength )

You do not need to respond to me but I did not trace your use of nLength, but see you did not pass it into the function. So... my question from before:
-- You have Call SalFileWrite( hFile, sImageBytes, nLength )
in the save... Are you SURE you know what the nLength is set to???

was just to verify that what you think it is set to is correct for the full size of the file.

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 1 guest