Hello Team,
while executing a named query with postgre database. system showing below error msg:
GatewayException: org.postgresql.util.PSQLException: ERROR: syntax error at or near "LIMIT"
** Position: 2**
** caused by Exception: org.postgresql.util.PSQLException: ERROR: syntax error at or near "LIMIT"**
** Position: 2**
But after executing same named query in query browser showing a correct result. i have attached the screen shots for better understanding. Please help me out if anyone knows why the name query showing Limit error.
I suspect that the error is because you are running a second query (SELECT 1) in the named query. I don't think Ignition can handle that.
Is there any solution for select statement?
[Note: I thought I was responding to the OP. I wasn't.]
Delete it?
Why do you think you need it?
You have the Query Type set to Scalor. I suspect that it should be set to UpdateQuery and let it return the number of rows affected.
- UpdateQuery: Allows all UPDATE types of queries (querys that mutate or otherwise modify rows on a table) and returns the number of rows affected by the query. This type should be selected when modifying the database in some way, such as when running an INSERT, UPDATE, or DELETE query.
We are migrating MSSQL named query to PostgreSQL. There are stored procedures with select statement we are not able to migrate it.
This thread is about named queries - not stored procedures. You should start your own thread - and provide enough information that someone could possibly help you.
2 Likes
DO $$
DECLARE
isExist INT;
BEGIN
SELECT COUNT(*) INTO isExist FROM "Scrap" WHERE "ScrapName" = :ScrapName AND "EquipmentId" = :EquipmentId;
IF isExist <> 1 THEN
INSERT INTO Scrap("ScrapName", "Description", "EquipmentId", "ScrapTime", "IsFrozen", "CreatedBy")
VALUES (:ScrapName, :Description, :EquipmentId, :ScrapTime, :IsFrozen, :CreatedBy);
RETURN 1;
ELSE
RETURN 0;
END IF;
END $$;
This postgre query is not working in ignition named query.
Make the "ScrapName" and "EquipmentId" unique, then you can do a simple insert and it just won't work if it breaks the unique constraint.
And it will be more performant AND easier to understand/maintain.
I can use simple insert function but I need to use declare to check whether the current inserting data is already present in the Table. So that I used declare function but this query is working well in PG admin but not working in Ignition 8.1 and showing the Limit error.
But why do you want to check if it already exists ?
If it's just so you don't insert it again, a unique constraint of those 2 columns will do that for you. It's built-in, and it's what it's for.
If there are other reasons you need to know if it exists, explain them and we'll help you figure out a saner way to do it.
Also:
- Don't reuse threads for something unrelated. Make a new one, with an explicit title and appropriate tags.
- Use the preformatted text tool to post code:
Or manually wrap your code in triple backticks:
```
code
```
You can even add the language to add syntax highlighting:
```sql
sql code
```
1 Like
That isn't a query. That is a script. Vanilla JDBC doesn't support scripts, and they only work accidentally in Ignition (some JDBC drivers go beyond the specification).
For your stated purpose, you should be using PostgreSQL's ON CONFLICT... support. Superior to your script, as it is race-free.
1 Like
