Query Builder .between() Usage

Classic Servoy
1

Servoy 8.2.1

How do you convert the following simplified SQL statement into Query Builder code using the .between() function?

SELECT date_start, date_end
FROM table
WHERE ? BETWEEN date_start AND date_end

var args = [ dateTest ]

We tried the QB code below, but a warning is emitted.

var query = datasources.db.database.table.createSelect()

query.params[ 'dateTest' ] = dateTest

query.where.add( query.getParameter( 'dateTest' ).between( query.columns.date_start, query.columns.date_end ) )

The method between() is undefined for the type QBParameterdb:/database/table

Any ideas?

2

Hi Kim,

This is an example:
var query = datasources.db.example.orders.createSelect();
query.result.addPk();
query.where.add(query.columns.date_order.between(date_start,date_end))

I recommend you watch this video: https://www.youtube.com/watch?v=WxPqhJZtr4A . It is about servoy QB.

Best,
Mina

3

Hi, Mina.

Thank you for responding.

I probably did not make myself clear in describing the problem. dateTest is a Javascript variable, not a table column name. dateStart and dateEnd are table column names. I want to select all records in the table where dateTest falls between dateStart and dateEnd. The SQL where clause works…I am trying to convert the SQL to a QB object using the .between() function. I am guessing that what I am attempting is not supported in QB, but thought I would post to see if someone had a solution.

4

The query build between function asks whether a column’s value is between two given values, so not what you want here.

Why not simply rephrase your query to

SELECT date_start, date_end
FROM table
WHERE date_start <= ? AND date_end >= ?

that one is easily doable with QueryBuilder then.

5

However, you can’t pass (SQL) object names as prepared statement parameters.
But I think, since you would use some (solution) code logic anyway to change your SQL to use the specific column name you can do the same to pass the proper QB column object to your QB Select.
Or am I missing something?

6

Kim,

This should give the same result:

var query = datasources.db.database.table.createSelect()

query.where
   .add(query.columns.date_start.le(query.getParameter( 'dateTest' ))
   .add(query.columns.date_end.ge(query.getParameter( 'dateTest' ))

query.params[ 'dateTest' ] = dateTest

Note that you have to set the parameter value after defining the parameter in the query, not before.

Rob

7

Thank you all for responding…I really appreciate your feedback!

…asks whether a column’s value is between two given values…

Too bad QB won’t take an external value like in my original SQL where clause. The only reason I tried to make dateTest a QB parameter was to use the QB .between() function, but it appears the function is not supported for parameters. For the alternate query, I think I would just skip the parameter business and pass the variable directly:

query.where
   .add( query.columns.date_start.le( dateTest )
   .add( query.columns.date_end.ge( dateTest )

Note that you have to set the parameter value after defining the parameter in the query, not before.

What is the reason for the precedence? I thought you could manipulate the query object in any way until it is executed (e.g. .getDataSetByQuery( query, -1 )).

8

What is the reason for the precedence? I thought you could manipulate the query object in any way until it is executed (e.g. .getDataSetByQuery( query, -1 )).

query.params[ 'dateTest' ] = dateTest

Looks for a parameter with that name and sets is value, it does not create it.
I guess we could support it the other order as well if people would need that.

Rob

9

I would like to see this implemented for the QB between function…it makes the code simpler and cleaner, and I believe it would be useful for other developers. Of course, this is just my opinion.