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?

Answers


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.


Need Your Help

ASP.NET 5 (Core): How to store objects in session-cache (ISession)?

c# type-conversion bytearray asp.net-core session-state

I am writing an ASP.NET 5 MVC 6 (Core) application. Now I came to a point where I need to store (set and get) an object in the session-cache (ISession).

Creating a JavaScript function to change a previously declared variable

javascript string function variables getelementbyid

I am having trouble changing a variable on a page with lots of PHP and JavaScript.