SQL record exists in DB using Select Count(*)

Ignition
1

I am trying to get a script to run that looks into a DB to see if a record exists. The record value is an on-screen tag. I can get the script to function properly if I hard code the value to compare. The code is below:

[code][code]
existsInDB = system.db.runScalarQuery("SELECT COUNT(*) FROM myTable where myData = ‘myTag’ ")
event.source.text = str(existsInDB)

if existsInDB == 0:
system.gui.errorBox(“Not in DB”)
else:
system.gui.errorBox(“Whoopee”)
[/code][/code]

as stated above, if I remove ‘myTag’ from the script, and place 2222 there (2222 is in the database) the script runs correctly and pops up the proper errorBox.

I’m sure I am missing a small thing (I hope…)

Thanks for any help…

Adam

2

You need to set the value of “myTag”

For example if you went above your code and done this:

myTag = 2222

You will have the same as entering it in direct.

I would do it different but you get the idea.

3

I’m assuming your ‘myTag’ reference is you wanting to use the value of some tag called ‘myTag’. If this is the case then you need to get the tag value by calling system.tag.read() and passing in the tag path. I’m going to assume that your tag contains a value of type Int. Then assign this to a variable and do the following:

myTag = system.tag.read("myTag").value
existsInDB = system.db.runScalarQuery("SELECT COUNT(*) FROM myTable where myData = %d " %myTag) 
event.source.text = str(existsInDB)

if existsInDB == None:
  system.gui.errorBox("Not in DB")
else:
  system.gui.errorBox("Whoopee")

You’ll notice that I change your existsInDB==0 to esistsInDB==None. This is because runScalarQuery returns None if there are no rows returned, not 0. Basically existsInDB==0 will never evaluate to true unless the value of the first column of the first row is actually equal to 0.