Executing java program on redshift

Question

For example I have coded the following php code and run on redshift like below.

<?php

$connect = pg_connect("host=xx.xxxx.us-east-1.redshift.amazonaws.com port=xxxx dbname=testdb user=xxxx password=xxxxxx");

$query = "select name, email from mytable LIMIT 7";
$result = pg_query($connect,$query);
while(($row = pg_fetch_array($result)) != null) {
        $name = $row[0];
        $email = $row[1];

        echo $name." <<>> ".$email." <<>> ";
}
?>

I saved the above php file and executed in the below way.

myuser@ip-20-143-43-144:/home/myuser$ php runquery.php     <Enter>

Then it gives me the output.

I want to test the same thing with java. So I took the example from the aws and saved in the same location.

public class ConnectToCluster {

    static final String dbURL = "jdbc:postgresql://xxxx.xxxxx.us-east-1.redshift.amazonaws.com:xxxx/testdb";
    static final String MasterUsername = "XXXX";
    static final String MasterUserPassword = "XXXXXX";

    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        try{
           //Dynamically load postgresql driver at runtime.
           Class.forName("org.postgresql.Driver");

           //Open a connection and define properties.
           System.out.println("Connecting to database...");
           Properties props = new Properties();

           //Uncomment the following line if using a keystore.
           //props.setProperty("ssl", "true");  
           props.setProperty("user", MasterUsername);
           props.setProperty("password", MasterUserPassword);
           conn = DriverManager.getConnection(dbURL, props);

           //Try a simple query.
           System.out.println("Listing system tables...");
           stmt = conn.createStatement();
           String sql;
           sql = "select * from information_schema.tables;";
           ResultSet rs = stmt.executeQuery(sql);

           //Get the data from the result set.
           while(rs.next()){
              //Retrieve two columns.
              String catalog = rs.getString("table_catalog");
              String name = rs.getString("table_name");

              //Display values.
              System.out.print("Catalog: " + catalog);
              System.out.println(", Name: " + name);
           }
           rs.close();
           stmt.close();
           conn.close();
        }catch(Exception ex){
           //For convenience, handle all errors here.
           ex.printStackTrace();
        }finally{
           //Finally block to close resources.
           try{
              if(stmt!=null)
                 stmt.close();
           }catch(Exception ex){
           }// nothing we can do
           try{
              if(conn!=null)
                 conn.close();
           }catch(Exception ex){
              ex.printStackTrace();
           }
        }
        System.out.println("Finished connectivity test.");
     }
 }

I saved this .java file too in the same location. So, can someone please tell me how to run this java file on command as I did for php.

This isn't really an redshift related question. What you are asking is ["How to run Java program in command prompt"](http://stackoverflow.com/questions/11965818/how-to-run-java-program-in-command-prompt) — Tomasz Tybulewicz, Jan 20 '14 at 12:51

score 1 · Accepted Answer · answered Jan 20 '14 at 21:24

1

(1) Download a postgresql JDBC driver from the following link and put it in the same directory of ConnectToCluster.java.

http://jdbc.postgresql.org/download/postgresql-8.4-703.jdbc4.jar

(2) Add the missing lines(import java library) in the head of the java file.

 import java.sql.*;
 import java.util.*;

 public class ConnectToCluster {
 ...

(3) Run the following command.

 javac ConnectToCluster.java && java -cp .:postgresql-8.4-703.jdbc4.jar ConnectToCluster

answered Jan 20 '14 at 21:24

Masashi M

2,679
21
22

Thank you so much for your answer. Do I need to compile every time? I want this program to be run everyday at particular time, I heard that cronjob would do this. If you could suggest on this, I would be thankful. – rick Jan 21 '14 at 05:59
No, once you compiled the java file, you don't need to compile it every time. Speaking of cronjob, you need to change the classpath(cp option) to use the absolute path like "-cp /path/to/your-java-file-dir/:/path/to/your-java-file-dir/postgresql-8.4-703.jdbc4.jar" because the current directory of cronjob is different. – Masashi M Jan 21 '14 at 06:31
Okay, thanks. I have one more jar file to be included in that project, do I need to use comma as separator and continue? Like -cp.:postgresql-8.4-703.jdbc4.jar, anotherjarfile.jar – rick Jan 21 '14 at 08:54
I got it worked by compiling and executing it in two separate commands. Like javac -cp.:one.jar:two.jar and java -cp.:one.jar:two.jar – rick Jan 21 '14 at 09:13

Executing java program on redshift

1 Answers1