Skip to Content
avatar image
Former Member

How can I set a where clause for getting the date dynamically in XSJS ?

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Nov 29, 2016 at 05:30 AM

    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);
    Add comment
    10|10000 characters needed characters exceeded