I run this query on a linked drawing in SQL 2005:

"SELECT [ID 2] FROM [IndexMap v1.2 Table] WHERE [Selection (I)] = TRUE"

Then a "Linking Data" popup appears with a progressbar counting all the 24,000 objects in the linked drawing. The selected row/object is then automatically deselected. And the query returns nothing.

If I Unlink this drawing from the SQL storage into my local .Map file, then the same query runs as expected and I get the ID2 value of the selected row/object.

It seems that if I run a query against the linked drawing it reload the data and remove the selections before my query is executed.

I need to select several objects and then update a field in each with a specific value. My script works well on my local drawing, but not in the linked drawing. Is there a workaround for this?

I'm having way too many problems with shared SQL storage. I hope to get some time to write it all down and send to support.

"I need to select several objects and then update a field in each with a specific value. My script works well on my local drawing, but not in the linked drawing." Have you checked if you're linking your drawing as read-only?

ghelo.arboleda

namria, philippines

ghelobytes@gmail.com

Thanks ghelobytes, I have confirmed that's not the problem.

I can see that the pink selection is disappearing just after the popup and progressbar disappears. If I use the local drawing the pink selection is still there after running the query.

After further testing It seems like the query is just sent to the server "as is" and just because the "Selection (I)" column is not available on the server then the query just returns null.

I guess that makes sense, all users should not share the selection state, that would make a real mess.

My question then is, what other alternative solution do I have when I store all my data on SQL? The Selection attribute on an object is apparently only available in the local cached Manifold copy.

Can this be be solved with SQL queries, or do I have to use some script, any ideas?

Cool question Jocke.

I'm out of my depth, but maybe this is worth a go.

I'm wondering if you could create a local table (T) which listed IDs and tracked Selection (I) in the linked drawing (D), by means of an active column of type Boolean.

The active column [Selected] would read its own RecordData("ID"), use that to look up Selection (I) in the RecordSet of D, and return that Boolean value.

The query would then reference T.[Selected] in place of D.[Selection (I)].

Just an idea. I have no idea whether it would work.

Tjhb, I mostly gave up on the SQL route, because the Selected(I) column is not available in the SQL Server table, I think it's created together with all the rest of the intrinsic columns when you load and link the Geom(I) into Manifold.

Anyhow thanks for your suggestions. I did it the "easy" way, with script and simple comment component to keep the state of my MaxGroupID.

Here the code snippet that I added as an addin button in Manifold. The user selects a group of buildings, then they click on the button, the buildings are then updated with a unique GroupID which is incremented with +1. I had some issue with the ID column, it's re-generated each time I refresh data from the SQL DB, so I use a SQL based ID2 column that is fixed for each building. A theme was used to show the user which buildings they already grouped.

This might be of use for someone:

Sub Main

  dim comp, comps

  dim qry, objs, obj

  dim cmpMaxGroupID,strMaxGroupID

  dim tmpID2

  dim r

  set comps = Document.ComponentSet

  set comp = comps("IndexMap v1.2")

  if comps.ItemByName("tmpQuery") < 0 then

    set qry = Document.NewQuery("tmpQuery")

  else

    set qry = comps.Item("tmpQuery")

  end if

  if comps.ItemByName("MaxGroupID") < 0 then

    set cmpMaxGroupID = Document.NewComments("MaxGroupID")

  else

    set cmpMaxGroupID = comps.Item("MaxGroupID")

  end if

  set objs = comp.Selection

  if objs.Count < 1 then

    Application.MessageBox "No Buildings Selected", "Error"

    exit sub

  else

    if len(cmpMaxGroupID.Text) > 0 then

      ' Get current Max GroupID

      strMaxGroupID = cmpMaxGroupID.Text

    else

      Application.MessageBox "No MaxGroupID", "Error"

      exit sub

    end if

    strMaxGroupID = strMaxGroupID + 1

    'Application.MessageBox strMaxGroupID,""

    for each obj in objs

      set r = obj.Record

      tmpID2 = r.DataText(2) & "," & tmpID2

      'Application.MessageBox r.DataText(2),""

    next

    tmpID2 = left(tmpID2,len(tmpID2)-1)

    'Application.MessageBox tmpID2,""

    qry.Text = "update [IndexMap v1.2 Table] SET [GroupID] = " & strMaxGroupID & " WHERE [ID 2] IN (" & tmpID2 & ")"

    qry.RunEx True

    qry.Text = ""

    Application.StatusText = "Updated " & cStr(objs.Count) &  " Buildings with GroupID = " & strMaxGroupID

    ' Update current Max GroupID

    cmpMaxGroupID.Text = strMaxGroupID

  end if

End Sub 

After further testing it seems like the query is just sent to the server "as is" and just because the "Selection (I)" column is not available on the server then the query just returns null.

It is not that.

Running a query that involves a linked component first refreshes data in that component. This clears the "Selection (I)" column. To avoid that, save the selection in a regular column.

Thanks Adam, for the confirmation about refreshed data, but the whole problem is that the data is supposed to be linked from a single source at the server and 10 people is updating it "live". I don't want them to start copying data locally and then try to update it back, I can't imagine how much problem I will get then.. and it would defeat the whole purpose of linked SQL storage.

I just needed to select a few objects and assign a unique ID for each group. The solutions works now, but I was inistially confused about the refresh of data and lost selection.

I can't see the Selection(I) column on the server in SQL, it's only locally available, correct?

We had the same problem in our production environment, We have several operators and in some cases the operator has more than one instance of the map (application) open.

We have implemented a "long workarround" to avoid that, based in: 1. We have independent ID for each row (we do not use Manifold ID) 2. Every instance of the app has its own "session id" 3. We have a table to save the IDs (our IDs not Manifold ID) of the records selected per session id. This table is used just for this kind of process 4.When you going to execute the command: 4a. Clear the selection in the temp table for the Session ID 4b. Save the selected records for the active session in the temp table 4c. execute your SQL statement making a inner joint between your drawing table and temp table (selection table) for Drawing.TableID = Temp.TableID and Temp.SessionID = App.SessionID. 4d. Clean yours SesionID records from the temp table.

At this way, just the previus selected record can be processed for the SQL statement.

This is not the optimal solution, but works... and is better than UNLINK and RE-EXPORT o copy data....

Another solution is copying the selected element to a Temporal Drawing, process with the sentence, drop the original elements, and change it for the new ones. This kind of solution didnt work for us because we have restriction at database level between geom tables (in order to protect data), so We can not drop a street if there are building dependent (near) of it. If your are just updating tabular data, you will not need to drop original element just make a update between temporal drawing elements and linked elements in the script or app.

I hope, those ideas can be usefull for you

Regards

J Valencia

I'm still curious about the last sentences of your initial posting because you should have none of these problems with shared components. I'm having way too many problems with shared SQL storage. I hope to get some time to write it all down and send to support. Could you give us some keywords of the problems you encountered?

I can't see the Selection(I) column on the server in SQL, it's only locally available, correct?

Yes.

Adamw,

I wouldn't go so far as to say that this is a bug, as it is expected behavior from the programmers point of view. But, it is inefficient in that you can't perform this kind of task using only an SQL query. Therefore, I think this would be considered some low hanging fruit that could be knocked off rather quickly.

It's hardly a low hanging fruit but it's on the wishlist for future versions of Manifold.