发布新帖

查找

文章
· 15 hr 前 阅读大约需 10 分钟

SQL LIKEのセキュリティ、パフォーマンス、ベストプラクティスの最適化

前回の記事、SQLでの変数やパターンを使ったLIKEの使用では、組み込みSQLから動的SQLまで、さまざまなシナリオでのLIKE述語の動作や、ワイルドカードや変数を使った場合のパフォーマンスへの影響について解説しました。 前回の記事では、動作するLIKEクエリを書くことに慣れることをテーマにしました。しかし、SQLを動かすことができるようになるのは、あくまで出発点に過ぎません。 信頼性が高く、スケーラブルで安全なアプリケーションを構築するには、LIKEを使ったクエリを含む、すべてのSQLに共通するベストプラクティスを理解する必要があります。

この記事では、次のステップに進みます。 SQLコードを強化し、ありがちな間違いを避け、SELECTステートメント文が正しく動作するだけでなく、効率的かつ安全に実行されるようにするためのいくつかの重要なポイントを見ていきます。 途中で、LIKE述語を使ったSELECTステートメントを例として取り上げ、これらの基本原則がどのようにクエリやその結果に直接影響するかを説明します。

*これは、Geminiがこの記事のために生成したものです。いい感じです。

讨论 (0)0
登录或注册以继续
文章
· 16 hr 前 阅读大约需 4 分钟

VSCodeだけでIRISターミナル完結!ObjectScript Lite Terminalを使ってみた

開発者の皆さん、こんにちは!

私は普段 Windows を使用しており、VSCode で ObjectScript を書いてテストする際には、IRIS ターミナルを別途起動し、VSCode とターミナルの 2 画面を行き来する操作を続けてきました。

ところが今回、ObjectScript エクステンションに追加された「ObjectScript Lite Terminal」を利用することで、VSCode 1 画面内で IRIS ターミナルを操作できるようになりました。

一見すると小さな追加機能ですが、日々 IRIS を触っている開発者にとっては “地味に革命” と言えるかもしれません。本記事では、その使い勝手を詳しくご紹介します。

Windows 以外の環境では、通常 iris session xxx を実行して ObjectScript シェルに切り替えますが、Lite Terminal を使えばこの操作が不要になります。

 

まずは、起動方法から。

Lite ターミナルの起動には、3種類の方法があります。

その1:コマンドパレットを使って起動する

VSCode の View > Command Palette.. >  ObjectScript: Launch Lite Terminal

 

クリック後、VSCode で現在接続しているネームスペースのターミナルが起動します。

 

その2:出力ウィンドウのプロファイルを利用する

VSCodeの出力ウィンドウを開いている場合、+のマーク(プロファイル)の右側のアイコンをクリックし、InterSystems Lite Terminal をクリックします。

この方法も現在接続しているネームスペースに対してLite Terminal が開きます。

 

その3:サーバーメニューのアイコンを利用する

VSCode 画面左端の ObjectScript エクステンションのアイコンをクリックし、SERVERS メニューを利用します。

接続しているサーバ名以下にある Namespaces > 接続したいネームスペース > Lite Terminal アイコンをクリックします。

 

次は、使い勝手をご紹介します。

 

複数行に分けて実行できる:

ObjectScript のターミナル実行と言えば、ループでもなんでも 1 行でなんとしてでも書くことが多いですが、Lite Terminal は複数行に分けて実行できます。

開始括弧 { があるけど閉じ括弧がない状況で Enter すると、複数行として認識され先頭に ... が表示されます。閉じ括弧 } を入力するまで継続行の入力ができます。

なかなか便利な機能です。

そしてよく見ると、コマンド入力に対して色が分かれています。

入力候補が出たりはありませんが、だいぶ見やすくなった気がしませんか?

 

<SYNTAX>エラーに続けて詳細なエラーが出る:

例えば、コマンドを間違えて実行すると <SYNTAX> のシステムエラーが出ますが、どういうエラーだったか、詳細を出してくれるようになりました。

IRIS ターミナルの例と比較してみます。

<SYNTAX> に続けて、Invalid command: 'sett' と表示してくれました。

 

その他、ネームスペース切り換え、[Ctrl + c ]で強制終了、Halt(または H)でターミナル終了、は通常のターミナルと同じです。

 

一部の機能は残念ながら使えないものもありますので、以下ご紹介します。

 

SQLシェル/Pythonシェルの起動:

通常のターミナルでは、SQL シェル、Python シェルへの切り替えに、コマンドショートカット(:s:p)が利用できますが、残念ながら Lite Terminal ではそれらが利用できません。

ご参考:IRIS ターミナルで履歴からのコマンド実行、コマンドのショートカット作成ができるのをご存知ですか?

各シェルへの切り替えは、以下のシステムオブジェクトのメソッド実行をご利用ください。

  • SQL シェルに切り換える場合は、do $SYSTEM.SQL.Shell()
  • Python シェルに切り換える場合は、do $SYSTEM.Python.Shell()
USER>do $SYSTEM.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------

The command prefix is currently set to: <<nothing>>.
Enter <command>, 'q' to quit, '?' for help.
[SQL]USER>>quit
USER>do $SYSTEM.Python.Shell()
Python 3.12.10 (tags/v3.12.10:0cc8128, Apr  8 2025, 12:21:36) [MSC v.1943 64 bit (AMD64)] on win32
Type quit() or Ctrl-D to exit this shell.
>>> 

 

ここまでの内容はドキュメントにも掲載されています。👉Using the InterSystems Lite Terminal

 

最後に、どのようにLite Terminalが動作しているのかについてですが、WebSocket を利用しています。

そのため、VS Codeと同じシステム上でも、リモートシステム上でも接続可能です。

なお、この機能は、InterSystems IRISバージョン2023.2以降に接続する場合にのみサポートされていますのでご注意ください。

 

新しく追加された Lite Terminal、ぜひご利用ください。VSCode 中心の開発スタイルをさらに快適にしてくれるはずです!😀

讨论 (0)1
登录或注册以继续
文章
· 22 hr 前 阅读大约需 7 分钟

API Unit Testing with CSP Requests and Reponses

In the previous article, we examined how we can use the %CSP.Request and %CSP.Response classes to test a REST API without having the API fully set up and accessible across a network with an authentication mechanism. In this article, we will build on that foundation to perform some simple unit testing of one of our REST API methods.

The unit testing framework requires a couple of setup steps before we can use it. First, we have to ensure that the unit testing portion of the management portal is enabled so we can review the results of our tests. It is located under System Explorer > Tools > UnitTest Portal. If it is not visible there, we should enter the following command into a terminal in the %SYS namespace to turn it on:

%SYS>set ^SYS("Security", "CSP", "AllowPrefix", "/csp/user/", "%UnitTest.")=1

Next, we have to set the global ^UnitTestRoot to a file path where we will store our test classes. After extending the %UnitTest.TestCase class, we must save the class file in this folder for the unit test manager to find it.. We should do it for the namespace where the tests will run. Remember to double up the slashes if you are on a Windows system. Check out the example below:

USER>set ^UnitTestRoot = “C:\\UnitTests”

Then, we should create a class extending the %UnitTest.TestCase in that folder. We will name it User.TestRequest.cls. Since test cases do not work like normal classes, you will not need to save and compile them into your IRIS instance. Instead, during the unit testing process, the class will be automatically compiled, the tests will be run, and the class will be removed from IRIS. It will not delete your test case file, but remove it from IRIS to save the space otherwise used for old unit tests.

We will define the methods in our class that start with the word "Test". It is the way for the unit testing framework to know what methods to run and test. You can also include any other supporting methods of your choice. However, the unit test will only directly call the methods starting with "Test". Your testing will be done with the various assert macros that the test case provides.

Macro

Description

$$$AssertEquals(value1,value2,description)

Passes if both provided values are equal

$$$AssertFailure(message)

Logs an unconditional failure with the given message

$$$AssertFilesSQLUnorderedSame(file1,file2,description)

Passes if the unordered SQL query results contained in the two files are identical

$$$AssertFilesSame(file1,file2,description)

Passes if two files are analogous

$$$AssertNotEquals(value1,value2,description)

Passes if the two provided values are not equal

$$$AssertNotTrue(value,description)

Passes if the boolean expression or value is not true

$$$AssertSkipped(message)

Logs that a test was skipped with the given message

$$$AssertStatusEquals(value1,value2,description)

Passes if the two provided %Status objects are equal

$$$AssertStatusNotOK(value,description)

Passes if the value of the %Status is not $$$OK

$$$AssertStatusOK(value,description)

Passes if the %Status provided is $$$OK

$$$AssertSuccess(message)

Logs an unconditional success with a message

$$$AssertTrue(value,description)

Passes if the Boolean value or expression is true

 

Almost all of the above-mentioned macros take multiple arguments, with the last one being a description that will show up in the unit testing portal. It helps you distinguish more clearly where each test belongs. A single method can include multiple tests by calling various macros. We will reuse the REST class we created in the previous article, focusing primarily on the CalcAge method. Below you can see this class again for reference:

Class User.REST Extends %CSP.REST
{
XData UrlMap [ XMLNamespace = "http://www.intersystems.com/urlmap" ]
{
    <Routes>
        <Route Url="/calc/age" Method="POST" Call="CalcAge" />
        <Route Url=”/echoname/:david” Method=”GET” Call=”EchoName” />
    </Routes>
}
ClassMethod CalcAge() As %Status
{
    try{
        set reqObj = {}.%FromJSON(%request.Content)
        set dob = reqObj.%Get("dob")
        set today = $P($H,",",1)
        set dobh = $ZDH(dob,3)
        set agedays = today - dobh
        set respObj = {}.%New()
        do respObj.%Set("age_in_days",agedays)
        set %response.ContentType = “application/json”
        write respObj.%ToJSON()
        return $$$OK
    }
    catch ex{
        return ex.AsStatus()
    }
}
ClassMethod EchoName(name As %String) As %Status
{
    try{
        if %request.Method '= "GET"{
            $$$ThrowStatus($$$ERROR($$$GeneralError,"Wrong method."))
        }
        write "Hello, "_name
    }
    catch ex{
        write "I'm sorry, "_name_". I'm afraid I can't do that."
    }
}
}

In our test case, we will now create a method to examine the CalcAge method. We will make it pick a hundred random numbers between 0 and the HOROLOG representation of today’s date. The two simplest things to check are whether the method returns a status of $$$OK and if the %response has an HTTP status of “200 OK”. We will also include two assertions for those tests, wrap them (the tests) in a try/catch block, and add an $$$AssertFailure in the exception-handling unit to collect any miscellaneous errors that will occur as the test runs. For instance, if our test method encounters any division by zero, it would catch and log these errors as unit test failures.

Method TestAge()
{
    for i=1:1:100{
        try{
            set today = $P($H,",",1)
            set dob = $RANDOM(today)
            set %request = ##class(%CSP.Request).%New()
            set %request.Content = ##class(%CSP.CharacterStream).%New()
            do %request.Content.Write("{""dob"":"""_$ZD(dob,3)_"""}")
            set %response = ##class(%CSP.Response).%New()
            do $$$AssertStatusOK(##class(User.REST).CalcAge(),"Method %Status Test")
            do $$$AssertEquals(%response.Status,"200 OK","HTTP Status Test")
        }
        catch ex{
            $$$AssertFailure(ex.DisplayString())
        }
    }
}

To execute our trial, we will save the class in the unit test root folder, then open a terminal session and execute the following command in the namespace where our unit test will need to run:

USER>do ##class(%UnitTest.Manager).RunTest()

After doing that, we can go into the System Management Portal and see the results of our unit tests:

By clicking a test's ID, we can check out detailed results. We can then click on (root)>the name of our test class>the name of the test method, and we will discover a table with a column called "actions". It shows which assertion was tested, its status (passed or failed), the description of the test we provided, and its location in our test case class.

It is a good start! Yet, suppose we want to keep digging in and verify that our method's output is correct. The calculated age should be equal to today’s HOROLOG date minus a randomly generated number. Although it is an easy calculation, the trick is capturing that output when the method is called. To do that, we will rely on the often-overlooked IO device, the spooler.

The spooler is simply a device named 2. When we use this device, instead of writing outputs directly from statements, they are written first to a global called ^SPOOL. Since we have a fairly simple method here, our output will end up in ^SPOOL(1,1). For more complex outputs, you may have to iterate over multiple subnodes within the global to get the entire response. The steps to do it are fairly simple. We will kill ^SPOOL to start fresh. Next, we will open 2 and use it. Then we will run our method, close 2, and create a dynamic object from the contents of ^SPOOL(1,1). From there on, the rest will look very familiar and comfortable. Let's add this to our test method.

Method TestAge()
{
    for i=1:1:100{
        try{
            set today = $P($H,",",1)
            set dob = $RANDOM(today)
            set %request = ##class(%CSP.Request).%New()
            set %request.Content = ##class(%CSP.CharacterStream).%New()
            do %request.Content.Write("{""dob"":"""_$ZD(dob,3)_"""}")
            set %response = ##class(%CSP.Response).%New()
            do $$$AssertStatusOK(##class(User.REST).CalcAge(),"Method %Status Test")
            do $$$AssertEquals(%response.Status,"200 OK","HTTP Status Test")
            kill ^SPOOL
            open 2
            use 2
            set sc = ##class(User.REST).CalcAge()
            close 2
            set json = ##class(%Library.DynamicObject).%FromJSON(^SPOOL(1,1))
            set age = json.%Get("age_in_days")
            do $$$AssertEquals(age,today - dob)
        }
        catch ex{
            $$$AssertFailure(ex.DisplayString())
        }
    }
}

We can tell whether our calculation was correct by using the spooler as mentioned above. Also, you might notice that we did not describe this test. When we see it in the unit test portal, it will have an automatically generated description outlining this test based on the assertion we utilized and the provided arguments.

If we wish to expand our testing, we have a few options. One would be creating a new class in the unit test root folder and adding methods there. We could also add more methods starting with the word "Test" to the existing class. In either case, the unit test manager will find and run those tests, but organize them differently in the unit test portal. It is my personal preference to have a separate test case class for each API class I am assessing, with a method in it for each method I am evaluating.

By learning to manipulate the %request and %response objects and understanding how to use those skills in your unit testing, you have gained a powerful new tool for your toolbox!

讨论 (0)1
登录或注册以继续
公告
· 23 hr 前

[Video] Automating Provisioning and Management of FHIR Services

Hey Developers,

Watch this video to learn about automating the provisioning and management of FHIR services:

     ⏯ Automating Provisioning and Management of FHIR Services @ Ready 2025

The presentation introduces REST-based management APIs that enable automated provisioning, configuration, and lifecycle management of FHIR servers without relying on a UI. It explains how these APIs support infrastructure-as-code workflows, large-scale deployments, custom package management, and data loading through background jobs. A live demo illustrates creating, monitoring, customizing, populating, and deleting FHIR servers, highlighting how the same APIs are used by both scripts and the UI.

🗣 Presenter: Jaideep Majumdar, Development Manager at InterSystems

Want to see it in action? Watch the video and subscribe to see more demos!👍

讨论 (0)1
登录或注册以继续
公告
· 二月 18

Beta Testers Needed for our Upcoming InterSystems ObjectScript Specialist Certification Exam

Hello DC community, 

InterSystems Certification is currently developing a certification exam for ObjectScript developers, and if you match the exam candidate description below, we would like you to beta test the exam! The exam will be available for beta testing starting February 18th, 2026. 

Beta testing will be completed May 4, 2026.

What are my responsibilities as a beta tester?

As a beta tester, we ask that you schedule and take the exam by May 4, 2026. The exam will be administered in an online proctored environment free of charge (the standard fee of $150 per exam is waived for all beta testers). The InterSystems Certification team will then perform a careful statistical analysis of all beta test data to set a passing score for the exam. The analysis of the beta test results typically takes 6-8 weeks, and once the passing score is established, you will receive an email notification from InterSystems Certification informing you of the results. If your score on the exam is at or above the passing score, you will have earned the certification!

Note: Beta test scores are completely confidential. 

Interested in participating? Read the Exam Details below. 

Exam Details

Exam title: InterSystems ObjectScript Specialist

Candidate description: An IT professional who:

  • is familiar with object-oriented programming concepts, and
  • uses InterSystems ObjectScript programming language, including objects and SQL, for data access.

Recommended practical experience: At least 6 months - 1 year experience writing and executing ObjectScript code using InterSystems technologies.

Recommended Preparation: Review the following:

Classroom Training

Online Learning

Documentation

Recommended practical experience

At least 6 months of experience developing with InterSystems IRIS using ObjectScript is recommended. 

Exam practice questions 

set of practice questions is provided here to help familiarize candidates with question formats and approaches. 

Exam format 

The questions are presented in two formats: multiple choice and multiple response. Access to InterSystems IRIS Documentation will be available during the exam.  

DISCLAIMER: Please note this exam has a 2-hour time limit. While InterSystems documentation will be available during the exam, candidates will not have time to search the documentation for every question. Thus, completing the recommended preparation before taking the exam, and searching the documentation only when absolutely necessary during the exam, are both strongly encouraged! 

System requirements for beta testing

  • Working camera & microphone
  • Dual-core CPU
  • At least 2 GB available of RAM memory
  • At least 500 MB of available disk space
  • Minimum internet speed:
    • Download - 500kb/s
    • Upload - 500kb/s

Exam topics and content

The exam contains questions that cover the areas for the stated role as shown in the exam topics chart immediately below:

Manages Data Model (23) Applies basic programming concepts to InterSystems ObjectScript and SQL (13) Identifies and leverages features unique to InterSystems IRIS (14) Uses ObjectScript functions and APIs for common operations (26) Handles and resolves errors in InterSystems IRIS (12)

1.1 Uses classes

  1. Identifies use cases for persistent and registered object classes
  2. Creates and saves a persistent object
  3. Deletes objects
  4. Interprets storage definitions
  5. Implements multiple inheritance
  6. Documents classes

1.2 Creates properties, indexes, and other class members

  1. Sets max length for string properties
  2. Uses stream properties for large data sets
  3. Creates properties that calculate values dynamically or are auto-updated (e.g., a timestamp for last updated)
  4. Creates and validates class member parameters and attributes
  5. Selects appropriate index type based on data distribution
  6. Uses unique index methods
  7. Recalls how foreign keys enforce referential integrity

1.3 Creates ObjectScript methods

  1. Differentiates between instance and class methods
  2. Uses class parameters inside methods
  3. Specifies method arguments and return type
  4. Passes objects to methods
  5. Passes variables by reference
  6. Passes multidimensional variables by reference
  7. Uses and overrides inherited methods
  8. Determines when to use ##super for calling superclass methods

1.4 Uses complex structures 

  1. Creates dynamic objects/arrays (JSON) 
  2. Uses stream objects of the appropriate type

2.1 Ensures data integrity

  1. Manages transactions
  2. Manages rollbacks
  3. Describes how LOCKs enforce concurrency
  4. Describes lock escalation threshold and effect on row locks versus table locks
  5. Differentiates between pessimistic and optimistic concurrency controls
  6. Uses transactions and applies concurrency controls in SQL scripts

2.2 Tracks application data

  1. Locates and accesses application globals
  2. Uses logging to track application data
  3. Adds and tracks metrics for performance monitoring

2.3 Implements security features when writing code

  1. Ensures appropriate variable and global use to avoid security leaks
  2. Checks roles for permission control
  3. Prevents SQL injection attacks
  4. Implements embedded SQL permission checks

3.1 Differentiates between different storage media in InterSystems IRIS

  1. Differentiates between PPGs, variables, temporary globals, and globals

3.2 Leverages InterSystems ObjectScript and SQL features

  1. Identifies ObjectScript as a weakly typed language and contrasts its usage with strongly typed languages
  2. Uses system macros and include files
  3. Describes how object structures are projected to SQL tables
  4. Differentiates between Embedded and Dynamic SQL
  5. Differentiates between runtime and select modes, and enforces the correct mode
  6. Uses SQL variables such as ROWID and SQLCODE

3.3 Handles nulls

  1. Manages $C(0) in SQL and ObjectScript

3.4 Handles schema evolution

  1. Adds indexes to existing properties
  2. Describes the impact of changing the name and datatype of a property with existing data
  3. Describes consequences of purging cached queries after schema changes

3.5 Ensures scalability and performance

  1. Uses TUNE TABLE to optimize performance
  2. Interprets basic query plans
  3. Tests code correctness and performance in non-functional tests

4.1 Traverses and sorts arrays

  1. Describes how subscripts are sorted within an array
  2. Traverses subset of a subscript using $ORDER
  3. Traverse multi-level variables using $ORDER
  4. Checks the existence of array nodes with $DATA

4.2 Manipulates and processes lists

  1. Uses $LIST to insert/update/delete elements in a list
  2. Retrieves and iterates through elements in a list using $LISTGET and $LISTNEXT
  3. Converts between lists and strings

4.3 Manipulates strings

  1. Uses $PIECE to extract and manipulate delimited strings
  2. Uses $EXTRACT to retrieve substrings
  3. Uses $REPLACE, $TRANSLATE, and $ZSTRIP to manipulate strings
  4. Interpolates strings
  5. Uses regular expressions to search and replace patterns in strings
  6. Uses $ZCONVERT to escape and encode strings
  7. Recalls how to escape special characters within strings

4.4 Performs mathematical, logical, date, and time operations

  1. Uses mathematical operators 
  2. Uses logical operators
  3. Formats dates and times (e.g., uses $ZDATE$ZTIME, and $HOROLOG)
  4. Performs arithmetic operations on date/time values

4.5 Uses decision and control structures

  1. Uses post-conditionals to control which commands are executed
  2. Distinguishes between the quit and return commands
  3. Identifies how expressions are evaluated in Boolean contexts

4.6 Executes and queries methods and objects

  1. Executes methods with $METHOD and $CLASSMETHOD
  2. Uses %IsA and %ClassName to obtain information about existing objects
  3. Uses %Dictionary to inspect class definitions

4.7 Uses APIs for common operations

  1. Reads and writes files with %Stream package
  2. Uses %Net to make HTTP requests, transfer files securely, and send emails

5.1 Uses InterSystems IRIS supported troubleshooting tools

  1. Uses tools provided in InterSystems IRIS for monitoring code performance
  2. Interprets class compilation errors 

5.2 Handles and logs runtime errors

  1. Uses TRY-CATCH to handle runtime errors
  2. Throws and handles exceptions in ObjectScript
  3. Reviews application error log for runtime failures
  4. Uses $STACK to analyze and trace runtime errors
  5. Converts error status codes to readable messages
  6. Differentiates between statuses and exceptions when troubleshooting

5.3 Diagnoses and debugs common runtime errors

  1. Diagnoses and debugs <SUBSCRIPT> errors
  2. Diagnoses and debugs <PROTECT> errors
  3. Diagnoses and debugs <FRAMESTACK> errors 
  4. Diagnoses and debugs <UNDEFINED> errors

Instructions: 

Please review the following instructions for scheduling and buying an exam:

  1. From our exam store, log in with your InterSystems Single Sign-On (SSO) account.
    1. If necessary, please register for an account.
  2. Select InterSystems ObjectScript Specialist - Beta (IOS-Beta) and click Get Started.
  3. Verify system compatibility as instructed. The Safe Exam Browser download requires administrative privileges on your device.
  4. Run the setup test to ensure the device satisfies the exam requirements.
  5. Schedule your exam – this must be done before checking out. The exam must be taken at least 24 hours after, but within 30 days, of scheduling the exam.
  6. Review the InterSystems Certification Program Agreement.
  7. Confirm your appointment. You will receive an email from Certiverse with your exam appointment details.
  8. You can access your reservations and history through the Exam Dashboard available through the MY EXAMS menu.

Below are important considerations that we recommend to optimize your testing experience:

  • Read the Taking InterSystems Exams and Exam FAQs pages to learn about the test-taking experience.
  • Read the InterSystems Certification Exam Policies.
  • On the day of your exam, log in to Certiverse at least 10 minutes before your scheduled time, launch the exam under MY EXAMS, and wait for the proctor to connect.
  • Please have your valid government ID ready for identification. The proctor will walk you through the process of securing your room and releasing the exam to you. 

You may cancel or reschedule your appointment without penalty as long as the action is taken at least 24 hours in advance of your appointment. The voucher code will reactivate and you can use it to reschedule the exam.

Please contact certification@intersystems.com if you have any questions or need assistance, and we encourage you to share any feedback about the exam, whether positive or negative.

1 条新评论
讨论 (1)2
登录或注册以继续