C# Guid to Oracle blob throw the System.ArgumentException:value does not fall within the expected range

[fuyong859050943]

   在 Oracle.ManagedDataAccess.Client.OracleParameter.set_DbType(DbType value)
   在 ParamInfo4292f180-6c8f-40d8-9d00-382a91f8abc2(IDbCommand , Object )
   在 Dapper.CommandDefinition.SetupCommand(IDbConnection cnn, Action`2 paramReader) 位置 D:\Dev\dapper-dot-net\Dapper NET40\SqlMapper.cs:行号 191
   在 Dapper.SqlMapper.ExecuteCommand(IDbConnection cnn, CommandDefinition& command, Action`2 paramReader) 位置 D:\Dev\dapper-dot-net\Dapper NET40\SqlMapper.cs:行号 3395
   在 Dapper.SqlMapper.ExecuteImpl(IDbConnection cnn, CommandDefinition& command) 位置 D:\Dev\dapper-dot-net\Dapper NET40\SqlMapper.cs:行号 1346
   在 Dapper.SqlMapper.Execute(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable`1 commandTimeout, Nullable`1 commandType) 位置 D:\Dev\dapper-dot-net\Dapper NET40\SqlMapper.cs:行号 1221
   在 AiDiEr.ServicesTest.UnitServiceTest.TestGuid2Blob() 位置 F:\Git\IdealAPP\idr_shoppers_app\idr_shoppers_app\IdealShopApp\AiDiEr.ServicesTest\UnitServiceTest.cs:行号 149

[fuyong859050943]

sorry, how can I use dapper get the data from oracle into C# GUID,the oracle type mybe blob or varchar2 ?

[NickCraver]

We'd have to have more info here. What is the schema and code you're using to access it? Just an error with no context isn't anything we can go on here.

[mgravell]

Note there should be a 1.50.3-beta1 that might already fix this, but I
think nuget ate it. Will re-upload tomorrow.

On 8 Nov 2016 11:49 a.m., "Nick Craver" [email protected] wrote:

We'd have to have more info here. What is the schema and code you're using
to access it? Just an error with no context isn't anything we can go on
here.

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
#633 (comment),
or mute the thread
https://github.com/notifications/unsubscribe-auth/AABDsN6EFDhHXy2XBURzBTRfiuWurItUks5q8GG5gaJpZM4KhFHq
.

[tangdf]

1. Remove Guid type.

            SqlMapper.RemoveTypeMap(typeof(Guid));
            SqlMapper.RemoveTypeMap(typeof(Guid?));

2. Create a custom TypeHandler.

    public class GuidTypeHandler : SqlMapper.ITypeHandler
    {
        public void SetValue(IDbDataParameter parameter, object value)
        {
            OracleParameter oracleParameter = (OracleParameter) parameter;
            oracleParameter.OracleDbType = OracleDbType.Raw;
            parameter.Value = value;
        }

        public object Parse(Type destinationType, object value)
        {
            return new Guid((byte[]) value);
        }
    }

3. Add the custom TypeHandler.

            SqlMapper.AddTypeHandler(typeof(Guid), new GuidTypeHandler());

[mgravell]

What throws that exception? Is it the Parse call by any chance? If so: what exactly is value? Is it a byte array? And if so: what length? Or is it an oracle primitive type? Side note: you need to be really really careful here. There's a reason that dapper doesn't automatically convert guids to byte arrays (and back): SQL server and oracle actually use different "endianness" for this conversion (except: it isn't just a "reverse all the bytes - it is something like "4 bytes reversed, then 4 chunks of 2 bytes reversed"). So you really really need to check that the guid you get back is the one you were thinking of. You can't just assume that new Guid(bytearray) does what you think. On 25 Dec 2017 10:21 a.m., "tangdf" <[email protected]> wrote: 1. Remove Guid type. SqlMapper.RemoveTypeMap(typeof(Guid)); SqlMapper.RemoveTypeMap(typeof(Guid?)); 1. Create a custom TypeHandler. public class GuidTypeHandler : SqlMapper.ITypeHandler { public void SetValue(IDbDataParameter parameter, object value) { OracleParameter oracleParameter = (OracleParameter) parameter; oracleParameter.OracleDbType = OracleDbType.Raw; parameter.Value = ((Guid) value); } public object Parse(Type destinationType, object value) { return new Guid((byte[]) value); } } 1. Add the custom TypeHandler. SqlMapper.AddTypeHandler(typeof(Guid), new GuidTypeHandler()); — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#633 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AABDsNzjcGnahhYLzcHPbTSxyfkPEg9iks5tD3czgaJpZM4KhFHq> .

[tangdf]

Is only for oracle database, use Raw(16) data type to store the Guid type.

1. When parameter type is Guid type, throw ArgumentException.

           Guid id = Guid.Parse("{47F2BD22-93DF-4CB6-8143-C801376D592F}");
           
           using (OracleConnection oracleConnection =  new OracleConnection("***********"))
           {
               oracleConnection.Execute(@"delete  rooms_bak where ROOMID=:id", new
               {
                   id = id
               });
           }

   System.ArgumentException
   Value does not fall within the expected range.
   at Oracle.ManagedDataAccess.Client.OracleParameter.set_DbType(DbType value)
   at ParamInfo5347f843-dcf0-4df8-9cc4-b50200c576c8(IDbCommand , Object )
   at Dapper.CommandDefinition.SetupCommand(IDbConnection cnn, Action`2 paramReader)
   at Dapper.SqlMapper.ExecuteCommand(IDbConnection cnn, CommandDefinition& command, Action`2 paramReader)
   at Dapper.SqlMapper.ExecuteImpl(IDbConnection cnn, CommandDefinition& command)
   at Dapper.SqlMapper.Execute(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable`1 commandTimeout, Nullable`1 commandType)
   at Dapper.Oracle.Tests.AppTest.Dapper_Oracle_Guid_Test() in G:\source\Dapper-master\Dapper.Oracle.Tests\AppTest.cs:line 32
   

2. When query from database, throw InvalidCastException.

           using (OracleConnection oracleConnection =  new OracleConnection("***********")
           {
               MyEntity myEntity = oracleConnection.QuerySingle<MyEntity>("select RoomId from rooms_bak where  ROWNUM <= (1)");
           }
           public class MyEntity
           {
               public Guid RoomId { get; set; }
           }

   System.Data.DataException
   Error parsing column 0 (ROOMID=System.Byte[] - Object)
   at Dapper.SqlMapper.ThrowDataException(Exception ex, Int32 index, IDataReader reader, Object value)
   at Deserialize97421cba-aa6e-4967-a115-3de767aeb463(IDataReader )
   at Dapper.SqlMapper.QueryRowImpl[T](IDbConnection cnn, Row row, CommandDefinition& command, Type effectiveType)
   at Dapper.SqlMapper.QuerySingle[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable`1 commandTimeout, Nullable`1 commandType)
   at Dapper.Oracle.Tests.AppTest.Dapper_Oracle_Guid_Test() in G:\source\Dapper-master\Dapper.Oracle.Tests\AppTest.cs:line 42
   
   System.InvalidCastException
   Specified cast is not valid.
   at Deserialize97421cba-aa6e-4967-a115-3de767aeb463(IDataReader )
   

[mgravell]

Let's take those things separately. The second one sounds like the value is not a byte[], so again : what is it? You can't cast an object to a byte[] unless it is actually a byte[]. Please check what the value actually is in the Parse method The first one sounds like a disagreement over the DB type; I'm not sure on the timing of that one compared to the other code. Has your custom method already been called by that point?

…

On 25 Dec 2017 11:26 a.m., "tangdf" ***@***.***> wrote: Is only for oracle database, use Raw(16) data type to store the Guid type. 1. When parameter type is Guid type, throw ArgumentException. Guid id = Guid.Parse("{47F2BD22-93DF-4CB6-8143-C801376D592F}"); using (OracleConnection oracleConnection = new OracleConnection("***********")) { oracleConnection.Execute(@"delete rooms_bak where ROOMID=:id", new { id = id }); } System.ArgumentException Value does not fall within the expected range. at Oracle.ManagedDataAccess.Client.OracleParameter.set_DbType(DbType value) at ParamInfo5347f843-dcf0-4df8-9cc4-b50200c576c8(IDbCommand , Object ) at Dapper.CommandDefinition.SetupCommand(IDbConnection cnn, Action`2 paramReader) at Dapper.SqlMapper.ExecuteCommand(IDbConnection cnn, CommandDefinition& command, Action`2 paramReader) at Dapper.SqlMapper.ExecuteImpl(IDbConnection cnn, CommandDefinition& command) at Dapper.SqlMapper.Execute(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable`1 commandTimeout, Nullable`1 commandType) at Dapper.Oracle.Tests.AppTest.Dapper_Oracle_Guid_Test() in G:\source\Dapper-master\Dapper.Oracle.Tests\AppTest.cs:line 32 2. When query from database, throw InvalidCastException. using (OracleConnection oracleConnection = new OracleConnection("***********") { MyEntity myEntity = oracleConnection.QuerySingle<MyEntity>("select * from rooms_bak where ROWNUM <= (1)"); } System.Data.DataException Error parsing column 0 (ROOMID=System.Byte[] - Object) at Dapper.SqlMapper.ThrowDataException(Exception ex, Int32 index, IDataReader reader, Object value) at Deserialize97421cba-aa6e-4967-a115-3de767aeb463(IDataReader ) at Dapper.SqlMapper.QueryRowImpl[T](IDbConnection cnn, Row row, CommandDefinition& command, Type effectiveType) at Dapper.SqlMapper.QuerySingle[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable`1 commandTimeout, Nullable`1 commandType) at Dapper.Oracle.Tests.AppTest.Dapper_Oracle_Guid_Test() in G:\source\Dapper-master\Dapper.Oracle.Tests\AppTest.cs:line 42 System.InvalidCastException Specified cast is not valid. at Deserialize97421cba-aa6e-4967-a115-3de767aeb463(IDataReader ) — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#633 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AABDsEM2Vf3YWJz063GBqMGCBbDKnNjWks5tD4ZTgaJpZM4KhFHq> .

[tangdf]

1. OracleParameter class can't set DbType property to DbType.Guid

           var oracleParameter = new OracleParameter();
           oracleParameter.DbType = DbType.Guid;

   System.ArgumentException
   Value does not fall within the expected range.
   at Oracle.ManagedDataAccess.Client.OracleParameter.set_DbType(DbType value)
   at Dapper.Oracle.Tests.AppTest.Dapper_Oracle_Guid_Test() in G:\source\Dapper-master\Dapper.Oracle.Tests\AppTest.cs:line 24

   The following is the same error :

            var oracleParameter = new OracleParameter();
            oracleParameter.Value = Guid.NewGuid();

   System.ArgumentException
   Value does not fall within the expected range.
   at Oracle.ManagedDataAccess.Client.OracleParameter.set_Value(Object value)
   at Dapper.Oracle.Tests.AppTest.Dapper_Oracle_Guid_Test() in G:\source\Dapper-master\Dapper.Oracle.Tests\AppTest.cs:line 47

   Successful code is :

            var oracleParameter = new OracleParameter();
            oracleParameter.DbType = DbType.Object;
            oracleParameter.OracleDbType = OracleDbType.Raw;
            oracleParameter.Value = Guid.NewGuid();

2. Oracle Database Online Documentation suggested that use Raw(16) data type to store the Guid type.
   https://docs.oracle.com/cd/E11882_01/win.112/e23174/featLINQ.htm#ODPNT219.
   Raw type storage format is binary，this test is successful.

       [Fact]
       public void Dapper_Oracle_Guid_Type_Test()
       {
           object guidValue = null;
   
           using (OracleConnection oracleConnection =
               new OracleConnection("**********"))
           {
               using (IDataReader dataReader = oracleConnection.ExecuteReader("select RoomId from rooms_bak where  ROWNUM <= (1)"))
               {
                   while (dataReader.Read())
                   {
                       guidValue = dataReader.GetValue(0);
                   }
               }
           }
   
           Assert.NotNull(guidValue);
           Assert.IsType<byte[]>(guidValue);
       }

[ben-at-sparq]

I've had a look through the issues related to GUIDs on Oracle and it's unclear whether or not they are meant to be supported.
I have tried both CHAR(32) and RAW(16) but Dapper doesn't want to convert either into a .NET Guid class.
Is this possible without performing the type conversion yourself?
(I would assume these are the Oracle column data types that need to be used to avoid endian issues using Guid.ToString)

[mgravell]

The RAW(16) gets very messy because of the problem of endianness; there are two choices in the wild, and different providers have gone different ways - there is no choice we can make that will work reliably, and we don't have the info available to choose between mixed-endian vs big-endian. Supporting something string-based sounds achievable, but sub-optimal - very vexingly.

…

On Mon, 21 May 2018 at 23:41, ben-at-sparq ***@***.***> wrote: I've had a look through the issues related to GUIDs on Oracle and it's unclear whether or not they are meant to be supported. I have tried both CHAR(32) and RAW(16) but Dapper doesn't want to convert either into a .NET Guid class. Is this possible without performing the type conversion yourself? — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#633 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AABDsCuJXEDbNdF90b67RRPPS7aZ8uZkks5t00KBgaJpZM4KhFHq> .

-- Regards, Marc

[ben-at-sparq]

Oh OK.. I thought RAW(16) would solve all endian problems if new Guid(byte[]) and Guid.ToByteArray were used.

Thanks for all the great work though!

Oh wait.. I think I get it. You're talking about supporting some source of Guid generation/interpretation outside of .NET?

[armplinker]

I added a helper for string to do this. Takes Oracle makes .Net and vice versa. Cannot take any credit. Debating it here seems a bit fruitless. Want it? Sent from my iPhone. Please pardon the inevitable terseness and typos, some of which are the fault of auto-correct helpfulness.

…

On May 21, 2018, at 19:27, Marc Gravell ***@***.***> wrote: The RAW(16) gets very messy because of the problem of endianness; there are two choices in the wild, and different providers have gone different ways - there is no choice we can make that will work reliably, and we don't have the info available to choose between mixed-endian vs big-endian. Supporting something string-based sounds achievable, but sub-optimal - very vexingly. On Mon, 21 May 2018 at 23:41, ben-at-sparq ***@***.***> wrote: > I've had a look through the issues related to GUIDs on Oracle and it's > unclear whether or not they are meant to be supported. > I have tried both CHAR(32) and RAW(16) but Dapper doesn't want to convert > either into a .NET Guid class. > Is this possible without performing the type conversion yourself? > > — > You are receiving this because you commented. > Reply to this email directly, view it on GitHub > <#633 (comment)>, > or mute the thread > <https://github.com/notifications/unsubscribe-auth/AABDsCuJXEDbNdF90b67RRPPS7aZ8uZkks5t00KBgaJpZM4KhFHq> > . > -- Regards, Marc — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub, or mute the thread.

[ben-at-sparq]

Sending a link couldn't hurt but at this point the question was largely academic - I was just checking if Guid support was built-in when using Oracle.