Bind parameters, a.k.a. dynamic parameters or bind variables, provide an alternative way to pass data to the relational database. Instead of putting the values directly into the SQL statement
select * from Foo f where f.baz = 'some string'
One can use a placeholder like ?, :name or @name, for example,
select * from Foo f where f.baz = :my_param
and provide the actual values using a separate API call, supported by many common relational database drivers and ORM frameworks (such as, for example, NHibernate for Java), usually, via a function call, for example, DataParameter.Create("my_param", "some string"); the exact syntax depends on the driver and framework (C, Java, .NET, Python, …).
Database engines that are capable of caching execution plans, such as, for example, MS SQL Server, PostgreSQL and Oracle, can reuse an execution plan and significantly improve the overall processing performance when executing the same statement multiple times with different values bound to a parameter, which is not an uncommon scenario. If nearly identical SQL statements contain the values themselves (as string literals, numbers, etc.) and those values are different, the database engine will handle those statements as unrelated and will need to re-create the execution plan rather than re-using a cached one. Also, bind variables are the best way to prevent SQL injection in parameterized queries.
And the last, but not least, parameterized queries provide a workaround for some database engine limitations. For instance, in Oracle, a string literal in a SQL statement is limited to 4000 bytes (which translates to 4000 characters for ASCII-only strings and even fewer characters for Unicode).
For example, in C# with the linq2db ORM, I can submit a chemical substructure query to an Oracle database with the BIOVIA Direct chemical data cartridge as follows:
string pat = File.ReadAllText("QueryMol.mol");
DataParameter p = DataParameter.Create("pattern", pat);
var r = this.ExecuteReader(@"select c.structureid, c.creationdate
from structure4_moltable s4
inner join compound c on c.structureid = s4.structureid
where sss(s4.ctab, :pattern) = 1", p);
r.Reader.Dump();
The above query will work even if QueryMol.mol contains a molecule encoded into a string longer than 4000 characters (which is quite common for MOL files, since the MOL CTAB encoding is very verbose). However, it does not seem that there’s a way to do that in KNIME using the standard DB Query Reader node and the following query:
select c.structureid,c.creationdate
from structure4_moltable s4
inner join compound c on c.structureid = s4.structureid
where sss(s4.ctab, '$${SMolfileString}$$') = 1
The DB Query Reader component will actually inline the MolfileString literal and send it to the Oracle as a SQL query containing the quoted string itself, which, if the string is longer than 4000 characters, will result in the “Execute failed: ORA-01704: string literal too long” error.
I now that there have been a number of discussions on parameterized SQL in the past, for example, https://forum.knime.com/t/oracle-bind-variables-in-knime/9190, but all those topics are now closed, so I decided to submit yet another one in the hope that either there already exists a way to execute parameterized SQL queries in the latest versions of KNIME that I am just unaware of, or this post can be considered as a feature request.