Perspective table Query binding MSSQL error

Ignition
1

I created a named query and when I run it in the testing area it gives me back the data correctly.
When I want to bind it to a table in perspective it says there is an error next to “FROM” but I don’t understand how there is an error if it works correctly in the named query.

image
image790×1238 45 KB

2

Regardless of the inconsistency, that error is coming from MSSQL/their driver, not Ignition code.

3

So just if somebody comes around this again. The connection to the datasource was modified with the gateway open. When I closed it and open it again the error was gone, hence the inconsistency.

4

Hi,
I am binding named query in table. showing error.
In query I have used temp tables. The query is working fine in SQL SERVER and getting results also. but in ignition I am getting error.
but testing in named query getting values.

image
image1546×530 29 KB

image
image1152×887 79.8 KB

second point is , I am able to bind other named query.

image
image930×621 51.3 KB

So, where is the problem I am not getting.

Please help

5

after edited,
parameters value set to 1, I am getting the results.

how it differ, I didn't understand.

image
image927×627 49.3 KB

6

  1. You have to provide values for named query parameters. If you don't, they are delivered as NULLs, which screws up many queries.

  2. You are using SQL scripts, not SQL statements, so your work will not be portable and will be subject to changes in IA's implementation. JDBC, which IA leverages, doesn't support scripts, just single statements. Some JDBC drivers, like Microsofts, allow them, but IA's implementation has some gotchas. If you need scripts to run in the DB, you should be creating stored procedures.

{Testing in SQL Server Management Studio is not sufficient to know if a query should work through JDBC, since SSMS doesn't use JDBC.}