When communicating with other Microsoft products, Rose
script is usually going to connect in the exact same way
as VB. So for these types of questions, one can always
go look for VB example code, which there is a lot of,
floating around on the web.
There are two methods which can be used to make SQL calls to
a database using Rational Rose scripting. You can use ODBC
functionality or use the Microsoft Windows ActiveX DataObjects
COM functionality.
===================================================================
ODBC functionality
===================================================================
This functionality is document in Rose online help.
The following is a summary of the commands available
Command Description
SQLClose Close a data source connection.
SQLError Return a detailed error message for ODBC functions.
SQLExecQuery Execute an SQL statement.
SQLGetSchema Obtain information about data sources, databases,
terminology, users, owners, tables, and columns.
SQLOpen Establish a connection to a data source for use by other functions.
SQLRequest Make a connection to a data source, execute an SQL
statement, and return the results.
SQLRetrieve Return the results of a select that was executed by
SQLExecQuery into a user-provided array.
SQLRetrieveToFile Return the results of a select that was executed by
SQLExecQuery into a user-specified file.
Example from online help:
'This example executes a query on the connected data source.
Sub Main()
Dim s As String
Dim qry As Long
Dim a() As Variant
On Error Goto Trap
id& = SQLOpen("dsn=SAMPLE", s$, 3)
qry = SQLExecQuery(id&,"Select * From c:\sample.dbf")
MsgBox "There are " & qry & " columns in the result set."
id& = SQLClose(id&)
Exit Sub
Trap:
rc% = SQLError(a)
If (rc%) Then
For x = 0 To (rc% - 1)
MsgBox "The SQLState returned was: " & a(x,0)
MsgBox "The native error code returned was: " & a(x,1)
MsgBox a(x,2)
Next x
End If
End Sub
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Another approach:
'the following script opens up a database registered with ODBC as
'MyDB (which could be a mdb or even an Oracle database), and gets
'all the rows in a table called t_myclasses, it then walks through
'them creating new classes in the model for each.
Set pkg = RoseApp.CurrentModel.RootCategory
Set conn = CreateObject("MSADO.Connection")
conn.open "MyDB"
Set rst = conn.execute("SELECT * FROM t_myclasses")
do while not rst.eof
pkg.AddClass( rst("class_name") )
rst.MoveNext
loop
===================================================================
ActiveX Data Objects (ADO) COM method
===================================================================
Sub Main
Dim Access_app As Object
Set Access_app = CreateObject("Access.Application")
Access_app.Visible = True
Access_app.OpenCurrentDatabase "C:\Program Files\Microsoft Office\samples\orders.mdb"
Access_app.Visible = True
End Sub
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Code previously posted to the forum:
From: Peter Mestrum <[EMAIL PROTECTED]>
To: "'Rose Forum'" <[EMAIL PROTECTED]>
Date: Fri, 1 Sep 2000 10:55:37 +0200
I wrote a script that accesses via OLE (actvieX) an MSAccess database.
Tables in tehh MSAccess database reflect the structure of the model.
Subroutines in the MSAccess database populate these tables with
information from the model.
Example:
The database is called "RoseModel.mdb" and contains a table "tblClasses"
that has columns psk, name, documentation and category.
Also a module called "RationalRose_subs" is defined. Subroutines and
functions in this module will be accessed from from my Rational Rose
model (the .mdl file). I included some coding (see end of my reply)
The procedure is as follows:
*)The model is loaded into Rational Rose.
*)The "export model information" script is loaded into Rational Rose.
*)The script is runned and the tables in the MSAccess database are
populated.
This procedure is not as flexible as XML would be but it has the
advantage for that once I have the model information inn an MSAcces
adatabase I can generate reports that exactly reflect the information
that I require.
And this method has a few conditions that have to be met beforehand:
1. An MSacces database must exist that reflects the information that you
want to store from the model.
2. In the MSAccess database subroutines must exist that access the
tables in the database.
3. Last but not least, you need to have knowledge of MSAccess databases
and accessing tables via VBA.
Another thing to keep in mind is that there is a strong coupling between
the Rational Rose script and the database that is populated.
The tricky thing is to set up an initial database, after that you gain
in efficiency because you can easily add extra columns to tables and
quickly generate custom made reports etc. so that specific information
can be obtained fast.
-----[ START OF CODE ]-----
Sub ExportModelInformation
...
...
...
'
' Open the MSAcces database. Create it if necessary
'
Set msaApp = getobject (dbLocation & dbName,"Access.application")
Set msaDbs = msaApp.currentdb ' Open database
msaApp.run "rroAddSession", sessionName, startofModule
'
' Retrieve information from the model and
' populate tables in MSAccess database
'
Dim theCategoryCollection As CategoryCollection
Dim theCategory As Category
' Open table "tblCategories"
' (rrOpentables is a sub in the MSACcess database)
msaApp.run "rroOpentables", tblCategories
' Retrieve package information from the model
' and populate table "tblCategories"
' (rroAddRecord is a sub in the MSAccess database)
Set theCategoryCollection = theModel.GetAllCategories()
For i=1 To theCategoryCollection.count
Set theCategory = theCategoryCollection.GetAt(i)
msaApp.run "rroAddRecord",
tblCategories,theCategory.name, theCategory.documentation
next i
...
...
...
' Close database and finish application
msaDbs.close
msaApp.quit
MsgBox "Dataloading finished"
End Sub
-----[ END OF CODE ]-----
===================================================================
MORE INFORMATION:
===================================================================
To get a list of the Access objects use the Object Browser.
You can set a reference to the Access object library and then view
all of the possible methods and attributes of the OLE objects
supported.
See Access help subjects:
"Work with objects in Visual Basic by using the Object Browser"
"Use the Microsoft Visual Basic Editor"
additionally see,
http://www.microsoft.com/AccessDev/Articles/comothap.htm
OLE Automation: Communicating with Other Applications"
Microsoft Office 2000/Visual Basic Programmer's Guide
Working with Microsoft Access Objects
ACCESS Application Object
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/off2000/html/acproApplication.asp
Properties Reference
The following reference groups all properties by object or collection
and lists them alphabetically. An asterisk (*) indicates that the
property was added for Microsoft Access 2000....
Patrick Kennedy
Rational Support
-----Original Message-----
From: Dirbach, Joerg [mailto:[EMAIL PROTECTED]]
Sent: Thursday, October 10, 2002 4:12 AM
To: [EMAIL PROTECTED]
Subject: (ROSE) Database access from Rose script
Hi all,
does anybody know how to access a database like Access or SQL Server via ODBC from Rose Script?
Any help with sample code is highly appreciated.
Cheers,
J�rg
---------------
Joerg Dirbach, mailto: [EMAIL PROTECTED]
Z�hlke Engineering AG Wiesenstrasse 10a CH-8952 Schlieren
Tel +41 (0) 1 733 65 71 Fax +41 (0) 1 733 30 21
************************************************************************
* Rose Forum is a public venue for ideas and discussions.
* For technical support, visit http://www.rational.com/support
*
* Post or Reply to: [EMAIL PROTECTED]
* Subscription Requests: [EMAIL PROTECTED]
* Archive of messages:
* http://www.rational.com/support/usergroups/rose/rose_forum.jsp
* Other Requests: [EMAIL PROTECTED]
*
* To unsubscribe from the list, please send email
* To: [EMAIL PROTECTED]
* Subject: <BLANK>
* Body: unsubscribe rose_forum
*************************************************************************
