1

passing column name/table names as parameters to xsjs

Feb 07, 2017 at 09:47 PM

101

avatar image

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!

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Florian Pfeffer
Feb 08, 2017 at 07:31 AM
1

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

Show 3 Share
10 |10000 characters needed characters left characters exceeded

Thank you so much for your speedy answer Florian! It works like a charm! So I went ahead and test CONTAINS_TABLE_NAME as well. They are exactly what i need.

How do we find such information though, such as guide, documentation? I would never have known 'CONTAINS_COLUMN_NAME' if I didn't post a questions here. Googling 'CONTAINS_COLUMN_NAME' barely gives me any relevant information.

Best Regards,

Violet

0

It is just a random name I used for the replacement with pure JavaScript, so no special thing for which you will find any documentation (except the JS string replace function which is not specific for that case) :-).

Regards,
Florian

0

Ha I see what you mean now. Thanks again!

0
Skip to Content