Monday, July 8, 2013

Call Sqlcmd using Java

Generate csv from java using sqlcmd command
import java.io.IOException;

public class TestSQLCMD {

 /**
  * Generate CSV Using SQLCMD command
  * @param _SQL
  * @param downloadPath
  * @return
  * @throws IOException
  */
 public static int exportCSVviaCommand(String _SQL, String downloadPath) 
                throws IOException  {
  
  String[] commandArray = {"sqlcmd", 
             "-S", "localhost,1433", //Server and Port
             "-d",  "my_server", // DB Name
             "-U", "sa",  // DB User
             "-P", "password",  // DB Password
             "-Q",  _SQL, // Query
             "-k2", // Removes all control characters from the output
             "-W", //  Remove tabs between columns
             "-f", "65001", // for UTF-8 format
             "-h", "-1", //Remove headers  
             "-s,", // Separator is comma ','
             "-o", downloadPath}; // "" --> output to file
    
  Process process = Runtime.getRuntime().exec(commandArray);
  
  /**
   * causes the current thread to wait, if necessary, until the process
   * represented by this Process object has terminated. This method
   * returns immediately if the subprocess has already terminated. If the
   * subprocess has not yet terminated, the calling thread will be blocked
   * until the subprocess exits. Returns: the exit value of the process.
   * By convention, 0 indicates normal termination.
   */
  int i = -1;
  try {
   i = process.waitFor();
   System.out.println(i);
  } catch (InterruptedException e) {
   e.printStackTrace();
  }

  return i;
 }

 public static void main(String[] args) {
  String sql= " select * from Employee where DeptNo=1  and ID > 10 ";
  try {
   StringBuilder downloadPath = new StringBuilder();
   downloadPath.append("D:\\downloads\\employee.csv");
   exportCSVviaCommand(sql, downloadPath.toString());
  } catch (IOException e) {
   e.printStackTrace();
  }catch (Exception e) {
   e.printStackTrace();
  }
  System.out.println("Done...........");
 }

}

No comments:

Post a Comment