/*
 * query31.java
 *
 * Created on March 13, 2006, 2:52 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

/**
 *
 * @author kottha
 */
import java.sql.*;

import uk.org.ogsadai.client.Util;
import uk.org.ogsadai.client.toolkit.security.wsrf.* ;
import uk.org.ogsadai.client.toolkit.activity.ActivityRequest;
import uk.org.ogsadai.client.toolkit.activity.sql.SQLQuery;
import uk.org.ogsadai.client.toolkit.activity.sql.WebRowSet;
import uk.org.ogsadai.client.toolkit.service.DataService;
import uk.org.ogsadai.client.toolkit.sessions.*;
import uk.org.ogsadai.client.toolkit.* ;
import uk.org.ogsadai.client.toolkit.wsrf.WSRFDataTransport ;
import uk.org.ogsadai.client.toolkit.activity.* ;
import uk.org.ogsadai.client.toolkit.activity.delivery.OutputStreamActivity;
import java.net.URL ;

public class query31 
{
    //register transport security
    static
    {
        Util.registerTransport();
    }
    public static void main(String[] args) throws Exception
    {
        
        if(!Util.isRegistered())
        {
            System.out.println(Util.getRegistrationErrorMessage() + Util.getRegistrationException());
            return;
        }

        // URL of data service
        DataService service = GenericServiceFetcher.getInstance().getDataService(
                "https://romeo.urz.tu-dresden.de:8443/wsrf/services/ogsadai/MyService","MyPQSPSIMAP169");
        //Setup encryption
        GSITransportEncryptionProperty enc = new GSITransportEncryptionProperty();
        service.setConnectionProperty(enc); 
        //service.setConnectionProperty(new GSITransportEncryptionProperty());        
        System.out.println("Ready to connect to secure data service at " + service.getURL());

        //start new session
        ActivityRequest request = new ActivityRequest();
        request.setSessionRequirements(new JoinNewSession());
        Response response = service.perform(request);
        Session session = response.getSession();

         // construct the SQL query
        SQLQuery query =  new SQLQuery("SELECT r1.pdb_id AS pdb_id, r1.pdb_chain_id AS chain1," +
        "r2.pdb_chain_id AS chain2, AVG(average_atom_distance) AS average_atom_distance, SUM( residue_contact_strength ), " +
        "count(*) AS total_ontacts FROM `residue_contact_derived` rcd, residue_contact rc, residue r1, residue r2 " +
        "WHERE rcd.residue_contact_id = rc.residue_contact_id && rc.residue_id1 = r1.residue_id && " +
        "rc.residue_id2 = r2.residue_id GROUP BY r1.pdb_id, r1.pdb_chain_id, r2.pdb_chain_id LIMIT 10");
        System.out.println(query.getExpression());
        WebRowSet rowset = new WebRowSet( query.getOutput() );
        OutputStreamActivity outputstream = new OutputStreamActivity(rowset.getOutput());
      
        // Construct the request
        request = new ActivityRequest();
        request.add( query );
        request.add( rowset );
        request.add(outputstream);        
        request.setSessionRequirements(new JoinExistingSession (session));
        
        service.perform( request );
        System.out.println("OK 1");
        // Retrieve the data using the data transport operations provided
        // by the OutputStream as a JDBC result set
                
        //outputstream.getDataTransport().setConnectionProperty(new GSITransportEncryptionProperty());
        outputstream.getDataTransport().setConnectionProperty(enc);
        ResultSet result = outputstream.getResultSet(10);
        //ResultSet result = rowset.getResultSet();
        while (result.next())
        {
           System.out.println(result.getString(1)+"\t"+result.getString(2)+"\t"+result.getString(3)+"\t");
        }
        System.out.println("OK 2");
    }    
}