QueryMultiple: Error parsing column
Nobody has claimed this yet.
- 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
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- 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