Insert Excel File into Oracle Table

[ahara]

Hello all;

My boss just gave me a weird request - he wants to know if it is possible to insert an Excel file into a BLOB column of an Oracle database. Thus far, I have been playing around with the ADODB stream object:

<vbcode>

Set mStream = New ADODB.Stream
mStream.Type = adTypeBinary
mStream.Open

mStream.LoadFromFile App.Path & "\" & "Book1.xls"

STR = "insert into test_table values(1, " & mStream.Read & ")"

cmdCommand.CommandText = STR

......

</vbcode>


....but when I try to execute the command, I get an invalid character error....I have also tried using the to_lob function from Oracle, but the same error persists...any ideas would be greatly appreciated (please assume all ADO objects are OK - I did not include all the code) THANKS MUCH!!

[Static]

I dont believe you can actaully put an object into an oracle DB

but how large can the field be set?

you read the file into a string...
dump it in...

the read the string ...and drop it into a file to open...

it works.

Dim xls As String
Open "C:\Test.xls" For Binary As #1
xls = Space(LOF(1))
Get #1, , xls
Close #1
'Put into the field
 
'Read it back from field and drop into a file
Open "C:\Test2.xls" For Output As #1
Print #1, xls
Close #1

[ahara]

Thanks for your thoughts....actually you can insert objects into Oracle....the following code now works:

<vbcode>

Set rs = New ADODB.Recordset
rs.Open "select * from test_file where file_id = '" + CStr(iId) + "' for update", conn, adOpenDynamic, adLockOptimistic


Set mStream = New ADODB.Stream
mStream.Type = adTypeBinary
mStream.Open

If Len(Trim(txtPath.Text)) <> 0 Then

mStream.LoadFromFile txtPath.Text
rs.Fields("file_name").Value = mStream.Read
rs.Update
Label2.Caption = CStr(iId)
End If

theId = iId

</vbcode>

The stream object loads the Excel file from the user's local drive. This object is then inserted into the table. The problem I am having now is, how do I retrieve it and re-write it back to the user's drive? Any thoughts would be greatly appreciated. Thanks



(description of the table)

'CREATE TABLE TEST_FILE(
' FILE_ID NUMBER(10),
' FILE_NAME BLOB,
' CONSTRAINT PK_IMAGE_FILE PRIMARY KEY (FILE_ID)
');

[ahara]

Hello all;

problem solved!!!


<vbcode>

Set rs = New ADODB.Recordset
rs.Open "select * from test_file where file_id = " & theId, conn


Set mStream = New ADODB.Stream
mStream.Type = adTypeBinary
mStream.Open

mStream.Write rs.Fields(1).Value
mStream.SaveToFile "c:\temp\fileForupdate.xls", adSaveCreateOverWrite

</vbcode>

Tres cool!

[Static]

Cool. Glad you figured it out....