sql query retuns nothing

Classic Servoy
1

Hello:

I have this method for a test query (SQl Server 2005):

function _SQL_Test()
{
	var sql = "DECLARE @RC INT SET @RC = 1 SELECT @RC";
	var dataset = databaseManager.getDataSetByQuery(currentcontroller.getServerName(), sql, null, 1);
	application.output(dataset.getValue(1,1));
}

But the dataset variable always returns null. With my SQL client I receive a dataset (1 row, 1 column), so the sql syntax is OK. Where is the issue?

Thanks,

2

databaseManager.getDataSetByQuery()

only accepts query’s wich begins with SELECT…

You could try the rawSQL plugin for that

3

But rawSQL doesn’t return anything, only true or false :cry:

4

log-out:
But rawSQL doesn’t return anything, only true or false :cry:

The SQL code you are trying to run needs to be a stored procedure. Then check out the sample code to rawSQL.executeStoredProcedure. The rawSQL plugin returns a dataset when executing a store procedure.

5

david:
The SQL code you are trying to run needs to be a stored procedure.

??? :shock: :shock:
But how?? It’s a simple query, not a procedure (no arguments, no types array…)

6

My guess is that as simple as it is, it has 2 statements in it. If you need to run a string of SQL commands that depend on each other or if you need to return data from the rawSQL plugin, the stored procedure approach is the way to go.

7

To add the previous comments:
get dataset by query accepts statements that begin with “select” OR with “with”.