Insert Dataset into SQL Table

Ignition
1

So, I’ve searched high and low for the right code to complete this task…I’ve got a feeling I’m close, but I’m still missing something. Still a fresh fish when it comes to scripting. Here’s the situation…I’m gathering information from one dataset ‘ClassRoster’ and using one it to generate another dataset for the SQL insert statement. The second dataset gets created correctly from the code included, but what does the SQL Insert code look like to finish the job? I’m trying to insert the 2nd dataset ‘oneRow’ into a SQL table. Thanks for any help!

[code]data = event.source.parent.getComponent(‘ClassRoster’).data
table = event.source.parent.getComponent(‘Table’)

rows = []
headers = [“EmployeeID”,“WstrnGroup”,“TrngGroupName”,“RecordDate”,“TrngChecklistCompLink”,“LockUser”,“InactiveFlag”,“TrngSkillLevel”,“DeptNum”]
for row in range(data.rowCount):
oneRow = [data.getValueAt(row, “EmployeeID”),event.source.parent.getComponent(‘WstrnGroup’).selectedStringValue,event.source.parent.getComponent(‘TrngGroupName’).selectedStringValue,event.source.parent.getComponent(‘ClassDate’).date,event.source.parent.getComponent(‘LabelLinkPath’).text,“NO LOCK”,0,event.source.parent.getComponent(‘SkillLevel’).selectedValue,event.source.parent.getComponent(‘Comments’).text]

system.db.runPrepUpdate(“INSERT INTO TrngRecords (oneRow) VALUES (?,?,?,?,?,?,?,?,?)”, [EmployeeID, TrngGroupName, WstrnGroup, RecordDate, TrngSkillLevel, TrngChecklistCompLink, DeptNum, LockUser, InactiveFlag])

rows.append(oneRow)

data = system.dataset.toDataSet(headers,rows)
table.data = data[/code]

2

I have to do something similar… I needed to get all the data from a foxpro database and insert all of the records into a SQL table This is how I did it.

[code]

DATA INITIALIZATION

Run once to initially populate the data in the SQL Database

first we get all of the LIMS data with a query to the LIMS database to return all records into a client dataset tag

LIMSData = system.tag.read(“MOS1B Fractionator/Raw Test Data From LIMS”).value
pdsLIMSData = system.dataset.toPyDataSet(LIMSData)

iterate through the entire dataset and pull out each individual row of data. Write the row data to the MS SQL Database

for index in range(len(pdsLIMSData)):
LIMSrowData = list(pdsLIMSData[index])

system.db.runPrepUpdate("INSERT INTO MOS1B_Fractionator_Raw_LIMS_Data (UniqueID, Barcode, Test_Date, Test_Time, Mat_Code, Mat_Descr, Test_Code, Test_Descr, Results) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", LIMSrowData, "Inductive_Automation_Ignition_New")[/code]
3

cbabb…thanks…works like a charm

4

The Power Scripting Module provides a function for inserting a dataset into a database table.

It could be done like this:

table = event.source.parent.getComponent('Table') pa.db.insertRows("MOS1B_Fractionator_Raw_LIMS_Data", table.data)
Check it out: doc.perfectabstractions.com/modu … insertrows

Best,

2 Likes
5

I want to use the data in my table and then insert the rows but trigger based on time. I can use a gateway time based script to call my script. My table is populated from a dataSet tag, should i use that as my source for pa.db.insertRows ?

6

to answer my own question

dataSet = system.tag.getTagValue("tagpath")
mpds = pa.dataset.toData(dataSet)


pa.db.insertRows("tableName",mpds)
7

Yep, looks good.

tttttttttttttttttttttttt