CODEWITHSUNDEEP
javasqlsearch
hamzaihoual
hamzaihoual

Reputation: 3

I want to add a search in my sql database, function to get the name by the id creates an error

I have a music database so I'll need a search function, now when entering into the search bar, I get the error : "Invalid argument in JDBC call: parameter index out of range: 1"

public static Song getSongByName(String name)
    {
        String sql = 
             "SELECT songID FROM Song "+
             "WHERE name = '?';              ";
        Connection conn          = Connections.getConnection();
        Song erg = null;
        try
        {
            PreparedStatement  pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,name);
            ResultSet  rs    = pstmt.executeQuery();
            rs.next();
            int id  =  rs.getInt     (1);
            name  =  rs.getString     (2);
            erg  = new SongImpl(id,name);
            rs.close();
            pstmt.close();
        }
        catch(SQLException exc)
        {
            System.err.println("Fehler: in SQL-Aufruf");
            System.err.println("["+sql+"]");
            exc.printStackTrace();
            System.exit(6);
        }
        Connections.putConnection(conn);
        return erg;
    }

Upvotes: 0

Views: 115

Answers (2)

Tomino
Tomino

Reputation: 475

It seems like you are selecting just one column instead of two.

The correct SQL query would be:

SELECT songID, songName FROM Song WHERE name = '?';

Try this:

public static Song getSongByName(String name)
{
    String sql = 
         "SELECT songID, songName FROM Song "+
         "WHERE name = ?";
    Connection conn = Connections.getConnection();
    Song erg = null;
    try
    {
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1,name);
        ResultSet rs = pstmt.executeQuery();
        rs.next();
        int id = rs.getInt(1);
        String name = rs.getString(2);
        erg = new SongImpl(id, name);
        rs.close();
        pstmt.close();
    }
    catch(SQLException exc)
    {
        System.err.println("Fehler: in SQL-Aufruf");
        System.err.println("["+sql+"]");
        exc.printStackTrace();
        System.exit(6);
    }
    Connections.putConnection(conn);
    return erg;
}

Let me know if that helped :)

Upvotes: 1

Alex Fedorov
Alex Fedorov

Reputation: 65

Look onto examples https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html
Try to remove single quotes:

String sql = 
             "SELECT songID FROM Song "+
             "WHERE name = ?;              ";

You should use string parameters without quotes because your statement interpreted as a SQL query without parameters.

Upvotes: 0

Related Questions