You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Andreas Pardeike <an...@pardeike.net> on 2006/05/19 17:58:13 UTC
Inserting with a BLOB column fails
Hi,
When I try to insert a new row into my image database using this
code:
File imgFile = new File(TEMPFILE2);
InputStream imgStream = new FileInputStream(imgFile);
long imgStreamLength = imgFile.length();
byte[] imgData = new byte[(int)imgStreamLength];
int offset = 0;
int numRead = 0;
while(offset < imgData.length && (numRead=imgStream.read
(imgData, offset, imgData.length-offset)) >= 0)
offset += numRead;
imgStream.close();
Images img = (Images)context.createAndRegisterNewObject
(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)
VALUES (?, ?, ?, ?, ?, ?)
QueryLogger: [bind: 'medarbetare', < 89 50 4E 47 0D 0A 1A 0A 00
00 00 0D ...>, 'jei', 'image/jpeg', 160, 120]
QueryLogger: *** error.
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 'group, image, name,
type, x, y) VALUES ('medarbetare', _binary'‰PNG\r\n\Z\n\0\0\' at
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=MyISAM DEFAULT CHARSET=latin1
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
Re: Inserting with a BLOB column fails [SOLVED]
Posted by Andrus Adamchik <an...@objectstyle.org>.
IIRC there is an existing Jira request that asks for the same thing,
and we realize there is an issue - will probably address it in
Cayenne after 1.2.
Andrus
On May 23, 2006, at 8:30 AM, Andreas Pardeike wrote:
> Andrus,
>
> I've found the problem and solved it. Which leads me to the question:
> Why doesn't the MySQLAdapter quote column names?
>
> The not quoting of column names actually bites me here because I
> have a
> column named 'group' - once I renamed it, everything works as
> expected.
>
> There are many tools out there that handle spaces, umlaut and other
> weird
> things in column names fine - I guess it would just be better if
> cayenne
> would do so too.
>
> JMHO,
> Andreas Pardeike
>
>
> On 22 maj 2006, at 11.23, Andreas Pardeike wrote:
>
>> I just double checked. My cayenne.xml contains:
>>
>> <node name="mysql"
>> datasource="mysql.driver.xml"
>> adapter="org.objectstyle.cayenne.dba.mysql.MySQLAdapter"
>> factory="org.objectstyle.cayenne.conf.DriverDataSourceFactory">
>> <map-ref name="mysql"/>
>> </node>
>>
>> Versions:
>>
>> Cayenne Release Notes 1.2 B3 (beta)
>> mysql Ver 14.7 Distrib 4.1.14, for redhat-linux-gnu (i386) using
>> readline 4.3
>>
>> I did some more work, but couldn't get any further,
>> Andreas
>>
>> On 19 maj 2006, at 18.46, Andrus Adamchik wrote:
>>
>>> A corresponding Cayenne unit test works on all version of MySQL
>>> between 4.0 and 5.0 (tested column type as 'longblob', but I
>>> don't think it matters). So what version do you have?
>>>
>>> Another thing to check - are you actually using MySQLAdapter (or
>>> 1.2 auto adapter)? Is it possible there is an adapter mixup.
>>>
>>> Andrus
>>>
>>> On May 19, 2006, at 11:58 AM, Andreas Pardeike wrote:
>>>
>>>> Hi,
>>>>
>>>> When I try to insert a new row into my image database using this
>>>> code:
>>>>
>>>> File imgFile = new File(TEMPFILE2);
>>>> InputStream imgStream = new FileInputStream(imgFile);
>>>> long imgStreamLength = imgFile.length();
>>>> byte[] imgData = new byte[(int)imgStreamLength];
>>>> int offset = 0;
>>>> int numRead = 0;
>>>> while(offset < imgData.length && (numRead=imgStream.read
>>>> (imgData, offset, imgData.length-offset)) >= 0)
>>>> offset += numRead;
>>>> imgStream.close();
>>>>
>>>> Images img = (Images)context.createAndRegisterNewObject
>>>> (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) VALUES (?, ?, ?, ?, ?, ?)
>>>> QueryLogger: [bind: 'medarbetare', < 89 50 4E 47 0D 0A 1A 0A
>>>> 00 00 00 0D ...>, 'jei', 'image/jpeg', 160, 120]
>>>> QueryLogger: *** error.
>>>> 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 'group, image, name,
>>>> type, x, y) VALUES ('medarbetare', _binary'‰PNG\r\n\Z\n\0\0
>>>> \' at 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=MyISAM DEFAULT CHARSET=latin1
>>>>
>>>> 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
>>
>
>
Re: Inserting with a BLOB column fails [SOLVED]
Posted by Andreas Pardeike <an...@pardeike.net>.
Andrus,
I've found the problem and solved it. Which leads me to the question:
Why doesn't the MySQLAdapter quote column names?
The not quoting of column names actually bites me here because I have a
column named 'group' - once I renamed it, everything works as expected.
There are many tools out there that handle spaces, umlaut and other
weird
things in column names fine - I guess it would just be better if cayenne
would do so too.
JMHO,
Andreas Pardeike
On 22 maj 2006, at 11.23, Andreas Pardeike wrote:
> I just double checked. My cayenne.xml contains:
>
> <node name="mysql"
> datasource="mysql.driver.xml"
> adapter="org.objectstyle.cayenne.dba.mysql.MySQLAdapter"
> factory="org.objectstyle.cayenne.conf.DriverDataSourceFactory">
> <map-ref name="mysql"/>
> </node>
>
> Versions:
>
> Cayenne Release Notes 1.2 B3 (beta)
> mysql Ver 14.7 Distrib 4.1.14, for redhat-linux-gnu (i386) using
> readline 4.3
>
> I did some more work, but couldn't get any further,
> Andreas
>
> On 19 maj 2006, at 18.46, Andrus Adamchik wrote:
>
>> A corresponding Cayenne unit test works on all version of MySQL
>> between 4.0 and 5.0 (tested column type as 'longblob', but I don't
>> think it matters). So what version do you have?
>>
>> Another thing to check - are you actually using MySQLAdapter (or
>> 1.2 auto adapter)? Is it possible there is an adapter mixup.
>>
>> Andrus
>>
>> On May 19, 2006, at 11:58 AM, Andreas Pardeike wrote:
>>
>>> Hi,
>>>
>>> When I try to insert a new row into my image database using this
>>> code:
>>>
>>> File imgFile = new File(TEMPFILE2);
>>> InputStream imgStream = new FileInputStream(imgFile);
>>> long imgStreamLength = imgFile.length();
>>> byte[] imgData = new byte[(int)imgStreamLength];
>>> int offset = 0;
>>> int numRead = 0;
>>> while(offset < imgData.length && (numRead=imgStream.read
>>> (imgData, offset, imgData.length-offset)) >= 0)
>>> offset += numRead;
>>> imgStream.close();
>>>
>>> Images img = (Images)context.createAndRegisterNewObject
>>> (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) VALUES (?, ?, ?, ?, ?, ?)
>>> QueryLogger: [bind: 'medarbetare', < 89 50 4E 47 0D 0A 1A 0A
>>> 00 00 00 0D ...>, 'jei', 'image/jpeg', 160, 120]
>>> QueryLogger: *** error.
>>> 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 'group, image, name,
>>> type, x, y) VALUES ('medarbetare', _binary'‰PNG\r\n\Z\n\0\0\'
>>> at 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=MyISAM DEFAULT CHARSET=latin1
>>>
>>> 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
>
Re: Inserting with a BLOB column fails
Posted by Andreas Pardeike <an...@pardeike.net>.
I just double checked. My cayenne.xml contains:
<node name="mysql"
datasource="mysql.driver.xml"
adapter="org.objectstyle.cayenne.dba.mysql.MySQLAdapter"
factory="org.objectstyle.cayenne.conf.DriverDataSourceFactory">
<map-ref name="mysql"/>
</node>
Versions:
Cayenne Release Notes 1.2 B3 (beta)
mysql Ver 14.7 Distrib 4.1.14, for redhat-linux-gnu (i386) using
readline 4.3
I did some more work, but couldn't get any further,
Andreas
On 19 maj 2006, at 18.46, Andrus Adamchik wrote:
> A corresponding Cayenne unit test works on all version of MySQL
> between 4.0 and 5.0 (tested column type as 'longblob', but I don't
> think it matters). So what version do you have?
>
> Another thing to check - are you actually using MySQLAdapter (or
> 1.2 auto adapter)? Is it possible there is an adapter mixup.
>
> Andrus
>
> On May 19, 2006, at 11:58 AM, Andreas Pardeike wrote:
>
>> Hi,
>>
>> When I try to insert a new row into my image database using this
>> code:
>>
>> File imgFile = new File(TEMPFILE2);
>> InputStream imgStream = new FileInputStream(imgFile);
>> long imgStreamLength = imgFile.length();
>> byte[] imgData = new byte[(int)imgStreamLength];
>> int offset = 0;
>> int numRead = 0;
>> while(offset < imgData.length && (numRead=imgStream.read
>> (imgData, offset, imgData.length-offset)) >= 0)
>> offset += numRead;
>> imgStream.close();
>>
>> Images img = (Images)context.createAndRegisterNewObject
>> (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) VALUES (?, ?, ?, ?, ?, ?)
>> QueryLogger: [bind: 'medarbetare', < 89 50 4E 47 0D 0A 1A 0A
>> 00 00 00 0D ...>, 'jei', 'image/jpeg', 160, 120]
>> QueryLogger: *** error.
>> 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 'group, image, name,
>> type, x, y) VALUES ('medarbetare', _binary'‰PNG\r\n\Z\n\0\0\'
>> at 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=MyISAM DEFAULT CHARSET=latin1
>>
>> 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
Re: Inserting with a BLOB column fails
Posted by Andrus Adamchik <an...@objectstyle.org>.
A corresponding Cayenne unit test works on all version of MySQL
between 4.0 and 5.0 (tested column type as 'longblob', but I don't
think it matters). So what version do you have?
Another thing to check - are you actually using MySQLAdapter (or 1.2
auto adapter)? Is it possible there is an adapter mixup.
Andrus
On May 19, 2006, at 11:58 AM, Andreas Pardeike wrote:
> Hi,
>
> When I try to insert a new row into my image database using this
> code:
>
> File imgFile = new File(TEMPFILE2);
> InputStream imgStream = new FileInputStream(imgFile);
> long imgStreamLength = imgFile.length();
> byte[] imgData = new byte[(int)imgStreamLength];
> int offset = 0;
> int numRead = 0;
> while(offset < imgData.length && (numRead=imgStream.read
> (imgData, offset, imgData.length-offset)) >= 0)
> offset += numRead;
> imgStream.close();
>
> Images img = (Images)context.createAndRegisterNewObject
> (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) VALUES (?, ?, ?, ?, ?, ?)
> QueryLogger: [bind: 'medarbetare', < 89 50 4E 47 0D 0A 1A 0A 00
> 00 00 0D ...>, 'jei', 'image/jpeg', 160, 120]
> QueryLogger: *** error.
> 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 'group, image, name,
> type, x, y) VALUES ('medarbetare', _binary'‰PNG\r\n\Z\n\0\0\'
> at 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=MyISAM DEFAULT CHARSET=latin1
>
> 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
>