Dataset

Classic Servoy
1

i want to make a sql query on my table ‘employees’ . How can i do it ? I have tried with

var dataset = databaseManager.getDataSetByQuery(‘example_data’,‘select lastname from employees’,null,100)

how can i use ‘dataset’ ?

but now i don’t know how to recover columns data

Someone can help me ?
Thanks

2

Hi Michele,

That is pretty basic stuff.

The best thing you can do is read the docs and look at the sample solutions I guess…

3

Where can i find ‘docs’ ??? The help in the developer version returns 0 if i search “dataset”

4

Try the ‘docs’ folder (it has a link to the wiki) and the ‘solutions’ folder. There are samples in there…

5

Hi Michelle, in order to retrieve a dataset from the database, you need to include the primairy key in your query, like this:

var vDS = databaseManager.getDataSetByQuery('example_data','select employee_id from employees',null,100)

Cheers,
-Karel

6

In docs i can’t find an explanation of datasource, dataset & foundset…if i repeat the examples in my form i don’t obtain the results i want…
I make a dataset, in the debug i see all elements of the dataset (my records). i can’t put my employeeid in a variable
I don’t know how read the element of the dataset…dataset.row_001…???

7

Karel Broer:
Hi Michelle, in order to retrieve a dataset from the database, you need to include the primairy key in your query, like this:

var vDS = databaseManager.getDataSetByQuery('example_data','select employee_id from employees',null,100)

Cheers,
-Karel

This is ok. The problem is that when i have my dataset i need to recover my fields content and i don’t know the correct way…

var dataset = databaseManager.getDataSetByQuery(‘example_data’,‘select * from employees’,null,100)
var uri = dataset.createDataSource( ‘uri_name’)
var fs = databaseManager.getFoundSet(???) //uri ? uri_name ?

8

Hi

Once you have your dataset, you can loop through it.

Within the loop you can retrieve data from spceific rows and columns using dataset.getValue(row,col)

for example

var server = globals.vServerToUse
var vQuery = 'select cd_id from component_detail'
var dataSet = databaseManager.getDataSetByQuery(server,vQuery,null,-1)

	for (var b=1; b<=dataSet.getMaxRowIndex(); b++)
	{
	
		application.output(dataSet.getValue(b,1))
	}

This example uses the index of the loop to loop down every row from column 1 of the dataset and output its value to the console, obviously you could do anything you want with the values from the dataset, like assign them to variables or pass them to functions.

Hope this helps,

Regards

9

Hi Zuke, thanks.
Ok for the dataset. I have seen i can navigate it.
But if i need to display the content of my dataset in a grid where the user has to select data what i need to do ?

10

Hi Michele,

I think you have to learn the basics first of Servoy.
Did you follow the free videos on http://www.servoyuniversity.com already?

For showing data in a grid, you dont have to use a query!, just create a form, based on that table, and set it to: table-view
You can also directly show the data in a tableview by creating a form method:

vQuery = ‘select cd_id from component_detail WHERE companyname = ?’
vArgs = [‘Test’]
controller.loadRecords(vQuery,vArgs)

but you can also do that doing this:

if(controller.find()) {
companyname = ‘Test’
controller.search()
}

Hope this helps

11

Thanks to all. I’ll try to explain to you what i want to reach. I have to make a query from my customer table on some parameters that the user has to insert and then display the results into a grid and let the user to select some customers with a check in the grid. Normally in foxpro, for example, the langage i use, i create a temporary cursor from my customer table and i create a logical temp field itnto my cursor that i use in the grid to select the customers. How can i make the same thing with servoy ?

Thanks

12

michele:
Thanks to all. I’ll try to explain to you what i want to reach. I have to make a query from my customer table on some parameters that the user has to insert and then display the results into a grid and let the user to select some customers with a check in the grid. Normally in foxpro, for example, the langage i use, i create a temporary cursor from my customer table and i create a logical temp field itnto my cursor that i use in the grid to select the customers. How can i make the same thing with servoy ?

Hi Michele -

Nice to meet a bunch of FoxPro developers at Servoy World. I also hung out for several hours at the Friday night FoxPro get-together and came away with a sense of how you guys are making the transition to Servoy. Nice to have all of you in our community.

Being confused about Servoy’s controller, foundset and dataset objects is nothing new. Probably the single biggest confusion point for all new Servoy developers. So don’t feel bad about getting stuck at this point. In fact, I’m actually a little surprised that you’ve skipped directly to dataset as your starting point. My guess is that the dataset makes more sense than the controller and foundset objects coming from your FoxPro background.

Anyway, since I’m currently in casting mode for our Sutra CMS release and this is one question that keeps coming up over and over and over and over…again; here’s a video cast where I build a quick solution that answers your specific question directly and then I transition into a full explanation of the differences between controller, foundset and dataset objects.

Controller vs Foundset vs Dataset p1
Controller vs Foundset vs Dataset p2

Hope this helps - David

PS. Attached is the Servoy solution and sample code sheet that I use in the cast.

Edit: error about minute 2 to 3 in the second video. You do need to set the index when using the controller with controller.setSelectedIndex() = i + 1 around line 13-14 in the cheat sheet. I haven’t looped with the controller object in years is my excuse :)

demo files.zip (7.92 KB)

13

Hi David, thanks, very very much. Good videos, good explanation. Foxpro developers have to look at Servoy in different way they were used. At the begininning it is not easy.