how to display data that have no unique key?

Classic Servoy
1

I’m trying to display data from a database view (in this case MS-SQL 2008) which provides a list of records which has NO unique key to identify each row.
This view was provided to simplify reporting and dispaying of the undelying data structure.

First of all: I don’t want to discuss the data structure :D AND I can’t change anything in the database… :(
Using the view on a form and filtering the data, the form shows the correct number of records and all match the search criteria.

This is what servoy seems to do (taken from the Performance data log):

Find 
select top 201 logon1 from MyView where logon1 = ? order by logon1 asc

Load foundset 
select top 4 logon1, id, typ, scope, typ2, delegator_id, logon2, [Start], [Stop] from MyView where logon1 in (?, ?, ?)

But this are not the same records that you get if you issue an identical SQL SELECT in the/a SQL-editor…
In my tests I got 3 records which contained one duplicated record. The result seems to be depending on “how wrong” I’m defining the row ident columns (so that Servoy feels happy to show any data from that view).

therefore:
a) it makes me a bit “nervous” (I’m pretty new in Servoy) that Servoy seems to present just wrong data if your PK definitions are not ok
b) do I really need to issue a “manual” SQL and create the according form on the fly via solution model?

thx in advance
Jo

2

You just have to figure out the correct row identificators, as a last resort try to set each and every column as row_ident.

3

Servoy seems to present just wrong data if your PK definitions are not ok

Well, yes. But that’s not a Servoy issue - Servoy uses Hibernate for its ORM layer, and Hibernate needs correct PK information, and it couldn’t really work any other way.

4

Using all/more colums is not an option because the performance (at least in this case) would be terribly slow.

Coming back to part b) of my question ;-)

do I really need to use rawsql + SOM to configure the form on the fly?

just wondering…
thx a lot

5

Why not stick it in an html field?

Create a label put it on your form make it big enough.
Use the getAs html function on the dataset to get the data (check the example code for an example)
put it on the label. Done!

hilgers:
Using all/more colums is not an option because the performance (at least in this case) would be terribly slow.

Coming back to part b) of my question ;-)

do I really need to use rawsql + SOM to configure the form on the fly?

just wondering…
thx a lot

6

Alternatively, you can select the data (databaseManager.getDatasetByQquery) and convert it to a data source (dataset.createDataDource).

There is also databaseManager.createDataSourceByQuery() to do both above in 1 step.

Data sets do not need unique rows.

Rob

7

Rob,
“databaseManager.createDataSourceByQuery()” seems to be what I was looking for!

I’m having a strong and long-time background in FoxPro and things which are absolutely simple to be done in FoxPro (e.g. "display a list and don’t care what’s in it) are sometimes hard to find in Servoy - and even harder to explain to servoy people ;-)

THX all!

8

You can get away with it in the FoxPro world because of the way DBF files work, they have the concept of the record number even if you don’t have an actual field that uniquely identifies rows. Having said that, IMO it’s a good idea to define an auto-incrementing integer id field for every FoxPro table anyway. I realise that in this case you cannot change the FoxPro data, however.

9

Alan,

I know the conceptional differences between Fox/DBF and SQL-(server) databases especially regarding needed PKs… ;-)

For me it just was hard to find out that servoy handles datasources used in the designers (which are updateable) differently than the ones defined as queries.

nevertheless THX!