Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

retrieving and loading data from ellipse using connector

retrieving and loading data from ellipse using connector

retrieving and loading data from ellipse using connector

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
End With

RE: retrieving and loading data from ellipse using connector

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", _
  ' Specify number of records per reply
  Block.Requests("InstEquip").AddFieldNameValue "_MaxInst", _

  ' Specify the fields required
  Block.Requests("InstEquip").AddFieldNameValue "_ReplyList", _
  ' 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
    '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
    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
        '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 = _
      Block.Requests(1).AddFieldNameValue "_Restart", _
    End If
  Loop ' go back for the next lot
  ' free the memory for the request list
  oMimsx.Blocks.Remove ("MainBlock")
  Exit Sub    

RE: retrieving and loading data from ellipse using connector

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.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close