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=gcdmg-mysql-2@m.gmane.org

RE: Select Into OUTFILE problem

--_000_30B3DF511CEC5C4DAE4D0D290504753413336567D4AAApmgiloca l_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hi Bruce,



SELECT ... INTO OUTFILE always creates the file local to the database serve=
r. If you want to dump results where your perl script is running you'll ha=
ve to use another method such as receiving the results of the query normall=
y and writing the file in the perl script.



Regards,

Gavin Towey





I have a bit of perl code that ends with an error:



$sql=3D"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 =3D $dbh->prepare($sql);

$rv =3D $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 c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e contact the sender by reply email and destroy all copies of the original =
message.

--_000_30B3DF511CEC5C4DAE4D0D290504753413336567D4AAApmgiloca l_--

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=gcdmg-mysql-2@m.gmane.org