Connection between Java program and mariadb won't work if it is executed via jsch

Question

So I try to start a Java program on a raspberryPi via jsch from my visual studio code on my pc. The program connects to a mariadb database and does a simple Select statement.

If I'm logged in on the raspberryPi, via putty, the connection between the java program and mariadb works fine. If I try to execute the program via another java program with jsch, the program will start but won't execute the line connection = verbindung_datenbank.getConnection(dbURL, user, password); I placed some System.out.println(); lines to find the line where the program stops working. Befor the line it worked, after that it didn't. What I don't understand is, why does the program work if I'm logged in over putty but won't work if a program is loged in via SSH?

Here the full code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.io.*;
import java.sql.*;


public class connection_db {

    public static Connection getConnection(String dbURL, String user, String password) throws SQLException {

        Connection connection = DriverManager.getConnection(dbURL, user, password);
        connection.setAutoCommit(true);
        return connection;
    }

    
    public static void main(String[] args) {

        Connection connection = null;
        Integer aId=null;
        String dbURL = "jdbc:mariadb://192.168.178.39:3306/temperature";
        String user =  "pi";
        String password = "test";

        try {
            connection = verbindung_datenbank.getConnection(dbURL, user, password);
            String query = "SELECT MAX(id) FROM table1" ;
            System.out.println(query);
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(query);
                
                while(rs.next())
                {
                    Integer id = rs.getInt("MAX(id)");
                    System.out.println(id);
                    aId = id;
                    
                }
            
            double temperature = -15;

            String query2 = "UPDATE table1 SET theke_1 = "+temperature +" WHERE id = "+ aId;
            System.out.println(query2);
            Statement stmt2 = connection.createStatement();
                stmt2.executeQuery(query2);
                stmt2.close();
                

            
        } catch (SQLException e) {
           e.printStackTrace();
        } finally {
          // connection.close();
        }

    }
}

Hopefully someone can help me.

Edit: the code from the jsch program:


import java.io.BufferedReader;
import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.nio.charset.StandardCharsets;

    public class main_slave {

        public static void main(String[] args) throws JSchException, InterruptedException, IOException{
            String host = "192.168.178.46";
            String user = "pi";
            String password = "password";
                       
                JSch jsch = new JSch();
                Session session = jsch.getSession(user, host, 22);
                session.setUserInfo(new main_slaveUserInfo(user, password));
                session.connect();
                Channel channel = session.openChannel("exec");
                
                InputStream in = channel.getInputStream();

                ((ChannelExec) channel).setCommand("java 
connection_db");  
                          

                channel.connect();

               BufferedReader reader = new BufferedReader(new InputStreamReader(in));

               String line;
               while((line = reader.readLine()) != null) {
                   System.out.println(line);
               }
            
                channel.disconnect();
                session.disconnect();
            
          


                
            
        }
    }```

"If I try to execute the program via another java program with jsch..." where is the code for this other program that uses jsch? And what happens when your code reaches the `getConnection()` line? You say it stops working, but what actually happens? Do you get an error message or an exception? What does the error say? — Kenster, Dec 28 '21 at 03:10
Hello, added the code and the second point is my problem. I don't get any message or exception. The only reason I know, that something happens at that line is, that I placed a `System.out.println("1")` befor and `System.out.println("2")` after the line and got only the 1 as a terminal output. — nordisch, Dec 28 '21 at 07:15
haven't heard about plink, will try it out. The new line isn't in my program, don't know why stackoverflow show it this way. — nordisch, Dec 28 '21 at 09:20
I tried to connect over another pi via a ssh script and now I get the message: No suitable driver found for jdbc:mariadb.... So could it be, that it won't use the Classpath from the .bashrc? That would explain why it doesn't work — nordisch, Dec 28 '21 at 16:57

score 0 · Accepted Answer · answered Dec 29 '21 at 13:54

So the tip from Martin Prikryl was right. Jsch uses different bash files and so my program didn't work. My solution: I programmed a small bash script:

#!/bin/bash
export CLASSPATH=/home/pi/Documents/Java/mariadb-java-client-2.7.3.jar:.

java connection_db

Than I changed the execute command from java connection_db to the bash script and now everything works fine.

Connection between Java program and mariadb won't work if it is executed via jsch

1 Answers1