0

I was trying to use Java to create a program that would allow me to insert, update, and delete entries on a MariaDB database running on a Pi 4 through the use of SSH from a remote session. This isn't something I have done before, as my database experience has been with XAMPP locally, but I wanted to see if I couldn't allow remote access, since it would be useful for this project. Currently, my code is set up to allow a user to operate a GUI to input data (the other functionality will come later), and is as follows:

   public void actionPerformed(ActionEvent event) {
      
         if(event.getSource() == submitButton){
      
         String name = userField.getText();
      
         String email = emailField.getText();
      
         String system = systemField.getText();
      
         String serial = serialField.getText();
      
         String date = dateField.getText();
      
                  
         //SSH and DB insanity begins here
      
          int localPort = 6000;
      
          String sshUser ="";
      
          String sshHost = "";
      
          String dbHost = "192.168.1.242";
      
          int dbPort = 3306;
      
          String dbUser = "root";
      
          String dbPassword = "wacky12";
          
          String driver = "com.mysql.jdbc.Driver";
          
          try{
      
         JSch jsch = new JSch();
      
         jsch.addIdentity("~/.ssh/id_rsa");
      
         Session session = jsch.getSession(sshUser, sshHost, 22);
         
         session.connect();
         
         int forwardedPort = session.setPortForwardingL(0, dbHost, dbPort);
         
         String url = "jdbc:mysql://localhost:" + forwardedPort;
         
         Connection connection = DriverManager.getConnection(url, dbUser, dbPassword);
         
         Statement stmt = connection.createStatement();
      
         String insert = "insert into LaptopReturns('" + name + "' , '" + email + "' , '" + system + "' , '" + serial + "' , '" + date + "')";
         
         stmt.execute(insert);
         
         System.out.print("Done!");

         
      }catch (Exception e) {
         e.printStackTrace();
         
      }

I fully realize that I am probably doing this wrong, but it doesn't seem to be connecting to the Pi at all when the submit button is hit. Is anyone able to give me some pointers? As mentioned I have no real experience with JSch. Also, would it be easier to have it connect to the server via SSH initially at run, or as needed when the button is pressed?

jarlh
  • 42,561
  • 8
  • 45
  • 63
SierraArgo
  • 1
  • 2
  • Can you debug your code and see where it is stuck? At connecting to SSH server or during connection to the database? In terms of reactivity you will be better off keeping the tunnel open at startup as no ssh login is required each time. But, if it is your network - just configure MariaDB to listen on 192.168.1.255 (which could be a broadcast address in a /24 subnet, please check the ip) directly so you don't need a tunnel at all. – Mandraenke Oct 22 '21 at 05:45
  • So, I had wanted to use SSH to allow for a user to connect to the database from outside the immediate network. – SierraArgo Oct 22 '21 at 05:54
  • jdbc protocol is not the ssh protocol. Or are you trying to do [access a mysql server running on a remote machine and listening on let's say port 3306 through a SSH tunnel](https://stackoverflow.com/questions/1968293/connect-to-remote-mysql-database-through-ssh-using-java) – Scary Wombat Oct 22 '21 at 05:54
  • That's correct. I had been trying to use ssh to connect to a database, and then make changes. – SierraArgo Oct 22 '21 at 05:58
  • so what error are you getting? – Scary Wombat Oct 22 '21 at 06:16
  • Move the ssh tunnel management outside your app. – Thorbjørn Ravn Andersen Oct 22 '21 at 06:16
  • Gah, stupid mistake on my part. Action listener wasn't set on the button. Now it's throwing this error: com.jcraft.jsch.JSchException: java.io.FileNotFoundException: C:\Users\kenda\.ssh\id_rsa (The system cannot find the path specified) – SierraArgo Oct 22 '21 at 06:20

0 Answers0