Error while inserting data to MSSQL

Ignition
, ,
1

Hi,

I am trying to insert data using an script in Vision, using preview mode I can successfully run the script and I can insert data to my MSSQL server.

Here is my code:

check_exist_query = "SELECT 1 FROM Users WHERE First_Name = ? AND Last_Name = ?"
	check_exist_args = [firstName, lastName]
	
	#Check if record exist
	record_exist = system.db.runScalarPrepQuery(check_exist_query, check_exist_args)
	
	if record_exist == None:
		insert_query = "INSERT INTO Users (First_Name, Last_Name, Age, Gender, Address) VALUES (?,?,?,?,?)"
		insert_args = [firstName, lastName, age, gender, address]
		print insert_args
		system.db.runPrepUpdate(insert_query, insert_args)
		
		system.gui.messageBox("Succesully Added!", "Success!")
	else:
		system.gui.messageBox("The name already exist!", "Failed!")

image
image667×537 30.7 KB

As seen above on my screenshot, I can successfully insert data to my MSSQL, however using the "Launch Project (F10 or F11)" I get an error:

image
image971×86 12.6 KB

Seems like the error is related to "Roles", but I already logged-in as administrator user, It's the same user when I logged-on my designer, so I don't know if there are still limitations as an administrator.

Hope you can help me.

Regards,

2

Go to your project properties and look at Vision->Permissions and see what Legacy Database Access is set to -
image

3

I get it now from this link:

Designer authentication settings is different from client/vision.

Thanks and regards

1 Like
4

FWIW Legacy DB Access can be a security hole. Your queries in your script don't look too complicated and it's good to use the prep version, but you can convert them to named queries and then turn off this permission to minimize risk. Not to mention named queries provide one source for all your CRUD (Create, Read, Update, Delete) operations which helps you not have to re-write the same queries in multiple places throughout your application.

3 Likes
5

What Brian said.

Don't turn on Legacy Database permissions for new projects. It is called "Legacy" because it exists to accommodate upgrades from Ignition versions before Named Queries existed.

Learn to use Named Queries. If you have an odd circumstance that an NQ cannot handle, put the "Prep" query in a project library script, call it from a gateway message handler, and use system.util.sendRequest() to call it from client scope. If you do this latter, and any dynamic SQL is constructed, be particular about excluding client-supplied strings, or the security hole opens back up.

1 Like
6

Thanks for the tips! I have a long way to go to learnrn the basics of Ignition platform.

Regards,

1 Like
7

You got it. There is https://inductiveuniversity.com/ if you haven't checked it out yet to learn all about it. I would also say pay this forum a visit when you get stuck or just randomly throughout the week during downtime (if you have any) and you can learn a lot regarding best practices and techniques from the very knowledgeable users here.

1 Like