dbase.executeQuery returns null instead results MSSQL 2005

[josuedavid]

Hi there, I got a custom login procedure, in the loginRequest event I call to a stored procedure, but the executeQuery function is returning null almost always, the SP always return results even if the username and password is not found in the database, this is weird, I'm connecting trough an ODBC connection to an SQL SERVER 2005 database, this is the error and the code.

this is the SFS log

upaepZone.as]: loginRequest <------ the event is fired
[upaepZone.as]: exec dbo.loginSmartfox 'arenas','arenas', 8 <---- I call the stored procedure
[upaepZone.as]: true <------ I'm tracing if the query results object is null
Error in extension [ upaepZone.as ]: TypeError: Cannot call method "get" of null (upaepZone.as#1528) Internal: 45 -- Line number: 44 in file: upaepZone.as <---- this is the error

this is the code

Code: Select all

var error = ""
trace(evt.name);
	if (evt.name == "loginRequest"){
		var nick = evt["nick"]
		var pass = evt["pass"]
		var chan = evt["chan"]
		
		var obj = _server.loginUser(nick, pass, chan) //temporally log the user to get an userId from the smartfox
		var response = new Object()
		response._typeMessage = "sys"
		if (obj.success){
			
			var u = _server.instance.getUserByChannel(chan)
			var sqlquery = "exec dbo.loginSmartfox '" + nick + "','"+ pass +"', " + 	u.getUserId()
			trace(sqlquery)
			var queryRes = dbase.executeQuery(sqlquery)
			trace(queryRes == null)
			var tempRow = queryRes.get(0) // the SP is always returning data even if the user is wrong but the result of the query is always null, Why???
				if (tempRow.getItem("Type") == "LoginSuccess"){
									response._cmd = "logOK"
					response.n = nick
					response.id = u.getUserId()
					response.mod = 0
					
				}else{
					//_server.kickUser(u, 15, "invalid username or password") //this is not working
					response._cmd = "logKO"
					response.err = "Invalid username or password"
				}
		}else{
			response._cmd = "logKO"
			response.err = obj.error
	}
	
	_server.sendResponse(response, -1, null, chan)
		}

if I restart the server, this works correctly, but only for a few times, then the error comes again
Thank you in advance for your help.

[Lapo]

I am no expert in this field. I know we have many customers interfacing MSSQL with SFS and they use stored procedures as well.
If you search a bit in this forum you should more infos.
Additionally you should look into a JDBC tutorial for MSSQL, this should clear all the doubts

p.s. = could you please resize the avatar 70 pixels max, thnx

[josuedavid]

Thanks Lapo I have founded the solution, is just matter of setting to GROW the OnExhaustedPool of the database manager of my zone.

PS. I have changed the avatar, do you like this???

[Lapo]

Thanks Lapo I have founded the solution, is just matter of setting to GROW the OnExhaustedPool of the database manager of my zone.

I see, glad it works now.

PS. I have changed the avatar, do you like this???

Awesome!

[mistermind]

Also a little note here:
Even thou SQL is not case sensitive, JaVa Is.
I just had a small issue a couple hours ago with extensions. It turns out that if you send a database call from your extension, make sure your columns respect their original names. In my case I had written "acctype" on my table long ago and always referred to it as accType in all my ASP and Query Browser codes. When I tried to use the same approach on an extension I kept getting a null. It turns out the original column was named acctype, not accType