CODEWITHSUNDEEP
c#asp.net-coredapper
Sylla
Sylla

Reputation: 77

dapper with oracle view

i am struggling using Dapper with just a select query from a view, the view is created manually from sql developper and working fine both on test and production. But when i publish the app on windows server i am getting this error:

ORA-01858: A non-numeric character was found where a numeric was expected

bellow is my class definition:

public class rib_model_new
{
    public int ID { get; set; }
    public string CUST_AC_NO { get; set; }
    public string AC_DESC { get; set; }
    public string CUST_NO { get; set; }
    public string AC_OPEN_DATE { get; set; }
    public string CUSTOMER_PREFIX { get; set; }
    public string E_MAIL { get; set; }
    public string ACY_CURR_BALANCE { get; set; }
    public string MOBILE_NUMBER { get; set; }
}

and this is my code with dapper:

var cmdDef = new CommandDefinition("select * from COMPTE_OPEN_NEW_2 where AC_OPEN_DATE='19-aug-22'",commandType: CommandType.Text, flags: CommandFlags.NoCache);
                rib_models = await objconn.QueryAsync<rib_model_new>(cmdDef);

Bellow is my view definition:

CREATE OR REPLACE VIEW COMPTE_OPEN_NEW_2 AS SELECT c.customer_prefix, cust_ac_no,cust_no, ac_desc, AC_OPEN_DATE,acy_curr_balance, c.e_mail, c.mobile_number FROM FCUBSLIVE.STTM_CUST_ACCOUNT a,FCUBSLIVE.sttm_customer d, FCUBSLIVE.sttm_cust_personal c WHERE a.auth_stat = 'A' and a.cust_no = d.customer_no and d.customer_no = c.customer_no AND a.record_stat = 'O';

This is the query im running from my code and its working from sql developper:

select * from COMPTE_OPEN_NEW_2 where ac_open_date='17-aug-22'

And finally this image show my view data types: enter image description here

from the stacktrace:

    at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
   at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, Int64 internalInitialJSONFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, IEnumerable`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)
   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
--- End of stack trace from previous location ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Dapper.SqlMapper.<QueryAsync>d__33`1.MoveNext() in /_/Dapper/SqlMapper.Async.cs:line 418
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at CheckbookHangfire.Services.Services_Flexcube_Test.<GetAccountsCreated_new>d__18.MoveNext() in C:\Users\Administrator\source\repos\CheckbookHangfire\CheckbookHangfire\Services\Services_Flexcube_Test.cs:line 318

From this Trace i am trying to understand this error :

 at Dapper.SqlMapper.<QueryAsync>d__33`1.MoveNext() in /_/Dapper/SqlMapper.Async.cs:line 418

please what does it mean, what dapper is doing here, is he cannot map the result to my model. All my models fields has same name with the query result columns.

can someone please help??

Best Regards...

Upvotes: 1

Views: 650

Answers (2)

Sylla
Sylla

Reputation: 77

Good Afternoon, How are you today. Finally i have solved my issue by deploying the application in another server. i think dapper has in memory cache from another model who i don't know really. but with deploying the application in another windows server works.

Thanks Everyone.

Best Regards...

Upvotes: 0

Palle Due
Palle Due

Reputation: 6292

It looks like it's your date format. It's very presumptious to think all servers would agree to '19-aug-22'. Change the code to:

var cmdDef = new CommandDefinition("select * from COMPTE_OPEN_NEW_2 where AC_OPEN_DATE=:openDate",commandType: CommandType.Text, flags: CommandFlags.NoCache,
new { openDate = new Datetime(2022,8,19)});
rib_models = await objconn.QueryAsync<rib_model_new>(cmdDef);

When you ask yourself: "Should I use a parameterized query instead of adding parameters directly in the SQL?", the answer is always yes.

Upvotes: 1

Related Questions