Why this JDBC statement causes an exception?
I have linked JDBC with PostgreSQL. How do we create a view with a placeholder?
But I get this error:
SQL Exception.: ERROR: there is no parameter $1 Position: 72
queryString = "CREATE VIEW clients AS (SELECT client_id FROM Client WHERE firstname = ?)"; pStatement = conn.prepareStatement( queryString ); System.out.println("Enter the name of a client"); br = new BufferedReader( new InputStreamReader(System.in) ); String client_name = br.readLine(); pStatement.setString(1, client_name); pStatement.executeUpdate();
The last line (pStatement.executeUpdate();) causes an exception. Why?
Creating a view called "clients" which filters for a subset of clients does not seem desirable - are you sure you don't just want a prepared statement, instead of a view?
If you do want to create views dynamically, you'll need to construct the SQL in Java (DDL statements cannot be parameterized):
String queryString = "CREATE VIEW \"clients_named_%1$s\" AS (SELECT client_id FROM client WHERE firstname = '%1$s')"; System.out.println("Enter the name of a client"); BufferedReader br = new BufferedReader(new InputStreamReader(System.in)); String clientName = br.readLine(); // sanitize in a way that makes sense for your data clientName = clientName.replaceAll("\\W", ""); Statement stmt = conn.createStatement(); stmt.executeUpdate(String.format(queryString, clientName));
Needless to say, sanitizing user input is important in this case.