How to pass an array parameter to a stored procedure

Discussion of open issues, suggestions and bugs regarding PgDAC (PostgreSQL Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Niels
Posts: 7
Joined: Fri 20 Apr 2012 05:24

How to pass an array parameter to a stored procedure

Post by Niels » Tue 02 Oct 2018 09:03

Hi,
I have a stored procedure with the following header:

Code: Select all

CREATE OR REPLACE FUNCTION "UpdateClassGeneral" (
  "ActiveRatios" integer [],
  "ActiveClass" varchar,
  "ActiveClassProjCodesSQL" varchar []
)
How can I pass the integer- and varchar array to my TPgStoredProc component?
There is no documentation for it available?
Or is there a work around?

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: How to pass an array parameter to a stored procedure

Post by azyk » Tue 02 Oct 2018 12:48

You should pass array parameter as string value of TPgStoredProc parameter. For example:

Code: Select all

  PgStoredProc1.StoredProcName := '"UpdateClassGeneral"';
  PgStoredProc1.Prepare;

  PgStoredProc1.ParamByName('ActiveRatios').AsString := '{1,2,3}';
  PgStoredProc1.ParamByName('ActiveClass').Value := 'some text';
  PgStoredProc1.ParamByName('ActiveClassProjCodesSQL').AsString := '{PgDAC,pass,array param}';

  PgStoredProc1.ExecProc;

Niels
Posts: 7
Joined: Fri 20 Apr 2012 05:24

Re: How to pass an array parameter to a stored procedure

Post by Niels » Wed 03 Oct 2018 02:19

Works perfectly now.
Thank you!

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: How to pass an array parameter to a stored procedure

Post by azyk » Wed 03 Oct 2018 08:13

If you have any questions about our products, feel free to contact us.

belohcka
Posts: 1
Joined: Sat 20 Jul 2019 08:42

Re: How to pass an array parameter to a stored procedure

Post by belohcka » Sat 20 Jul 2019 08:54

May somebody help me pls with smtg...

I tryed post data into pg using pgDac and Delphi Berlin.
As date-type i tryed post - array of 'compex-type' with pgStoredProc, and the problem what i have - i cant post array of varchar/text values using '{'attr1','attr2'}' because attr1 and attr2 have values like 'Something ...., something ...'.

p/s. If it so, may u show me a link to a documentation ...

Thank you!

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: How to pass an array parameter to a stored procedure

Post by MaximG » Mon 22 Jul 2019 15:33

Please compose and send us a small sample along with the DLL script that creates the stored procedure that is used in this project, so that could investigate the issue. You can use an e-support form on our website to upload the sample with the DDL script (https://www.devart.com/company/contactform.html)

Post Reply