MySQL Bugs: #8323: LOAD DATA INFILE not supported anymore? in preparedStatement

Bug #8323	LOAD DATA INFILE not supported anymore? in preparedStatement
Submitted:	4 Feb 2005 18:05	Modified:	13 Jul 2005 14:50
Reporter:	bruno haleblian	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S4 (Feature request)
Version:	3.1.6, 3.2.0-alpha	OS:	Any (WK2K/XP)
Assigned to:		CPU Architecture:	Any

Description:
LOAD DATA LOCAL INFILE ? 
...
stmt.SetString(1,fileName);
...
used to work fine in my previous release (3.0.6) but don't anymore!!

I may missing something In JDBC Setup...
Shall I have to change to createStatement/executeQuery ?

How to repeat:
use a 'load data local infile ? ...' 
SQLException should be thrown  on 
 connection.prepareStatement(loadTmp); 
request and setString the filename.

same with 3.0.x driver works fine

Suggested fix:
worked in 3.0.6

This is a shortcoming with the MySQL server. 'LOAD DATA INFILE' is not supported as a server-side prepared statement.

You should disable server-side prepared statements with the 'useServerPrepStmts=false' configuration property in your JDBC URL as a workaround.

For a list of which statements are supported as
prepared statements, see:

http://dev.mysql.com/doc/mysql/en/c-api-prepared-statements.html

Paul, your explanation of the problem is correct. The list of supported statements
is given in the documentation. LOAD DATA INFILE might have worked in some early
release of 4.1 (4.1.2 for example), but later we disabled it, as the implementation wasn't really prepared-statements ready. We should extend the list of supported commands 
in a future release (not in 5.0, though). Meanwhile I think I should change severity of this bug to 'feature request'.
Bruno, what is the server version that you were using connector-3.1.6 with?

4.1.8-nt or 4.1.9-nt, not shure of wich, but it's still not working with my actual 4.1.10. Anyway, I've now planned to change the request text building process  and embed filename parameter by myself.
My only way to migrate to a 3.1 or later JDBC Driver.

BTW, thanks to Paul & Mark's advises :
1 - I would'nt have been reading the C API part of the refMan by my own, forgetting C is the basis. I will, for now!
2 - I have not tried to disable useServerPrepStmts to let my others preparedStatements work just fine.

I just discovered "load data local infile "..." doesn't work inside the "mysql" client either. When I try it I get:

ERROR 1148 (42000): The used command is not allowed with this MySQL version

mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.0.51a-community |
+-------------------+

Whoops.

Add --local-infile and all works fine.

Here's a vote for this feature.

I would allow one to fully automate the recreation and reloading of large datasets without having to resort to another language just for this one step.

Thank you.