Grab string from SQL database

Ignition
1

I’m trying to grab a value from a table like so

groupName = system.db.runPrepQuery("SELECT DISTINCT groupname FROM shipping_stores WHERE store LIKE ? AND chain LIKE ?", [(valueMod), (value2)], 'Sample') value4 ="There is already '%s' going to chain '%s' in group '%s'." % (valueMod, value2, groupName)
and spit out a string which uses this value.
I cant seem to coerce what looks like a pydataset to a single string value.
Any idea what I’m missing here?

2

I assume the query returns a single value, right? You can use this to get the element 0,0 in the dataset:

groupName[0][0]
3

You can use system.db.runScalarQuery() which will return the value located in the first row and column of your resultset. inductiveautomation.com/support/ … rquery.htm

If you have more than one value in the resultset that you want to use you’ll have to loop through the pydataset and pull out the values you want.

4

I’m trying to write a loop for this like

[code]value1 = event.source.parent.getComponent(‘GroupName1’).text
divertID = event.source.parent.divertID
bool = false

results = system.db.runPrepQuery(“SELECT distinct groupname FROM shipping_stores WHERE divert LIKE ?”, [divertID], ‘Sample’)

for i in results[][]:
if results[i] == value1:
bool = 1
break

if bool == 1:
…:[/code]

Is this a good way to set it up? I’m getting some errors with my syntax

5

It’d need to be more like this, I think:

for i in results:
   if i[0] == value1:
      bool = 1
      break

In this instance, i is a row in the dataset. The [0] would be the first value in the row.

6

thanks Jordan,

update:

for i in range(len(results)): if results[i][0] == value1: bool = 1 break
It’s a single column dataset.
It works now.

7

Great, glad you got it working.

8

Nice! :smiley: