1

Does the DBMS save the compiled queries from prepared statements in JDBC, in the form of stored procedures on the DBMS server? I thought that prepared statement isn't a concept in DBMS but in JDBC, so I was wondering how it is implemented on DBMS server side.

My question comes from Why do Parameterized queries allow for moving user data out of string to be interpreted?

I read DIfference Between Stored Procedures and Prepared Statements..?, but don't find my answer.

Thanks.

I am interested in PostgreSQL, MySQL, or SQL server in order.

Tim
  • 1
  • 141
  • 372
  • 590
  • 1
    Without specifying a specific database system it is technically impossible to answer (although for most, if not all, the answer is probably "no"). You are also wrong that prepared statements are not a concept in DBMS, in most it is. But asking multiple questions is another reason that makes your question too broad. – Mark Rotteveel Jun 13 '18 at 17:34
  • Thanks. updated. So is prepared statements in JDBC implemened as prepared statements in RDBMS server side? – Tim Jun 13 '18 at 17:43
  • 1
    And you think asking about three different database systems somehow makes this better? – Mark Rotteveel Jun 13 '18 at 17:44
  • So I use "or", any of them satisfies – Tim Jun 13 '18 at 17:49

1 Answers1

1

No, prepared statements are not implemented as stored procedures in any RDBMS.

Prepared statements are parsed and saved on the server-side so they can be executed multiple times with different parameter values, but they are not saved in the form of a stored procedure. They are saved in some implementation-dependent manner. For example, as some kind of in-memory object, totally internal to the code of the database server. These are not callable like a stored procedure.

Re your comment:

Consider MySQL for example.

MySQL in the very early days did not support prepared statements, so the MySQL JDBC driver has an option to "emulate" prepared statements. The idea of emulation mode is that the SQL query string is saved in the JDBC client when you create a PreparedStatement. The SQL is not yet sent to the database server. Then when you bind parameters and call execute(), it copies the parameter values into the SQL query and sends the final result.

I don't know whether a similar feature exists in other brands of JDBC driver.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks. May I ask in what of RDBMS prepared statements in JDBC are implemented? Are prepared statements in JDBC implemented in prepared statements of RDBMS? – Tim Jun 13 '18 at 18:00
  • The option to emulate prepared statements in the MySQL JDBC driver is used by default (see `useServerPrepStmts` defaults to false in https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-configuration-properties.html) – Mark Rotteveel Jun 13 '18 at 18:11
  • @MarkRotteveel, thanks for the correction, I have edited my answer to match. I mistakenly thought that useServerPrepStmts defaulted to true! My mistake. – Bill Karwin Jun 13 '18 at 18:13
  • I have to admit, I am a bit surprised it still isn't used by default. – Mark Rotteveel Jun 13 '18 at 18:14
  • My bad. the last question in my comment is "Are prepared statements in JDBC implemented in terms of **prepared statements of RDBMS**?" – Tim Jun 13 '18 at 18:24
  • The answer to that is: it depends on the implementation. I gave an example of MySQL, which emulates prepared statements by default, but has an option to do "real" server-side prepared statements. – Bill Karwin Jun 13 '18 at 18:35