First, sorry for the length of this. Second, thanks for your time. And off we go:
I checked out the thread (thanks) and I am not sure that it helps. I am pretty well versed in using SQLEXEC with SQL Server so the syntax I can understand. I will try passing the parameters without the question marks and see if that makes a difference for some reason. It is ironic that that thread concerns password validation as well <g>. Happens to be exactly what I am trying to do. I'll place my SQLEXEC call below and then the stored procedure that it is calling.
Call from VFPresultCode = SQLExec(oUserInfo.ConnHandle, ;
"{CALL pchecklogin (?lcLogin,?lcPassword, ?@mUserName, ?@mSource, ?@mAccessLevel, ?@mretval)}")
As you can see I am trying to have the Username, Source, AccessLevel, and Return value returned from this procedure. The returnval simply sends back a 0, -1, or -1 showing password matched/user found, password did not match, login not found respectively.
The problem is that on what seems like a random basis the mSource, which is a varchar2(2) field, returns the proper 2 character source along with what seems like garbage characters. The mAccessLevel will often do the same thing (it is a varchar2(1) field that contains a number in case that makes any difference. I've put the procedure code below in case anyone can see a glaring mistake that I might have made. This is my first Oracle SP so that might be the case.
Stored Procedurecreate or replace procedure pCheckLogin
(LoginID IN VARCHAR2,
ePassWord IN VARCHAR2,
InUserName IN OUT VARCHAR2,
InSource IN OUT VARCHAR2,
InAccessLevel IN OUT VARCHAR2,
RetVal IN OUT NUMBER)
as
TablePword UserControl.Password%TYPE;
RecordCount NUMBER;
CurrentDate DATE;
NumTimesIn NUMBER;
BEGIN
SELECT COUNT(*) INTO RecordCount
FROM UserControl
WHERE UserID = LoginID;
IF RecordCount > 0 THEN
SELECT Password INTO TablePword
FROM UserControl
WHERE UserID = LoginID;
IF TablePword=ePassWord THEN
/* password matches */
RetVal := 0;
SELECT RTRIM(username), RTRIM(source), RTRIM(accesslevel)
INTO InUserName, InSource, InAccessLevel
FROM UserControl
WHERE UserID = LoginID;
SELECT SYSDATE INTO CurrentDate FROM DUAL;
SELECT timesin INTO NumTimesIn
FROM usercontrol
WHERE UserID = LoginID;
IF NumTimesIn = 0 THEN
UPDATE UserControl
SET FirstIn = CurrentDate
WHERE UserID = LoginID;
END IF;
NumTimesIn := NumTimesIn + 1;
UPDATE UserControl
SET loggedin=1, lastin=CurrentDate, TimesIn=NumTimesIn
WHERE UserID = LoginID;
COMMIT;
ELSE
/* password does not match */
RetVal := -1;
END IF;
ELSE
/* No password for this ID */
RetVal := -2;
END IF;
END;
>Or Tim. :)
>
>I would first direct you to
This Thread for function call syntax.
>
>Second, I would say that would not necessarily be the case. In an Oracle SP, you can manipulate any parameter passed within the SP or function. You can also declare other variables along with their data type and length. You can also set their values to whatever you want within the SP then return that new value back. AFAIK, you can either get back 1 return value or a cursor of results.
>
>Disclaimer: I am not at work right now, so I can not test any of this. If I get a chance, I will try this when I get back later this week or first of next week.
>
>>It looks as if the parameters that are passed in to an Oracle stored procedure must be of the exact size that the data will be when passed back out from Oracle. I may be wrong but I am having some odd problems and think that this is related. If say an empty string variable is passed into an Oracle SP and this will also be an Output parameter, then the data passed back will be truncated. Does anyone know if this is the case? (and I am betting that "anyone" will be Mark <g>).
>>
>>As always,
>>Thanks!
>>George
Everything we see or seems
Is but a dream within a dream - Edgar Allen Poe