Subscribe to this thread
Home - General / All posts - Change ConnectionString in code
jkelly


1,234 post(s)
#17-Feb-12 05:16

Hi all

I am guessing this is a real long shot, but I was wondering if there is any way to script changing the connectionstring for a drawing.

I have a large number of maps that have linked drawings / tables, that are coming out of Postgres. I have set up a new server, moved my database across, now I just want to switch the connection across in the Manifold Map files. I know this can be done using the old "Right-click" then relink, but I would love to be able to avoid doing this manually.

edit: I have just checked, if I do the relink option on the menu, then for drawings linked on an Area Of Interest, it relinks the whole thing with no way to specify that I want an area of interest. Looks like I am going to have to manually link the drawings, then add them into the maps and format them. Not good news.

Any ideas?


James Kelly

http://www.locationsolve.com

cartomatic

905 post(s)
#28-Feb-12 17:53

Any luck with this James?

I have been recently playing with ImportSqlServer object and it has a bLink property on the Import method. The problem is that it seems to not affect the importer and imports a table even if I pass it true. The other thing is that it imports a table as a table component even if it has a geom column.

I am hoping I am missing something here and it is possible to both link a component and make it a drawing if it has a geom column.


maps made easy - www.cartomatic.pl || www.cartoninjas.net

jkelly


1,234 post(s)
#29-Feb-12 02:00

No luck, attacked it from a different angle in the end.

I wrote an add-in that Links the new drawings using a GUI script that it loads in from another map file (so it runs in a different thread), then it goes through and copies across all formatting and labels and themes from the old to the new drawings, then replaces all of the old components in all the maps with the new ones, and finally removes the old drawings.

Sorry, that all doesn't help you importing, I haven't had a close look at the ImportSqlServer. The help doesn't mention the bLink property though. I have always just used a gui script to do the linking, as I am only doing this sort of stuff in the gui.


James Kelly

http://www.locationsolve.com

gregory
198 post(s)
#28-Feb-12 23:44

What about changing the name of DB for the same name as old one had ?

If the DBs are keep on the same host and if the name of the tables across the DBs are the same it should do the trick and Manifold will keep formating. This is the scenario I daily deal with (same host, switch the name of DBs, refresh all linked components) but if the your environment is set up similliar way.

jkelly


1,234 post(s)
#29-Feb-12 01:45

Unfortunately, the DB is on a different host.


James Kelly

http://www.locationsolve.com

cartomatic

905 post(s)
#29-Feb-12 07:41

Same here - different hosts, different databases. An all done dynamically so manual adjustment will not do.

I use linking in a webservice for exporting sql server data to shp. So gui scripting won't help in this case ;-(

BTW bLink property although not present in the docu shows up in visual studio.


maps made easy - www.cartomatic.pl || www.cartoninjas.net

hubble_xm9 post(s)
#02-Mar-12 11:53

I had a similar issue when migrating a postgres box. Fortunately once I had verified the migrated databases I no longer had a need to keep the old host up. Easiest solution for me was to add a virtual eth interface on the new host so that connections to the old host ip were redirected to the new host. Obviously may not be applicable if you have other services needing the old host alive. This was on a linux box (debian) but I assume you can do similar in windows.

#05-Feb-16 14:48

Revisiting this thread. Has anyone managed to re-link their drawing & table components programmatically? We have dozens of projects with links to hundreds of datasets and we're going to be moving over to a new server and upgrading to SQL Server 2014 (from 2008). Any thoughts?

Thanks,

G.


Gloria Dangerfield, GIS/Database Coordinator Grey Sauble Conservation, ON Canada g.dangerfield@greysauble.on.ca

GIS Applications Specialist, Partner, Grey Bruce GeoSpatial

firsttube


1,439 post(s)
#05-Feb-16 14:49

See my script below: http://www.georeference.org/forum/t114820.10#129371


"The blessing in life is finding the torture you are comfortable with." - Jerry Seinfeld, 6/26/2013

#08-Feb-16 19:47

I'm not sure if I'm missing something but I'm not seeing a script there.


Gloria Dangerfield, GIS/Database Coordinator Grey Sauble Conservation, ON Canada g.dangerfield@greysauble.on.ca

GIS Applications Specialist, Partner, Grey Bruce GeoSpatial

firsttube


1,439 post(s)
#08-Feb-16 19:53

my post from Jan. 6, 2016, at the end of this thread...


"The blessing in life is finding the torture you are comfortable with." - Jerry Seinfeld, 6/26/2013

BCowper


1,275 post(s)
#08-Feb-16 20:31

Hey Gloria, firstube responded to a blacklisted user. You'll need to uncheck 'Hide posts from users in system blacklist' in your profile to see it.

#08-Feb-16 20:43

Ahhh Thank You!! I really thought I was losing it. Got it!


Gloria Dangerfield, GIS/Database Coordinator Grey Sauble Conservation, ON Canada g.dangerfield@greysauble.on.ca

GIS Applications Specialist, Partner, Grey Bruce GeoSpatial

firsttube


1,439 post(s)
#09-Feb-16 20:27

Sorry, here is the script in case someone else encounters this:

Before running the script you need to set up the new data connection in the Database Console. The value of the newDataConName variable needs to match this exactly. You could modify this to loop through all the drawings, etc.

'vbscript

newDataConName = "new data connection name"

geomName = "name of the geometry column"

set comp = document.componentset("Drawing")

set proj = ui.Panes("Project")

set tree = proj.ControlSet("TreeViewComponents")

tree.Text = comp.Name

ui.InvokeCommand "ViewProjectReLink""pane: Project"

 

Do ' Nothing

Loop Until UI.ModalDialog.Caption = "Data Source"

set dlg = ui.modaldialog

set ctrlset = dlg.ControlSet

'select new datasource

ctrlset("ListViewDataSources").Text = newDataConName

ctrlset("ButtonOK").Push()

'wait

Do:x=x+1: Application.History.Log x & vbnewline

 Loop Until UI.ModalDialog.Caption = "Link Drawing"

set dlg = ui.modaldialog

set ctrlset = dlg.ControlSet

'set all the link options

ctrlset("ComboBoxType").Text = "Drawing"

ctrlset("ComboBoxSource").Text = comp.LinkRowSet & "." & geomName

ctrlset("ComboBoxVersion").Text = "[None]"

ctrlset("ButtonOK").Push()


"The blessing in life is finding the torture you are comfortable with." - Jerry Seinfeld, 6/26/2013

#23-Feb-16 14:47

Thanks so much for this firsttube. It has helped immensely. I've changed it to loop through the drawings and relink them to the defined new data connection.

I have several projects with dozens of drawings linked to different databases. All of these databases are in the same instance but are being upgraded and moved to a new instance of SQL Server. What I would like to do is to either:

1. Best Case - Loop through all of the drawings, set the data connection by splitting (or trimming) the drawing.linkSource and extracting the database name then append a "02" to it (each datasource for the new databases will be the database name with 02 on the end). I'm not sure if this is doable.

2. Worst Case (but not a huge deal if I have to go this route as I there are only about 15 databases) - Run the script for each databases data source defined manually. To do this, however, I need to add a statement whereby if the drawing.linkRowset.geometry is not listed in the in the tables for that data source then it skips it (or uses ButtonCancel to cancel out of the dialog) and moves to the next. Currently, if it comes to a drawing that is not listed in the tables for the defined data sources then it relinks it to the first one in the list!

So this is what I was attempting but it's it doesn't work. Any insight, thoughts appreciated.

sub main

'vbscript

geomName = "Geometry"

set compset = document.componentset

set ui = Application.UserInterface

set proj = ui.Panes("Project")

'loops through drawings

for each cmp in compset

if cmp.type=ComponentDrawing then

set tree = proj.ControlSet("TreeViewComponents")

tree.Text = cmp.Name

'set dataconnection equal to database name + 02 by splitting Link Source string

set mySource = cmp.linkSource

set myArray = Split(mySource, "Database=")

set newDataConName = TRIM(myArray(1)) & "02"

ui.InvokeCommand "ViewProjectReLink", "pane: Project"

Do' Nothing

Loop Until UI.ModalDialog.Caption = "Data Source"

set dlg = ui.modaldialog

set ctrlset = dlg.ControlSet

'select new datasource

ctrlset("ListViewDataSources").Text = newDataConName

ctrlset("ButtonOK").Push()

'wait

Do:x=x+1: Application.History.Log x & vbnewline

Loop Until UI.ModalDialog.Caption = "Link Drawing"

set dlg = ui.modaldialog

set ctrlset = dlg.ControlSet

'set all the link options

ctrlset("ComboBoxType").Text = "Drawing"

ctrlset("ComboBoxSource").Text = cmp.LinkRowSet & "." & geomName

ctrlset("ComboBoxVersion").Text = "[None]"

ctrlset("ButtonOK").Push()

end if

next

end sub

*edit - I do realize I have a trailing ; after the database name which i can put in my new datasource name or trim to remove


Gloria Dangerfield, GIS/Database Coordinator Grey Sauble Conservation, ON Canada g.dangerfield@greysauble.on.ca

GIS Applications Specialist, Partner, Grey Bruce GeoSpatial

firsttube


1,439 post(s)
#23-Feb-16 16:00

Interestingly enough, we are doing something similar! I have to convert all of our drawings in SQL Server that use WKB binary columns for the geometry to SQL Server spatial Geometry data type. It is not a straight convert operation. I wrote a SQL script that does all the conversions for all tables that contain a binary "geom" column. Then I have to run a script in Manifold similar to what you are doing to re-link all the linked drawings in all of our projects using the new geometry columns. The tricky part will be running the script in other users' manifold projects to re-link their linked drawings. And whenever someone opens an old project they will have to do same. I will post the script in our server console so users can download and run it as-needed. Which means it has to run flawlessly!

1)

Change:

if cmp.type=ComponentDrawing then

To:

if cmp.type=ComponentDrawing and cmp.IsLinked() then

This checks to see not only if the component is a drawing but if it is linked.

2)

Change:

set mySource = cmp.linkSource

To:

mySource = cmp.linkSource

3)

Replace:

set myArray = Split(mySource, "Database=")

set newDataConName = TRIM(myArray(1)) & "02"

With:

newDataConName = replace(split(mySource, "database=")(1), ";", "") & "02"


"The blessing in life is finding the torture you are comfortable with." - Jerry Seinfeld, 6/26/2013

#23-Feb-16 16:46

Thank you!!

Yes the tricky part will definitely dealing with old projects etc! I also plan to have it stored in our server console so that everyone can run it on there own if need be. That being said, I plan to set up my data sources on my machine initially and run the script for the majority of ours users projects from my end to reduce any issues (I suspect we have fewer end users than you; about 10). It's going to be a big transition but this will make the process MUCH smoother!

Here's the script

'loops through drawings and their datasources and relinks them

'assumes the new datasource is the DatabaseName02

sub main

geomName = "Geometry"

set compset = document.componentset

set ui = Application.UserInterface

set proj = ui.Panes("Project")

'loops through drawings

for each cmp in compset

if cmp.type=ComponentDrawing then

if cmp.IsLinked() then

set tree = proj.ControlSet("TreeViewComponents")

tree.Text = cmp.Name

'set dataconnection equal to database name + 02 by splitting Link Source string

mySource = cmp.linkSource

myArray = Split(mySource, "Database=")

newDataConName = replace(split(mySource, "database=")(1), ";", "") & "02"

ui.InvokeCommand "ViewProjectReLink", "pane: Project"

Do' Nothing

Loop Until UI.ModalDialog.Caption = "Data Source"

set dlg = ui.modaldialog

set ctrlset = dlg.ControlSet

'select new datasource

ctrlset("ListViewDataSources").Text = newDataConName

ctrlset("ButtonOK").Push()

'wait

Do:x=x+1: Application.History.Log x & vbnewline

Loop Until UI.ModalDialog.Caption = "Link Drawing"

set dlg = ui.modaldialog

set ctrlset = dlg.ControlSet

'set all the link options

ctrlset("ComboBoxType").Text = "Drawing"

ctrlset("ComboBoxSource").Text = cmp.LinkRowSet & "." & geomName

ctrlset("ComboBoxVersion").Text = "[None]"

ctrlset("ButtonOK").Push()

end if

end if

next

end sub


Gloria Dangerfield, GIS/Database Coordinator Grey Sauble Conservation, ON Canada g.dangerfield@greysauble.on.ca

GIS Applications Specialist, Partner, Grey Bruce GeoSpatial

firsttube


1,439 post(s)
#23-Feb-16 16:55

We have about the same amount of users. I think you'll have to set up the data sources on each users' computer first, though (?)

I don't think you need this line: myArray = Split(mySource, "Database=")

Becareful in the future because I think the split function is case-sensitive, so splitting the mySource string into an array using "Database=" may not work if the string contains "database="


"The blessing in life is finding the torture you are comfortable with." - Jerry Seinfeld, 6/26/2013

#23-Feb-16 18:22

Oops you're right I don't need that line. And thanks for the tip about the split command. I did read that it's case sensitive.

Yes if the end users are running the script to fix their own projects they will definitely need the data sources installed on their own machines. To save the headache, however, I might just open their projects and do all the relinking from my machine then once those projects are saved the components are linked to the new instance.

The end users don't need to have the data sources setup in manifold for them to open the projects with linked components but if they ever want to link in new data or relink old projects themselves they will require those data sources setup regardless. Make sense? I've had it happen several times that users get a new computer and we don't realize that I hadn't setup their data sources til later when they need to link in a new component from the database console.

Good luck with your transition!

Cheers,

Gloria


Gloria Dangerfield, GIS/Database Coordinator Grey Sauble Conservation, ON Canada g.dangerfield@greysauble.on.ca

GIS Applications Specialist, Partner, Grey Bruce GeoSpatial

Sloots

678 post(s)
#05-Feb-16 16:00

How about this?

http://www.georeference.org/forum/t113496.7

Chris


http://www.mppng.nl/manifold/pointlabeler

andrej
107 post(s)
#06-Jan-16 13:17

Hi,

new MS SQL box. A couple of houndred projects. Is there something new with this problem or is still prehistoric way (manually link the drawings)?


...Don't take life too seriously . No one gets out alive! ...

firsttube


1,439 post(s)
#06-Jan-16 15:43

I've relinked drawings to new connections using UI scripting. Before running the script you need to set up the new data connection in the Database Console. The value of the newDataConName variable needs to match this exactly. You could modify this to loop through all the drawings, etc.

'vbscript

newDataConName = "new data connection name"

geomName = "name of the geometry column"

set comp = document.componentset("Drawing")

set proj = ui.Panes("Project")

set tree = proj.ControlSet("TreeViewComponents")

tree.Text = comp.Name

ui.InvokeCommand "ViewProjectReLink""pane: Project"

 

Do ' Nothing

Loop Until UI.ModalDialog.Caption = "Data Source"

set dlg = ui.modaldialog

set ctrlset = dlg.ControlSet

'select new datasource

ctrlset("ListViewDataSources").Text = newDataConName

ctrlset("ButtonOK").Push()

'wait

Do:x=x+1: Application.History.Log x & vbnewline

        Loop Until UI.ModalDialog.Caption = "Link Drawing"

set dlg = ui.modaldialog

set ctrlset = dlg.ControlSet

'set all the link options

ctrlset("ComboBoxType").Text = "Drawing"

ctrlset("ComboBoxSource").Text = comp.LinkRowSet & "." & geomName

ctrlset("ComboBoxVersion").Text = "[None]"

ctrlset("ButtonOK").Push()


"The blessing in life is finding the torture you are comfortable with." - Jerry Seinfeld, 6/26/2013

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