getValue() by columnname using getDataSetByQuery

Classic Servoy
1

Hi there,

I dont know if this request is already been made but I would like to have the option to select the value by columnname instead of column index.

For example now it is

var vSQL = databaseManager.getDataSetByQuery('server', 'SELECT * FROM table', null, -1);

// Lets loop trough the result set
for(i = 1; i < vSQL.getMaxRowIndex(); i++)
{
    variable = vSQL.getValue(1,i);
}

But this is not usable because the order of the index could change or be different on any SQL installation. But you dont want to select the column manually if you need them all when you have about 80 columns or what.

So what I would like to see is

var vSQL = databaseManager.getDataSetByQuery('server', 'SELECT * FROM table', null, -1);

// Lets loop trough the result set
for(i = 1; i < vSQL.getMaxRowIndex(); i++)
{
    variable = vSQL.getValue('pk',i);
}
2

you can use vSQL[‘my_column’] to return the value from current row (where rowIndex points); not sure when it was introduced , I think in some version of Servoy 4 (I can check if you need this info)

3

Even vSQL.columnName should work, but you have to set the rowIndex first.

Paul

4

or

for(i = 1; i < vSQL.getMaxRowIndex(); i++)
{
    variable = vSQL[i]['name'];
}

without setting the row index.

there is one catch, that only works for GETTING values not for setting!

5

Ok didn’t know that. But does that also work in Servoy 3.5.x?

6

Bonkie:
Ok didn’t know that. But does that also work in Servoy 3.5.x?

no only in the latest versions of servoy. (that you should use by now ofcourse ;) )

7

jcompagner:

Bonkie:
Ok didn’t know that. But does that also work in Servoy 3.5.x?

no only in the latest versions of servoy. (that you should use by now ofcourse ;) )

To badly that some customers still work @ Servoy 3.5… But is really no option to use it in 3.5?

8

no those new constructs cant be used in 3.5

9

Hi

What does the “-1” mean in

databaseManager.getDataSetByQuery(..., -1);

Thanks and regards
Birgit

10

-1 means, no limit on max returned records.

11

Wow, thanks, Harjo! That’s what I was hoping for. I didn’t know about this, but I can use it everywhere now :D

Regards
Birgit

12

You better can specify 1000 or something… (or 10K)
just to be sure that not somehow that query will transfer billion of rows to your client.

13

Hi Johan

Billion rows of course is not an issue. In our environment we talk about hundreds of rows. Until now I always defined an upper border of 1’000 or 10’000 rows. And I always had this bad feeling of: what happens if the maximum number of rows returned is too small? Should I always ask for hadMoreData and log this if yes?

I prefer no upper limit. And if the number of rows exceeds a reasonable size, I will add useful filters to the user.

Other ideas?

Regards
Birgit

14

Do set an upper limit and check if there were more rows. If yes, prompt the user with a meaningful message or log something, what ever you like, like giving them the option to extend the upper limit, maybe.

IMHO it’s better to have an upper limit and notify the user somehow than having the Client stall/crash because there are too many rows downloaded.

Paul