Update MySQL table appending text

Following SQL command can be used to update MySQL table with appending text "text" for field "field".

update table set field=concat('text',field);

Execute procedure in informix

Following command can be used to execute procedure in informix database server.
In informix "dbaccess" shell, execute the following command. This will run the "this_is_it" procedure with three parameters "1", "2" and "3".

execute procedure this_is_it ('1','2','3');

create procedure this_is_it(this CHAR(3), that CHAR(4), other CHAR(3))
select * from inff_charge into temp this_is_it;
end procedure;

Force use IPv4 instead of IPv6 in JAVA

Sometimes, Netbeans IDE can't communicate via HTTP over IPv6. In that cases, you can let IDE to use IPv4 protocol instead of IPv6.

Following JVM option can be used to communicate over IPv4.

-Djava.net.preferIPv4Stack=true

Send nohup.out to different file

Following command can use to redirect nohup.out to different files.

nohup java -jar -Xms32m -Xmx64m  Application.jar  > AppLog.out &

Above command will redirect "Application.jar" output to "AppLog.out" file.

SFTP JAVA Client

"JSch" is a library that can be used for SFTP connection. You have to follow following steps to create SFTP connection. For this secure connection, you need to download "jsch-0.1.50.jar" and add to the project.

1) Create SSH key

GitHub Shell can be used to create a SSH key pair for secure connection.

Reference :- https://github.com/

In Git Shell, execute following commands to create key pair for particular server.

ssh-keygen -t rsa -C your_machine_ip

Above command will create "id_rsa.pub" and "id_rsa" keys for connection between your PC and server.

id_rsa :- Private key of FTP client machine
id_rsa.pub :- Public key of SFTP server machine

Reference :- https://help.github.com/articles/generating-ssh-keys

2) Write code snip to create connection and store file

import com.jcraft.jsch.Channel;
import com.jcraft.jsch.ChannelSftp;
import com.jcraft.jsch.JSch;
import com.jcraft.jsch.JSchException;
import com.jcraft.jsch.Session;
import com.jcraft.jsch.SftpException;

public class SFTP {

    public static void main(String[] args) throws SftpException {

Session session = null;
        Channel channel = null;

        try {
            JSch ssh = new JSch();
            session = ssh.getSession("sujith", "192.168.2.6", 22);
            session.setPassword("sujith");

            String identityfile = "d:/.ssh/id_rsa";
            ssh.setKnownHosts("d:/.ssh/id_rsa.pub");
            ssh.addIdentity(identityfile);

            session.setConfig("PreferredAuthentications",
"publickey,keyboard-interactive,password");
            session.connect();

            channel = session.openChannel("sftp");
            channel.connect();
            ChannelSftp sftp = (ChannelSftp) channel;

            sftp.put("d:/test.pdf", "/apps/home/sujith/");
            
        } catch (JSchException e) {
            e.printStackTrace();
        } catch (SftpException e) {
            e.printStackTrace();
        } finally {
            if (channel != null) {
                channel.disconnect();
            }
            if (session != null) {
                session.disconnect();
            }
        }
    }
}

Grouping data by SQL expression

Following SQL syntax can be used to group data using expression.

select date(added_time) as a,count(*) from test_table group by a                
This will group data by date and display its count. It doesn't work grouping with expression, So need to use alias naming for expression.

JAVA Resultset metadata information

In JAVA, you can use retrieve information about resultset using following code.

String str ="select * from table001";

ResultSet rs = st.executeQuery(str);
ResultSetMetaData rsmd = rs.getMetaData();

System.out.println("Number of columns - " + rsmd.getColumnCount());
System.out.println("JAVA data type for Table field 3 - "+rsmd.getColumnClassName(3));
System.out.println("Database Table field 3 type - "+rsmd.getColumnTypeName(3));