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
j.CreationTime,
j.ProjectionConfigDisplay,
j.ProjectionContext,
j.ProjectionStartInfoDisplay,
j.ProjectionState,
--j.SystemVariableSetDisplay,
s.DisplayName As SyncProjDisplayName,
f.ObjectDisplay,
f.ObjectIdentifier,
f.ObjectState,
f.ProjectionStepDisplay,
f.SchemaTypeName,
--f.SchemaUid,
f.MessageType,
f.Reason,
j.UID_DPRJournal,
s.UID_DPRShell
FROM dbo.DPRJournal j
INNER JOIN dbo.DPRProjectionConfig c
ON c.UID_DPRProjectionConfig = j.UID_DPRProjectionConfig
INNER JOIN dbo.DPRShell s
ON c.UID_DPRShell = s.UID_DPRShell
INNER JOIN (
SELECT
'E' as MessageType,
ObjectDisplay,
ObjectIdentifier,
ObjectState,
ProjectionStepDisplay,
SchemaTypeName,
SchemaUid,
Reason,
UID_DPRJournal
FROM dbo.DPRJournalFailure
UNION
SELECT
MessageType,
null as ObjectDisplay,
null as ObjectIdentifier,
MessageContext as ObjectState,
null As ProjectionStepDisplay,
null as SchemaTypeName,
null as SchemaUid,
MessageString As Reason,
UID_DPRJournal
FROM dbo.DPRJournalMessage
) f
ON f.UID_DPRJournal = j.UID_DPRJournal
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 ---
Here is my code...
namespace QBM.CompositionApi
{
public class CCC_JournalWithFailure : IApiProvider
{
public void Build(IApiBuilder builder)
{
builder.AddMethod(Method.Define("sync/journalwithfailure")
// Insert the statement name (QBMLimitedSQL.Ident_QBMLimitedSQL) and the type
.HandleGetBySqlStatement("CCC_SyncJournalWithFailures", SqlStatementType.SqlExecute)
// Define the result schema columns and data types
.WithResultColumns(
new SqlResultColumn("CreationTime", VI.Base.ValType.Date) { ClientProperty = { TableName = "DPRJournal", Display = "Creation time", IsValidColumnForFiltering = true, IsValidColumnForSorting = true }, LikeDbTable = "DPRJournal", LikeDbColumn = "CreationTime" },
new SqlResultColumn("ProjectionConfigDisplay", VI.Base.ValType.String) { ClientProperty = { TableName = "DPRJournal", Display = "Synchronization workflow" }, LikeDbTable = "DPRJournal", LikeDbColumn = "ProjectionConfigDisplay" },
new SqlResultColumn("ProjectionContext", VI.Base.ValType.String) { ClientProperty = { TableName = "DPRJournal", Display = "Synchronization context" }, LikeDbTable = "DPRJournal", LikeDbColumn = "ProjectionContext" },
new SqlResultColumn("ProjectionStartInfoDisplay", VI.Base.ValType.String) { ClientProperty = { TableName = "DPRJournal", Display = "Start configuration" }, LikeDbTable = "DPRJournal", LikeDbColumn = "ProjectionStartInfoDisplay" },
new SqlResultColumn("ProjectionState", VI.Base.ValType.String) { ClientProperty = { TableName = "DPRJournal", Display = "Projection state" }, LikeDbTable = "DPRJournal", LikeDbColumn = "ProjectionState" },
new SqlResultColumn("SyncProjDisplayName", VI.Base.ValType.String) { ClientProperty = { TableName = "DPRShell", Display = "Synchronization project Display name" }, LikeDbTable = "DPRShell", LikeDbColumn = "SyncProjDisplayName" },
new SqlResultColumn("ObjectDisplay", VI.Base.ValType.String) { ClientProperty = { TableName = "DPRJournalFailure", Display = "Object display" }, LikeDbTable = "DPRJournalFailure", LikeDbColumn = "ObjectDisplay" },
new SqlResultColumn("ObjectIdentifier", VI.Base.ValType.String) { ClientProperty = { TableName = "DPRJournalFailure", Display = "Object identifier" }, LikeDbTable = "DPRJournalFailure", LikeDbColumn = "ObjectIdentifier" },
new SqlResultColumn("ObjectState", VI.Base.ValType.String) { ClientProperty = { TableName = "DPRJournalFailure", Display = "Object state" }, LikeDbTable = "DPRJournalFailure", LikeDbColumn = "ObjectState" },
new SqlResultColumn("ProjectionStepDisplay", VI.Base.ValType.String) { ClientProperty = { TableName = "DPRJournalFailure", Display = "Synchronization step display" }, LikeDbTable = "DPRJournalFailure", LikeDbColumn = "ProjectionStepDisplay" },
new SqlResultColumn("SchemaTypeName", VI.Base.ValType.String) { ClientProperty = { TableName = "DPRJournalFailure", Display = "Schema type" }, LikeDbTable = "DPRJournal", LikeDbColumn = "SchemaTypeName" },
new SqlResultColumn("MessageType", VI.Base.ValType.String) { ClientProperty = { TableName = "DPRJournalMessage", Display = "Message type", IsValidColumnForFiltering = true }, LikeDbTable = "DPRJournalMessage", LikeDbColumn = "MessageType" },
new SqlResultColumn("Reason", VI.Base.ValType.Text) { ClientProperty = { TableName = "DPRJournalFailure", Display = "Error reason" }, LikeDbTable = "DPRJournalFailure", LikeDbColumn = "Reason" },
new SqlResultColumn("UID_DPRJournal", VI.Base.ValType.String) { ClientProperty = { TableName = "DPRJournal", Display = "Synchronization journal" }, LikeDbTable = "DPRJournal", LikeDbColumn = "UID_DPRJournal" },
new SqlResultColumn("UID_DPRShell", VI.Base.ValType.String) { ClientProperty = { TableName = "DPRShell", Display = "Synchronization project" }, LikeDbTable = "DPRShell", LikeDbColumn = "UID_DPRShell" }
)
.WithParameter(new RequestParameter("FromDate") { Description = "Sync-Journal Entries after Date", DefaultValue = "", Type = typeof(string), IsInQuery = true })
);
}
}
}