How to convert file to OLE for SQL table

[capella07]

I barely understand what I need to do myself, so if you need clarification, let me know.

Here's the relevant code from a Sub I'm working in (in Visual Studio Pro 2005):

Dim mailAtmtName As String
mailAtmtName = redempMailItem.Attachments.Item(atmtCounter).FileName

Dim filePath As String
filePath = ("Y:\Attachments_Test\TempProcessing\" & redempMailItem.Attachments.Item(atmtCounter).FileName.ToString)

Dim atmtFileStream As New FileStream("y:\attachments_test\TempProcessing\" & mailAtmtName, FileMode.Open, FileAccess.Read)

Dim atmtFile(atmtFileStream.Length) As Byte
atmtFileStream.Read(atmtFile, 0, atmtFileStream.Length)
atmtFileStream.Close()

The mailAtmtName, filePath and atmtFile variables are used later in the Sub in a SQL INSERT statement to write to a SQL 2000 table. The atmtFile is saved to a column as image datatype. Not an image like .jpg, but the datatype - big difference!


My supervisor informed me that to allow users elsewhere in the company to use the Access module they work in to view the file, it needs to be converted to OLE prior to putting it in the table.

My question: Can anyone tell me how to convert the file to OLE in VB.NET?

Please don't give me advice on how I should really not convert to OLE - it's not my decision. I'm just the poor soul who has to figure out how to do it!

Thanks!

 

[cjard]

I dont know how much I can help with this.. because I dont really understand how something can be converted to OLE - its not a file format per se.. its a way of linking some data to a program that can use it.

Do you mean to ask how JPEG data stored in a blob column can be configured so it is accessible via OLE and rendered by whatever handler the local system has for drawing jpegs?

 

[TechGnome]

cjajrd - there is no JPEG image.... he's using the IMAGE datatye in SQL Server to store a file. Which is the way to do it. Apparently there are some users that access the database via Access. Presumably via linked tables. Access has a "datatype" called OLE, which allows the "embeding" of a file. Then when you view the table from access, you can double-click the field and the file will open. There is no SQL Server equivelent..... so I don't think it can be done. The way Access stores the OLE info isn't part of the ANSI SQL Standard. It's an Access only "feature".

-tg

 

[capella07]

Thanks for replying, cjard.

The bigger picture is, I am replacing (with a Windows program) a module that was created in Access. To do its job, the Access module mixed VBA, macros, and queries for both Access and SQL 2000 database tables.

Part of what it did/does is save an email attachment, such as a pdf, xls, or doc file, to a SQL 2K table. Then, in another Access module, which I have no control over, that file is accessed from the SQL 2K table to be able to be displayed in the 2nd Access module, I guess as an image or something. I honestly don't know.

What I do know is that because of how the 2nd Access module was written or because of the limitations of Access itself, either way, the file can't just be written to the database as an image datatype. I did that and it couldn't be read by the 2nd Access module.

I was given a snippet of code that did the conversion in the existing Access VBA:

[OLEPath] = MyFolder & "\" & MyFile
[FileName] = MyFile
[OLEFile].Class = [OLEClass]
[OLEFile].OLETypeAllowed = acOLEEmbedded
[OLEFile].SourceDoc = [OLEPath]
[OLEFile].Action = acOLECreateEmbed

I know that MyFolder and MyFile are variables for the location of the file itself. [OLEFile] and [OLEPath] are two columns from the SQL 2K table being written to - [OLEFile] being the column the file is actually written to and read by the 2nd Access module.

Hope that clarifies things! I'm still confused

 

[cjard]

cjajrd - there is no JPEG image.... he's using the IMAGE datatye in SQL Server to store a file. Which is the way to do it.

I know, kinda.. IMAGE is a data type in sql server intended for string up to 2 Gb of binary data.. i.e. its what i would call a BLOB.. just MS chose to call it something else. MS assert that other cthings are BLOBs too .. text, ntext are for big character-based files. I would call these CLOBs

Maybe picking on a jpeg image as a representative of a binary file that can be stored in a BLOB/IMAGE is bad, because it leads to confusion between IMAGE in the "binary image" sense and IMAGE in the "photograph or picture" sense.


AFAICS, OLE isnt a type, per se.. so you cannot convert a file into OLE format.. its just a way of associating a file with the database such that, when called upon the file's native app will be used to render it. I.e. you can put a coreldraw file in an OLE and coreldraw will be used to open/render it.. Have i got this right?

so I don't think it can be done.

I reckon it can, but it would be a little on the horrendous side of things.. I'd be tempted to pick one db or the other, and switch.. Or write code for both, and have 2 columns in the database

 

[capella07]

AFAICS, OLE isnt a type, per se.. so you cannot convert a file into OLE format.. its just a way of associating a file with the database such that, when called upon the file's native app will be used to render it. I.e. you can put a coreldraw file in an OLE and coreldraw will be used to open/render it.. Have i got this right?

Essentially, yes. Originally, there was an Access interface/module writing to a SQL table and a different, separate Access module reading what the first Access module wrote. It appears that Access adds information on to what it writes to the image datatype/OLE column. That's how the 2nd Access module read it. However, since I'm using T-SQL to write to the table, that extra info isn't there.

The effects of that were demonstrated when I wrote to the table with my program (using T-SQL in the code) and the 2nd Access module couldn't read it, even though it was the same datatype (image). But when we switched back to writing the same file to the table with Access in the first place, the 2nd Access module picked it up just fine.

I reckon it can, but it would be a little on the horrendous side of things.. I'd be tempted to pick one db or the other, and switch.. Or write code for both, and have 2 columns in the database

A) I hope you're right that it can be done, and
B) it's already proving to be "on the horrendous side of things".

Either I figure out how to do it, or tell my supervisor (with plenty of proof, mind you) that it can't be done!

Wish me luck - I need it!

 

[TechGnome]

Well, it depends on how the data is being presented to the user. I assumed they were simply opening the tables in Access... but if there is a GUI to it, then all you need to do is tream the BLOB back to the user's hard drive, giving it the original name (or at least the original extension of the file) and then shell out to it to launch the file in it's associated program. Did that make any sense.

-tg