Query Builder Result Clause

Classic Servoy
1

Version: 6.1.4 - build 1429

I am attempting my first use of the Query Builder and need help replicating the following SQL statement:

SELECT index, mailcode FROM eom WHERE period = ?

Simple, eh?

Here is the initial Query Builder code:

    var query = databaseManager.createSelect( 'db:/ucsd/eom' )

        query.where.add( query.columns.charge_period.eq( query.getParameter( 'period' ) ) )
        query.params[ 'period' ] = period

    var fs = databaseManager.getFoundSet( 'db:/ucsd/eom' )

    fs.loadRecords( query )

and, it loads all records for the given period. Now, I want to select a specific column:

    var query = databaseManager.createSelect( 'db:/ucsd/eom' )

        query.result.add( query.columns.index )
        query.where.add( query.columns.charge_period.eq( query.getParameter( 'period' ) ) )
        query.params[ 'period' ] = period

    var fs = databaseManager.getFoundSet( 'db:/ucsd/eom' )

    fs.loadRecords( query )

and, it returns a single column of data for the period as expected. Now, I want to select two specific columns:

    var query = databaseManager.createSelect( 'db:/ucsd/eom' )

        query.result.add( query.columns.index )
        query.result.add( query.columns.mailcode )
        query.where.add( query.columns.charge_period.eq( query.getParameter( 'period' ) ) )
        query.params[ 'period' ] = period

    var fs = databaseManager.getFoundSet( 'db:/ucsd/eom' )

    fs.loadRecords( query )

and, this is where the problem arises. The following error is emitted:

[attachment=0]QueryBuilderErrorMessage.png[/attachment]

I have tried re-writing the ‘query.result’ in two different ways:

        query.result
            .add( query.columns.index )
            .add( query.columns.mailcode )

and as:

query.result.add( query.columns.index ).add( query.columns.mailcode )

but, they all produce the same error.

What am I doing wrong?

2

Kim,

Servoy always loads data in a 2-steps:

  1. select pk from tab where ……
  2. select column-data from tab where pk in (?. ?, …, ?)

A foundset query, either custom sql or QueryBuilder, must always be the pk-query.
In your first try, you did not specify anything in the result clause, in that case Servoy defaults to the pk(s) of the table.

Just load the foundset with pk (or let Servoy default to pk columns) and use columns in the foundset records, they will be loaded in step 2.

Rob

3

Thanks for responding, Rob, and for the explanation.