Can’t figure this out. Have an application that was built around MySQL. Have to convert to using SQL Server. Converting all queries that have compatibility issues but have this Transaction Group update has me stumped.
Using Ignition V7.5.5 with SQL Server 2005 Express
When I trigger the transaction, this error shows up:
but if I use the DB Query Browser and duplicate the query, it runs without error.
What happens if you throw
ID into brackets?
[ID]
While I am not sure how to fix this in a transaction group…
The error is caused by the order clause in the update query without a top ().
So you would have to create a subquery with the Order.
Also, you can’t choose TOP 1 *. It would have to be a column like TOP 1 ID
UPDATE cipruns SET <column> = ??? WHERE id in (SELECT TOP 1 ID FROM cipruns WHERE SystemID = 0 and StopTime IS NULL ORDER BY ID DESC)So you could try in the transaction group, but I am not certain it will work.
id in (SELECT TOP 1 ID FROM cipruns WHERE SystemID = 0 and StopTime IS NULL ORDER BY ID DESC)Hope that helps!
Cheers,
Chris
thanks for that, i will give it a try later today.
Also am thinking of trying the OFFSET and FETCH NEXT , as both of these statements are at the end of the WHERE clause in the transaction group. That is the problem, the elect/Update has no place for the TOP 1 to be inserted and the syntax of the SQL Server query does not recongize LIMIT 1 which was at the end of the original MySQL where clause.
I too have fond memories of being able to use LIMIT…
I am not sure how to use the FETCH NEXT or OFFSET in the transaction group as where do you put it?
Issues I see:
Code would look something like this:
SELECT * FROM cipruns WHERE SystemID = 0 and StopTime IS NULL ORDER BY ID DESC FETCH NEXT 1 ROWS ONLYor
SELECT * FROM cipruns WHERE SystemID = 0 and StopTime IS NULL ORDER BY ID DESC OFFSET 10 ROWS FETCH NEXT 1 ROWS ONLYIf you get it to work thou, can you post it?
Cheers,
Chris
[quote=“CPowell”]I too have fond memories of being able to use LIMIT…
I am not sure how to use the FETCH NEXT or OFFSET in the transaction group as where do you put it?
Issues I see:
Code would look something like this:
SELECT * FROM cipruns WHERE SystemID = 0 and StopTime IS NULL ORDER BY ID DESC FETCH NEXT 1 ROWS ONLYor
SELECT * FROM cipruns WHERE SystemID = 0 and StopTime IS NULL ORDER BY ID DESC OFFSET 10 ROWS FETCH NEXT 1 ROWS ONLYIf you get it to work thou, can you post it?
Cheers,
Chris[/quote]
Thanks for that, but right now I am battling getting the syntax correct for SQL Server 2005 Express. It does not like the OFFSET no matter what I do, and using even documnetation examples.
Query using Management Sudio Express:
[quote=“CPowell”]While I am not sure how to fix this in a transaction group…
The error is caused by the order clause in the update query without a top ().
So you would have to create a subquery with the Order.
Also, you can’t choose TOP 1 *. It would have to be a column like TOP 1 ID
UPDATE cipruns SET <column> = ??? WHERE id in (SELECT TOP 1 ID FROM cipruns WHERE SystemID = 0 and StopTime IS NULL ORDER BY ID DESC)So you could try in the transaction group, but I am not certain it will work.
id in (SELECT TOP 1 ID FROM cipruns WHERE SystemID = 0 and StopTime IS NULL ORDER BY ID DESC)Hope that helps!
Cheers,
Chris[/quote]
Chris, tried that and it appears the TOP 1 is not executed with the transaction group query, following error shows up:
when I exceute the query within Query Browser , it returns only 1 record:
Okay,
now I am really confused. In ensured the Transaction Group query should only return 1 record, by using a search of the Primary key ID , and I still get the multiple row error from the transaction group:
Just to be sure, ran same query in Browser
When I switch the database connection back to MySQL, the transaction trigger works without any errors using the same query.
One of the issues here that you have the StopTime column set as both the timestamp column as well as a write target for one of your items. If you just uncheck the custom timestamp option then that error message should go away. As for using an Order By in the custom where clause area, we are looking into that issue and will post back when we figure out why this isn’t working with SQL Server.
Dave,
thanks, but as previously posted, this timestamp write works when the database is MySQL. Only when it is SQL Server does this duplicate error occur.
Originally this application was developed for us by Travis and this is the way it has been coded and working successfully for 3 years in MySQL applications.
Yes, because SQL Server doesn't let you put the same column multiple times in a query, while apparently MySQL does.
Different databases act differently. The important thing, though, is that while it wasn't previously erroring out, it still wasn't exactly "working correctly", since you had (or perhaps, Travis had) two different sources of data (the item, and the group's timestamp) tied to the same column. Only one of these values was making it in. Switching the backend db has only brought to light this flaw, so you just need to decide which one is correct. Maybe the timestamp field is set incorrectly, and there's a different column available for the group timestamp? Otherwise, I would guess that you should probably disable the "store timestamp" option, since I imagine the expression item is mapped for a reason.
Regards,
Thanks Colby.
I am beginning to think then I may need to use scripting to insert the Stoptime into the CIPRuns database rather than a transaction group due to the limitations of this particular transaction group when the DB is SQL Server.
Hi,
I’m not sure that’s true, this group was working fine before, right?
A couple of your possible solutions above could work fine. Any time you were running into the “Column name ‘StopTime’…” errors, it had nothing to do with the various solutions you were trying, only that the column was in use twice in the group. If you fix that, you should be fine.
In your last screenshot, you have an expression item named “CIPRunID”, which I assume is running the SQL query “SELECT TOP 1 CIPRunID FROM cipruns WHERE stoptime is null ORDER BY id DESC”. Then you’re using it directly in the where clause. This is exactly what I would have suggested had I been quicker to get on this thread. As far as I can tell, you only need to do two thing:
Regards,
[quote=“Colby.Clegg”]Hi,
I’m not sure that’s true, this group was working fine before, right?
A couple of your possible solutions above could work fine. Any time you were running into the “Column name ‘StopTime’…” errors, it had nothing to do with the various solutions you were trying, only that the column was in use twice in the group. If you fix that, you should be fine.
In your last screenshot, you have an expression item named “CIPRunID”, which I assume is running the SQL query “SELECT TOP 1 CIPRunID FROM cipruns WHERE stoptime is null ORDER BY id DESC”. Then you’re using it directly in the where clause. This is exactly what I would have suggested had I been quicker to get on this thread. As far as I can tell, you only need to do two thing:
Regards,[/quote]
Thanks Colby,
I will try this next week in my office after I return from out of town.
[quote=“Curlyandshemp”][quote=“Colby.Clegg”]Hi,
I’m not sure that’s true, this group was working fine before, right?
A couple of your possible solutions above could work fine. Any time you were running into the “Column name ‘StopTime’…” errors, it had nothing to do with the various solutions you were trying, only that the column was in use twice in the group. If you fix that, you should be fine.
In your last screenshot, you have an expression item named “CIPRunID”, which I assume is running the SQL query “SELECT TOP 1 CIPRunID FROM cipruns WHERE stoptime is null ORDER BY id DESC”. Then you’re using it directly in the where clause. This is exactly what I would have suggested had I been quicker to get on this thread. As far as I can tell, you only need to do two thing:
Regards,[/quote]
Thanks Colby,
I will try this next week in my office after I return from out of town.[/quote]
Colby,
that worked, thank you.
Biggest issue I am having converting a MySQL based application to a SQL Server application is the use of quotations. MSSQL has a definite purpose for a single ’ and a " . Once I discovered that, conversion became easier.
