Naming the columns of a DataSet half works

[harbourj]

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

Code: Select all

   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

Code: Select all

/** @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

[mboegem]

Hi John,

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

Code: Select all

@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:

Code: Select all

ds.cxbvalue.trim()

and you'll see it works

Hope this helps

[harbourj]

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

[hilgers]

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

[harbourj]

Thanks, Joachim

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

Code: Select all

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

Is this also a problem?

John

[hilgers]

not sure
when going the way of "defensive programming": keep it all lowercase
eventually use underscores to enhance readability?