4

Please read the comments of the answer for a more complete understanding of what the problem is/was

First, I read through a lot of the other SO questions related to this and still can't get this to work with a basic setup. Here is the related question I have already read:

Passing query parameters in Dapper using OleDb

EDIT: The troubleshooting below is somewhat misleading. The only thing that was going wrong was the query syntax from the Github example was not valid using the ProgressDB OpenEdge driver.

The problem with that question's answer and with the example given in the documented Git examples is that a true ODBC object is not being used, but rather an OleDbConnection object. This causes problems with the scenario where I am trying to use Dapper. Some background and restrictions to my scenario:

  • I cannot change the DB technology, we are connecting to an Progress DB. The connection string to connect to the DB: connectionString="PROVIDER=MSDASQL;DRIVER={Progress OpenEdge 10.2A Driver};HOST=...;PORT=...;DB=mfgsys;UID=...;PWD=...;DIL=READ UNCOMMITTED" Notice the Provider: MSDASQL
  • According to MSDN, https://msdn.microsoft.com/en-us/library/a6cd7c08%28v=vs.110%29.aspx - "The .NET Framework Data Provider for OLE DB does not work with the OLE DB provider for ODBC (MSDASQL). To access an ODBC data source using ADO.NET, use the .NET Framework Data Provider for ODBC."
  • When I attempt to use the OdbcConnection object with Dapper I get the following error: "System.Data.Odbc.OdbcException : ERROR [HY000] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Syntax error in SQL statement at or about "= ?, Age = ?" (10713)"

I am using the exact same query syntax as the other SO question:

var row = _odbcConn.Query("select Id = ?, Age = ?", new DynamicParameters(new{foo = 12, bar = 23}) {RemoveUnused = false}).Single();

I also removed the DynamicParameters object and attempted with a dynamic object with same result:

var row = _odbcConn.Query("select Id = ?, Age = ?", new{foo = 12, bar = 23}).Single();

Is there a way to accomplish this simple query using an OdbcConnection object? Or does this really have more to do with the specific Progress driver we are using and as such precludes using Dapper?

Edit

Including working ADO.Net code per requests below, the Build.FromReader<EmployeeDataModel>(reader) just loops through the reader and maps the columns with hard coding and is confirmed to work:

public class EmployeeRepository : IEmployeeRepository
{
    private readonly OdbcConnection _sqlConn = new OdbcConnection();

    public EmployeeRepository() : this(ConfigurationManager.ConnectionStrings["TCI_Epicor"].ConnectionString) { }
    public EmployeeRepository(string connString)
    {
        _sqlConn.ConnectionString = connString;
    }

    public EmployeeDataModel GetById(string id)
    {
        try
        {
            _sqlConn.Open();
            using (OdbcCommand command = new OdbcCommand())
            {
                command.Connection = _sqlConn;
                command.CommandType = CommandType.Text;
                command.CommandText = GetEmployeeDataQuery();
                command.Parameters.Add("empID", OdbcType.NVarChar);
                command.Parameters["empID"].Value = id;
                var reader = command.ExecuteReader();
                return Build.FromReader<EmployeeDataModel>(reader);
            }
        }
        catch
        {
            return new EmployeeDataModel();
        }
        finally
        {
            _sqlConn.Close();
        }
    }

    private string GetEmployeeDataQuery()
    {
        var sb = new StringBuilder();
        sb.AppendLine("SELECT EmpID as 'EmployeeID',");
        sb.AppendLine("       FirstName + ' ' + LastName as 'EmployeeName'");
        sb.AppendLine("  FROM MFGSYS.PUB.EmpBasic");
        sb.AppendLine(" WHERE EmpID = ?");
        return sb.ToString();
    }
}
Tom Bascom
  • 13,405
  • 2
  • 27
  • 33
akousmata
  • 1,005
  • 14
  • 34
  • Just to be clear, you are now NOT using dapper? Where is the "dapperness"? Thanks. – JustAspMe Sep 21 '15 at 21:15
  • @JustAspMe, actually, I AM using dapper, the examples above the "Edit" section above are similar to the ones provided in Dapper's example code on Github. Everything below the "Edit" section is pure ADO.Net, and was provided as a response to Marc's request. The point of all of this is that if you are using ProgressDB, you can still use Dapper, you just can't specify params in the `select` clause. For example, `("select Id from empTable where Id = ?foo?", new {foo = 12})` WILL work in ProgressDB, whereas `("select ID = ?foo?", new {foo = 12})` will not work on ProgressDB. Clear as mud? – akousmata Sep 22 '15 at 15:15
  • Okay, thanks for the clarification. I am researching the use of Dapper w/ ODBC to an iSeries. I am finally "getting it". FYI (not that it matters now since this post is old) - With regard to the "point" of this post, I don't send parameters in with my sql statement. I just concat the parms into the sql statement. Not sure if that would help you or not. So for your where clause, I would use " WHERE EmpId = ' + id.toString().Trim() + ' "; There's always many roads to the destination you seek. – JustAspMe Sep 22 '15 at 17:35

1 Answers1

5

If the problem is using anonymous (?) parameters, then:

var row = _odbcConn.Query(
    "select Id = ?foo?, Age = ?bar?", new { foo = 12, bar = 23 }
).Single();

Dapper will rewrite that as per your original query, but will know which parameter to put where.

However, if the problem is that the ODBC provider does not support parameters: I can't help much with that :( If you can show how to do it in working ADO.NET code, I can probably show you how to do it easier via dapper.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • I am getting the same error message. I think that it's related to the fact that I'm using the `OdbcConnection` object rather than the `OleDbConnection` object. I just cloned Dapper to test my theory out locally. – akousmata Mar 31 '15 at 17:18
  • @akousmata so: forget about dapper a moment: do you have working parameterized ADO.NET code using `OdbcConnection` ? – Marc Gravell Mar 31 '15 at 17:26
  • yes, I was doing all the ORM mapping by hand which is what I'm trying to avoid. The code I was using returns an OdbcDataReader which I then read through the results and mapped to my object with hard coding. This will work but was hoping to avoid that scenario. Separately, I can confirm that the issue is with the `{Progress OpenEdge 10.2A Driver}`, when I switched to using the `{SQL Server}`, your tests pass. Will post my ADO.Net code shortly. – akousmata Mar 31 '15 at 17:40
  • @akousmata that *should* work via `?empID?` and `new { empID }` - is it not? – Marc Gravell Mar 31 '15 at 17:51
  • Well, this is working now, however, the example query you provided in the answer does not. It throws the exception I mentioned in my question. I don't think the ProgressDB syntax allows you to make an assignment within a select statement. I could be wrong. – akousmata Mar 31 '15 at 18:42
  • 1
    @UriAbramson I already posted working ADO.Net code. As I said in one of my comments on the main thread, the main issue is that Progress DB doesn't support assigments in a `SELECT` clause. So `Select ID = ?foo?` is not valid syntax and instead must be `Select ID from table where ID = ?foo?` – akousmata Mar 06 '16 at 19:59
  • @akousmata that isn't an assignment - that is a column alias; have you tried `select ?foo? as ID` or `select ?foo? as [ID]` ? – Marc Gravell Mar 07 '16 at 10:27
  • 1
    @MarcGravell thanks for the clarification; my misunderstanding but that makes sense. No, using a column alias like that does not work. I get the same syntax error. On a side note, I experimented with a number of different combinations and finally got a different error message. With the syntax: `select ?foo? from myactualtablename` I got the error message `No Params allowed in select list`. So ultimately, that may be the issue. However, it should be noted that I have not been able to get ANYTHING to work without a legitimate table name and a `FROM` clause. – akousmata Mar 07 '16 at 13:38
  • @akousmata I am not familiar with that provider and cannot advise much on that :( – Marc Gravell Mar 07 '16 at 21:31
  • @MarcGravell no worries, your input has still been really valuable. I am learning about this provider simply through trial and error myself. – akousmata Mar 08 '16 at 19:45