SQL Bridge Block Transaction to SQL View

Ignition
1

Morning All,
I have been trying in vain for an hour or 2 now to create an SQL Bridge block transaction to read from an SQL view. I cannot figure this one out as it looks like SQL bridge needs to write back to the view but cannot. Is this even possible as it was inferred to me on purchasing that this could be done and I can select the view and columns in the selector buttons on the right hand side.

I get the error Columns missing from table and 'create table' option is not available. Missing column is the ndx column.

Cheers,

Callum

2

You are trying to read from a view and use the SQL bridge module to put the data where? In to PLC tags?

You might be better off using some of the system.db | Ignition User Manual functions to make that happen.

3

You have to make sure everything in the transaction group has direction set to "DB => OPC".

Also note that you can only have triggers from the OPC/Ignition side, and you'll need to make sure they aren't tied to a DB column.

{ Please share your use-case. You may have received marginal advice. Everything the SQL Bridge can do can also be done with gateway event scripts, and some desirable combinations can only be done with scripts. The SQL Bridge module's primary purpose is to offer a drag-n-drop tool for those who do not want to write/maintain scripts. }

1 Like
4

Afternoon Both,
Thanks for your contributions. It is much appreciated!
I would prefer to stick with the drag and drop functionality as we use the SQL bridge to sent data to an Alan Bradley PLC, specifically a UDT tag.

I have setup 30 odd transactions so far without issue but these have all been from OPC > DB using Stored Procedures and Standard Groups.

This one however is to download our daily orders from an SQL view as the data comes from multiple tables.

At the moment I have it on a 5 second trigger to prove it but eventually we will be doing this once at 5AM every morning. Below is an image of the error I get and below that is the redacted SQL view.

Thanks,

Callum

image (5)
image (5)1918×1078 376 KB

PRDs
PRDs839×298 16.7 KB

5

Can you check the "use custom index column" and select the Item_No column? That might satisfy the requirement.

1 Like
6

In the "Table Action" section, you have "Insert new block" selected. Change that to "Update/Select". You may also need to use a WHERE clause.

7

Thank you both!
I believe it was a mixture of both answers that resolved this for me. B Schroeder's answer stopped the transaction from erroring and P Turmel's answer allowed the transaction to work.

Bit strange as the View already has a WHERE clause to present the data as required. All I had to do was a redundant "WHERE QTY_Blocks > 0" in Ignition and it started to work.

Cheers,

Callum