Query Builder Result Clause

Questions, tips and tricks and techniques for scripting in Servoy

Query Builder Result Clause

Postby kwpsd » Sat Jul 13, 2013 12:54 am

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:

QueryBuilderErrorMessage.png
QueryBuilderErrorMessage.png (14.79 KiB) Viewed 1354 times


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 W. Premuda
San Diego, CA USA
User avatar
kwpsd
 
Posts: 635
Joined: Sat Jul 28, 2007 6:59 pm
Location: San Diego, CA USA

Re: Query Builder Result Clause

Postby rgansevles » Sun Jul 14, 2013 1:15 pm

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
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1899
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL

Re: Query Builder Result Clause

Postby kwpsd » Mon Jul 22, 2013 5:06 am

Thanks for responding, Rob, and for the explanation.
Kim W. Premuda
San Diego, CA USA
User avatar
kwpsd
 
Posts: 635
Joined: Sat Jul 28, 2007 6:59 pm
Location: San Diego, CA USA


Return to Methods

Who is online

Users browsing this forum: No registered users and 1 guest