How to execute SQL file in remote server using ANT

Question

I want to execute .sql file at /x/y/z location of a UNIX server, using ANT script from my local Windows XP system. The .sql files are at UNIX server.

I have used the below ant target from my Windows XP system:

  <target name="execute" >
    <sshexec host="hostname" 
    username="UNIX system username" 
    keyfile="/export/home/appldev/.ssh/id_dsa" 
    commandResource="psql -f /u01/appldev/devappl/po/11.5.0/reports/xxbt/1.0.0/sql"/>
  </target>

I have tried the below post keyfile error:

  <target name="execute" >
    <sshexec host="server_ip"
    username="server_uname" 
    password="server_pass" 
    command="touch abc"/>
  </target>

Got the error:

Buildfile: C:\Program Files\Java\apache-ant-1.8.1\build.xml

execute:
  [sshexec] Connecting to server_IP:22

BUILD FAILED
C:\Program Files\Java\apache-ant-1.8.1\build.xml:49: com.jcraft.jsch.JSchExcepti
on: connection is closed by foreign host

Please help.

Answer 1

Use the Ant sql task.

<sql
    driver="org.database.jdbcDriver"
    url="jdbc:database-url"
    userid="sa"
    password="pass" >
  <transaction  src="data1.sql"/>
  <transaction  src="data2.sql"/>
  <transaction  src="data3.sql"/>
  <transaction>
    truncate table some_other_table;
  </transaction>
</sql>

Edit:

To make the file accessible from your Windows XP box, you have lots of options. Which you choose depends on security settings, and how you feel about Ant prompting for passwords or storing passwords in a file on the file system.

1. You can expose the UNIX directory using Samba. Then you can mount that as a drive in Windows.
2. Expose the file using an FTP server on the UNIX side. Then use the FTP task to copy the file.
3. Use the scp task to copy the file using SSH.

Another alternative is to use the sshexec task to invoke the database client on the UNIX server.

<sshexec host="unix-host"
  username="dbuser"
  keyfile="${user.home}/.ssh/id_dsa"
  commandResource="psql -f /x/y/z"/>