DapperLib / DapperLib/Dapper

QueryMultiple: Error parsing column

Open
#658 1 comment 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

area:typehandlers db:oracle
Dominant language
C#
Stars
18.4k
Forks
3.7k
Avg merge
5h 8m
Merged PRs (30d)
1

Description

When using QueryMultiple against an OracleConnection with the following query:

IEnumerable<PhoneNumber> results;
using (var conn = new OracleConnection(_connStr))
{
    var cmdText = @"BEGIN
    OPEN :q9 FOR
        SELECT d.veh_type_nm
        FROM v_dim_veh_type d
        INNER JOIN provider_veh_type p
            ON p.key_veh_type = d.id
        WHERE p.provider_id = :providerId
        AND d.locale_cd = :localeCode;
    OPEN :q1 FOR
        SELECT p.provider_id AS ProviderId,
        d.phone_type_nm AS PhoneType,
        p.phone_nbr AS FormattedNumber
        FROM provider_phone p
        INNER JOIN v_dim_phone_type d
        ON d.id = p.key_phone_type
        WHERE p.provider_id = :providerId
        AND d.locale_cd = :localeCode;
    END;";
    var dynParams = new OracleDynamicParameters();
    dynParams.Add(":q9", OracleDbType.RefCursor, ParameterDirection.Output);
    dynParams.Add(":q1", OracleDbType.RefCursor, ParameterDirection.Output);
    dynParams.Add(":providerId", OracleDbType.Int32, ParameterDirection.Input, providerId);
    dynParams.Add(":localeCode", OracleDbType.Varchar2, ParameterDirection.Input, _localeCode);
    using (var multi = conn.QueryMultiple(cmdText, dynParams))
    {
        var x = multi.Read<string>();
        results = multi.Read<PhoneNumber>();
    }

... I see the following exception:

System.Data.DataException was unhandled by user code
  HResult=-2146233087
  Message=Error parsing column 1 (PHONETYPE=1 - Decimal)
  Source=Dapper
  StackTrace:
       at Dapper.SqlMapper.ThrowDataException(Exception ex, Int32 index, IDataReader reader, Object value)
       at Deserialized8c24a26-e123-4377-9b7a-54760268d46a(IDataReader )
       at Dapper.SqlMapper.GridReader.<ReadDeferred>d__52`1.MoveNext()
       at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
       at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
       at Dapper.SqlMapper.GridReader.ReadImpl[T](Type type, Boolean buffered)
       at Dapper.SqlMapper.GridReader.Read[T](Boolean buffered)
       at FindARide.Models.FindARideRepository.GetPhoneNumbers(Int32 providerId) in D:\Projects\Find-A-Ride\src\FindARide\Models\FindARideRepository.cs:line 109
       at FindARide.Controllers.ProviderController.GetProviderPhoneNumbers(Int32 id, String locale) in D:\Projects\Find-A-Ride\src\FindARide\Controllers\ProviderController.cs:line 38
       at lambda_method(Closure , Object , Object[] )
       at Microsoft.AspNetCore.Mvc.Internal.ObjectMethodExecutor.Execute(Object target, Object[] parameters)
       at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeActionFilterAsync>d__28.MoveNext()
  InnerException: 
       HResult=-2146233080
       Message=Index was outside the bounds of the array.
       Source=Oracle.ManagedDataAccess
       StackTrace:
            at Oracle.ManagedDataAccess.Client.OracleDataReader.GetValue(Int32 i)
            at Oracle.ManagedDataAccess.Client.OracleDataReader.get_Item(Int32 i)
            at Deserialized8c24a26-e123-4377-9b7a-54760268d46a(IDataReader )
       InnerException: 

If I reverse the order of the queries, I have no issue:

IEnumerable<PhoneNumber> results;
using (var conn = new OracleConnection(_connStr))
{
    var cmdText = @"BEGIN
    OPEN :q1 FOR
        SELECT p.provider_id AS ProviderId,
        d.phone_type_nm AS PhoneType,
        p.phone_nbr AS FormattedNumber
        FROM provider_phone p
        INNER JOIN v_dim_phone_type d
        ON d.id = p.key_phone_type
        WHERE p.provider_id = :providerId
        AND d.locale_cd = :localeCode;
    OPEN :q9 FOR
        SELECT d.veh_type_nm
        FROM v_dim_veh_type d
        INNER JOIN provider_veh_type p
            ON p.key_veh_type = d.id
        WHERE p.provider_id = :providerId
        AND d.locale_cd = :localeCode;
    END;";
    var dynParams = new OracleDynamicParameters();
    dynParams.Add(":q1", OracleDbType.RefCursor, ParameterDirection.Output);
    dynParams.Add(":q9", OracleDbType.RefCursor, ParameterDirection.Output);
    dynParams.Add(":providerId", OracleDbType.Int32, ParameterDirection.Input, providerId);
    dynParams.Add(":localeCode", OracleDbType.Varchar2, ParameterDirection.Input, _localeCode);
    using (var multi = conn.QueryMultiple(cmdText, dynParams))
    {
        results = multi.Read<PhoneNumber>();
        var x = multi.Read<string>();
    }

Happy to provide more info as needed.

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start by reproducing the OracleConnection case from FindARideRepository.cs around line 109, using QueryMultiple and GridReader.Read in the shown order. Inspect the OracleDataReader failure and Dapper's multi-result handling to identify which result metadata is mismatched. Done means the supplied query order no longer raises the parsing exception, with regression coverage if an existing test entry point is found.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp, sql
Domain
backend, databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.