Tossing (blob) chunks into a (adodb) stream
Category Domino
One of the Internet resources that has helped to fuel my Lotus Notes/Domino passion is Notes.net, now known as IBM's developerWorks forums. These forums are a tremendous resource for anyone using Lotus Notes or developing applications on top of Domino.
Since the forum members have helped me so much over the years, I now spend time helping others when and where I can.
Today's problem concerned an issue of how to get a blob (Binary Large OBject) out of a SQL database. The blob under consideration happened to be an XML file, but it really could be any type of binary (or text really) file structure that happened to be stored in a database.
The poster ran into a problem that I had hit in the past using an IBM/Lotus database connection technology called LC LSX (a LotusScript extension). The LCLSX abstracts a connection to a wide variety of datasources to the point where you can write very generic code and talk to a bunch of different database management systems from a variety of different operating systems running Notes or Domino without recoding...at least that is the theory...in practice?
Anyway, I suggested taking a different path to solving the problem based on the fact that he had specified a particular back-end database server (MS SQL Server) on a particular operating system. I suggested using a MS database connection technology called ADO (Active Data Object) DB. One of the features of this particular release of ADODB was the ability to grab a BLOB from a database record and write it to a file on the operating system control hard disk....the perfect solution to the guy's problem.
So I posted my idea and suggested an MS site for additional help.
Of course it didn't work.
I had tried the MS code from inside Lotus Domino to my local MS SQL server and it worked fine, yet I hadn't considered a much larger object sitting in the data and that was the problem this guy ran into. Talk about lending a hand
Anyway, now I was on a mission...I knew I had done this exact thing before, ran into the same problem, and got it fixed...I only had to remember where I had coded it. Eventually, I found my code and adapted it to the guys specific problem.
My solution used a concept called a 'stream'. A stream is a programmatic method to pass data from one object to another, in this case from the database server to the O/S file. The stream object does all of the 'plumbing' work for you and is a great tool to move data around inside of an application.
The problem we ran into was that the stream object has a limit as to how much data it could move around at one time so that a bad programmer wouldn't try to move to much at once an overwhelm the available system resources (memory.) Thankfully, this is a problem that the people who build database connectors run into all the time and they provide a work around called 'chunking.' Basically, the database connector can ask for a specific size portion (a chunk) of a BLOB from the server and the server will hand it over readily. The connector can pass the chunk to the stream, and the stream can put the chunk into the file. Once this is done, the stream can tell the connector to get the next chunk from the server...and this repeats until the BLOB is completely read and written.
Here is my code for accomplishing this process. I tested this against a 2MB file, then a 20MB file, the a 200MB, and since I was having success at all levels, I tried it against a huge file, 2GBs and it worked.
I just now need to see if it works for my forum friend.
The reason I am blogging this is to comment on the way the Internet has changed how we work. Prior to the Internet and forums like these, I would have probably had to CALL IBM/Lotus Technical Support for assistance. Not that Tech Support is bad, but this is a very exacting problem and most likely the folks answering the phone wouldn't have a clue how to solve the problem or offer an alternative. They would have to escalate the problem to the next level of support, and again, and again, until it landed on the software engineer responsible for LCLSX. Who knows how many DAYS it would take to get to that engineer...and it is quite possible that it couldn't be solved using that particular technology.
Instead, here is a semi-public forum built for collaborative work. A person having a problem can post and a person with experience in the problem domain can answer...quickly. We can jointly work through problems with the solutions until we get it right...AND, IMPORTANTLY, the solution is stored in a public place so it can be searched for and read by the next person having the same or similar issue.
Isn't technology GREAT!?!?!?!?!
Dim cn As Variant
Dim rs As Variant
Dim mstream As Variant
Dim field As Variant
Dim FileLength As Variant
Dim NumBlocks As Variant
Dim leftover As Variant
Dim strVer As String
'VB Constants
Const adOpenKeyset = 1
Const adLockOptimistic = 3
Const adTypeBinary = 1
Const adSaveCreateOverWrite = 2
Const adBlocksize = 4096
'Connect to the serve server
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set mstream = CreateObject("ADODB.Stream")
Call cn.Open("Provider=SQLOLEDB;data Source=UKLONDMEYPCRAU1\UATSSPOW;Initial Catalog=Credentials;User Id=readonly;Password=readonly")
Call rs.Open("Select * from Credentials", cn, adOpenKeyset, adLockOptimistic)
rs.movefirst 'if you need to process multiple records, you will need to put in a looping mechanism here
set field = rs.fields("Attachment")
FileLength = Field.ActualSize
NumBlocks = FileLength \ BlockSize
LeftOver = FileLength Mod BlockSize
mstream.Type = adTypeBinary
Call mstream.Open
mstream.Write field.getchunk(leftover) 'This gathers the remainder portion of the stream first, you could do it last if you want
'Now loop through the majority of the stream
For intLoop = 1 To NumBlocks
mstream.Write field.getchunk(BlockSize)
Next
Call mstream.SaveToFile("C:\thisis.xml", adSaveCreateOverWrite) 'again. if you are dealing with multiple records with different blobs, you will need to adjust the output filename with each loop.
Call rs.Close
Call cn.Close
One of the Internet resources that has helped to fuel my Lotus Notes/Domino passion is Notes.net, now known as IBM's developerWorks forums. These forums are a tremendous resource for anyone using Lotus Notes or developing applications on top of Domino.
Since the forum members have helped me so much over the years, I now spend time helping others when and where I can.
Today's problem concerned an issue of how to get a blob (Binary Large OBject) out of a SQL database. The blob under consideration happened to be an XML file, but it really could be any type of binary (or text really) file structure that happened to be stored in a database.
The poster ran into a problem that I had hit in the past using an IBM/Lotus database connection technology called LC LSX (a LotusScript extension). The LCLSX abstracts a connection to a wide variety of datasources to the point where you can write very generic code and talk to a bunch of different database management systems from a variety of different operating systems running Notes or Domino without recoding...at least that is the theory...in practice?
Anyway, I suggested taking a different path to solving the problem based on the fact that he had specified a particular back-end database server (MS SQL Server) on a particular operating system. I suggested using a MS database connection technology called ADO (Active Data Object) DB. One of the features of this particular release of ADODB was the ability to grab a BLOB from a database record and write it to a file on the operating system control hard disk....the perfect solution to the guy's problem.
So I posted my idea and suggested an MS site for additional help.
Of course it didn't work.
I had tried the MS code from inside Lotus Domino to my local MS SQL server and it worked fine, yet I hadn't considered a much larger object sitting in the data and that was the problem this guy ran into. Talk about lending a hand
Anyway, now I was on a mission...I knew I had done this exact thing before, ran into the same problem, and got it fixed...I only had to remember where I had coded it. Eventually, I found my code and adapted it to the guys specific problem.
My solution used a concept called a 'stream'. A stream is a programmatic method to pass data from one object to another, in this case from the database server to the O/S file. The stream object does all of the 'plumbing' work for you and is a great tool to move data around inside of an application.
The problem we ran into was that the stream object has a limit as to how much data it could move around at one time so that a bad programmer wouldn't try to move to much at once an overwhelm the available system resources (memory.) Thankfully, this is a problem that the people who build database connectors run into all the time and they provide a work around called 'chunking.' Basically, the database connector can ask for a specific size portion (a chunk) of a BLOB from the server and the server will hand it over readily. The connector can pass the chunk to the stream, and the stream can put the chunk into the file. Once this is done, the stream can tell the connector to get the next chunk from the server...and this repeats until the BLOB is completely read and written.
Here is my code for accomplishing this process. I tested this against a 2MB file, then a 20MB file, the a 200MB, and since I was having success at all levels, I tried it against a huge file, 2GBs and it worked.
I just now need to see if it works for my forum friend.
The reason I am blogging this is to comment on the way the Internet has changed how we work. Prior to the Internet and forums like these, I would have probably had to CALL IBM/Lotus Technical Support for assistance. Not that Tech Support is bad, but this is a very exacting problem and most likely the folks answering the phone wouldn't have a clue how to solve the problem or offer an alternative. They would have to escalate the problem to the next level of support, and again, and again, until it landed on the software engineer responsible for LCLSX. Who knows how many DAYS it would take to get to that engineer...and it is quite possible that it couldn't be solved using that particular technology.
Instead, here is a semi-public forum built for collaborative work. A person having a problem can post and a person with experience in the problem domain can answer...quickly. We can jointly work through problems with the solutions until we get it right...AND, IMPORTANTLY, the solution is stored in a public place so it can be searched for and read by the next person having the same or similar issue.
Isn't technology GREAT!?!?!?!?!
Dim cn As Variant
Dim rs As Variant
Dim mstream As Variant
Dim field As Variant
Dim FileLength As Variant
Dim NumBlocks As Variant
Dim leftover As Variant
Dim strVer As String
'VB Constants
Const adOpenKeyset = 1
Const adLockOptimistic = 3
Const adTypeBinary = 1
Const adSaveCreateOverWrite = 2
Const adBlocksize = 4096
'Connect to the serve server
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set mstream = CreateObject("ADODB.Stream")
Call cn.Open("Provider=SQLOLEDB;data Source=UKLONDMEYPCRAU1\UATSSPOW;Initial Catalog=Credentials;User Id=readonly;Password=readonly")
Call rs.Open("Select * from Credentials", cn, adOpenKeyset, adLockOptimistic)
rs.movefirst 'if you need to process multiple records, you will need to put in a looping mechanism here
set field = rs.fields("Attachment")
FileLength = Field.ActualSize
NumBlocks = FileLength \ BlockSize
LeftOver = FileLength Mod BlockSize
mstream.Type = adTypeBinary
Call mstream.Open
mstream.Write field.getchunk(leftover) 'This gathers the remainder portion of the stream first, you could do it last if you want
'Now loop through the majority of the stream
For intLoop = 1 To NumBlocks
mstream.Write field.getchunk(BlockSize)
Next
Call mstream.SaveToFile("C:\thisis.xml", adSaveCreateOverWrite) 'again. if you are dealing with multiple records with different blobs, you will need to adjust the output filename with each loop.
Call rs.Close
Call cn.Close
|
Permalink
| Comments (5)
| TrackBacks (0)
Bookmark :
Comments
Thanks,
E
Posted by E at 03:05:37 PM on 11/10/2006 | - Website - |
instead of..
For intLoop = 1 To NumBlocks
_________________________________
For intLoop = 1 To NumBlocks -1
it was going over too many blocks! :)
Posted by E at 03:17:24 PM on 11/10/2006 | - Website - |
Posted by Han at 05:47:10 AM on 06/22/2007 | - Website - |
Call cn.Open("Provider=SQLOLEDB;data Source=UKLONDMEYPCRAU1\UATSSPOW;Initial Catalog=Credentials;User Id=readonly;Password=readonly")
to reference the DB2 ADODB library.
The code may need to be changed slightly depending on how DB2 treats BLOBs,but I would think that part is pretty much standard ANSI SQL.
Posted by Andy at 08:39:19 AM on 06/22/2007 | - Website - |
This is all that needed to be changed to get it working in DB2:
cn.ConnectionTimeout = 30
cn.Provider = "IBMDADB2.1"
cn.Properties("Data Source")= "YourDBName"
cn.Properties("User ID") = "userName"
cn.Properties("Password") = "password"
cn.Open
Posted by Han at 07:57:14 AM on 06/28/2007 | - Website - |