retrieving and loading data from ellipse using connector

[reciprocity00]

I am trying to get all the standard job#, origpriority and mainttype for a giving mainttype (e.g.MF). The script I wrote is just giving me the first 20 results (much like the screen dataset in MSQ690). Does anyone know to scroll through the blocks for both retrieval and updating (I would like to update the origpriorty of the retrieved records).

Here is my VBA code snippet:
Set RetrieveSJBlock = gobjMIMS.Blocks.New

With RetrieveSJBlock.Requests.New
.AddFieldNameValue MIMSX_FLAG_SERVICE, "STANDARDJOB.Retrieve"
.AddFieldNameValue MIMSX_FLAG_REPLYLIST, "StandardJob,MaintType,OrigPriority"

With .Instances.New
.AddFieldNameValue "MaintType", "MF"
End With

End With

On Error Resume Next
Set RetrieveSJReply = RetrieveSJBlock.Send

Set sjSheet = Worksheets("StandardJobs")

j = 2
m = 0
l = 0
l = RetrieveSJReply.Requests.Count
m = RetrieveSJReply.Requests(1).Instances.Count
With sjSheet
For x = 1 To m
.Cells(j, 1) = RetrieveSJReply.Requests(1).Instances(x).Fields("StandardJob")
.Cells(j, 2) = RetrieveSJReply.Requests(1).Instances(x).Fields("MaintType")
.Cells(j, 3) = RetrieveSJReply.Requests(1).Instances(x).Fields("OrigPriority")
j = j + 1
Next
End With

 

[Calator]

below is an example of code that loops in order to get all records beyong the first 20. Note that '20' is coded as a constant and could be changed in theory to anything else, but you may find that if changed, the retrival no longer works as expected, just because Mincom classes could only handle 20 (I am not certain, it could be put to the test, but to be safe just use 20). The other constants presents in the code can be safely varied as they have nothing to do with Mincom classes.

The key to the process is the test on "IsRestart", which tells if restart information is present, ie if there are more records available.

Hope that helps.


'global definitions
'max number of records to be returned by one call to object RETRIEVE method
Const maxInstRet As Integer = 20
'max number of records to be returned in TOTAL (ie from iterating calls),
'before asking user if they wish to continue
'set to a low value during testing, eg 25, then to a higher value eg 500
Const maxRecords As Integer = 100


sub ...
'this example calls the EQUIPMENT.Retrieve service
'in order to obtain all Current Equipment for the Productive Unit in the first argument
'Equipment returned is filtered by Status in second argument

Dim Block As MIMSBlock
Dim Reply As MIMSReply
Dim Request As MIMSRequest
Dim Inst As MIMSInstance
Dim intNoOfRecords As Integer

' For training purposes, a max limit of total items
' is set as a constant, so that thousands are not retrieved;
' following variable keeps count of the number of items returned
intNoOfRecords = 0

' Create a new block (network message) to send to the server
Set Block = oMimsx.Blocks.New("MainBlock")

' Create a request for information within the network message
Block.Requests.New ("InstEquip")

' Specify the back end class and method
Block.Requests("InstEquip").AddFieldNameValue "_Service", _
"EQUIPMENT.Retrieve"

' Specify number of records per reply
Block.Requests("InstEquip").AddFieldNameValue "_MaxInst", _
Format$(maxInstRet)

' Specify the fields required
Block.Requests("InstEquip").AddFieldNameValue "_ReplyList", _
"EquipNo,ParentEquip,EquipGrpId"
'=======================================================================

' Create an instance of the request
Block.Requests("InstEquip").Instances.New ("Instance")

'Specify the INPUT FILTERS:

Block.Requests("InstEquip").Instances("Instance").AddFieldNameValue _
"ParentEquip", argParent
Block.Requests("InstEquip").Instances("Instance").AddFieldNameValue _
"EquipStatus", argStatus

' loop to get every block
Do
'Send the block to the server for processing
Set Reply = Block.Send

'Process the reply
If Not (Reply Is Nothing) Then
'If someting was returned
'Note: the number of records returned is available in: Reply.Requests(1).Instances.Count
' Loop through the requests and instances, representing a list of
' Install Position Equipment, and for each item in the list,
' do some processing not shown in this example:

For Each Request In Reply.Requests
For Each Inst In Request.Instances
'some non-relevant processing here

intNoOfRecords = intNoOfRecords + 1
Next
Next

End If

' if no restart information then exit the loop
If Not Reply.Requests(1).IsRestart Then
Exit Do
End If

' there are more records; test how many we already have got,
'and ask permission to continue, if we already have more than the maximum constant

If intNoOfRecords >= maxRecords Then
If MsgBox("More Equipment exists! Do you want to continue?", vbOKCancel) = vbCancel Then
Exit Do
Else
'reset counter and continue; this will allow to return another block of
' maxRecords, before asking again to continue
intNoOfRecords = 0
End If
End If


' add restart field, or set its value if already added
If Block.Requests(1).IsRestart Then
Block.Requests(1).Fields.Item("_Restart").Value = _
Reply.Requests(1).Fields.Item("_Restart").Value
Else
Block.Requests(1).AddFieldNameValue "_Restart", _
Reply.Requests(1).Fields.Item("_Restart").Value
End If

Loop ' go back for the next lot

' free the memory for the request list
oMimsx.Blocks.Remove ("MainBlock")

Exit Sub

 

[Drw]

To add to Calator's comments above, the _Maxinst attribute can be set to a higher number however the limitation on how many records are returned is the 64K packet size.
In one API call we have it set to 1000 however we only get back 128 records. I have not experienced any performance degradation or other issues with setting to a higher number.

This packet size limitation also is apparent when creating multiple instances and sending to the server. e.g. when creating standard text we ran into problems when creating several hundred lines... I found several inconsistencies and limitations to do with the packet size and performance depending of the method used. When trying to resolve the problem (trial and error) it was interesting to find different results when using 128, 256 & 384 instances and/or text lines.

Drew