Manage Results from SQL Searches

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?

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

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.

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:

I stand corrected!

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

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

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

Good thing I wrote ‘(I think)’… :D 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

  • 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

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

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… :D

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

: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…

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);

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

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]+‘<a href="javascript:test2(’" + pkdataset[2]+ “')>

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

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

Bob

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… :D
Paul

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… :P

Here’s some other sample code

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

method=calShow
parameters:

  1. literal text
  2. variable

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