Re: JDBC and LOAD DATA LOCAL INFILE :
HI, Thanks all for the suggestions given for load data... It indeed helped me a lot. Right now i am using J/connector version 3 . Load data local works fine with version 3. I hit a new problem. It works fine when there are below 2 records in the file. When i tried wiht 4,00,000 records inthe file it gave an exception. packet for query is too large (5242884 5241856) com.mysql.jdbc.Packettoobigexception Any thoughts on this. Anis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: JDBC and LOAD DATA LOCAL INFILE :
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Ahmed S K Anis wrote: HI, Thanks all for the suggestions given for load data... It indeed helped me a lot. Right now i am using J/connector version 3 . Load data local works fine with version 3. I hit a new problem. It works fine when there are below 2 records in the file. When i tried wiht 4,00,000 records inthe file it gave an exception. packet for query is too large (5242884 5241856) com.mysql.jdbc.Packettoobigexception Any thoughts on this. Anis You need to set the max_allowed_packet configuration variable for your MySQL server large enough to handle the largest file you want to send as a single packet. If you are sending large files, you will most likely want to use MySQL-4.0 or newer as well, as the protocol in MySQL-4.0 handles larger packets than MySQL-3.23 does. -Mark - -- MySQL 2003 Users Conference - http://www.mysql.com/events/uc2003/ For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.1.90 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+S6UytvXNTca6JD8RAk14AJ4xXGKDunjkHXnfZLOHJtPJfucK1wCdFEZ/ yTQL8+T/K5ce4pHXLtgx1is= =ihg1 -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
JDBC and LOAD DATA LOCAL INFILE : The used command is not allowed with this MySQL version
Hello, I'm using this command into a Java servlet LOAD DATA LOCAL INFILE \ + FIC_DB + \ REPLACE INTO TABLE news; It runs well with mysql-3.23.45 but not with mysql-3.23.55 The error message is : java.sql.SQLException: General error: The used command is not allowed with this MySQL version at org.gjt.mm.mysql.MysqlIO.sendCommand(MysqlIO.java:497) at org.gjt.mm.mysql.MysqlIO.sqlQueryDirect(MysqlIO.java:550) at org.gjt.mm.mysql.MysqlIO.sqlQuery(MysqlIO.java:635) at org.gjt.mm.mysql.Connection.execSQL(Connection.java:882) at org.gjt.mm.mysql.Connection.execSQL(Connection.java:815) at org.gjt.mm.mysql.Statement.executeQuery(Statement.java:169) at org.gjt.mm.mysql.jdbc2.Statement.executeQuery(Statement.java:78) ... I compiled mysql-3.23.55 with the option --enable-local-infile and runs it with the argument! --local-i nfile=1. If I use the mysql client, it runs but not inside my Java servlet. Could you help me to find a solution ? Thank you Cédric. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: JDBC and LOAD DATA LOCAL INFILE : The used command is notallowed with this MySQL version
Hi all, please let me now if there is a solution to this. I am facing the same problem. Anis [EMAIL PROTECTED] [EMAIL PROTECTED] Wednesday, February 12, 2003 3:48:16 PM Hello, I'm using this command into a Java servlet LOAD DATA LOCAL INFILE \ + FIC_DB + \ REPLACE INTO TABLE news; It runs well with mysql-3.23.45 but not with mysql-3.23.55 The error message is : java.sql.SQLException: General error: The used command is not allowed with this MySQL version at org.gjt.mm.mysql.MysqlIO.sendCommand(MysqlIO.java:497) at org.gjt.mm.mysql.MysqlIO.sqlQueryDirect(MysqlIO.java:550) at org.gjt.mm.mysql.MysqlIO.sqlQuery(MysqlIO.java:635) at org.gjt.mm.mysql.Connection.execSQL(Connection.java:882) at org.gjt.mm.mysql.Connection.execSQL(Connection.java:815) at org.gjt.mm.mysql.Statement.executeQuery(Statement.java:169) at org.gjt.mm.mysql.jdbc2.Statement.executeQuery(Statement.java:78) ... I compiled mysql-3.23.55 with the option --enable-local-infile and runs it with the argument! --local-i nfile=1. If I use the mysql client, it runs but not inside my Java servlet. Could you help me to find a solution ? Thank you Cédric. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: JDBC and LOAD DATA LOCAL INFILE : The used command is not allowedwith this MySQL version
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Ahmed S K Anis wrote: Hi all, please let me now if there is a solution to this. I am facing the same problem. Anis [EMAIL PROTECTED] [EMAIL PROTECTED] Wednesday, February 12, 2003 3:48:16 PM Hello, I'm using this command into a Java servlet LOAD DATA LOCAL INFILE \ + FIC_DB + \ REPLACE INTO TABLE news; It runs well with mysql-3.23.45 but not with mysql-3.23.55 The error message is : java.sql.SQLException: General error: The used command is not allowed with this MySQL version at org.gjt.mm.mysql.MysqlIO.sendCommand(MysqlIO.java:497) at org.gjt.mm.mysql.MysqlIO.sqlQueryDirect(MysqlIO.java:550) at org.gjt.mm.mysql.MysqlIO.sqlQuery(MysqlIO.java:635) at org.gjt.mm.mysql.Connection.execSQL(Connection.java:882) at org.gjt.mm.mysql.Connection.execSQL(Connection.java:815) at org.gjt.mm.mysql.Statement.executeQuery(Statement.java:169) at org.gjt.mm.mysql.jdbc2.Statement.executeQuery(Statement.java:78) ... I compiled mysql-3.23.55 with the option --enable-local-infile and runs it with the argument! --local-i nfile=1. If I use the mysql client, it runs but not inside my Java servlet. Could you help me to find a solution ? You have to use the 3.0 Connector/J drivers for 'LOAD DATA LOCAL INFILE'. See http://www.mysql.com/products/connector-j/ The support for 'LOAD DATA LOCAL INFILE' appeared in 3.0, the latest version. It was never in MM.MySQL (which is what you're using, I assume something like 2.0.14), so I don't know how it was _ever_ working for you :) -Mark - -- MySQL 2003 Users Conference - http://www.mysql.com/events/uc2003/ For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.1.90 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+Sk2MtvXNTca6JD8RAkjDAKDHfJR6jCsHRrGq3yxK+LTCPzRbWQCfc0Ld RUnZYHgbelXstPKvO2qOj8U= =IvB1 -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: JDBC and LOAD DATA LOCAL INFILE :
Thanks Mark, I tried to work with the latest driver. 3.0.4 Now it gave error shown below . Also see code snippet below at the end Error code is0 estoredata1.txt'for 'LOAD DATA LOCAL INFILE' command.Due to underlying IOExcepti on: ** BEGIN NESTED EXCEPTION ** java.io.FileNotFoundException estoredata1.txt (The system cannot find the file specified) STACKTRACE: estoredata1.txt (The system cannot find the file specified) at java.io.FileInputStream.open(Native Method) at java.io.FileInputStream.init(FileInputStream.java:59) at com.mysql.jdbc.MysqlIO.sendFileToServer(MysqlIO.java:1505) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:964) at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:911) at com.mysql.jdbc.Connection.execSQL(Connection.java:1777) at com.mysql.jdbc.Connection.execSQL(Connection.java:1711) at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1293) at Sanity.Restore(Sanity.java:138) at Sanity.main(Sanity.java:70) CODE SNIPPET String filepath = sys:\\restore\\data1.txt; String query = load data local infile \' + filepath + \' into table trail; stmt.executeUpdate(query); what could be wrong here ? Anis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: JDBC and LOAD DATA LOCAL INFILE :
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Ahmed S K Anis wrote: Thanks Mark, I tried to work with the latest driver. 3.0.4 Now it gave error shown below . Also see code snippet below at the end Error code is0 estoredata1.txt'for 'LOAD DATA LOCAL INFILE' command.Due to underlying IOExcepti on: ** BEGIN NESTED EXCEPTION ** java.io.FileNotFoundException estoredata1.txt (The system cannot find the file specified) STACKTRACE: estoredata1.txt (The system cannot find the file specified) at java.io.FileInputStream.open(Native Method) at java.io.FileInputStream.init(FileInputStream.java:59) at com.mysql.jdbc.MysqlIO.sendFileToServer(MysqlIO.java:1505) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:964) at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:911) at com.mysql.jdbc.Connection.execSQL(Connection.java:1777) at com.mysql.jdbc.Connection.execSQL(Connection.java:1711) at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1293) at Sanity.Restore(Sanity.java:138) at Sanity.main(Sanity.java:70) CODE SNIPPET String filepath = sys:\\restore\\data1.txt; String query = load data local infile \' + filepath + \' into table trail; stmt.executeUpdate(query); what could be wrong here ? First, you do realize that 'load data local infile' means load the file that is on the client and send it to the server. Second, Java is saying it can't open the file 'estoredata1.txt', which either means it doesn't exist at the path you say it does, or the permissions are wrong. Can you load this same file using the MySQL command-line client using the _exact_ same path? -Mark - -- MySQL 2003 Users Conference - http://www.mysql.com/events/uc2003/ For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.1.90 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+Slf+tvXNTca6JD8RAq+YAJ0bV2N62jOE7UsCOL2aOH4A7Y2MnACgiQRw uLPb/oWgEJubdksxBafP6RU= =uzys -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: JDBC and LOAD DATA LOCAL INFILE :
Yes Mark it worked from the command line. The Archived resides at the server side. I ran Load data local infile 'sys:\\mail\\dump.txt into table X it worked. The from a different machine i ran this java program. The Archived file still exists at the DB server side. So i guess local option is not necessary. It didnt work though I guess the path it takes is wrong. String filepath = sys:\\restore\\data1.txt; String query = load data local infile \' + filepath + \' into table x it takes as estoredata1.txt as file name not clear why ? Like to add here that Select * into outfile works perfect from JDBC wiht same filepath Mark Matthews [EMAIL PROTECTED] Wednesday, February 12, 2003 7:49:42 PM -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Ahmed S K Anis wrote: Thanks Mark, I tried to work with the latest driver. 3.0.4 Now it gave error shown below . Also see code snippet below at the end Error code is0 estoredata1.txt'for 'LOAD DATA LOCAL INFILE' command.Due to underlying IOExcepti on: ** BEGIN NESTED EXCEPTION ** java.io.FileNotFoundException estoredata1.txt (The system cannot find the file specified) STACKTRACE: estoredata1.txt (The system cannot find the file specified) at java.io.FileInputStream.open(Native Method) at java.io.FileInputStream.init(FileInputStream.java:59) at com.mysql.jdbc.MysqlIO.sendFileToServer(MysqlIO.java:1505) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:964) at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:911) at com.mysql.jdbc.Connection.execSQL(Connection.java:1777) at com.mysql.jdbc.Connection.execSQL(Connection.java:1711) at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1293) at Sanity.Restore(Sanity.java:138) at Sanity.main(Sanity.java:70) CODE SNIPPET String filepath = sys:\\restore\\data1.txt; String query = load data local infile \' + filepath + \' into table trail; stmt.executeUpdate(query); what could be wrong here ? First, you do realize that 'load data local infile' means load the file that is on the client and send it to the server. Second, Java is saying it can't open the file 'estoredata1.txt', which either means it doesn't exist at the path you say it does, or the permissions are wrong. Can you load this same file using the MySQL command-line client using the _exact_ same path? -Mark - -- MySQL 2003 Users Conference - http://www.mysql.com/events/uc2003/ For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.1.90 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+Slf+tvXNTca6JD8RAq+YAJ0bV2N62jOE7UsCOL2aOH4A7Y2MnACgiQRw uLPb/oWgEJubdksxBafP6RU= =uzys -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: JDBC and LOAD DATA LOCAL INFILE :
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Ahmed S K Anis wrote: Yes Mark it worked from the command line. The Archived resides at the server side. I ran Load data local infile 'sys:\\mail\\dump.txt into table X it worked. The from a different machine i ran this java program. The Archived file still exists at the DB server side. So i guess local option is not necessary. It didnt work though I guess the path it takes is wrong. String filepath = sys:\\restore\\data1.txt; String query = load data local infile \' + filepath + \' into table x it takes as estoredata1.txt as file name not clear why ? Because '\\' in Java is the escape code for a _single '\' :) You need to get \\ in String literals. What your String actually ends up being is sys:\restore\data1.txt, and '\' also has special meaning in MySQL. -Mark - -- MySQL 2003 Users Conference - http://www.mysql.com/events/uc2003/ For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.1.90 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+Sl0btvXNTca6JD8RAmptAKCqDXC/l8YnSqemI5YbHVhlaC187wCfVzRG OPROaY07+yKvKHHjuwy7uEU= =V70Y -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: JDBC and LOAD DATA LOCAL INFILE :
* Ahmed S K Anis [...] String filepath = sys:\\restore\\data1.txt; String query = load data local infile \' + filepath + \' into table x it takes as estoredata1.txt as file name not clear why ? A double backslash will in many environments be reduced to a single backslash. Your result indicate that you are in such an environment (VBA?), as mysql only sees single backslashes: mysql select sys:\restore\data1.txt; +--+ estoredata1.txt | +--+ estoredata1.txt | +--+ 1 row in set (0.00 sec) Try to double your backslashes twice: String filepath = sys:restoredata1.txt Your programming environment will remove every other backslash, so that mysql sees this: mysql select sys:\\restore\\data1.txt; ++ | sys:\restore\data1.txt | ++ | sys:\restore\data1.txt | ++ 1 row in set (0.00 sec) -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php