Can't Update MS SQL Table with Opto Data Link

PAC Project
1

I’m using ODL for the first time. I set up a test table in my MS SQL server with three columns as the docs say is necessary. I set up the link, tested the connection, and it all works.

I then set up a text box in Groov tied to an integer32 variable called nSendData so I can put the value 1 or 0 to trigger the event. I change the value to 1 and it triggers the INSERT command.

Here is what it looks like:

image
image.png704×951 34.7 KB

Doesn’t work. I get this error:

image
image.png1189×34 2.85 KB

Here is the structure of my table in the SQL Server
image

EDIT: So I fixed it. Here’s what was wrong:

The Date/TimeStamp field has to be either a text or datetime data type. I had it as date.

I labeled the index field “index” which must be a reserved field although SQL server didn’t bark at me. Changed it to “somekey” and presto! it worked.

The lesson to be learned here: RTDM (read the darn manual).

Thanks,

Dave

1 Like
2

Unfortunately I’ve found what appears to be a serious limitation for what I’m trying to to.

Here’s an example. I have a MS SQL server table with these records (forgive the NULLS, it’s a test db):

image
image.png743×121 8.27 KB

So now I want to update some other fields in this database within the existing records. Let’s say the Company field. I uncheck the box that says “Single Sample” which appends records and I get another set of records appended to my database AND my original HoursEach data is gone from the new records, not what I want. However, this is what the docs say will happen so now I know.

image
image.png815×220 14.7 KB

So I think my final option is to check the box that says “Single Sample” hoping that will update my existing records. But it wipes out everything and I’m back to square one:

image
image.png833×141 10.9 KB

So I ask you: How do you update an existing set of records using ODL? It seems like a fundamental feature of any sort of database tool. What am I missing?

Thanks,

Dave

3

My recommendation would be to have ODL write to a dedicated table and store your additional data in a separate table, then create a view to join these tables together to get your desired results.

Out of curiosity, are you giving up on the Node-Red and switching to ODL, or is this for something else?

4

That won’t work for me as I already have a dedicated schema that we paid to have designed and are going to stick to.

It was suggested I try ODL as it’s an off the shelf product and has Opto support. However, after spending several hours with it I find that it won’t do what I want (see above) and I also have connectivity problems. I set up a trigger to update the db but half the time I get a connection error even when the connection test is good. This happens even when nothing has changed. I found if I restart the ODL service that seems to provide a temporary fix but It makes me question its reliability for an automated system.

I’m back to NR for the moment. At least it’s the Devil I know…