[Nelsonlab-cmts] libnelson/java gecCel.java,1.1,1.2

[<all...@su...>]

Update of /cvsroot/libnelson/java
In directory sumo.genetics.ucla.edu:/tmp/cvs-serv6451

Modified Files:
	gecCel.java 
Log Message:
now exports annotations as well


Index: gecCel.java
===================================================================
RCS file: /cvsroot/libnelson/java/gecCel.java,v
retrieving revision 1.1
retrieving revision 1.2
diff -C2 -d -r1.1 -r1.2
*** gecCel.java	31 Jan 2006 00:50:02 -0000	1.1
--- gecCel.java	17 Feb 2006 01:45:32 -0000	1.2
***************
*** 19,40 ****
      String SEL_EXP_RUN = "SELECT sample.sample_id, chip.chip_id, chip.file_id || '.file' file_id FROM gen_experiment_chips sample, gen_chip_files chip, gen_file_types type WHERE type.file_type_id = chip.file_type_id AND sample.chip_id = chip.chip_id AND type.file_extension = 'cel'";
  
      Connection conn = null;
!     PreparedStatement pstmt = null;
!     ResultSet rs = null;
! 	
      try {
        conn = getConnection();
!       pstmt = conn.prepareStatement(SEL_EXP_RUN);
!       rs = pstmt.executeQuery();
        System.out.println("<files>");
!       while(rs.next()) {
!         int chip_id   = rs.getInt("CHIP_ID");
!         String file_id   = rs.getString("FILE_ID");
!         int sample_id = rs.getInt("SAMPLE_ID");
!         System.out.println("  <file file_id=\"" + file_id + "\" chip_id=\"" + chip_id + "\" sample_id=\"" + sample_id + "\"/>"); 
  
        }
        System.out.println("</files>");
!       cleanup(conn, pstmt, rs);
      }
      catch(Exception e){
--- 19,68 ----
      String SEL_EXP_RUN = "SELECT sample.sample_id, chip.chip_id, chip.file_id || '.file' file_id FROM gen_experiment_chips sample, gen_chip_files chip, gen_file_types type WHERE type.file_type_id = chip.file_type_id AND sample.chip_id = chip.chip_id AND type.file_extension = 'cel'";
  
+ //    String SEL_ANNOT = "SELECT d.sample_id, x.accession FROM gen_annotation_exp_sample_data d, gen_annotation_cvterm c, gen_annotation_dbxref x WHERE d.cell_type_level_id = c.cvterm_id AND c.dbxref_id = x.dbxref_id AND sample_id = ? UNION SELECT d.sample_id, 'null:' || c.name FROM gen_annotation_exp_sample_data d, gen_annotation_cvterm c WHERE d.dev_stage_level_id = c.cvterm_id AND sample_id = ? UNION SELECT d.sample_id, 'null:' || c.name FROM gen_annotation_exp_sample_data d, gen_annotation_cvterm c WHERE d.cell_growth_level_id = c.cvterm_id AND sample_id = ? UNION SELECT d.sample_id, x.accession FROM gen_annotation_exp_sample_ds d, gen_annotation_cvterm c, gen_annotation_dbxref x WHERE d.disease_state_level_id = c.cvterm_id AND c.dbxref_id = x.dbxref_id AND d.sample_id = ? UNION SELECT d.sample_id, x.accession FROM gen_annotation_exp_sample_ed d, gen_annotation_cvterm c, gen_annotation_dbxref x WHERE d.experiment_design_level_id = c.cvterm_id AND c.dbxref_id = x.dbxref!
_id AND d.sample_id = ? UNION SELECT d.sample_id, x.accession FROM gen_annotation_exp_sample_pt d, gen_annotation_cvterm c, gen_annotation_dbxref x WHERE d.phenotype_level_id = c.cvterm_id AND c.dbxref_id = x.dbxref_id AND d.sample_id = ? UNION SELECT d.sample_id, x.accession FROM gen_annotation_exp_sample_data d, gen_annotation_cvterm c, gen_annotation_dbxref x WHERE d.rna_level_id = c.cvterm_id AND c.dbxref_id = x.dbxref_id AND sample_id = ?";
+ 
      Connection conn = null;
!     PreparedStatement stmt1 = null;
!     PreparedStatement stmt2 = null;
!     ResultSet rs1 = null;
!     ResultSet rs2 = null;
! 
      try {
        conn = getConnection();
!       stmt1 = conn.prepareStatement(SEL_EXP_RUN);
!       rs1 = stmt1.executeQuery();
        System.out.println("<files>");
!       while(rs1.next()) {
!         int chip_id   = rs1.getInt("CHIP_ID");
!         String file_id   = rs1.getString("FILE_ID");
!         int sample_id = rs1.getInt("SAMPLE_ID");
! 
!         System.out.println("  <file file_id=\"" + file_id + "\" chip_id=\"" + chip_id + "\" sample_id=\"" + sample_id + "\">"); 
! 
!         stmt2 = conn.prepareStatement(
!           "SELECT d.sample_id, x.accession FROM gen_annotation_exp_sample_data d, gen_annotation_cvterm c, gen_annotation_dbxref x WHERE d.cell_type_level_id = c.cvterm_id AND c.dbxref_id = x.dbxref_id AND sample_id = "+sample_id+" UNION "+
!           "SELECT d.sample_id, 'null:' || c.name FROM gen_annotation_exp_sample_data d, gen_annotation_cvterm c WHERE d.dev_stage_level_id = c.cvterm_id AND sample_id = "+sample_id+" UNION "+
!           "SELECT d.sample_id, 'null:' || c.name FROM gen_annotation_exp_sample_data d, gen_annotation_cvterm c WHERE d.cell_growth_level_id = c.cvterm_id AND sample_id = "+sample_id+" UNION "+
!           "SELECT d.sample_id, x.accession FROM gen_annotation_exp_sample_ds d, gen_annotation_cvterm c, gen_annotation_dbxref x WHERE d.disease_state_level_id = c.cvterm_id AND c.dbxref_id = x.dbxref_id AND d.sample_id = "+sample_id+" UNION "+
!           "SELECT d.sample_id, x.accession FROM gen_annotation_exp_sample_ed d, gen_annotation_cvterm c, gen_annotation_dbxref x WHERE d.experiment_design_level_id = c.cvterm_id AND c.dbxref_id = x.dbxref_id AND d.sample_id = "+sample_id+" UNION "+
!           "SELECT d.sample_id, x.accession FROM gen_annotation_exp_sample_pt d, gen_annotation_cvterm c, gen_annotation_dbxref x WHERE d.phenotype_level_id = c.cvterm_id AND c.dbxref_id = x.dbxref_id AND d.sample_id = "+sample_id+" UNION "+
!           "SELECT d.sample_id, x.accession FROM gen_annotation_exp_sample_data d, gen_annotation_cvterm c, gen_annotation_dbxref x WHERE d.rna_level_id = c.cvterm_id AND c.dbxref_id = x.dbxref_id AND sample_id = "+sample_id
!         );
! 
! //        rs2 = stmt2.executeQuery( sample_id, sample_id, sample_id, sample_id, sample_id, sample_id, sample_id );
!         rs2 = stmt2.executeQuery();
! 
!         while(rs2.next()) {
!           String accession = rs2.getString("ACCESSION");
!           System.out.println("    <annotation accession=\""+ accession +"\"/>");
!         }
  
+         rs2.close();
+         rs2 = null;
+         stmt2.close();
+ 
+         System.out.println("  </file>");
        }
        System.out.println("</files>");
!       cleanup(conn, stmt1, rs1);
      }
      catch(Exception e){
***************
*** 53,65 ****
    }
      
!   private static void cleanup(Connection conn, PreparedStatement pstmt, ResultSet rs) throws SQLException {
!     if (rs != null) {
!       rs.close();
!       rs = null;
      }
!     if (pstmt != null) {
!       pstmt.close();
!       pstmt = null;
      }
      if (conn != null) {
        conn.close();
--- 81,97 ----
    }
      
!   private static void cleanup(Connection conn, PreparedStatement stmt1, ResultSet rs1) throws SQLException {
!     if (rs1 != null) {
!       rs1.close();
!       rs1 = null;
      }
!     if (stmt1 != null) {
!       stmt1.close();
!       stmt1 = null;
      }
+ //    if (stmt2 != null) {
+ //      stmt2.close();
+ //      stmt2 = null;
+ //    }
      if (conn != null) {
        conn.close();