Solved

converting BLOB to String

Posted on 2004-08-24
13
6,966 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

776 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