Skip to Content
1
Former Member
Apr 24, 2020 at 06:34 AM

Challenge for the day: Fixing the procedure

133 Views

Hello Friends,

I just tried to take up a challenge for the day to mimic small login functionality in HANA.

I stumbled upon time constraints and further issues or bugs (syntactically & you can improve the logic if necessary )

PROCEDURE "LoginValidate"(in IM_GID nvarchar(6),in IM_PASSWD nvarchar(96), out EX_StatusIndicator nvarchar(3),out Ex_Status nvarchar(216) )
   LANGUAGE SQLSCRIPT
   SQL SECURITY INVOKER
   --DEFAULT SCHEMA <default_schema_name>
  -- READS SQL DATA
  AS
BEGIN
   /*************************************
       Write your procedure logic 
   *************************************/
   declare LV_UserExistCount string;
   declare LV_UserExistPass string;
   declare LV_Attempt Integer;
   select DISTINCT(USERID) into LV_UserExistCount  from "Datasource.MD_USER_DETAIL" where USERID = :IM_GID ;
   if(Length(:LV_UserExistCount)) = 0
   then 
	EX_StatusIndicator =  'X1' ;
	Ex_Status = 'You are not registered, please register';
	else 
		if(Length(:LV_UserExistCount)) = 1
        then 
        select  DISTINCT("Password") into LV_UserExistPass  from "Datasource.MD_USER_DETAIL" where "Password" = :IM_PASSWD;
        	if(Length(:LV_UserExistPass)) = 0
        		then 
        			EX_StatusIndicator =  'X2' ;
	                Ex_Status = 'You are not Authorized, Incorrect password';  
	                select "NoOfAttempt" into LV_Attempt from    "Datasource.DailyLoginControl" ;
	                if(LV_Attempt is null) then
	                LV_Attempt = 0;
	                END IF;
	                if (to_Integer(LV_Attempt) >= 3 ) then
	                	EX_StatusIndicator =  'X3' ;
	                    Ex_Status = 'Breach ! Your account has been locked';
	                 else if (to_Integer(LV_Attempt) <= 3 ) then
	                 select "NoOfAttempt" into LV_Attempt from    "Datasource.DailyLoginControl" ;
	                 EX_StatusIndicator =  'X4' ;
	                 Ex_Status = 'un Authorized ! Your account has been entered with incorrect password'+LV_Attempt+'times';
	                 LV_Attempt = LV_Attempt + 1;
	                 update "Datasource.DailyLoginControl" set "GID" = IM_GID, "Status" = 'Failed',"LoggedInat"=current_date,NoOfAttempt = LV_Attempt, ResetRequestDate='00000000' where "GID" = IM_GID and "LoggedInat" = current_date;  
	                 commit;
	                
	                 else
	                 EX_StatusIndicator =  '200' ;
	                 Ex_Status = ' Authorized ! Login success';
	                 
	                 update "Datasource.DailyLoginControl" set "GID" = IM_GID, "Status" = 'success',"LoggedInat"=current_date,NoOfAttempt = 0, ResetRequestDate='00000000' where "GID" = IM_GID and "LoggedInat" = current_date;  
	                 commit;
	                
	                end if;
            end if;
        end if;
   end if;                           
   
END


Error as follows

11:56:23 AM (HDB)         at "src/Procedure/Authorization/LoginValidate.hdbprocedure" (55:1)    Error: Precompiling... failed [8212133]   Error: Calculating dependencies... failed [8212108]   Make failed (5 errors, 0 warnings): tried to deploy 1 files, undeploy 0 files, redeploy 0 dependent files   Error: Making... failed [8211605]

Further Reference to recreate the scenario.

context Datasource {

entity "MD_USER_DETAIL"
{
 key "USERID":String(12);
};


entity DailyLoginControl
{
Key "GID" : String(12);
Status:String(13);
LoggedInat:UTCTimestamp;
NoOfAttempt:Integer;
ResetRequestDate:UTCTimestamp; 
	
};

}


Lets solve this and help our community to try this out as small #Learnings.

I would also post the results of fix and working code in the same page and also add it to GITHUB.

Br,

Asgariel.