Comparing values in the table with the value the user keyed in

pancakeleh

try{

    Connection con = DriverManager.getConnection( );------------------------> Not sure what to include here.

    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT INCIDENTID FROM INCIDENTDATA");
    ArrayList allResults = new ArrayList();

    while (rs.next()) {
   
       int incidentID = rs.getInt("INCIDENTID");

        allResults.add(incidentID);

        }
}

catch (Exception ex) {
              log("ErrorDescription", ex);
          error(ex.getMessage());

}

My code are as shown above. i am unsure of how to make use of sql to check/compare values in the table and with the values the user keyed into the textboxes.

Lost User

I really think you should do some serious Database/SQL study before attempting to go any further with this project or you are likely to get into serious difficulties. Take a look at the tutorials here[^] for a good starter.

I must get a clever new signature for 2011.

Arman S

I'm not sure what you mean "... check/compare values in the table and with the values the user keyed into the textboxes." But I guess you need to get the value from the textbox and write your sql like so:

String myKey = myTextBox.getText(); // something like this to get the value from the textbox

Statement stmt = con.createStatement();
// change the PERSONNAME with an appropriate column from your table INCIDENTDATA.
ResultSet rs = stmt.executeQuery("SELECT INCIDENTID FROM INCIDENTDATA WHERE PERSONNAME = '" + myKey + "'");

// And the rest is the same...

-- Arman

Lost User

Arman S. wrote:

ResultSet rs = stmt.executeQuery("SELECT INCIDENTID FROM INCIDENTDATA WHERE PERSONNAME = '" + myKey + "'");

Please do not encourage people to use constructs that allow for SQL injection; it is almost guaranteed to compromise their entire system.

I must get a clever new signature for 2011.

Arman S

lol, the OP should first realize the existence of such a construct and then understand it's disadvantages. Thanks!

-- Arman

Lost User

Arman S. wrote:

the OP should first realize the existence of such a construct and then understand it's disadvantages.

Not quite; we are supposed to be helping people to learn. Your answer offers the OP the chance to make a big mistake, without advising how to avoid it.

I must get a clever new signature for 2011.

Arman S

Agreed - generally speaking. But not quite, for this concrete situation. If it were so, why wouldn't you go ahead and explain the whole theory behind secure Web development to the OP? Common sense tells me, that you (and I) didn't do it as: * We are not sure if it will solve a bit of OP's problem * We are not sure if OP is even doing e.g. Web * We are not sure if the proposed solution above (by myself) even targets the right problem (because of poor explanation of the problem) My belief is that for beginners (assuming OP is) what matters most is the tangible result, something they can see and the feeling that something is working.

-- Arman

jschell

Arman S. wrote:

Agreed - generally speaking. But not quite, for this concrete situation. If it were so, why wouldn't you go ahead and explain the whole theory behind secure Web development to the OP? Common sense tells me, that you (and I) didn't do it as: * We are not sure if it will solve a bit of OP's problem * We are not sure if OP is even doing e.g. Web * We are not sure if the proposed solution above (by myself) even targets the right problem (because of poor explanation of the problem) My belief is that for beginners (assuming OP is) what matters most is the tangible result, something they can see and the feeling that something is working.

Which would matter only if any of that is relevant. One uses parameters (bind variables) in SQL because it prevents injection attacks AND because it correctly deals with other issues in correctly constructing SQL. And this applies to any programming language that supports such usage. There are very few cases where one should be using string concatenation without using parameters. Even when concatenation is used in constructing SQL, such as with variable list productions, parameter usage is still better. So you are presenting a technique that one should almost never use. Your rationalizations do not change that. And by the way SQL injection attacks are not something that are only relevant in Web apps.

Lost User

Arman S. wrote:

My belief is that for beginners (assuming OP is) what matters most is the tangible result, something they can see and the feeling that something is working.

And in your case working wrongly. There are already far too many examples of questions from people who obviously do not understand the dangers inherent in using this sort of construct, and you are just teaching one more programmer to do it the wrong way. You better hope that you do not fall foul of any product developed by this guy.

I must get a clever new signature for 2011.

pancakeleh

i have change my code slightly. Code as shown below:

String dbURL = "jdbc:derby:....";

String myString = (String) getIncidentidtxt().getValue();
Integer incidentIDName = Integer.parseInt(myString);
String incidentNameName = (String)getIncidentnametxt().getValue();

try{

    Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();

    Connection con = DriverManager.getConnection(dbURL);

    Statement stmt = con.createStatement();

    ResultSet rs = stmt.executeQuery("SELECT INCIDENTID FROM INCIDENTDATA");

    //Create New Array
    ArrayList allResults = new ArrayList();

    while (rs.next()) {
        int instanceID = rs.getInt("INCIDENTID");
        allResults.add(instanceID);
        // size of the array
        int arrayCount = allResults.size();
        label3.setValue(arrayCount);

        for(Integer i =0; i<allResults.size(); i++)
        {
        if(myString.equals(allResults.get(i))){

        }
     }

            }
      }

catch (Exception ex) {
              log("ErrorDescription", ex);
          error(ex.getMessage());
}

But i have no idea if i did this correctly. what shld i do now to proceed on? the main purpose of this is to compare the values in the textboxes with the database (displayed on a table). btw i am using betneans.. http://img263.imageshack.us/img263/3673/arraysofvalues.png[^] the image u see above is the array that i have created to store the incidentID values. how do i bases on this array to validation entry to the database? Like example the database alrdy have the id 3 when user enter 3 into the id textbox, an error message will appears?

modified on Monday, January 10, 2011 3:39 AM

Lost User · modified on Monday, January 10, 2011 3:39 AM

How efficient would this code be if you have 1,000,000 records? Not very, I suspect. Instead of selecting all records in your database and comparing against the id entered by the user, do it the other way round. Select only the record with the id entered; if it does not exist you will get a failure. You should always try and design your system to take the shortest path to a solution. As I have stated a number of times in this thread, try and spend some time studying database and SQL, it will pay dividends in the long run.

I must get a clever new signature for 2011.

pancakeleh

i very much want to spend time studying after all is for my own good, however i am rushing out this. I have made some changes to the code again as shown:

String incidentString = (String) getIncidentidtxt().getValue();

    String incidentNameName = (String)getIncidentenametxt().getValue();

try{

     Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();

    Connection con = DriverManager.getConnection(dbURL);

    stmt = con.createStatement();

    if(incidenteidtxt != null){
    rs = stmt.executeQuery("Select INCIDENTID from INCIDENTDATA");
    
    while(rs.next()){

      id = rs.getString("INCIDENTID");           
      String idtxt = id.toString();

        if(incidentString != idtxt){

            getSessionBean1().updateIncident(incidentString, incidentNameName);
  
        }

        else{

      incidentidtxt.setRequiredMessage("Id valid, enter new Id.");
           
    }
        }

    }
    }

       catch (Exception ex) {

    log("ErrorDescription", ex);
          error(ex.getMessage());
}

however when i run the program, instead of adding one value when the add button is being clicked, it will add 20 over values. why is this so? hope to hear from you all soon.

Lost User

pancakeleh wrote:

however i am rushing out this.

That is a sure recipe for disaster. You have the following statement within your try block, after you have opened your connection to the database.

   if(incidenteidtxt != null){

I would assume that if the value is null there is no point in accessing your database, so you should make this test earlier. Other than that I cannot quite figure out what your code is doing, although it looks like you are reading the database in sequence, and every time you see a record that does not match the incident id, you call your updateIncident method to do something.

I must get a clever new signature for 2011.

pancakeleh

i have further change my code as shown below:

     try{

     Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();

    Connection con = DriverManager.getConnection(dbURL);

    stmt = con.createStatement();

    if(incidentidtxt != null){
  // rs = stmt.executeQuery("Select INCIDENTID from INCIDENTDATA where INCIDENTID = "+incidentStringNo+";");

         id = rs.getInt(1);

        if( id == 0){


            getSessionBean1().updateIncident(incidentId, instanceName);

        }

        else{

    Integer newIncidentID =  incidentId + 1;

     getSessionBean1().updateIncident(newIncidentID, instanceName);
    }

    }
    //}
    }

  catch (Exception ex) {

    log("ErrorDescription", ex);
          error(ex.getMessage());
}
    return null;
}

Lost User

pancakeleh wrote:

if(incidentidtxt != null){
// rs = stmt.executeQuery("Select INCIDENTID from INCIDENTDATA where INCIDENTID = "+incidentStringNo+";");

1. You still have this test within your try catch block, you should be doing it outside as there is no point entering this block of code if incidentidtxt has a null value. 2. your executeQuery command is commented out; is this what you meant to do? 2a. adding database parameters by string concatenation in this way is extremely dangerous, and leaves your application open to SQL Injection problems: Google it and learn how to avoid it. 3. I am no longer certain what you are actually trying to do here or what your problem is.

I must get a clever new signature for 2011.