SQL query problem

Classic Servoy
1

I want to find all records where the creation date is within the last 20 days.

This works:

var maxReturnedRows = 1000;
var today = new Date();
var days = null;
days = 20;
var query = "SELECT crid FROM cr WHERE creation_date > (? - 20)";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(),query,[today],maxReturnedRows);
controller.loadRecords(dataset);

And this doesn’t:

var maxReturnedRows = 1000;
var today = new Date();
var days = null;
days = 20;
var query = "SELECT crid FROM cr WHERE creation_date > (? - ?)";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(),query,[today, days],maxReturnedRows);
controller.loadRecords(dataset);

The only differences between the two versions is the use of a second variable for the number of days to subtract.

(? - 20) is replaced by (? - ?)

and

[today] is replaced by [today,days]

I’ve used this same syntax for two and more variables in other SQL queries before without problems. What am I missing here?

2

Morley:
I want to find all records where the creation date is within the last 20 days.

This works:

var maxReturnedRows = 1000;

var today = new Date();
var days = null;
days = 20;
var query = “SELECT crid FROM cr WHERE creation_date > (? - 20)”;
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(),query,[today],maxReturnedRows);
controller.loadRecords(dataset);



And this doesn't:

var maxReturnedRows = 1000;
var today = new Date();
var days = null;
days = 20;
var query = “SELECT crid FROM cr WHERE creation_date > (? - ?)”;
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(),query,[today, days],maxReturnedRows);
controller.loadRecords(dataset);


The only differences between the two versions is the use of a second variable for the number of days to subtract. 



> (? - 20) is replaced by (? - ?)

and

> [today] is replaced by [today,days]


I've used this same syntax for two and more variables in other SQL queries before without problems. What am I missing here?

I’m I wrong or you have to put the parameters in an array?

3

Riccardino:
I’m I wrong or you have to put the parameters in an array?

From memory, I learned this technique from Maarten. I’m successfully using it elsewhere.

4

Hello Morley,

that parameter is supposed to be an Array. So what you can write is

var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, new Array(today, days), maxReturnedRows);

But then I don’t quite see how

... WHERE create_date > (today - 20)

should give you all records where the creation date is “within the last 20 days”. Why not “within the last 20 seconds” or “within the last 20 years”?

5

patrick:
that parameter is supposed to be an Array. So what you can write is

var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, new Array(today, days), maxReturnedRows);

Doesn’t work. So far only hard coding the number of days to subtract works.

patrick:
But then I don’t quite see how

... WHERE create_date > (today - 20)
```should give you all records where the creation date is "within the last 20 days". Why not "within the last 20 seconds" or "within the last 20 years"?

That’s what I thought until I talked with a friend (not using Servoy) who said he uses it all the time. In my tests here it does indeed work – finds only records with a creation date within the past 20 days.

I’d really like a SQL date calculation which subtracts a variable number of days from today, one that does not depend on hard coding the days to be subtracted.

6

Morley,

I just tried

var c1 = 50000;
var c2 = 5;
var c3 = 100000;

var query = "SELECT id FROM table WHERE id >= (? - ?) and id <= (? + ?) ";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(),query, new Array(c3,c1,c1,c2), 10000);
var test = dataset.getAsText('\t', '\n', '\t', 1)
application.output(dataset.getAsText('\t', '\n', '\t', 1));

and that gives me exactly what I expected:

id	
50000	
50002	
50003	
50004	
50005

So this DOES work.

As I suggested before somewhere: try your SQL against your database directly- Once you have the right statement, put that into your method and replace constants by variables.

7

Thanks Patrick, that is helpful. I’ll chip away at this later this morning.