Use columnames with dataset

Classic Servoy
1

I have a query that I execute and it returns a dataset. I can get the data out with dataset.getvalue(row,col) (for example dataset.getValue(1,1) ), but I don’t want to use column numbers but the column names instead. I can see the names are present in the dataset because a dataset.getColumnName(index) gives me correct names back. So is there a good reliable way to acces the data in a dataset with columnames? Something like dataset[rownumber][columnname]?

2

Hi,

within your loop you can set the dataset rowindex with something like:

myDs.rowindex = i;

You can start using columnnames right away, but you probably want code completion on this as well.
This can be achieved using JSDocs as:

var myQry = "SELECT client_id, client_name, NULL AS myExtraInfo FROM clients"
/** @type {JSDataSet<client_id:Number, client_name:String, myextrainfo:String>} */
var myDs = databaseManager.getDatasetByQuery(etc. etc. etc.)

for(var i = 1; i <= myDs.getMaxRowIndex(); i++) {
   myDs.rowindex = i;
   application.output(myDs.client_name);
   myDs.myextrainfo = 'some additional text'
}

Although the JSDoc needs some typing, your code is gonna look a whole lot better and you never have to think about shifting column index again whenever you insert an additional field in your query.
Also setting the value works quite convenient.

Only thing to bear in mind: Servoy handles all columnnames in lowercase.
That’s why I converted the ‘myExtraInfo’ to lowercase already in the JSDoc

You could work on a bit of code which outputs the columnnames in JSDoc format already.
This will save you some work as your queries get bigger and return more columns.

3

As I could use the convenience of generating JSDoc as well, I thought of doing the job in between
Just put it in some ‘tool’ or ‘utils’ scope and pass a dataset to the function…

/**
 * @param {JSDataSet} _ds
 */
function generateDsJSDoc(_ds) {
	var _sName,
		_nType,
		_sType,
		_aColumns = [];
	for (var i = 1; i <= _ds.getMaxColumnIndex(); i++) {
		_sName = _ds.getColumnName(i).toLowerCase();
		_nType = _ds.getColumnType(i);
		switch(_nType) {
			case JSColumn.TEXT:
				_sType = ':String';
				break;
			case JSColumn.INTEGER:
			case JSColumn.NUMBER:
				_sType = ':Number';
				break;
			default:
				_sType = ''
		}
		
		_aColumns.push(_sName + _sType);
	}
	
	var _sDoc = '/** @type {JSDataSet<' + _aColumns.join(',') + '>} */';
	application.setClipboardContent(_sDoc);
	
	application.output('JSDoc has been placed in your clipboard:');
	application.output(_sDoc);
}
4

THX! this is exactely what I needed. works like a charm :)

5

An additional question. I’m getting an error, that I don’t understand.

I have a Postgresql database, but there were some changes, so I deleted the database, created a new one and imported data into it. I have made a select query to get some data out of it.
I’m working with two databases, and doing the same on both databases. One gives me a dataset without any problems, the other gives the error.

query = "";
	query += "SELECT ";
	query += " * ";
	query += "FROM ";
	query += "collection ";
	query += "WHERE cmc_thid = ? ";

	Dataset = databaseManager.getDataSetByQuery( DB, query, ["45:80006501:AAEC7704"], 1 );

ERROR: relation “collection” does not exist
Position: 16
Wrapped java.lang.RuntimeException: com.servoy.j2db.dataprocessing.DataException: ERROR: relation “collection” does not exist

6

Your problem is in the PG db.
Quick search on Google returned this:

7

You also could use the \ to write SQL strings over several lines, like

sSQL = "SELECT * FROM collection WHERE \
           column1 = ? AND \
           column2 = ?";

One needs to put a blank before the \ and no blank behind.

8

I’m not really familiar with Postgresql. But why do i need to make a relation? “collection” is just a table in the database, i can make a form with this table as a datasource, so I don’t really understand why I get the error…

9

That is the standard error message in that case.
You will get the same message when you try a “select * from x”, when x is not existent.

Codd also did not talk about tables but about relations, so a relation is represented by a table, see Relation (database) - Wikipedia

10

We changed the columnnames to all lowercase, and now it works. So I guess it was a conflict between PG and Servoy in the columnnames.

11

nick1461658753:
We changed the columnnames to all lowercase, and now it works. So I guess it was a conflict between PG and Servoy in the columnnames.

Be aware that database objects are case sensitive.
PostgreSQL defaults to lowercase (Oracle for instance defaults to uppercase) so when you use SQL like you did then it assumes you mean “collection”.
Lets say your table name is “Collection” then you need to put the object name between double quotes.
So you SQL would look like this:

var query = "SELECT \
   * \
   FROM \"Collection\" \
   WHERE cmc_thid = ? ";

Dataset = databaseManager.getDataSetByQuery( DB, query, ["45:80006501:AAEC7704"], 1 );

Hope this makes things more clear.