JDBC & MSSQL seem to be truncating large fields
I'm using jython 2.2.1, and jdbc 1.2 and connecting to a mssql 2000 database, writing the contents of an email to it. When I get to the body of the email which can be quite large sometimes I need to truncate the data at 5000 chars. Except mssql & jdbc gang up on me like school yard bullies, when i check the database loads of my data is missing, every time, with max chars = 256 chars.
I have checked the size of the field and it is set to 5000. what gives?
I am pretty sure it is related to jdbc, as the previous version used .... vb6 & odbc, without a hitch.
here is some code:
BODY_FIELD_DATABASE=5000 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._emailRecv) self._stmt.setString(6,self._emailSent) self._stmt.setString(7,str(int(self._attachmentCount) + 1)) self._stmt.setString(8,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 " " if len(self._emailBody) > BODY_FIELD_DATABASE: return self._clean_body(self._emailBody[:BODY_FIELD_DATABASE]) else: return self._clean_body(self._emailBody) def _clean_body(self,dirty): '''used to clean =20 occurrence in email body that contains chinese characters http://en.wikipedia.org/wiki/Quoted-printable''' dirty=str(dirty) return dirty.replace(r"=20","")
Deleted my answer - it was totally wrong. Keeping it here though so comments & conversation hang around.
As you can read in the comments, here's what happened:
The data was being put into the database fine, but the MSSQL Query Manager could not display the Chinese characters.