1 2 Previous Next 19 Replies Latest reply: Apr 30, 2007 8:09 AM by masijade RSS

    Unable to set string in prepared statement

    807606
      Hi all,

      I just want to set an string to a prepared stmt.
      the setting string is in the format..... the integers with comma saparated....
      str="23,55,22"
      ps.setString(1,str)
      The prepare statement is taking only the first integer... i.e. 23


      can any one help me out..........
      thanks in advance,
      prakhyath
        • 1. Re: Unable to set string in prepared statement
          800649
          post your code plz
          • 2. Re: Unable to set string in prepared statement
            807606
            thanks for the responce...
            here is the sample code...

            PreparedStatement ps1=null;
            ResultSet rs1=null;
            ps1=Connection1.prepareStatement(select * from EMPLOYEE
            where userid IN (?) );
            String str="22,44,26";
            ps.setString(1,str);
            rs1=ps1.executeQuery();



            the query will give required output when we set the value without using the variable.
            i .e. ps.setString(1,"22,44,26");


            thanks.
            • 3. Re: Unable to set string in prepared statement
              807606
              thanks for the responce...
              here is the sample code...

              PreparedStatement ps1=null;
              ResultSet rs1=null;
              ps1=Connection1.prepareStatement(select * from EMPLOYEE
              where userid IN (?) );
              String str="22,44,26";
              ps.setString(1,str);
              rs1=ps1.executeQuery();


              Thanks
              • 4. Re: Unable to set string in prepared statement
                800649
                        PreparedStatement ps1=null;
                        ResultSet rs1=null;
                        Connection Connection1 = ...................; //create/get sql connection here
                        ps1=Connection1.prepareStatement("select * from EMPLOYEE where userid IN (?)" );
                        String str="22,44,26";
                        ps1.setString(1,str);
                        rs1=ps1.executeQuery();
                • 5. Re: Unable to set string in prepared statement
                  807606
                  Hi,

                  I sorry for missed line... I have just not included that line while putting....
                  Connection is fine... i'm geeting the result.
                  but the result is not valid... it is taking only first integer before comma...

                  if if not mistaken that is coz of single quotes with string while seeting the values.
                  am i rightt..???
                  If so wats the solution for that.....

                  thanks..
                  • 6. Re: Unable to set string in prepared statement
                    masijade
                            PreparedStatement ps1=null;
                    ResultSet rs1=null;
                    Connection Connection1 = ...................;
                    //create/get sql connection here
                    ps1=Connection1.prepareStatement("select *
                    from EMPLOYEE where userid IN (?)" );
                    String str="22,44,26";
                    ps1.setString(1,str);
                    rs1=ps1.executeQuery();
                    That has nothing to do with anything. It is fairly obvious, from the sample code, that the connection was created at an earlier point.


                    But all that aside, are you attemtping to use a String to set an "IN" list of integer ids? If, so, I don't believe that this can be done, and find it hard to believe that using the literal String produced the correct results (at least not in a PreparedStatement, in a normal Statement, yes, but as an argument in a PreparedStatement, I doubt it).
                    • 7. Re: Unable to set string in prepared statement
                      807606
                      Hi all,
                      Here is the upadated sample code..
                      Sorry for the missed line...

                      PreparedStatement ps1=null;
                      ResultSet rs1=null;
                      Connection Connection1=....... //Connection is created here
                      ps1=Connection1.prepareStatement(select * from EMPLOYEE
                      where userid IN (?) );
                      String str="22,44,26";
                      ps.setString(1,str);
                      rs1=ps1.executeQuery();


                      if if not mistaken that is coz of single quotes with string while seeting the values.
                      am i rightt..???
                      If so wats the solution for that.....

                      Thanks
                      • 8. Re: Unable to set string in prepared statement
                        800649
                        That has nothing to do with anything. It is fairly obvious, from the sample code, that the connection was created at an earlier point.
                        fu_ck u
                        • 9. Re: Unable to set string in prepared statement
                          masijade
                          I assume, what you want is as follows:
                          ..... IN (22, 44, 26)
                          Well, using PreparedStatement's setString on the query String
                          ..... IN (?)
                          will produce
                          ..... IN ('22, 44, 26')
                          which is something completely different.

                          You can only set one value per parameter, and hopefully you set it using the proper type. The above would have to be done as follows:
                          "..... IN (?, ?, ?)"
                          ps.setInt(1, 22);
                          ps.setInt(2, 44);
                          ps.setInt(3, 26);
                          • 10. Re: Unable to set string in prepared statement
                            masijade
                            That has nothing to do with anything. It is fairly
                            obvious, from the sample code, that the connection
                            was created at an earlier point.

                            fu_ck u
                            If you can't take a little criticism/correction then I don't know why you are posting here. If that suggestion of yours had had anything to do with his problem I would not have said anything. But, for that to have anything to do with his problem, he would have had a completely different problem than what he is having.
                            • 11. Re: Unable to set string in prepared statement
                              807606
                              Thanks masijade,

                              Ya... u r right....

                              It will be in the format
                              ........ IN ('22, 44, 26')

                              What is the solution for this.....
                              it is not possible to set the values saparately..since the no parameteres(user id's) may increase........
                              the only solution i got is ... avoiding the prepare statement... using a normal sql query........
                              • 12. Re: Unable to set string in prepared statement
                                masijade
                                Statement is the easiest solution, yes.

                                But as I said, in PreparedStatement, there is no solution for this, other than doing each one, one at a time, and then, using the correct method (i.e. set Int and not setString).

                                I'm sorry, I know that that is not what you want to hear, but that's life.

                                Edit: And, BTW, it would still be possible to use the multiple ? type, you would just simply have to create the PreparedStatement after receiving the arguments that are to be provided to it, and using a corresponding number of ? signs. But, as I said, Statement is probably the easiest, and/or quickest, solution, just not necessarily the right one.
                                • 13. Re: Unable to set string in prepared statement
                                  807606
                                  Thanks a lot... masijade....

                                  I will proceed with the normal sql query.....

                                  if i am not mistaken.... If I create the Prepared Statement after receiving the arguments that are to be provided to it.... the actual purpose.. Of using a prepared statement is not served at all.... every time new prepare statement is created based on the output (user id's) from the outer query.....

                                  Thanks.
                                  • 14. Re: Unable to set string in prepared statement
                                    masijade
                                    if i am not mistaken.... If I create the Prepared
                                    Statement after receiving the arguments that are to
                                    be provided to it.... the actual purpose.. Of using
                                    a prepared statement is not served at all....
                                    Not creating a new Statement each time, is only one of the purposes of PreparedStatement. In this case, since the arguments should be ints (and I hope you are checking that), then it doesn't make much of a difference. But a PreparedStatement is alos used to easily facilitate the proper quoting and escaping of the parameters set, which almost eliminates any chance of an SQL injection attack. There are also a few other convienences associated with a PreparedStatement, but, in this case, you are probabyl right in that there is no advantage of PreparedStatement over Statement (as long as you are checking that the arguments provided are actually ints, otherwise you are opening yourself up to an injection attack).
                                    1 2 Previous Next