"select * from abc into outfile" local file (not

[mparak]

Dear Friends

I am using MySQL and am able to issue the following:

select * from `setup_new` into outfile "c:\_setup.txt"

The server is remote and the text file is on the server (somewhere).
How do I type the path so that the resulting file is on my PC, not on the remote server?

If I use mysqldump the file is created locally,
why can't I do this from within my application? or Rather How can I do the same from within my app with Zeos.

I find that I can use the mysql.exe
c:\mysql\bin\mysql.exe -B -C --database=databasename --verbose --delimiter=, -f -remoteserver --column-names -ppassword -P3306 --protocol=tcp -uusername --connect_timeout=100 -e"select * from `setup_new`" > c:\_setup.txt

With this method I am able to achieve what I need but I do have to execute this from the DOS command line using a batch file, and find this a drag.


When I am able to find the file locally, I will then

LOAD DATA LOCAL INFILE "c:\_setup.txt" INTO TABLE `setup_new`

If somebody out there can help, i would be very grateful.

Regards
M.Parak
Durban South Africa

[mdaems]

Hi mparak.

I think I have bad news for you.
The mysql reference is quite clear on this topic:

The SELECT ... INTO OUTFILE statement is intended primarily to let you very quickly dump a table to a text file on the server machine. If you want to create the resulting file on some client host other than the server host, you cannot use SELECT ... INTO OUTFILE. In that case, you should instead use a command such as mysql -e "SELECT ..." > file_name to generate the file on the client host.

So, using zeos, I think you should write the file yourself. If you look for the right syntax in an example file that should not be a big problem, I suppose. Just iterate the resultset of a query and add some quotes, insert statements, ...
You can also consider to create a mysqldump/import script from your application and run that script from your app. If you need feedback from the script in the program that's a little bit difficult. If the user knows what happens it can be sufficient to show the dos box and let the user close it after he saw the output.

Mark

[mparak]

Dear Mark,

I too found the same bad news on the MySQL forums, just this minute.
Thanks anyway.
The option of iterating through the records is not available as the data I am moving is huge.
MySQLDump works fine, but hangs the server if dumping large tables that are being edited, as it places a lock on the table.
MySQL.exe command line seems to be my best bet. I was hoping for some "silver bullet" "hack" that could let me do from within what I can from mysql.exe command line.

Thanks anyway.
Mo

[mparak]

Dear Mark,

I was just going through this old post when I realised that the new "Federated" storage engine makes this entire process possible.

The outfile that is produced would be on the local pc's drive instead of the server's hard drive.

Regards

M Parak

[mdaems]

Nice trick... You would need a server running locally, however. Possible using the embedded server? (Not even a clue if that would work )