VA Smalltalk is a "100% VisualAge compatible" IDE that includes the original VisualAge technology and the popular VA Assist and WidgetKit add-ons.

Moderators: Eric Clayberg, wembley, tc, Diane Engles, solveig


Postby mariocarrera » Mon Feb 12, 2007 1:55 am

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

adding the folloing:
at: 113 put: AbtOracleLongRawField; " BLOB"
In the blob field I will store diferent kinds of files.
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
Posts: 17
Joined: Thu Dec 14, 2006 6:53 am

Postby tc » Mon Feb 12, 2007 9:03 am


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.

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.

Posts: 304
Joined: Tue Oct 17, 2006 7:40 am
Location: Raleigh, NC

don't work

Postby mariocarrera » Mon Feb 12, 2007 9:37 am

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: ''.
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 ...

Posts: 17
Joined: Thu Dec 14, 2006 6:53 am

Postby benvandijk » Mon Feb 26, 2007 6:50 am

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

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

v_blob_loc BLOB;


select object
into v_blob_loc
from blob_tabel
where id = p_id;


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';
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;
Posts: 45
Joined: Sun Feb 25, 2007 7:14 am
Location: Arnhem, Netherlands

Return to VA Smalltalk 7.0, 7.5 & 8.0

Who is online

Users browsing this forum: No registered users and 1 guest