|
Q.
I've moved on from the Standard and Custom ODBC Select statement and I
am in the process of setting up with the
"Advanced"
ODBC string. Tell me more about this.
When you use the "Use string to validate (empty rowset indicates failure)" option a simple macro substitution is made at run time, replacing values such as $USERNAME$ with their runtime values.. Then the statement is executed using the ODBC SQLExecDirect call. You need to make sure the statement you use should make sense to the ODBC driver and database you are using. If the call results in an empty rowset access is denied, otherwise access is granted, and the username and password combination are stored in the AuthentiX ODBC username/password cache. The other two Advanced Options ("Use Standard Select to validate, execute ODBC string on success." and "Use Custom Select to validate, execute ODBC string on success.") only calls the Advanced ODBC string if they succeed. This can be useful if you want to log successful logins for example. In this case the $VERIFY$ macro subsitution indicates whether this is an initial login, or a verification against the database, in accordance with the operation of the ODBC cache. Here is an example string: EXEC sp_Login '$USERNAME$', '$PASSWORD$', '$IPADDRESS$', '$USERAGENT$', '$VERIFY$'And another, used in the SQL sample below EXEC sp_Login '$USERNAME$', '$PASSWORD$', 'c:\inetpub\wwwroot\members" Here is an example SQL Stored procedure:
CREATE PROCEDURE VerifyUser
@UserName VarChar(50), /* THIS IS THE USERNAME PARAMETER */
@Password VarChar(15), /* THIS IS THE PASSWORD PARAMETER */
@DirName VarChar(50) /* THIS IS THE DIRECTORY NAME PARAMETER */
AS
/* THIS SELECT RETURNS A NON-EMPTY RESULTSET IF */
/* THE USER IS A MEMBER OF A GROUP THAT HAS ACCESS TO THE */
/* REQUESTED DIRECTORY AND IF THE USER HAS A VALID PASSWORD */
SELECT @UserName, @Password, @DirName FROM
WebUsers w, UserRelations u, GroupRelations g, GroupDirs d
WHERE w.UserName=@UserName
AND w.Password = @Password
AND w.UserID = u.UserID
AND u.GroupID = g.GroupID
AND g.DirID = d.DirID
AND d.DirName =@DirName
Also,
"Alexandre Volpim" (volpim@camerasurf.com.br)
shows us how to create a stored procedure with multiple selects.
set nocount on
declare @loginCheck varchar(100)
select @loginCheck=login from clients where login=@login and
password=@password
if (@loginCheck<>'') then
begin
insert into log (login,date) values (@loginCheck,getdate())
end
select * from clientes where login=@loginCheck
The result of this stored-procedure will be the result of the last Select
because all other statements (select and insert) don't return data.
This SP is not usefull, but my ideia is to tranform the IP of the form
xxx.xxx.xxx.xxx to a int before the select statement. The code to transform
the IP didn't return data, but the SP doesn't works.
Actually I call another SP (valIP) in the authentication SP:
CREATE PROCEDURE valIP
@ip char(15),
@resultado numeric(15) output
AS
DECLARE
@octeto int,
@pos int,
@posant int,
@contador int,
@valor numeric(15)
select @posant=1
select @valor=0
select @contador=0
select @pos=CHARINDEX('.',@ip)
while (@pos<>0)
begin
select @octeto=SUBSTRING(@ip,@posant,@pos-@posant)
select @valor=@octeto+@valor*256
select @contador=@contador+1
select @posant=@pos+1
select @pos=CHARINDEX('.',@ip,@posant)
end
select @octeto=SUBSTRING(@ip,@posant,Len(@ip)-@posant+1)
select @valor=@octeto+@valor*256
select @resultado=@valor
|
|