Parametrized SQL queries for PostgreSQL?

Hi, I use Database Toolbox to fetch data from a Postgres database.

Is it possible to parametrize SQL query when using fetch?

conn = postgresql(...);

col4 = "foobar";

query = ...
    "SELECT s.col1, s.col2, s.col3  " + ...
    "FROM mystuff s " + ...
    "WHERE s.col4 = '" + col4 + "'";

fetch_opts = databaseImportOptions(conn, query);

results = fetch(conn, query, fetch_opts);

close(conn);
clear conn query fetch_opts;

Is there a way to declare the col4 as a query parameter istead of concatenating the value into query string?

Answer 1

[Animesh]

If you are working with a PostgreSQL server and need to execute parameterized queries using MATLAB's database toolbox, you will find that the default “fetch” function does not support this directly. However, you can utilize the “JDBC driver” for PostgreSQL to establish a connection and then create a parameterized query with an “SQLPreparedStatement” object, which is compatible with a JDBC connection. This method allows you to pass the prepared statement to the “fetch” function to retrieve your results.

You can refer the following documentation for more information:

PostgreSQL JDBC for Windows:

https://www.mathworks.com/help/releases/R2021b/database/ug/postgresql-jdbc-windows.html?searchHighlight=postgresql%20jdbc&s_tid=doc_srchtitle

SQLPreparedStatement along with example: https://www.mathworks.com/help/releases/R2021b/database/ug/database.preparedstatement.sqlpreparedstatement.html