Missing IN or OUT parameter at index:: 1 error in Java, Oracle

Question

Hi I coded a Library Management System in JSF 2.2 with using Netbeans 8.0.2 and Oracle 11g Express Edition. I have several pages named Books, Borrowers etc. and some tables named same in database. My problem is this: in Borrowers screen book ids are displayed. But I want to reach book's titles that have same id. Here is my code.

public List<Borrower> getBorrowers()throws ClassNotFoundException,SQLException, InstantiationException, IllegalAccessException{
    Class.forName("oracle.jdbc.driver.OracleDriver");
    con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","Alparslan-PC","123456");
    ps=con.prepareStatement("SELECT * FROM BORROWER");

    BookidPs = con.prepareStatement("SELECT TITLE FROM BOOK AND BORROWER WHERE ISBN=?");


    ResultSet rs=ps.executeQuery();
    ResultSet Rs2=BookidPs.executeQuery();
    List<Borrower> liste = new ArrayList<Borrower>();
    while(rs.next()){

        Borrower borrow = new Borrower();
        borrow.setId(rs.getBigDecimal("ID"));
        borrow.setName(rs.getString("NAME"));
        borrow.setSurname(rs.getString("SURNAME"));
        borrow.setAddress(rs.getString("ADDRESS"));
        borrow.setPhonenumber(rs.getString("PHONENUMBER"));
        borrow.setBorrowdate(rs.getString("BORROWDATE"));
        //book.setIsbn(rs.getString("BOOKID"))
        borrow.setBookid(Rs2.getString("BOOKID"));
        //borrow.setBookid(((Book) rs.getObject("BOOKID")).getIsbn());
        liste.add(borrow);
    }
    System.out.print(liste);
    return liste;
}

if line 22 changed with borrow.setBookid(rs.getString("BOOKID")); The bookids are shown. I wrote another query named BookidPs. if I run this program I get Missing IN or OUT parameter at index:: 1 error. If you help I will appretiate. Thanks

janos · Accepted Answer · 2014-12-07 13:19:30Z

22

The "Missing IN or OUT parameter" in the error message is for the ? in the query:

SELECT TITLE FROM BOOK AND BORROWER WHERE ISBN=?

You didn't supply a value for it. Try like this:

BookidPs = con.prepareStatement("SELECT TITLE FROM BOOK AND BORROWER WHERE ISBN=?");
BookidPs.setString(1, theIsbn);

The query needs an ISBN parameter, but I don't see where you have such variable in your code. To use this query, you need to provide the missing parameter. In fact, it seems this second query is not useful for you at all. Given the code, I don't see why you want to use this query instead of getting the value from rs.

But actually, even with that parameter filled, the query is invalid SQL.

I think you probably want just one query, a JOIN. Your second query seems to try to select the TITLE from the BOOK table. So maybe you want this query, without parameters:

SELECT * FROM BORROWER JOIN BOOK ON BORROWER.BOOKID = BOOK.BOOKID

edited Dec 7, 2014 at 13:19

answered Dec 7, 2014 at 12:51

janos

126k31 gold badges242 silver badges253 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Alp Over a year ago

Thanks for your attention. I wrote in this way 'BookidPs = con.prepareStatement("SELECT TITLE FROM BOOK AND BORROWER WHERE ?=?"); BookidPs.setString(1, book.getIsbn()); BookidPs.setString(2, bookid);' But in this time I got HTTP Status 500 - ORA-00933: SQL command not properly ended error. If this way is not efficient please show me how to achieve this issue. How I can print book names with using book id.

janos Over a year ago

?=? is incorrect. Column names cannot be parameterized that way. You can use ? only in right-hand side expressions.

Alp Over a year ago

Because I want to show Book names that are borrowed in Borrowers screen. I tried BookidPs = con.prepareStatement("SELECT TITLE FROM BOOK AND BORROWER WHERE ISBN=?"); BookidPs.setString(1, bookid); same error with previous one.

Alp Over a year ago

In Book table there are book names and in Borrower table there are book id s that are wanted. I want to reach ISBN s so to book names with using Book id in Borrower table. Now I can only show bookid in borrowers screen.

Alp Over a year ago

Thanks I saw. This helped, this is the true statement but now I should add result of query to the list item named liste. It caries the printed values to view you know. How can I add it to list

|

Collectives™ on Stack Overflow

Missing IN or OUT parameter at index:: 1 error in Java, Oracle

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related