検索

記事
· 2024年4月9日 2m read

Foreign Tables with CSV in action

#CSV #SQL #InterSystems IRIS

FOREIGN TABLES is a rather fresh feature in IRIS  (2023.?)
So I was motivated to try something new by own hands.
Documentation of Foreign Table from File is a good starting point.
Also the related promotional video is fine to start with.


The  advantages of the approach are pretty obvious to me

  • differently to SQL LOAD DATA data remain outside IRIS
  • you pick out what is useful to you and leave the remaining junk aside
  • this seems to be especially attractive for UPDATE and ADD-ON data
  • when you are done you have no pollution of your IRIS instance

On my way to create the example I met some facts that I want to share

  • The requirement of a JAVA language server is only mentioned in LOAD DATA
    • You fail at first access  if it is missing.  Docker Containers have it.
    • My personal environment doesn't need it.
  • Next surprise: What is this FOREIGN SERVER for?
    • Just a placeholder to point to your directory of your CSV-file
  • Creation of the TABLE is pretty straight forward and very flexible
  • Skipping USING clause presented my headers as first ROW of the table
  • And as it's no real table and had no ROW numbers I missed my numeric ID's
  • No surprise: It is READ ONLY, No Updates
  • Also: Finding the right mix of single and double quotes is some challenge
  • Similar: Data presentation in CSV can be tricky
    • Most simple approach: - thinking of date formats -
    • Declare critical columns as VARCHAR 
    • Then handle useful transformations or content check locally  
  • Finally:
    • the Table is visible for SQL but there is no related Class to it
    • you have no chance to play with CALCULATED properties

Summary

  • A great and highly useful feature
  • You have to understand the limits
  • Adjust your expectations to the available options
  • Test also the "impossible" combinations - they will pop up

Enjoy the demo package
GitHub
 

2 Comments
ディスカッション (2)2
続けるにはログインするか新規登録を行ってください
記事
· 2024年4月3日 2m read

Comfortable VSCode Auto Save and ObjectScript CompileOnSave settings

#ベストプラクティス #ObjectScript #VSCode #InterSystems IRIS

Hi - Recently I have been investigating an annoying situation whilst editing ObjectScript classes or routines in VSCode.

What was happening to me was, as I was typing in lines of code into my class (for example, adding a new Method, or changing the Class signature, or a block of code),  this would quickly get syntax checked, re-formatted, and compiled - inevitably, (since I would be mid-way through my typing), this would generate compilation errors.

Knowing that I was mid-way through adding code, I could simply dismiss this message, however, this soon got very annoying, and conditioned me to ignore the error (which is not a good thing).

Even worse, however, is that the compilation process also reformatted the document and sometimes broke what was previously well formatted lines of code (appearing below the line I was writing) - which required me to go into that area of the document and fix them back up.

What's going on ?

There are two settings at play here:

  1. VSCode's auto-save (files.autosave) settings which dictates wether to automatically save a document that has been changed, and if so, how long to wait before saving (files.autoSaveDelay)
  2. ObjectScript extension's compileOnSave (objectscript.compileOnSave), which determines wether after saving the document, this also imports into and compiles on the connected IRIS namespace.

Since I had autosave turned on, and on a short wait delay together with compileOnSave meant that partially edited code kept compiling, generating the above erros, and as mentioned, sometimes re-formatting good, pre-existing blocks of code.

In my case, I have settled on the following:

  1. AutoSave (files.autosave) off (autosaveDelay is ignored),  and
  2. CompileOnSave (objectscript.compileOnSave) on.

Now, when I'm happy with my edits (in my own time !), I press Ctrl-S, to save, import and compile my class, and look out for that pop-up message which has real meaning and purpose now.

You may choose a different combination, (for example, go ahead and Autosave after 5 minutes or so, accepting the code may not fully compile), however, I've found the above combination works for me, and hope that - if you are experiencing the same annoyance, then, this article may help you find your optimum configuration.

Thankyou to @Patrick Sulin, and @Raj Singh for pointing me in the direction of these settings !

Steve Pisani

12 Comments
ディスカッション (12)8
続けるにはログインするか新規登録を行ってください
記事
· 2024年4月3日 3m read

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
続けるにはログインするか新規登録を行ってください
質問
· 2024年4月2日

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
続けるにはログインするか新規登録を行ってください
記事
· 2024年3月29日 2m read

.NET Client-Side Development on IRIS Data Platform

#.NET #ベストプラクティス #開発環境 #InterSystems IRIS

InterSystems IRIS provides a complete application development environment for building sophisticated data- and analytics-intensive applications that connect data and application silos. It is designed to work with all of the common development technologies in an open, standards-based fashion and supports both server-side and client-side programming.

InterSystems IRIS supports server-side application development with both Python and InterSystems ObjectScript. InterSystems IRIS also supports client-side development using many popular development technologies, including Java, C#/.NET, Node.js, Python, and ObjectScript.

The purpose of this article will be to focus on client-side development using a popular environment, the .NET development environment.

The ADO.NET Managed Provider, NET Native SDK, XEP API, and the Entity Framework Provider are a set of powerful APIs that combine to cover your bases regarding client-side InterSystems IRIS data platform development by leveraging the .NET framework.

ADO.NET Managed Provider

The ADO.NET Managed Provider is the InterSystems implementation of the ADO.NET data access interface, which will enable connection to IRIS from your .NET application, enabling the use of SQL queries to access data. The other three APIs use this underlying connection protocol.

.NET Native SDK

The .NET Native SDK will provide direct access to InterSystems IRIS objects, globals, and ObjectScript functionality, such as running classes and routines. Directly accessing globals, the fundamental storage structure for data in IRIS, can speed up data retrieval for your .NET application.

XEP API

The XEP API will facilitate high-speed access to InterSystems objects. This is most useful when working with high throughput objects with low to medium complexity.

The Entity Framework Provider and Object Relational Mapping (ORM)

The Entity Framework Provider is the InterSystems implementation of the Entity Framework, the object-relational mapping for ADO.NET.

  • What is Object-Relational Mapping or ORM?
    • A technique that lets you query and manipulate data from a database using an object-oriented paradigm. These techniques are often implemented as libraries, such as the SQLAlchemy library for Python.
    • If you're drawing a blank thinking of the equivalent library that implements ORM in IRIS, you should be. IRIS can be treated as a relational database (you can use SQL queries to query data stored in IRIS), so there is no need for a library implementing ORM techniques when ORM is built into the platform itself.

.NET developers can leverage any of these APIs alone or in conjunction with the stipulation of requiring the InterSystems.Data.IRISClient.dll assembly file to be referenced in their .NET project. Each API has its pros and cons, but a measured use of each one's capabilities provides a balanced approach to developing on the InterSystems IRIS data platform with the .NET Framework.

1 Comment
ディスカッション (1)1
続けるにはログインするか新規登録を行ってください