I am using Reporting with Angular connecting to the PostgreSql database. I am getting an error with a custom query. When the query is made by Query Builder, no error occurs. But if I make any changes to the query, the error is displayed.
I am attaching a sample sample.
- To reproduce the problem, create the table below in a Postgre database and configure the connection in appsettings.json:
Codecreate table scientist (id integer, firstname varchar(100), lastname varchar(100));
insert into scientist (id, firstname, lastname) values (1, 'albert', 'einstein');
insert into scientist (id, firstname, lastname) values (2, 'isaac', 'newton');
insert into scientist (id, firstname, lastname) values (3, 'marie', 'curie');
select * from scientist;
- Run the project, open the ReportTestIn report and view (everything should work fine so far);
- Now open Query Build and make any changes (eg any line break). When running the report. You should be able to track the error below;
Call StackDXReporting: Error: [02/05/2021 23:59:01]: Exception occurred. Message: 'StartBuild error'. Exception Details:
System.AggregateException: One or more errors occurred. (Error when trying to populate the datasource. The following exception was thrown:
Query scientist failed to execute.)
---> DevExpress.XtraReports.DataRetrievalException: Error when trying to populate the datasource. The following exception was thrown:
Query scientist failed to execute.
---> System.AggregateException: One or more queries have failed to execute. (Query scientist failed to execute.)
---> DevExpress.DataAccess.Sql.QueryExecutionException: Query scientist failed to execute.
---> DevExpress.DataAccess.Native.Sql.SqlExecutionException: Error trying to execute an SQL query 'select "scientist"."id","scientist"."firstname","scientist"."lastname" from "scientist" "scientist"
where "scientist"."id" in (@ids) ' with parameters:
ids(Object) = System.Int32[].
Error: 42883: operator does not exist: integer = integer[]
---> Npgsql.PostgresException (0x80004005): 42883: operator does not exist: integer = integer[]
at Npgsql.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|194_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
at Npgsql.NpgsqlDataReader.NextResult()
at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior)
at DevExpress.DataAccess.Native.ColumnarDataLoaderHelper.ExecuteReader(IDbCommand command, CommandBehavior commandBehavior, CancellationToken cancellationToken)
at DevExpress.Xpo.Logger.LogManager.Log[T](String category, LogHandler`1 handler, MessageHandler`1 createMessageHandler)
at DevExpress.DataAccess.Native.ColumnarDataLoaderHelper.InternalExecuteReader(Object connectionProvider, IDbCommand command, CommandBehavior commandBehavior, CancellationToken cancellationToken)
at DevExpress.DataAccess.Native.ColumnarDataLoaderHelper.GetData(Dictionary`2 readers, Object connectionProvider, IDbCommand command, CancellationToken cancellationToken, Func`2 prepareSchema, Boolean nativeSkipSupport, Int32 skipCount, Int32 topCount)
at DevExpress.DataAccess.Native.ColumnarDataLoaderHelper.GetData(Dictionary`2 readers, Object connectionProvider, IDbCommand command, CancellationToken cancellationToken, ColumnDescription[] columns, Boolean nativeSkipSupport, Int32 skipCount, Int32 topCount)
at DevExpress.DataAccess.Native.ColumnarDataLoaderHelper.GetData(Dictionary`2 readers, Object connectionProvider, IDbCommand command, CancellationToken cancellationToken, ColumnDescription[] columns, Boolean nativeSkipSupport, Int32 skipCount)
at DevExpress.DataAccess.Native.Sql.ConnectionProviders.ColumnarDataLoader.<>c__DisplayClass7_0.<SelectData>b__0(IDbCommand command)
at DevExpress.DataAccess.Native.Sql.ConnectionProviders.ColumnarDataLoader.CreateAndExecuteQueryCommand[T](IConnectionOptions connectionOptions, Query query, Func`2 func)
Exception data:
Severity: ERROR
SqlState: 42883
MessageText: operator does not exist: integer = integer[]
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Position: 124
File: parse_oper.c
Line: 726
Routine: op_error
--- End of inner exception stack trace ---
at DevExpress.DataAccess.Native.Sql.ConnectionProviders.ColumnarDataLoader.CreateAndExecuteQueryCommand[T](IConnectionOptions connectionOptions, Query query, Func`2 func)
at DevExpress.DataAccess.Native.Sql.ConnectionProviders.ColumnarDataLoader.SelectData(CancellationToken cancellationToken, Query query, ColumnDescription[] columns, IConnectionOptions connectionOptions)
at DevExpress.DataAccess.Native.Sql.QueryExecutor.Execute(CustomSqlQuery query, IEnumerable`1 parameters, CancellationToken cancellationToken)
at DevExpress.DataAccess.Native.Sql.QueryExecutor.Execute(SqlQuery query, Boolean topThousand, CancellationToken cancellationToken)
at DevExpress.DataAccess.Native.Sql.QueryExecutor.Execute(SqlQuery query, CancellationToken cancellationToken)
at DevExpress.DataAccess.Sql.SqlDataSource.ExecuteQuery(SqlQuery query, QueryExecutor executor, CancellationToken cancellationToken)
--- End of inner exception stack trace ---
at DevExpress.DataAccess.Sql.SqlDataSource.ExecuteQuery(SqlQuery query, QueryExecutor executor, CancellationToken cancellationToken)
at DevExpress.DataAccess.Sql.SqlDataSource.FillInternal(IEnumerable`1 parameters, CancellationToken cancellationToken, String[] queriesToFill)
--- End of inner exception stack trace ---
at DevExpress.DataAccess.Sql.SqlDataSource.FillInternal(IEnumerable`1 parameters, CancellationToken cancellationToken, String[] queriesToFill)
at DevExpress.DataAccess.Sql.SqlDataSource.FillCore(IEnumerable`1 parameters, CancellationToken cancellationToken, String[] queriesToFill)
at DevExpress.DataAccess.Sql.SqlDataSource.DevExpress.Data.IListAdapter2.FillList(IServiceProvider serviceProvider, String[] queriesToFill)
at DevExpress.XtraReports.Native.ListAdapterFillerAsync2`1.FillListSynchronously()
at DevExpress.XtraReports.Native.ListAdapterFillerAsync`1.ExecuteCore()
at DevExpress.XtraReports.Native.DataSourceFiller.Execute()
--- End of inner exception stack trace ---
at DevExpress.XtraReports.Native.DataSourceFiller.Execute()
at DevExpress.XtraReports.UI.XtraReportBase.FillDataSources(Boolean skipIfFilled)
at DevExpress.XtraReports.UI.XtraReport.FillDataSources()
at DevExpress.XtraReports.UI.XtraReport.DevExpress.XtraReports.IReport.CreateDocumentCore(Single progressRange, Boolean buildForInstantPreview)
at DevExpress.XtraReports.Native.DocumentCreator.Create(Single progressRange, Boolean buildForInstantPreview)
at DevExpress.XtraReports.UI.XtraReport.CreateDocument(Single progressRange, Boolean buildForInstantPreview)
at DevExpress.XtraReports.UI.XtraReport.CreateDocument(Boolean buildForInstantPreview)
at DevExpress.XtraReports.UI.XtraReport.CreateDocument()
at DevExpress.XtraPrinting.Caching.CachedReportSourceBase.CreateDocumentCore()
at DevExpress.XtraReports.Web.WebDocumentViewer.Native.BuildTaskFactoryWeb.<>c__DisplayClass9_0.<CreateTask>b__0()
at System.Threading.Tasks.Task.InnerInvoke()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of inner exception stack trace ---
Hi Sandro,
Thank you for reporting this issue. I reproduced and forwarded it to the team for further investigation. We'll let you know once we make any progress with it, so stay tuned.
Thanks