Mailing-List: contact cayenne-user-help@incubator.apache.org; run by ezmlm
Precedence: bulk
Reply-To: cayenne-user@incubator.apache.org
Received-SPF: pass (asf.osuosl.org: local policy)
Mime-Version: 1.0 (Apple Message framework v750)
Content-Transfer-Encoding: quoted-printable
Message-Id: <7E627DC4-7F74-4311-8DD0-70A157BEC157@pardeike.net>
Content-Type: text/plain; charset=WINDOWS-1252; delsp=yes; format=flowed
To: cayenne-user@incubator.apache.org
From: Andreas Pardeike <andreas@pardeike.net>
Subject: Inserting with a BLOB column fails
Date: Fri, 19 May 2006 17:58:13 +0200

Hi,

When I try to insert a new row into my image database using this
code:

     File imgFile =3D new File(TEMPFILE2);
     InputStream imgStream =3D new FileInputStream(imgFile);
     long imgStreamLength =3D imgFile.length();
     byte[] imgData =3D new byte[(int)imgStreamLength];
     int offset =3D 0;
     int numRead =3D 0;
     while(offset < imgData.length && (numRead=3DimgStream.read=20
(imgData, offset, imgData.length-offset)) >=3D 0)
        offset +=3D numRead;
     imgStream.close();

     Images img =3D (Images)context.createAndRegisterNewObject=20
(Images.class);
     img.setName("jei");
     img.setGroup("medarbetare");
     img.setType("image/jpeg");
     img.setX(new Integer(160));
     img.setY(new Integer(120));
     img.setImage(imgData);
     context.commitChanges();

Cayenne fails with this error:

     QueryLogger: INSERT INTO images (group, image, name, type, x, y) =20=

VALUES (?, ?, ?, ?, ?, ?)
     QueryLogger: [bind: 'medarbetare', < 89 50 4E 47 0D 0A 1A 0A 00 =20
00 00 0D ...>, 'jei', 'image/jpeg', 160, 120]
     QueryLogger: *** error.
     java.sql.SQLException: Syntax error or access violation message =20
from server: "You have an error in your SQL syntax;
     check the manual that corresponds to your MySQL server version =20
for the right syntax to use near 'group, image, name,
     type, x, y) VALUES ('medarbetare', _binary'=89PNG\r\n\Z\n\0\0\' at =20=

line 1"

Images is defined as:

     CREATE TABLE `images` (
         `name` varchar(64) NOT NULL default '',
         `group` varchar(64) NOT NULL default '',
         `type` varchar(64) NOT NULL default '',
         `image` blob NOT NULL,
         `x` int(11) NOT NULL default '0',
         `y` int(11) NOT NULL default '0',
         PRIMARY KEY  (`name`,`group`)
     ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1

and the relevant part in Images is:

     public void setImage(byte[] image) {
         writeProperty("image", image);
     }
     public byte[] getImage() {
         return (byte[])readProperty("image");
     }


Any pointers or ideas? I already tried changing the column type but it
doesn't change anything.

Andreas Pardeike