ORACLE BLOB II part

[mariocarrera]

Help!
To read a blob field in an Oracle table I changed the method:

AbtOracle8DatabaseManager>>buildTypeDict
adding the folloing:
at: 113 put: AbtOracleLongRawField; " BLOB"
In the blob field I will store diferent kinds of files.
READ
So reading, I transform the byteArray to string (sending asString es. byteArray asString) then I put it to a writeStream and save. This works well for text files. But for other types it does't work!
Can any one help me
Thanks
Mario

[tc]

Hello,

Here is how it is done for ST 7.5 (I am not sure it will work directly on your version of VAST). This is from one of our sunit test cases.

Code: Select all

dropAndCreateRawTable: connection
   
| rc |
rc := connection executeSQLStatement: ('drop table ', self class rawTableName) ifError: [ :err | nil ].
rc := connection executeSQLStatement: 'create table ', self class rawTableName, ' (ID smallint, DATA raw(2000))'.
connection commitUnitOfWork.



Code: Select all

writeRawDataToFile: connection
   
| sqlString querySpec resultCollection result buf filename toFile outfileSize dict |
   
"Read data from table"
sqlString :=  'select * from ', self class rawTableName, ' where id = 1'.
querySpec := (AbtQuerySpec new) statement: sqlString.
resultCollection := OrderedCollection new.
result := connection resultTableFromQuerySpec: querySpec.
result do: [:eachRow | resultCollection add: eachRow asDictionary ].
dict := resultCollection first.
buf := dict at: 'DATA'.

"write data to a file"
filename := CwFileSelectionPrompter new title: 'Output file for raw data'; prompt.
toFile := CfsFileDescriptor open: filename oflag: CfsConstants::OWRONLY | CfsConstants::OCREAT | CfsConstants::OTRUNC.
toFile isCfsError ifTrue: [ ^System prompt: toFile message ].
toFile write: buf startingAt: 1 nbyte: buf size.
toFile close.

"get file size"
toFile := CfsFileDescriptor open: toFile path oflag: CfsConstants::ORDONLY.
outfileSize := toFile size.
toFile close.
^outfileSize


One correction, these are not BLOBs. BLOBs are stored in a file controlled by Oracle and the pointer to the file is stored in the table. ST 7.5 has BLOB support.

--tc

[mariocarrera]

Hi thank you for ansew.
your method works like my method, with out errors, but the result is incorrect.
I saved to oracle a zip file, size 4m. I red this and write to file system but te size is only 32k.
this is my method, I tried many variants
| q2 s |
s := CfsWriteFileStream openEmpty: 'new_zip.zip'.
q2 := AbtQuerySpec new.
q2 statement: 'select * from ' , 'My_table_BLOB '.
"there is only one row "
( db connectionDefault resultTableFromQuerySpec: q2 ) do: [ :r2 |
| unByteArray |
unByteArray := ( r2 at: 'COL_BLOB' ).
s nextPutAll: unByteArray asString.
].
s close.
It does not work because of size, if the file is small then, all is ok but ...

Thanks
Mario

[benvandijk]

Mario,
Your method is restricted to 32k because that is the maximum size of the Oracle raw field. We solved this in Vast 6.02 using an Oracle stored procedure which reads chunks of about 30k bytes from the blob.
In smalltalk we call this procedure repeatedly to get all data.
We use a similar process to write data to the blob from smalltalk.

Our smalltalk code (with excuses for the dutch comments and variable names, if neeed i can translate them for you )
=========
leesBlobInByteArray
" Maak van de Blob waarop de ontvanger betrekking heeft weer een byteArray
"

| eenInputOutputRow eenInputDict einde positie byteArray eenOutputDict |

eenInputOutputRow :=
(AbtCompoundType new
name: '' ;
addField: ((#AbtDatabaseLongVarBinaryField asClass) new name: 'P_RAUW'; nullsOk: true; procBindType: 4; count: 32767) ;
addField: ( (#AbtDatabaseDoubleFloatField asClass) new name: 'P_ID'; nullsOk: true; procBindType: 1; isFloat: false) ;
addField: ( (#AbtDatabaseDoubleFloatField asClass) new name: 'P_POSITIE'; nullsOk: true; procBindType: 1; isFloat: false) ;
addField: ((#AbtDatabaseVarCharField asClass) new name: 'P_EINDE'; nullsOk: true; procBindType: 4; count: 1)).

eenInputDict := Dictionary new.
self byteArrayCollectie: OrderedCollection new.

eenInputDict at: 'P_ID' put: (self id).

positie := 1.
einde := 'N'.

[ einde = 'N' ]
whileTrue: [
eenInputDict at: 'P_POSITIE' put: positie.

eenOutputDict := (self class dataStore)
invokeStoredProcNamed: 'lees_blob'
withInputDictionary: eenInputDict
inputRow: eenInputOutputRow
ifError: [ :anError | self businessError: 'fout in proc lees_blob' . ].

byteArray := eenOutputDict at: 'P_RAUW'.
einde := eenOutputDict at: 'P_EINDE'.

positie := positie + (byteArray size).

self byteArrayCollectie add: byteArray.
].

==========
invokeStoredProcNamed: aStoredProcName withInputDictionary: aDictionary inputRow: aRow ifError: anErrorBlock

| aStoredProcSpec |
aStoredProcSpec := AbtStoredProcSpec new
name: aStoredProcName ;
procName: aStoredProcName ;
databaseMgrName: AbtDatabaseManager name ;
useSqlCall: true ;
inputStringLabel: nil ;
inputRow: aRow ;
yourself.

^self connection
invokeStoredProcSpec: aStoredProcSpec
withInputDictionary: aDictionary
ifError: anErrorBlock
==========
And here is the oracle stored procedure
==========
CREATE OR REPLACE procedure lees_blob
( p_rauw out raw, p_id number, p_positie number, p_einde out varchar2)
is

v_blob_loc BLOB;
v_aantal BINARY_INTEGER;

begin

select object
into v_blob_loc
from blob_tabel
where id = p_id;

DBMS_LOB.OPEN (v_blob_loc, DBMS_LOB.LOB_READONLY);

v_aantal := DBMS_LOB.GETLENGTH (v_blob_loc);

v_aantal := v_aantal + 1 - p_positie;

if v_aantal > 30000 then
v_aantal := 30000;
p_einde := 'N';
else
p_einde := 'Y';
end if;

DBMS_LOB.READ (v_bLob_loc, v_aantal, p_positie, p_rauw);

DBMS_LOB.CLOSE (v_blob_loc);

end lees_blob;
=========