How to execute an “insert into” query to a remote database with channel.connect();

Question

This is the java code I have tried to insert into my remote database table.

session.connect();
Channel channel = session.openChannel("exec");

String query = "INSERT INTO table_name
(id,serialno,userid,checktime,checktype,eventType) 
VALUES(502,1011,0078,'2017-04-17 17:27:51',6,23)";

((ChannelExec) channel).setCommand("mysql -uuser -ppwd -h localhost -e'" + 
query + "' database_name");
    InputStream in = channel.getInputStream();
    channel.connect();

I used the same code to execute "select *" query, instead for "Insert query" it executed perfectly and I got the output. But in this case it doesn't. Please help me find a solution.

Can you clarify what you mean when you say, "in this case it doesn't". What were you expecting? Did you get an error response? Did you run a query to see whether your insert worked? — BPS, Apr 28 '17 at 17:54
@BPS :this is my question - How to execute an “insert into” query to a remote database with channel.connect(); I didn't get any error while execute the query for insert. But the "insert" not reflected in the remote database. I am expecting a perfect query to insert into remote database — shameem at, May 03 '17 at 06:25
@BPS :Why you give down-vote for no reason? If you read my question top to bottom with Head Line, you could be easily understand. — shameem at, May 03 '17 at 07:10
Welcome to Stack Overflow! Please read http://stackoverflow.com/help/how-to-ask and other community guidelines. People here want you to get help even if they can't answer your question. Helping you to improve your question is one way that happens. — BPS, May 03 '17 at 20:39

Martin Prikryl · Answer 1 · 2017-05-03T06:40:40.207

0

Did you try the same command on command-line?

mysql -uuser -ppwd -h localhost -e'INSERT INTO table_name (id,serialno,userid,checktime,checktype,eventType) VALUES(502,1011,0078,'2017-04-17 17:27:51',6,23)' database_name

It would not work either. The quotes in the INSERT command conflict with the quotes on the command-line, that wrap the SQL command.

One way is to use double-quotes on the command-line:

mysql -uuser -ppwd -h localhost -e"INSERT INTO table_name (id,serialno,userid,checktime,checktype,eventType) VALUES(502,1011,0078,'2017-04-17 17:27:51',6,23)" database_name

Then in Java, you have to escape those double quotes as \", because Java uses double-quotes to delimit the string.

edited May 03 '17 at 06:40

answered Apr 28 '17 at 13:19

Martin Prikryl

188,800
56
490
992

Sorry, I have tried(with backslash) the same code in command-line and also in java.It didn't work. – shameem at May 03 '17 at 06:37
You are right. One cannot escape single quotes the way I suggested. I've fixed my answer. – Martin Prikryl May 03 '17 at 06:41

kuttan pillai · Accepted Answer · 2017-05-03T07:10:53.930

0

You Need to escape double quotes With \" , because double quotes are used as delimiters in Java.

String query = "INSERT INTO table_name
(id,serialno,userid,checktime,checktype,eventType) 
VALUES(502,1011,0078,'2017-04-17 17:27:51',6,23)"                

((ChannelExec) channel).setCommand("mysql -uroot -proot -h localhost -e\"" + query + "\" yourdbname");

edited May 03 '17 at 07:10

answered May 03 '17 at 06:31

kuttan pillai

118
1
15

How to execute an “insert into” query to a remote database with channel.connect();

2 Answers2