go to post Sylvain Guilbaud · Mar 21 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(); } }
go to post Sylvain Guilbaud · Mar 20 Congrats @Robert Cemper 👏Your tireless contribution to the community is a model that no one will be able to match.
go to post Sylvain Guilbaud · Mar 11 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 :
go to post Sylvain Guilbaud · Feb 7 Thanks to @Vitaliy Serdtsev : it is also possible to directly retrieve JSON from an SQL query, with the functionsJSON_ARRAYAGG and JSON_OBJECT : SELECT JSON_ARRAYAGG(json_obj) FROM (SELECT TOP 5 JSON_OBJECT( 'Name':name ,'Age':age ,'DOB':to_char(dob,'Day DD Month YYYY') ) json_obj FROM sample.person ) SELECT JSON_ARRAYAGG(json_obj) FROM (SELECT JSON_OBJECT( 'Name':name ,'Age':age ,'DOB':to_char(dob,'Day DD Month YYYY') ) json_obj FROM sample.person ) WHERE %VID BETWEEN 1 AND 5 Result :
go to post Sylvain Guilbaud · Feb 5 Congrats @José Roberto Pereira, @Henry Pereira Pereira, @Henrique Dias , @Muhammad Waseem, @Flavio Naves, Denis Kiselev, Maria Ogienko, Anastasia Samoilova, Kseniya Hoar and all the participants to this FHIR contest !!
go to post Sylvain Guilbaud · Feb 5 Hello @Ashok Kumar 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.
go to post Sylvain Guilbaud · Feb 1 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"}]}
go to post Sylvain Guilbaud · Feb 1 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"}]}
go to post Sylvain Guilbaud · Feb 1 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" } ] }
go to post Sylvain Guilbaud · Jan 30 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.
go to post Sylvain Guilbaud · Jan 29 Thanks @Alberto Fuentes for this very good example of LLM + Embedded Python.
go to post Sylvain Guilbaud · Jan 29 Welcome to Developer Community moderators @Enrico Parisi 'Hoping you're doing well.
go to post Sylvain Guilbaud · Jan 27 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.
go to post Sylvain Guilbaud · Jan 25 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()
go to post Sylvain Guilbaud · Jan 25 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
go to post Sylvain Guilbaud · Jan 25 A big thank to @Evgeny Shvarov who made my day 😁 By simply removing extra lines in the SSLSetting.properties in order to only keep these 2 lines : trustStore = /Users/guilbaud/keystore.jks trustStorePassword = 123456 Everything works now in DBeaver :