| Bug #15735 | SubString is altering my data | ||
|---|---|---|---|
| Submitted: | 14 Dec 2005 13:27 | Modified: | 13 Feb 2006 3:23 |
| Reporter: | Chris Robinson | ||
| Status: | Can't repeat | ||
| Category: | Connector/J | Severity: | S2 (Serious) |
| Version: | 3.1.11 | OS: | Microsoft Windows (win xp) |
| Assigned to: | Target Version: | ||
[14 Dec 2005 15:04]
Aleksey Kishkin
Chris, do you change 'pos' in the loop?
[16 Dec 2005 12:43]
Chris Robinson
Yes, sorry, I thought the e-mail update would tell me when this got updated. Sorry for the delay... yes, forgot as I was writing it out in the forum... pos has the bytes read added to it so that you get the next chunk of data
[16 Dec 2005 12:44]
Chris Robinson
ie: from above in the loop pos+=length;
[16 Dec 2005 13:19]
Valeriy Kravchuk
Can you, please, send the results of the following statement show variables like 'character%'; executed in your Java program? It should return a result set, just like select. Why do you think it is Server problem, not Connector/J one?
[17 Dec 2005 13:24]
Chris Robinson
For the statement you would like me to run I need to wait until monday. The reason I think its a server and not connector J problem is because if I retrieve the whole data chunk without using substr then I end up getting my binary file exactly as it is in the db. the second I use substr it changes the binary data I am retrieving and the binary file is then corrupt. Even if I use a substr that is the length of the data itself it comes back changed.
[19 Dec 2005 10:24]
Chris Robinson
For the statement you would like me to run I need to wait until monday. (have not run yet.... too many meetings at the moment) The reason I think its a server and not connector J problem is because if I retrieve the whole data chunk without using substr then I end up getting my binary file exactly as it is in the db. the second I use substr it changes the binary data I am retrieving and the binary file is then corrupt. Even if I use a substr that is the length of the data itself it comes back changed. ----- Also, I have found a temporary way around, when I store the information I hex it and then in the java program itself I unhex it (would have done it in mysql but unhex of substr also translates the characters. Alot of my characters become 3F... But making the change in mysql by taking a 2 character hex string and using Integer.Convert(str,16) I get a byte that works.
[19 Dec 2005 10:44]
Valeriy Kravchuk
Please, send the results of show variables like 'character%'; from Java when you'll have time. substring, as other string manipulation functions, may work differently with differtent character sets. And your workaround (with hex representation, always containing digits and English letters) only proves my idea about the real reason for the problem.
[19 Dec 2005 13:37]
Chris Robinson
character_set_client|latin1 character_set_connection|latin1 character_set_database|latin1 character_set_results| character_set_server|latin1 character_set_system|utf8 character_sets_dir|C:\Program Files\MySQL\MySQL Server 5.0\share\charsets/ ---- I output the two fields as a seperated set with the seperator being the '|' character. Also, each result row is on a seperate line
[21 Dec 2005 15:40]
Valeriy Kravchuk
To complete the information, send, please, the results of "SHOW CREATE TABLE blobTest" statement.
[21 Dec 2005 15:44]
Chris Robinson
Here is the result: ---------- CREATE TABLE `blobtest` ( `BID` int(11) NOT NULL auto_increment, `stuff` longblob, `fileName` varchar(255) default NULL, `fileType` varchar(255) default NULL, PRIMARY KEY (`BID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
[26 Dec 2005 16:02]
Valeriy Kravchuk
That empty character_set_results looks strange for me. Can you, please, execute "set names latin1" statement in your code before fetching blob value and check the results? The complete but simple Java code to repeat the problem will be useful too.
[29 Dec 2005 2:26]
Chris Robinson
That did not make a difference unfortunately. It still alters data...
To retrieve the file
-----------------------------
public static void main(int BID,String args)
{
try
{
Connection db = null;
try
{
db = DbRegistry.getDbConnection();
} catch (Exception e)
{
System.err.println(e);
throw e;
}
PreparedStatement pstmnt=db.prepareStatement("select fileType,fileName from blobTest
where BID=?");
pstmnt.setInt(1,BID);
ResultSet rs = pstmnt.executeQuery();
if (rs.next())
{
System.out.println("File Name:"+rs.getString("fileName"));
System.out.println("File Type:"+rs.getString("fileType"));
int chunkSize=1048576;
int pos=1;
int count=0;
pstmnt=db.prepareStatement("select substr(stuff,?,?) from blobTest where BID=?");
pstmnt.execute("set names latin1");
pstmnt.setInt(2,chunkSize);
pstmnt.setInt(3,BID);
File file=new File(args);
if (file.exists())
{
file.delete();
}
System.out.println(file.getAbsolutePath());
FileOutputStream fos=new FileOutputStream(file);
BufferedOutputStream bos=new BufferedOutputStream(fos);
// RandomAccessFile raf = new RandomAccessFile(args,"rw");
while(true)
{
pstmnt.setInt(1,pos);
ResultSet blobData=pstmnt.executeQuery();
if (blobData.next())
{
Blob blob = blobData.getBlob(1);
int length=(int)blob.length();
if(length==0)
{
break;
}
byte[] _blob=blob.getBytes(1,length);
bos.write(_blob);
// raf.write(_blob);
pos+=length;
}
else
break;
}
bos.close();
// raf.close();
}
else
{
System.err.println("No file found");
}
System.out.println("Completed...");
} catch (Exception e)
{
System.err.println(e);
}
}
-------------------------------
to store the file
-------------------------------
public static void main(String fileName, String fileType, File file)
{
try
{
Connection db = null;
try
{
db = DbRegistry.getDbConnection();
} catch (Exception e)
{
System.err.println(e);
throw e;
}
PreparedStatement pstmnt=db.prepareStatement("insert into
blobTest(stuff,filename,filetype) values(?,?,?)");
InputStream is = new FileInputStream(file);
pstmnt.setBinaryStream(1,is,(int)file.length());
pstmnt.setString(2,fileName);
pstmnt.setString(3,fileType);
pstmnt.executeUpdate();
is.close();
System.out.println("Completed...");
} catch (Exception e)
{
System.err.println(e);
}
}
---------------------------------------------
Hope this helps.... The retrieve is to file but same concept used for http instead...
cheers
[27 Jan 2006 15:31]
Larry Hill
I'm experiencing the same problem with .wav data albeit with ColdFusion MX 7 through MyODBC 3.51. Every byte with a value of 80h through FFh is changed to 3Fh (the question mark) leading me to believe something is trying to convert the data to readable text rather than leaving it as is. Could the blame be placed on the MyODBC connector? Note that retrieving the entire blob in one shot, rather than in SubString chunks, works fine (provided you have the RAM to store it).
[28 Jan 2006 14:39]
Valeriy Kravchuk
Sorry, but I was not able to repeat the problem using server size prepared statements:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.15-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE `blobtest` (
-> `BID` int(11) NOT NULL auto_increment,
-> `stuff` longblob,
-> `fileName` varchar(255) default NULL,
-> `fileType` varchar(255) default NULL,
-> PRIMARY KEY (`BID`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (1.18 sec)
mysql> insert into blobtest(stuff) values (0x21222365808182FF);
Query OK, 1 row affected (0.04 sec)
mysql> select * from blobtest;
+-----+----------+----------+----------+
| BID | stuff | fileName | fileType |
+-----+----------+----------+----------+
| 1 | !"#eАБВ | NULL | NULL |
+-----+----------+----------+----------+
1 row in set (0.01 sec)
mysql> select collation(stuff) from blobtest;
+------------------+
| collation(stuff) |
+------------------+
| binary |
+------------------+
1 row in set (0.00 sec)
mysql> select collation(substring(stuff,1,8)), substring(stuff,1,8) from blobtes
t;
+---------------------------------+----------------------+
| collation(substring(stuff,1,8)) | substring(stuff,1,8) |
+---------------------------------+----------------------+
| binary | !"#eАБВ |
+---------------------------------+----------------------+
1 row in set (0.02 sec)
mysql> prepare stmt1 from 'select collation(substring(stuff,?,?)), substring(stu
ff,?,?) from blobtest where BID=?';
Query OK, 0 rows affected (0.02 sec)
Statement prepared
mysql> set @pos1=1;
Query OK, 0 rows affected (0.00 sec)
mysql> set @pos2=8;
Query OK, 0 rows affected (0.00 sec)
mysql> set @id=1;
Query OK, 0 rows affected (0.00 sec)
mysql> execute stmt1 using @pos1, @pos2, @pos1, @pos2, @id;
+---------------------------------+----------------------+
| collation(substring(stuff,?,?)) | substring(stuff,?,?) |
+---------------------------------+----------------------+
| binary | !"#eАБВ |
+---------------------------------+----------------------+
1 row in set (0.00 sec)
mysql> select hex(substring(stuff,1,8)), hex(stuff) from blobtest;
+---------------------------+------------------+
| hex(substring(stuff,1,8)) | hex(stuff) |
+---------------------------+------------------+
| 21222365808182FF | 21222365808182FF |
+---------------------------+------------------+
1 row in set (0.02 sec)
mysql> prepare stmt2 from 'select hex(substring(stuff,?,?)), hex(stuff) from blobtest
where BID=?';
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> execute stmt2 using @pos1, @pos2, @id;
+---------------------------+------------------+
| hex(substring(stuff,?,?)) | hex(stuff) |
+---------------------------+------------------+
| 21222365808182FF | 21222365808182FF |
+---------------------------+------------------+
1 row in set (0.00 sec)
So, the problem seems Connector-related. So, please, specify the version of Connector/J
(and, Larry - Connector/ODBC) used. Any ideas on how to demonstrate the problem on server,
similary to my test case above, will be also useful.
[28 Jan 2006 19:04]
Chris Robinson
If it is connectorJ related then you need only input a binary file like a jpeg and then output it again via substring and you will see that the bytes mentioned by Larry are changed. If you look at the beginning of the jpeg files you start with FF and this will be changed... Using the above mentioned code
[29 Jan 2006 3:18]
Larry Hill
I think Valeriy wisely changed the Category of this bug from Server to Connector/J because he/she demonstrated that the server is doing its job. Again, I'm using ColdFusion MX 7 through MyODBC 3.51 (more precisely, MyODBC-3.51.11-2-win.msi) so my so-called higher level language is 2 layers away from the server, thus, may not help Chris much. Here's something interesting: When an entire BLOB is retrieved, it arrives as an unaltered ByteArray (footnote [1]). But retrieving the SubString of a BLOB returns a String data type[2] perhaps subject to modification by an encoding transform by the connector and/or ColdFusion (latin1, Java UCS-2, UTF-8/16, Klingon, etc.). It's all Greek to me, I just want to manipulate the raw data saved in the BLOB. While I'm dreaming: Is SubString() an ANSI standard? How about a SubByteArray() query function for MySQL 6? Footnotes for ColdFusion folks who might be following along: [1] Whole BLOBs arrive as ByteArrays. <cfquery name="WhateverQueryName" datasource="WhateverDataSource"> SELECT WhateverBLOBfield FROM WhateverTableName </cfquery> <cfset buffer=WhateverQueryName.WhateverBLOBfield> This statement... <cfoutput>#buffer#</cfoutput> ...crashes coldfusion.runtime.Cast$ComplexByteArrayException: with ComplexByteArrayException: ByteArray objects cannot be converted to strings. This statement... <cfoutput>#ToString(buffer)#</cfoutput> ...results in an ugly web page but works. Use... <cfprocessingdirective suppresswhitespace="yes"> <cfheader name="Content-Disposition" value="inline; filename=Whatever.wav"> <cfcontent type="audio/wav" reset="yes"> <cfquery name="WhateverQueryName" datasource="WhateverDataSource"> SELECT WhateverBLOBfield FROM WhateverTableName </cfquery> <cfoutput>#ToString(WhateverQueryName.WhateverBLOBfield)#</cfoutput> </cfprocessingdirective> ...to push a BLOB (in this case, .wav data) directly to the browser. [2] SubStrings of BLOBs arrive as Strings. <!--- get first 1000 bytes ---> <cfquery name="WhateverQueryName" datasource="WhateverDataSource"> SELECT SubString(WhateverBLOBfield,1,1000) AS Chunk FROM WhateverTableName </cfquery> <cfset buffer=WhateverQueryName.Chunk> This statement... <cfoutput>#ArrayLen(buffer)#</cfoutput> ...crashes coldfusion.runtime.NonArrayException: with Object of type class java.lang.String cannot be used as an array It's not an array, it's a string. But save it to a file, push it to a browser, or Asc() each byte and you will probably get altered data.
[2 Feb 2006 6:57]
Larry Hill
Duh. Sorry, make that mysql-connector-java-3.1.10-bin.jar.
[2 Feb 2006 20:14]
Mark Matthews
Unfortunately, a complex issue, as the function is clobbering the information that Connector/J uses to determine if a value actually is of a binary information or not. Certain "optimizations" in the server can cause different combinations of character sets, metadata flags, etc. to represent values as BINARY data when they're not, and vice versa, so there's quite a bit of logic in the JDBC driver to try and feret these out. Unfortunately, the return from a function, even when the underlying column is a string, is very often returned looking like BINARY data, when in fact it is not, so the driver checks for no table name (which indicates result of a function call) for the column-level metadata when it _looks_ like something is binary. For your particular case, this causes the driver to think your actual binary data is a string, and it treats it as such when one calls .getObject(). The workaround is to use ResultSet.getBytes(), which does not try to do such detective work.
[2 Feb 2006 20:35]
Mark Matthews
The following code snippet shows that at least for the range of values in the byte type,
the JDBC driver stores and retrieves them correctly, even when substring() is used when I
run it. Is there a particular sequence of bytes that causes this problem for you?
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager
.getConnection(
"jdbc:mysql:///test?user=...&password=....",
null);
Statement stmt = conn.createStatement();
stmt.executeUpdate("DROP TABLE IF EXISTS bytesTest;");
stmt.executeUpdate("CREATE TABLE bytesTest(field1 blob)");
byte counter = Byte.MIN_VALUE;
byte[] allBytesOriginal = new byte[256];
for (int i = 0; i < 256; i++) {
allBytesOriginal[i] = counter++;
}
PreparedStatement pStmt = conn
.prepareStatement("INSERT INTO bytesTest VALUES (?)");
pStmt.setBytes(1, allBytesOriginal);
pStmt.executeUpdate();
ResultSet rs = stmt
.executeQuery("SELECT SUBSTRING(field1,1, 300) FROM bytesTest");
rs.next();
byte[] retrBytes = rs.getBytes(1);
if (retrBytes.length != allBytesOriginal.length) {
System.out.println("Bytes not same length");
System.exit(1);
}
for (int i = 0; i < retrBytes.length; i++) {
if (retrBytes[i] != allBytesOriginal[i]) {
System.out.println("Bytes differed at position " + i
+ ", retrieved "
+ Integer.toHexString(retrBytes[i] & 0xff) + " , sent "
+ Integer.toHexString(allBytesOriginal[i] & 0xff));
}
}
Blob asBlob = rs.getBlob(1);
byte[] blobBytes = asBlob.getBytes(1, 256);
if (blobBytes.length != allBytesOriginal.length) {
System.out.println("Bytes not same length");
System.exit(1);
}
for (int i = 0; i < blobBytes.length; i++) {
if (blobBytes[i] != allBytesOriginal[i]) {
System.out.println("Bytes differed at position " + i
+ ", retrieved "
+ Integer.toHexString(blobBytes[i] & 0xff) + " , sent "
+ Integer.toHexString(allBytesOriginal[i] & 0xff));
}
}
}
[2 Feb 2006 22:15]
Larry Hill
The string your constructing, storing, then retrieving looks like it starts at 00 hex and runs up to FF hex. Maybe Connector/J is looking at the first byte, which is obviously non-printable "non-text" and interprets the string as binary. Please try it again starting with some text characters then sprinkle in some binary stuff (both above 80 hex and below 20 hex). e.g. "abcdefg" + 80h + FFh + 00h + 08h + "hijklmn".
[3 Feb 2006 0:30]
Larry Hill
Forgive my ignorance about the inner working of MySQL and Connector/J. But if C/J can solicit the name of a column from which the data is extracted, why can't it also know the column type? By definition, (Tiny/Medium/Long)BLOBs are binary, (Tiny/Medium/Long)Texts are supposedly readable text strings subject to encoding. Are you saying that "optimizations" can hide the real intended (developer-specified) purpose, thus column type, of certain fields from even C/J? In the month I've been working with MySQL, sure, I've already seen char(X)'s magically turn into varchars. But do efficiency and benchmark speeds trump predictability?
[13 Feb 2006 3:13]
Chris Robinson
Allright, I figured it out... The problem is with prepared statements!! If you take a clearly harmless select substring(filedata,1,102400) from files where fid=1 then everything goes well and the file is returned properly. However if you use the string: String query="select substring(filedata,1,102400) from files where fid=?" and then make a preparedStatement statement=conn.prepareStatement(query); statement.setInt(1,1); ResultSet rs=statement.executeQuery(); Then all fails... Don't know why but I made a workaround by taking the toString on the preparedstatement and then executing the sql myself so all the placeholders are fixed and I am a happy camper as the file comes out unchanged...
[13 Feb 2006 3:23]
Chris Robinson
Hey Mark... I changed the code to highlight the error I was receiving and here ya go... It
now has errors with bytes being changed... All I did was make the query a
PreparedStatement.
Cheers
Chris
==================================================
public static void main2(String[] args) throws Exception {
Connection db = DbRegistry.getDbConnection();
Statement stmt = db.createStatement();
stmt.executeUpdate("DROP TABLE IF EXISTS bytesTest;");
stmt.executeUpdate("CREATE TABLE bytesTest(field1 blob)");
byte counter = Byte.MIN_VALUE;
byte[] allBytesOriginal = new byte[256];
for (int i = 0; i < 256; i++) {
allBytesOriginal[i] = counter++;
}
PreparedStatement pStmt = db
.prepareStatement("INSERT INTO bytesTest VALUES (?)");
pStmt.setBytes(1, allBytesOriginal);
pStmt.executeUpdate();
pStmt = db
.prepareStatement("SELECT SUBSTRING(field1,1, ?) FROM bytesTest");
pStmt.setInt(1,300);
ResultSet rs= pStmt.executeQuery();
// ResultSet rs = stmt
// .executeQuery("SELECT SUBSTRING(field1,1, 300) FROM bytesTest");
rs.next();
byte[] retrBytes = rs.getBytes(1);
if (retrBytes.length != allBytesOriginal.length) {
System.out.println("Bytes not same length");
System.exit(1);
}
for (int i = 0; i < retrBytes.length; i++) {
if (retrBytes[i] != allBytesOriginal[i]) {
System.out.println("Bytes differed at position " + i
+ ", retrieved "
+ Integer.toHexString(retrBytes[i] & 0xff) + " , sent "
+ Integer.toHexString(allBytesOriginal[i] & 0xff));
}
}
Blob asBlob = rs.getBlob(1);
byte[] blobBytes = asBlob.getBytes(1, 256);
if (blobBytes.length != allBytesOriginal.length) {
System.out.println("Bytes not same length");
System.exit(1);
}
for (int i = 0; i < blobBytes.length; i++) {
if (blobBytes[i] != allBytesOriginal[i]) {
System.out.println("Bytes differed at position " + i
+ ", retrieved "
+ Integer.toHexString(blobBytes[i] & 0xff) + " , sent "
+ Integer.toHexString(allBytesOriginal[i] & 0xff));
}
}
}
==================================================
[7 Feb 2007 1:30]
Michael Springmann
Experiencing exactly same strange behavior with MySQL Connector/J 5.04 and 3.1.14 (both
using the precompiled -bin.jar files) and MySQL 4.1.11 as sever.
When using prepared statements, I get back replaced characters. E.g. byte of (int) values
141, 135, 136, 164, 180, 184, 199, 230, 231, 240, 241 (and most likely many others) in
images and other binary content stored in a table with the following schema
CREATE TABLE `Raw_Object_Content` (
`Raw_Object_ID` varchar(255) NOT NULL default '',
`Raw_Content` longblob NOT NULL,
PRIMARY KEY (`Raw_Object_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=4294967295 AVG_ROW_LENGTH=5000
get all replaced with byte of (int) value 63 (= 0x3F = ? in ASCII) with Connector/J 5, or
byte of (int) value 157 with Connector/J 3.1.
For me it seems like using a non-prepared statement solves the problem.
Piece of code to illustrate:
// DOES NOT WORK CORRECTLY DUE TO REPLACED BYTES
PreparedStatement ps = connection.prepareStatement("SELECT SUBSTRING(Raw_Content FROM
? FOR ?) FROM Raw_Object_Content WHERE Raw_Object_ID = ?");
ps.setLong(1, blobPos); // set offset to start reading from
ps.setLong(2, maxBytesToRead); // set length
ps.setString(3, contentID);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
newBufferContent = rs.getBytes(1);
} else {
log.warn(contentID + " does not exist.");
throw new FileNotFoundException("Raw content does not exist: " + contentID);
}
// WORKS:
Statement s = connection.createStatement();
String sql = "SELECT SUBSTRING(Raw_Content FROM " + blobPos + " FOR " + maxBytesToRead
+ ") FROM Raw_Object_Content WHERE Raw_Object_ID = '" + contentID + "'";
ResultSet rs = s.executeQuery(sql);
if (rs.next()) {
newBufferContent = rs.getBytes(1);
} else {
log.warn(contentID + " does not exist.");
throw new FileNotFoundException("Raw content does not exist: " + contentID);
}
Best regards,
Michael
[23 Jun 2008 6:58]
Dan Zhou
I might not be an issue with Connector/J, because I had a similar issue with substring but
with MySQL C API. I have a sample test case, in C, as follows.
When USE_BOUND_VARIABLE is not defined, the query succeeds and I can read 100 bytes from
the blob.
But when USE_BOUND_VARIABLE is defined, no data had been read from the blob, and no error
from any of those mysql api calls.
std::string read_sql = "SELECT SUBSTRING(blobfield, ";
#ifdef USE_BOUND_VARIABLE
read_sql += ",?,?) FROM ";
#else
read_sql += ",1,100) FROM ";
#end
read_sql + "mytable where id = 1";
MYSQL_STMT *mysql_stmt = mysql_stmt_init(conn->mysql);
assert(mysql_stmt);
if(!mysql_stmt_prepare(mysql_stmt, (const char*)buf, buflen)) {
MYSQL_RES* meta = mysql_stmt_result_metadata(mysql_stmt);
assert(meta);
num_fields = mysql_num_fields(meta);
assert(num_fields == 1);
MYSQL_FIELD *fields = mysql_fetch_fields(meta);
int ret;
#ifdef USE_BOUND_VARIABLE
MYSQL_BIND bound_params[2];
for(int i = 0; i < 2; ++i) {
MYSQL_BIND bind = {0};
bind.buffer_type = MYSQL_TYPE_DECIMAL;
bind.buffer = new char[10];
bind.buffer_length = 1000;
bind.length = new unsigned long;
bind.error = new my_bool;
bind.is_null = new my_bool;
*bind.length = 0;
*bind.error = 0;
*bind.is_null = 0;
memcpy(&bound_params[i], &bind, sizeof(MYSQL_BIND));
}
strcpy((char*)bound_params[0].buffer, "1");
strcpy((char*)bound_params[1].buffer, "100");
ret = mysql_stmt_bind_param(mysql_stmt, bound_params);
assert(ret == 0);
#end
ret = mysql_stmt_execute(mysql_stmt);
assert(ret == 0);
MYSQL_BIND bind = {0};
bind.buffer = new char[1000];
bind.buffer_length = 1000;
bind.buffer_type = fields[0].type;
bind.length = new unsigned long;
bind.error = new my_bool;
bind.is_null = new my_bool;
*bind.length = 0;
*bind.error = 0;
*bind.is_null = 0;
ret = mysql_stmt_bind_result(mysql_stmt, &bind);
assert(ret == 0);
ret = mysql_stmt_fetch(mysql_stmt);
assert(ret == 0);
ret = mysql_stmt_fetch_column(mysql_stmt, &bind, 0, 0);
assert(ret == 0);
pbuf((char*)bind.buffer, (int)(*bind.length));
mysql_stmt_free_result(mysql_stmt);
mysql_stmt_close(mysql_stmt);
.....
}
[23 Jun 2008 7:02]
Dan Zhou
forget to mention, The client library version is: mysql-5.0.51a-0.rhel5.i386 The server version is 5.0.32 on a debian linux.
Description: To get around the large blob retrieval problem where java runs out of memory I have implemented a version that uses the substring function in mysql to retrieve chunks of data as suggested on the site. What I have stored in the table is binary data from files. The problem I run into I frist noticed on my test run for a picture. Just to take an aside first... When a text file is removed from the blob field in substring chunks and then saved to the hard disk there are absolutely no problems. When an image or binary file is removed there are major problems. It seems that the substring function is changing bytes of data. My first test that removed an image had so many changes the image would not even display anymore. to note that the problem is not in the database itself I removed the same image without the substring function and it was fine for vieweing. However, removing it with the substring function changes the data. I was wondering if you have any other work-arounds (because at the moment I am thinking of just hexing and then unhexing the data but that would create files that are twice as large in the database and java does not seem to have a built in hex function. Thanks How to repeat: insert a binary file as normal into the database (image for example) use the substring idea to repeatedly remove the data and save it into a file. Following is a small example excerpt... BID is the blob's unique ID handled by auto_increment. stuff is the blob column. db is the connection pstmnt=database prepared statement args=variable from the main that tells me the name of the file I am saving to. I know there are no try/catch blocks though there are in my code, it is to simplify it. ------------------------- int chunkSize=1024; int pos=1; db.prepareStatement("select substring(stuff,?,?) from blobTest where BID=?"); pstmnt.setInt(2,chunkSize); pstmnt.setInt(3,BID); RandomAccessFile raf = new RandomAccessFile(args,"rw"); while(true) { ResultSet rs = pstmnt.executeQuery(); pstmnt.setInt(1,pos); if (rs.next) { Blob blob = rs.getBlob(1); int length=(int)blob.length(); if(length==0) break; byte[] _blob=blob.getBytes(1,length); raf.write(_blob); } else break; } raf.close(); ------------------------- Suggested fix: I was wondering if you have any other work-arounds (because at the moment I am thinking of just hexing and then unhexing the data but that would create files that are twice as large in the database and java does not seem to have a built in hex function. Otherwise, is it possible to request an equivalent to substring for a later release of mySQL that will remove unmodified binary data in chunks?