Mssql is processing rows, returning rather awkwardly
Here's the problem:
for reference:
database records 1,2 and 3 are done using jython 2.2.1 using jdbc1.2. writing to database 4 is done using vb of the old replacement program using odbc.
We found that if I copy and paste the jython and vb MailBody entries into the text block directly from this SQL Server Enterprise Manager software, it outputs the format perfectly with the correct string return. if I compare the bytes of each file with a hex editor or KDiff3, they are binary identical.
There is a third party program that consumes this data. Unfortunately the third party program reads the data, and for records 1 through 3 it displays the data without returning a row. although for input 4 it formats the text correctly. As additional evidence, which we see in the picture, the data in the database is displayed differently. Either way, the returned rows are stored in the database for vb records, but jython records are ignored. if i click on the MailBody 4 input field i can click down i can see the rest of the email. Whereas data for jython is displayed in one line.
What gives, what am I missing, and how can I deal with it? Here is the code snippet where I am actually posting it to the database.
EDIT: FYI: please ignore the discrepancies in the Processed column, it doesn't matter. EDIT: I want to make the jython program enter data the same way as the vb program. So the third party program will come in and display the data correctly. so it looks like every entry in "MailBody" will display "This is just testing!" then the next line is "and so on", so if I had to do a screendump, all records would be like database 4 record.
SOLVED
add _force_CRLF to mix:
def _force_CRLF(self, data):
'''Make sure data uses CRLF for line termination.
Nicked the regex from smtplib.quotedata. '''
print data
newdata = re.sub(r'(?:\r\n|\n|\r(?!\n))', "\r\n", data)
print newdata
return newdata
def _execute_insert(self):
try:
self._stmt=self._con.prepareStatement(\
"INSERT INTO EmailHdr (EntryID, MailSubject, MailFrom, MailTo, MailReceive, MailSent, AttachNo, MailBody)\
VALUES (?, ?, ?, ?, ?, ?, ?, cast(? as varchar (" + str(BODY_FIELD_DATABASE) + ")))")
self._stmt.setString(1,self._emailEntryId)
self._stmt.setString(2,self._subject)
self._stmt.setString(3,self._fromWho)
self._stmt.setString(4,self._toWho)
self._stmt.setString(5,self._format_date(self._emailRecv))
self._stmt.setString(6,self._format_date(self._emailSent))
self._stmt.setString(7,str(self._attachmentCount))
self._stmt.setString(8,self._force_CRLF(self._format_email_body()))
self._stmt.execute()
self._prepare_inserting_attachment_data()
self._insert_attachment_data()
except:
raise
def _format_email_body(self):
if not self._emailBody:
return "could not extract email body"
if len(self._emailBody) > BODY_TRUNCATE_LENGTH:
return self._clean_body(self._emailBody[:BODY_TRUNCATE_LENGTH])
else:
return self._clean_body(self._emailBody)
def _clean_body(self,dirty):
'''this method simply deletes any occurrence of an '=20' that plagues my output after much testing this is not related to the line return issue, even if i comment it out I still have the problem.'''
dirty=str(dirty)
dirty=dirty.replace(r"=20","")
return r"%s"%dirty
source to share