avatar image
Former Member

passing column name/table names as parameters to xsjs

Hi experts,

I'm trying to pass the field names and table names as parameters to xsjs.

	var querystr = $.request.parameters.get("query");
	var field = $.request.parameters.get("field");

..

query = 'SELECT TO_INT(SCORE()*100)/100 AS SCORE, * FROM "xxx::xxx" WHERE CONTAINS(?,?,FUZZY( 0.6 , \'similarCalculationMode=symmetricsearch\')) ORDER BY score DESC';
rs = conn.executeQuery(query, field, querystr);

when I run the xsjs service

.xsjs?cmd=filter&query=mo&field=NAME1

I'm getting this error:

dberror($.hdb.Connection.executeQuery): 8 - SQL error, server error code: 8. invalid argument: The first argument should be a column.

when I hardcode the column name in the CONTAINS() , it works.

It seems to be the same kind of problem with passing column name as a parameter in the stored procedure where you have to use dynamic sql.

Any idea how should I overcome this?

Appreciate your help!

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Feb 08, 2017 at 07:31 AM

    Column names cannot be set as a query argument.

    You either have to build your query statement using string concatenation or a replacement mechanism (considering SQL Injections).

    For instance with a replacement mechanism, the query is defined with a constant for the contains column name (CONTAINS_COLUMN_NAME) which is replaced with the column name before the query is executed:

    ...
    
    var query = 'SELECT TO_INT(SCORE()*100)/100 AS SCORE, * FROM "xxx::xxx" WHERE CONTAINS("CONTAINS_COLUMN_NAME",?,FUZZY( 0.6 , \'similarCalculationMode=symmetricsearch\')) ORDER BY score DESC';
    quey = query.replace('CONTAINS_COLUMN_NAME', field);
    conn.executeQuery(query, querystr); ...

    Regards,
    Florian

    Add comment
    10|10000 characters needed characters exceeded

Skip to Content