Exemplo n.º 1
0
  // these come from the performance test JDBC.Parameters that was failing
  private static void testManyOut(Connection conn) throws SQLException {

    System.out.println("start testManyOut");

    Statement scp = conn.createStatement();

    scp.execute(
        "CREATE PROCEDURE OP_OUT "
            + "(OUT I1 INT, OUT I2 INT, OUT I3 INT, OUT I4 INT, OUT I5 INT, "
            + "OUT V1 VARCHAR(40), OUT V2 VARCHAR(40), OUT V3 VARCHAR(40), OUT V4 VARCHAR(40), OUT V5 VARCHAR(40)) "
            + "EXTERNAL NAME '"
            + CLASS_NAME
            + "output' NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");

    scp.execute(
        "CREATE PROCEDURE OP_INOUT "
            + "(INOUT I1 INT, INOUT I2 INT, INOUT I3 INT, INOUT I4 INT, INOUT I5 INT, "
            + "INOUT V1 VARCHAR(40), INOUT V2 VARCHAR(40), INOUT V3 VARCHAR(40), INOUT V4 VARCHAR(40), INOUT V5 VARCHAR(40)) "
            + "EXTERNAL NAME '"
            + CLASS_NAME
            + "output' NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");

    CallableStatement csOut_cs = conn.prepareCall("CALL OP_OUT(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
    CallableStatement csInOut_cs = conn.prepareCall("CALL OP_INOUT(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

    System.out.println("Ten OUT parameters");

    executeOutput(csOut_cs);
    executeOutput(csOut_cs);

    csOut_cs.close();

    System.out.println("Ten INOUT parameters");

    setupInput(csInOut_cs);
    executeOutput(csInOut_cs);

    setupInput(csInOut_cs);
    executeOutput(csInOut_cs);

    csInOut_cs.close();

    scp.execute("DROP PROCEDURE OP_OUT");
    scp.execute("DROP PROCEDURE OP_INOUT");
    scp.close();

    System.out.println("end testManyOut");
  }
 /**
  * Control de transacción en el procedimiento almacenado
  *
  * @param prod
  */
 @Override
 public void create2(Producto prod) {
   Connection cn = null;
   try {
     cn = AccesoDB.getConnection();
     cn.setAutoCommit(true);
     String query = "{call usp_crea_producto(?,?,?,?,?)}";
     CallableStatement cstm = cn.prepareCall(query);
     cstm.registerOutParameter(1, Types.INTEGER);
     cstm.setInt(2, prod.getIdcat());
     cstm.setString(3, prod.getNombre());
     cstm.setDouble(4, prod.getPrecio());
     cstm.setInt(5, prod.getStock());
     cstm.executeUpdate();
     prod.setIdprod(cstm.getInt(1));
     cstm.close();
   } catch (SQLException e) {
     throw new RuntimeException(e.getMessage());
   } catch (Exception e) {
     throw new RuntimeException("No se puede crear el producto.");
   } finally {
     try {
       cn.close();
     } catch (Exception e) {
     }
   }
 }
Exemplo n.º 3
0
    public Query(String unparsed_query, Connection db) throws SQLException {

      params = new ArrayList<String>();
      String query =
          StringUtil.format(
              unparsed_query,
              new StringUtil.ParamResolver() {
                public String resolveParam(String param) {
                  params.add(param);
                  return "?";
                }
              });

      try {
        sql = db.prepareCall(query);
        pmd = sql.getParameterMetaData();
      } catch (SQLException ex) {
        throw new SQLException(
            "JDBC failed to prepare ESXX-parsed SQL statement: " + query + ": " + ex.getMessage());
      }

      if (pmd.getParameterCount() != params.size()) {
        throw new SQLException(
            "JDBC and ESXX report different " + "number of arguments in SQL query");
      }
    }
Exemplo n.º 4
0
 @Override
 protected String getParam(String name) {
   /*		function get_setting (
        				p_key in dxdy_spider_setting.key%type) return varchar2;
   */
   String proc = "{? = call dxdy_executable.get_setting (?)}";
   CallableStatement st = null;
   String result = null;
   try {
     st = connection.prepareCall(proc);
     st.registerOutParameter(1, java.sql.Types.VARCHAR);
     st.setString(2, name);
     st.execute();
     result = st.getString(1);
   } catch (SQLException e) {
     e.printStackTrace();
   } finally {
     if (st != null) {
       try {
         st.close();
       } catch (SQLException e) {
         e.printStackTrace();
       }
     }
   }
   return result;
 }
Exemplo n.º 5
0
 @Override
 protected void saveModer(ModeratorMessage mm, int mID) {
   /*	function add_moderation (
   p_message_id   in dxdy_moderation.message_id%type,
   p_move_from    in dxdy_forum.forum_name%type,
   p_move_to      in dxdy_forum.forum_name%type,
   p_user         in dxdy_user.username%type,
   p_message_type in dxdy_notice_type.notice_name%type) return varchar2;	*/
   String proc = "{? = call dxdy_executable.add_moderation(?, ?, ?, ?, ?)}";
   CallableStatement st = null;
   try {
     st = connection.prepareCall(proc);
     st.registerOutParameter(1, java.sql.Types.VARCHAR);
     st.setInt(2, mID);
     st.setString(3, mm.moveFrom);
     st.setString(4, mm.moveTo);
     st.setString(5, mm.moderator);
     st.setString(6, mm.messageType);
     st.execute();
     //	System.out.println("Link ID = " + st.getString(1));
   } catch (SQLException e) {
     e.printStackTrace();
   } finally {
     if (st != null) {
       try {
         st.close();
       } catch (SQLException e) {
         e.printStackTrace();
       }
     }
   }
 }
Exemplo n.º 6
0
 @Override
 protected void saveUser(User u) {
   /*procedure add_user(
    p_id       in dxdy_user.id%type,
    p_username in dxdy_user.username%type,
    p_status   in dxdy_status.status_name%type,
    p_reg_date in varchar2);
   */
   super.saveUser(u);
   String procedure = "{call dxdy_executable.add_user (?, ?, ?, ?)}";
   CallableStatement st = null;
   try {
     st = connection.prepareCall(procedure);
     st.setInt(1, u.ID);
     st.setString(2, u.name);
     st.setString(3, u.status);
     st.setString(4, u.regDate);
     st.execute();
     // System.out.println("User ID = " + u.ID + ": OK");
   } catch (SQLException e) {
     e.printStackTrace();
   } finally {
     if (st != null) {
       try {
         st.close();
       } catch (SQLException e) {
         e.printStackTrace();
       }
     }
   }
 }
Exemplo n.º 7
0
 public String getStatuses() {
   ResultSet rs = null;
   try (CallableStatement cs = conn.prepareCall("{call ti_getStatuses}")) {
     rs = cs.executeQuery();
     if (rs != null) {
       ArrayList<Status> statuses = new ArrayList<>();
       while (rs.next()) {
         // (int status_id, String displayName, boolean isSystem, boolean isNotify, boolean
         // isClosed, String notifyMsg)
         statuses.add(
             new Status(
                 rs.getInt("status_id"),
                 rs.getString("display_name"),
                 rs.getBoolean("isSystem"),
                 rs.getBoolean("isNotify"),
                 rs.getBoolean("isClosed"),
                 rs.getString("notify_msg")));
       }
       StatusList statusList = new StatusList(statuses);
       return generateGson(statusList);
     }
   } catch (SQLException e) {
     e.printStackTrace();
   }
   return "";
 }
Exemplo n.º 8
0
  public static CallableStatement callPro2(
      String sql, String[] inparameters, Integer[] outparameters) {

    try {

      ct = getConnection();
      cs = ct.prepareCall(sql);
      if (inparameters != null) {
        for (int i = 0; i < inparameters.length; i++) {
          cs.setObject(i + 1, inparameters[i]);
        }
      }

      if (outparameters != null) {
        for (int i = 0; i < outparameters.length; i++) {
          cs.registerOutParameter(inparameters.length + 1 + i, outparameters[i]);
        }
      }

      cs.execute();
    } catch (Exception e) {
      e.printStackTrace();
      throw new RuntimeException(e.getMessage());
    } finally {

    }
    return cs;
  }
  private void cargar() {

    DefaultTableModel tabla = new DefaultTableModel();
    try {
      tabla.addColumn("CODIGO");
      tabla.addColumn("NOMBRES");
      tabla.addColumn("APELLIDOS");
      tabla.addColumn("SEXO");
      tabla.addColumn("DNI");
      tabla.addColumn("TELEFONO");
      tabla.addColumn("RUC");
      tabla.addColumn("E_MAIL");
      tabla.addColumn("DIRECCION");
      cts = cn.prepareCall("{call mostrarclientes}");
      r = cts.executeQuery();
      while (r.next()) {
        Object dato[] = new Object[9];
        for (int i = 0; i < 9; i++) {
          dato[i] = r.getString(i + 1);
        }
        tabla.addRow(dato);
      }
      this.jTable1.setModel(tabla);
      jLabel2.setText("" + jTable1.getRowCount());
    } catch (Exception e) {
    }
  }
Exemplo n.º 10
0
  private static void testNull(Connection conn) throws Throwable {
    System.out.println("==============================================");
    System.out.println("TESTING NULLS");
    System.out.println("==============================================\n");
    System.out.println("Test for bug 4317, passing null value for a parameter");

    Statement scp = conn.createStatement();

    scp.execute(
        "CREATE PROCEDURE testNullBug4317(IN P1 VARCHAR(10)) "
            + "EXTERNAL NAME '"
            + CLASS_NAME
            + "testNullBug4317'"
            + " NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");

    CallableStatement cs0 = conn.prepareCall("call testNullBug4317(?)");
    try {
      cs0.setString(1, null); // passing in null
      cs0.execute();
    } catch (SQLException se) {
      System.out.println("cs0.execute() got unexpected exception: " + se);
    }

    try {
      // BUG 5928 - setNull throws an exception - fixed.
      cs0.setNull(1, java.sql.Types.VARCHAR); // passing in null
      cs0.execute();
    } catch (SQLException se) {
      System.out.println("cs0.execute() got unexpected exception: " + se);
    }
    cs0.close();
    scp.execute("DROP PROCEDURE testNullBug4317");
  }
Exemplo n.º 11
0
  @Override
  public boolean getForumFullScan(String forum) {
    /*      function get_forum_scan (p_forum in varchar2) return number;             */
    boolean result = true;
    String proc = "{? = call dxdy_executable.get_forum_scan (?)}";
    CallableStatement st = null;
    try {
      st = connection.prepareCall(proc);
      st.registerOutParameter(1, Types.INTEGER);
      st.setString(2, forum);
      st.execute();
      result = (st.getInt(1) == 1);
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      if (st != null) {
        try {
          st.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }
    }

    return result;
  }
Exemplo n.º 12
0
  @Override
  public String getLastTopicDate(int tID) {
    /*    function get_topic_last_update (p_topic_id  in dxdy_topic.id%type) return varchar2;        */
    String proc = "{? = call dxdy_executable.get_topic_last_update(?)}";
    CallableStatement st = null;
    String res = "";
    try {
      // System.out.println("Request date for topic ID = " + tID);
      st = connection.prepareCall(proc);
      st.registerOutParameter(1, Types.VARCHAR);
      st.setInt(2, tID);
      st.execute();
      res = st.getString(1);
      // System.out.println("Requested date = " + res);

    } catch (SQLException e) {
      System.out.println("Error while querying last message date of topic ID = " + tID);
      e.printStackTrace();
    } finally {
      if (st != null) {
        try {
          st.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }
    }
    return res;
  }
  public long nextTSecGroupMemberIdGen(
      CFSecurityAuthorization Authorization, CFSecurityTenantPKey PKey) {
    final String S_ProcName = "nextTSecGroupMemberIdGen";
    if (!schema.isTransactionOpen()) {
      throw CFLib.getDefaultExceptionFactory()
          .newUsageException(getClass(), S_ProcName, "Not in a transaction");
    }
    Connection cnx = schema.getCnx();
    long Id = PKey.getRequiredId();

    CallableStatement stmtSelectNextTSecGroupMemberIdGen = null;
    try {
      String sql = "{ call sp_next_tsecgroupmemberidgen( ?" + ", " + "?" + " ) }";
      stmtSelectNextTSecGroupMemberIdGen = cnx.prepareCall(sql);
      int argIdx = 1;
      stmtSelectNextTSecGroupMemberIdGen.registerOutParameter(argIdx++, java.sql.Types.BIGINT);
      stmtSelectNextTSecGroupMemberIdGen.setLong(argIdx++, Id);
      stmtSelectNextTSecGroupMemberIdGen.execute();
      long nextId = stmtSelectNextTSecGroupMemberIdGen.getLong(1);
      return (nextId);
    } catch (SQLException e) {
      throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    } finally {
      if (stmtSelectNextTSecGroupMemberIdGen != null) {
        try {
          stmtSelectNextTSecGroupMemberIdGen.close();
        } catch (SQLException e) {
        }
        stmtSelectNextTSecGroupMemberIdGen = null;
      }
    }
  }
Exemplo n.º 14
0
  /**
   * Executes a stored Procedure that will update Fields in DB (Ask DBM for SP names)
   *
   * @param spName Stores Procedure's name
   * @param param integer parameter needed by the SP
   * @return return 1 if update worked
   * @throws SQLException mostly if SP does not exist in DB
   */
  public boolean updateStoredProcedure(String spName, int param) throws SQLException {
    // preparing callable statement
    cs = conn.prepareCall("{call " + spName + "(?)}");
    cs.setInt(1, param);

    return (cs.executeUpdate() == 1);
  }
Exemplo n.º 15
0
 @Override
 public int getLastTopicPage(int tID) {
   String proc = "{? = call dxdy_executable.get_topic_last_page(?)}";
   CallableStatement st = null;
   int res = 0;
   try {
     st = connection.prepareCall(proc);
     st.registerOutParameter(1, java.sql.Types.INTEGER);
     st.setInt(2, tID);
     st.execute();
     res = st.getInt(1);
   } catch (SQLException e) {
     System.out.println("Error while querying last page of topic ID = " + tID);
     e.printStackTrace();
   } finally {
     if (st != null) {
       try {
         st.close();
       } catch (SQLException e) {
         e.printStackTrace();
       }
     }
   }
   return res;
 }
Exemplo n.º 16
0
  // test: do we get an appropriate update count when using ?=call?
  private static void testUpdate(Connection conn) throws Throwable {
    System.out.println("==============================================");
    System.out.println("TESTING UPDATE COUNT");
    System.out.println("==============================================\n");

    Statement scp = conn.createStatement();

    scp.execute(
        "CREATE FUNCTION returnsIntegerP(P1 INT) RETURNS INTEGER "
            + "EXTERNAL NAME '"
            + CLASS_NAME
            + "returnsIntegerP'"
            + " NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");

    CallableStatement cs = conn.prepareCall("? = call returnsIntegerP(0)");
    cs.registerOutParameter(1, Types.INTEGER);
    try {
      int updCount = cs.executeUpdate();
      System.out.println("executeUpdate on ? = call returnsIntegerP returned " + updCount);
      System.out.println("getString(1) returned " + cs.getString(1));
    } catch (SQLException se) {
      System.out.println("cs.execute() got unexpected exception: " + se);
    }

    cs.close();
    scp.execute("DROP FUNCTION returnsIntegerP");
    scp.close();
  }
Exemplo n.º 17
0
  public void nonQueryStoredProcedure(String SPName, int param1, int param2) throws SQLException {
    // preparing callable statement
    cs = conn.prepareCall("{call " + dbName + "." + SPName + "(?,?)}");
    cs.setInt(1, param1);
    cs.setInt(2, param2);

    cs.executeUpdate();
  }
Exemplo n.º 18
0
 public CallableStatement prepareCall(
     String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) {
   try {
     return con.prepareCall(sql, resultSetType, resultSetConcurrency, resultSetHoldability);
   } catch (Exception ex) {
     ex.printStackTrace();
   }
   return null;
 }
Exemplo n.º 19
0
  /**
   * Gets the result of query Stored Procedure into object's result set object.
   *
   * @param spName Stored Procedure's name
   * @param param1 first String parameter
   * @param param2 second String parameter
   * @throws SQLException if the SQL call is incorect (mostly if SPName is incorect)
   */
  public void queryStoredProcedure(String spName, String param1, String param2)
      throws SQLException {
    // preparing callable statement
    cs = conn.prepareCall("{call " + dbName + "." + spName + "(?,?)}");
    cs.setString(1, param1);
    cs.setString(2, param2);

    rs = cs.executeQuery();
  }
Exemplo n.º 20
0
  private static void test5116(Connection conn) throws Throwable {
    System.out.println("==============================================");
    System.out.println("TESTING FIX OF 5116 -- VAR BIT VARYING INPUT");
    System.out.println("==============================================\n");

    Statement stmt = conn.createStatement();
    stmt.executeUpdate(
        "CREATE TABLE ACTIVITY_INSTANCE_T ("
            + "AIID                               char(16) for bit data              NOT NULL ,"
            + "KIND                               INTEGER                            NOT NULL ,"
            + "PIID                               char(16) for bit data              NOT NULL ,"
            + "PTID                               char(16) for bit data              NOT NULL ,"
            + "ATID                               char(16) for bit data              NOT NULL ,"
            + "RUN_MODE                           INTEGER                            NOT NULL ,"
            + "FINISHED                           TIMESTAMP                                   ,"
            + "ACTIVATED                          TIMESTAMP                                   ,"
            + "STARTED                            TIMESTAMP                                   ,"
            + "LAST_MODIFIED                      TIMESTAMP                                   ,"
            + "LAST_STATE_CHANGE                  TIMESTAMP                                   ,"
            + "STATE                              INTEGER                            NOT NULL ,"
            + "TRANS_COND_VALUES                  VARCHAR(66) FOR BIT DATA           NOT NULL ,"
            + "NUM_CONN_ACT_EVA                   INTEGER                            NOT NULL ,"
            + "NUMBER_OF_ITERATIONS               INTEGER                            NOT NULL ,"
            + "NUMBER_OF_RETRIES                  INTEGER                            NOT NULL ,"
            + "HAS_CUSTOM_ATTRIBUTES              SMALLINT                           NOT NULL ,"
            + "NON_BLOCK_PTID                     char(16) for bit data              NOT NULL ,"
            + "NON_BLOCK_PIID                     char(16) for bit data              NOT NULL ,"
            + "EXPIRES                            TIMESTAMP                                   ,"
            + "TASK_ID                            VARCHAR(254)                                ,"
            + "UNHANDLED_EXCEPTION                BLOB(3993600)                       ,"
            + "SUB_PROCESS_PIID                   char(16) for bit data                                    ,"
            + "OWNER                              VARCHAR(32)                                 ,"
            + "USER_INPUT                         VARCHAR(130) FOR BIT DATA                   ,"
            + "DESCRIPTION                        VARCHAR(254)                                ,"
            + "VERSION_ID                         SMALLINT                           NOT NULL ,"
            + "PRIMARY KEY ( AIID ) )");

    stmt.execute(
        "CREATE PROCEDURE doInsertion(IN P1 VARCHAR(2) FOR BIT DATA) "
            + "EXTERNAL NAME '"
            + CLASS_NAME
            + "doInsertion'"
            + " MODIFIES SQL DATA LANGUAGE JAVA PARAMETER STYLE JAVA");

    CallableStatement cs = conn.prepareCall("call doInsertion (?)");
    cs.setNull(1, java.sql.Types.VARBINARY);
    cs.execute();
    byte[] b = new byte[2];
    b[0] = 1;
    b[1] = 2;
    cs.setBytes(1, b);
    cs.execute();
    cs.close();
    stmt.executeUpdate("DROP PROCEDURE doInsertion");
    stmt.close();
  }
Exemplo n.º 21
0
  public static void main(String[] args) {
    Connection conn = null;
    CallableStatement cStmt = null;
    try {
      // STEP 2: Register JDBC driver
      Class.forName(JDBC_DRIVER);

      // STEP 3: Open a connection
      System.out.println("Connecting to a selected database...");
      conn = DriverManager.getConnection(DB_URL, USER, PASS);
      System.out.println("Connected database successfully...");

      // STEP 4: Execute a query
      System.out.println("Enrollno: 140053131008");
      cStmt = conn.prepareCall("{call studentnew1()}");
      cStmt.execute();

      System.out.println("Selected Data is:");
      System.out.println();

      ResultSet rs = cStmt.getResultSet();
      System.out.print("ID:");
      System.out.print("Name:");
      System.out.print("Branch:");

      while (rs.next()) {
        System.out.println();
        System.out.print(rs.getInt(1) + "\t");
        System.out.print(rs.getString(2) + "\t");
        System.out.print(rs.getString(3) + "\t");
        System.out.println();
      }
      cStmt.close();
    } catch (SQLException se) {
      // Handle errors for JDBC
      se.printStackTrace();
    } catch (Exception e) {
      // Handle errors for Class.forName
      e.printStackTrace();
    } finally {
      // finally block used to close resources

      try {
        if (cStmt != null) conn.close();
        cStmt.close();

      } catch (SQLException se) {
      } // do nothing
      try {
        if (conn != null) conn.close();
      } catch (SQLException se) {
        se.printStackTrace();
      } // end finally try
    } // end try
    System.out.println("Goodbye!");
  } // end main
Exemplo n.º 22
0
  public static void main(String[] args) {

    // Declare the JDBC objects.
    Connection con = null;
    CallableStatement cstmt = null;
    ResultSet rs = null;

    try {
      // Establish the connection.
      SQLServerDataSource ds = new SQLServerDataSource();
      ds.setIntegratedSecurity(true);
      ds.setServerName("localhost");
      ds.setPortNumber(1433);
      ds.setDatabaseName("AdventureWorks");
      con = ds.getConnection();

      // Execute a stored procedure that returns some data.
      cstmt = con.prepareCall("{call dbo.uspGetEmployeeManagers(?)}");
      cstmt.setInt(1, 50);
      rs = cstmt.executeQuery();

      // Iterate through the data in the result set and display it.
      while (rs.next()) {
        System.out.println(
            "EMPLOYEE: " + rs.getString("LastName") + ", " + rs.getString("FirstName"));
        System.out.println(
            "MANAGER: "
                + rs.getString("ManagerLastName")
                + ", "
                + rs.getString("ManagerFirstName"));
        System.out.println();
      }
    }

    // Handle any errors that may have occurred.
    catch (Exception e) {
      e.printStackTrace();
    } finally {
      if (rs != null)
        try {
          rs.close();
        } catch (Exception e) {
        }
      if (cstmt != null)
        try {
          cstmt.close();
        } catch (Exception e) {
        }
      if (con != null)
        try {
          con.close();
        } catch (Exception e) {
        }
    }
  }
Exemplo n.º 23
0
 @Override
 public void callSelectP(String question) throws SQLException {
   CallableStatement statement =
       sqlConn.prepareCall(
           "COPY ( select p_"
               + question
               + ","
               + "sum(case when code like '%K%' then 1 else 0 end) as k,'',"
               + "sum(case when code like '%M%' then 1 else 0 end) as m,'',"
               + "sum(case when null is null then 1 else 0 end) as all,'',"
               + "sum(case when m_mz = 'a' then 1 else 0 end) as m_mza,'',"
               + "sum(case when m_mz = 'b' then 1 else 0 end) as m_mzb,'',"
               + "sum(case when m_mz = 'c' then 1 else 0 end) as m_mzc,'',"
               + "sum(case when m_mz = 'd' then 1 else 0 end) as m_mzd,'',"
               + "sum(case when m_mz = 'e' then 1 else 0 end) as m_mze,'',"
               + "sum(case when m_lr = '0' then 1 else 0 end) as lr_0, '',"
               + "sum(case when m_lr = '1' then 1 else 0 end) as lr_1, '',"
               + "sum(case when m_lr = '2' then 1 else 0 end) as lr_2, '',"
               + "sum(case when m_lr = '3' then 1 else 0 end) as lr_3, '',"
               + "sum(case when m_lr in ('4', '5', '6', '7') then 1 else 0 end) as lr_4_wiecej, '',"
               + "sum(case when m_wr LIKE '%a%' then 1 else 0 end) as wr_a, '',"
               + "sum(case when m_wr LIKE '%b%' then 1 else 0 end) as wr_b, '',"
               + "sum(case when m_wr LIKE '%c%' then 1 else 0 end) as wr_c, '',"
               + "sum(case when m_wm = 'c' then 1 else 0 end) as wmc,'',"
               + "sum(case when m_wm = 'd' then 1 else 0 end) as wmd,'',"
               + "sum(case when m_wm = 'e' then 1 else 0 end) as wme,'',"
               + "sum(case when m_wm = 'f' then 1 else 0 end) as wmf,'',"
               + "sum(case when m_wm = 'g' then 1 else 0 end) as wmg,'',"
               + "sum(case when m_wm not in ('a','b','c','d','e', 'f', 'g') then 1 else 0 end) as wmnic,'',"
               + "sum(case when m_wo = 'c' then 1 else 0 end) as woc,'',"
               + "sum(case when m_wo = 'd' then 1 else 0 end) as wod,'',"
               + "sum(case when m_wo = 'e' then 1 else 0 end) as woe,'',"
               + "sum(case when m_wo = 'f' then 1 else 0 end) as wof,'',"
               + "sum(case when m_wo = 'g' then 1 else 0 end) as wog,'',"
               + "sum(case when m_wykm = 'a' then 1 else 0 end) as wykma,'',"
               + "sum(case when m_wykm = 'b' then 1 else 0 end) as wykmb,'',"
               + "sum(case when m_wykm = 'c' then 1 else 0 end) as wykmc,'',"
               + "sum(case when m_wykm = 'd' then 1 else 0 end) as wykmd,'',"
               + "sum(case when m_wykm not in ('a','b','c','d') then 1 else 0 end) as wykmnic,'',"
               + "sum(case when m_wyko = 'a' then 1 else 0 end) as wykoa,'',"
               + "sum(case when m_wyko = 'b' then 1 else 0 end) as wykob,'',"
               + "sum(case when m_wyko = 'c' then 1 else 0 end) as wykoc,'',"
               + "sum(case when m_wyko = 'd' then 1 else 0 end) as wykod, ''"
               + "    from ankieta_pat group by p_"
               + question
               + " order by p_"
               + question
               + " "
               + ") TO '/tmp/p_a"
               + question
               + ".csv' WITH CSV;");
   statement.execute();
   sqlConn.commit();
 }
Exemplo n.º 24
0
 public void ramasserObjet() {
   boolean vide = true;
   try {
     ResultSet rset =
         stmt.executeQuery(
             "select idObjet from objet where positionX="
                 + positionX
                 + " and positionY="
                 + positionY);
     Hashtable<String, String> tabObj = new Hashtable<String, String>();
     while (rset.next()) {
       vide = false;
       String idObj = rset.getString("idObjet");
       System.out.println(idObj);
       tabObj.put(idObj, idObj);
     }
     rset.close();
     if (!vide) {
       System.out.println("Quel objet voulez vous ramasser ? : ");
       String obj = IO.lireChaine();
       if (tabObj.containsKey(obj)) {
         proc = conn.prepareCall("{call rammasser(?,?)}");
         proc.setInt(1, idTroll);
         proc.setString(2, obj);
         proc.executeUpdate();
         proc.close();
         rset = stmt.executeQuery("select typeObjet from objet where idObjet='" + obj + "'");
         rset.first();
         String type = rset.getString("typeObjet");
         if (type.equals("potion")) {
           menu.supprimerPopo(obj);
         } else {
           menu.supprimerObjet(obj);
         }
         rset = stmt.executeQuery("select paRestants from troll where idTroll=" + idTroll);
         int ancVal = 0;
         while (rset.next()) {
           ancVal = rset.getInt("paRestants");
         }
         stmt.executeUpdate(
             "update troll SET paRestants = " + (ancVal - 1) + " where idTroll=" + idTroll);
         paRestants = paRestants - 1;
       } else {
         System.out.println("Cet objet ne se trouve pas sur votre case !");
       }
     } else {
       System.out.println("Il n'y a aucun objet sur votre case");
     }
   } catch (SQLException E) {
     System.err.println("SQLException: " + E.getMessage());
     System.err.println("SQLState:     " + E.getSQLState());
   }
 }
Exemplo n.º 25
0
  private void init2() {
    try {
      for (int count = 0; stmt == null && count++ <= 5; count++) {
        wait(100L);
        dbConn = DBCHPool.getConnection();
        stmt = dbConn.createStatement(1004, 1007);
      }

      if (isProcedure) cstmt = dbConn.prepareCall(querySQL, 1004, 1007);
    } catch (Exception exception) {
    }
  }
Exemplo n.º 26
0
  @Override
  protected void saveMessage(Message m, int tID) {
    /*function add_message(
    p_id            in dxdy_message.id%type,
    p_username      in dxdy_user.username%type,
    p_created_on    in dxdy_message.created_on%type,
    p_msg_size      in dxdy_message.msg_size%type,
    p_topic_id      in dxdy_message.topic_id%type,
    p_size_wo_quote in dxdy_message.msg_size_wo_quote%type,
    p_latex_size    in dxdy_message.latex_size%type,
    p_offtop        in dxdy_message.offtop%type,
    p_edit_count    in dxdy_message.edit_count%type,
    p_code_size     in dxdy_message.code_size%type,
    p_code_language in dxdy_message.code_language%type,
    p_header        in dxdy_message.header%type) return varchar2;	*/

    String proc = "{? = call dxdy_executable.add_message (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}";
    CallableStatement st = null;
    try {
      st = connection.prepareCall(proc);
      st.registerOutParameter(1, java.sql.Types.VARCHAR);
      st.setInt(2, m.ID);
      st.setString(3, m.user);
      st.setString(4, m.createdOn);
      st.setInt(5, m.size);
      st.setInt(6, tID);
      st.setInt(7, m.sizeWithoutQuote);
      st.setInt(8, m.latexSize);
      st.setInt(9, m.offtop ? 1 : 0);
      st.setInt(10, m.editCount);
      st.setInt(11, m.sizeCode);
      st.setString(12, m.codeLang);
      st.setString(13, m.header);
      st.execute();

      for (Link l : m.links) saveLink(l, m.ID);

      for (ModeratorMessage mm : m.mms) saveModer(mm, m.ID);

    } catch (SQLException e) {
      e.printStackTrace();
      System.out.println("m.createdOn = " + m.createdOn);
    } finally {
      if (st != null) {
        try {
          st.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }
    }
  }
Exemplo n.º 27
0
 public boolean saveInformeReferencia(
     int code,
     String fecElab,
     String diag,
     String mot,
     String pulso,
     String presion,
     String tem,
     String freqresp,
     String resumen,
     String instrc,
     String fecenvio,
     String acomp,
     String horaenvia,
     String seEnviaA,
     String medico) {
   boolean resultado = false;
   try {
     conx = Conexion.getSQLConnection();
     if (conx != null) {
       ps = conx.prepareCall("{call saveInformeReferencia(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
       ps.setInt(1, code);
       ps.setString(2, fecElab);
       ps.setString(3, diag);
       ps.setString(4, mot);
       ps.setString(5, pulso);
       ps.setString(6, presion);
       ps.setString(7, tem);
       ps.setString(8, freqresp);
       ps.setString(9, resumen);
       ps.setString(10, instrc);
       ps.setString(11, fecenvio);
       ps.setString(12, acomp);
       ps.setString(13, horaenvia);
       ps.setString(14, seEnviaA);
       ps.setString(15, medico);
       ps.setString(16, lib.getFechaActual());
       ps.setString(17, "usuario");
       ps.executeUpdate();
       conx.close();
       resultado = true;
     } else {
       System.out.println("Ha ocurrido un error al conectarse a la BD PostgreSQL");
       resultado = false;
     }
   } catch (SQLException e) {
     JOptionPane.showMessageDialog(null, e.getMessage());
   }
   return resultado;
 }
Exemplo n.º 28
0
  /**
   * Gets the result of query Stored Procedure into object's result set object
   *
   * @param spName Stored Procedure's Name without the dataBase name
   * @param param integer parameter needed by the SP
   * @throws SQLException raised if the SQL call is incorect (mostly if SPName is incorect
   */
  public void queryStoredProcedure(String spName, int param) throws SQLException {

    // preparing callable statement
    String call = "{call " + dbName + "." + spName + "(?)}";
    //	statement.concat(SPName);
    //	statement.concat("(");
    //	statemendt.concat(paramStr);
    //	statements.concat(")}");

    cs = conn.prepareCall(call);
    cs.setInt(1, param);

    rs = cs.executeQuery();
  }
Exemplo n.º 29
0
 @Override
 public boolean createAccount(String username, String password, String ingame, String rank) {
   try {
     CallableStatement cs;
     cs = conn.prepareCall("call ti_createAccount(?,?,?,?)");
     cs.setString(1, username);
     cs.setString(2, password);
     cs.setString(3, ingame);
     cs.setString(4, rank);
     cs.executeUpdate();
     return true;
   } catch (SQLException ex) {
     return false;
   }
 }
Exemplo n.º 30
0
 public static void main(String[] args) {
   try {
     Class.forName("com.mysql.jdbc.Driver");
     Connection conn =
         DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "Harsh1408");
     CallableStatement callable = conn.prepareCall("{ call getdata(?)}");
     callable.setString(1, "cse");
     callable.executeUpdate();
     ResultSet rs = callable.getResultSet();
     while (rs.next()) {
       System.out.println(rs.getString(2) + "    " + rs.getInt(1) + "    " + rs.getString(3));
     }
   } catch (Exception e) {
     System.out.println(e);
   }
 }