Provide a mechanism for parameterized SQL queriesODBC libraries typically provide the ability to create parameterized queries that pass the query and parameters to the database separately. This basically eliminates the risk of SQL injection attacks, but is also very convenient from a programming standpoint. Because JMP has ODBC queries, it should also have parameterized queries so that you can do:
Execute SQL(database,
"Select * from Employees where Name like ? and Status=?",
"BobList",
{"Bob", "Active"}
);pyodbc's parameterized queries:
https://github.com/mkleehammer/pyodbc/wiki/Getting-started#parameters
The .NET ecosystem's parameterized queries:
https://docs.microsoft.com/en-us/dotnet/api/system.data.odbc.odbccommand.parameters?redirectedfrom=MSDN&view=dotnet-plat-ext-5.0#System_Data_Odbc_OdbcCommand_Parameters
Automation and ScriptingRe: Provide a mechanism for parameterized SQL queriesI think you could implement this using named queries: Create Named Query() and Execute Named Query(). Under the hood, these functions would use the ODBC SQLPrepare(), SQLBindParameter(), and SQLExecute() functions. The Create Named Query() function would take the SQL statement and parameters. It would return a named query object. The Execute Named Query() would take the database connection object, the named query object, and the parameter values as inputs and return the SQL results as a data table (if needed). The database connection object could be an input of the create function instead of the execute function. The benefit of this approach is that the creation can happen at script initialization leaving the executing piece separate which will probably be more efficient.
Re: Provide a mechanism for parameterized SQL queriesI would also like to have something like this as well, at the very least to be able to pass SQL connection and query arguments separately to make unit testing functions easier. For example, when testing a function that contains a SQL query, the connection could be easily swapped for a local JMP table.
