对oracle的特殊字段类型提供支持

gmij commented 4 years ago

SmartSql version

last version

Database provider and version

oracle any version oracle core provider

Steps to reproduce

oracle的字段类型中的clob/blob类型，以及自定义类型，请提供一下支持。
oracle中使用存储过程返回数据集，需要打开游标，请提供对参数设置类型为Cursor
Expected result

Actual result

    public override IDataParameter CreateCursorParameter(string name)
    {
        var p = CreateDbParameter();
        p.ParameterName = name;
        p.OracleDbType = OracleDbType.RefCursor;
        p.Direction = ParameterDirection.Output;
        return p;
    }

    private new OracleParameter CreateDbParameter()
    {
        return base.CreateDbParameter() as OracleParameter;
    }

    public override IDataParameter CreateClobParameter(string name, object obj)
    {
        var p = CreateDbParameter();
        p.OracleDbType = OracleDbType.Clob;
        p.ParameterName = name;
        p.Value = obj;
        return p;
    }

    public override IDataParameter CreateBlobParameter(string name, object obj)
    {
        var p = CreateDbParameter();
        p.OracleDbType = OracleDbType.Blob;
        p.ParameterName = name;
        p.Value = obj;
        return p;
    }

Stack trace

gmij commented 4 years ago

针对Oracle大字段（clob和blob）目前实测，有如下两种方法：

[A] 使用RequestContext.Request进行提交，可以无视参数类型设置，测试代码如下：

    [Fact]
    public void Test2()
    {
        var content = _sql.ExecuteScalar<string>(new RequestContext()
        {
            RealSql = "select flowdefine from flows where flowid = 21"
        });
        content += content + content + content;
        _testOutputHelper.WriteLine(content.Length.ToString());
        var t = _sql.Execute(new RequestContext()
        {
            RealSql = "update flows set flowdefine = :flowdefine where flowid = 21",
            Request = new
            {
                flowdefine= content
            }
        });
        _testOutputHelper.WriteLine(t.ToString());
    }

[B] 使用RequestContext.Parameters进行提交，代码无法运行，测试代码如下：
```
    [Fact]
    public void Test3()
    {
        var content = _sql.ExecuteScalar<string>(new RequestContext()
        {
            RealSql = "select flowdefine from flows where flowid = 21"
        });
        content += " ";
        _testOutputHelper.WriteLine(content.Length.ToString());
        var t = _sql.Execute(new RequestContext()
        {
            RealSql = "update flows set flowdefine = :flowdefine where flowid = 21",
            Parameters = new SqlParameterCollection()
            {
                new SqlParameter("flowdefine", content)
            }
        });
        _testOutputHelper.WriteLine(t.ToString());
    }
```
引发的异常如下： Oracle.ManagedDataAccess.Client.OracleException : ORA-01008: 并非所有变量都已绑定 at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone) at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQuery(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, Boolean isFromEF) at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery() at SmartSql.Command.CommandExecuter.<>c__DisplayClass10_0.b__0() at SmartSql.Command.CommandExecuter.ExecuteWrap[TResult](Func`1 executeImpl, ExecutionContext executionContext, String operation)

和开发团队中的正超沟通后，发现是在RequestContext中的方法SetupParameters里，对Parameters 做了覆盖，导致外部设置值无效。原始代码如下：

        public override void SetupParameters()
        {
            Parameters = SqlParameterCollection.Create<TRequest>(Request,
                ExecutionContext.SmartSqlConfig.Settings.IgnoreParameterCase);
        }

修改为如下代码后，可正常工作：

        public override void SetupParameters()
        {
            if (Parameters == null)
                Parameters = SqlParameterCollection.Create<TRequest>(Request,
                    ExecutionContext.SmartSqlConfig.Settings.IgnoreParameterCase);
        }

gmij commented 4 years ago

关于调用Oracle存储过程返回记录集的问题，目前，实测，可以调用的代码如下，希望虎哥能提供更完善的解决方案：

        [Fact]
        public void Test4()
        {
            var context = new RequestContext()
            {
                RealSql = "TREEDATATABLE.GET_ORGANISE",
                CommandType = CommandType.StoredProcedure,
                Parameters = new SqlParameterCollection(true)
                {
                    new SqlParameter("VALLID", ""),
                    new SqlParameter("VORGID", "0"),
                    // 用于接收记录集的返回参数，必须要先声明
                    new SqlParameter("RETURN_LIST", "")
                }
            };
            // 修改RETURN_LIST的参数类型和参数的方向
            context.Parameters["RETURN_LIST"].SourceParameter =
                new OracleParameter("RETURN_LIST", 
                     OracleDbType.RefCursor, ParameterDirection.Output);
            // 只有设置了上面的这句话，下面的这条语句，才能取得到东西，不然Rows.Count始终是0
            var ds = _sql.GetDataSet(context).Tables[0];
            _testOutputHelper.WriteLine(ds.Rows.Count.ToString());
        }

参考资料：Oracle官方使用存储过程返回记录集

Ahoo-Wang commented 4 years ago

1 . 使用 TypeHandler 即可。

Ahoo-Wang commented 4 years ago

ORA-01008: 并非所有变量都已绑定该问题请引入：

Install-Package SmartSql.Oracle

new SmartSqlBuilder().
    UseOracleCommandExecuter()

gmij commented 4 years ago

SmartSql.Oracle 这个包，已经引入过了。

Ahoo-Wang commented 4 years ago

https://github.com/dotnetcore/SmartSql/commit/3f0ac5b88881e0246f328616a018bf63f46490b5

gmij commented 4 years ago

4.1.46版本中，已解决。

gxfmmm commented 4 years ago

can it suport type 'object'？/ 可以兼容数据库自定义参数类型吗？

dotnetcore / SmartSql