| Product: | MicroStation | ||
| Version: | 10.16.00.80 | ||
| Environment: | N\A | ||
| Area: | Database | ||
| Subarea: | Oracle |
ODBC can leverage MS_ODBCPARAMS, but a direct connection to Oracle cannot.
How can I connect automatically to an Oracle Database when launching MicroStation?
This can be accomplished by storing the required command statements and connect string within a script (text) file to be executed via the -s switch appended to your MicroStation shortcut. A guideline for such a procedure with troubleshooting built in follows.
* This process requires and results in storage of a password in plain text. This is a security issue. We recommend avoiding this approach if possible.
Please see the final section 11 (a,b,c) below before proceeding if this is of concern.
- Examples below use the MicroStation CONNECT Update 16 architecture and GUI. The same process is applicable within MicroStation V8i with the GUI changes excepted.
- Your MicroStation CONNECT Edition may have been installed without technology preview preventing execution of script files. When available from the installer Execute Startup Script must be installed and enabled for script execution (step 10).
To check for and enable Execute Startup Script :
from the MicroStation backstage: Settings > User > Operation
00) Ensure that you have the correct Oracle RSF's for the intended MicroStation. e.g. MicroStation CONNECT edition will require use of the x64 client files.
01) Ensure the presence of a valid Oracle Net Service Name within the Oracle Client to be used by MicroStation.
When multiple Oracle Homes exist, this will be predicated by the desired Oracle Home.
If you do not have a net service name, create one using the Oracle Net Configuration Assistant:
After configuration, the Local Net Service Name should be evident within the tnsnames.ora file.
e.g. A01702 as seen here:
It is this Net Service Name with which MicroStation as client will be able to connect to the Oracle Server.
If the created Net Service Name is not shown in your tnsnames.ora file, you have not added the name to the correct Oracle Home, or you are looking at the wrong tnsnames.ora file.
For more information on tnsnames.ora, see: Identifying Destinations: TNSNAMES.ORA
02) Locate and make a copy your SQL+ shortcut (within the same directory should be fine).
03) Edit the properties of the shortcut copy, right click on the shortcut for the context menu and select 'Properties'
On the Shortcut tab, edit the Target field.
Append your connect string e.g.:
user/pass@servicename
...to the end of the shortcut target (after sqlplus.exe) and apply or OK the change.
where:
user: substitute your Oracle username
pass: substitute your password for said username
servicename: substitute the Oracle Net Service Name as from previously
e.g.
...sqlplus.exe user01/pass@A01702
04) Launch SQL+ from the modified shortcut. You should be connected and logged in automatically.
If you are unable to connect using your connect string, You will not be able to connect via MicroStation.
Troubleshoot the connection before proceeding.
05) Launch a MicroStation session and create a file (copy, create new) for the sake of brevity named db.dgn. We will use this file name in later steps. Open the file.
Ideally use a simple path devoid of space characters. e.g. E:\db.dgn We will use this file name in later steps. Open the file.
06) Open the MS Key-in Window.
e.g. > Workflow: Drawing > Ribbon: Home > Ribbon Group: Primary > Key-in
Alternately use <F4> to access the ribbon search tool, and type "Key-in":
07) Via the Key-in window, we will execute 3 statements required to cause connection to the database:
mdl load dbconmg this loads the database connection manager
dbconmgr oracle this tells the database connection manager to prepare to connect to Oracle, and to read the MS Oracle.cfg file
db=(connect string) this tells the database connection manager to attempt a connection via the desired connect string (as from step 03)
These statements can be executed in order, piecemeal, to achieve a connection but for our purposes we will concatenate them into a single string to be used in later steps.
Concatenation should result in a syntax with each trailing statement demarcated from the previous by a semi-colon and devoid of extra space characters.
e.g.
mdl load
dbconmgr;dbconmgr oracle;db=user/pass@servicename
for purposes of this Wiki example thus:
mdl load
dbconmgr;dbconmgr oracle;db=user01/pass@A01702
Check the MS Message Center area for confirmation of connection.
If successful, you should see confirmation in the form of "Database:user/pass@servicename"
If you see "Unsuccessful CONNECT statement" and/or "Database:NONE", the connection was unsuccessful.
For an unsuccessful direct connection to Oracle (when the previous SQLplus test was successful), we would first investigate the MicroStation MS_ORACLE_HOME variable.
To do so, first open the full Message Center window, this can be done via Key-in:
msgcenter open
then, execute the following Key-in:
expand echo $(MS_ORACLE_HOME)
Within the expanded Message Center window, select the $(MS_ORACLE_HOME) entry.
If the Message Details area contains no information, the variable has no definition and may thus need to be set.
Locate and open your ...\MicroStation\config\database\oracle.cfg file within a text editor.
Define the MS_ORACLE_HOME variable to lead to the root of your /bin folder.
e.g.
MS_ORACLE_HOME = E:\0000_APPS\ORA\product\12.2.0\client_1x64
not: MS_ORACLE_HOME = E:\0000_APPS\ORA\product\12.2.0\client_1x64\
not: MS_ORACLE_HOME = E:\0000_APPS\ORA\product\12.2.0\client_1x64\bin
Save the changes to the oracle.cfg file. Close the existing MicroStation session and begin a new session, reopen db.dgn.
Re-execute your connection command string.
e.g.
mdl load dbconmgr;dbconmgr oracle;db=user/pass@servicename
For convenience, you should be able to recall your previous command string from the Key-in window history (bottom pane) or using the 'up' arrow with the focus in the Key-in pane.
If at this point you are still unable to connect, troubleshoot before proceeding.
08) Create a text file to serve as the script file for MicroStation.
As with db.dgn, a short path devoid of spaces is suggested.
e.g.. E:\db.txt
Open the file for editing within a text editor.
Enter your connect string as exists/copied from the MS Key-in window. We’ll use our example:
mdl load dbconmgr;dbconmgr oracle;db=user01/pass@A01702
While it should be obvious whether the string is executed by the script, we’ll add an additional command as an easy ‘proof of life’ indicator.
co=90 this will set the active color to color 90. If your file already uses this as the active color, choose a different number.
perhaps also
place line;xy=0,0,0;xy=100,100,100;reset;null this will place a line at the shown coordinates, terminate the line then exit the place line command
e.g.
mdl load dbconmgr;dbconmgr oracle;db=user01/pass@A01702
co=90
place line;xy=0,0,0;xy=100,100,100;reset;null
We so far have used semi-colons to separate command statements that exist on a single line, for organizational purposes.
There is nothing to prevent you from arranging the commands line-by-line and omitting the semi-colons.
e.g. line 3 could exist instead as line 3-7 as follows with no effect on our particular purpose here:
place line
xy=0,0,0
xy=100,100,100
reset
null
Save the script file.
09) Test the script file execution from within your MicroStation session via Key-in syntax:
@<path><filename>
e.g.
@E:\db.txt
If all has gone to plan, MicroStation will:
(re)connect to the Oracle Database
change the active color to 90
place a line with the active color, and exit the place line command:
*If you have used spaces in your path or filename, you will need to encapsulate the <path><filename> with double quotes
e.g. (adding a space to the file name)
@"E:\d b.txt"
If the script fails to execute troubleshoot before proceeding.
Exit the MicroStation session.
10) Locate and copy your MicroStation shortcut
Edit the properties of the shortcut target, append 2 items after microstation.exe:
E:\db.dgn this will load our db.dgn automatically, speeding any troubleshooting/testing. You may employ but will presumably omit this in your application.
-sE:\db.txt the -s command line switch will launch the specified script file
e.g.
...\microstation.exe E:\db.dgn -sE:\db.txt
Save the changes to the shortcut and launch MicroStation from the shortcut.
db.dgn should load, and the db.txt script will execute connecting you to the database.
11) final notes and suggestions:
- of course, to re-use the existing db.txt, you will want to remove the ancilary 'proof of life' commands. Delete the lines, or simply comment them out by prepending with the # symbol:
mdl load dbconmgr;dbconmgr oracle;db=user01/pass@A01702
#co=92
#place line
#xy=0,0,0
#xy=100,100,100
#reset
#null
- remember, in the course of this exercise we have added or left evidence of plain text passwords via:
a) your copy of the SQL+ shortcut (see step 02)
b) db.txt (of course, just don't forget about it) and any of its progeny produced by yourself
c) the MicroStation command window history will have stored your plain text passwords. To clear this the user preferences file (*.upf) must be deleted. This will cause loss of other preferences
(end)
See also
Other languages sources
| Original Author: | Nelson Hobdell |