I'm trying to execute a SQL statement with an embedded @parameter, and am receiving the error...
VI.DB.DatabaseException: Database error 137: Must declare the scalar variable "@FromDate".
I've uploaded a SQL statement with an @parameter using the API-Designer.
I added an end-point to call the SQL-statement.
When I invoke the end-point, I see the above error appear in the API-Server log.
If I copy/paste my end-point code to the ApiSamples project that ships with 8.2, the code runs, and the parameter works just fine. No errors, and my SQL is getting filtered on FromDate.
Both the actual API-Server, as well as the self-hosted API-Server give the error. It appears my C# code as well as my SQL statement are ok.
What can be causing this?
Here is the full error...
2022-04-14 09:53:04.7121 ERROR ( WebLog ) : An error occurred while processing the request: GET localhost/.../journalwithfailure System.Exception: An error occurred while processing the request: GET localhost/.../journalwithfailure ---> VI.Base.ViException: Error running statement: SELECT
.... WHERE isnull(@FromDate, '') = '' Or j.CreationTime > @FromDate
ORDER BY j.CreationTime DESC ---> VI.DB.DatabaseException: Database error 137: Must declare the scalar variable "@FromDate". ---> System.Data.SqlClient.SqlException: Must declare the scalar variable "@FromDate".
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader(Boolean isInternal, Boolean forDescribeParameterEncryption)
at System.Data.SqlClient.SqlCommand.InternalEndExecuteReader(IAsyncResult asyncResult, String endMethod, Boolean isInternal)
at System.Data.SqlClient.SqlCommand.EndExecuteReaderInternal(IAsyncResult asyncResult)
at System.Data.SqlClient.SqlCommand.EndExecuteReaderAsync(IAsyncResult asyncResult)
at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at VI.DB.DataAccess.PhysicalMsSqlConnection._AsyncSqlCommand.<ExecuteReaderAsync>d__33.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at VI.DB.DataAccess.SafeDbCommand.<_CheckedAsync>d__43`1.MoveNext()
--- End of inner exception stack trace ---
at VI.DB.DataAccess.SafeDbCommand.<_CheckedAsync>d__43`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at VI.DB.DataAccess.ReadOnlyDbSession.<>c__DisplayClass38_0.<<SqlExecuteAsync>b__0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at VI.DB.DataAccess.ReadWriteDbSession.<IgnoreBrokenConnectionAsync>d__49`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at VI.DB.DataAccess.ReadOnlyDbSession.<SqlExecuteAsync>d__38.MoveNext()
--- End of inner exception stack trace ---
at VI.DB.DataAccess.ReadOnlyDbSession.<SqlExecuteAsync>d__38.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at VI.DB.DataAccess.DbStatementRunner.<SqlExecuteAsync>d__3.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at QBM.CompositionApi.Definition.SqlStatementMethod.<GetAsync>d__19.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at QBM.CompositionApi.Definition.SqlStatementRouteBuilder.<GetAsync>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at QBM.CompositionApi.ApiManager.JsonResponseBuilder.InnerJsonResponseBuilder.<WriteAsync>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at QBM.CompositionApi.Compression.CompressionResponseBuilder.CompressedResponse.<WriteAsync>d__4.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at QBM.CompositionApi.ApiManager.MethodRequestHandler.<SendAsync>d__16.MoveNext()
--- End of inner exception stack trace ---
