Postgres Pro
Downloads
Home   >   mailing lists

Thread: Run Stored procedure - function from VBA

Run Stored procedure - function from VBA

From
Łukasz Jarych
Date:
Hi Guys,

i have example function :

CREATE OR REPLACE FUNCTION totalRecords ()
RETURNS integer AS $total$
declare
total integer;
BEGIN
   SELECT count(*) into total FROM COMPANY;
   RETURN total;
END;
$total$ LANGUAGE plpgsql;

and i want to run it from VBA using odbc connection. 

What is the best way to use it ?

something like this: 

Dim dbCon as new ADODB.Connection
Dim rst as new ADODB.Recordset

Dbcon.connectionstring=”Your connection string goes here!”
Dbcon.open

Rst.open strsql
where strsql is "Select * from totalRecords" or this is not a good solution?

Best,
Jacek

Re: Run Stored procedure - function from VBA

From
Rob Sargent
Date:


On Jun 18, 2018, at 9:47 AM, Łukasz Jarych <jaryszek@gmail.com> wrote:

CREATE OR REPLACE FUNCTION totalRecords ()
RETURNS integer AS $total$
declare
total integer;
BEGIN
   SELECT count(*) into total FROM COMPANY;
   RETURN total;
END;
$total$ LANGUAGE plpgsql;

Re: Run Stored procedure - function from VBA

From
Rob Sargent
Date:


On Jun 18, 2018, at 9:47 AM, Łukasz Jarych <jaryszek@gmail.com> wrote:

Hi Guys,

i have example function :

CREATE OR REPLACE FUNCTION totalRecords ()
RETURNS integer AS $total$
declare
total integer;
BEGIN
   SELECT count(*) into total FROM COMPANY;
   RETURN total;
END;
$total$ LANGUAGE plpgsql;

and i want to run it from VBA using odbc connection. 

What is the best way to use it ?

something like this: 

Dim dbCon as new ADODB.Connection
Dim rst as new ADODB.Recordset

Dbcon.connectionstring=”Your connection string goes here!”
Dbcon.open

Rst.open strsql
where strsql is "Select * from totalRecords" or this is not a good solution?

Best,
Jacek

You need the parentheses after the function name: “select * from totalrecords();"

Re: Run Stored procedure - function from VBA

From
Łukasz Jarych
Date:
Thank you Rob,

question is it is the optimal way to run SP from VBA? 
Or not?

Best,
Jacek

2018-06-19 1:34 GMT+02:00 Rob Sargent <robjsargent@gmail.com>:


On Jun 18, 2018, at 9:47 AM, Łukasz Jarych <jaryszek@gmail.com> wrote:

Hi Guys,

i have example function :

CREATE OR REPLACE FUNCTION totalRecords ()
RETURNS integer AS $total$
declare
total integer;
BEGIN
   SELECT count(*) into total FROM COMPANY;
   RETURN total;
END;
$total$ LANGUAGE plpgsql;

and i want to run it from VBA using odbc connection. 

What is the best way to use it ?

something like this: 

Dim dbCon as new ADODB.Connection
Dim rst as new ADODB.Recordset

Dbcon.connectionstring=”Your connection string goes here!”
Dbcon.open

Rst.open strsql
where strsql is "Select * from totalRecords" or this is not a good solution?

Best,
Jacek

You need the parentheses after the function name: “select * from totalrecords();"


Re: Run Stored procedure - function from VBA

From
Rob Sargent
Date:



On 06/18/2018 09:51 PM, Łukasz Jarych wrote:
Thank you Rob,

question is it is the optimal way to run SP from VBA? 
Or not?

Best,
Jacek

2018-06-19 1:34 GMT+02:00 Rob Sargent <robjsargent@gmail.com>:


On Jun 18, 2018, at 9:47 AM, Łukasz Jarych <jaryszek@gmail.com> wrote:

Hi Guys,

i have example function :

CREATE OR REPLACE FUNCTION totalRecords ()
RETURNS integer AS $total$
declare
total integer;
BEGIN
   SELECT count(*) into total FROM COMPANY;
   RETURN total;
END;
$total$ LANGUAGE plpgsql;

and i want to run it from VBA using odbc connection. 

What is the best way to use it ?

something like this: 

Dim dbCon as new ADODB.Connection
Dim rst as new ADODB.Recordset

Dbcon.connectionstring=”Your connection string goes here!”
Dbcon.open

Rst.open strsql
where strsql is "Select * from totalRecords" or this is not a good solution?

Best,
Jacek

You need the parentheses after the function name: “select * from totalrecords();"



Depends on the usage pattern.  I'm sure there is an ODBC construct for stored procedures/function, which you could build once and re-use with new parameter values if you're going to call this repeatedly.

Re: Run Stored procedure - function from VBA

From
Asif Ali
Date:
how the fuck i unsubscribe to this mailing list , i get more than 100 emails a day

Bye

From: Rob Sargent <robjsargent@gmail.com>
Sent: Wednesday, June 20, 2018 12:54 AM
To: Łukasz Jarych
Cc: pgsql-general@postgresql.org
Subject: Re: Run Stored procedure - function from VBA
 



On 06/18/2018 09:51 PM, Łukasz Jarych wrote:
Thank you Rob,

question is it is the optimal way to run SP from VBA? 
Or not?

Best,
Jacek

2018-06-19 1:34 GMT+02:00 Rob Sargent <robjsargent@gmail.com>:


On Jun 18, 2018, at 9:47 AM, Łukasz Jarych <jaryszek@gmail.com> wrote:

Hi Guys,

i have example function :

CREATE OR REPLACE FUNCTION totalRecords ()
RETURNS integer AS $total$
declare
total integer;
BEGIN
   SELECT count(*) into total FROM COMPANY;
   RETURN total;
END;
$total$ LANGUAGE plpgsql;

and i want to run it from VBA using odbc connection. 

What is the best way to use it ?

something like this: 

Dim dbCon as new ADODB.Connection
Dim rst as new ADODB.Recordset

Dbcon.connectionstring=”Your connection string goes here!”
Dbcon.open

Rst.open strsql
where strsql is "Select * from totalRecords" or this is not a good solution?

Best,
Jacek

You need the parentheses after the function name: “select * from totalrecords();"



Depends on the usage pattern.  I'm sure there is an ODBC construct for stored procedures/function, which you could build once and re-use with new parameter values if you're going to call this repeatedly.

Re: Run Stored procedure - function from VBA

From
Łukasz Jarych
Date:
Thank you Rob,

exactly. Do you know this odbc constructtion?

Best,
Jacek 

2018-06-20 0:08 GMT+02:00 Asif Ali <asif2k@hotmail.com>:
how the fuck i unsubscribe to this mailing list , i get more than 100 emails a day

Bye

From: Rob Sargent <robjsargent@gmail.com>
Sent: Wednesday, June 20, 2018 12:54 AM
To: Łukasz Jarych
Cc: pgsql-general@postgresql.org
Subject: Re: Run Stored procedure - function from VBA
 



On 06/18/2018 09:51 PM, Łukasz Jarych wrote:
Thank you Rob,

question is it is the optimal way to run SP from VBA? 
Or not?

Best,
Jacek

2018-06-19 1:34 GMT+02:00 Rob Sargent <robjsargent@gmail.com>:


On Jun 18, 2018, at 9:47 AM, Łukasz Jarych <jaryszek@gmail.com> wrote:

Hi Guys,

i have example function :

CREATE OR REPLACE FUNCTION totalRecords ()
RETURNS integer AS $total$
declare
total integer;
BEGIN
   SELECT count(*) into total FROM COMPANY;
   RETURN total;
END;
$total$ LANGUAGE plpgsql;

and i want to run it from VBA using odbc connection. 

What is the best way to use it ?

something like this: 

Dim dbCon as new ADODB.Connection
Dim rst as new ADODB.Recordset

Dbcon.connectionstring=”Your connection string goes here!”
Dbcon.open

Rst.open strsql
where strsql is "Select * from totalRecords" or this is not a good solution?

Best,
Jacek

You need the parentheses after the function name: “select * from totalrecords();"



Depends on the usage pattern.  I'm sure there is an ODBC construct for stored procedures/function, which you could build once and re-use with new parameter values if you're going to call this repeatedly.

Re: Run Stored procedure - function from VBA

From
Rob Sargent
Date:
Sorry. I don’t use ODBC directly. If it’s not obvious in the manual, google ‘ODBC functions’

On Jun 19, 2018, at 10:39 PM, Łukasz Jarych <jaryszek@gmail.com> wrote:

Thank you Rob,

exactly. Do you know this odbc constructtion?

Best,
Jacek 

2018-06-20 0:08 GMT+02:00 Asif Ali <asif2k@hotmail.com>:
how the fuck i unsubscribe to this mailing list , i get more than 100 emails a day

Bye

From: Rob Sargent <robjsargent@gmail.com>
Sent: Wednesday, June 20, 2018 12:54 AM
To: Łukasz Jarych
Cc: pgsql-general@postgresql.org
Subject: Re: Run Stored procedure - function from VBA
 



On 06/18/2018 09:51 PM, Łukasz Jarych wrote:
Thank you Rob,

question is it is the optimal way to run SP from VBA? 
Or not?

Best,
Jacek

2018-06-19 1:34 GMT+02:00 Rob Sargent <robjsargent@gmail.com>:


On Jun 18, 2018, at 9:47 AM, Łukasz Jarych <jaryszek@gmail.com> wrote:

Hi Guys,

i have example function :

CREATE OR REPLACE FUNCTION totalRecords ()
RETURNS integer AS $total$
declare
total integer;
BEGIN
   SELECT count(*) into total FROM COMPANY;
   RETURN total;
END;
$total$ LANGUAGE plpgsql;

and i want to run it from VBA using odbc connection. 

What is the best way to use it ?

something like this: 

Dim dbCon as new ADODB.Connection
Dim rst as new ADODB.Recordset

Dbcon.connectionstring=”Your connection string goes here!”
Dbcon.open

Rst.open strsql
where strsql is "Select * from totalRecords" or this is not a good solution?

Best,
Jacek

You need the parentheses after the function name: “select * from totalrecords();"



Depends on the usage pattern.  I'm sure there is an ODBC construct for stored procedures/function, which you could build once and re-use with new parameter values if you're going to call this repeatedly.

Re: Run Stored procedure - function from VBA

From
Łukasz Jarych
Date:
Thank you Rob,

I googled it and there a loit about ODBC but not a lot about ODBC and postgresql.

Best,
Jacek

2018-06-20 7:41 GMT+02:00 Rob Sargent <robjsargent@gmail.com>:
Sorry. I don’t use ODBC directly. If it’s not obvious in the manual, google ‘ODBC functions’

On Jun 19, 2018, at 10:39 PM, Łukasz Jarych <jaryszek@gmail.com> wrote:

Thank you Rob,

exactly. Do you know this odbc constructtion?

Best,
Jacek 

2018-06-20 0:08 GMT+02:00 Asif Ali <asif2k@hotmail.com>:
how the fuck i unsubscribe to this mailing list , i get more than 100 emails a day

Bye

From: Rob Sargent <robjsargent@gmail.com>
Sent: Wednesday, June 20, 2018 12:54 AM
To: Łukasz Jarych
Cc: pgsql-general@postgresql.org
Subject: Re: Run Stored procedure - function from VBA
 



On 06/18/2018 09:51 PM, Łukasz Jarych wrote:
Thank you Rob,

question is it is the optimal way to run SP from VBA? 
Or not?

Best,
Jacek

2018-06-19 1:34 GMT+02:00 Rob Sargent <robjsargent@gmail.com>:


On Jun 18, 2018, at 9:47 AM, Łukasz Jarych <jaryszek@gmail.com> wrote:

Hi Guys,

i have example function :

CREATE OR REPLACE FUNCTION totalRecords ()
RETURNS integer AS $total$
declare
total integer;
BEGIN
   SELECT count(*) into total FROM COMPANY;
   RETURN total;
END;
$total$ LANGUAGE plpgsql;

and i want to run it from VBA using odbc connection. 

What is the best way to use it ?

something like this: 

Dim dbCon as new ADODB.Connection
Dim rst as new ADODB.Recordset

Dbcon.connectionstring=”Your connection string goes here!”
Dbcon.open

Rst.open strsql
where strsql is "Select * from totalRecords" or this is not a good solution?

Best,
Jacek

You need the parentheses after the function name: “select * from totalrecords();"



Depends on the usage pattern.  I'm sure there is an ODBC construct for stored procedures/function, which you could build once and re-use with new parameter values if you're going to call this repeatedly.