Link to home
Create AccountLog in
Avatar of worthyking1
worthyking1Flag 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

Avatar of Scott Fell
Scott Fell
Flag of United States of America image

What happens if you cast as VARCHAR(MAX)?
ASKER CERTIFIED SOLUTION
Avatar of lcohan
lcohan
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of 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".
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.
@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.
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.