autoComplete question. I am trying to get an autoComplete statement t

Keith over 9 years ago

autoComplete question.

I am trying to get an autoComplete statement to work and Appian is throwing an error.

I am wondering if the predefined datastore has to have a name longer than three letters.
I looked at documentation and could not see anything refer to the name size being an issue.
If I chose a different database and table, I can get it to work. This is a view and I am not able to get it to work.

I can query view SQL Server 2008 without and issue and also with a query database node in Appian it will work.

var query = "Select applicationname from vw_active_application where applicationname not in ('zOther','DBA','') order by applicationname”;
FormAPI.attachAutocompleteFromQuery("appName”,"jdbc/DDS", query,"applicationname", " applicationname",false);

I even tried it without the where and order clause and it will not work.

Any thoughts?

...

Discussion posts and replies are publicly visible

Parents

0 Eduardo Fuentes
Appian Employee
over 9 years ago

Here's an example:

var query = "SELECT applicationname FROM vw_active_application WHERE applicationname NOT IN (\\"\\'zOther\\'\\",\\"\\'DBA\\'\\",\\"\\'\\'\\") order by applicationname";

my main concern is that since this string eventually gets passed to the EvaluateServerExpression servlet this servlet replaces all single quotes with double quotes which means the query will look like this

ERROR com.appiancorp.database.ExecuteQuery - Using query: SELECT applicationname FROM vw_active_application WHERE applicationname NOT IN ("zOther","DBA","") order by applicationname

but in SQL Server "" are used for identifiers, not for strings. Strings need to be wrapped in single quotes.

I still suggest you try this query and check the application server log for both these queries:

var query = "SELECT applicationname FROM vw_active_application WHERE applicationname NOT IN (\\"\\'zOther\\'\\",\\"\\'DBA\\'\\",\\"\\'\\'\\") order by applicationname";

var query = "SELECT applicationname FROM vw_active_application WHERE applicationname NOT IN (\\"\\'zOther\\'\\",\\"\\'DBA\\'\\") order by applicationname";
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Reply

0 Eduardo Fuentes
Appian Employee
over 9 years ago

Here's an example:

var query = "SELECT applicationname FROM vw_active_application WHERE applicationname NOT IN (\\"\\'zOther\\'\\",\\"\\'DBA\\'\\",\\"\\'\\'\\") order by applicationname";

my main concern is that since this string eventually gets passed to the EvaluateServerExpression servlet this servlet replaces all single quotes with double quotes which means the query will look like this

ERROR com.appiancorp.database.ExecuteQuery - Using query: SELECT applicationname FROM vw_active_application WHERE applicationname NOT IN ("zOther","DBA","") order by applicationname

but in SQL Server "" are used for identifiers, not for strings. Strings need to be wrapped in single quotes.

I still suggest you try this query and check the application server log for both these queries:

var query = "SELECT applicationname FROM vw_active_application WHERE applicationname NOT IN (\\"\\'zOther\\'\\",\\"\\'DBA\\'\\",\\"\\'\\'\\") order by applicationname";

var query = "SELECT applicationname FROM vw_active_application WHERE applicationname NOT IN (\\"\\'zOther\\'\\",\\"\\'DBA\\'\\") order by applicationname";
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Children

No Data