Getting a record by primary key?

Classic Servoy
1

I feel like I’m missing something obvious here but…Is there a way in servoy to get a record from a table by mearly passing the primary key?

In a global method I’m getting a foundset with getFoundSet
var ci_foundset = databaseManager.getFoundSet(currentcontroller.getServerName(), ‘carton_item’);

Now say I want to get record 11000 (that being the PK id) in that table, the only way I see to do that is by doing a find, passing the value, then a search, then ci_foundset.getRecord(1). But that feels like too many steps for what seems like it should be a simple operation. I think I can get a dataset from a query that just gets one record, then load that into the foundset, but that feels just as clumsy.

Basically I’m looking for something like ci_record = ci_foundset.getRecordByPK(primary key)

Am I missing something here?

2

Hi Ryan,

ORIGINAL POST :
There was a new 3.5 function added to the JSFoundset node called : selectRecord(pk_id) which works on passing the primary key

Whoops, NO, this does not do what I believe that you need - back to the drawing board :(

One question is:
How do you know which primary key you want ?

Cheers
Harry

3

The problem with features like this is that records of a foundset are “lazy loading”. Not all records are transferred to the client, but only the first 200. So what should happen if your record is not among the first 200? Servoy would have to transfer all records to the client and then jump to that one. Why not use the ways you suggested? There is nothing wrong about them. Or do something like this

controller.loadRecords(‘SELECT pk FROM table WHERE pk = ?’, [yourPk]);

Hope this helps…

4

Yeah, I understand the limitation of the foundset. Mostly I was using it as an example as I couldn’t think of anywhere else I could get a IRecord object.

The methods I presented work fine. In fact I use the find, search, foundset.getRecord(1) method all the time. I just felt there should be a way to just pick a IRecord object out of a table with one simple function and I was failing to find it. I’m used to being able to do that in the other ORM’s I work with during the day.

So I guess since there isn’t a function already, can I request that it get added to the queue as a feature request?

Something like…

IRecord databaseManager.getRecordByPK(server name, table name, requested primary key)

5

Hi,

What if you use

forms.<form>.loadRecords( databaseManager.convertToDataSet([primKey Value]) );

Regards,

6

For the sake of future people searching this is what I did thanks to this post…
http://www.servoymagazine.com/home/2008 … ecord.html

I created a global called gRecordByPK

/**
*	gRecordByPK
*
*	Retreives a record by its primary key.
*
*	@pram string arguments[0] Server name
*	@pram string arguments[1] Table name
*	@pram int arguments[2]	Primary key ID
*
*	@returns mixed IRecord, or bool false if record was not found
*
**/

vServer = arguments[0];
vTable = arguments[1];
vPKid = arguments[2];

if (vPKid == null) {
	return false;
}

//create a foundset
var vSet = databaseManager.getFoundSet(vServer, vTable);

//load record by ID in foundset
vSet.loadRecords(databaseManager.convertToDataSet([vPKid]));

//or retrieve record data from the record object
if (databaseManager.hasRecords(vSet)) {
	var vRecord = vSet.getRecord(1);
} else {
	var vRecord = false;
}
return vRecord;
7

Thanks for that code snippet, Ryan.

Ben