发布新帖

検索

文章
· 四月 3, 2024 阅读大约需 3 分钟

Connect Microsoft Excel to InterSystems IRIS via ODBC (Windows)

#新手 #最佳实践 #Microsoft Windows #ODBC #InterSystems IRIS

How to create an ODBC connection on your native Windows laptop to IRIS running on a Windows VM on the same computer, test the connection, and pull data from IRIS into Excel.

Recently I learned that Excel can connect to external databases via ODBC. This includes basically any ODBC data source. Since IRIS speaks ODBC via the ODBC API, we can take advantage of the InterSystems ODBC Driver to establish an ODBC connection to IRIS on Windows that Excel can utilize.

Before getting started, make sure you have installed the latest InterSystems ODBC driver on your native Windows, which can either be found on WRC or on GitHub. I assume that you are running IRIS on a Windows VM using VMWare hosted on your Windows machine. I also assume that you have Microsoft Excel installed on your native Windows as well. You’ll also need some data pre-loaded into your namespace to access. For this post, I am using data from the Python Quickstart. The data can be found on GitHub.

First you need to find the IP address for the VM. (Keep in mind that you may need to repeat this step if you restart the VM, connect to a different network or connect/disconnect a VPN connection.)

To find your IP address in the Windows VM, go to the Windows Command Prompt (cmd):

  1. In the VM, hold the Windows key and the R key. This opens the Run dialog (as seen below). Type in “cmd” and click <OK>:

 

  1. On the Command Prompt, type in “ipconfig -all” to find your IP address. You want to locate the line that says “IPv4 Address….” And make note of the numeric IP address given on that line:

 

  1. Also make note of the Web Server Port of your VM IRIS, because you’ll need that later.

 

Now, return to the Windows host system and open the ODBC Data Source Administrator (ODBC DSA) and configure the connection:

  1. In the ODBC DSA, open the “System DSN” tab and click “Add”:

 

  1. You will be prompted to select a driver for the data source. If you installed the InterSystems ODBC driver on your native Windows machine, you should select “InterSystems IRIS ODBC…” as I have done below and click “Finish”:

 

 

 

  1. This will open a new dialog where you need to name the data source, provide a description, input the IP address you copied previously and the Web Server Port. Input the namespace you want to connect to, the User Name and Password that you use for the Management Portal as well. Then click <Test Connection> as shown below:

 

 

  1. If all went well, you should see that the test completed successfully as shown below. This means that we now have a connection via our native Windows to IRIS on the VM:

 

Now we can open Microsoft Excel and access data via the ODBC data source. We do this by selecting the “Data” tab (1), then clicking “Get Data” (2). Click “From Other Sources” (3), and then “From ODBC” (4).

 

 

From the drop down, select the data source you created previously and click OK:

 

In the Navigator, select the table you want to connect to and click “Load”:

 

From here, we can see that we successfully connected to the data on IRIS on a VM and are able to access it in Microsoft Excel running on native Windows:

 

Wrapping up, we might wonder why we would want to access IRIS data in Excel. Many people have years of Excel expertise. They can use Excel to explore data, create pivot tables, visualizations, and dashboards.

2 Comments
讨论 (2)2
登录或注册以继续
文章
· 四月 3, 2024 阅读大约需 2 分钟

Reviews on Open Exchange - #41

#Open Exchange

If one of your packages on OEX receives a review you get notified by OEX only of YOUR own package.   
The rating reflects the experience of the reviewer with the status found at the time of review.   
It is kind of a snapshot and might have changed meanwhile.   
Reviews by other members of the community are marked by * in the last column.

I also placed a bunch of Pull Requests on GitHub when I found a problem I could fix.    
Some were accepted and merged, and some were just ignored.     
So if you made a major change and expect a changed review just let me know.

# Package Review Stars IPM Docker *
1 iris-xml-sample just perfect 5.5* 5.5   y  
2 fhirserver-profile-based-validation A valuable help to save time on the operation of FHIR profile validation. 5.0     *
3 Jupyter Server Proxy for VS Code Great utility 5.0   y *
4 OwnObjectScriptExtension Works 5.0     *
5 Vector-inside-IRIS all in COS 5.0 y y *
6 webHERALD Simplifying Mobile Integration for Caché Developers 5.0     *
7 workshop-smart A valuable help to save time on the operation of FHIR profile validation. 5.0   y *
8 UnitTest_RuleSet Great working example - easy to adapt 5.0     *
9 workshop-vector-face funny example on vectors 4.8   y  
10 yaml-utils Excellent yaml to json converter. 4.6 y   *

 

A Bit of History & Statistics

  • I started this series 3 years ago with April 2021 reviews
  • 913 packages of OEX were checked over this time
  • 708 (77.3 %) had a review worth to be listed
  • 576 (81.4 %) of them were done by myself
  • 134 (18.6 %) were contributed by other members of the community (BIG THANKS)

A technical note

All data were collected by analysis of official published web pages of OEX and GitHub.
So there may be some numeric differences as I never adjusted historical data.

讨论 (0)1
登录或注册以继续
问题
· 四月 2, 2024

What is the Difference Between do $Increment and if $Increment?

#ObjectScript #Caché #InterSystems IRIS

I'm currently making some changes to legacy code and I've noticed that it uses "i $i("  all over the place. Testing in terminal seems like this does the same thing as "do $i()". Is there a difference between these two (and if not is there some interesting history around this)?

7 Comments
讨论 (7)5
登录或注册以继续
问题
· 四月 2, 2024

Importing HealthShare WSDLs to SoapUI not working for several reasons (no authentication? linked resource not available?)

#业务服务 #CSP #安全 #SOAP #HealthShare

I'm currently fighting with SoapUI to get the HS WSDLs imported.

My first issue is, the IRISUsername and IRISPassword parameters don't seem to work.

i've tried the following url:

https://ifwap0661.ad.klinik.xxxyyy.de/ucrdev/csp/healthshare/hsrepositor...

result:
Error loading: org.apache.xmlbeans.XmlException: org.apache.xmlbeans.XmlException: error: The entity name must immediately follow the '&' in the entity reference.   

not entirely sure what is happening here.

if i enter the URL in a browser i'm required to still authenticate with user / pw, so i guess the Username & Password parameter somehow isn't working?

Second issue:

I downloaded the WSDL from the browser and saved it locally, to then import it into SoapUI, which also does not work:
Error loading [http://www.intersystems.com:80/healthshare/ihe/schema/IHE/XDS.b_Document... org.apache.xmlbeans.XmlException: org.apache.xmlbeans.XmlException: error: Element type "link" must be followed by either attribute specifications, ">" or "/>".  

going to http://www.intersystems.com:80/healthshare/ihe/schema/IHE/XDS.b_Document... doesn't seem to provide the required document.

have these been moved? are there new WSDLs provided somewhere?

1 Comment
讨论 (1)2
登录或注册以继续
问题
· 四月 2, 2024

How to define file name in EnsLib.File.PassthroughOperation?

#InterSystems IRIS

EnsLib.File.PassthroughOperation has File Name property with default value: %f_%Q%!+(_a)

The Management Portal documentation description of the property:

Name of file to output the document(s) to. May include timestamp specifiers. The %f specifier if present will be replaced with the name of the document's original source filename (stripped of characters illegal in target filenames).

See the method Ens.Util.File.CreateTimestamp() for documentation of timestamping options.

More documentation describing the behaviour of %f: https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls...

But I fail to find any documentation how I can set the "document's original source filename". The data is written correctly to a file but the file names are like:

$ ls -1 /tmp/out/
_2024-04-02_17.10.16.766
_2024-04-02_17.12.14.145
_2024-04-02_17.24.06.437
5 Comments
讨论 (5)2
登录或注册以继续