0

Here is what i have so far. But the problem is first I need to tunnel via ssh(port 22) then port forward 3306 to access the database over the ssh connection. I was able to get this far but no luck, can someone please look into it?

import groovy.sql.Sql
package mypackage
import java.sql.*
import com.jcraft.jsch.JSch
import com.jcraft.jsch.Session


// ssh login
def sshHost = ''
def sshUser = ''
def sshPass = ''
def sshPort = 22

// database login
def targetHost = '127.0.0.1'
def targetUser = ''
def targetPass = ''
def targetPort = '3306'

JSch jsch = new JSch();
Session session = jsch.getSession(sshUser, sshHost, sshPort);
session.setPassword(sshPass);
session.setConfig("StrictHostKeyChecking", "no");
System.out.println("Establishing Connection...");
session.connect();
int assinged_port=session.setPortForwardingL(0, targetHost, targetPort);

Connection con = null;
def driver = 'org.mariadb.jdbc.Driver'
def connectionString = 'jdbc:mariadb://localhost:3306/db'
con = DriverManager.getConnection(connectionString, targetUser, targetPass);
Statement st = con.createStatement();
String sql = "select * company "
st.execute(sql);
  • what do you mean by `ssh database`? You should be able to connect using your client libraries with above script. Please clarify. – Rao Nov 08 '16 at 01:35
  • Sorry I meant to say i first need to tunnel via ssh(port 22) then port forward 3306 to access the database over the ssh connection –  Nov 08 '16 at 02:05
  • You mean db is running on remote machine and does not allow remote clients to connect using that port? How do you connect manually? Isn't it possible to open the port? May you want to run the script by connecting to ssh to see if that is connecting? – Rao Nov 08 '16 at 02:08
  • not sure how to approach this but i can do this manually using the mysql workbench using TCP/IP over ssh option - https://dev.mysql.com/doc/workbench/en/wb-mysql-connections-methods-ssh.html –  Nov 08 '16 at 02:29
  • Can you please do one thing? What error do you get when the script is run from soapui? – Rao Nov 08 '16 at 02:39
  • Please check this one - http://stackoverflow.com/questions/1968293/connect-to-remote-mysql-database-through-ssh-using-java – Rao Nov 08 '16 at 08:33
  • The error I am getting is - java.sql.SQLNonTransientConnectionException: Could not connect to address=(host=localhost)(port=3306)(type=master) : Connection refused: connect error at line: 9 –  Nov 08 '16 at 14:10
  • sorry i am not sure. will JSch work with groovy? –  Nov 08 '16 at 16:28
  • Yes of course. Most of the Java code works in groovy as it is. – Rao Nov 08 '16 at 16:46
  • I just updated my code. I was able to get that far but no luck –  Nov 08 '16 at 21:17

0 Answers0