Re: Select Into OUTFILE problem
Thanks all who replied. After I posted I kept looking and found it... Also had folks point it out to me. Your suggestion is what I ended up doing. Bruce Gavin Towey wrote: > Hi Bruce, > > > > SELECT … INTO OUTFILE always creates the file local to the database > server. If you want to dump results where your perl script is running > you’ll have to use another method such as receiving the results of the > query normally and writing the file in the perl script. > > > > Regards, > > Gavin Towey > > > > > > I have a bit of perl code that ends with an error: > > > > $sql="SELECT convert_tz( a.stamp,'GMT','$tz' ) as ts, a.status, > > a.reason, a.tl > > INTO OUTFILE '/application/result.csv' > > FIELDS TERMINATED BY ',' > > ENCLOSED BY '\"' > > LINES TERMINATED BY '\n' > > FROM alerts a > > WHERE a.stamp BETWEEN ? AND ? > > ORDER BY a.stamp DESC"; > > > > $sth = $dbh->prepare($sql); > > $rv = $sth->execute; > > > > DBD::mysql::st execute failed: Can't create/write to file > > '/application/result.csv' (Errcode: 2) > > > > Te database is remote from the system where the perl is executing. > > > > Te SQL works as expected when fed to mysql command line client > > (i.e. mysql -h remote). The outfile ends up in the application > > directory of the macine running the mysql client. > > > > What I'd found is, when the perl code runs the file tries to drop on the > > database server and the application directory doesn't exist there giving > > me the error. > > > > Any suggestions to get the outfile to drop in the right place would be > > appreciated. > > > > Bruce > > > > > > > > The information contained in this transmission may contain privileged > and confidential information. It is intended only for the use of the > person(s) named above. If you are not the intended recipient, you are > hereby notified that any review, dissemination, distribution or > duplication of this communication is strictly prohibited. If you are not > the intended recipient, please contact the sender by reply email and > destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Select Into OUTFILE problem
Hi Bruce, SELECT ... INTO OUTFILE always creates the file local to the database server. If you want to dump results where your perl script is running you'll have to use another method such as receiving the results of the query normally and writing the file in the perl script. Regards, Gavin Towey I have a bit of perl code that ends with an error: $sql="SELECT convert_tz( a.stamp,'GMT','$tz' ) as ts, a.status, a.reason, a.tl INTO OUTFILE '/application/result.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' FROM alerts a WHERE a.stamp BETWEEN ? AND ? ORDER BY a.stamp DESC"; $sth = $dbh->prepare($sql); $rv = $sth->execute; DBD::mysql::st execute failed: Can't create/write to file '/application/result.csv' (Errcode: 2) Te database is remote from the system where the perl is executing. Te SQL works as expected when fed to mysql command line client (i.e. mysql -h remote). The outfile ends up in the application directory of the macine running the mysql client. What I'd found is, when the perl code runs the file tries to drop on the database server and the application directory doesn't exist there giving me the error. Any suggestions to get the outfile to drop in the right place would be appreciated. Bruce The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
Select Into OUTFILE problem
I have a bit of perl code that ends with an error: $sql="SELECT convert_tz( a.stamp,'GMT','$tz' ) as ts, a.status, a.reason, a.tl INTO OUTFILE '/application/result.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' FROM alerts a WHERE a.stamp BETWEEN ? AND ? ORDER BY a.stamp DESC"; $sth = $dbh->prepare($sql); $rv = $sth->execute; DBD::mysql::st execute failed: Can't create/write to file '/application/result.csv' (Errcode: 2) Te database is remote from the system where the perl is executing. Te SQL works as expected when fed to mysql command line client (i.e. mysql -h remote). The outfile ends up in the application directory of the macine running the mysql client. What I'd found is, when the perl code runs the file tries to drop on the database server and the application directory doesn't exist there giving me the error. Any suggestions to get the outfile to drop in the right place would be appreciated. Bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: SELECT... INTO OUTFILE problem
"Jay Blanchard" <[EMAIL PROTECTED]> wrote: > [snip] > Exception occured in Microsoft OLE DB Provider for ODBC Drivers, > [MySQL][ODBC 3.51 Driver][mysqld-4.0.20a-nt]Can't create/write to file > 'C:\data_out\day234\data_out.txt' (Errcode: 2) [EMAIL PROTECTED] egor]$ perror 2 System error: 2 = No such file or directory -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT... INTO OUTFILE problem
As Jay said, it's either a permission issue or the file already exists. MySQL will not overwrite a file that already exists. -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Thursday, October 21, 2004 3:35 PM To: Mysql General (E-mail) Subject: RE: SELECT... INTO OUTFILE problem [snip] Exception occured in Microsoft OLE DB Provider for ODBC Drivers, [MySQL][ODBC 3.51 Driver][mysqld-4.0.20a-nt]Can't create/write to file 'C:\data_out\day234\data_out.txt' (Errcode: 2) So I copy the query statement and send via PhpMyAdmin, it ended up as below #1 - Can't create/write to file 'C:\data_out\day234\data_out.txt' (Errcode: 2) So it is not a problem of Labview BUT my SQL statement... [/snip] It is a permissions problem, make sure that directory is writable. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT... INTO OUTFILE problem
The directory is being created by Labview and data is written out by MySQL. Can it be this? I am using windows 2000. And that reminded me, do I have to create the directory that I will output the file? Or, MySQL will automaticly create it if not exits??? Regards.. - Original Message - From: "Jay Blanchard" <[EMAIL PROTECTED]> To: "Mysql General (E-mail)" <[EMAIL PROTECTED]> Sent: Thursday, October 21, 2004 10:35 PM Subject: RE: SELECT... INTO OUTFILE problem [snip] Exception occured in Microsoft OLE DB Provider for ODBC Drivers, [MySQL][ODBC 3.51 Driver][mysqld-4.0.20a-nt]Can't create/write to file 'C:\data_out\day234\data_out.txt' (Errcode: 2) So I copy the query statement and send via PhpMyAdmin, it ended up as below #1 - Can't create/write to file 'C:\data_out\day234\data_out.txt' (Errcode: 2) So it is not a problem of Labview BUT my SQL statement... [/snip] It is a permissions problem, make sure that directory is writable. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT... INTO OUTFILE problem
[snip] Exception occured in Microsoft OLE DB Provider for ODBC Drivers, [MySQL][ODBC 3.51 Driver][mysqld-4.0.20a-nt]Can't create/write to file 'C:\data_out\day234\data_out.txt' (Errcode: 2) So I copy the query statement and send via PhpMyAdmin, it ended up as below #1 - Can't create/write to file 'C:\data_out\day234\data_out.txt' (Errcode: 2) So it is not a problem of Labview BUT my SQL statement... [/snip] It is a permissions problem, make sure that directory is writable. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT... INTO OUTFILE problem
Hi, I am sending a query to a MySQL server on a network via LabView EXEC QUERY VI. It looks like that SELECT `data1` , `data2` INTO OUTFILE 'C:\\data_out\\day234\\data_out.txt' FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' FROM data_table; What I get as a result is Exception occured in Microsoft OLE DB Provider for ODBC Drivers, [MySQL][ODBC 3.51 Driver][mysqld-4.0.20a-nt]Can't create/write to file 'C:\data_out\day234\data_out.txt' (Errcode: 2) So I copy the query statement and send via PhpMyAdmin, it ended up as below #1 - Can't create/write to file 'C:\data_out\day234\data_out.txt' (Errcode: 2) So it is not a problem of Labview BUT my SQL statement... Do I do something wrong? Regards... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FW: select into outfile problem
Have your script delete it first. Niklas Rudemo wrote: > > Maybe I didn't state the problem clearly enough. > I want write to a file that does exist, > i.e., overwrite the existing file. > > -Original Message- > From: Paul DuBois [mailto:[EMAIL PROTECTED]] > Sent: den 9 april 2001 17:01 > To: Niklas Rudemo > Cc: [EMAIL PROTECTED] > Subject: Re: select into outfile problem > > On Mon, Apr 09, 2001 at 03:48:43PM +0200, Niklas Rudemo wrote: > > I'm trying to select into outfile to a file that already exist. > > This has been discussed before, I know, but the solution suggested > > results in an SQL-error, as shown below. So what's the real solution? > > Select your rows into a temporary table, which will allow you to run > several queries to obtain the rows. Then use SELECT ... INTO OUTFILE > with the temporary table (into a file that doesn't exist). > > > > > Niklas Rudemo > > Virtutech > > > > mysql> select * into outfile 'person.txt' from person; > > Query OK, 0 rows affected (0.00 sec) > > > > mysql> select * into outfile 'person.txt' from person; > > ERROR 1086: File 'person.txt' already exists > > > > mysql> select * into outfile 'person.txt' REPLACE from person; > > ERROR 1064: You have an error in your SQL syntax near 'REPLACE from > person' > > at l > > ine 1 > > - 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
FW: select into outfile problem
Maybe I didn't state the problem clearly enough. I want write to a file that does exist, i.e., overwrite the existing file. -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: den 9 april 2001 17:01 To: Niklas Rudemo Cc: [EMAIL PROTECTED] Subject: Re: select into outfile problem On Mon, Apr 09, 2001 at 03:48:43PM +0200, Niklas Rudemo wrote: > I'm trying to select into outfile to a file that already exist. > This has been discussed before, I know, but the solution suggested > results in an SQL-error, as shown below. So what's the real solution? Select your rows into a temporary table, which will allow you to run several queries to obtain the rows. Then use SELECT ... INTO OUTFILE with the temporary table (into a file that doesn't exist). > > Niklas Rudemo > Virtutech > > mysql> select * into outfile 'person.txt' from person; > Query OK, 0 rows affected (0.00 sec) > > mysql> select * into outfile 'person.txt' from person; > ERROR 1086: File 'person.txt' already exists > > mysql> select * into outfile 'person.txt' REPLACE from person; > ERROR 1064: You have an error in your SQL syntax near 'REPLACE from person' > at l > ine 1 - 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: select into outfile problem
On Mon, Apr 09, 2001 at 03:48:43PM +0200, Niklas Rudemo wrote: > I'm trying to select into outfile to a file that already exist. > This has been discussed before, I know, but the solution suggested > results in an SQL-error, as shown below. So what's the real solution? Select your rows into a temporary table, which will allow you to run several queries to obtain the rows. Then use SELECT ... INTO OUTFILE with the temporary table (into a file that doesn't exist). > > Niklas Rudemo > Virtutech > > mysql> select * into outfile 'person.txt' from person; > Query OK, 0 rows affected (0.00 sec) > > mysql> select * into outfile 'person.txt' from person; > ERROR 1086: File 'person.txt' already exists > > mysql> select * into outfile 'person.txt' REPLACE from person; > ERROR 1064: You have an error in your SQL syntax near 'REPLACE from person' > at l > ine 1 - 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
select into outfile problem
I'm trying to select into outfile to a file that already exist. This has been discussed before, I know, but the solution suggested results in an SQL-error, as shown below. So what's the real solution? Niklas Rudemo Virtutech mysql> select * into outfile 'person.txt' from person; Query OK, 0 rows affected (0.00 sec) mysql> select * into outfile 'person.txt' from person; ERROR 1086: File 'person.txt' already exists mysql> select * into outfile 'person.txt' REPLACE from person; ERROR 1064: You have an error in your SQL syntax near 'REPLACE from person' at l ine 1 - 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