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

There is no restriction in the length of the name of the data source, it seems you're running into what I explain here forum.appian.com/.../f-83518 meaning you will have to escape the quotes

Check the application server log, this plug-in logs information when this function errors out. Something like this:

09:36:41,117 INFO [stdout] (ajp-/0.0.0.0:8009-5) 2014-08-01 09:36:41,116 [ajp-/0.0.0.0:8009-5] ERROR com.appiancorp.database.ExecuteQuery - com.microsoft.sqlserver.jdbc.SQLServerException: XXXXXXXXXXXXXX

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";

Eduardo,
Thank you for your continued assistance.
I could not get the where clause to work. Nothing shows up in the log file either. I tried another query I had in a file and it worked. I tried the other data store and it did not work. I copied the one the worked in Appian and then edited the information to point to the other data store and other query and it then worked. So, the moral of the story here is the quotes must have been weird in this statement.
FormAPI.attachAutocompleteFromQuery("appName","jdbc/DDS", query,"applicationname", "applicationname", false);
This is the one that works.
I am glad it is working. The characters can drive you crazy.

Oh I forgot to mention that ... yes you original query had styled quotes...if you paste it in Notepad you will see that you had a different type of " closing the query and after the word appname in the Attach expression

I am struggling again... I tried various things... Without the where clause we get data. However we want to filter the data for a site. the data also has a slash in it and it looks like autoComplete without the where clause shows the result very differently.

For instance. 10.120.0.0/29 shows as 10.120.0.29

Here is my statement..
importScript('/plugins/servlet/FormsExt.js');
var query = "select StartingIP from SIP_Networks where SegmentNumber = \\"\\'kygra\\'\\" and DataCenter = \\"\\'center\\'\\" order by 1";
FormAPI.attachAutocompleteFromQuery("text2", "jdbc/2029_SQL_Inet_ECS", query, "StartingIP", "StartingIP", false);

this does not work - it resolves, but no data shows.

If I remove the where clause it then works.

Hi Eduardo,
We had a process using the "attachAutocompleteFromQuery" and it worked fine in 661. we have upgraded to 7.6 and its not working. the log says "ERROR com.appiancorp.database.ExecuteQuery - javax.naming.NameNotFoundException: java:jdbc/Appian_SoR
javax.naming.NameNotFoundException: java:jdbc/Appian_SoR".
I am able to use the same predefined data-source for querying the DB directly in query DB node and it works fine.

I also tried importing the sample app "Autocomplete Example" for fruits that you have posted in the link provided above. I am getting the same exception in logs for this as well. Although the tables are created and I can see the values inside tables, means the Data-source configuration is fine.

can you please assist in this ?

Maybe the JNDI name is now different after the upgrade. What's the exact name you see under Data Management - Data Stores - Create a Data Store - Choose a Data Source?

What's the name in custom.properties?

Thanks Eduardo,
I verified there is a "/" that's missed out in datasource name, "java:jdbc/Appian_SoR"
it should be "java:/jdbc/Appian_SoR".

my bad :)