VB Oracle stored procedure problem

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Jagdip Singh Ajimal
    #1

    VB Oracle stored procedure problem

    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(chec kUsername CHAR,
    checkPassword CHAR) RETURN BOOLEAN IS
    CURSOR filterUsername IS
    SELECT * FROM AllEmployees
    WHERE allemployees.us ername = checkUsername;
    BEGIN
    FOR eachFilterUsern ame IN filterUsername LOOP
    IF (eachFilterUser name.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(‘Doc 1', 'Doc1Pass') THEN
    DBMS_OUTPUT.PUT _LINE(‘Login ok');
    ELSE
    DBMS_OUTPUT.PUT _LINE(‘Unauthor ised 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.Ora cleConn
    DBconn.Open
    Dim checkLoginCmd As New ADODB.Command
    Dim returnBoolean As Boolean
    returnBoolean = dataEnviron.AJI MALJ0_CHECKLOGI N(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
    Tags: None
    • Jim Kennedy
      #2
      Re: VB Oracle stored procedure problem


      "Jagdip Singh Ajimal" <jsa1981@hotmai l.comwrote in message
      news:c84eb1b0.0 402160740.4fec6 175@posting.goo gle.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(chec kUsername CHAR,
      checkPassword CHAR) RETURN BOOLEAN IS
      CURSOR filterUsername IS
      SELECT * FROM AllEmployees
      WHERE allemployees.us ername = checkUsername;
      BEGIN
      FOR eachFilterUsern ame IN filterUsername LOOP
      IF (eachFilterUser name.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('Doc 1', 'Doc1Pass') THEN
      DBMS_OUTPUT.PUT _LINE('Login ok');
      ELSE
      DBMS_OUTPUT.PUT _LINE('Unauthor ised 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.Ora cleConn
      DBconn.Open
      Dim checkLoginCmd As New ADODB.Command
      Dim returnBoolean As Boolean
      returnBoolean = dataEnviron.AJI MALJ0_CHECKLOGI N(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


        Comment

        • Dave
          #3
          Re: VB Oracle stored procedure problem

          jsa1981@hotmail .com (Jagdip Singh Ajimal) wrote in message news:<c84eb1b0. 0402160740.4fec 6175@posting.go ogle.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(chec kUsername CHAR,
          checkPassword CHAR) RETURN BOOLEAN IS
          CURSOR filterUsername IS
          SELECT * FROM AllEmployees
          WHERE allemployees.us ername = checkUsername;
          BEGIN
          FOR eachFilterUsern ame IN filterUsername LOOP
          IF (eachFilterUser name.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(?Doc 1', 'Doc1Pass') THEN
          DBMS_OUTPUT.PUT _LINE(?Login ok');
          ELSE
          DBMS_OUTPUT.PUT _LINE(?Unauthor ised 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.Ora cleConn
          DBconn.Open
          Dim checkLoginCmd As New ADODB.Command
          Dim returnBoolean As Boolean
          returnBoolean = dataEnviron.AJI MALJ0_CHECKLOGI N(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....cou ld probably be
          improved further...

          CREATE OR REPLACE FUNCTION checkLogin(p_ch eckUsername 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;
          /

            Comment

            • Jagdip Singh Ajimal
              #4
              Re: VB Oracle stored procedure problem

              davidr21@hotmai l.com (Dave) wrote in message news:<8244b794. 0402161154.1691 142d@posting.go ogle.com>...
              jsa1981@hotmail .com (Jagdip Singh Ajimal) wrote in message news:<c84eb1b0. 0402160740.4fec 6175@posting.go ogle.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(chec kUsername CHAR,
              checkPassword CHAR) RETURN BOOLEAN IS
              CURSOR filterUsername IS
              SELECT * FROM AllEmployees
              WHERE allemployees.us ername = checkUsername;
              BEGIN
              FOR eachFilterUsern ame IN filterUsername LOOP
              IF (eachFilterUser name.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(?Doc 1', 'Doc1Pass') THEN
              DBMS_OUTPUT.PUT _LINE(?Login ok');
              ELSE
              DBMS_OUTPUT.PUT _LINE(?Unauthor ised 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.Ora cleConn
              DBconn.Open
              Dim checkLoginCmd As New ADODB.Command
              Dim returnBoolean As Boolean
              returnBoolean = dataEnviron.AJI MALJ0_CHECKLOGI N(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....cou ld probably be
              improved further...
              >
              CREATE OR REPLACE FUNCTION checkLogin(p_ch eckUsername 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.

                Comment

                Previous template Next
                Working...