GeoSCADA VB SQL

[DavidSkilbeck]

Hi, I am trying to use the below SQL statement to retrieve the lowest number in a column of a table.

SELECT MIN(column_name) FROM table_name

Is this SQL statement usable in GeoSCADA or does it require different syntax?

Thanks,

[sbeadle]

That will work. The column needs to be numeric.

If you want to show more columns you would need a GROUP BY

e.g.

SELECT
MAX( ID ), NAME
FROM
CDBOBJECT
GROUP BY
NAME

F1 help contains a syntax guide to the SQL engine.

[BevanWeiss]

As Steve said, that's a valid SQL query.

But... given the subject of your question is 'GeoSCADA VB SQL', your question appears to leaving out the VB part (Visual Basic?.. important to note that GeoSCADA allows the use of VBscript in mimics... but NOT Visual Basic)... so I suspect there's more to it than you asked about.

[DavidSkilbeck]

Hi, the code runs untill MsgBox.
I am trying to get the value of "FirstRowTable" to display in a Msg Box.

Sub DeleteFirstRow()

Dim objectTable
Dim Name
Dim SelectedRow
Dim SQLStatement
Dim FirstRowTable

Set objectTable = Server.FindObject("PoC.Web Service.Work Order.Work Order Table.XXXXX")

SQLStatement = "SELECT MIN(ColumnA) FROM XXXXX"

Set FirstRowTable= Server.Query(SQLStatement)

'Mimic.Layers(0).Item("Lst_1").Sql = Mimic.Layers(0).Item("Lst_1").Sql

MsgBox (FirstRowTable)

End Sub

[BevanWeiss]

When you say 'the value of "FirstRowTable" to display in a Msg Box'.. what do you think the data type of "FirstRowTable" will actually be?  and how would that 'look' in a MsgBox?

I'll cut to the chase, it won't work.  FirstRowTable is not a datatype that will EVER work as a parameter to a MsgBox.  MsgBox is expecting a string.  FirstRowTable is NOT a string.  It is a ServerQuery object.

So.. what you should have, is something closer to the Server.Query example code in the help (below)

Set DGRS = Server.Query("SELECT MIN(ColumnA) FROM XXXXX")

If Not(DGRS.Error) Then

  DGRows = DGRS.Rows

  DGColNames = DGRS.ColumnNames

  For c = 0 To (DGRS.ColumnCount - 1)

    MsgBox DGColNames(c)

  Next

  For r = 0 To (DGRS.RowCount - 1)

    string = ""

    For c = 0 to (DGRS.ColumnCount - 1)

      string = string & DGRows(r,c) & " , "

    Next

    Msgbox Cstr(r) & string

  Next

Else

  Msgbox DGRS.ErrorMessage, vbCritical + vbOKOnly, "Error in SQL Command"

End If

You'll also want to take care around your use of XXXXX, the way that you've used them makes me suspect that you might think the SQL TableName for the DataTable IS the Name of the object in the ClearSCADA Database.

This is NOT the case.

The SQL TableName is the 'TableName' OPC property field of the DataTable / DataGrid, so you'd need to open up the Properties of the Table object to find it.

[DavidSkilbeck]

Hi, I replaced the string to TestResult to get the code to run. When the code is run a message box appears with "min(WorkOrderNo)". Press OK.

Then another message box with "Variable uses an automation type not supported in VBScript".

Referring to this line of code - TestResult = TestResult & DGRows(r,c) & " , ".
Thanks,

[sbeadle]

These lines are important: DGRows = DGRS.Rows DGColNames = DGRS.ColumnNames (Particularly the first). Accessing the data in secondary variables this way seems to avoid a VB issue where the structure is not interpreted correctly.

[sbeadle]

Better still, why not wrap the query into a class to give Record handling functions?

Add the following into a Script Library and reference that library from your script:

'******************************************************************************* 
Class ResultSet
	Public SQL
	Public QueryObj
	Private Dic
	Public CurRow
	Public Rows
	
	Sub Class_Initialize()
		Set Dic = CreateObject("Scripting.Dictionary")		
	End Sub
	
	Public Sub Execute(SQL)
		Set QueryObj = Server.Query(SQL)
		ColNames = QueryObj.ColumnNames
		For i = 0 To QueryObj.ColumnCount - 1
			Dic.Add LCase(ColNames(i)), i
		Next
		Rows = QueryObj.Rows
		CurRow = 0
	End Sub
	
	Public Sub ExecuteS( myServer, SQL)
		Set QueryObj = myServer.Query(SQL)
		ColNames = QueryObj.ColumnNames
		For i = 0 To QueryObj.ColumnCount - 1
			Dic.Add LCase(ColNames(i)), i
		Next
		Rows = QueryObj.Rows
		CurRow = 0
	End Sub

	Public Property Get ColumnIndex(Name)
		'If the column has a space, it is surrounded by quote marks. 
		ColumnIndex = Dic.Item(Replace(LCase(Name),"""",""))
	End Property
	
	Public Property Get Fields(Name)				
		If Not EOF Then
			ColIndex = ColumnIndex(Name)
			Fields = Rows(CurRow, ColIndex)
		Else
			Err.Raise vbObjectError + 1, "RecordSet", "EOF"
		End If	
	End Property
	
	Public Property Get EOF()
		If CurRow = QueryObj.RowCount Then
			EOF = True
		Else
			EOF = False
		End If
	End Property
	
	Public Property Get BOF()
		If CurRow = 0 Then 
			BOF = True
		Else
			BOF = False
		End If
	End Property
	
	Public Sub MoveNext()
		If Not EOF Then
			CurRow = CurRow + 1
		Else
			Err.Raise vbObjectError + 1, "RecordSet", "EOF"
		End If
	End Sub
	
	Public Sub MoveFirst()
		CurRow = 0 
	End Sub
	
	Public Sub MoveLast()
		CurRow = q.RowCount
	End Sub
	
	Public Sub MovePrev()
		CurRow = CurRow - 1
		If CurRow < 0 Then CurRow = 0
	End Sub
End Class

Now you can write code like this:

Set rs = New ResultSet
sql = "SELECT ID, FULLNAME AS FNAME FROM CMimic"
rs.Execute sql
If rs.QueryObj.RowCount = 0 Then
	Msgbox "No Mimics found"
	Exit Sub
End If
Do While Not rs.EOF
	m = rs.Fields("FNAME")
	'Do something
	rs.MoveNext
Loop

[DavidSkilbeck]

Hi, if the SQL statement is changed to "SELECT * FROM WorkOrderData", then the code steps through the names of the columns.
Until it crashes. Thanks,

[DavidSkilbeck · Accepted solution]

Hi, thanks Stephen and Bevan.

I appreciate your answers.

The solution to keep it simple, is to use the syntax as below.
This is suffice for what I need in my particular scenario.

Set DeleteMinRow= Server.Query("DELETE FROM WorkOrderData WHERE WorkOrderNo = (SELECT MIN(WorkOrderNo) FROM WorkOrderData) ")

Thanks,

[BevanWeiss]

You clearly need to work on how you ask questions.

Your problem was

"I am trying to use the below SQL statement to retrieve the lowest number in a column of a table."

"I am trying to get the value of "FirstRowTable" to display in a Msg Box."

and now you mark your own answer as 'The Solution' when it doesn't display a thing, doesn't retrieve the lowest number in a column of a table, doesn't display a MsgBox... so it doesn't solve the problem that you asked people for help with.

[DavidSkilbeck]

Hi Bevan, yes I will try to declare the ultimate goal next time in the first post.

I jumped a step thinking it would be give me the first step to resolving the ultimate goal.

A number of syntax combinations had been tested prior to the first post and unfortunately I had not found the correct syntax.

I couldn't get the code to work and give me the result I required.

If I get chance to return to it I will do so. However, at the moment I have to move on to my next task.

I will bear this in mind and give my ultimate goal in the next post, I post.

Thanks,