Can select ... into outfile not save it into a file, but save it in blob instead

Question

I want to do a select * from x into outfile 'c:/test.csv'.
But instead of saving into an outfile test.csv I want to save it into a blob field.

I'm starting the query from a client on Windows.
The MySQL server is on a server on Windows or Linux (can be both).
But I want to have the file client-side, not somewhere on the server.

BTW
The client software in written in Delphi 2007 and uses ZEOS to connect to the MySQL database on a remote server.

How do I get the outfile client side, instead of server side?

I don't understand. You start out asking how to save query results into a blob field *instead of a file*, but then you go on to ask how to get a file on the client side. Do you want a file or not? Please edit your question to clarify. — Rob Kennedy, Apr 20 '11 at 13:55
For now I used DBAdvGrid.OutputToCSV *(Had to change the sourcecode of TAdvStringGrid a bit to prevent it from exporting a header row, but it works)* — Johan, Apr 20 '11 at 20:52
Johan, if that's the solution to your problem, then please write an *answer* describing it. That's where other people will look if they encounter a similar problem; mere comments even get *hidden* after some time. — Rob Kennedy, Apr 20 '11 at 20:56

RRUZ · Answer 1 · 2011-04-20T13:11:13.217

Johan, MySql executes this sentence from the server where is running. the only way to create the file in the client side is passing a shared folder location and file name which points to the client machine. also the MySQL service (daemon) owner must possess adequate privileges to write to the target directory.

from the Mysql Documentation

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 other host than the server host, you normally cannot use SELECT ... INTO OUTFILE since there is no way to write a path to the file relative to the server host's file system.

However, if the MySQL client software is installed on the remote machine, you can instead use a client command such as mysql -e "SELECT ..." > file_name to generate the file on the client host.

It is also possible to create the resulting file on a different host other than the server host, if the location of the file on the remote host can be accessed using a network-mapped path on the server's file system. In this case, the presence of mysql (or some other MySQL client program) is not required on the target host.

score 1 · Accepted Answer · answered Apr 20 '11 at 21:01

OK In case people want to know I made a workaround to the the TMS DBAdvGrid to export the CSV file.

I added a new property to TAdvStringGrid

public {properties}
property HideCSVHeader: boolean read FHideCSVHeader write FHideCSVHeader;

and changed the following code:

procedure TAdvStringGrid.OutputToCSV(FileName:String;appendmode: Boolean; 
  Unicode: boolean);
....
//changed this code further down the procedure:
//for z := SaveStartRow to SaveEndRow do 
//Into:

MyStartRow:= SaveStartRow;
if HideCSVHeader then Inc(MyStartRow);
for z := MyStartRow to SaveEndRow do

Then when I call

procedure TForm1.BtnExportClick(Sender: TObject);
var
  Filename: string;
  succes: Boolean;
begin
  succes:= True;
  if ExportSaveDialog.Execute then begin
    Filename:= ExportSaveDialog.FileName;
    try
      DBGridExportExact.Delimiter:= ';';
      DBGridExportExact.AlwaysQuotes:= True;
      DBGridExportExact.QuoteEmptyCells:= True;
      DBGridExportExact.SaveHiddenCells:= True;
      DBGridExportExact.HideCSVHeader:= True;
      DBGridExportExact.SaveToCSV(bestandsnaam);
    except
      succes:= False;
    end;
    if not(succes) then StatusLabel.Caption:= 'Error bla bla';
  end;
end;

Can select ... into outfile not save it into a file, but save it in blob instead

2 Answers2