mssql 处理行返回相当尴尬
问题如下：
供您参考：
数据库条目 1， 2 和 3 是使用 jython 2.2.1 使用 jdbc1.2 制作的。 数据库条目 4 是使用 vb 制作的，旧的程序将使用 odbc 替换。
我们发现，如果我直接从 SQL Server Enterprise Manager 软件将 jython 和 vb MailBody 条目复制并粘贴到写字板，它会完美地输出格式并具有正确的行返回。 如果我使用十六进制编辑器或 KDiff3 比较每个文件的字节，它们是完全相同的二进制文件。
有一个第三方程序会消耗这些数据。 遗憾的是，第 3 方程序读取数据，并且对于条目 1 到 3，它显示数据而没有行返回。 尽管对于条目 4，它正确地设置了文本格式。 作为进一步的证明，我们可以在图中看到，数据库中的数据显示不同。 不知何故，行返回被保留在数据库中的 vb 条目中，但它们被忽略了 jython 条目。 如果我点击条目 4 的“MailBody”字段，我可以按下我可以看到电子邮件的其余部分。 而 jython 的数据显示在一行中。
给出了什么，我缺少什么，我该如何处理？ 这是我实际将其发送到数据库的代码片段。
编辑：仅供参考：请忽略“已处理”列中的差异，它是不相关的。 编辑：我想要做的是让 jython 程序以与 vb 程序相同的方式输入数据。 这样第 3 方程序就会出现并正确显示数据。 因此，“MailBody”中的每个条目都会显示“这只是测试！” 然后下一行“etc etc”，所以如果我要进行屏幕转储，所有条目都将类似于数据库条目 4。
已解决
将 _force_CRLF 添加到混合中：
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
Here is the problem:
for your reference:
database entries 1,2 and 3 are made using jython 2.2.1 using jdbc1.2.
database entry 4 is made using vb the old to be replace program using odbc.
We have found that if I copy and paste both jython and vb MailBody entries to wordpad directly from that SQL Server Enterprise Manager software it outputs the format perfectly with correct line returns. if I compare the bytes of each file with a hex editor or KDiff3 they are binary identically the same.
There is a 3rd party program which consumes this data. Sadly that 3rd party program reads the data and for entries 1 to 3 it displays the data without line returns. though for entry 4 it correctly formats the text. As futher proof we can see in the picture, the data in the database is displayed differently.
Somehow the line returns are preserved in the database for the vb entries but the jython entries they are overlooked. if I click on the 'MailBody' field of entry 4 i can press down i can see the rest of the email. Whereas the data for jython is displayed in one row.
What gives, what am i missing, and how do I handle this?
Here is a snippet of the code where I actually send it to the database.
EDIT: FYI: please disregard the discrepancies in the 'Processed' column, it is irrelevant.
EDIT: what i want to do is make the jython program input the data in the same way as the vb program. So that the 3rd party program will come along and correctly display the data.
so what it will look like is every entry in 'MailBody' will display "This is a testing only!" then next line "etc etc" so if I was to do a screendump all entries would resemble database entry 4.
SOLVED
add _force_CRLF to the 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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我建议向您的程序添加调试输出，在插入数据库之前转储字符代码。 Jython 有可能用单个字符替换 CrLf 对，并且在写入数据库时不会恢复它。
I suggest to add a debug output to your program, dumping character codes before insertion in DB. There are chances that Jython replace CrLf pair with single character and doesn't restore it when written to DB.
您应该查看 quopri 模块（以及其他有关电子邮件的模块），这样您就不必像
_clean_body使用肮脏的技巧You should look at the quopri module (and others regarding email) so you don't have to use dirty tricks as
_clean_body