By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,016 Members | 2,265 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,016 IT Pros & Developers. It's quick & easy.

Using INSERT to write to a directory

P: n/a
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.

Feb 24 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
bb****@gmail.com 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
Feb 24 '06 #2

P: n/a
<bb****@gmail.com> wrote in message
news:11**********************@z34g2000cwc.googlegr oups.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.
Feb 24 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.