| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> comp.databases.oracle -> Re: VB Oracle stored procedure problem
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_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
>
> 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
 |
 |