I have a users table containing logins, passwords and some other user data.
When a new user is added to the “t_user” table, the login matches his active directory login.
Therefore, a call to the following function retrieves his user ID from anywhere in the database.
CREATE FUNCTION [dbo].[fn_user_id]()
RETURNS int
AS
BEGIN
declare @id_user int
declare @user varchar(255)
select @user = system_user
if charindex('\', @user) = 0
select @user = current_user
else
select @user = right(@user, len(@user) - charindex('\', @user))
select @id_user = u.id_user
from t_user u with (nolock)
where u.login = @user
END
This works perfectly when used within the database or a Delphi application using the FireDAC components.
However, it looks like calling this function directly or within a stored procedure from a schema command fails without any error message and the user ID returned is always null.
I am using the latest DA/RO builds under Delphi XE5.
Am I doing something wrong or is this a known problem?
Is there any workaround to make this work (using other components/drivers for instance)?
Any help would be much appreciated.
Thanks in advance for your support.
This works perfectly when used within the database or a Delphi application using the FireDAC components.
However, it looks like calling this function directly or within a stored procedure from a schema
command fails without any error message and the user ID returned is always null.
How do you call this function in DA now? Can you send us your schema?
You have a function and you can’t call it as a stored procedure in DA.
To get data from this function you can:
Transform your function to stored procedure with out parameter
Create a new datatable with sql like: select dbo.fn_user_id() userid
The following function is defined in my database. It returns me the value of an application setting for a given setting code.
CREATE FUNCTION [dbo].[fn_setting]
(
@code_setting varchar(50)
)
RETURNS nvarchar(max)
AS
BEGIN
declare @value nvarchar(max)
select @value = s.value
from t_setting s with (nolock)
where
s.code_setting = @code_setting
and s.active = 1
return @value
END
I already told you about the other function that returns me the ID of the currently logged user (see my first post above).
In my schema, I have defined two commands, both with connection type MSSQL (based on the same ADO connection to my database backend) and with statement type “SQL”.
Calling the following statement in my first command (named “GetSetting”) works perfectly!
select :value = dbo.fn_setting(:code_setting)
Calling the following statement in my second command (named “GetUserID”) always gets me a null value.
select :id_user = dbo.fn_user_id()
I have already tried to execute the function within a command with statement type “StoredProcedure” as you suggested but this did not solve the problem : the value of id_user is always null.
Converting to a table did not solve the problem either and using a stored procedure is not an option since, as stated before, the stored procedure also returns null…
Any suggestion would be much appreciated.
Kind regards,