Get pgsql variable into VB parameter function

Question

I have this code in VB where I open my connection to the database and connect to my pgsql function..

Set oCmd = New ADODB.Command    
oCmd.ActiveConnection = GetConnectionString
oCmd.CommandType = adCmdStoredProc
oCmd.CommandTimeout = 36000
oCmd.Parameters.Append oCmd.CreateParameter("lngUserId", adInteger, adParamOutput)
oCmd.CommandText = "P_Login"
oCmd.Execute    
g_USRid = oCmd.Parameters("lngUserId")

In my pgsql function I have this:

CREATE OR REPLACE FUNCTION p_login(
OUT lnguserid bigint) RETURNS record AS
$BODY$

DECLARE
BEGIN

--Select USR_Id into lngUserID From tbl_User Where USR_Login = strLogin;
lngUserID := 369;

END;

$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
COST 100;

The problem is that my variable g_USRid doesn't get the value 369. How can I do that?

That can't be VBScript or you'd be getting `Microsoft VBScript runtime error (1, 1) : Class not defined: 'ADODB'` on the line `Set oCmd = New ADODB.Command`. You sure this isn't VBA which supports [early-binding](http://stackoverflow.com/a/10581/692942)? **Edit:** Just realised the tag [has been removed](http://stackoverflow.com/revisions/42089493/2), will add it back in. — user692942, Feb 07 '17 at 13:34

Roman Tkachuk · Answer 1 · 2017-02-08T14:03:26.827

0

You can not use only one OUT and RETURNS record together.

May be:

CREATE OR REPLACE FUNCTION p_login(OUT lnguserid bigint)

or

CREATE OR REPLACE FUNCTION p_login() RETURNS bigint

Repaierd version your function (from comment)

CREATE OR REPLACE FUNCTION p_login(
       IN strlogin varchar,
       IN strpassword varchar,
      OUT "lngUserId" bigint,
      OUT stroraclelogin varchar,
      OUT stroraclepassword varchar,
      OUT lngprofile bigint,
      OUT interror bigint)
RETURNS record AS $BODY$ Declare 
begin --Select USR_Id into lngUserID From tbl_User Where USR_Login = strLogin;
lngUserID := 369;
end; $BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER COST 100; 

SELECT * FROM p_login('1','1');

 lnguserid | stroraclelogin | stroraclepassword | lngprofile | interror 
-----------+----------------+-------------------+------------+----------
       369 |                |                   |            |         
(1 row)

Errors:

1) postgr4esql does not have varchar2, just varchar

2) twice RETURNS record RETURNS record

To access to result use:

Set rst = cmd.Execute MsgBox rst.Fields(1).Value

edited Feb 08 '17 at 14:03

answered Feb 07 '17 at 12:48

Roman Tkachuk

3,096
1
16
15

i just remove others parameters into the function but the has much more parameters into it but didn't wanted to write them all – Kamfasage Feb 08 '17 at 08:36
CREATE OR REPLACE FUNCTION p_login( IN strlogin varchar2, IN strpassword varchar2, OUT lnguserid bigint, OUT stroraclelogin varchar2, OUT stroraclepassword varchar2, OUT lngprofile bigint, OUT interror bigint) RETURNS record AS RETURNS record AS $BODY$ Declare begin --Select USR_Id into lngUserID From tbl_User Where USR_Login = strLogin; lngUserID := 369; end; $BODY$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER COST 100; – Kamfasage Feb 08 '17 at 08:36
i want just to ask another question without opening a new discussion... now i wealing to do so _Select USR_Id into lngUserId From tbl_User Where USR_Login = strLogin;_ but it doesn't work.. how can i do that?? – Kamfasage Feb 08 '17 at 14:07
looks like normal sql – Roman Tkachuk Feb 08 '17 at 14:09
can you run in psql/pgadmin: `Select USR_Id into lngUserId From tbl_User Where USR_Login = 'some login'` where some login is real user login? – Roman Tkachuk Feb 08 '17 at 14:10
thanks for your help i found out the problem.. this was due to a commit command i put into my function – Kamfasage Feb 08 '17 at 16:28

score 0 · Answer 2 · answered Feb 07 '17 at 12:57

You have a few issues with your function. Most importantly, you should not return a record but simply the value you are interested in. Furthermore, you can use a much more efficient sql language function. Lastly, the function should not be VOLATILE (it does not change anything in the database, nor does it depend on changing factors like the current time) but STRICT (parameters need to be specified to invoke the function). Try this:

CREATE FUNCTION p_login(strLogin text) RETURNS bigint AS $BODY$
   SELECT USR_Id FROM tbl_User WHERE USR_Login = strLogin;
$BODY$ LANGUAGE sql STRICT;

Note that you have to set the parameter strLogin in VB prior to calling the function.

Thanks for your answer. the 'select' statement is in comment..i want just to figure out how to get the 'lngUserId' value into 'g_userID' in my VB code — Kamfasage, Feb 08 '17 at 08:43

score 0 · Answer 3 · answered Feb 08 '17 at 08:40

0

this is how the function is constructed:

CREATE OR REPLACE FUNCTION p_login(
    IN strlogin varchar2,
    IN strpassword varchar2,
    OUT lnguserid bigint,
    OUT stroraclelogin varchar2,
    OUT stroraclepassword varchar2,
    OUT lngprofile bigint,
    OUT interror bigint)
  RETURNS record AS
$BODY$

Declare

begin

    --Select USR_Id into lngUserID From tbl_User Where USR_Login = strLogin;
lngUserID := 369;

end;

$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100;

answered Feb 08 '17 at 08:40

Kamfasage

181
1
7
14

ok corrected, but still having the same problem... cannot get the 'lngUserId' value into VB – Kamfasage Feb 08 '17 at 10:33
If you want case sensitivity name of object in PostgreSQL you must use ": "lngUserId", or call it as lnguserid from your progaram: oCmd.Parameters("lnguserid") – Roman Tkachuk Feb 08 '17 at 10:38
i can't really get what you are saying..please can you give me an example?? – Kamfasage Feb 08 '17 at 13:12
try `oCmd.Parameters("lnguserid")` instead `oCmd.Parameters("lngUserId")` or change argument name in function from `lngUserId` to `"lngUserId"` – Roman Tkachuk Feb 08 '17 at 13:16
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/135181/discussion-between-roman-tkachuk-and-kamfasage). – Roman Tkachuk Feb 08 '17 at 13:44

Get pgsql variable into VB parameter function

3 Answers3