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]
