Re: VB Oracle stored procedure problem
Date: 17 Feb 2004 06:01:43 -0800
Message-ID: <c84eb1b0.0402170601.4ec4f718_at_posting.google.com>
davidr21_at_hotmail.com (Dave) wrote in message news:<8244b794.0402161154.1691142d_at_posting.google.com>...
> jsa1981_at_hotmail.com (Jagdip Singh Ajimal) wrote in message news:<c84eb1b0.0402160740.4fec6175@posting.google.com>...
> > I have a new VB 6 project, and I have successfully created a data
> > environment that connects to my oracle server (they don't make this
> > easy!!).
> > In my oracle server, I have the following stored procedure:
> >
> > CREATE OR REPLACE FUNCTION checkLogin(checkUsername CHAR,
> > checkPassword CHAR) RETURN BOOLEAN IS
> > CURSOR filterUsername IS
> > SELECT * FROM AllEmployees
> > WHERE allemployees.username = checkUsername;
> > BEGIN
> > FOR eachFilterUsername IN filterUsername LOOP
> > IF (eachFilterUsername.password = checkPassword) THEN
> > RETURN TRUE;
> > EXIT;
> > END IF;
> > END LOOP;
> > RETURN FALSE;
> > END;
> > /
> >
> > I know this works as, when I run
> > SET SERVEROUTPUT ON
> > BEGIN
> > IF checkLogin(?Doc1', 'Doc1Pass') THEN
> > DBMS_OUTPUT.PUT_LINE(?Login ok');
> > ELSE
> > DBMS_OUTPUT.PUT_LINE(?Unauthorised login');
> > END IF;
> > END;
> > /
> > I get the right output.
> >
> > MY PROBLEM. I have added the data environment to my project, and also
> > added this stored procedure. When I try to run the following code,
> >
> > Set dataEnviron = New dataEnv
> > Set DBconn = dataEnviron.OracleConn
> > DBconn.Open
> > Dim checkLoginCmd As New ADODB.Command
> > Dim returnBoolean As Boolean
> > returnBoolean = dataEnviron.AJIMALJ0_CHECKLOGIN(username, password)
> > checkLogin = returnBoolean
> >
> > I get the following error
> >
> > Run-time error '-2147467259 (80004005)':
> > [Oracle][ODBC][Ora]ORA-06550: line 1, cloumn 13:
> > PLS-00382: expression is of wrong type
> > ORA-06550: line 1, column 7:
> > PL/SQL: Statement ignores
> >
> > I am almost 100% sure this is because of the way vb passing the
> > variables to oracle, but I am unsure how to fix it. I have checked the
> > parameters in teh dataenvironment and they all seem right.
> >
> > Any help good be greatly appriciated
> >
> > Jagdip Singh Ajimal
>
> You never mention the datatypes you specify for username and password
> in your VB code, perhaps that is the issue.
>
> A simpler implementation of your function....could probably be
> improved further...
>
> CREATE OR REPLACE FUNCTION checkLogin(p_checkUsername in VARCHAR2,
> p_checkPassword in VARCHAR2) RETURN BOOLEAN
> IS
> L_CNT NUMBER;
> BEGIN
>
> SELECT COUNT(*) into L_CNT
> FROM AllEmployees
> WHERE username = p_checkUsername
> AND password = p_checkPassword;
>
> IF L_CNT > 0 THEN
> RETURN TRUE;
> ELSE
> RETURN FALSE;
> END IF;
> END;
> /
Thank you both for your help. I changed the return value in the oracle PL/SQL from TRUE FALSE to 'T' and 'F'. Now I get T and F returned correctly, so I can atleast continuing programming.
Jagdip Singh Ajimal
In this cruel world, isn't it satisfying that at least us Computer Scientists keep are humanity and try to help each other out. Received on Tue Feb 17 2004 - 15:01:43 CET