Re: VB Oracle stored procedure problem

From: Jim Kennedy <kennedy-downwithspammersfamily_at_attbi.net>
Date: Mon, 16 Feb 2004 15:58:09 GMT
Message-ID: <lM5Yb.44889$uV3.88349_at_attbi_s51>


"Jagdip Singh Ajimal" <jsa1981_at_hotmail.com> wrote in message news:c84eb1b0.0402160740.4fec6175_at_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
I usually use oo4o and have no problems and it is easy to use. (also better performance) Try not having it return a Boolean. Have it return 'T' or 'F' or 1 and 0. That will probably work.
Jim Received on Mon Feb 16 2004 - 16:58:09 CET

Original text of this message