Subscribe to this thread
Home - General / All posts - VBScript in M9: Do While table is not empty
gjsa100 post(s)
#28-Jul-18 04:28

Have read the latest API for M9 and come up with this loop to run while a table contains at least one record.

The query run inside the loop deletes all the records in the table f21 and inserts some records back into the table. The number of records being re-inserted diminishes until there aree no rows to insert after 6-10 loops. The table f21 will then be empty of records.

The problem I have is twofold:

1. Sometimes the loop runs only once and exits despite the table f21 still being loaded with records

2. Other times the loop runs as expected multiple times but exits when there are still a few records remaining in table f21.

Is it bad practice to have the While condition dependent on a recordset that is changed by the query within the loop?

Sub Main

    Set app = Manifold.Application

    Set db = app.GetDatabaseRoot()

    Q1 = db.GetProperty("Query1","Text")

    Set table = db.Search("f21")

    Set records = table.SearchAll("mfd_id")

    Do While records.Fetch

        Manifold.Application.Log "Running Query1"

        db.Run(Q1)

    Loop

    Manifold.Application.Log "End"

    Manifold.Application.OpenLog

End Sub

vincent

1,972 post(s)
#28-Jul-18 20:55

I do something similar in M8, without problem, like this (javascript) . I get the number of record at each pass with another query.

query2.Text = 'Select [ID] from [chemins_eau_500m] where [situation] = "indéterminée"';

query2.Run();

countPname = query2.Table.RecordSet.Count;

if (countPname > 0){ 

 while (countPname != 0)

 {

 

 query.Text = 'Update (select  [b].[situation] as [situation2]  from [chemins_eau_500m] as [a], (Select * from [chemins_eau_500m] as [b] where [b].[situation] = "indéterminée")where Touches([a].[Geom (I)] , [b].[Geom (I)]) and [a].[situation] = "amont" and [a].[Nom_PNC] = [b].[Nom_PNC]) SET [situation2] = "amont"';

 

query.Run();

 

 

 

 query2.RunEx(1);

 countPname = query2.Table.RecordSet.Count;

 

 }

}

tjhb
10,094 post(s)
#28-Jul-18 21:43

It's hard to be sure without seeing the query itself.

But it seems just about possible that the script gets to Loop (and therefore records.Fetch) before the engine has populated the table with new records, especially if they are inserted in a significant batch.

If that is possible then it might be made worse by using Database.Run which requires recompilation for each loop; better to use Database.RunCompile first then Command.Run in the loop.

Just a hunch, but I wouldn't be surprised if the issue goes away if you switch from VBScript to a .NET language.

tjhb
10,094 post(s)
#28-Jul-18 22:02

In other words there is nothing explicitly requiring the query execution to finishbefore the loop condition is retested.

Especially important if the query has two statements (I think so, DELETE then INSERT).

(Sorry for all thinking out loud.)

I would try breaking the query into two statements, compiled in advance and run separately, and in each case consuming the result table (by assigning it to a variable; you could also log the first record as the number of affected rows).

I think that would have the effect of making the loop and query run synchrnously.

gjsa100 post(s)
#28-Jul-18 23:55

That makes entire sense to me and would explain the inconsistent behavior.

I will report back with a revised script that works.

adamw


10,447 post(s)
#01-Aug-18 13:13

Whenever you write into a table, you have to close and reopen the sequences you are using to read data from it. You won't crash if you don't do it, but the results will be finicky: a sequence might stop before reaching the end of the table, it might jump over some records, or it might start returning records it already went through before.

The script in 8 given by vincent works because the RecordSet object returned by the query is a cached copy of the data. You can do the same in 9 using a temporary table.

With sequences, you usually capture data into a local storage (easiest: a temporary table) then write from that local storage. Or write into a second table (then delete the original and rename the new table to the former name).

tjhb
10,094 post(s)
#01-Aug-18 13:40

So gjsa's initial hunch ("bad practice") was exactly right, and my hunch about timing within the loop was wrong.

Out of my depth, but I suppose this is because once a sequence is "on its feet" it maintains internal state, including a pointer to the next item (obtained via yield or something similar), whose coherence is compromised if the collection over which the sequence is operating is truncated or extended.

adamw


10,447 post(s)
#01-Aug-18 14:58

Yes, this is because a sequence is a low-level object designed to be fast.

tjhb
10,094 post(s)
#01-Aug-18 15:30

This looks a bit nuts--but also correct?

'VBScript

...

Set cmd = db.RunCompile(Q1)

Set records = table.SearchAll("mfd_id")

Do While records.Fetch

    cmd.Run ' changes record set

    records.Dispose

    Set records = table.SearchAll("mfd_id")

Loop

...

tjhb
10,094 post(s)
#01-Aug-18 15:52

But I think (even though records is a sequence, lazily evaluated) that it may be faster to replace the inline query with API methods. Depending on the detail of the query.

(And as before, care must be taken to ensure that the query always reduces the record set, else potential infinite loop.)

adamw


10,447 post(s)
#01-Aug-18 16:17

This is not very useful, because SearchAll inside the loop loses position all the time. What would work is saving the key for the record from which to restart the search and use Search.

Give me a minute and I will post an example of how to do it much simpler.

adamw


10,447 post(s)
#01-Aug-18 16:44

See attached.

We have a table with fields 'a' and 'b', we are reading it and trying to update a subset of records.

This script first collects all changes and then applies them:

' VBScript

 

Sub Main

  Set app = Manifold.Application

  Set db = app.GetDatabaseRoot()

  Set table = db.Search("t")

 

  ' change records with [a] < 200: set [b] to [a]+1

 

  ' create temporary table to hold changes

  Set changes = app.CreateTable() ' creates mfd_id / mfd_id_x

  Set schema = changes.GetSchema()

  schema.AddField "b""int32"

  changes.Design schema

 

  ' prepare to collect changes

  Set valuesNew = app.CreateValueSet()

  valuesNew.AddValue "mfd_id"

  valuesNew.AddValue "b"

 

  ' collect changes

  Set records = table.SearchAll(Array("mfd_id""a"))

  If records Is Nothing Then

    app.Log "No records"

    app.OpenLog

    Exit Sub

  End If

  Do While records.Fetch

    Set valuesOld = records.GetValues()

    a = CInt(valuesOld(1).Data)

    If a < 200 Then

      valuesNew(0).Data = valuesOld(0).Data

      valuesNew(1).Data = a+1

      changes.Insert valuesNew

    End If

  Loop

 

  ' apply changes

  Set records = changes.SearchAll(Array("mfd_id""b"))

  If records Is Nothing Then

    app.Log "No changes"

    app.OpenLog

    Exit Sub

  End If

  Set records = records.Recompose(Array("mfd_id"), Array("b"))

    ' tell the table "mfd_id"

    ' ask the table to change "b"

  updated = table.UpdateBatch("mfd_id_x", records)

  app.Log "Records updated: " & updated

  app.OpenLog

End Sub

Hope this helps.

Attachments:
read-write-table-from-script.mxb

tjhb
10,094 post(s)
#01-Aug-18 17:39

Thanks Adam but I don't think that gets it.

The OP's original process, in the query we don't have, first removes all records (on each pass I think) then adds some (fewer) records back (on each pass), eventually none. The idea is to keep going until there are no more records.

I don't know why! So far not told. Anyway it's not a straight selective update.

I can't see why my revision above would not do it? Though I am certain it's not the best method.

Really we need to see the query and know its purpose.

tjhb
10,094 post(s)
#02-Aug-18 02:16

But regardless, it’s a great example.

E.g. I didn’t understand how to use Sequence.Recompose() with Table.UpdateBatch() before. (It’s very SQL-ish!)

adamw


10,447 post(s)
#02-Aug-18 09:39

I guess you might be right, I somehow lost vision of the key paragraph from the first post, sorry. Your process is good for that with cmd.Run potentially doing many changes at once (a small adjustment: have to check if the result of SearchAll is Nothing).

@gjsa:

The query run inside the loop deletes all the records in the table f21 and inserts some records back into the table. The number of records being re-inserted diminishes until there aree no rows to insert after 6-10 loops. The table f21 will then be empty of records.

You might try changing the logic to read from f21 and write into f21_temp, then delete everything from f21 and move records over from f21_temp, then repeat.

Or you might try doing what is being done in the script I posted above: go through f21 collecting records you want to insert into a temporary table, collecting (keys for) records you want to delete into another temporary table, collecting updates if you have them as well, then doing deletes, inserts and updates in bulk and repeating the scan and collect process.

gjsa100 post(s)
#03-Aug-18 01:18

The code below works - now the Do While loop consistently repeats until the count of records in table f21 is zero.

The test now applies to the count of records in an interim table (s12) that is used to update f21, rather than trying to count records in f21 after all the deletes and inserts have occurred. This approach allows the test variable to be updated inside the loop before a new query (Qsd) that updates table f21 and then deletes all the interim tables:

 Qs = db.GetProperty("Q_second","Text")

 Qsd = db.GetProperty("Qs_delete","Text")

 Qe = db.GetProperty("Q_end""Text")

 test = 1

 Do While test

    Manifold.Application.Log "Running Q_second"

    db.Run(Qs)

    Set table = db.Search("s12")

    Set records = table.SearchAll("mfd_id")

    test = records.fetch

    db.Run(Qsd)

 Loop

 Manifold.Application.Log "Running Q_end"

 db.Run(Qe)

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.