Why does this ADO.NET query return no results?

Question

I have the following code that executes a SQL statement and looks for a result.

var sql = @"select BOQ_IMPORT_ID "
          + "from ITIS_PRJ.PRJ_BOQ_IMPORT_HEADER "
          + "where PROJECT_ID = :Projectid "
          + "order by CREATED_ON desc "
          + "fetch first 1 row only";
using (var conn = new OracleConnection(ApplicationSettings.ConnectionString))
using (var cmd = new OracleCommand(sql, conn))
{
    conn.Open();
    cmd.Parameters.Add(LocalCreateParameterRaw("ProjectId", projectId));
    var reader = cmd.ExecuteReader();
    if (reader.Read())
    {
        byte[] buffer = new byte[16];
        reader.GetBytes(0, 0, buffer, 0, 16);
        var boqId = new Guid(buffer);
        return boqId;
    }

    return null;
}

Where LocalCreateParameterRaw is declared as:

public static OracleParameter LocalCreateParameterRaw(string name, object value)
{
    OracleParameter oracleParameter = new OracleParameter();
    oracleParameter.ParameterName = name;
    oracleParameter.OracleDbType = OracleDbType.Raw;
    oracleParameter.Size = 16;
    oracleParameter.Value = value;
    return oracleParameter;
}

The underlying type for 'projectId' is 'Guid'.

The if (reader.Read()) always evaluates to false, despite there being exactly one row in the table. It normally should return only one row.

Using GI Oracle Profiler I can catch the SQL sent to the db, but only once did the profiler provide a value for the :ProjectId parameter, and it was in lower case. Like that it returned no results, but as soon as I applied UPPER to that value, I get a result.

It looks like I somehow have to get my parameter into uppercase for the query to work, but I have no idea how. Yet if I do a ToString().ToUpper() on the projectId GUID, I get a parameter binding error.

VERY IMPORTANT: I have tried removing the where clause altogether, and no longer add a parameter, so all rows in the table should be returned, yet still no results.

If your query returns always just one row and one column then you may consider `cmd.ExecuteScalar();` — Wernfried Domscheit, Jun 13 '19 at 09:38
@ProfK - See if this is directly related, it has to do with how Guid's are translated between .net and oracle. [Convert from Oracle's RAW(16) to .NET's GUID](https://stackoverflow.com/q/7289734/1260204) — Igor, Jun 20 '19 at 14:37
If you are still having trouble it would help if you included the underlying type and the value for the variable `projectId` (variable being used as the parameter in your query). — Igor, Jun 20 '19 at 15:07
@ProfK - updated my answer below. Pass a byte array to as value instead of the .net guid instance. You can convert using `ToByteArray` method on Guid. — Igor, Jun 20 '19 at 15:25
@Igor I have tried that, `parm.Value = projectId.ToByteArray();` with the same zero result. — ProfK, Jun 20 '19 at 15:28
Have you tried the code posted in [Convert from Oracle's RAW(16) to .NET's GUID](https://stackoverflow.com/q/7289734/1260204)? Basically `parm.Value = BitConverter.ToString(projectId.ToByteArray()).Replace("-", "");` — Igor, Jun 20 '19 at 15:31
@ProfK I think I figured out the binding error when you tried `projectId.ToString().ToUpper()`. check updated answer. — Nkosi, Jun 20 '19 at 16:54
I have removed the `where` clause and any parameters, and still get no data. — ProfK, Jun 21 '19 at 08:45

score 4 · Accepted Answer · answered Jun 21 '19 at 09:20

4

I don't know how, but making the SQL string a verbatim string (prefixed with @) causes the proc to work. So, it doesn't work with:

var sql = @"SELECT BOQ_IMPORT_ID "
      + "FROM ITIS_PRJ.PRJ_BOQ_IMPORT_HEADER "
      + "WHERE PROJECT_ID = :projectid "
      + "ORDER BY CREATED_ON DESC "
      + "FETCH FIRST ROW ONLY";

Yet the same command string in SQL Developer executes and returns results. When I make my SQL string verbatim, as below, I get results.

var sql = @"select BOQ_IMPORT_ID 
            from ITIS_PRJ.PRJ_BOQ_IMPORT_HEADER 
            where PROJECT_ID = :ProjectId 
            order by CREATED_ON desc 
            fetch first 1 row only";

answered Jun 21 '19 at 09:20

ProfK

49,207
121
399
775

Wow. Ok. What happens if you remove the `@` from the first example. – Nkosi Jun 21 '19 at 10:04
I totally missed that prefixed to the first string. – Nkosi Jun 21 '19 at 10:06
@Nkosi Wow, it also works if I just remove the `@` from the first example. – ProfK Jun 21 '19 at 10:12
@ProfK Any chance that the source code contains invisilbe characters(unbreakable space, null-symbol, ...)? You could perform a test: `var sql = @"SELECT BOQ_IMPORT_ID FROM ITIS_PRJ.PRJ_BOQ_IMPORT_HEADER" + "WHERE PROJECT_ID = :projectid "` If it returns all rows it means that verbatim is null-character ended(The null character '\0' is used to terminate C strings) – Lukasz Szozda Jun 25 '19 at 20:46
@LukaszSzozda I suspect this is the case, as changing between a verbatim and a concatenated string changes the outcome. – ProfK Jun 26 '19 at 11:42
@ProfK Did you tried the concatenated verbatim + normal string? I am really curious if it's really the case of `NULL-terminator` like: `var sql = @"SELECT 1 AS BOQ_IMPORT_ID FROM dual " + "WHERE 1=2"`. – Lukasz Szozda Jun 26 '19 at 12:34
If you have notepad++, you will be able to inspect any such issues by right clicking on the bottom right corner and selecting the platform on which the file was created. – Alin Jun 27 '19 at 10:57

Nkosi · Answer 2 · 2019-06-21T10:14:03.933

Using a more general approach, try the following

var sql = "SELECT BOQ_IMPORT_ID "
      + "FROM ITIS_PRJ.PRJ_BOQ_IMPORT_HEADER "
      + "WHERE PROJECT_ID = :projectid "
      + "ORDER BY CREATED_ON DESC "
      + "FETCH FIRST ROW ONLY";
using (DbConnection conn = new OracleConnection(ApplicationSettings.ConnectionString))
using (DbCommand cmd = conn.CreateCommand()) {
    DbParameter parameter = cmd.CreateParameter();
    parameter.ParameterName = "projectid";
    parameter.Value = projectId.ToString("N").ToUpper(); //<-- NOTE FORMAT USED

    cmd.Parameters.Add(parameter);
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = sql;

    conn.Open();

    var reader = cmd.ExecuteReader();
    if (reader.Read()) {
        var boqId = new Guid((byte[])reader[0]);
        return boqId;
    }
    return null;
}

It looks like I somehow have to get my parameter into uppercase for the query to work, but I have no idea how. Yet if I do a ToString().ToUpper() on the projectId GUID, I get a parameter binding error.

Reference Guid.ToString Method

Specifier N formats it to 32 digits: 00000000000000000000000000000000

When no format is provided the default format is D which would include 32 digits separated by hyphens.

00000000-0000-0000-0000-000000000000

That would explain your binding error.

Why does this ADO.NET query return no results?

2 Answers2