SQL Bridge Module Feature Request

After using the SQL Bridge Module for a few different projects, I’ve got a few request that would be very helpful to the way we use this module.

1. Have a setting to return a 0 for “null” values from SQL
Currently we use triggered expressions such as SELECT ISNULL(MAX(SomeValue),0) FROM SomeTable WHERE SerialNumber = ‘{[.]SerialNumber}’ to accomplish this.

2. Under Table action offer a selection under the update that will insert if the custom Where fails
Now we have to have 3 transactions to do this. We check with one and based on the check either chose an insert or update.

3. The custom index column is unchecked but we still get a “Columns missing from table, and ‘create table’ option is not enabled. Missing columns”[sometable_ndx]"
A minor complaint, but sometimes throws new users off.

4. Incorrect values shown when Update mode set to DB to OPC
On occasion we see string values shown incorrectly, usually a single digit instead of the full string for values returned from SQL. The PLC will have the correct value and everything works, just the group values are incorrectly displayed

5. Show the full tag path in the group item name as a default
This applies mainly to Allen Bradley strings, but I would like to see the full tag path for the default item name. So instead of STRING we would see Station_20.Serial_Number.STRING. This would also be helpful in block transfers or any time your have program tags or Data Type tags. Currently we have to take the time to copy from the path and paste into the name for each tag.

Just a few comments from another user’s point of view:

1. Have a setting to return a 0 for “null” values from SQL
I would put this type of operation in a stored procedure. Having various return types overridden sounds like it might be even more confusing and down the road and adds the possibility of biting me in the butt.

2. Under Table action offer a selection under the update that will insert if the custom Where fails
Again, sounds like a good use for a stored procedure.

3. The custom index column is unchecked but we still get a “Columns missing from table, and ‘create table’ option is not enabled. Missing columns”[sometable_ndx]"
Perhaps a little UX magic could make this more evident or clear. Maybe something to make the workflow a little more intuitive.

4. Incorrect values shown when Update mode set to DB to OPC
Sounds like something with the encoding in the UI. Localization changes?

5. Show the full tag path in the group item name as a default
Fully support this change.

Let me add a little more to #1

Suppose we have a DB to OPC standard group where we are updating some DINTs and BOOL data types. If we already have values stored in the tags a returned null value will not update the values and the transaction completed correctly.

[quote=“JamesPresley”]Let me add a little more to #1

Suppose we have a DB to OPC standard group where we are updating some DINTs and BOOL data types. If we already have values stored in the tags a returned null value will not update the values and the transaction completed correctly.[/quote]

Right, but perhaps someone has a usage scenario in which sometimes they want to return nulls on purpose for just that reason. If you want to handle null values in a different way that seems like it depends on your workflow and process and not how a transaction group should work.

A more eloquent solution might be to put a default value for a column definition in the transaction groups.

[quote=“Greg.Buehler”][quote=“JamesPresley”]Let me add a little more to #1

Suppose we have a DB to OPC standard group where we are updating some DINTs and BOOL data types. If we already have values stored in the tags a returned null value will not update the values and the transaction completed correctly.[/quote]

Right, but perhaps someone has a usage scenario in which sometimes they want to return nulls on purpose for just that reason. If you want to handle null values in a different way that seems like it depends on your workflow and process and not how a transaction group should work.

A more eloquent solution might be to put a default value for a column definition in the transaction groups.[/quote]

I agree. I’ve had situations come up where a returned null value worked nicely thats why I would like to see this as a setting. Maybe a check box with numerical input data field for the default value for SQL returned null.

Re 2:
with mySQL:
INSERT … ON DUPLICATE KEY UPDATE

For other DBs this page has some good information

Most of those are fairly easy to deal with, beside perhaps #2, which might be part of a bigger upgrade. For #1, I agree with the suggestion that perhaps the better route would be to allow the specification of default values for the columns created by the group.

#2 is something that’s requested a lot, with a few different variations: insert when there’s a new key, insert and then start updating hour meters, etc. When we get around to working on the SB again, this will be one of the first things tackled.

3 & 4 seem like minor bugs. For #3, I assume your table is, in fact, missing the index column, but since the “create table” isn’t enabled, you wouldn’t expect to see it, correct? Fundamentally, the groups really expect to have an index- did you not set the custom column because your index isn’t a number? (That’s the other problem, it expects the index to be a number. This is something else we’ll change)

Is there any pattern to the types of values that are shown incorrectly for #4? If it helps to know, all of the values shown are those read back from the plc, not the database.

And then, I’m sure #5 can be handled one way or another.

Thanks for the feedback,

Most of the time I see this is when I have only multiple strings setup DB to OPC. A lot of times they are numerical values in string format.

[quote=“Colby.Clegg”]Most of those are fairly easy to deal with, beside perhaps #2, which might be part of a bigger upgrade. For #1, I agree with the suggestion that perhaps the better route would be to allow the specification of default values for the columns created by the group.

#2 is something that’s requested a lot, with a few different variations: insert when there’s a new key, insert and then start updating hour meters, etc. When we get around to working on the SB again, this will be one of the first things tackled.
[/quote]

Item #2 Is exactly what I am dealing with in my current project and I foresee it coming up in the near future on other projects. Is there now a recommended method for dealing with this?

Thanks