Using INSERT to write to a directory

[bballr]

I have a problem that I'm not sure if it can be done. I'm trying to
use the MySQL C API to be able to use a normal sql insert statement
that will send the data or file to a directory and NOT the database.
I'm not sure if there is anything out there that will allow this, but
I've searched everywhere, and I have found no solution. If anyone has
any work around solutions, I also welcome those. Thanks for the help.

[noone]

bb****@gmail.co m wrote:

I have a problem that I'm not sure if it can be done. I'm trying to
use the MySQL C API to be able to use a normal sql insert statement
that will send the data or file to a directory and NOT the database.
I'm not sure if there is anything out there that will allow this, but
I've searched everywhere, and I have found no solution. If anyone has
any work around solutions, I also welcome those. Thanks for the help.

just curious, but why not the db...

In most other db engines, the way we would accomplish this would be
through a trigger. There are very few hints as to how to write an
external procedure that would be called by the trigger - except to
review the sources for a proc already written - and it is about as clear
as mud.

see docs for exact syntax:
after insert
execute procedure -> move data to external file system
end

[Bill Karwin]

<bb****@gmail.c om> wrote in message
news:11******** **************@ z34g2000cwc.goo glegroups.com.. .

I have a problem that I'm not sure if it can be done. I'm trying to
use the MySQL C API to be able to use a normal sql insert statement
that will send the data or file to a directory and NOT the database.

I would strongly recommend to use a program intended for remote file
manipulation, such as scp or sftp, instead of using the MySQL API.

I was also about to ask why you want do this. There is already a perfectly
nice set of functions to write to files and directories from C. fopen,
fwrite, etc. But then I realized that one reason to use the MySQL API is to
be able to write to files on the host running mysqld, instead of the local
host where your C code is executing. This circumvents normal network
authentication, and uses the MySQL login to gain access to the filesystem on
the server host. This is huge security hole! You should not do this.

There are a couple of alternatives you can do with MySQL. You can use the
CSV storage engine
(http://dev.mysql.com/doc/refman/5.0/...e-engine.html), and create
a table to which to write the data. The table will be stored in a file
"tablename. CSV" under the MySQL data directory. This allows you to use
INSERT statements to store alphanumeric data in a file on the server, but
you are limited to the directory location of the file, and the data format
inside it.

Another option is to use SELECT ... INTO OUTFILE instead of INSERT. You can
specify the directory location of the file, and the data format within the
file is more easily specified (using similar arguments as used by LOAD DATA
INFILE). But there are necessary limitations with this solution too. The
file cannot already exist (otherwise an attacker could clobber /etc/passwd
or other important files). The file is created as world-writeable. See
coverage of this feature on
http://dev.mysql.com/doc/refman/5.0/en/select.html.

Regards,
Bill K.