Servoy Forum

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:

Code: Select all

    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:

Code: Select all

    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:

Code: Select all

    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:



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

Code: Select all

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

and as:

Code: Select all

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

but, they all produce the same error.

What am I doing wrong?

Kim,

Servoy always loads data in a 2-steps:
1. select pk from tab where ...<conditions>...
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

Thanks for responding, Rob, and for the explanation.