Retrieve Value From SQL Query

Ignition
, ,
1

Hi All,
I’m trying to create a log of users that have entered inside a building (in Vision, with some success).

  1. created SQL database called ‘users’ in MySQL workbench (completed)
  2. created SQL database called 'building_log" in MySQL workbench (completed)
  3. add new users based on a unique user id (primary key), username, department (completed)
  4. delete users based from the user database based on user id (completed)

next steps that I am having trouble with:

  1. operator enters their user ID into a text box (completed)
  2. clicks Audit momentary pushbutton (completed)
  3. named query searches a pre-existing database for a user id match (completed)
  4. accessing the returned data to fill text boxes of “username”, “department” (uncompleted)
  5. write to ‘building_log’ the data from the audit query plus a date-time timestamp

Search query

SELECT users.Username,
users.Department,
users.SecurityLevel
FROM users
WHERE users.BadgeID = :BadgeID

AuditButton (momentary pushbutton) script

data = event.source.parent.getComponent(‘Badge Log Table’).data
AuditBadgeID = event.source.parent.getComponent(‘BadgeIDAudit’).value

result = system.db.runNamedQuery(“Select”,{“BadgeID”:AuditBadgeID})
event.source.parent.getComponent(‘UsernameAudit’).text = result[“Username”]

Can someone let me know in the last line, what I am doing wrong to reference the query return?

2

Welcome @Robert_Gohn!

Is your named query set to Scalar?

Do you only ever want one column, one row returned?

A standard named query call will return a dataset; multiple rows and columns if your DB has that information stored.

A scalar query only returns one item.

So if you change your named query to scalar (then save to apply), it is simply:

event.source.parent.getComponent(‘UsernameAudit’).text = result

For posting future code snippets, can you please use the pre-formatted text button too. This helps us read it better especially if there is indentation (that matters, in Python/Jython). It’s above where you type your post and looks like </>

1 Like