Why this JDBC statement causes an exception?

Question

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?

Not sure that a placeholder will be correctly taken into account, inside creation queries . Look at this topic and attempt to print the final query to have some hint of what will the statement really look like after your setString . http://stackoverflow.com/questions/2683214/get-query-from-java-sql-preparedstatement — Arnaud, Mar 14 '16 at 15:46
You can't create a view with a parameter (btw: the parentheses around the select for a `create view` are totally useless) — , Mar 14 '16 at 17:57

score 0 · Answer 1 · answered Mar 14 '16 at 22:50

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.

Why this JDBC statement causes an exception?

1 Answers1