Naming the columns of a DataSet half works

Questions
1

Hi,
Please forgive any schoolboy errors. I am very much a Servoy schoolboy.

	let q = datasources.mem.rcrParams.createSelect();
	q.result.add(q.columns.cus_name).add(q.columns.cxbprofile).add(q.columns.source).add(q.columns.cxbvalue)
		.add(q.case.when(q.columns.usertype.eq('smiths')).then(1).else(0).sum, 'cntSmiths')
		.add(q.case.when(q.columns.usertype.eq('cust')).then(1).else(0).sum, 'cntCust');
	q.where.add(q.columns.bssprmname.eq(txtFindParamValue));
	q.groupBy.add(q.columns.cus_name).add(q.columns.cxbprofile).add(q.columns.source).add(q.columns.cxbvalue)
	q.sort.add(q.columns.cus_name).add(q.columns.cxbprofile).add(q.columns.source).add(q.columns.cxbvalue)

	/** @type {JSDataSet<{cus_name:String, cxbprofile:Number, source:String, cxbValue:String, cntCust:Number, cntSmiths:Number}>} */
	let ds = databaseManager.getDataSetByQuery(q, -1);
	application.output('ds.getMaxRowIndex() = ' + ds.getMaxRowIndex()); 
	let s = '', cusName = 'x', cusCount = 0, cusStr = '', secStr = '';

	for (let i = 1; i <= ds.getMaxRowIndex(); i++) {
		ds.rowIndex = i;
		if (cusName != ds.cus_name + ds.cxbprofile) {
			cusStr = '', secStr = '';
			cusName = ds.cus_name + ds.cxbprofile;
			cusCount++;
		}
		if (ds.source == 'Customer') {
			cusStr = 'Customer: ' + ds.cxbValue.trim();
//			cusStr = 'Customer: ' + ds.getValue(i,4).trim();
		} else {
//			secStr = ds.cntCust + '+' + ds.cntSmiths;
			secStr = ds.getValue(i,5) + '+' + ds.getValue(i,6);
		}

The lines of interest here are

/** @type {JSDataSet<{cus_name:String, cxbprofile:Number, source:String, cxbValue:String, cntCust:Number, cntSmiths:Number}>} */
301		cusName = ds.cus_name + ds.cxbprofile;
304		if (ds.source == 'Customer') {
305		cusStr = 'Customer: ' + ds.cxbValue.trim();
306	//	cusStr = 'Customer: ' + ds.getValue(i,4).trim();
309 	secStr = ds.getValue(i,5) + '+' + ds.getValue(i,6);

Lines 301 & 304 work fine.
Line 305 gives “TypeError: Cannot call method “trim” of undefined”.
If I replace 305 with 306 it works fine. 309 is necessary for the same reason.

So the first three of my six named columns work, the last three fail.
Any ideas?
Regards
John

2

Hi John,

one gotcha here: names are always converted to lowercase.
So even your docs:

@type {JSDataSet<{cus_name:String, cxbprofile:Number, source:String, cxbValue:String, cntCust:Number, cntSmiths:Number}>}

will not force the dataset to use uppercase characters.

try:

ds.cxbvalue.trim()

and you’ll see it works

Hope this helps

3

With hindsight it’s blindingly obvious that the ones that are working are the ones in lower case.

Thanks. I’ll switch to using lower case column names in Servoy.

John

4

Hi John,

and eventually keep in mind that your next database might have case sensitive column names.
Even SQL Server can be (miss-)configured to behave like this.

That can become painful easily when coming from a dev environment like foxpro

5

Thanks, Joachim

In the first line my in-memory table name is also in mixed case.

let q = datasources.mem.rcrParams.createSelect();

Is this also a problem?

John

6

not sure
when going the way of “defensive programming”: keep it all lowercase ;-)
eventually use underscores to enhance readability?