Issue with vision scripting and SQL queries

Hello,
I am trying to insert data into the table and there is user_id in the table. I am selecting username from dropdown and the id of that username should be entered into the table but when inserting, the user_id is entered as 0

sensor_name = event.source.parent.getComponent('Text Field').text
sensor_description = event.source.parent.getComponent('Text Field 1').text
selected_user = event.source.parent.getComponent('Dropdown').selectedValue
selected_station = event.source.parent.getComponent('Dropdown 1').selectedValue
assign_time = event.source.parent.getComponent('Popup Calendar').date

# Query to retrieve the user_id based on the selected username
user_query = "SELECT s.sensor_id, s.sensor_name, s.sensor_description, guu.id AS user_id, s.station_number, s.assign_date FROM sensors s JOIN users g ON s.user_id = g.id WHERE g.username = ?"
user_args = [selected_user]

# Execute the query to get the user_id
user_id_result = system.db.runPrepQuery(user_query, user_args, "Database")

# Check if a user_id was found
if user_id_result.getRowCount() > 0:
** user_id = user_id_result.getValueAt(0, "user_id")**
else:
** # Handle the case where the selected username was not found**
** user_id = None**

payload = [6, sensor_name, sensor_description, selected_user, selected_station, assign_time, None]
args = [sensor_name, sensor_description, selected_user, selected_station, assign_time]

#Adding to database
query = "INSERT INTO sensors(sensor_name,sensor_description,user_id,station_number,assign_date) VALUES (?,?,?,?,?)"
system.db.runPrepUpdate(query, args, "Database")
system.db.refresh(event.source.parent.getComponent('Table'), "data")

event.source.parent.getComponent('Text Field').text = None
event.source.parent.getComponent('Text Field 1').text = None
event.source.parent.getComponent('Dropdown').selectedValue = None
event.source.parent.getComponent('Dropdown 1').selectedValue = None
event.source.parent.getComponent('Popup Calendar').date = None

This is the script used.
It would be a great help if somebody could assist me with some help and thank you in advance

The error says - Problem running system.db.runPrepUpdate and that the current user does not have the required roles for this operation

You are probably lacking legacy DB permissions. You should not turn on legacy DB permissions, but convert to a named query.

2 Likes

Thanks! Appreciate it!