-3

I learned the concept of prepared statements in JDBC in Java. So I think that prepared statement is a concept in JDBC, but not in RDBMS.

To see whether my guess is right, may I ask whether any major RDBMS provide the feature of prepared statements, in their PL/PSM like languages,such as PL/SQL, PL/pgSQL, MySQL, Transact-SQL?

If there is any such RDBMS, is prepared statement provided in SQL, or in PL/PSM like languages,such as PL/SQL, PL/pgSQL, MySQL, Transact-SQL?

I read DIfference Between Stored Procedures and Prepared Statements..?, but I can't find which provides the feature of prepared statements, although I think prepared statement is a concept in JDBC not in RDBMS, and stored procedure is a concept in RDBMS only.

Tim
  • 1
  • 141
  • 372
  • 590
  • At least Oracle and Postgres have server side prepared statements. –  Jun 13 '18 at 17:24
  • Thanks. Could you point me to their usage references and examples? – Tim Jun 13 '18 at 17:25
  • For Postgres: https://www.postgresql.org/docs/current/static/sql-prepare.html –  Jun 13 '18 at 17:26
  • Thanks. Is prepared statement provided in SQL, or PL/PSM like languages,such as PL/SQL, PL/pgSQL, MySQL, Transact-SQL? – Tim Jun 13 '18 at 17:27
  • 3
    Things like PL/PSM, PL/PSQL, PL/pgSQL, etc have nothing to do with prepared statements, they are about stored procedures (except that stored procedure usually can also prepare and execute prepared statements). For some one who has earned most of his reputation with asking questions, you are curiously bad at phrasing good and to the point questions. In any case, prepared statements are defined in the SQL standard (eg see ISO-9075-2:2016, section 20.7 ). – Mark Rotteveel Jun 13 '18 at 17:41
  • @Mark. You might be right. I have almost always tried my best to make my questions clear from my point of view, and I never think I am very good in English and communication. – Tim Jun 13 '18 at 18:02
  • 1
    This has nothing to do with your use of English. You are conflating multiple concepts in confusing ways, asking multiple questions and being highly non-specific and vague. Those are red flags in my opinion. – Mark Rotteveel Jun 13 '18 at 18:05
  • @Mark Prepared statement feels like procedures and it makes sense to think of it as part of PL/PSM, PL/PSQL, PL/pgSQL, and is surprising it is in SQL. – Tim Jun 14 '18 at 00:11
  • Prepared statements don't feel like procedures at all, the only similarity is superficial in that they both can have parameters. That is a pretty low bar. – Mark Rotteveel Jun 14 '18 at 06:28
  • @Mark I am not able to buy ISO-9075-2:2016. Or is ISO-9075-2:2016 freely available somewhere on the Internet but I miss it on Google search? – Tim Jun 14 '18 at 14:16
  • You need to buy it from your local ISO representative (usually your national standards organization), there is no free version. But I saw you link to a a draft revision of an earlier version elsewhere, in this aspect, the standard hasn't changed much AFAIK. – Mark Rotteveel Jun 14 '18 at 16:05

1 Answers1

3

Every implementation of SQL-compliant RDBMS should support an API for server-side prepared statements. I can't think of one RDBMS that doesn't support prepared statements.

JDBC has a class for PreparedStatement. The implementation varies by each brand of JDBC driver, but all those that I have used just delegate to the RDBMS API. The JDBC driver sends an SQL query string to the database server, and the SQL may contain parameter placeholders for example ? (some brands — like Oracle — support named parameters).

Some database implementations provide packages or functions you can use to execute a prepared statement, so you can create a query at runtime within a stored procedure.

Some database implementations also support PREPARE and EXECUTE statements that you can call as a query. This allows you to use prepared statements in a stored procedure or an SQL script.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks. Per the links you gave, prepared statements in Oracle, SQL Server, and MySQL are created from strings, and thus seem to be dynamic SQL. On the other hand, prepared statements are not created from strings in PostgreSQL and in [SQL](http://jtc1sc32.org/doc/N2301-2350/32N2311T-text_for_ballot-CD_9075-2.pdf), so are they dynamic SQL in PostgreSQL and SQL? Are prepared statements in PostgreSQL the same concept as prepared statements in MySQL, SQL Server and Oracle Database? – Tim Jun 14 '18 at 03:09
  • https://stackoverflow.com/questions/50849126/do-prepared-statements-belong-to-dynamic-sql-in-postgresql-and-sql – Tim Jun 14 '18 at 03:22