Solved

converting BLOB to String

Posted on 2004-08-24
13
6,935 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
 
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
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 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

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.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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 how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

706 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now