How do you write a dataset to SQL?

Ignition
1

I have not had to write more than one row at a time to MSSQL from Ignition and I want to make this is done correctly.

I have a calculated dataset about 1,000 rows long that I want to write to SQL.

My columns are:
block_id
xVal
yVal

Do I have to iterate through the dataset writing one row at a time?

def writeRollData(dsReducedData,recordNumber): import system pyData = system.dataset.toPyDataSet(dsReducedData) block_id = system.tag.getTagValue("[PMIServer]GaugeBandTester/block_id") for row in pyData: xVal = row["distance"] yVal = row["height"] system.db.runPrepUpdate("INSERT INTO GuageBand_RollData (block_id, xVal, yVal) VALUES (?,?,?)", [block_id, xVal, yVal], "PMIServer") return

or can I pass the dataset to it like this?

def writeRollData(dsReducedData,recordNumber): import system pyData = system.dataset.toPyDataSet(dsReducedData) block_id = system.tag.getTagValue("[PMIServer]GaugeBandTester/block_id") newData= [] for row in pyData: xVal = row["distance"] yVal = row["height"] newData.append([block_id, xVal, yVal]) headers = ["block_id", "xVal", "yVal"] dsRollData = system.dataset.toDataSet(headers, newData) system.db.runPrepUpdate("INSERT INTO GuageBand_RollData (block_id, xVal, yVal) VALUES (?,?,?)", [dsRollData], "PMIServer") return

I welcome any other comments you may have.

Thanks,

2

You can’t pass in an array to a single SQL query. However, you can do something like this:[code]def writeRollData(dsReducedData,recordNumber):
import system
pyData = system.dataset.toPyDataSet(dsReducedData)
block_id = system.tag.getTagValue("[PMIServer]GaugeBandTester/block_id")

valueString = “”
args = []
for row in pyData:
xVal = row[“distance”]
yVal = row[“height”]
valueString += “(?,?,?),”
args.append(block_id)
args.append(xVal)
args.append(yVal)

if valueString != “”:
valueString[:-1]
system.db.runPrepUpdate(“INSERT INTO GuageBand_RollData (block_id, xVal, yVal) VALUES %s” % valueString, args, “PMIServer”)
return[/code]It builds one insert query that looks something like:INSERT INTO GaugeBand_RollData (block_id, xVal, yVal) VALUES (?,?,?), (?,?,?), (?,?,?).......You can also use transactions:[code]def writeRollData(dsReducedData,recordNumber):
import system
pyData = system.dataset.toPyDataSet(dsReducedData)
block_id = system.tag.getTagValue("[PMIServer]GaugeBandTester/block_id")
txId = system.db.beginTransaction(timeout=5000)
for row in pyData:
xVal = row[“distance”]
yVal = row[“height”]
system.db.runPrepUpdate(“INSERT INTO GuageBand_RollData (block_id, xVal, yVal) VALUES (?,?,?)”, [block_id, xVal, yVal], “PMIServer”, txId)

system.db.commitTransaction(txId)
system.db.closeTransaction(txId)
return[/code]The first one is more efficient because it only calls one system.db function. Each function call uses around 80 ms to talk to the gateway. The second one uses more calls so it will take longer.

3

That looks good, I will try it.

I have another function that writes these same values to the PLC. I build the lists then use:

system.opc.writeValues(opcServer,rollItemPaths,rollValues)

It takes about 10 seconds to write all of the values. Now that I will be writing this to SQL, will I get better performance by waiting until the data is written to SQL and then use a block write to the PLC from a transaction group?

4

Yes, you would. You get better performance when you have less function calls from the client to the server. Transaction groups run on the server so they are always fast.

5

It looks like I am trying to pass too much data in one call. Any suggestions on how to get around this without having to make 1000 calls? Would it be advisable to somehow split this into several chunks of data?

[quote]The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100.
[/quote]

6

If there is a max you can’t get all of it into a single call. I would have a counter that maxes out at 2100 and runs the query, resets and goes for another 2100. So if you have 4201 you will get three calls. That is much better than 4201.