Ok, here is an update. I have now switched everything to UTF-8, database,
application, jsp page. I have added a tomcat filter that sets the request and
response encoding to UTF-8 presumably before anything else sees the request
(it seems to write to the log 10 times for each request). I added the
following code that processes all the fields before submitting to the database
for update or insert.
Charset utfCharset = Charset.forName("UTF-8");
CharsetDecoder decoder = utfCharset.newDecoder();
CharsetEncoder encoder = utfCharset.newEncoder();
try {
ByteBuffer bbuf = encoder.encode(CharBuffer.wrap(advertArr[i]));
CharBuffer cbuf = decoder.decode(bbuf);
advertArr[i] = cbuf.toString();
//log.info(" after conversion: " + advertArr[i]);
} catch ( CharacterCodingException cce ) {
log.info("character coding exception: " + cce.getMessage());
}
I tested as I added each of these elements. The problem persists. Same
errors. Same massive, horrific overwrite of the table on every update. AND
the data displayed in the browser has a garbage character in front of the £
sign. Why???? All the books and internet pages I've looked at say this stuff
should work.
By fiddling with the encoding settings I did achieve something. Before
switching everything to UTF-8, I explicitly set everything to ISO-8859-1 (since
the £ sign is definitely included in ISO-8859-1), and for a while the update
refused to do any rows at all, rather than overwriting them all.
Again, the update and insert statements work when pasted into the mysql command
line utility. A perl script brings up the data nicely, and does an update
including a £ sign to just one row without a problem.
So, has anyone ever encountered this?? I'm going mad.
Thanks,
Paul
----- Original Message ----
From: Paul Warner <[EMAIL PROTECTED]>
To: mysql@lists.mysql.com
Sent: Thursday, October 19, 2006 11:03:15 AM
Subject: £ (Great Britain Pound) breaks MySQL insert and update - disastrous
overwrite
Hello,
I have a Java Struts application running on Tomcat 5. I have mysql 5 installed
on redhat EL 3. When a user enters text with a £ sign (Great Britain Pound) in
the browser and clicks enter, any insert or update statement apparently gets
truncated in mysql. The insert reliably breaks and returns this error:
db exception thrown: java.sql.SQLException: Syntax error or access violation
message from server: "You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use near
'' at line 1"
I am writing the insert statement to STDERR before and after the insert, and in
the java app, it is fine. The insert statement in the app is not truncated or
messed up, and if I copy and paste it from the log into the mysql command line
client, it does the insert without a problem, *with* the pound signs (£)! But
from the browser, it will not update and throws the error, every time.
If I enter some text in the browser without pound signs, and do the insert, it
goes in fine. Then if I update that text, after entering a pound sign, the
update OVERWRITES EVERY ROW in the table! In other words, the where statement
in the update must be truncated inside mysql such that the id is removed
(should be "where advertid = 887"). Again, I am writing the update statement
to STDERR, and in Java it is fine. If I copy it from there into the mysql
command line client, it updates just one row perfectly well.
The character set in the database table is latin1. The character set in the
browser is ISO-8859-1, and the character set in Tomcat and in my application
are all the default.
Samples and info below. Thanks for any help on this urgent problem!!
Thanks,
Paul
Table info:
DROP TABLE IF EXISTS `advert`;
CREATE TABLE `advert` (
`AdvertID` int(11) NOT NULL auto_increment,
`VacancyID` int(11) NOT NULL default '0',
`AdvertTitle` varchar(100) NOT NULL default '',
`MainBody` mediumtext,
`ForFurtherInfo` mediumtext,
`FinalCopy` mediumtext,
`InstructionsForAgency` mediumtext,
`PublicationReleaseDate` datetime default NULL,
`BlueSheetDate` datetime default NULL,
`Created` datetime NOT NULL default '0000-00-00 00:00:00',
`Updated` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
UNIQUE KEY `AdvertID` (`AdvertID`),
KEY `IDX_VacancyID` (`VacancyID`),
KEY `IDX_PublicationReleaseDate` (`PublicationReleaseDate`),
KEY `IDX_BlueSheetDate` (`BlueSheetDate`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `advert`
--
sample insert statement (works in MySQL command line client, bombs from the
browser) from STDERR:
insert into advert (vacancyid, adverttitle, mainbody, forfurtherinfo,
finalcopy, instructionsforagency, created) values (884,'test3', 'Computer
Officer (Part-time)
Department of Social Anthropology
Vacancy Reference No: JF00881 Salary: £24,161-£31,525 pa pro rata
We wish to appoint a half-time Computer Officer to support and further the
provision of information technology in all areas of the Department of Social
Anthropology. etc.
', '', '<h3>TEST JOB</h3><h3>Management Information Services Division</h3>
<h4>Salary: </h4><p>Computer Officer (Part-time)</p>
<p>Department of Social Anthropology</p>
<p>Vacancy Reference No: JF00881 Salary: £24,161-£31,525 pa pro rata</p>
<p>We wish to appoint a half-time Computer Officer to support and further the
provision of information technology in all areas of the Department of Social
Anthropology. etc.</p>
<p></p>
Quote Reference: AF00884,Closing Date: 25 November 2006', '',now())
sample update statement (updates one row in mysql client, overwrites every row
in the table from the browser):
update advert set adverttitle='test4', mainbody='Computer Officer (Part-time)
Department of Social Anthropology
Vacancy Reference No: JF00881 Salary: £24,161-£31,525 pa pro rata
We wish to appoint a half-time Computer Officer to support and further the
provision of information technology in all areas of the Department of Social
Anthropology. etc.', finalcopy='<h3>TEST JOB</h3><h3>Management Information
Services Division</h3>
<h4>Salary: </h4><p>Computer Officer (Part-time)</p>
<p>Department of Social Anthropology</p>
<p>Vacancy Reference No: JF00881 Salary: £24,161-£31,525 pa pro rata</p>
<p>We wish to appoint a half-time Computer Officer to support and further the
provision of information technology in all areas of the Department of Social
Anthropology. etc.</p>
<p></p>
Quote Reference: AF00884,Closing Date: 25 November 2006' where advertid=887
mysql Ver 14.12 Distrib 5.0.22, for pc-linux-gnu (i686) using readline 5.0
Linux 2.4.21-47.EL #1 Wed Jul 5 20:46:55 EDT 2006 i686 i686 i386 GNU/Linux
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
