Subscribe to this thread
Home - General / All posts - basic help translating m8 vbscript loop with query into m9 vbscript
tgazzard
133 post(s)
#26-Jun-18 23:49

Hi,

Trying to translate some basic code from m8 to m9 and run into a simple issue with how to write the equivalent in m9. I use this type of format often in m8 - the looping of a query sequence is cool.

I had a look through the help around this. In particular, I got stuck on the syntax "&(i)&" bit. I wasn't sure how to search for this in the help? I tried loop, query and a few other search words

m8 = vbscript

Sub Main

set doc = Application.ActiveDocument

 set comps = doc.componentset

i = 1

set q1 = comps.item("q1_phx_variables_input_query")

do

q1.text = "update (select c.treated_area e, d.sumarea f from [Fop17_20_y1_phx5_alt1_tests_rr 2] c, "_

&"(select scenarioid, sumarea from (select sum(area_ha1) as sumarea from [Rand_weighted_fop17_bu_setup_200_iters] where total"&(i)&" = 2) a, "

&"(values ("&(i)&") names (scenarioid)) b) d where c.scenarioid = d.scenarioid )set e = f;"

q1.run()

i = i+1

loop until i > 50

End Sub

----------

m9 vbscript - attempt at getting something to work

' VBScript

Sub Main

 Set app = Manifold.Application

    Set db = app.GetDatabaseRoot()

 

 i = 2

 text = "alter table year"i" (add year_test int64);"

 Set query = db.RunCompile(text)

 Set table = query.Run()

 

 

End Sub

Any help appreciated.

Dimitri


4,991 post(s)
#27-Jun-18 06:02

Have you looked at the examples in the API Documentation for 9? It is a different document than regular user interface help.

tgazzard
133 post(s)
#27-Jun-18 06:50

Yes, apologies for not being clear about this. But I went to the API documentation. I wasn't sure what I should be searching for. For example, if you use Ctrl F and search for "query" that is useful. But doesn't quite get into the nitty gritty of the syntax.

KlausDE

6,214 post(s)
#27-Jun-18 08:43

Mfd 9 insists on keyword AS for aliases.

adamw

8,090 post(s)
#27-Jun-18 16:18

Here is my take on the attempted script in 9:

' VBScript

 

Sub Main

  Set app = Manifold.Application

  Set db = app.GetDatabaseRoot()

  i = 2

  text = "create table year" & i & _

    " (mfd_id int64, index mfd_id_x btree (mfd_id));"

  db.Run text

  text = "alter table year" & i & _

    " (add year_test int64);"

  db.Run text

End Sub

You don't have to compile a query (Database.RunCompile) before running it - this is mostly for when you are running the same query over and over with different parameter values. You seem to be embedding parameter values right into the script text, so just use Database.Run (and in VBScript, you can just ignore the returned table if you want to).

Regarding porting the original script in 8, could you post example MAP file for 8 with model data? Good node from Klaus on syntax changes in queries, it would be worth it to first run the query you are trying to construct programmatically on 9 because as it stands now, the generated text probably will fail with an error.

The analog of 8's ComponentSet in 9 is Database. A Database in 9 is a set of Table's. Components other than tables are just sets of properties, accessed either directly via Database or through Table for MFD_META.

tgazzard
133 post(s)
#27-Jun-18 22:51

Thanks this is really helpful.

I will have a play and post something back when I get it working. But you have given me some concrete to work with. Much appreciated.

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