Indusoft (Aveva Edge) VbScript to find a string in a column and return line values

[lunenburger]

I am using Indusoft Version 8.1, and trying to use the VBScript button command to find a string in a column in a MySql database, and return all the values in that line.

So I have a schema called machine12, a table called recipe_list, a column called recipe_name and an Indusoft HMI string tag called RecipeName.

The operator scans in the recipe name as a string and I want to be able to lookup in the MySql table and return the recipe settings for the recipe name.

I can't figure out how to find the return the settings using a string....

I can do it using the line ID with the following script:

Dim sql, numRow, StartOffset
sql="Select * FROM machine12.`recipe_list`Where ID="&$RecipeValue&""
$numCur=$DBCursorOpenSQL("MySqlDB",sql)
numRow=$DBCursorRowCount($numCur)
$globaldata_CurrentRecipe.ID=$DBCursorGetValue($numCur," id")

I think it should look like:

Dim sql, numRow, StartOffset
sql="Select * FROM meerer12.`recipe_list`Where recipe_name="&$RecipeName&""
$numCur=$DBCursorOpenSQL("MySqlDB",sql)
numRow=$DBCursorRowCount($numCur)

but of course that doesn't work...

any help would be greatly appreciated

 

[drbitboy]

For a string, SQL statements need ticks (single quotes; ASCII code 39 decimal = 0x27) around them.

So the statement should look like this:

sql="Select * FROM meerer12.`recipe_list`Where recipe_name=[B][COLOR=blue]'[/COLOR][/B]"&$RecipeName&"[COLOR=Blue][B]'[/B][/COLOR]"

Normally you should not do it that way, because there is usually a way to specify the string with a marker (the ?/query/question mark) below in the statement that gets replaced by the content of the string variable:

sql="Select * FROM meerer12.`recipe_list`Where recipe_name=?"

$numcur=$DbSomething("MySqlDB",sql,$RecipeName)

but that may not exist in your infrastructure (InduSoft Web Studio/AVEVA)

 

[lunenburger]

thank you for the reply....

I tried this code:

Dim sql, numRow, StartOffset
sql="Select * FROM meerer12.`recipe_list`Where recipe_name=?"
$numCur=$DBCursorOpenSQL("MySqlDB",sql,$RecipeName)
numRow=$DBCursorRowCount($numCur)

Didn't work though..... must not be available in Indusoft.

 

[KirkC]

The ticks are only needed if there is a space in the column name, table name, or schema. You don't need any ticks in your statement.

A space *is* needed between SQL language tokens.

SELECT * FROM meer12.recipe_list WHERE recipe_name=?

You need the space before the WHERE.

 

[drbitboy]

Sorry to disagree with KirkC, but I am pretty sure that ticks are needed for a string constant in SQL, even if it has no spaces, but that probably depends on the SQL server.

The "...=?" option will not work unless it is built in to the calling interface (DbCursorOpenSql), which I think it is not for Web Studio.

Did you try with the ticks? i.e.

sql="...'" & RecipeName & "'"

I noticed the missing space also, but wondered if the OP got away without it in the "...`WHERE ID=" & RecipeValue & "" case, because the missing space followed a closing backtick [`], so maybe the SQL parser does not require a space there.

 

[KirkC]

You need either single ' or double " matching quotes around strings. But not a backtick `. A backtick (the character below the tilde on the keyboard) is used for column names with spaces. This is specific to MySQL.

SELECT `my column` FROM my_table WHERE where `my column` LIKE "Apples%"

 

[ndzied1]

I have done this but don’t remember the exact way to do it. I will try to look at the project tomorrow night if you still haven’t gotten it.

 

[Phrog30]

It's easy enough to figure out, create the SQL string and print to a console. Then copy and paste into your SQL software and execute the query.

 

[ndzied1]

So in the vbscript I had an itemnumber scanned from a barcode in the variable ItemNumber. I created a search criteria like this (with trace commands included to show you what is going on:

sCriteria = "ITEMNUM LIKE " & Chr(39) &  $Str( $ItemNumber ) & Chr(39)

$Trace("criteria Message")

$Trace(sCriteria)

Chr(39) gives you a single quote
Then I used that criteria in a DBSelect command like this. If successful, columns in strColumns are placed into the tags in strTags:

ret=$DBSelect("Main","Item",strTags,strColumns,sCriteria,"",1,SdbeRR)

If ret <> 1 Then

	$ShowMessageBox("Item Number Not Found!",48,"Alert")		

Else

   'Do your stuff

End If

When I preview this sCriteria has a space within it in the DBSelect line. Not sure what is up there...

I found using the Chr(39) is the best way to include single quotes within the SQL statements. Otherwise you play around with a single quote within a pair of double quotes and it's hard to look at and I never felt like I got consistent results with that.

 

[drbitboy]

I found using the Chr(39) is the best way to include single quotes within the SQL statements. Otherwise you play around with a single quote within a pair of double quotes and it's hard to look at and I never felt like I got consistent results with that.

Good point. I usually take it one step further, for clarity:

tick = chr(39)

sCriteria = "ITEMNUM LIKE " & tick &  $Str( $ItemNumber ) & tick

...

Good luck!

 

[lunenburger]

Thanks everyone for the replies....

I am not sure where the sCriteria fits in.

I removed the ticks from the database name and added the space before where.

I can write to the database, but it is not finding the string in the column.

There are no database errors...

This is what I have in the code:

Dim sql, numRow, StartOffset
sql="Select * FROM meerer12.recipe_list Where scan_code=?"
$numCur=$DBCursorOpenSQL("MySqlDB",sql,$RecipeScan)
numRow=$DBCursorRowCount($numCur)

Do I add the ticks to $RecipeScan

(I have changed some of the variable names since my first post)
- scan_code is the column name that I am looking in
- RecipeScan is the Indusoft HMI string tag

This code that I am using the ID line number is working:

Dim sql, numRow, StartOffset
sql="Select * FROM meerer12.`recipe_list`Where ID="&$RecipeValue&""
$numCur=$DBCursorOpenSQL("MySqlDB",sql)
numRow=$DBCursorRowCount($numCur)

Thanks again for the help, I am just getting started with VbScript

Thanks Ndzied1 for the ShowMessageBox, that works great!

 

[JesperMP]

Nothing to add to the particular problem, just a small hint.

If you add this line at the beginning of the script:

On Error Resume Next

And then subsequently after each code section that could be potentially problematic, or you are trying to troubleshoot because there is an actual problem, place this:

If Err.Number <> 0 Then
  MsgBox("some description" & "Error=" & Err.Number & " Description=" & Err.Description)
  Err.Clear
End If

That may give you some minimum of error messaging which you otherwise would not get, and without which you are poking in the dark.
Replace the "some description" with something more pertinent. I.e. "Tried to open file."

Depending on what you want to do when there is an error, you may decide to add an 'exit' before the End If.

 

[ndzied1]

Note that we are using different methods to try to “lookup” the record In the database.

The DBSslect command I’m using consolidates multiple steps and is specific to indusoft. The sCriteria is the text of my WHERE clause. The strTags is a list is tags that you want to put the column data into and the strColumns is a list of the columns where you get the data from. If all goes well. With one command you open the table, search for your record and move the column data into the appropriate tags.

The help is pretty ok on it. Give it a scan.

 

[drbitboy]

Thanks everyone for the replies....

I am not sure where the sCriteria fits in.

I removed the ticks from the database name and added the space before where....

I suspect the "... WHERE ...=?" will never work with Indusoft/Web Studio.


Change it to "... WHERE ...=" & tick & $RecipeName & tick


which is functionally equivalent to the first suggestion in Post #2 of this thread.


I apologize for distracting you with hopes of the "...=?" option.

 

[ndzied1]

I’m away from my computer again but you may not be able to use the equal sign, you may need to use the LIKE keyword.