MapInfo Pro Developers User Group

I'm developing a PostgreSQL/PostGIS backend for a MapInfo application. One of the procedures available to users is to select items on the map (from a table `orders` stored in the database) and then copy the selected items in to a different table (`proposed_order` - also stored in the database). In the desktop only version, the code works something like this:

Select item_ref From Selection Group By item_ref InTo Item_Refs_To_Copy

Select * From orders Where item_ref In (Select item_ref From Item_refs_To_Copy) Into Order_Items_To_Copy

Insert Into proposed_order (Select * From Order_Items_To_Copy)

The problem is that I can't make a selection in the database based on the `item_refs` in the local selection table, the only way I can think of doing it is building up a string of comma-separated item_refs e.g. `sListItemRefs` and then sending that off to the database as a `Select * from orders Where item_ref In (sListItemRefs)` command with Server_Execute.

As a side note, it would be great it this forum could provide the facility to insert code snippets like Stack Overflow or the MapInfo-L group.

Thanks,

Alex

actually I think I just worked this one out:

I'll create an empty table in the database, open it locally, then insert the records from Item_Refs_To_Copy, at which point I can save the local table which will be reflected in the database, so I can make Select Where In statements

Good one, Alex.

I would pick one of your two solutions as well.

The first, building a comma separated string, is the simplest one but also somewhat limited to the a smaller number of records due to the string length.

The second, inserting the values into an empty table, has no limits to the number of records.

You must however remember to also include some sort of ID in the table as you might get in the situation where two users would be performing this task at the same time.

The ID could be a combination of username and computername or a GUID.

PS: The Code Snippet formating is on the wish list

Hi Peter,

Re multi-user, thanks for pointing that out! Not sure if I would have thought of that in time and could have caused some problems down the line.

Sending the comma separated string didn't seem like a very clean way of doing it and as you point out would have failed if it becomes too long. An unlikely scenario I think but best not left unaccounted for!

Code snippets will be a good addition!