How to pass parameters to a PostgreSQL query

Posted by: anthony.zoccolillo on 10 April 2019, 12:13 pm EST

    • Post Options:
    • Link

    Posted 10 April 2019, 12:13 pm 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?

  • Posted 12 April 2019, 4:45 am 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.

  • Posted 12 April 2019, 10:48 am EST - Updated 30 September 2022, 5:45 pm 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.d__59
    1.MoveNext()    at System.Linq.Buffer
    1…ctor(IEnumerable
    1 source)    at System.Linq.OrderedEnumerable
    1.d__1.MoveNext()

    at System.Linq.Enumerable.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)

  • Posted 12 April 2019, 10:49 am EST

    Typo…

    AND casinoname IN (@sites)

  • Posted 12 April 2019, 10: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

  • Posted 12 April 2019, 10: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.d__59
    1.MoveNext()    at System.Linq.Buffer
    1…ctor(IEnumerable
    1 source)    at System.Linq.OrderedEnumerable
    1.d__1.MoveNext()

    at System.Linq.Enumerable.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)

  • Posted 12 April 2019, 1:53 pm EST

    BTW…

    Dsn=AIRPGSQL;Uid=username;Pwd=password;

  • Posted 15 April 2019, 5:38 am 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.

  • Posted 15 April 2019, 11: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

  • Posted 16 April 2019, 2:34 am 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