Can ODBC parameter place holders be named?

问题:

I did some searching and haven't found a definitive answer to my questions.

Is there a way to define which ? in a SQL query belongs to which parameter?
For example, I need to perform something like this:

SELECT * FROM myTable WHERE myField = @Param1 OR myField2 = @Param1 
       OR myField1 = @Param2 OR myField2 = @Param2

The same query in ODBC is:

SELECT * FROM myTable WHERE myField = ? or myField2 = ? or myField1 = ? 
       or myField2 = ?

Is there a way to tell the ODBC command which parameter is which besides loading parameters in twice for each value?

I suspect there isn't but could use perspective from more experienced ODBC programmers.

EDIT : The ODBC driver I'm using is a BBj ODBC Driver.

回答1:

In MSDN it is explicitly stated that you cannot name the parameters which is the only way to "tell the ODBC command which parameter is which".

Although the documentation can generate a bit of confusion:

From MSDN, OdbcParameter Class:

When CommandType is set to Text, the .NET Framework Data Provider for ODBC does not support passing named parameters to an SQL statement or to a stored procedure called by an OdbcCommand. In either of these cases, use the question mark (?) placeholder.

The order in which OdbcParameter objects are added to the OdbcParameterCollection must directly correspond to the position of the question mark placeholder for the parameter in the command text.

From the above it seems to suggest that when CommandType is not set to Text maybe you can use named parameters, but unfortunately you can't:

From MSDN, OdbcCommand.CommandType Property:

When the CommandType property is set to StoredProcedure, you should set the CommandText property to the full ODBC call syntax. The command then executes this stored procedure when you call one of the Execute methods (for example, ExecuteReader or ExecuteNonQuery).

The .NET Framework Data Provider for ODBC does not support passing named parameters to an SQL statement or to a stored procedure called by an OdbcCommand. In either of these cases, use the question mark (?) placeholder...

回答2:

Thank you Tom for your Idea and your code.
However the code was not working correctly in my test.
So I have written a simpler (and at least in my tests working) solution to replace named parameters with positional parameters (where ? is used instead of the name):

public static class OdbcCommandExtensions
{
    public static void ConvertNamedParametersToPositionalParameters(this OdbcCommand command)
    {
        //1. Find all occurrences parameters references in the SQL statement (such as @MyParameter).
        //2. Find the corresponding parameter in the command's parameters list.
        //3. Add the found parameter to the newParameters list and replace the parameter reference in the SQL with a question mark (?).
        //4. Replace the command's parameters list with the newParameters list.

        var newParameters = new List<OdbcParameter>();

        command.CommandText = Regex.Replace(command.CommandText, "(@\\w*)", match =>
        {
            var parameter = command.Parameters.OfType<OdbcParameter>().FirstOrDefault(a => a.ParameterName == match.Groups[1].Value);
            if (parameter != null)
            {
                var parameterIndex = newParameters.Count;

                var newParameter = command.CreateParameter();
                newParameter.OdbcType = parameter.OdbcType;
                newParameter.ParameterName = "@parameter" + parameterIndex.ToString();
                newParameter.Value = parameter.Value;

                newParameters.Add(newParameter);
            }

            return "?";
        });

        command.Parameters.Clear();
        command.Parameters.AddRange(newParameters.ToArray());
    }
}

回答3:

I couldn't get it to use the named parameters - only positional parameters. You can add all the parameters you want like below, but you have to add the values in order.

SELECT * FROM myTable WHERE myField = ? or myField1 = ? or myField2 = ? 
       or myField2 = ?
myOdbcCommand.Parameters.AddWithValue("DoesNotMatter", val1); //myField
myOdbcCommand.Parameters.AddWithValue("WhatYouPutHere", val2); //myField1
myOdbcCommand.Parameters.AddWithValue("DoesNotMatter", val3); //myField2
myOdbcCommand.Parameters.AddWithValue("WhatYouPutHere", val4); //myField2

As you can see from the above, the parameter names don't matter and aren't used. You can even name them all the same if you want or better yet, leave the param names empty "".

回答4:

I know that when using Oracle Rdb ODBC, I cannot use place holders name and have to use '?'; which I find extremely annoying.

回答5:

I’ve had a need to write code that handles converting named parameters to ordinal parameters with the question mark. My need was with OleDb instead of Odbc… but I’m sure this would work for you if you change the types.

using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Linq;
using System.Text.RegularExpressions;

namespace OleDbParameterFix {
    static class Program {
        [STAThread]
        static void Main() {
            string connectionString = @"provider=vfpoledb;data source=data\northwind.dbc";
            using (var connection = new OleDbConnection(connectionString))
            using (var command = connection.CreateCommand()) {
                command.CommandText = "select count(*) from orders where orderdate=@date or requireddate=@date or shippeddate=@date";
                command.Parameters.Add("date", new DateTime(1996, 7, 11));

                connection.Open();

                OleDbParameterRewritter.Rewrite(command);
                var count = command.ExecuteScalar();

                connection.Close();
            }
        }
    }

    public class OleDbParameterRewritter {
        public static void Rewrite(OleDbCommand command) {
            HandleMultipleParameterReferences(command);
            ReplaceParameterNamesWithQuestionMark(command);
        }

        private static void HandleMultipleParameterReferences(OleDbCommand command) {
            var parameterMatches = command.Parameters
                                          .Cast<OleDbParameter>()
                                          .Select(x => Regex.Matches(command.CommandText, "@" + x.ParameterName))
                                          .ToList();

            // Check to see if any of the parameters are listed multiple times in the command text. 
            if (parameterMatches.Any(x => x.Count > 1)) {
                var newParameters = new List<OleDbParameter>();

                // order by descending to make the parameter name replacing easy 
                var matches = parameterMatches.SelectMany(x => x.Cast<Match>())
                                              .OrderByDescending(x => x.Index);

                foreach (Match match in matches) {
                    // Substring removed the @ prefix. 
                    var parameterName = match.Value.Substring(1);

                    // Add index to the name to make the parameter name unique. 
                    var newParameterName = parameterName + "_" + match.Index;
                    var newParameter = (OleDbParameter)((ICloneable)command.Parameters[parameterName]).Clone();
                    newParameter.ParameterName = newParameterName;

                    newParameters.Add(newParameter);

                    // Replace the old parameter name with the new parameter name.   
                    command.CommandText = command.CommandText.Substring(0, match.Index)
                                            + "@" + newParameterName
                                            + command.CommandText.Substring(match.Index + match.Length);
                }

                // The parameters were added to the list in the reverse order to make parameter name replacing easy. 
                newParameters.Reverse();
                command.Parameters.Clear();
                newParameters.ForEach(x => command.Parameters.Add(x));
            }
        }

        private static void ReplaceParameterNamesWithQuestionMark(OleDbCommand command) {
            for (int index = command.Parameters.Count - 1; index >= 0; index--) {
                var p = command.Parameters[index];
                command.CommandText = command.CommandText.Replace("@" + p.ParameterName, "?");
            }
        }
    }
}

回答6:

Here is a short solution to the post: https://stackoverflow.com/a/21925683/2935383

I've wrote this code for an OpenEdge (Progress) ODBC wrapper. The DatabaseAdapter-class is this wrapper and will not shown here.

string _convertSql( string queryString, List<DatabaseAdapter.Parameter> parameters, 
                    ref List<System.Data.Odbc.OdbcParameter> odbcParameters ) {
    List<ParamSorter> sorter = new List<ParamSorter>();
    foreach (DatabaseAdapter.Parameters item in parameters) {
        string parameterName = item.ParameterName;
        int indexSpace = queryString.IndexOf(paramName + " "); // 0
        int indexComma = queryString.IndexOf(paramName + ","); // 1

        if (indexSpace > -1){
            sorter.Add(new ParamSorter() { p = item, index = indexSpace, type = 0 });
        }
        else {
            sorter.Add(new ParamSorter() { p = item, index = indexComma, type = 1 });
        }
    }

    odbcParameters = new List<System.Data.Odbc.OdbcParameter>();
    foreach (ParamSorter item in sorter.OrderBy(x => x.index)) {
        if (item.type == 0) { //SPACE
            queryString = queryString.Replace(item.p.ParameterName + " ", "? ");
        }
        else { //COMMA
            queryString = queryString.Replace(item.p.ParameterName + ",", "?,");
        }
        odbcParameters.Add(
                new System.Data.Odbc.OdbcParameter(item.p.ParameterName, item.p.Value));
    }
}

Utility class for sorting

class ParamSorter{
    public DatabaseAdapter.Parameters p;
    public int index;
    public int type;
}

If the named parameter the last in string - you have to add a whitespace. e.g. "SELECT * FROM tab WHERE col = @mycol" must "SELECT * FROM tab WHERE col = @mycol "