Parametrized SQL queries for PostgreSQL?

Question

Sergey Kostrukov 2021-12-14

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1610370-parametrized-sql-queries-for-postgresql

回答： Animesh 2024-2-15

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?

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Animesh 2024-2-15

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1610370-parametrized-sql-queries-for-postgresql#answer_1409233

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