Avatar of worthyking1
worthyking1
Flag for United States of America asked on

How to retrieve IMAGE (binary?) data type from SQL and convert to usable text string in ASP/VBScript

We have a SQL Server 2008 that houses our emails for our CRM (Goldmine) and a legacy business processes system built in VBScript.  It's pretty straightforward but having trouble with one thing.

The email message body is stored in the SQL DB as an IMAGE datatype (the column name is 'RFC822'). I am writing a very simple script to retrieve emails from a certain mailbox and check the message body for a certain string.

Problem is that my script cannot see the value of the RFC822 column.

I've tried casting it and converting it and I don't get any errors, but I just keep getting a null value on my variable MsgBody. What am I doing wrong here?

DebugCodeOn = True

SQL = 	"SELECT   ACCOUNTNO, MAILREF, CAST(CAST(RFC822 AS varbinary(MAX)) AS varchar(MAX)) AS MsgBody " & _
		"FROM         MAILBOX WHERE     (USERID = 'SALES') "
Set rsEmails = con.Execute(SQL)

' Other variations of Select Statement tried as well:
' 		"SELECT   ACCOUNTNO, MAILREF, RFC822 AS MsgBody "
'		"SELECT   ACCOUNTNO, MAILREF,  CONVERT(varchar(MAX), CONVERT(varbinary(MAX), RFC822)) AS MsgBody "


'Loop through the emails
While NOT rsEmails.EOF

	' set vars
	AcctNo = rsEmails("ACCOUNTNO")
	MailRef = rsEmails("MAILREF")
	MsgBody = rsEmails("MsgBody ")     ' <-- THIS IS ALWAYS NULL/EMPTY NO MATTER WHAT SELECT STATEMENT USED :(

	If DebugCodeOn then Response.Write "<p>This email ref: [" & MailRef & "]"
	If DebugCodeOn then Response.Write "<p>" & MsgBody   
	
	Set objRegEx = CreateObject("VBScript.RegExp")
	objRegEx.Global = True
	objRegEx.Pattern = "\d{3}-\d{7}-\d{7}"
	Set colMatches = objRegEx.Execute(MsgBody)  

	If colMatches.Count > 0 Then

	   For Each strMatch in colMatches
		If DebugCodeOn then Response.Write "<p>References order # " & strMatch.Value
	   Next

	End If

		If DebugCodeOn then Response.Write "##############################################################################"

  rsEmails.MoveNext()
Wend

Open in new window

Microsoft SQL ServerVisual Basic ClassicASPVB ScriptSQL

Avatar of undefined
Last Comment
worthyking1

8/22/2022 - Mon
Scott Fell

What happens if you cast as VARCHAR(MAX)?
ASKER CERTIFIED SOLUTION
lcohan

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
worthyking1

ASKER
@Scott

When casting directly to varchar(max) we get the good old SQL error "Explict conversion from data type Image to varchar(max) is prohibited".
Scott Fell

Was image used to store text before nvarchar(max) was available?  I started to test some options and am doing to much multi tasking right now.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
worthyking1

ASKER
@Scott

Not sure why the software vendor decided to use an Image data type, but that's the way they do it. I think because they're storing massive amounts of text (every email every sent or received in the CRM) that they decided to compress it to binary.
worthyking1

ASKER
Thanks, that did the trick, even though I was hoping to not have to write to a file and read back in order to get the text string, but seems to be running fine without too much lag.