Solved

converting BLOB to String

Posted on 2004-08-24
13
7,051 Views
Last Modified: 2012-05-05
hey guys, I need a select statement that convert a blob, and bring me back a string

i know for sure, everytime, that the blob is just plain old text. Is there anyway to do that?
0
Comment
Question by:lmpsteelwire
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 1

Expert Comment

by:JanMah
ID: 11886693
Hi

Do you want to convert BLOB to string in oracle itself or in a programming language (VB ..)?
0
 
LVL 2

Author Comment

by:lmpsteelwire
ID: 11886763
Either oracle or VB.net....but if vb.net, im using oracleclient dll
0
 
LVL 1

Expert Comment

by:JanMah
ID: 11886811
OK

Here is one of the VB solutions to convert BLOB to string.

After getting the BLOB from the oracle

Dim sAns As String
Dim iPos As String
   
sAns = StrConv(GetAttachment, vbUnicode) ' GetAttachment is BLOB
iPos = InStr(sAns, Chr(0))
If iPos > 0 Then sAns = Left(sAns, iPos - 1)
   
GetAttachment = sAns

Hope this helps.

JanMah
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Author Comment

by:lmpsteelwire
ID: 11886846
im using vb.net, not vb
0
 
LVL 1

Expert Comment

by:JanMah
ID: 11886871
well i am sure you can change it to .Net, you can't expect all as piece of cake, huh?

JanMah
0
 
LVL 2

Author Comment

by:lmpsteelwire
ID: 11886884
I could convert it...

but I dont know how to bring a blob in from the database to my vb/vb.net
0
 
LVL 1

Expert Comment

by:JanMah
ID: 11886966
OK.. here we go

The SQL will be

Select * from TableName where ID = <ID>

Nothing special here.

After getting it in recordset, use

Dim sAns As String
Dim iPos As String
Dim bGetAttachment as Byte()

bGetAttachment = rs("Attachment").GetChunk(rs("Attachment").ActualSize)

   
sAns = StrConv(bGetAttachment, vbUnicode) ' GetAttachment is BLOB
iPos = InStr(sAns, Chr(0))
If iPos > 0 Then sAns = Left(sAns, iPos - 1)
   
GetAttachment = sAns


janMah
0
 
LVL 2

Author Comment

by:lmpsteelwire
ID: 11887023
StrConv wont take a byte array.
0
 
LVL 1

Expert Comment

by:JanMah
ID: 11887045
well, it will take. Have you checked it?
0
 
LVL 2

Author Comment

by:lmpsteelwire
ID: 11887051
nevermind. I used another question on EE to answer that last part...thanks man.


is inserting a string back into oracle as a blob just the same?
0
 
LVL 1

Expert Comment

by:JanMah
ID: 11887075
yes, if you want to just strore the string just because, it is more than 4000 char long, then I would suggest you to go for clob.

If you want to strore a file (eg. excel/image) to keep the format, then go for blob.

JanMah
0
 
LVL 2

Author Comment

by:lmpsteelwire
ID: 11887176
how exactly can i insert it back into the database? I cant insert a byte()

is there another method i have to do?
0
 
LVL 1

Accepted Solution

by:
JanMah earned 500 total points
ID: 11887305
This is one of the methods:

Pass the blob data as an variant to a procedure.
Here it will be a_aryAttachment

Dim OraSession As OracleInProcServer.OraSessionClass
Dim OraDB As OraDatabase
Dim OraField As OraField
Dim OraSound As OraBlob
Dim OraDyn As OraDynaset
Dim chunksize As Long
Dim conString As String
Dim tempconString() As String
Dim tempStr() As String
Dim server As String
Dim userid As String
Dim password As String
Dim nDsnCounter As Integer
Dim TmpChunkSize As Long



Set OraSession = New OracleInProcServer.OraSessionClass
    Set OraDB = OraSession.OpenDatabase(server, userid & "/" & password, 0&)

    'Opening the Dynaset.
    Set OraDyn = OraDB.CreateDynaset(SQL, ORADB_ENLIST_IN_MTS)
    Set OraSound = OraDyn.Fields("BLOB").Value
    'if the blob field is null for the first time before writing
    ' then an Empty keyword is written into the field.
    If OraSound.IsNull Then
        Set OraField = OraDyn.Fields("BLOB")
        OraDyn.Edit
        OraField.Value = Empty
        OraDyn.Update
    End If


    Set OraSound = OraDyn.Fields("BLOB").Value

    'to write the blob data in a single shot.
    chunksize = UBound(a_aryAttachment) + 1
    'to get the previous size of the Blob field.
    TmpChunkSize = OraSound.Size
    OraSound.offset = 1
    OraSound.PollingAmount = a_lSize
    OraDyn.Edit
   
    OraSound.Write a_aryAttachment, chunksize, ORALOB_ONE_PIECE
    'if the previous size is greater than the present size then Blob field
    'is trimmed to avoid the Unnecessary data.
    If chunksize < TmpChunkSize Then OraSound.Trim chunksize
    OraDyn.Update

    OraDyn.Close
    OraDB.Close

    Set OraSession = Nothing

Hope you are clear. All the best.

JanMah
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

631 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question