I am getting the attached error message when trying to use Query Database smart

erickp over 8 years ago

I am getting the attached error message when trying to use Query Database smart service. I am doing a simple select statement: "select univ_name from University where univ_id = 1. (It is my first time using Query Database smart service and I am just trying to execute a simple scenario so ignore how simple the query is and the fact I am using univ_id = 1) I have a CDT called University with both fields univ_id and univ_name. What does the attached error message means? Also, how would I go ahead if I wanted to store the returned univ_name in a process variable? Would I create an output? If yes, how do I tell that output that I want to store the SQL response in my process variable?

Discussion posts and replies are publicly visible

0 ChristineH
Certified Lead Developer
over 8 years ago

That error generally means when Appian is trying to validate the table and columns you are going to query via the data source that it can't find a column with that name.

If the query was "validated", you would be able to go to the setup tab and store the output into a pv.

Also, if you have a CDT, you may want to use a query rule or query entity instead so you are not writing the query or having to set the data source in the model after import/export if the data source name is different.

Hope that helps!
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 narasimhaadityac
A Score Level 2
over 8 years ago

Hi erickp, when i look at the image, your option indicates that you are using a pre-defined data source, but the option to select the data source name is not there (generally a dropdown will be shown as its present under data stores), in this case i think its not able to connect to the database server so its giving error.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 sandeepr343 over 8 years ago

erickp# narasimhaadityac is right..if you are using pre-defind data source then you need to select data source name from drop down.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 erickp over 8 years ago

the source name is specified. For some reason, when the error message appears, the drop down with the source name doesn't show up but it is there. Any other ideas of why I might be getting this error message?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 narasimhaadityac
A Score Level 2
over 8 years ago

@eric, Could you please provide the table specs, what is the column data type for the Univ_name ? Looks like the system is not able to cast / identify the data type.. Kindly let us know from the underlying db, what is the actual data type.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 erickp over 8 years ago

univ_name is of type Text. How do I make it so that the system can cast or identify univ_name?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 narasimhaadityac
A Score Level 2
over 8 years ago

@eric, I believe you are on SQL Server, as Text is a type defined for the SQL Server.

Please refer to "Primitive Data Types" under https://forum.appian.com/suite/help/7.7/Defining_a_Custom_Data_Type.html

by default the system would be able to translate the ones provided in the system, if there are any other data types which need to be configured then the way to do is through defining the CDT (through ColumnDefinition ) and using them in either Query Entity or Query Rules. hope this gives an answer why the above error is thrown.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 erickp over 8 years ago

That is not the problem. I am using MySQL. The type Text is of the CDT. At this point in time, don't have access to MySQL database. As soon as I get access, I will let you know. (The link you posted above I have seeing already and have the data types in mind)
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 erickp over 8 years ago

I am attaching the XSD of the University entity. Anyone has any clue why I am getting the error?
University.xsd
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel