getValue() by columnname using getDataSetByQuery

Discuss all feature requests you have for a new Servoy versions here. Make sure to be clear about what you want, provide an example and indicate how important the feature is for you

getValue() by columnname using getDataSetByQuery

Postby Bonkie » Wed Feb 10, 2010 12:24 pm

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

Code: Select all
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

Code: Select all
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);
}
Rick Bonkestoter
Certified Servoy 7 Developer

Direct ICT / ServoyCamp
User avatar
Bonkie
 
Posts: 68
Joined: Wed Jun 27, 2007 11:05 am
Location: Den Ham, The Netherlands

Re: getValue() by columnname using getDataSetByQuery

Postby lvostinar » Wed Feb 10, 2010 1:39 pm

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)
Laurian Vostinar
Servoy
lvostinar
 
Posts: 1051
Joined: Tue Feb 19, 2008 10:53 am

Re: getValue() by columnname using getDataSetByQuery

Postby pbakker » Fri Feb 12, 2010 4:40 pm

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

Paul
pbakker
 
Posts: 2822
Joined: Wed Oct 01, 2003 8:12 pm
Location: Amsterdam, the Netherlands

Re: getValue() by columnname using getDataSetByQuery

Postby jcompagner » Mon Feb 15, 2010 5:45 pm

or

Code: Select all
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!
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8212
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet

Re: getValue() by columnname using getDataSetByQuery

Postby Bonkie » Wed Feb 17, 2010 2:33 pm

Ok didn't know that. But does that also work in Servoy 3.5.x?
Rick Bonkestoter
Certified Servoy 7 Developer

Direct ICT / ServoyCamp
User avatar
Bonkie
 
Posts: 68
Joined: Wed Jun 27, 2007 11:05 am
Location: Den Ham, The Netherlands

Re: getValue() by columnname using getDataSetByQuery

Postby jcompagner » Wed Feb 17, 2010 2:44 pm

Bonkie wrote: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 ;) )
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8212
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet

Re: getValue() by columnname using getDataSetByQuery

Postby Bonkie » Wed Feb 17, 2010 2:45 pm

jcompagner wrote:
Bonkie wrote: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?
Rick Bonkestoter
Certified Servoy 7 Developer

Direct ICT / ServoyCamp
User avatar
Bonkie
 
Posts: 68
Joined: Wed Jun 27, 2007 11:05 am
Location: Den Ham, The Netherlands

Re: getValue() by columnname using getDataSetByQuery

Postby jcompagner » Wed Feb 17, 2010 3:45 pm

no those new constructs cant be used in 3.5
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8212
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet

Re: getValue() by columnname using getDataSetByQuery

Postby birgit » Thu Feb 18, 2010 11:58 am

Hi

What does the "-1" mean in
Code: Select all
databaseManager.getDataSetByQuery(..., -1);

Thanks and regards
Birgit
Birgit Rieder
7r gmbh, Switzerland
SAN Developer
User avatar
birgit
 
Posts: 302
Joined: Sun Jan 15, 2006 10:04 pm
Location: Switzerland

Re: getValue() by columnname using getDataSetByQuery

Postby Harjo » Thu Feb 18, 2010 12:14 pm

-1 means, no limit on max returned records.
Harjo Kompagnie
Direct ICT / Servoy Hosting / ServoyCamp
Servoy Certified Developer
Servoy Valued Professional
SAN Developer
User avatar
Harjo
 
Posts: 4296
Joined: Fri Apr 25, 2003 11:42 pm
Location: DEN HAM OV, The Netherlands

Re: getValue() by columnname using getDataSetByQuery

Postby birgit » Thu Feb 18, 2010 12:49 pm

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
Birgit Rieder
7r gmbh, Switzerland
SAN Developer
User avatar
birgit
 
Posts: 302
Joined: Sun Jan 15, 2006 10:04 pm
Location: Switzerland

Re: getValue() by columnname using getDataSetByQuery

Postby jcompagner » Thu Feb 18, 2010 12:57 pm

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.
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8212
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet

Re: getValue() by columnname using getDataSetByQuery

Postby birgit » Thu Feb 18, 2010 1:16 pm

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
Birgit Rieder
7r gmbh, Switzerland
SAN Developer
User avatar
birgit
 
Posts: 302
Joined: Sun Jan 15, 2006 10:04 pm
Location: Switzerland

Re: getValue() by columnname using getDataSetByQuery

Postby pbakker » Thu Feb 18, 2010 2:02 pm

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
pbakker
 
Posts: 2822
Joined: Wed Oct 01, 2003 8:12 pm
Location: Amsterdam, the Netherlands


Return to Discuss Feature Requests

Who is online

Users browsing this forum: No registered users and 2 guests