Looking for some idea’s on this and i am New to Ignition.
One of the Ask is, users want to Upload a sheet that contains around 70 columns of data into SQL Server DB through Ignition, which i made a code, but my question is “How can i Validate the Data before i Insert the Data into Base Tables.”
Well, when you open the file, you’ll have to loop through the rows extracting the content from the CSV. Validate those rows as you read them in, inside this loop.
If the rows are independent of each other, you can use runPrepUpdate() to insert each row after validation.
If you need to make sure they are all ok before inserting any of them, you’ll store your validated rows in a temporary (in memory) list during the read loop, throwing some error if any row fails validation. If they all pass, you’ll use a second loop to read the list of rows and insert them into your DB.
Not particularly. It’ll almost certainly be limited by your DB speed, not jython. Or possibly limited by memory capacity if the CSV files are very large.
I would construct a window/view to show progress. That window/view could have a text area to which you can write any validation failures. Or you could make the process two steps: read and display in a table, highlighting failures, then a separate button to send to the DB.
FWIW, I wouldn’t bother counting the rows separately. Just read them in, validating and counting as you go, until end of file.
Strictly speaking, Jython is python. Just a different implementation. The most common python is CPython, which is a native implementation in numerous operating systems. Jython is an implementation that runs inside a Java Virtual Machine–exactly what Ignition needs.
You could do this process in CPython, as it supports CSV reading and DB operations, but it would run outside Ignition, and you’d need to use a CPython-compatible user interface technology. If you want to leverage Ignition’s user interface and platform resources, it has to be the jython flavor of python.
Hello @Matrix_Engineering, i was able to Import data and Insert into SQL Server table without any issue’s, but the Major thing i was after is “How to do data validations (Either at application level or at DB Level)”.
At app level as mentioned by @pturmel he suggested to use the Jython.
But at DB Level, even if i do Validations, by inserting data in a Temp Table before i load into Actual base tables, i am not quiet sure how to bring the Validation errors into Ignition screen from SQL Server.(May be i think i can do a Check constraint at DB Level and show the error, using default, but the thing is if CSV File is having 100 records and 70 columns, if first record is having error, the entire upload gets cancelled.)
for x in range(0,numRows):
if rowData == validationChecks:
append to dbTempTableWrite
elif rowData != validationChecks:
append to errorLog
system.db.suitableFunction ("insert dbTempTableWrite into foo")
system.db.suitableFunction ("insert errorLog into foo_fighters")
Phil will be along in a minute to rip my pseudo code to shreds