on 11-28-2016 12:45 PM
Hi ,
I am facing the issue of getting some data based on a date filter.
Hence, I need to provide the date in the where clause. I'v tried these till now ---
1. var query = 'SELECT DISTINCT \"StationId\",\"Status\" FROM \"***********************************************\" WHERE \"StartDate\" = \'CURRENT_DATE\'';
2. var query =
'SELECT DISTINCT \"StationId\",\"Status\" FROM \"***********************************************\" WHERE \"StartDate\" = \'GetDate()\'';
The other option was to write a new xsjs to get the date as output & modify it accordingly. I did that as well.
But, the problem lies in passing a variable within a where clause/ getting the date dynamically in where clause.
Please, suggest a solution.
Thanks a Lot,
Sudipta
Hi Sudipta,
As already mentioned I think you are better finding a new more relevant tag for the question.
My suggestion for the answer however is to use a prepared statement such as
select ? from TABLE where COLUMN_NAME = ?
pstmt.setString(1, select_column);
pstmt.setString(2, where_clause);
To set the date dynamically for the SQL a modified hello user type example below.
Regards
Robert
$.response.contentType = "text/html";
//var output = "Hello, " + $.session.getUsername() + new Date + " <br><br>";
var output = "Hello, " + $.session.getUsername() + " Full Date is " + new Date() + ' and the day of month is ' + new Date().getDate() + " <br><br>";
var conn = $.db.getConnection();
var pstmt;
var query = 'Select ? FROM DUMMY';
pstmt = conn.prepareStatement(query);
pstmt.setString(1, output);
var rs = pstmt.executeQuery();
if (!rs.next()) {
$.response.setBody( "Failed to retrieve data" );
$.response.status = $.net.http.INTERNAL_SERVER_ERROR;
}
else {
output = output + "This is the response from my SQL. The current user is: " + rs.getString(1);
}
rs.close();
pstmt.close();
conn.close();
$.response.setBody(output);
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.