Named Parameters in Parameterized Queries
From Ingres Community Wiki
Many databases provide support for queries containing parameters as both unnamed (positional) and named.
The unnamed (positional) parameter marker syntax using a question mark "?" supported by the Ingres .NET Data Provider and Call Level Interfaces such as Java and ODBC. The problem with the unnamed parameter marker syntax is two-fold: (1) Parameters must be set in the order that they appear in the SQL statement and (2) The programmer must keep track of the relative order of the parameter markers. For a large number of parameters this can be a burden.
The named parameter marker syntax is determined by prefixing an identifier with a marker such as a colon (:) or an at-sign (@) as databases vary in their syntax.
Let’s take a simple example of a parameterized statement. Following is the syntax supported by Ingres and then two examples of Named Parameters.
- Current Ingres Support for Positional Parameter Markers
- Positional Parameter Markers: select table_name, table_owner from iitables where table_name = ? and table_owner=?
- Other DBMS Vendors Support for Named Parameter Marker
- Named Parameter Marker #1: select table_name, table_owner from iitables where table_name=:tablename and table_owner=:tableowner
- Syntax Used by Oracle and DB2UDB
- Named Parameter Market #2: select table_name, table_owner from iitables where table_name=@tablename and table_owner=@tableowner
- Syntax Used by Microsoft SQL Server and Sybase
One of the advantages of Named Parameters is that it allows the parameters values to be set independent of the order that they appear in the SQL statement.
The proposed layer is conceptually a runtime preprocessor layer that sits between the application and the Call Level Interface (CLI). Since the low level interfaces to the Ingres DBMS only support unnamed parameters, ultimately everything must be translated into unnamed parameters and the intermediate layer must take care of the housekeeping on behalf of the application.
This adds attributes that are normally associated with a preprocessor. Implementing this in the driver should be looked at as a short term hack that will ultimately be replaced by a more formal implementation in the DBMS. (It is interesting to note that OpenROAD actually implements this syntax by dynamicaly compiling the SQL and making low level calls to LIBQ to implement this type of behavior. The Query Tool project has also implemented a layer that simulates the use of named parameters using the colon (:) syntax. This has been extended to support the at-sign (@)syntax for testing purposes.)
Design Document for Ingres .NET Data Provider is available here.
Coded for Ingres .NET Data Provider for release 10.
In order to start this discussion and make sure that we have not left any stone unturned, it will be helpful to attempt to list what this feature should looik like without any regard to timeframe or resources. This should allow us to set a baseline of expectation.
- Support both forms of named parameters in the same query
- Be supported in the following core environments: Ingres .NET Data Provider
- Have minimal impact on performance in both the client and server
- Tracing must be enhanced to trace the translation between named and unnamed parameters if this is being done on the client side
- Be supported in the following additional environments: (1) Java ; (2) ODBC; (3) Python; (4) PHP; and (5) Ruby
- Allow the intermixing of positional and named parameters
- Allow the binding of a parameter only once if it appears more than one time in a statement
- Provide Database Administration and Management Tools to support the testing and debugging of these queries
- Allow rudimentary query translation of SCALAR functions, AGGREGATE functions and other constructs from non-Ingres syntax to Ingres syntax.
One of the drivers for implementing this in the Ingres .NET Data Provider immediately is the need to help with an existing migration opportunity from Oracle to Ingres. The use of Named Parameters is pervasive in the prospects application.
A C# class has been provided by the Luminary services organization that was extracted from their Carbon Framework. The use of this class requires that an additional call be made each time a statement is prepared. The challenge that we are faced is how to minimize the impact on the prospects appliation without breaking existing Ingres applications.
Analysis of Luminary Utility Method
One of the questions that was asked is are there any efficiencies that can be gained by implementing support for Named Parmeters in the .NET Data Provider from a GCA and DBMS standpoint. Specifically, if a parmeter is referenced more than once is is only bound once in the Client, GCA and DBMS?
David Postle answered this question from a .NET Client perspective. David Thole answered this from a .NET Data Provider, GCA and DBMS viewpoint.
Initial Analysis by David Thole
I took a minute to look at the Luminary utility for converting named parameters to a positional parameter list. The code is relatively short.
The application calls the utility's ProcessCommandForPositionalParameters() method:
- Saves the original IngresParameter parameter objects in a Dictionary, indexed by ParameterName containing a "@ident" value
- Clears the original parameter collection in preparation to rebuild it from scratch
- Splits the possibly batched SQL statement into an array of SQL statement by scanning for ';'. If more than 2 statements, gives up and throws an Exception.
- Using a regular expression scanner, scan the SQL statement and build a list of the "@ident" references
- For each "@ident" reference in the SQL statement
- Index into the Dictionary list using the "@ident" as the parameter name key to find the application's original IngresParameter object
- Create and add a new entry into the parameter collection using the application's original IngresParameter's values
- After the parameter list has been rebuilt into a positional order, replace all "@ident" occurrences in the SQL statement with "?"
You had a question: "... if the same data is needed in multiple parts of the query, is the bind only done once & the same placeholder is used in all places or are multiple binds done for each reference?" If the same "@ident" reference is used in the SQL statement, the utility would allow the application to just bind once for the references. The multiple references would index to the one binding. It is legal in SQL Server.
In-depth Analysis by David Thole
The Luminary method logically sits between application and the data provider.
The method is called just before the Prepare method in the data provider is called. Or, Luminary would like the code embedded in the data provider’s Prepare code so that they don’t need to change a bunch of programs.
As I read the Luminary method, in the case of the same variable bound multiple times, the code will simply fill out the new parameter list with additional parameter entries if multiple “@ident” parameter markers refer to the same parameter. For example, if the application passes an SQL statement of:
- SELECT *
- FROM mytable
- WHERE mycol1 = @parm1
- AND mycol2 = @myparm1
- AND mycol3 = @myparm1
The parameter list just has one entry for @parm1. And that one entry has a pointer to the bound value.
After the call to the Luminary method and what is sent to the DBMS server is:
- SELECT *
- FROM mytable
- WHERE mycol1 = ?
- AND mycol2 = ?
- AND mycol3 = ?
The parameter list now has three entries. Each entry has a pointer to the bound value.
The Luminary code is driven by the parameter markers in the SQL statement.
If the same variable is bound multiple times, the new parameter list is fleshed out with new entries as triggered by the “@ident” parameter marker. In the data provider processing of the parameter list, what is sent to DAS and the DBMS server in the GCA messages are copies of the same value, one for each “@ident” parameter marker in the SQL. The data provider and DBMS are none the wiser that the original application parameter list was short-sheeted.
What interfaces need to be supported?
Just .NET or others? If more than two, probably makes sense to add support for this to the DBMS.
Initial request comes .NET but this is a concept used in JDBC/hibernate applications as well.
- Decision was made to only support .NET at this time
What syntax needs to be supported?"
The at-sign (@) syntax or the colon (:) support or both?
- At-sign (@), colon (:), and question-mark (?) syntaxes all will be supported.
Should intermixing of the at-sign (@) syntax or the colon (:) be supported?
- Conceptually, the use of either form for named parameters probably could be supported with little effort.
- I did some checking and it appears that Ingres identifiers cannot start with either (@) or (:).
- The Colon syntax is the same as what is ESQL which would make like way more complicated if intermixing was allowed
- No. Intermixing is not allowed for readability.
Should intermixing of Named and Unnamed Parameters be supported?
- This would allow no changes in the methods that process parameterized queries.
- It would probably be unusual to encounter this type of syntax in migrations.
- No. Readability would suffer to allow the mix.
Should PrepareCommand be extended to support both Named and Unnamed Parameters?
- No application changes required
- Named and unnamed parameters in different SQL statements is allowed but not in the same statement.
Should the PrepareStatement method be left AS-IS and add a new NamedPrepareStatement method that handles Name Parameters in Prepared Statements?
- Clearly separates code and use of Named from Unnamed
- Application changes required
- Not applicable to the .NET data provider
A reference implementation for Java whereby this was implemented as a new method is available [here.] He also explained that by taking this approach he was able to allow the use of either method at the discretion of the programmer. This particular implementation only supported the Oracle/DB2UDB syntax.
Java Prepared Statement Overview
A link to the overview of the Java Prepared Statement API can be accessed by clicking [here.]
Microsoft Executing Prepared Statements
A link to the overview of the SQL Server 2000 Executing Prepared Statements can be accessed by clicking [here.]
Microsoft .NET SQL Prepare Method
A link to the overview of the Microsoft .NET SQL Prepare Method description can be accessed by clicking [here.]
Should feature be implemented in DBMS Server?
- Feature would be extensible to multiple drivers and work would not be replicated in the drivers and front-ends
- One of the benefits for named parameters in parameterized queries is that if the same data is needed in multiple parts of the query, the bind is only done once by using the same placeholder name. If the change is made to the driver to support the syntax, the query will still be sent to the DBMS as in the unnamed (positional) format and this benefit would not be gained.
- DBMS would need to support named parameters in parameterized queries
- GCA would need support named parameters in parameterized queries requiring a GCA protocol bump
- Feature would not work against servers at old GCA levels (older DBMSs and gateways)
- The decision was made to only support this feature in the .NET Data Provider and not extend the DBMS to support this capability
Should the Ingres Database Administration and Database Management Tools Support this syntax?
- We need some way to allow the adhoc use of these queries for testing
- Right now the best that we have is ESQLC
- A .NET implementation of adhoc query capability should have this type of support
- Future consideration.
Should .NET data provider support a named parameter marker named such as "?mykey" or "@mykey" matching true against a ParameterName of "mykey"?
- A match without the parameter marker character in DbParameter.ParameterName is undocumented but supported by SQL Server
- Used by Visual Studio Query Designer and Parameter Collection Editor
- Support by Ingres .NET Data Provider would ease migrations and smooth the Visual Studio design experience
- Implemented in Ingres .NET Data Provider, file version 2.1.1000.36
Proposed .NET Implementation
The Ingres .NET Data Provider will scan SQL statements for named parameter markers that begin with at-sign (@), colon (:), and question-mark (?). The named parameter is saved in an internal IngresParameterCollection. The named parameter marker is replaced with a simple positional parameter marker of question mark (?). Just before the parameters are sent to DAS, a new parameter list is created with the original parameters cloned and re-ordered in parameter marker order as dictated by the saved internal IngresParameterCollection. The send of the parameters then proceeds as normal just as if positional parameters had been used all along. Only positional parameter markers are sent to DAS and DBMS. The application's original IngresParameterCollection and IngresParameter in the IngresCommand.Parameters collection is not modified. IngresParameter objects are cloned for the internal copy of the IngresParameterCollection.
Ingres Enhancement Number
SD Issue 138495
SIR 123585 - .NET Data Provider support for named parameters.
.NET DDS Review Summary
Feedback was very positive. After email discussion, it was decided:
- multiple named parameter markers with the same name can reference a single IngresParameter.
- to match parameter marker name against the named parameters as case-insensitive
- to allow a mixture of upper and lower case of parameter names
- to allow alphabetic, number, and special characters "_#@$" to follow the initial parameter marker character
- to allow extra unreferenced named parameters in the IngresParameterCollection and to ignore them
- no maximum limit on the length of the name of the parameter
- Test that existing unnamed positional parameter list, e.g. ... VALUES (?, ?, ?), is not broken
- Test named parameters with question mark (?) as initial parameter marker character
- Test named parameters with at-sign (@) as initial marker character
- Test named parameters with colon (:) as initial marker character
- Test named parameters in random order in IngresParameterCollection are located correctly
- Test name matching against the named parameters is case-insensitive
- Test extra named parameters in the IngresParameterCollection are ignored if the SQL statement has parameter markers
- Test that multiple named parameter markers with the same name can reference a single IngresParameter
- Test that a mix of named and unnamed parameter markers is rejected
- Test that a mix of different initial parameter marker characters is rejected
- Test that alphabetic, number, and special characters "_#@$" are accepted after the initial parameter marker character
The Connectivity Guide will be updated in the Understanding .NET Connectivity chapter to document this functionality.
Named Parameters in Parameterized Queries in .NET Data Provider
The Ingres .NET Data Provider now supports named parameters in queries. Named parameters allow the parameter values to be set regardless of the order that they appear in the SQL statement.
A named parameter marker is constructed by an initial character of question mark (?), at-sign (@), or colon(:) followed by a name. The name is an alphanumeric identifier or an integer. While the recommended parameter marker character is the question mark, the Ingres .NET Data Provider supports all three characters to ease migrations from other database products.
(Prior releases supported the unnamed (positional) parameter marker placeholder syntax using the question mark (?), similar to ODBC and JDBC.)
Using named and unnamed parameters in the same SQL statement is not allowed.
For more information, see the Connectivity Guide.
The data provider supports named parameters. A named parameter marker is constructed by an initial character of question mark (?), at-sign (@), or colon (:) followed by a name. The name is an alphanumeric identifier or an integer. Databases vary in the use of the initial character for a named parameter. While the Ingres recommended initial parameter marker character is the question mark, the Ingres .NET Data Provider supports all three characters.
If one parameter is named then all of the parameters must be named. If one parameter is unnamed (positional) then all of the parameters must be unnamed. For the sake of code readability, a mix of named parameters and unnamed parameters is not permitted.
For the sake of code readability, a mix of named parameters, each with a different initial parameter marker character in a single SQL statement is also not permitted.
Multiple occurrences of the same named parameter marker in the SQL statement is permitted.
Excess named parameters that are not referenced in the SQL statement are permitted in the IngresParameterCollection and are ignored.
The name comparison between the named parameter marker in the SQL statement and the named parameter in the IngresParameterCollection is not case sensitive.
Named Parameters Example
The following is an example of using named parameters:
cmd = conn.CreateCommand(); cmd.CommandText = "insert into mytable values ( ?key, ?col1 ?col2 ?col3 )"; cmd.Parameters.AddWithValue("?key", mykey); cmd.Parameters.AddWithValue("?col3", mystring3); cmd.Parameters.AddWithValue("?col1", mystring1); cmd.Parameters.AddWithValue("?col2", mystring2); cmd.ExecuteNonQuery(); cmd = conn.CreateCommand cmd.CommandText = "insert into mytable values ( @key, @col1 @col2 @col3 )"; cmd.Parameters.AddWithValue("@key", mykey); cmd.Parameters.AddWithValue("@col3", mystring3); cmd.Parameters.AddWithValue("@col1", mystring1); cmd.Parameters.AddWithValue("@col2", mystring2); cmd.ExecuteNonQuery(); cmd = conn.CreateCommand(); cmd.CommandText = "insert into mytable values ( :key, :col1 :col2 :col3 )"; cmd.Parameters.AddWithValue(":key", mykey); cmd.Parameters.AddWithValue(":col3", mystring3); cmd.Parameters.AddWithValue(":col1", mystring1); cmd.Parameters.AddWithValue(":col2", mystring2); cmd.ExecuteNonQuery();