Help with MS SQL INSERT Stored Procedure

[jwilliamson]

Hey All,

I have been wracking my head with this one. I would like to use a SP for security reasons but I can't get this thing to run! The full SQL statement below works but the SP won't execute without error. I get a .F. on the flag but 'Success' in my call result text.

	Dim conn  as SQL::Connection
	Dim argz as SQL::Arguments
	Dim SQL as c 
	Dim SQLStatement as c
	dim cLoginType as c = ""
	dim cResultText as c
	
	'flag to test if connection opens
	dim flag as l
	SQLStatement = <<%sql%
	INSERT INTO WebUsersLogins (Username, IPaddress, LoginError, LoginType) OUTPUT INSERTED.Id VALUES('test3','1001001003','gridinitialize3','in')
	%sql%
	SQL = <<%sql%
	EXEC jwspWebUsersLoginsInsert(:username,:ip,:loginerror,:logintype)
	%sql%
	
	argz.set("username","testuser")
	argz.set("ip","101010101")
	argz.set("loginerror","OnGrid")	
	argz.set("logintype","in")	
	
	conn.Open("::name::RaTSTest")
	cResultText = conn.CallResult.Text							
	'flag = conn.execute(SQLStatement)
	flag = conn.execute(SQL,argz)
	debug(1)

Use RaTSTest
GO
ALTER PROCEDURE jwspWebUsersLoginsInsert 
@username nvarchar(250), @ip nvarchar(50), @error nvarchar(250), @logintype nvarchar(3) 
/*This procedure adds a record everytime a user logs in to the Reportal and is called by the Web User Security function 'saveLoginLog' */
AS
INSERT INTO WebUsersLogins (Username, IPaddress, LoginError, LoginType) 
--OUTPUT INSERTED.Id
VALUES(@username, @ip, @error, @logintype);

[glenschild]

Re: Help with MS SQL INSERT Stored Procedure

EXEC jwspWebUsersLoginsInsert(:username,:ip,:loginerror,:logintype)

Have you tried replacing the above with

EXEC jwspWebUsersLoginsInsert @username = :username,@ip = :ip, @error = :loginerror, @logintype = :logintype

[kenshap]

Re: Help with MS SQL INSERT Stored Procedure

Hi Joseph,

It doesn't look like you executed the procedure. This is clunky, but it works for me. :)

dim cn as sql::connection
cn.open("::Name::xxx")

dim flag as l
flag = cn.Execute("exec fnow_edit_attendance @pactionid=:pactionid, @pactivityid=:pactivityid," + \
"@pactivitydetailid=:pactivitydetailid, @pdos=:pdos, @pstarttime=:pstarttime, @pendtime=:pendtime, @pduration=:pduration," +\
"@pgroupsize=:pgroupsize, @plocation=:plocation, @prate=:prate, @punits=:punits, " +\
"@ptotalamt=:ptotal_amt, @preturn_message=:preturn_message",args)
if cn.CallResult.text <> "Success" then
on_validate.hasError = .t.
on_validate.errortext = "Server error reported was: " + cn.CallResult.text
cn.close()
end
end if

[jwilliamson]

Re: Help with MS SQL INSERT Stored Procedure

Thanks! Got it working! I had a syntax error. I don't know why I thought I needed parenthesis around the parameters, but I think that was the issue.

I didn't need to do

mySProc @parameter = :argument

because I was passing all the parameters in the same order as listed in the SP and could just do

mySProc :arguments

but obviously that isn't the case in every scenario

Thanks for the help!

[glenschild]

Re: Help with MS SQL INSERT Stored Procedure

Glad you got it to work, I must admit I have got into the habit of expressly naming the parameters as it helps when debugging and future reference.