VW & Oracle BLOB

Hi Yuwei,

 

Thank for the answer. I’m using vw 7.4.1. In which release are the changes you mention?

 

Have there been other improvements to the Oracle EXDI? Some time ago with had problems with memory not being released properly but I kind quite remember what it was. It had to do with OracleLobProxy I believe. Perhaps it rings a bell?

 

Mark

 

 

Hi Mark,

 

Thanks for your message.  I guess you are not using the latest VW releases, in the last a few VW releases, we did change defaultDisplayLobSize to be an instance variable of the session class where you can set whatever values. This way you can choose different values for different sessions and change them whenever you want.

 

The reason behind this restriction was that we did not want users to accidentally retrieve the entire LOBs when they are very large (e.g., a few hundred MBs or a few GBs) so that the memory of their machines will be exhausted, especially when they choose to get the LOBs as values. Having this restriction gives users a way to control how they can process the large objects.

 

If you want to get the entire BLOB, just pass a big enough (bigger than the length of the BLOBs retrieved) value to message OracleSession>>defaultDisplayLobSize:.

 

Let us know if you have any other questions.

 

Regards,

 

Yuwei       

 

 

Hi,

 

I’m using BLOB’s on Oracle and have a question about this method:

 

OracleLargeObjectBuffer>>getBlobAt: index

            | beginLobLocator resultByteArray readSize |

            beginLobLocator := self lobLocator.

            self lobLocator: (beginLobLocator refAt: index -1).

            readSize := self class defaultDisplayLobSize min: self getLobLength.

            resultByteArray := (self readFrom: 1 amount: readSize) contents.

            self lobLocator: beginLobLocator.

            ^resultByteArray

 

 

This is the method that gets invoked when retrieving rows containing a BLOB column. But, instead of returning the contents of the entire blob, the size is limited to the value of a class instance variable ‘defaultDisplayLobSize’. The default value of this is 4000.

 

Does anyone know why this is? When I use #answerLobAsProxy and later on send #readAll to it, it also returns a limited size, but in this case it’s limited to 16MB:

 

readAll

            | bytes |

            bytes := (self readFrom: 1 amount: (self getLobLength min: self class defaultDisplayLobSize)) contents.

            self databaseType == 112 ifTrue: [

                                    ^bytes asString.

            ].

            ^bytes.

 

Why are these restrictions? There are other methods to read parts from the BLOB (like #readFrom:amount:) but I would expect the default behavior to just return the entire blob. And also, why is it in a class instance variable instead of something you can set on the session performing the query?

 

Thanks,

Mark

Hi Yuwei,

 

Thanks for the information. I’m looking forward to the 7.6 release. If I want to try it out, do you think it would be possible to load and use the Oracle EXDI that comes with 7.6 in a VW 7.4.1 image? Or will it depend too much on VW 7.6?

 

Thanks,

Mark

 

 

Sorry, there is no VW 7.5.1 release, it should be the one coming out as 7.6.

 

 

7.5.1??

Cheers!

-Boris (via BlackBerry)

----- Original Message -----
From: [hidden email] <[hidden email]>
To: Mark Plas <[hidden email]>; VWNC <[hidden email]>; VW-Development List <[hidden email]>
Sent: Thu Jan 17 08:39:39 2008
Subject: RE: VW & Oracle BLOB

Hi Mark,



I think the change mentioned is in VW 7.5, but I strongly recommend that you start using the latest VW release (7.5.1 is out, 7.6 will be out soon) since there are quite some improvements to OracleEXDI including the LOB processing.



Please let us know if you find any problems.



Yuwei



________________________________

From: Mark Plas [[hidden email]]
Sent: Thursday, January 17, 2008 11:27 AM
To: Li, Yuwei; VWNC; VW-Development List
Subject: RE: VW & Oracle BLOB



Hi Yuwei,



Thank for the answer. I’m using vw 7.4.1. In which release are the changes you mention?



Have there been other improvements to the Oracle EXDI? Some time ago with had problems with memory not being released properly but I kind quite remember what it was. It had to do with OracleLobProxy I believe. Perhaps it rings a bell?



Mark



________________________________

From: Li, Yuwei [[hidden email]]
Sent: donderdag 17 januari 2008 17:10
To: Mark Plas; VWNC; VW-Development List
Subject: RE: VW & Oracle BLOB



Hi Mark,



Thanks for your message.  I guess you are not using the latest VW releases, in the last a few VW releases, we did change defaultDisplayLobSize to be an instance variable of the session class where you can set whatever values. This way you can choose different values for different sessions and change them whenever you want.



The reason behind this restriction was that we did not want users to accidentally retrieve the entire LOBs when they are very large (e.g., a few hundred MBs or a few GBs) so that the memory of their machines will be exhausted, especially when they choose to get the LOBs as values. Having this restriction gives users a way to control how they can process the large objects.



If you want to get the entire BLOB, just pass a big enough (bigger than the length of the BLOBs retrieved) value to message OracleSession>>defaultDisplayLobSize:.



Let us know if you have any other questions.



Regards,



Yuwei      



________________________________

From: [hidden email] [[hidden email]] On Behalf Of Mark Plas
Sent: Thursday, January 17, 2008 10:32 AM
To: VWNC; VW-Development List
Subject: VW & Oracle BLOB



Hi,



I’m using BLOB’s on Oracle and have a question about this method:



OracleLargeObjectBuffer>>getBlobAt: index

            | beginLobLocator resultByteArray readSize |

            beginLobLocator := self lobLocator.

            self lobLocator: (beginLobLocator refAt: index -1).

            readSize := self class defaultDisplayLobSize min: self getLobLength.

            resultByteArray := (self readFrom: 1 amount: readSize) contents.

            self lobLocator: beginLobLocator.

            ^resultByteArray





This is the method that gets invoked when retrieving rows containing a BLOB column. But, instead of returning the contents of the entire blob, the size is limited to the value of a class instance variable ‘defaultDisplayLobSize’. The default value of this is 4000.



Does anyone know why this is? When I use #answerLobAsProxy and later on send #readAll to it, it also returns a limited size, but in this case it’s limited to 16MB:



readAll

            | bytes |

            bytes := (self readFrom: 1 amount: (self getLobLength min: self class defaultDisplayLobSize)) contents.

            self databaseType == 112 ifTrue: [

                                    ^bytes asString.

            ].

            ^bytes.



Why are these restrictions? There are other methods to read parts from the BLOB (like #readFrom:amount:) but I would expect the default behavior to just return the entire blob. And also, why is it in a class instance variable instead of something you can set on the session performing the query?



Thanks,

Mark

Hi Yuwei,

 

Thanks for the example and the guidelines. I’ll try it out as soon as VW 7.6 is available.

 

While we’re at it, I have another question about blobs: do you know whether there is a limitation in size when binding ByteArrays to BLOB columns? I thought it was limited to some fixed number of bytes and that, if you wanted to bind more than this size, you had to rely on the ‘streaming’ protocol (#writeFrom: startingPosition with: aByteArray) to insert the bytearray into the Blob. But now that I’m experimenting with it, it seems I can bind bytearrays at least up to 500KB in size (using session bindInput: (Array with: #[…large byteArry…]), so I guess I can just bind any size ByteArrays without limitation.

 

Are you aware of any limitations like this or am I inventing things and has there never been any limit at all?

 

Best regards,

Mark

 

 

Yes, I think it is possible for the OracleEXDI 7.6 to work with VW 7.4.1.

 

If you want to try it out, do the following:

 

1. Rename Database.pcl and Database.pst in …\parcels folder created by 7.4.1, copy and past the new Database.pcl and Database.pst coming with 7.6 in this folder.
2. Rename OracleEXDI.pcl and OracleEXDI.pst (possibly others if you are using OracleThapiEXDI and OracleLens) in … \database created by 7.4.1,

copy and past the new OracleEXDI.pcl and OracleEXDI.pst coming with 7.6 in this folder.

 

Then start your test.

 

For your convenience, here is a code piece showing how the defaultDisplayLobSize can be reset (in VW 7.6).

 

| conn sess ansStrm res|

conn := OracleConnection new.

conn username: 'username';

password: 'pwd';

environment: 'env'.

conn connect.

 

sess := conn getSession.

sess answerLobAsValue.

sess defaultDisplayLobSize: 8000.

sess prepare:  'select * from Testlob'.

sess execute.

ansStrm := sess answer.

res := ansStrm upToEnd.

 

……

 

Let me know whether it works for you.

 

Regards,

 

Yuwei

 

 

Hi Yuwei,

 

Thanks for the information. I’m looking forward to the 7.6 release. If I want to try it out, do you think it would be possible to load and use the Oracle EXDI that comes with 7.6 in a VW 7.4.1 image? Or will it depend too much on VW 7.6?

 

Thanks,

Mark

 

 

Sorry, there is no VW 7.5.1 release, it should be the one coming out as 7.6.

 

 

7.5.1??

Cheers!

-Boris (via BlackBerry)

----- Original Message -----
From: [hidden email] <[hidden email]>
To: Mark Plas <[hidden email]>; VWNC <[hidden email]>; VW-Development List <[hidden email]>
Sent: Thu Jan 17 08:39:39 2008
Subject: RE: VW & Oracle BLOB

Hi Mark,



I think the change mentioned is in VW 7.5, but I strongly recommend that you start using the latest VW release (7.5.1 is out, 7.6 will be out soon) since there are quite some improvements to OracleEXDI including the LOB processing.



Please let us know if you find any problems.



Yuwei



________________________________

From: Mark Plas [[hidden email]]
Sent: Thursday, January 17, 2008 11:27 AM
To: Li, Yuwei; VWNC; VW-Development List
Subject: RE: VW & Oracle BLOB



Hi Yuwei,



Thank for the answer. I’m using vw 7.4.1. In which release are the changes you mention?



Have there been other improvements to the Oracle EXDI? Some time ago with had problems with memory not being released properly but I kind quite remember what it was. It had to do with OracleLobProxy I believe. Perhaps it rings a bell?



Mark



________________________________

From: Li, Yuwei [[hidden email]]
Sent: donderdag 17 januari 2008 17:10
To: Mark Plas; VWNC; VW-Development List
Subject: RE: VW & Oracle BLOB



Hi Mark,



Thanks for your message.  I guess you are not using the latest VW releases, in the last a few VW releases, we did change defaultDisplayLobSize to be an instance variable of the session class where you can set whatever values. This way you can choose different values for different sessions and change them whenever you want.



The reason behind this restriction was that we did not want users to accidentally retrieve the entire LOBs when they are very large (e.g., a few hundred MBs or a few GBs) so that the memory of their machines will be exhausted, especially when they choose to get the LOBs as values. Having this restriction gives users a way to control how they can process the large objects.



If you want to get the entire BLOB, just pass a big enough (bigger than the length of the BLOBs retrieved) value to message OracleSession>>defaultDisplayLobSize:.



Let us know if you have any other questions.



Regards,



Yuwei      



________________________________

From: [hidden email] [[hidden email]] On Behalf Of Mark Plas
Sent: Thursday, January 17, 2008 10:32 AM
To: VWNC; VW-Development List
Subject: VW & Oracle BLOB



Hi,



I’m using BLOB’s on Oracle and have a question about this method:



OracleLargeObjectBuffer>>getBlobAt: index

            | beginLobLocator resultByteArray readSize |

            beginLobLocator := self lobLocator.

            self lobLocator: (beginLobLocator refAt: index -1).

            readSize := self class defaultDisplayLobSize min: self getLobLength.

            resultByteArray := (self readFrom: 1 amount: readSize) contents.

            self lobLocator: beginLobLocator.

            ^resultByteArray





This is the method that gets invoked when retrieving rows containing a BLOB column. But, instead of returning the contents of the entire blob, the size is limited to the value of a class instance variable ‘defaultDisplayLobSize’. The default value of this is 4000.



Does anyone know why this is? When I use #answerLobAsProxy and later on send #readAll to it, it also returns a limited size, but in this case it’s limited to 16MB:



readAll

            | bytes |

            bytes := (self readFrom: 1 amount: (self getLobLength min: self class defaultDisplayLobSize)) contents.

            self databaseType == 112 ifTrue: [

                                    ^bytes asString.

            ].

            ^bytes.



Why are these restrictions? There are other methods to read parts from the BLOB (like #readFrom:amount:) but I would expect the default behavior to just return the entire blob. And also, why is it in a class instance variable instead of something you can set on the session performing the query?



Thanks,

Mark

Hi Yuwei,

 

I’m not going to bind multimegabyte/gigabyte bytearrays but they could on occasion run into several hundreds of kilobytes. So the writeFrom:with: method isn’t really necessary for my needs. It’s good to read that there is no limitation. This makes it a bit simpler to insert/update values on the tables.

 

Regards and thanks for the swift answers.

Mark

 

 

Hi Mark,

 

I’m not aware of this limitation.

 

Here is what Oracle says in their OCI doc.: “Oracle allows nonzero binds for INSERTs and UPDATEs of any size LOB….., The bind of more than 4 kilobytes of data to a LOB column uses space from the temporary tablespace. Make sure that your temporary tablespace is big enough to hold at least the amount of data equal to the sum of all the bind lengths for LOBs. If your temporary tablespace is extendable, it will be extended automatically after the existing space is fully consumed.”

 

However, in practice, it is not a good idea to bind huge ByteArrays (of a few hundred MBs or a few GBs) to BLOB columns since your machine’s memory is limited, you’d better use LOB proxies to write or retrieve them piece by piece. If you look at method OracleLobProxy>>writeFrom: startingPosition with: aByteArray, you’ll see that if the ByteArray is big, we are doing the writing piece by piece.

 

Regards,

 

Yuwei

 

 

Hi Yuwei,

 

Thanks for the example and the guidelines. I’ll try it out as soon as VW 7.6 is available.

 

While we’re at it, I have another question about blobs: do you know whether there is a limitation in size when binding ByteArrays to BLOB columns? I thought it was limited to some fixed number of bytes and that, if you wanted to bind more than this size, you had to rely on the ‘streaming’ protocol (#writeFrom: startingPosition with: aByteArray) to insert the bytearray into the Blob. But now that I’m experimenting with it, it seems I can bind bytearrays at least up to 500KB in size (using session bindInput: (Array with: #[…large byteArry…]), so I guess I can just bind any size ByteArrays without limitation.

 

Are you aware of any limitations like this or am I inventing things and has there never been any limit at all?

 

Best regards,

Mark

 

 

Yes, I think it is possible for the OracleEXDI 7.6 to work with VW 7.4.1.

 

If you want to try it out, do the following:

 

1. Rename Database.pcl and Database.pst in …\parcels folder created by 7.4.1, copy and past the new Database.pcl and Database.pst coming with 7.6 in this folder.
2. Rename OracleEXDI.pcl and OracleEXDI.pst (possibly others if you are using OracleThapiEXDI and OracleLens) in … \database created by 7.4.1,

copy and past the new OracleEXDI.pcl and OracleEXDI.pst coming with 7.6 in this folder.

 

Then start your test.

 

For your convenience, here is a code piece showing how the defaultDisplayLobSize can be reset (in VW 7.6).

 

| conn sess ansStrm res|

conn := OracleConnection new.

conn username: 'username';

password: 'pwd';

environment: 'env'.

conn connect.

 

sess := conn getSession.

sess answerLobAsValue.

sess defaultDisplayLobSize: 8000.

sess prepare:  'select * from Testlob'.

sess execute.

ansStrm := sess answer.

res := ansStrm upToEnd.

 

……

 

Let me know whether it works for you.

 

Regards,

 

Yuwei

 

 

Hi Yuwei,

 

Thanks for the information. I’m looking forward to the 7.6 release. If I want to try it out, do you think it would be possible to load and use the Oracle EXDI that comes with 7.6 in a VW 7.4.1 image? Or will it depend too much on VW 7.6?

 

Thanks,

Mark

 

 

Sorry, there is no VW 7.5.1 release, it should be the one coming out as 7.6.

 

 

7.5.1??

Cheers!

-Boris (via BlackBerry)

----- Original Message -----
From: [hidden email] <[hidden email]>
To: Mark Plas <[hidden email]>; VWNC <[hidden email]>; VW-Development List <[hidden email]>
Sent: Thu Jan 17 08:39:39 2008
Subject: RE: VW & Oracle BLOB

Hi Mark,



I think the change mentioned is in VW 7.5, but I strongly recommend that you start using the latest VW release (7.5.1 is out, 7.6 will be out soon) since there are quite some improvements to OracleEXDI including the LOB processing.



Please let us know if you find any problems.



Yuwei



________________________________

From: Mark Plas [[hidden email]]
Sent: Thursday, January 17, 2008 11:27 AM
To: Li, Yuwei; VWNC; VW-Development List
Subject: RE: VW & Oracle BLOB



Hi Yuwei,



Thank for the answer. I’m using vw 7.4.1. In which release are the changes you mention?



Have there been other improvements to the Oracle EXDI? Some time ago with had problems with memory not being released properly but I kind quite remember what it was. It had to do with OracleLobProxy I believe. Perhaps it rings a bell?



Mark



________________________________

From: Li, Yuwei [[hidden email]]
Sent: donderdag 17 januari 2008 17:10
To: Mark Plas; VWNC; VW-Development List
Subject: RE: VW & Oracle BLOB



Hi Mark,



Thanks for your message.  I guess you are not using the latest VW releases, in the last a few VW releases, we did change defaultDisplayLobSize to be an instance variable of the session class where you can set whatever values. This way you can choose different values for different sessions and change them whenever you want.



The reason behind this restriction was that we did not want users to accidentally retrieve the entire LOBs when they are very large (e.g., a few hundred MBs or a few GBs) so that the memory of their machines will be exhausted, especially when they choose to get the LOBs as values. Having this restriction gives users a way to control how they can process the large objects.



If you want to get the entire BLOB, just pass a big enough (bigger than the length of the BLOBs retrieved) value to message OracleSession>>defaultDisplayLobSize:.



Let us know if you have any other questions.



Regards,



Yuwei      



________________________________

From: [hidden email] [[hidden email]] On Behalf Of Mark Plas
Sent: Thursday, January 17, 2008 10:32 AM
To: VWNC; VW-Development List
Subject: VW & Oracle BLOB



Hi,



I’m using BLOB’s on Oracle and have a question about this method:



OracleLargeObjectBuffer>>getBlobAt: index

            | beginLobLocator resultByteArray readSize |

            beginLobLocator := self lobLocator.

            self lobLocator: (beginLobLocator refAt: index -1).

            readSize := self class defaultDisplayLobSize min: self getLobLength.

            resultByteArray := (self readFrom: 1 amount: readSize) contents.

            self lobLocator: beginLobLocator.

            ^resultByteArray





This is the method that gets invoked when retrieving rows containing a BLOB column. But, instead of returning the contents of the entire blob, the size is limited to the value of a class instance variable ‘defaultDisplayLobSize’. The default value of this is 4000.



Does anyone know why this is? When I use #answerLobAsProxy and later on send #readAll to it, it also returns a limited size, but in this case it’s limited to 16MB:



readAll

            | bytes |

            bytes := (self readFrom: 1 amount: (self getLobLength min: self class defaultDisplayLobSize)) contents.

            self databaseType == 112 ifTrue: [

                                    ^bytes asString.

            ].

            ^bytes.



Why are these restrictions? There are other methods to read parts from the BLOB (like #readFrom:amount:) but I would expect the default behavior to just return the entire blob. And also, why is it in a class instance variable instead of something you can set on the session performing the query?



Thanks,

Mark