title: mysql batch linux programming
body: <p>I have a table named my_table that contains rows for hash_value, file_name, file_contents, and id.</p>
  
  <p>I have a program that accepts a hash value from the user.</p>
  
  <p>I would like to take this value and create a mysql batch that will dump the file out to file_name</p>
  
  <p>I can create a batch to dump the file contents using</p>
  
  <pre><code>SELECT file_contents INTO DUMPFILE 'myfilename' FROM my_table WHERE hash_value='myhashvalue';
  </code></pre>
  
  <p>but I have not been able to find a way to get file_name first and use that in the command.
  I would also like a way to let my program know if the batch failed and why (no such hash in table, file exists already, etc)</p>
  
  <p>pseudo code</p>
  
  <pre><code>@file_name = SELECT file_name FROM my_table WHERE hash_value='myhashvalue';
  if @file_name is not empty
      SELECT file_contents INTO DUMPFILE @file_name FROM my_table WHERE hash_value='myhashvalue';
      // if file exists already then the error gets sent to stdout so I can determine that
  else
      echo something to stdout so I know the user entered a value not in the table
  endif
  </code></pre>
  
  <p>I am running this batch in the followng manner</p>
  
  <pre><code>mysql -h localhost -u theuser -pthepassword mydatabase &lt; mybatch.sql
  </code></pre>
  
  <p>Thank you</p>
  
  <p><strong>EDIT:</strong>
  I am getting closer.  I am able to set the file name using  </p>
  
  <p><code>SELECT @filename:=file_name FROM my_table WHERE hash_value='myhahsvalue';</code></p>
  
  <p>but I get an error when trying to use this in my next statement
  <code>SELECT file_contents INTO DUMPFILE @filename FROM my_table WHERE hash_value='myhashvalue';</code></p>
  
  <p>results in <strong>You have an error in your SQL syntax;</strong></p>
  
  <p><strong>EDIT 2:</strong>
  I can now get the file name (if it exists based on the hash) using this.  I am still working on the error reporting part, but this works well</p>
  
  <pre><code>SELECT @filename:=file_name FROM my_table WHERE hash_value='myhashvalue';
  SET @query:=CONCAT("SELECT file_contents INTO DUMPFILE '",@filename,"' FROM mytable WHERE hash_value='myhashvalue'");
  PREPARE stmt FROM @query;
  EXECUTE stmt;
  </code></pre>