NI TestStand

cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Query in TestStand

Hi,
I am having trouble running a query in a Open SQL statement
 
what I have in SQL statement is:
"SELECT TEMP2.*  FROM TEMP2  WHERE Repair_Code=Locals.Repeted_Fault[1]"
 
I am getting error:
" Error executing substep 'Post'.
The following SQL command failed: 'SELECT TEMP2.*  FROM TEMP2  WHERE Repair_Code=Locals.Repeted_Fault[1]...'
Native error code -2147217900 0x80040e14
Microsoft JET Database Engine:
Syntax error (missing operator) in query expression 'Repair_Code=Locals.Repeted_Fault[1]'. "
 
It does work if I remove the local varibale(number) and use absolute number. I have tried various option like leave the local out of quotes, add single quote to query etc. None worked out. I have Statement requires parameters box unticked.
 
I have similar problem using a local variable of type string. But sorted out using Build example.
 
Any help please...
Thanks
MAMA007
0 Kudos
Message 1 of 11
(8,625 Views)

Your command should be:

"SELECT TEMP2.*  FROM TEMP2  WHERE Repair_Code = " + Locals.Repeted_Fault[1]

By enclosing the TestStand expression inside the quote marks, all you are passing is the literal string and not letting the expression be evaluated.

 
0 Kudos
Message 2 of 11
(8,627 Views)
Thanks Dennis,
 
I have tried it too and getting this error:
"Error executing substep 'Post'.
Error In SQL Statement Expression. "SELECT TEMP2.*  FROM TEMP2  WHERE Repair_Code ="+ Locals.Repeted_Fault[1]
Specified value does not have the expected type.
 
"
 
The only way I can get this statement working is by using absolute value like below
"SELECT TEMP2.*  FROM TEMP2  WHERE Repair_Code =207"
 
I thought the double quotes were just there because TestStand treat the statement as an expression and to use an string value or parameter all we need to do is add extra set of single quotes to the existing double quotes. But it didn't worked like that
 
Any clues plz...
Thanks
mama007
0 Kudos
Message 3 of 11
(8,610 Views)

Hi,

Dont you have to convert your number to a string  so that it concat with the rest of the string expression.

eg

"SELECT TEMP2.*  FROM TEMP2  WHERE Repair_Code = " + Str(Locals.Reported_Fault[1])

regards

Ray Farmer

Regards
Ray Farmer
Message 4 of 11
(8,609 Views)
You're right. I missed that. Sorry.
0 Kudos
Message 5 of 11
(8,601 Views)

Thanks Ray,

It did worked perfectly. I thought it would give a error regarding data type mismatch !!!

I also have another question here?

When u run a qurey, how everyone normally get that information to a seq. variable. I feel i am doing in a weired way. For example if I need to find count of a Column in a table,  I run the qurey and save the information to a new table. Then Select newtable.* and perform read operation. It does work. But I have to create a new table and then delete that at the end

So everytime I need to do count operation I have to create a new table and delete it at the end and this is completely waste of time. But is there any other way to return single value returns like count,max..etc staright to a local variable

Thanks in advance

-mama007

0 Kudos
Message 6 of 11
(8,582 Views)
Hi,
Using Labwindows SQL tool kit simplify my previous question. I use both teststand and Labwindows.
I wonder how everyone does run some simple qurries.
 
All I need here is find the most common entry in my  Log.xls  and append this re-occuring information to my Teststand  print report. I have done it by linking the my table to a access database and running some SQL quries & data operations on it to retrieve the re-occuring information
Please can anyone tell if there is any simpler and better way !!!
 
Thanks
-mama007
0 Kudos
Message 7 of 11
(8,551 Views)

Hi,

That seems to be a long winded way of obatining the result. Have you looked that the example WritingAndReadingTableData.seq in Teststand\examples\database.

That may help.

Regards

Ray Farmer

Regards
Ray Farmer
0 Kudos
Message 8 of 11
(8,472 Views)

Dear Dennis,

I have a little different problem than this.... I have a database in which table has a string datatype as a primary key and unique ID... Hence I am trying to run the following Statement:

Select * from BIODATA where BIODATA!ID = "T01.01"

The code works great in LabVIEW but I have trouble in the exacting the same in Teststand.

Attached are the sapshots of my Teststand Steps and Database

I have first opend the database, then opened a SQL statement that has the SQL command i want to pass. Next I have commanded a data operation to extract the current record.

It still is not working!!

0 Kudos
Message 9 of 11
(7,963 Views)

Hi,

In your Data Operations step your SQL statement should be

"Select * from BIODATA where BIODATA!ID = \"T01.01\""

not

"Select * from BIODATA where BIODATA!ID = T01.01"

 

Regards

Ray Farmer

Regards
Ray Farmer
0 Kudos
Message 10 of 11
(7,947 Views)