How to pass parameters to a PostgreSQL query

Posted by: anthony.zoccolillo on 10 April 2019, 3:13 am EST

  • Posted 10 April 2019, 3:13 am EST

    I'm able to connect to PostgreSQL (11.2) and run reports, but I can't figure out the syntax for the parameters in the query. I've tried :parm1 and @parm1 and both error. If I use a question mark (?) it validates but when I run I get an error. Has anyone ever run ActiveReports against PostgreSQL with parameter?
  • Replied 11 April 2019, 7:45 pm EST

    Hello Anthony,

    Could you please share the error messages while using the :parm1 and @parm1 in the query. Also, have you add the parameter in the "DataSet" dialog. Please refer to the following documentation link for more information:
    https://help.grapecity.com/activereports/webhelp/AR13/webframe.html#Parameters.html


    Thanks.
  • Replied 12 April 2019, 1:48 am EST

    Here's my dataset query:
    SELECT casinoname
    ,outletname
    ,tm_name
    ,fullname
    ,RoleName
    ,tm_number
    ,incidentdatetime
    ,reason_cutoff
    ,reason_id
    ,gender
    ,ethnicity
    ,namedescript
    ,securcalled
    ,comments
    ,manager
    ,datecreated
    ,createdby
    FROM public.vw_incidentreports
    WHERE incidentdatetime >= :startdate
    AND incidentdatetime <= :enddate
    AND casinoname ON (:sites)
    order by casinoname, incidentdatetime desc

    Parameters are defined...see attached. '@' and ':' prduce the same error.




    Exception details:
    GrapeCity.ActiveReports.ReportException: An unexpected error occurred. Additional information: 'No mapping exists from object type System.Object[] to a known managed provider native type.' ---> System.ArgumentException: No mapping exists from object type System.Object[] to a known managed provider native type.
    at GrapeCity.ActiveReports.Extensions.EnumerableExtensions.MemoizedBuffer`1.#7kY(Boolean& hasValue)
    at GrapeCity.ActiveReports.Extensions.EnumerableExtensions.MemoizedBuffer`1.<GetEnumerator_>d__30.MoveNext()
    at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
    at System.Linq.Lookup`2.Create[TSource](IEnumerable`1 source, Func`2 keySelector, Func`2 elementSelector, IEqualityComparer`1 comparer)
    at System.Linq.GroupedEnumerable`3.GetEnumerator()
    at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
    at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
    at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
    at GrapeCity.ActiveReports.DataProcessing.DataProcessor.<>c__DisplayClass17.#mg1(ExecutionContext exc)
    at GrapeCity.ActiveReports.DataProcessing.DataProcessor.<>c__DisplayClass10.#8r0(ExecutionContext exc)
    at GrapeCity.ActiveReports.DataProcessing.DataProcessor.<>c__DisplayClass3b.#U.#sZh.#NE0(#TRZ fn)
    at System.Linq.Enumerable.WhereSelectArrayIterator`2.MoveNext()
    at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
    at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
    at GrapeCity.ActiveReports.DataProcessing.DataProcessor.<>c__DisplayClass3b.#U.#sZh.#FB0()
    at GrapeCity.ActiveReports.DataTree`1.Scope.get_Regions()
    at GrapeCity.ActiveReports.DataProcessing.DataTreeGenerator.<>c__DisplayClass3e.#U.#wRi.#PE0()
    at GrapeCity.ActiveReports.DataTree`1.Scope.get_Regions()
    at GrapeCity.ActiveReports.DataTree`1.Scope.get_IsEmpty()
    at GrapeCity.ActiveReports.Rendering.Data.DataScopeBase.#BHA(Content content, Scope scope, Int32 index)
    at GrapeCity.ActiveReports.Rendering.Data.DataScopeBase.#BHA(String name)
    at #wxA.#UzA.Create(ReportRenderingContext reportRenderingContext, ReportItem rdlItem, IDataScopeInternal currentDataContext)
    at GrapeCity.ActiveReports.Rendering.ReportRenderingContext.<>c__DisplayClass10.#f20(ReportItem item)
    at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
    at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
    at System.Linq.Enumerable.<ConcatIterator>d__59`1.MoveNext()
    at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
    at System.Linq.OrderedEnumerable`1.<GetEnumerator>d__1.MoveNext()
    at System.Linq.Enumerable.<SelectIterator>d__5`2.MoveNext()
    at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
    at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
    at GrapeCity.ActiveReports.Layout.L2.CanvasImpl.#n3V[T](IEnumerable`1 canvasItems)
    at GrapeCity.ActiveReports.Layout.L2.#AY(IEnumerable`1 canvasItems, Boolean galleyMode, Object item, Int32 width, Int32 height, Boolean consumeWhiteSpace)
    at GrapeCity.ActiveReports.Layout.L2.#AY(IEnumerable`1 canvasItems, Boolean galleyMode, Object item, Int32 width, Int32 height, Boolean canShrink, Boolean canGrow, Boolean consumeWhiteSpace)
    at GrapeCity.ActiveReports.Layout.ReportCanvasLayout.#AY(IEnumerable`1 items, #saR context, Object item, Int32 width, Int32 height, Boolean canShrink, Boolean canGrow, Boolean consumeWhiteSpace)
    at GrapeCity.ActiveReports.Layout.ReportImpl.#QaX(#saR context, IBody body, Object item, Size`1 contentSize, Nullable`1 size, Boolean consumeWhiteSpace)
    at GrapeCity.ActiveReports.Layout.LayoutEngine.#Lv0(#saR context)
    at GrapeCity.ActiveReports.Layout.LayoutEngine.BuildLayout(LayoutInfo layoutInfo)
    at #GPr.#RLf.#NNf(IReport report, TargetDeviceCapabilities targetDevice, LayoutNotificationCallback callback)
    at GrapeCity.ActiveReports.Export.Image.Page.ImageRenderingExtension.Render(IReport report, StreamProvider streams, NameValueCollection settings)
    at GrapeCity.ActiveReports.Document.PageDocument.Render(IRenderingExtension renderingExtension, StreamProvider streams, NameValueCollection settings, Boolean forceDataRefresh, Boolean forceParameterDataRefresh)
    --- End of inner exception stack trace ---
    at GrapeCity.ActiveReports.Document.PageDocument.Render(IRenderingExtension renderingExtension, StreamProvider streams, NameValueCollection settings, Boolean forceDataRefresh, Boolean forceParameterDataRefresh)
    at GrapeCity.ActiveReports.Viewer.Win.Rdlx.RdlReport.<>c__DisplayClassf.#7yz()
    at GrapeCity.ActiveReports.Viewer.Win.AsyncLoader.<>c__DisplayClass7.#cAz(Object param0, DoWorkEventArgs param1)
    at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)
    at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)
  • Replied 12 April 2019, 1:49 am EST

    Typo...
    AND casinoname IN (@sites)
  • Replied 12 April 2019, 1:52 am EST

    This doesn't error...but the params don't map.
    SELECT casinoname
    ,outletname
    ,tm_name
    ,fullname
    ,RoleName
    ,tm_number
    ,incidentdatetime
    ,reason_cutoff
    ,reason_id
    ,gender
    ,ethnicity
    ,namedescript
    ,securcalled
    ,comments
    ,manager
    ,datecreated
    ,createdby
    FROM public.vw_incidentreports
    WHERE incidentdatetime >= ?
    AND incidentdatetime <= ?
    AND casinoname IN (?)
    order by casinoname, incidentdatetime desc
  • Replied 12 April 2019, 1:53 am EST

    This is the error when trying to run with '?'

    Exception details:
    GrapeCity.ActiveReports.ReportException: An unexpected error occurred. Additional information: 'No mapping exists from object type System.Object[] to a known managed provider native type.' ---> System.ArgumentException: No mapping exists from object type System.Object[] to a known managed provider native type.
    at GrapeCity.ActiveReports.Extensions.EnumerableExtensions.MemoizedBuffer`1.#7kY(Boolean& hasValue)
    at GrapeCity.ActiveReports.Extensions.EnumerableExtensions.MemoizedBuffer`1.<GetEnumerator_>d__30.MoveNext()
    at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
    at System.Linq.Lookup`2.Create[TSource](IEnumerable`1 source, Func`2 keySelector, Func`2 elementSelector, IEqualityComparer`1 comparer)
    at System.Linq.GroupedEnumerable`3.GetEnumerator()
    at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
    at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
    at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
    at GrapeCity.ActiveReports.DataProcessing.DataProcessor.<>c__DisplayClass17.#mg1(ExecutionContext exc)
    at GrapeCity.ActiveReports.DataProcessing.DataProcessor.<>c__DisplayClass10.#8r0(ExecutionContext exc)
    at GrapeCity.ActiveReports.DataProcessing.DataProcessor.<>c__DisplayClass3b.#U.#sZh.#NE0(#TRZ fn)
    at System.Linq.Enumerable.WhereSelectArrayIterator`2.MoveNext()
    at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
    at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
    at GrapeCity.ActiveReports.DataProcessing.DataProcessor.<>c__DisplayClass3b.#U.#sZh.#FB0()
    at GrapeCity.ActiveReports.DataTree`1.Scope.get_Regions()
    at GrapeCity.ActiveReports.DataProcessing.DataTreeGenerator.<>c__DisplayClass3e.#U.#wRi.#PE0()
    at GrapeCity.ActiveReports.DataTree`1.Scope.get_Regions()
    at GrapeCity.ActiveReports.DataTree`1.Scope.get_IsEmpty()
    at GrapeCity.ActiveReports.Rendering.Data.DataScopeBase.#BHA(Content content, Scope scope, Int32 index)
    at GrapeCity.ActiveReports.Rendering.Data.DataScopeBase.#BHA(String name)
    at #wxA.#UzA.Create(ReportRenderingContext reportRenderingContext, ReportItem rdlItem, IDataScopeInternal currentDataContext)
    at GrapeCity.ActiveReports.Rendering.ReportRenderingContext.<>c__DisplayClass10.#f20(ReportItem item)
    at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
    at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
    at System.Linq.Enumerable.<ConcatIterator>d__59`1.MoveNext()
    at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
    at System.Linq.OrderedEnumerable`1.<GetEnumerator>d__1.MoveNext()
    at System.Linq.Enumerable.<SelectIterator>d__5`2.MoveNext()
    at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
    at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
    at GrapeCity.ActiveReports.Layout.L2.CanvasImpl.#n3V[T](IEnumerable`1 canvasItems)
    at GrapeCity.ActiveReports.Layout.L2.#AY(IEnumerable`1 canvasItems, Boolean galleyMode, Object item, Int32 width, Int32 height, Boolean consumeWhiteSpace)
    at GrapeCity.ActiveReports.Layout.L2.#AY(IEnumerable`1 canvasItems, Boolean galleyMode, Object item, Int32 width, Int32 height, Boolean canShrink, Boolean canGrow, Boolean consumeWhiteSpace)
    at GrapeCity.ActiveReports.Layout.ReportCanvasLayout.#AY(IEnumerable`1 items, #saR context, Object item, Int32 width, Int32 height, Boolean canShrink, Boolean canGrow, Boolean consumeWhiteSpace)
    at GrapeCity.ActiveReports.Layout.ReportImpl.#QaX(#saR context, IBody body, Object item, Size`1 contentSize, Nullable`1 size, Boolean consumeWhiteSpace)
    at GrapeCity.ActiveReports.Layout.LayoutEngine.#Lv0(#saR context)
    at GrapeCity.ActiveReports.Layout.LayoutEngine.BuildLayout(LayoutInfo layoutInfo)
    at #GPr.#RLf.#NNf(IReport report, TargetDeviceCapabilities targetDevice, LayoutNotificationCallback callback)
    at GrapeCity.ActiveReports.Export.Image.Page.ImageRenderingExtension.Render(IReport report, StreamProvider streams, NameValueCollection settings)
    at GrapeCity.ActiveReports.Document.PageDocument.Render(IRenderingExtension renderingExtension, StreamProvider streams, NameValueCollection settings, Boolean forceDataRefresh, Boolean forceParameterDataRefresh)
    --- End of inner exception stack trace ---
    at GrapeCity.ActiveReports.Document.PageDocument.Render(IRenderingExtension renderingExtension, StreamProvider streams, NameValueCollection settings, Boolean forceDataRefresh, Boolean forceParameterDataRefresh)
    at GrapeCity.ActiveReports.Viewer.Win.Rdlx.RdlReport.<>c__DisplayClassf.#7yz()
    at GrapeCity.ActiveReports.Viewer.Win.AsyncLoader.<>c__DisplayClass7.#cAz(Object param0, DoWorkEventArgs param1)
    at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)
    at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)
  • Replied 12 April 2019, 4:53 am EST

    BTW....
    Dsn=AIRPGSQL;Uid=username;Pwd=password;
  • Replied 14 April 2019, 8:38 pm EST

    Hello Anthony,

    Sorry to mention that I am not able to reproduce the error using "?" syntax at my end. Could you please check with the basic report and a single parameter and see if the issue is still reproducible with the basic report.

    Thanks.
  • Replied 15 April 2019, 2:47 am EST

    Ok...I can get to work with dates....so I think my where sites IN (?) is the issue...I think I need to parse and quote separate these properly...the WHEN IN is failing. I've had this issue before and I know how to fix it.

    Thanks
  • Marked as Answer

    Replied 15 April 2019, 5:34 pm EST

    Hello Anthony,

    I have created the sample for you to use "IN" operator. Please refer to the attachment.

    Thanks.

    test_SQl.zip
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels