Hi Zhang,

you'll find a JDBC example below : 

import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class RetrieveBlob {

  public static void main(String[] args) throws Exception {
    // Replace with your connection details
    String url = "jdbc:mysql://localhost:3306/your_database";
    String username = "your_username";
    String password = "your_password";

    Connection conn = DriverManager.getConnection(url, username, password);

    String sql = "SELECT image FROM your_table WHERE id = ?";
    PreparedStatement stmt = conn.prepareStatement(sql);
    stmt.setInt(1, 1); // Replace with the ID you want to query

    ResultSet rs = stmt.executeQuery();

    if (rs.next()) {
      Blob blob = rs.getBlob("image"); // Replace "image" with your column name

      // Option 1: Get Bytes
      byte[] imageBytes = blob.getBytes(1, (int) blob.length()); // Get all bytes
      // Process the imageBytes byte array

      // Option 2: Stream Processing
      // InputStream in = blob.getBinaryStream();
      // ... process the stream
    }

    rs.close();
    stmt.close();
    conn.close();
  }
}

Congrats @Robert Cemper 👏
Your tireless contribution to the community is a model that no one will be able to match.

Thanks @Alberto Fuentes 
I suggest to keep information regarding the classname of the message and its ID :

Method %ShowContents(pZenOutput As %Boolean = 0)
{
   do ..%JSONExportToString(.jsonExport)
    set formatter = ##class(%JSON.Formatter).%New()
    do formatter.FormatToString(jsonExport, .json)
    &html<
        <i style="color:green;">type: #($classname())#  id: #(..%Id())# </i>
        <pre>#(json)#</pre>
    >
}

 in order to get :

Many thanks @Vitaliy Serdtsev ; this is exactly what I needed.

Congrats @José Roberto Pereira, @Henry Pereira, @Henrique Dias , @Muhammad Waseem, @Flavio Naves, Denis Kiselev, Maria Ogienko, Anastasia Samoilova, Kseniya Hoar  and all the participants to this FHIR contest !!

Hello @Ashok Kumar T 
this SQL query is very powerful, but I'm not able to make it work neither with simple TOP, nor with %VID in order to get a pagination.

SELECT top 5
         JSON_ARRAYAGG(
          JSON_OBJECT(
             'Name':name
             ,'Age':age
             ,'DOB':to_char(dob,'Day DD Month YYYY')
            )
         ) 
         FROM sample.person

SELECT *
   FROM (SELECT
         JSON_ARRAYAGG(
          JSON_OBJECT(
            'Name':name
            ,'Age':age
            ,'DOB':to_char(dob,'Day DD Month YYYY')
          )
        ) 
         FROM sample.person)
WHERE %VID BETWEEN 1 AND 5

in both cases, I get the full table with all its records.

Hello, 

with IRIS you can avoid the now deprecated %ZEN.Auxiliary and rather use %DynamicObject / %DynamicArray : 

ObjectScript : 

ClassMethod getJSON() As %String
{
    set query = "SELECT top 3 name,age,to_char(dob,'Day DD Month YYYY') as dob FROM sample.person"
    set rs=##class(%SQL.Statement).%ExecDirect(,query)

    set list = []
    while rs.%Next() {
        set row = {"name":(rs.%Get("name")),"age":(rs.%Get("age")),"dob":(rs.%Get("dob"))}
        do list.%Push(row)
    }
    set result = {"results":(list.%Size()),"items":(list)}
    return result.%ToJSON()
}

Result : 

write ##class(resultset).getJSON()
{"results":3,"items":[{"name":"Pantaleo,Mary Q.","age":36,"dob":"Monday 07 December 1987"},{"name":"Underman,Laura I.","age":71,"dob":"Friday 16 May 1952"},{"name":"Huff,Lydia J.","age":26,"dob":"Sunday 09 November 1997"}]}

Embedded Python :  

ClassMethod get() As %String [ Language = python ]
{
import iris
import json
query = "SELECT top 3 name,age,to_char(dob,'Day DD Month YYYY') FROM sample.person"
rs=iris.sql.exec(query)

list = []
for idx, x in enumerate(rs):
    row = {"name":x[0],"age":x[1],"dob":x[2]}
    list.append(row)
result = {"results":len(list),"items":list}
return json.dumps(result)
}

Result : 

write ##class(resultset).get()
{"results": 3, "items": [{"name": "Pantaleo,Mary Q.", "age": 36, "dob": "Monday 07 December 1987"}, {"name": "Underman,Laura I.", "age": 71, "dob": "Friday 16 May 1952"}, {"name": "Huff,Lydia J.", "age": 26, "dob": "Sunday 09 November 1997"}]}

With embedded python you can get your JSON with few lines of code.

resultsetJSONembedded.py :

import os
import getpass
if not 'IRISUSERNAME' in os.environ:
    os.environ['IRISUSERNAME'] = input('set IRISUSERNAME [_SYSTEM] :') or "_SYSTEM"
if not 'IRISPASSWORD' in os.environ:
    os.environ['IRISPASSWORD'] = getpass.getpass('set IRISPASSWORD [SYS]:') or 'SYS'
if not 'IRISNAMESPACE' in os.environ:
    os.environ['IRISNAMESPACE'] = input('set IRISNAMESPACE [IRISAPP] :') or "IRISAPP"
import iris
import json
query = "SELECT top 5 name,age,to_char(dob,'Day DD Month YYYY') FROM sample.person"
rs=iris.sql.exec(query)

list = []
for idx, x in enumerate(rs):
    row = {"name":x[0],"age":x[1],"dob":x[2]}
    list.append(row)
result = {"results":len(list),"items":list}
print(json.dumps(result))

Result :

irispython resultsetJSONembedded.py 
set IRISUSERNAME [_SYSTEM] :
set IRISPASSWORD [SYS]:
set IRISNAMESPACE [IRISAPP] :
{"results": 3, "items": [{"name": "Xander,Dan T.", "age": 35, "dob": "Tuesday 10 January 1989"}, {"name": "Kratzmann,Jane O.", "age": 34, "dob": "Thursday 14 September 1989"}, {"name": "Ingrahm,Bill S.", "age": 82, "dob": "Saturday 01 November 1941"}]}

or if you prefer export environment variables (without exposing the IRISPASSWORD) :

export IRISUSERNAME=_SYSTEM
export IRISNAMESPACE=IRISAPP
irispython resultsetJSONembedded.py 
set IRISPASSWORD [SYS]:
{"results": 3, "items": [{"name": "Xander,Dan T.", "age": 35, "dob": "Tuesday 10 January 1989"}, {"name": "Kratzmann,Jane O.", "age": 34, "dob": "Thursday 14 September 1989"}, {"name": "Ingrahm,Bill S.", "age": 82, "dob": "Saturday 01 November 1941"}]}

{"results": 3, "items": [{"name": "Xander,Dan T.", "age": 35, "dob": "Tuesday 10 January 1989"}, {"name": "Kratzmann,Jane O.", "age": 34, "dob": "Thursday 14 September 1989"}, {"name": "Ingrahm,Bill S.", "age": 82, "dob": "Saturday 01 November 1941"}]}

Hi,

I know your question was about ObjectScript, but if Python is allowed in your environment, I suggest to use it to get JSON : 

resultsetJSON.py :

import iris
import json
import getpass
import os
if 'IRISHOSTNAME' in os.environ:
    hostname = os.environ['IRISHOSTNAME']
else:
    hostname = input('hostname [localhost] :') or "localhost"
if 'IRISPORT' in os.environ:
    port = os.environ['IRISPORT']
else:
    port = input('port [1972] :') or "1972"
if 'IRISUSERNAME' in os.environ:
    username = os.environ['IRISUSERNAME']
else:
    username = input('login [_SYSTEM] :') or "_SYSTEM"
if 'IRISPASSWORD' in os.environ:
    password = os.environ['IRISPASSWORD']
else:
    password = getpass.getpass('password [SYS]:') or 'SYS'
if 'IRISNAMESPACE' in os.environ:
    namespace = os.environ['IRISNAMESPACE']
else:
    namespace = input('namespace [IRISAPP] :') or "IRISAPP"
connection_string = hostname + ":" + port + "/" + namespace
connectionIRIS = iris.connect(connection_string, username, password)
cursorIRIS = connectionIRIS.cursor()
print("Connected to",connection_string)

query = "SELECT top 3 name,age,to_char(dob,'Day DD Month YYYY') FROM sample.person"
cursorIRIS.execute(query)
 
resultSet = cursorIRIS.fetchall()

list = []
result = {"results":len(resultSet),"items":list}
for x in resultSet:
    row = {"name":x[0],"age":x[1],"dob":x[2]}
    list.append(row)
print(json.dumps(result))
 
connectionIRIS.close()

Result :

python resultsetJSON.py
hostname [localhost] :
port [1972] :51779
login [_SYSTEM] :
password [SYS]:
namespace [IRISAPP] :
Connected to localhost:51779/IRISAPP
{"results": 3, "items": [{"name": "Pantaleo,Mary Q.", "age": 36, "dob": "Monday 07 December 1987"}, {"name": "Underman,Laura I.", "age": 71, "dob": "Friday 16 May 1952"}, {"name": "Huff,Lydia J.", "age": 26, "dob": "Sunday 09 November 1997"}]}

{
"results":3,
"items":[
{
"name":"Pantaleo,Mary Q.",
"age":36,
"dob":"Monday 07 December 1987"
},
{
"name":"Underman,Laura I.",
"age":71,
"dob":"Friday 16 May 1952"
},
{
"name":"Huff,Lydia J.",
"age":26,
"dob":"Sunday 09 November 1997"
}
]
}

Very good article ; thank you @Luis Angel Pérez Ramos 
For those who want to extend the discovery of FHIR, you can access the online documentation on the official web site.

Thanks @Alberto Fuentes for this very good example of LLM + Embedded Python.

Welcome to Developer Community moderators @Enrico Parisi 
'Hoping you're doing well.

Hi @Theo Stolker 

the log is managed by the ^FSLogChannel global.

 set ^FSLogChannel(channelType) = 1

For example, to enable logging for all types of information, enter:

 set ^FSLogChannel("all") = 1

ChannelType is one of the following:

• Msg — Logs status messages.
• SQL — Logs SQL-related information.
• _include — Logs information related to searches that use the _include and _revinclude parameters.
• all — Logs all three types of information.

That's very handy ; thank you @Evgeny Shvarov 

Thanks @Evgeny Shvarov ; that is very handy

Here a simple python test used :

import iris
import ssl
import getpass
import os
 
def main():
    connection_string = "k8s-092c0f86-acbb1223-47d44444fb-7667ad082a377a9e.elb.us-east-1.amazonaws.com:443/USER"
    try:
        username = os.environ['CLOUDLOGIN']
    except:
        username = getpass.getpass('Login:')
    try:
        password = os.environ['CLOUDPASSWORD']
    except:
        password = getpass.getpass('Password:')

    context = ssl.SSLContext(ssl.PROTOCOL_TLS_CLIENT)
    context.verify_mode=ssl.CERT_REQUIRED
    context.check_hostname = False
    context.load_verify_locations("certificateSQLaaS.pem")

    connection = iris.connect(connection_string, username, password, sslcontext=context)
 
    print("connected")
    tablename = "data.movie"
    cursor = connection.cursor()
    try:
        cursor.execute("DROP TABLE "+tablename)   
        print(tablename+" dropped succesfully") 
    except InterfaceError as err:
        print(f"Unexpected {err=}")
    except Exception as err:
        print("ERROR WHILE DROPPING TABLE "+tablename)
        print(f"Unexpected {err=}, {type(err)=}") 
    try:
        cursor.execute("CREATE TABLE "+tablename+" (title varchar(500), year int, score numeric)")
        print(tablename+" created succesfully")
    except Exception as err:
        print("ERROR WHILE CREATING TABLE"+tablename) 
        print(f"Unexpected {err=}, {type(err)=}")
    data = [
        ("Monty Python Live at the Hollywood Bowl", 1982, 7.9),
        ("Monty Python's The Meaning of Life", 1983, 7.5),
        ("Monty Python's Life of Brian", 1979, 8.0),
    ]
    try:
        cursor.executemany("INSERT INTO "+tablename+" VALUES(?, ?, ?)", data)
        print("data succesfully inserted in "+tablename)

    except Exception as err:
        print("ERROR WHILE INSERTING DATA IN"+tablename) 
        print(f"Unexpected {err=}, {type(err)=}")
    connection.commit()
    connection.close()
    print("disconnected")
 
if __name__ == "__main__":
    main()

By investigating a bit further, I've discovered the line you should avoid in your SSLConfig.properties : 

# cipherSuites = TLS_AES_256_GCM_SHA384

By commenting it, everything's working.

But again, you only need 2 settings to make it work :

trustStore = keystore.jks
trustStorePassword = 123456