Manage Results from SQL Searches

agis · October 25, 2003, 10:29am

With the command: getDataSetByQuery we can run a SQL search and fill the result in a variable (resp. array).
Now my Question: How to process the result. For Example showing the dataset in a portal, combine it with other datasets, make it to the aktual selection and so on. No idea how to manage this, could you help us?

IT2Be · October 25, 2003, 11:32am

I am not certaiin but I think you can only fill a portal through a relationship.

Filling fields etc. can be done like ‘dataprovider = dataset.getValue(x,y)’ and hooking up values to elements can be done by naming an element ‘element.name.whatever = dataset.getValue(x,y)’.

Hope this is somewhat of an answer. I am pretty new to servoy so there could be lot’s of other/better answers…

Marcel

Jan_Aleman · October 25, 2003, 4:21pm

Click on move sample while highlighting the object in the method editor and you’ll see some documented examples.

Additionally you can also load the results of a query into a form, in that case make sure your query is returning primary keys and load those into the form by using:

var query = “select orderid from orders where orderid = 10248”;
var pkdataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, 100);
controller.loadRecords(pkdataset);

Keep in mind that if you use the method above you can’t print anymore or sort.

Harjo · October 25, 2003, 10:51pm

jaleman:
Keep in mind that if you use the method above you can’t print anymore or sort.

I thought the limititations where solved!?
see last reply of Jan Blok:

Jan_Aleman · October 26, 2003, 9:35am

I stand corrected!

pbakker · January 16, 2004, 9:34pm

Question about loading the result of a custom SQL statement (getdatasetbyquery) into a form: No problem of getting the result into the form as a foundset, but is there a way to display it on listview or a portal?

What I’ trying is to get a listview of al the users in the system. The getasHTML doesn’t fullfill my needs (I think), because I want the user to be able to select one of the found users and based on the selection trigger methods.

So far I have the following code:

var pkdataset = security.getUsers()
//elements.test.text = ‘’+users.getAsHTML()+‘’;
controller.loadRecords(pkdataset)

Any way of doing this? (without building a form based on the repository)

Paul

pbakker · January 16, 2004, 9:45pm

I see we cannot base forms on the Servoy system DB-tables, since they are not visible when creating a new form…

bcusick · January 17, 2004, 10:08am

pbakker:
Question about loading the result of a custom SQL statement (getdatasetbyquery) into a form: No problem of getting the result into the form as a foundset, but is there a way to display it on listview or a portal?

What I’ trying is to get a listview of al the users in the system. The getasHTML doesn’t fullfill my needs (I think), because I want the user to be able to select one of the found users and based on the selection trigger methods.

So far I have the following code:

var pkdataset = security.getUsers()
//elements.test.text = ‘’+users.getAsHTML()+‘’;
controller.loadRecords(pkdataset)

Any way of doing this? (without building a form based on the repository)

Hi Paul,

Beginning in 2.0b1 you can execute a global method with INLINE HTML. So, if you displayed the list of users as HTML - you can add this line to your HTML - and have a “link” that the users can click on that execute a Servoy global method:

PersonName

You can even pass parameters to the global method:

PersonName

IMPORTANT NOTE: If you pass parameters - they MUST be surrounded by SINGLE QUOTES.

Hope this helps,

Bob Cusick

pbakker · January 17, 2004, 12:09pm

Good thing I wrote ‘(I think)’… all these new features, I cannot keep up with them.

Tnx for the help, I’ll go and play around with it.

Two other questions though:

Based on your answer: I asume that it is not possible to retrieve the result in a portal of normal listview?
How can I alter the look of the HTML retrieval, to make it look more like a regular listview?

Paul

maarten · January 17, 2004, 12:47pm

Based on your answer: I asume that it is not possible to retrieve the result in a portal of normal listview?

portal>no, listview>with controller.loadRecords(pkdataset)

How can I alter the look of the HTML retrieval, to make it look more like a regular listview?

you can do a loop through the dataset and combine html with the
dataset values:

var HTML = '<html><table>' //var HTML receives all html code
//get dataset by query

for( var i = 1 ; i <= dataset.getMaxRowIndex() ; i++ )
{
    dataset.rowIndex = i;
    HTML += '<tr><td>'+ dataset[1]+'</td><td>'+ dataset[2]+'</td></tr>';
}
HTML += '</table>'
elements.test.text = HTML; //set your label with the html code

pbakker · January 17, 2004, 1:59pm

Maarten,

“controller.loadRecords(pkdataset)”: does this work with a dataset retrieved by getDatasetByQuery, especially if the Query runs over another table (or multiple tables) than the table the form, on which you want to display the dataset, is based?

As for the suggested loop through the data: I’ll go and fiddle with it…

Paul

bcusick · January 17, 2004, 2:19pm

pbakker:
“controller.loadRecords(pkdataset)”: does this work with a dataset retrieved by getDatasetByQuery, especially if the Query runs over another table (or multiple tables) than the table the form, on which you want to display the dataset, is based?

YES! Does work. That’s sort of the whole point…

In your select statement returnONLY the primary key of table you’re going to display in.

For example: customers is the base table for my listview (used in a tabpanel). The primary key is “custId”.

Your query would look like:

SELECT custId from customers where …

Bob Cusick

pbakker · January 17, 2004, 3:19pm

:lol:

Well, I guess I have to try more…

Last question then: If I use the security node to retrieve all user_id’s and user_names, the PK is User_ID and I want to display the result using controller.loadrecords(pkdataset), I asume I must have a form based on the table where the user_ID’s are pk as well, right?

As far as I know, I cannot build a form on that table, because that table (a Servoy table) does not show in the available tables when creating a new form…

jcompagner · January 19, 2004, 11:36am

no you can’t create a form based on a servoy table
The only thing to do is showing the user data in a value list

var ds = security.getUsers();
var names = new Array();
var id = new Array();
for(var k=1;k<ds.getMaxRowIndex();k++)
{
	id[k-1] = ds.getValue(k, 1);
	names[k-1] = ds.getValue(k, 2);
}
application.setValueListItems( 'users',  names,id);

pbakker · January 28, 2004, 9:55pm

According to Bob, you could now have a link in HTML view launch a Servoy method. Based on the given examples, I created the following code. The HTML shows that it is a link, but the method is not launched… where am I going wrong?

var pkdataset = security.getUsers()
var HTML = ‘

’ //var HTML receives all html code
for( var i = 1 ; i <= pkdataset.getMaxRowIndex() ; i++ )
{
pkdataset.rowIndex = i;
HTML += ‘’;
}
HTML += ‘

’+ pkdataset[1]+‘

’+ pkdataset[2]+‘

’
elements.test.text = HTML; //set your label with the html code

tnx in advance,

Paul

bcusick · January 29, 2004, 7:28am

pbakker:
According to Bob, you could now have a link in HTML view launch a Servoy method. Based on the given examples, I created the following code. The HTML shows that it is a link, but the method is not launched… where am I going wrong?

var pkdataset = security.getUsers()
var HTML = ‘
’ //var HTML receives all html code
for( var i = 1 ; i <= pkdataset.getMaxRowIndex() ; i++ )
{
pkdataset.rowIndex = i;
HTML += ‘’;
}
HTML += ‘
’+ pkdataset[1]+‘ ’+ pkdataset[2]+‘
’
elements.test.text = HTML; //set your label with the html code

tnx in advance,

Paul

Hi Paul,

Are you trying to pass the the pkdataset[2] INTO the script? If so, you need to use the following code:

var pkdataset = security.getUsers()
var HTML = ‘

’ //var HTML receives all html code
for( var i = 1 ; i <= pkdataset.getMaxRowIndex() ; i++ )
{
pkdataset.rowIndex = i;
HTML += ‘”;
}
HTML += ‘

’+ pkdataset[1]+‘

’
elements.test.text = HTML; //set your label with the html code

Those double quotes, single quotes double quotes will kill you. :-)

Bob

pbakker · January 29, 2004, 8:19am

Hi Bob,

No, so far I was just trying to launch a method… but clicking on the HTML link doesn’t do a thing…

Passing a variable into the mathod is the next step…
Paul

pbakker · January 29, 2004, 8:44am

BTW, I tried your altered code, but it doesn’t get through verification.. Didn’t find the quote or double quote that has to be deleted, added or moved…

maarten · January 29, 2004, 9:03am

Here’s some other sample code

'<A href="javascript:calShow(\'calWeekView\', '+weekOfYear+')">.......</A>'

method=calShow
parameters:

literal text
variable

pbakker · January 30, 2004, 8:22pm

Guys, I’ve tried all i can think of, but cannot get this to work…

I examined all the example code you put on the forum, and based on the logic I can derive from it, I think the following code is correct:

var pkdataset = security.getUsers()
var HTML = ‘

’ //var HTML receives all html code
for( var i = 1 ; i <= pkdataset.getMaxRowIndex() ; i++ )
{
pkdataset.rowIndex = i;
HTML += ‘’;
}
HTML += ‘

’+ pkdataset[1]+‘

’+ pkdataset[2]+‘

’
elements.test.text = HTML;

The problem is: It doesn’t work.

The result of this code is the following: In the label I get a list of user_ID and user_Name, where the user_Name is collor Blue, like there is a link behind it. But if I click the link, the method test2 does not start.

The method: I made a very simple method, that just shows a dialog, nothing fancy. I tried it both with a Form methos and a global method, both give no result.

I guess I’m stuck…please enlighten me…

Paul

Topic		Replies	Views
GetDataSetbyQuery - Displaying results on form Classic Servoy	3	2840	October 27, 2004
Query result in portal Classic Servoy	7	2874	April 17, 2009
SQL Query Question Classic Servoy	9	6939	May 19, 2004
getDataSetByQuery calculated field to form Classic Servoy	9	3916	June 16, 2011
Handle Your SQL Classic Servoy	5	3087	July 26, 2005

Manage Results from SQL Searches

Related topics