Description:
Hi,
I've found on the web several Connector/J discussions regarding the LOAD DATA
LOCAL stuff, especially the programmatic access to the stream via pipes.
So, I guess, I'm not the only one interested in that transfer.
I'd like to propose that the Connector/J gives the user an Interface to access the
stream directly. This has some major advantages:
- The LOAD DATA LOCAL stuff is very fast in comparison to update/insert statements,
if the bytes (in the correct charset) are already available, or for binary data.
- No hassly to go thru prepared statement to load efficient binary data to the server!
Reading thru Blobs or InputStream of ResultSets is already fairly simple,
updating the db with binary data is much more complex.
(Prepared statements with Blobs or strange 1 byte to 2 byte hex convertion.)
Which is fairly slow due to unneccessary char/byte/hex convertions.
I was able to load binary data onto a remote 4.0 MySQL db using the
proposed stream with 23 MByte/sec !!! (Which is close to the disk-io limitation on
my server!). This compares to about 3 MB/sec going thru UPDATE <hex-data> stuff;
- The Stream interface is much easier for programmers than going thru a pipe.
No installation problems, no operation system dependencies.
- It could be implemented very easily, see my proposal
How to repeat:
Suggested fix:
1.) Define a simple public Interface: ILocalFileInputStream
(see attachment)
2.) Allow in Connection to allow setting an application implementation of
ILocalFileInputStream:
Connection.java:
private ILocalFileInputStream locFileHandler;
public void setLocalFileInputStream(ILocalFileInputStream lfis) {
locFileHandler= lfis;
}
public ILocalFileInputStream getLocalFileInputStream() {
return locFileHandler != null ? locFileHandler :
(locFileHandler= new ILocalFileInputStream.DefaultImpl());
}
3. In MySqlIO use the Interface from the connection and not the
FileInputStream directly by overwriting 2 LINES:
replace:
BufferedInputStream fileIn = null;
with:
ILocalFileInputStream fileIn = this.connection.getLocalFileInputStream();
and replace:
fileIn = new BufferedInputStream(new FileInputStream(fileName));
with:
fileIn.open(fileName);
The Library user may now implement the short and easy ILocalFileInputStream ,
and finally call
ILocalFileInputStream.setLocalFileInputStream(mySqlConnection, mystream);
without the need to have access to any Connector/J sources, except the Interface.
Description: Hi, I've found on the web several Connector/J discussions regarding the LOAD DATA LOCAL stuff, especially the programmatic access to the stream via pipes. So, I guess, I'm not the only one interested in that transfer. I'd like to propose that the Connector/J gives the user an Interface to access the stream directly. This has some major advantages: - The LOAD DATA LOCAL stuff is very fast in comparison to update/insert statements, if the bytes (in the correct charset) are already available, or for binary data. - No hassly to go thru prepared statement to load efficient binary data to the server! Reading thru Blobs or InputStream of ResultSets is already fairly simple, updating the db with binary data is much more complex. (Prepared statements with Blobs or strange 1 byte to 2 byte hex convertion.) Which is fairly slow due to unneccessary char/byte/hex convertions. I was able to load binary data onto a remote 4.0 MySQL db using the proposed stream with 23 MByte/sec !!! (Which is close to the disk-io limitation on my server!). This compares to about 3 MB/sec going thru UPDATE <hex-data> stuff; - The Stream interface is much easier for programmers than going thru a pipe. No installation problems, no operation system dependencies. - It could be implemented very easily, see my proposal How to repeat: Suggested fix: 1.) Define a simple public Interface: ILocalFileInputStream (see attachment) 2.) Allow in Connection to allow setting an application implementation of ILocalFileInputStream: Connection.java: private ILocalFileInputStream locFileHandler; public void setLocalFileInputStream(ILocalFileInputStream lfis) { locFileHandler= lfis; } public ILocalFileInputStream getLocalFileInputStream() { return locFileHandler != null ? locFileHandler : (locFileHandler= new ILocalFileInputStream.DefaultImpl()); } 3. In MySqlIO use the Interface from the connection and not the FileInputStream directly by overwriting 2 LINES: replace: BufferedInputStream fileIn = null; with: ILocalFileInputStream fileIn = this.connection.getLocalFileInputStream(); and replace: fileIn = new BufferedInputStream(new FileInputStream(fileName)); with: fileIn.open(fileName); The Library user may now implement the short and easy ILocalFileInputStream , and finally call ILocalFileInputStream.setLocalFileInputStream(mySqlConnection, mystream); without the need to have access to any Connector/J sources, except the Interface.