Hi,
CREATE VIEW is a DDL sentence, and far as I know is not supposed to have
parameters.
If what you want is to check the a received value , you could make a prior
execution of the query with a prepared statement to validate this value and
only then create the view with a fixed text.
regards,
Dario.
El 06/09/10 21:58, Erin Drummond escribió:
> I'm not sure if PreparedStatement parameter insertion is actually
> supposed to work with the AS clause in CREATE VIEW to begin with, but
> when I tried it, no error was thrown so I assumed it should work.
> Anyway, it doesn't, it just produces an empty result set when trying
> to query the view later. My use case for this is dynamically creating
> a view in code, using potentially unsafe parameters. User-supplied
> queries are then analysed, and parts rewritten to include the view
> (which provides a subset of data) instead of the original table (which
> provides all the data, including data the user shouldn't have access
> to).
>
> Here is a test case to reproduce this:
>
> public class H2Test {
>
> public static void main(String[] args) throws Exception {
> Class.forName("org.h2.Driver");
> Connection conn = DriverManager.getConnection("jdbc:h2:mem:", "sa",
> "");
> Statement s = conn.createStatement();
> s.execute("CREATE TABLE Test(id INT AUTO_INCREMENT NOT NULL,
> f1 VARCHAR NOT NULL, f2 VARCHAR NOT NULL)");
> s.execute("INSERT INTO Test(f1, f2) VALUES ('value1','value2')");
> s.execute("INSERT INTO Test(f1, f2) VALUES ('value1','value3')");
> s.execute("CREATE VIEW Test_View AS SELECT f2 FROM Test WHERE
> f1='value1'");
> ResultSet rs = s.executeQuery("SELECT * FROM Test_View");
> System.out.println("(not using prepared statement) Expect to
> see 'value2' and 'value3' printed below.");
> while (rs.next()) {
> String str = rs.getString("f2");
> System.out.println(str);
> }
> s.execute("DROP VIEW Test_View");
> PreparedStatement ps = conn.prepareStatement("CREATE VIEW
> Test_View AS SELECT f2 FROM Test WHERE f1=?");
> ps.setString(1, "value1");
> ps.executeUpdate();
> rs = s.executeQuery("SELECT * FROM Test_View");
> System.out.println("(using prepared statement) Expect to see
> 'value2' and 'value3' printed below (or at least an error thrown)");
> while (rs.next()) {
> String str = rs.getString("f2");
> System.out.println(str);
> }
> }}
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
