Si vous migrez d'Oracle vers InterSystems IRIS, comme beaucoup de mes clients, vous risquez de rencontrer des modèles SQL spécifiques à Oracle nécessitant une conversion.

Prenons l'exemple suivant:

SELECT (TO_DATE('2023-05-12','YYYY-MM-DD') - LEVEL + 1) AS gap_date
FROM dual
CONNECT BY LEVEL <= (TO_DATE('2023-05-12','YYYY-MM-DD') - TO_DATE('2023-05-02','YYYY-MM-DD') + 1);

Dans Oracle:

• LEVEL est une pseudo-colonne utilisée dans les requêtes hiérarchiques (CONNECT BY). Elle commence à 1 et s'incrémente de 1.
• CONNECT BY LEVEL <= (...) détermine le nombre de lignes à générer.
• La différence entre les deux dates plus un donne 11, donc la requête génère 11 lignes, en comptant à rebours à partir du 12 mai 2023 jusqu'au 2 mai 2023.

Répartition du résultat:

LEVEL = 1  → 2023-05-12
LEVEL = 2  → 2023-05-11
...
LEVEL = 11 → 2023-05-02

La question est maintenant de savoir comment obtenir ce résultat dans InterSystems IRIS, qui ne prend pas en charge CONNECT BY?

Une solution consiste à implémenter une requête de type SQL à l'aide d'ObjectScript qui imite ce comportement. Vous trouverez ci-dessous un exemple de définition CREATE QUERY qui accepte une date de début STARTDATE et un nombre de jours DAYS, et renvoie la liste des dates par ordre descendant.

✅ InterSystems IRIS: mise en œuvre d'une requête de l'intervalle de date

CREATE QUERY GET_GAP_DATE(IN STARTDATE DATE, IN DAYS INT)
  RESULTS (GAP_DATE DATE)
  PROCEDURE
  LANGUAGE OBJECTSCRIPT
Execute(INOUT QHandle BINARY(255), IN STARTDATE DATE, IN DAYS INT)
{
SET QHandle("start") = STARTDATE
SET QHandle("days")  = DAYS
SET QHandle("level") = 1
RETURN $$$OK
}
Fetch(INOUT QHandle BINARY(255), INOUT Row %List, INOUT AtEnd INT)
{
IF (QHandle("level") > QHandle("days")) {
SET Row = ""
SET AtEnd = 1
} ELSE {
SET Row = $ListBuild(QHandle("start") - QHandle("level") + 1)
SET QHandle("level") = QHandle("level") + 1
}
RETURN $$$OK
}
Close(INOUT QHandle BINARY(255))
{
KILL QHandle
QUIT $$$OK
}

Vous pouvez exécuter la commande CREATE QUERY Vous pouvez exécuter la commande CREATE QUERY ci-dessus dans le portail IRIS System Management, ou via un outil tel que DBeaver ou un éditeur Python/Jupyter Notebook utilisant JDBC/ODBC.

🧪 Exemple d'utilisation:

Pour générer le même résultat que la requête Oracle ci-dessus, utilisez:

SELECT * FROM GET_GAP_DATE(
  TO_DATE('2023-05-12', 'YYYY-MM-DD'),
  TO_DATE('2023-05-12', 'YYYY-MM-DD') - TO_DATE('2023-05-02', 'YYYY-MM-DD') + 1
);

Cela donnera le résultat suivant:

GAP_DATE
----------
2023-05-12
2023-05-11
...
2023-05-02
(11 rows)

🔁 Utilisation avancée: Jointure avec d'autres tables

Vous pouvez également utiliser cette requête comme sous-requête ou dans des jointures:

SELECT * 
FROM GET_GAP_DATE(TO_DATE('2023-05-12', 'YYYY-MM-DD'), 11) 
CROSS JOIN dual;

Cela vous permet d'intégrer des plages de dates dans des flux SQL plus importants.

J'espère que cela sera utile à tous ceux qui sont confrontés à des scénarios de migration d'Oracle vers IRIS ! Si vous avez mis au point des solutions alternatives ou si vous proposez des améliorations, n'hésitez pas à me faire part de vos commentaires.

J'écris cet article principalement pour recueillir un consensus informel sur la façon dont les développeurs utilisent Python avec IRIS. N'hésitez donc pas à répondre au sondage à la fin de cet article ! Dans le corps de l'article, je détaillerai chaque choix proposé, ainsi que ses avantages, mais n'hésitez pas à le parcourir et à simplement répondre au sondage.

Problèmes de chaînes

J'utilise Python pour accéder aux bases de données IRIS avec JDBC (ou ODBC). Je veux récupérer les données dans pandas dataframe pour manipuler les données et créer des graphiques à partir de celles-ci. Lors de l'utilisation de JDBC, j'ai rencontré un problème avec la gestion des chaînes. Cet article est destiné à aider les personnes qui ont les mêmes problèmes. S'il existe un moyen plus simple de résoudre ce problème, faites-le moi savoir dans les commentaires !

J'utilise OSX, donc je ne sais pas à quel point mon problème est unique. J'utilise Jupyter Notebooks, mais le code serait généralement le même si vous utilisiez n'importe quel autre programme ou cadre en Python.

Le problème JDBC

Lorsque je récupère des données de la base de données, les descriptions de colonnes et toutes les données de type chaîne sont renvoyées en tant que données de type java.lang.String. Si vous imprimez une chaîne de données, elle se présentera sous la forme suivante : "(p,a,i,n,i,n,t,h,e,r,e,a,r)" au lieu de l'attendu "painintherear".

Ceci est probablement dû au fait que les chaînes de caractères de type java.lang.String se présentent sous la forme d'un itérable ou d'un tableau lorsqu'elles sont récupérées à l'aide de JDBC. Cela peut arriver si le pont Python-Java que vous utilisez (par exemple, JayDeBeApi, JDBC) ne convertit pas automatiquement java.lang.String en une chaîne Python en une seule étape.

La représentation de la chaîne str de Python, en revanche, dispose de la chaîne entière en tant qu'unité unique. Lorsque Python récupère une chaîne normale (par exemple via ODBC), elle n'est pas divisée en caractères individuels.

La solution JDBC

Pour résoudre ce problème, vous devez vous assurer que le type java.lang.String est correctement converti en type str de Python. Vous pouvez explicitement gérer cette conversion lors du traitement des données récupérées afin qu'elles ne soient pas interprétées comme un itérable ou une liste de caractères.

Il existe de nombreuses façons de manipuler les chaînes de caractères ; c'est ce que j'ai fait.

import pandas as pd

import pyodbc

import jaydebeapi
import jpype

def my_function(jdbc_used)

    # Un autre code pour créer la connexion se trouve ici

    cursor.execute(query_string)

    if jdbc_used:
        # Récupération des résultats, conversion des données java.lang.String en str Python
        # (java.lang.String est retourné comme suit : "(p,a,i,n,i,n,t,h,e,r,e,a,r)" Conversion en type str "painintherear"
        results = []
        for row in cursor.fetchall():

            converted_row = [str(item) if isinstance(item, jpype.java.lang.String) else item for item in row]

            results.append(converted_row)


        # Obtention des noms des colonnes et vérification qu'il s'agit bien de chaînes Python

        column_names = [str(col[0]) for col in cursor.description]


        # Création du cadre de données

        df = pd.DataFrame.from_records(results, columns=column_names)


        # Vérification des résultats

        print(df.head().to_string())


    else:

        # Je testais aussi ODBC
        # Dans le cas d'ensembles de résultats très volumineux, obtenez les résultats par blocs en utilisant cursor.fetchmany() ou fetchall()
        results = cursor.fetchall()
        # Obtention des noms des colonnes
        column_names = [column[0] for column in cursor.description]
        # Création du cadre de données
        df = pd.DataFrame.from_records(results, columns=column_names)

    # Faites des choses avec votre cadre de données

Le problème ODBC

Lors d'une connexion ODBC, les chaînes de caractères ne sont pas renvoyées ou sont S.O.

Si vous vous connectez à une base de données qui contient des données Unicode (par exemple, des noms dans des langues différentes) ou si votre application doit stocker ou récupérer des caractères qui ne sont pas ASCII, vous devez vous assurer que les données restent correctement encodées lorsqu'elles sont transmises entre la base de données et votre application Python.

La solution ODBC

Ce code garantit que les données de type chaîne sont encodées et décodées en utilisant UTF-8 lors de l'envoi et de la récupération de données dans la base de données. C'est particulièrement important lorsqu'il s'agit de caractères non ASCII ou d'assurer la compatibilité avec les données Unicode.

def create_connection(connection_string, password):
    connection = None

try:
        # print(f"Connecting to {connection_string}")
        connection = pyodbc.connect(connection_string + ";PWD=" + password)

        # Veiller à ce que les chaînes de caractères soient lues correctement
        connection.setdecoding(pyodbc.SQL_CHAR, encoding="utf8")
        connection.setdecoding(pyodbc.SQL_WCHAR, encoding="utf8")
        connection.setencoding(encoding="utf8")

    except pyodbc.Error as e:
        print(f"The error '{e}' occurred")

    return connection

connection.setdecoding(pyodbc.SQL_CHAR, encoding="utf8")

Désigné ci-dessus indique à pyodbc comment décoder les données de caractères de la base de données lors de la récupération des types SQL_CHAR (typiquement, les champs de caractères de longueur fixe).

connection.setdecoding(pyodbc.SQL_WCHAR, encoding="utf8")

Désigné ci-dessus définit le décodage pour SQL_WCHAR, les types de caractères larges (c'est-à-dire les chaînes Unicode, telles que NVARCHAR ou NCHAR dans SQL Server).

connection.setencoding(encoding="utf8")

Désigné ci-dessus garantit que toutes les chaînes ou données de caractères envoyées de Python à la base de données seront encodées en UTF-8, cela signifie que Python traduira son type str interne (qui est Unicode) en octets UTF-8 lors de la communication avec la base de données.

La mise en place de l'ensemble

Installation de JDBC

Installation de JAVA-utiliser dmg

https://www.oracle.com/middleeast/java/technologies/downloads/#jdk23-mac

Mise à jour du shell pour définir la version par défaut

$ /usr/libexec/java_home -V
Matching Java Virtual Machines (2):
    23 (arm64) "Oracle Corporation" - "Java SE 23" /Library/Java/JavaVirtualMachines/jdk-23.jdk/Contents/Home
    1.8.421.09 (arm64) "Oracle Corporation" - "Java" /Library/Internet Plug-Ins/JavaAppletPlugin.plugin/Contents/Home
/Library/Java/JavaVirtualMachines/jdk-23.jdk/Contents/Home
$ echo $SHELL
/opt/homebrew/bin/bash
$ vi ~/.bash_profile

Ajoutez JAVA_HOME à votre chemin

export JAVA_HOME=$(/usr/libexec/java_home -v 23)
export PATH=$JAVA_HOME/bin:$PATH

Obtention du pilote JDBC

https://intersystems-community.github.io/iris-driver-distribution/

Placement du fichier jar quelque part... Je l'ai placé dans $HOME

$ ls $HOME/*.jar
/Users/myname/intersystems-jdbc-3.8.4.jar

Exemple de code

Cela suppose que vous avez configuré ODBC (un exemple à suivre un autre jour, le chien a mangé mes notes...).

Remarque : il s'agit d'un piratage de mon propre code réel. Notez les noms des variables.

import os

import datetime
from datetime import date, time, datetime, timedelta

import pandas as pd
import pyodbc

import jaydebeapi
import jpype

def jdbc_create_connection(jdbc_url, jdbc_username, jdbc_password):

    # Chemin d'accès au pilote JDBC
    jdbc_driver_path = '/Users/yourname/intersystems-jdbc-3.8.4.jar'

    # Il faut s'assurer que JAVA_HOME est défini 
    os.environ['JAVA_HOME']='/Library/Java/JavaVirtualMachines/jdk-23.jdk/Contents/Home'
    os.environ['CLASSPATH'] = jdbc_driver_path

    # Démarrage de la JVM (si elle n'est pas déjà en cours d'exécution)
    if not jpype.isJVMStarted():
        jpype.startJVM(jpype.getDefaultJVMPath(), classpath=[jdbc_driver_path])

    # Connexion aux bases de données:
    connection = None

    try:
        connection = jaydebeapi.connect("com.intersystems.jdbc.IRISDriver",
                                  jdbc_url,
                                  [jdbc_username, jdbc_password],
                                  jdbc_driver_path)
        print("Connection successful")
    except Exception as e:
        print(f"An error occurred: {e}")

    return connection


def odbc_create_connection(connection_string):
    connection = None

    try:
        # print(f"Connecting to {connection_string}")
        connection = pyodbc.connect(connection_string)

        # Veiller à ce que les chaînes de caractères soient lues correctement
        connection.setdecoding(pyodbc.SQL_CHAR, encoding="utf8")
        connection.setdecoding(pyodbc.SQL_WCHAR, encoding="utf8")
        connection.setencoding(encoding="utf8")

    except pyodbc.Error as e:
        print(f"The error '{e}' occurred")

    return connection

# Paramètres

odbc_driver = "InterSystems ODBC"
odbc_host = "your_host"
odbc_port = "51773"
odbc_namespace = "your_namespace"
odbc_username = "username"
odbc_password = "password"

jdbc_host = "your_host"
jdbc_port = "51773"
jdbc_namespace = "your_namespace"
jdbc_username = "username"
jdbc_password = "password"

# Création d'une connexion et des graphiques

jdbc_used = True

if jdbc_used:
    print("Using JDBC")
    jdbc_url = f"jdbc:IRIS://{jdbc_host}:{jdbc_port}/{jdbc_namespace}?useUnicode=true&characterEncoding=UTF-8"
    connection = jdbc_create_connection(jdbc_url, jdbc_username, jdbc_password)
else:
    print("Using ODBC")
    connection_string = f"Driver={odbc_driver};Host={odbc_host};Port={odbc_port};Database={odbc_namespace};UID={odbc_username};PWD={odbc_password}"
    connection = odbc_create_connection(connection_string)


if connection is None:
    print("Unable to connect to IRIS")
    exit()

cursor = connection.cursor()

site = "SAMPLE"
table_name = "your.TableNAME"

desired_columns = [
    "RunDate",
    "ActiveUsersCount",
    "EpisodeCountEmergency",
    "EpisodeCountInpatient",
    "EpisodeCountOutpatient",
    "EpisodeCountTotal",
    "AppointmentCount",
    "PrintCountTotal",
    "site",
]

# Construction de la partie de la requête relative à la sélection des colonnes
column_selection = ", ".join(desired_columns)

query_string = f"SELECT {column_selection} FROM {table_name} WHERE Site = '{site}'"

print(query_string)
cursor.execute(query_string)

if jdbc_used:
    # Récupération des résultats
    results = []
    for row in cursor.fetchall():
        converted_row = [str(item) if isinstance(item, jpype.java.lang.String) else item for item in row]
        results.append(converted_row)

    #  Il faut récupérer les noms des colonnes et s'assurer qu'il s'agit bien de chaînes Python (java.lang.String is returned "(p,a,i,n,i,n,t,h,e,a,r,s,e)" 
    column_names = [str(col[0]) for col in cursor.description]

    # Création du cadre de données
    df = pd.DataFrame.from_records(results, columns=column_names)
    print(df.head().to_string())
else:
    # Dans le cas d'ensembles de résultats très volumineux, obtenez les résultats par blocs en utilisant cursor.fetchmany() ou fetchall()
    results = cursor.fetchall()
    # Obtention des noms des colonnes
    column_names = [column[0] for column in cursor.description]
    # Création du cadre de données
    df = pd.DataFrame.from_records(results, columns=column_names)

    print(df.head().to_string())

# # Construction des graphiques pour un site
# cf.build_7_day_rolling_average_chart(site, cursor, jdbc_used)

cursor.close()
connection.close()

# Arrêt de la JVM (si vous l'avez démarrée)
# jpype.shutdownJVM()

Nous sommes ravis de continuer à déployer de nouvelles fonctionnalités dans InterSystems IRIS Cloud SQLInterSystems IRIS Cloud SQL, telles que la nouvelle capacité de recherche vectorielle Vector Search qui a été lancée pour la première fois avec InterSystems IRIS 2024.1. Le service Cloud SQL est un service en nuage qui offre précisément ce qui suit: l'accès SQL dans le cloud. Cela signifie que vous utiliserez des technologies de pilote standard telles que JDBC, ODBC et DB-API pour vous connecter à ce service et accéder à vos données. La documentation décrit en détail comment configurer les paramètres importants au niveau du pilote, mais ne présente pas les outils tiers spécifiques car, comme vous pouvez l'imaginer, il en existe un nombre infini.

Dans cet article, nous allons compléter cette documentation de référence avec des étapes plus détaillées pour un outil de visualisation de données tiers populaire que plusieurs de nos clients utilisent pour accéder aux données basées sur IRIS : Microsoft Power BI.

Étape 0: Création de votre déploiement

Tout d'abord, connectez-vous au portail des services Cloud Services Portal et créez un déploiement Cloud SQL. Il vous ne faut qu'une chose: cochez la case permettant d'activer les connexions externes. À part cela, tous les paramètres par défaut devraient fonctionner correctement.

Étape 1: Téléchargement du certificat

Afin de se connecter en toute sécurité, nous utiliserons des certificats pour crypter tout ce qui est envoyé sur le réseau. Vous pouvez télécharger le certificat à partir de la page des détails du déploiement en cliquant sur le bouton "Get X.509 certificate" (Obtenir un certificat X.509):

Nous devrons nous référer à ce certificat plus tard, il faudra donc l'enregistrer dans un répertoire approprié. Par exemple, j'utilise C:\Users\bdeboe\odbc\.

Étape 2: Création du fichier SSLDefs.ini

Pour savoir quels certificats et paramètres de cryptage utiliser, le pilote ODBC d'InterSystems recherche un fichier SSLDefs.ini, que nous allons créer ensuite. Par défaut, il recherche ce fichier dans  C:\Program Files (x86)\Common Files\InterSystems\IRIS, mais vous pouvez modifier cet emplacement à l'aide de la variable d'environnement ISC_SSLconfigurationsISC_SSLconfigurations. Pour conserver tous mes paramètres de configuration ensemble, j'ai défini cette variable à C:\Users\bdeboe\odbc\, le répertoire où j'ai également sauvegardé mon certificat.

Le fichier SSLDefs.ini doit contenir deux éléments pour qu'ODBC sache comment se connecter : une configuration de serveur et une configuration SSL. La configuration du serveur déclare simplement le nom de la configuration SSL à utiliser pour une combinaison particulière de nom d'hôte et de port, et la configuration SSL contient tous les détails nécessaires à l'établissement de la connexion cryptée. Il est donc facile de réutiliser une seule configuration SSL pour plusieurs serveurs. Voici le contenu de mon fichier SSLDefs.ini:

[My CloudSQL Server]
Address=k8s-da0bcd5e-a1b3a0c7-545df92ec8-2e44304cebef1543.elb.us-east-1.amazonaws.com
Port=443
SSLConfig=SampleSSLConfig
[SampleSSLConfig]
CAFile=
CertFile=C:\Users\bdeboe\odbc\certificateSQLaaS.pem
KeyFile=
Password=
KeyType=2
Protocols=28
CipherList=ALL:!aNULL:!eNULL:!EXP:!SSLv2
VerifyPeer=0
VerifyDepth=9

La première section contient la configuration du serveur auquel vous pouvez donner un nom de votre choix. Vous devrez modifier l'adresse pour qu'elle corresponde au nom d'hôte de votre déploiement Cloud SQL, qui peut être obtenu à partir de la page des détails du déploiement où vous avez téléchargé le certificat.

La deuxième section contient la configuration SSL, dont le nom doit correspondre à ce que vous avez spécifié pour SSLConfig dans la section de configuration du serveur. La valeurCertFile doit évidemment correspondre à l'endroit où vous avez sauvegardé votre certificat.

Pour plus de détails sur les autres paramètres, veuillez vous référer à la documentation complète sur les paramètres TLS.

Étape 3: Création du DSN de type ODBC

Power BI, comme la plupart des outils basés sur ODBC, fonctionne avec un DSN (Data Source Name), que vous enregistrez à l'aide d'un utilitaire Windows. Il suffit de cliquer sur l'icône Start (Démarrer) de Windows et de taper "ODBC", puis de cliquer sur "ODBC Data Sources (64 bit)" (Sources de données ODBC (64 bit)). Choisissez l'onglet "System DSN" (DSN du système) pour enregistrer une connexion à notre déploiement Cloud SQL. Si vous avez des installations locales d'InterSystems IRIS (j'en ai une douzaine à tout moment 😉), vous verrez que le programme d'installation a créé des entrées DSN par défaut pour:

Cliquez sur "Add..." (Ajouter...) pour créer un nouveau DSN, en choisissant un nom et en renseignant l'hôte, le port et l'espace de noms. J'enregistre habituellement mon nom d'utilisateur et mon mot de passe dans le DSN pour toute instance non productive (et nous en aurons besoin pour tester la connexion dans quelques instants), mais vous pouvez laisser ces champs vides et fournir ces informations d'identification plus tard. Le champ Nom du serveur SSL/TLS est quelque peu superflu (nous y travaillerons!).

 Ensuite, cliquez sur "Test Connection" (Tester la connexion) pour vérifier que tout fonctionne comme prévu. Vous devriez obtenir un message "Connectivity test completed successfully!" (Test de connectivité terminé avec succès). Si ce n'est pas le cas, vérifiez les étapes ci-dessus ou reportez-vous au Guide de dépannage. Un message d'erreur particulier peut vous laisser perplexe (en tout cas, il m'a laissé perplexe !): "No SSL config found in the registry or in ssldefs.ini" (Aucune configuration SSL trouvée dans le registre ou dans ssldefs.ini). Cette erreur signifie que le pilote ODBC n'a pas trouvé de correspondance pour votre combinaison nom d'hôte/port dans le fichier SSLDefs.ini. Comme votre nom d'hôte change à chaque fois que vous créez un nouveau déploiement, vous devrez mettre à jour ou ajouter les configurations de serveur pour chacun d'entre eux.

Étape 4: Connexion à partir de Power BI

Il est maintenant temps d'utiliser notre DSN ODBC pour extraire des données dans Power BI. Après avoir ouvert l'application, sélectionnez la commande "Get Data" (Obtenir des données) ou "Get data from other sources" (Obtenir des données à partir d'autres sources), et choisissez l'option ODBC:

Ensuite, choisissez le DSN que vous venez de créer dans la liste:

Dans l'écran suivant, vous pouvez indiquer votre nom d'utilisateur et votre mot de passe. Aucune propriété des informations d'identification supplémentaire n'est requise.

 Et c'est tout! Vous pouvez maintenant sélectionner les tables que vous souhaitez inclure dans vos rapports Power BI:

Comme vous l'avez probablement remarqué, toutes les étapes de cet article, à l'exception de la dernière, sont universelles pour la configuration ODBC, ce qui devrait vous permettre d'utiliser la plupart des outils basés sur ODBC. J'espère que cet article vous a permis d'avancer, et n'hésitez pas à ajouter vos propres trucs et astuces, ou à partager vos expériences sur la connexion à Cloud SQL. Notez également qu'il n'y a pratiquement rien ici, à part l'étape 0, qui soit spécifique à Cloud SQL, donc vous pouvez utiliser les mêmes étapes pour vous connecter à n'importe quelle instance IRIS qui nécessite des connexions cryptées.

InterSystems FAQ rubric

Pour désactiver le délai d'attente, réglez le délai d'attente de la requête sur désactivé dans les paramètres DSN :

Panneau de configuration de Windows > Outils d'administration > Sources de données (ODBC) > Configuration DSN du système

Si vous cochez la case Désactiver le délai d'attente des requêtes, le délai d'attente sera désactivé.

Si vous souhaitez le modifier du côté de l'application, vous pouvez le définir au niveau de l'API ODBC.

Bonjour,Je me débattais avec une procédure qui devait recevoir une chaîne de caractères et l'utiliser comme filtre, j'ai découvert que puisque je voulais que la procédure fasse une transformation de données et retourne un ensemble de données, j'avais besoin d'utiliser le langage objectScript.J'ai créé la procédure en utilisant l'interface graphique SQL dans le portail, et tout fonctionne correctement lorsque j'appelle la procédure à partir de l'interface graphique SQL, mais pas à travers une connexion JDBC - voici l'appel "call spPatientOS('2024-04-07T12:35:32Z')".Le résultat est que la

Bonjour, j'espère que ce billet vous aidera.

Introduction

Il existe une option d'assistant de procédure de liaison dans le portail de gestion (Système > SQL > Assistants > Procédure de liaison) avec laquelle j'ai eu des problèmes de fiabilité, j'ai donc décidé d'utiliser cette solution à la place.

Problème

Bonjour,

Je veux créer une vue pour exposer une requête SQL.

Voici ma vue :

Class Client.TEST-REQ1 [ ClassType = view
                        , CompileAfter = Client.Client
                        , DdlAllowed
                        , Owner = {UnknownUser}
                        , Not ProcedureBlock
                        , SqlTableName = "TEST-O-REQ-1"
                        , ViewQuery = { 
                            SELECT NumeroClient, NomClient, ModeReglement.CodeModeReglement as ModeReglement_CodeModeReglement FROM Client.Client
                        }]
{

}

Récemment, la question s'est posée en discutant avec mes étudiants de l'accès aux données stockées dans IRIS depuis différentes langues : s'il était possible d'initier la connexion et d'obtenir les données de la solution Cloud (InterSystems IRIS CloudSQL) à partir de Microsoft Excel, et non l'inverse. Compte tenu des nombreuses façons différentes d'obtenir des données dans Excel (importation de données à partir de sources externes, connexion à des bases de données à l'aide de pilotes ODBC, utilisation de requêtes avancées et de requêtes Web, etc.), le choix évident était d'essayer le pilote ODBC. La seule tâche qui restait était d'essayer de se connecter à la base de données dans le cloud à l'aide du pilote ODBC.

InterSystems FAQ rubric

Si plusieurs produits InterSystems sont installés sur le même système, la dernière version du pilote ODBC InterSystems parmi les produits installés restera enregistrée dans le gestionnaire de pilotes.

Vous pouvez passer à n'importe quel pilote en modifiant l'entrée de la registry ci-dessous.

Veuillez noter que l'exécution de RegFiles.bat ne modifie pas le pilote ODBC.

L'entrée de la registry est la suivante.

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\InterSystems ODBC35 key Driver
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\InterSystems ODBC35 key Setup

Salut la Communauté!

Découvrez notre première vidéo, doublée grâce à l'intelligence artificielle (IA) !

Profitez de regarder la nouvelle vidéo sur le moyen de se connecter aux InterSystems Cloud Services à partir de l'application C++, à l'aide du pilote ODBC InterSystems.

📺 Connexion aux InterSystems Cloud Services avec ODBC

Découvrez les étapes simples et rapides de configuration des connexions ODBC/JDBC à la plate-forme de données InterSystems IRIS® et InterSystems IRIS for Health™ :

• Configuration des connexions JDBC
• Configuration des connexions ODBC

Maintenant dans un format de liste de contrôle facile à utiliser pour vous aider à suivre le processus sur votre propre système.

Il a été demandé à plusieurs reprises récemment, comment on peut faire fonctionner Laravel Framework avec InterSystems IRIS Data Platform. Cela fait un moment que cet article sur Laravel et InterSystems Caché n’a pas été publié. Pour le mettre à jour, l’article donne un bref ensemble d’instructions sur la façon d’installer et de configurer un projet Laravel pour une utilisation avec InterSystems IRIS via ODBC.

Qu’est-ce que Laravel?

Laravel est un framework PHP basé sur l’architecture MVC. L’utilisation de Laravel simplifie et accélère le développement backend tout en créant des applications Web sécurisées modernes. Il est très convivial pour les débutants, largement utilisé dans le monde PHP et tend à être le framework backend le plus populaire selon github.com nombre d’étoiles mesuré dans cette vidéo. La combinaison de tout cela avec la flexibilité et les capacités de performance fournies par InterSystems IRIS en tant que base de données est considérée comme bénéfique pour les deux mondes.

Ce post est organisé en 4 étapes qui représentent une séquence d’actions à effectuer pour que la connexion fonctionne. Les façons spécifiques d’effectuer chaque étape peuvent varier en fonction de la plate-forme. Les commandes ici sont affichées pour Ubuntu 22.02 (x64).

Comme nous le savons tous, Caché est une excellente base de données qui accomplit de nombreuses tâches en son sein. Cependant, que faites-vous lorsque vous avez besoin d'accéder à une base de données externe ? Une façon de le faire est d'utiliser la passerelle Caché SQL Gateway via JDBC. Dans cet article, mon objectif est de répondre aux questions suivantes pour vous aider à vous familiariser avec cette technologie et à déboguer certains problèmes courants.

Plan de travail

• Quels sont les paramètres de connexion dont vous avez besoin pour vous connecter à une base de données distante ?
• Qu'est-ce que la passerelle JDBC Gateway et le service de passerelle Java Gateway Service dans Ensemble ?
• Quels outils et méthodes sont disponibles pour déboguer les problèmes ?
• Quels sont les types de problèmes courants et les approches pour les résoudre ?

Avant de se plonger dans ces questions, discutons rapidement de l'architecture de la passerelle JDBC SQL Gateway. Pour simplifier, vous pouvez considérer que l'architecture est la suivante : Cache établit une connexion TCP avec un processus Java, appelé processus de passerelle. Le processus de passerelle se connecte ensuite à une base de données distante, telle que Caché, Oracle ou SQL Server, en utilisant le pilote spécifié pour cette base de données. Pour plus d'informations sur l'architecture de la passerelle SQL Gateway, veuillez consulter la documentation sur Utilisation de la passerelle Caché SQL Gateway.

Paramètres de connexion

Lorsque vous vous connectez à une base de données distante, vous devez fournir les paramètres suivants :

• nom d'utilisateur
• mot de passe
• nom du pilote
• URL
• chemin de classe

Connexion à la base de données Caché

Par exemple, si vous avez besoin de vous connecter à une instance de Caché en utilisant la passerelle SQL Gateway via JDBC, vous devez naviguer vers [System Administration] -> [Configuration] -> [Connectivity] -> [SQL Gateway Connections] dans le portail de gestion du système (SMP). Cliquez ensuite sur "Créer une nouvelle connexion" et spécifiez "JDBC" comme type de connexion.

Lors de la connexion à un système Caché, le nom du pilote doit toujours être com.intersys.jdbc.CacheDriver, comme indiqué dans la capture d'écran. Si vous vous connectez à une base de données tierce, vous devrez utiliser un nom de pilote différent (voir Connexion à des bases de données tierces ci-dessous).

Lorsque vous vous connectez aux bases de données Caché, vous n'avez pas besoin de spécifier un chemin de classe car le fichier JAR est téléchargé automatiquement.

Le paramètre URL varie également en fonction de la base de données à laquelle vous vous connectez. Pour les bases de données Caché, vous devez utiliser une URL de la forme suivante

jdbc:Cache://[server_address]:[superserver_port]/[namespace]

Connexion à des bases de données tierces

Une base de données tierce courante est Oracle. Un exemple de configuration est présenté ci-dessous.

Comme vous pouvez le constater, le nom du pilote et l'URL ont des caractéristiques différentes de celles que nous avons utilisées pour la connexion précédente. En outre, j'ai spécifié un chemin de classe dans cet exemple, car je dois utiliser le pilote d'Oracle pour me connecter à leur base de données.

Comme vous pouvez l'imaginer, SQL Server utilise différents modèles d'URL et de noms de pilotes.

Vous pouvez tester si les valeurs sont valides en cliquant sur le bouton " Testez la connexion ". Pour créer la connexion, cliquez sur "Enregistrer".

JDBC Gateway vs le service Java Gateway Business Service

Tout d'abord, la passerelle JDBC et le service de passerelle Java sont complètement indépendants l'un de l'autre. La passerelle JDBC peut être utilisée sur tous les systèmes basés sur Caché, alors que le service de passerelle Java n'existe que dans le cadre d'Ensemble. En outre, le service de passerelle Java utilise un processus différent de celui utilisé par la passerelle JDBC. Pour plus de détails sur le service commercial de passerelle Java, veuillez consulter Le service commercial de passerelle Java.

Méthodes et outils

Vous trouverez ci-dessous 5 outils et méthodes couramment utilisés pour résoudre des problèmes avec la passerelle JDBC SQL Gateway. Je vais d'abord parler de ces outils et vous montrer quelques exemples de leur utilisation dans la section suivante.

1. Journaux

A. Journal du pilote et journal de la passerelle

Lorsque vous utilisez la passerelle JDBC, le journal correspondant est le journal de la passerelle JDBC SQL. Comme nous l'avons vu précédemment, la passerelle JDBC est utilisée lorsque Caché doit accéder à des bases de données externes, ce qui signifie que Caché est le client. Le journal du pilote, par contre, correspond à l'utilisation du pilote JDBC d'InterSystems pour accéder à une base de données Caché à partir d'une application externe, ce qui signifie que Caché est le serveur. Si vous avez une connexion d'une base de données Caché à une autre base de données Caché, les deux types de journaux peuvent être utiles.

Dans notre documentation la section relative à l'activation du journal du pilote est intitulée "Activation de la journalisation pour JDBC", et la section relative à l'activation du journal de la passerelle est intitulée "Activation de la journalisation pour la passerelle SQL JDBC".

Même si les deux journaux comportent le mot "JDBC", ils sont totalement indépendants. L'objet de cet article est la passerelle JDBC, c'est pourquoi j'aborderai plus en détail le journal de la passerelle. Pour plus d'informations sur le journal du pilote, veuillez vous reporter à la section Activation du journal du pilote.

B. Activation du journal du pilote

Si vous utilisez la passerelle Caché JDBC SQL Gateway, vous devez effectuer les opérations suivantes pour activer la journalisation : dans le portail de gestion, allez dans [System Administration] > [Configuration] > [Connectivity] > [JDBC Gateway Settings]. Indiquez une valeur pour le journal de la passerelle JDBC. Ce doit être le chemin complet et le nom d'un fichier journal (par exemple, /tmp/jdbcGateway.log). Le fichier sera automatiquement créé s'il n'existe pas, mais le répertoire ne le sera pas. Caché va démarrer la passerelle JDBC SQL Gateway avec journalisation pour vous.

Si vous utilisez le service commercial Java Gateway dans Ensemble, veuillez consulter Activation de la journalisation de la passerelle Java Gateway dans Ensemble pour savoir comment activer la journalisation.

C. Analyse du journal d'une passerelle

Maintenant que vous avez collecté un journal de passerelle, vous vous posez peut-être la question suivante : quelle est la structure du journal et comment le lire ? Bonne question ! Je vais vous fournir ici quelques informations de base pour vous aider à démarrer. Malheureusement, il n'est pas toujours possible d'interpréter complètement le journal sans avoir accès au code source. Pour les situations complexes, n'hésitez pas à contacter le WRC (Centre de réponse global d'InterSystems) !

Pour démystifier la structure du journal, rappelez-vous qu'il s'agit toujours d'un morceau de données suivi d'une description de ce qu'il fait. Par exemple, voyez cette image avec une coloration syntaxique de base :

Afin de comprendre ce que Received signifie ici, vous devez vous rappeler que le journal de la passerelle enregistre les interactions entre la passerelle et la base de données descendante. Ainsi, Received signifie que la passerelle a reçu l'information de Caché/Ensemble. Dans l'exemple ci-dessus, la passerelle a reçu le texte d'une requête SELECT. Les significations des différentes valeurs de msgId peuvent être trouvées dans le code interne. Le 33 que nous voyons ici signifie " Preparer l'instruction ".

Le journal lui-même fournit également des informations sur le pilote, ce qui est intéressant à vérifier lors du débogage des problèmes. Voici un exemple,

Comme nous pouvons le voir, le Driver Name est com.intersys.jdbc.CacheDriver, ce qui est le nom du pilote utilisé pour se connecter au processus de passerelle. Le Jar File Name est cachejdbc.jar, ce qui est le nom du fichier jar situé dans <cache_install_directory>\lib\.

2. Trouver le processus de passerelle

Pour trouver le processus de passerelle, vous pouvez exécuter la commande ps. Par exemple,

ps -ef | grep java

Cette commande ps affiche des informations sur le processus Java, notamment le numéro de port, le fichier jar, le fichier journal, l'ID du processus Java et la commande qui a lancé le processus Java.

Voici un exemple du résultat de la commande :

mlimbpr15:~ mli$ ps -ef | grep java
17182 45402 26852   0 12:12PM ??         0:00.00 sh -c java -Xrs -classpath /Applications/Cache20151/lib/cachegateway.jar:/Applications/Cache20151/lib/cachejdbc.jar com.intersys.gateway.JavaGateway 62972 /Applications/Cache20151/mgr/JDBC.log 2>&1
17182 45403 45402   0 12:12PM ??         0:00.22 /usr/bin/java -Xrs -classpath /Applications/Cache20151/lib/cachegateway.jar:/Applications/Cache20151/lib/cachejdbc.jar com.intersys.gateway.JavaGateway 62972 /Applications/Cache20151/mgr/JDBC.log
502 45412 45365   0 12:12PM ttys000    0:00.00 grep java

Dans Windows, vous pouvez consulter le gestionnaire des tâches pour trouver des informations sur le processus de passerelle.

3. Lancement et arrêt de la passerelle

Il y a deux façons de lancer et d'arrêter la passerelle :

1. Par le biais du SMP
2. Utilisation du terminal

A. Par le biais du SMP

Vous pouvez lancer et arrêter la passerelle dans le SMP en accédant à [System Administration] -> [Configuration] -> [Connectivity] -> [JDBC Gateway Server].

B. Utilisation du terminal

Sur les machines Unix, vous pouvez également démarrer la passerelle depuis le terminal. Comme nous l'avons vu dans la section précédente, le résultat de ps -ef | grep java contient la commande qui a démarré le processus Java, qui dans l'exemple ci-dessus est le suivant:

java -Xrs -classpath /Applications/Cache20151/lib/cachegateway.jar:/Applications/Cache20151/lib/cachejdbc.jar com.intersys.gateway.JavaGateway 62972 /Applications/Cache20151/mgr/JDBC.log

Pour arrêter la passerelle depuis le terminal, vous pouvez tuer le processus. L'ID du processus Java est le deuxième chiffre de la ligne qui contient la commande ci-dessus, dans l'exemple ci-dessus c'est 45402. Ainsi, pour arrêter la passerelle, vous pouvez exécuter :

kill 45402

4. Écrire un programme Java

Exécuter un programme Java pour se connecter à une base de données descendante est un excellent moyen de tester la connexion, de vérifier la requête et d'aider à isoler la cause d'un problème donné. Je joins un exemple de programme Java qui établit une connexion avec SQL Server et imprime une liste de tous les tableaux. J'expliquerai pourquoi cela peut être utile dans la section suivante.

import java.sql.*;
import java.sql.Date;
import java.util.*;
import java.lang.reflect.Method;
import java.io.InputStream;
import java.io.ByteArrayInputStream;
import java.math.BigDecimal;
import javax.sql.*;

// Auteur : Vicky Li
// Ce programme établit une connexion avec le serveur SQL et récupère tous les tableaux. Le résultat est une liste de tableaux.

public class TestConnection {
    public static void main(String[] args) {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            //please replace url, username, and password with the correct parameters
            Connection conn = DriverManager.getConnection(url,username,password);

            System.out.println("connected");

            DatabaseMetaData meta = conn.getMetaData();
            ResultSet res = meta.getTables(null, null, null, new String[] {"TABLE"});
            System.out.println("List of tables: ");
            while (res.next()) {
                System.out.println(
                    "   " + res.getString("TABLE_CAT") +
                    ", " + res.getString("TABLE_SCHEM") +
                    ", " + res.getString("TABLE_NAME") +
                    ", " + res.getString("TABLE_TYPE")
                );
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Pour exécuter ce programme Java (ou tout autre programme Java), vous devez d'abord compiler le fichier .java, qui dans notre cas s'appelle TestConnection.java. Ensuite, un nouveau fichier sera généré au même endroit, que vous pourrez ensuite exécuter avec la commande suivante sur un système UNIX :

java -cp "<path to driver>/sqljdbc4.jar:lib/*:." TestConnection

Dans Windows, vous pouvez exécuter la commande suivante :

java -cp "<path to driver>/sqljdbc4.jar;lib/*;." TestConnection

5. Suivi d'une trace de jstack

Comme son nom l'indique, jstack imprime l'arborescence des appels de procédure Java. Cet outil peut devenir pratique lorsque vous avez besoin de mieux comprendre ce que fait le processus Java. Par exemple, si vous voyez le processus de la passerelle s'accrocher à un certain message dans le journal des passerelles, vous pourriez vouloir recueillir une trace jstack. Je tiens à souligner que jstack est un outil de bas niveau qui ne devrait être utilisé que lorsque d'autres méthodes, comme l'analyse du journal des passerelles, ne résolvent pas le problème.

Avant de collecter une trace jstack, vous devez vous assurer que le JDK est installé. Voici la commande pour collecter une trace jstack :

jstack -F <pid> > /<path to file>/jstack.txt

où le pid est l'ID du processus de la passerelle, qui peut être obtenu en exécutant la commande ps, telle que ps -ef | grep java. Pour plus d'informations sur la façon de trouver le pid, veuillez consulter Lancement et arrêt de la passerelle.

Maintenant, voici quelques considérations spéciales pour les machines Red Hat. Dans le passé, il y a eu des problèmes pour attacher jstack au processus de la passerelle JDBC (ainsi qu'au processus du service métier de la passerelle Java lancé par Ensemble) sur certaines versions de Red Hat, donc la meilleure façon de collecter une trace jstack sur Red Hat est de lancer le processus de la passerelle manuellement. Pour les instructions, veuillez consulter Collecter une trace jstack sur Red Hat.

Types courants de problèmes et approches pour les résoudre

1. Problème : Java n'est pas installé correctement

Dans cette situation, vérifiez la version de Java et les variables d'environnement.

Pour vérifier la version de Java, vous pouvez exécuter la commande suivante à partir d'un terminal :

java -version

Si vous obtenez l'erreur java : Command not found, cela signifie que le processus Cache ne peut pas trouver l'emplacement des exécutables Java. Cela peut généralement être résolu en plaçant les exécutables Java dans le PATH. Si vous rencontrez des problèmes, n'hésitez pas à contacter le WRC (Centre de réponse global).

2. Problème : échec de la connexion

Un bon diagnostic des échecs de connexion est la vérification du lancement du processus de la passerelle. Vous pouvez le faire en vérifiant le journal de la passerelle ou le processus de la passerelle. Sur les versions modernes, vous pouvez également aller sur le SMP et visiter [System Administration] -> [Configuration] -> [Connectivity] -> [JDBC Gateway Server], et vérifier si la page affiche "JDBC Gateway is running".

Si le processus de passerelle ne s'exécute pas, il est probable que Java n'est pas installé correctement ou que vous utilisez le mauvais port ; si le processus de passerelle s'exécute, il est probable que les paramètres de connexion sont incorrects.

Dans le premier cas, veuillez vous reporter à la section précédente et vérifiez le numéro de port. Je discuterai plus en détail de la deuxième situation ici.

Il est de la responsabilité du client d'utiliser les paramètres de connexion corrects :

• nom d'utilisateur
• mot de passe
• nom du pilote
• URL
• chemin de classe

Vous pouvez vérifier si vous avez les bons paramètres de l'une des trois façons suivantes :

• Utilisez le bouton "Test Connection" après avoir sélectionné un nom de connexion dans [System Administration] -> [Configuration] -> [Connectivity] -> [SQL Gateway Connections]. Note : pour les systèmes modernes, "Test Connection" donne des messages d'erreur utiles ; pour les systèmes plus anciens, le JDBC gateway log est nécessaire pour trouver plus d'informations sur l'échec.

• Exécutez la ligne de commande suivante depuis un terminal Caché pour tester la connexion :

    d $SYSTEM.SQLGateway.TestConnection(<connection name>)
  

• Exécutez un programme Java pour établir une connexion. Le programme que vous écrivez peut être similaire à l' example dont nous avons parlé précédemment.

3. Problème : décalage entre la façon dont Caché comprend JDBC et la façon dont la base de données distante comprend JDBC, par exemple :

• problèmes de type de données
• procédure stockée avec des paramètres de sortie
• flux

Pour cette catégorie, il est souvent plus utile de travailler avec le WRC (Centre de réponse global). Voici ce que nous faisons souvent pour déterminer si le problème se situe dans notre code interne ou dans la base de données distante (ou dans le pilote) :

• regarder les journaux et analyser ce qui est envoyé
• reproduire le problème en dehors de Caché en écriture d'un programme java.

Remarque

Le service commercial de la passerelle Java

Le nom de la classe du Service Métier d' Ensemble est EnsLib.JavaGateway.Service, et la classe de l'adaptateur est EnsLib.JavaGateway.ServiceAdapter. La session Ensemble crée d'abord une connexion avec le serveur Java Gateway, qui est un processus Java. L'architecture est similaire à celle de la passerelle JDBC SQL, sauf que le processus Java est géré par l'opération commerciale. Pour plus de détails, veuillez consulter la documentation.

Activation du journal du pilote

Pour activer le journal du pilote, vous devez ajouter un nom de fichier journal à la fin de la chaîne de connexion JDBC. Par exemple, si la chaîne de connexion originale ressemble à czci :

jdbc:Cache://127.0.0.1:1972/USER

Pour activer la journalisation, ajoutez un fichier (jdbc.log) à la fin de la chaîne de connexion, de sorte qu'elle ressemble à ceci :

jdbc:Cache://127.0.0.1:1972/USER/jdbc.log

Le fichier journal sera enregistré dans le répertoire de travail de l'application Java.

Activation de la journalisation de la passerelle Java dans Ensemble

Si vous utilisez le service métier de la passerelle Java dans Ensemble pour accéder à une autre base de données, vous devez, pour activer la journalisation, spécifier le chemin et le nom d'un fichier journal (par exemple, /tmp/javaGateway.log) dans le champ "Log File" du service de la passerelle Java. Veuillez noter que le chemin d'accès doit exister.

N'oubliez pas que la connexion de la passerelle Java utilisée par la production Ensemble est distincte des connexions utilisées par les tableaux liés ou d'autres productions. Ainsi, si vous utilisez Ensemble, vous devez collecter le journal dans le service de passerelle Java. Le code qui démarre le service de passerelle Java utilise le paramètre "Log File" dans Ensemble, et n'utilise pas le paramètre dans la passerelle Caché SQL dans le SMP comme décrit précédemment.

Récupération d'une trace jstack sur Red Hat

La clé ici est de lancer le processus de la passerelle manuellement, et la commande pour lancer la passerelle peut être obtenue en exécutant ps -ef | grep java. Vous trouverez ci-dessous les étapes complètes à suivre pour collecter une trace jstack sur Red Hat lors de l'exécution de la passerelle JDBC ou du service métier de la passerelle Java.

1. Assurez-vous que le JDK est installé.

2. Dans un terminal, exécutez ps -ef | grep java. Obtenez les deux informations suivantes à partir du résultat :

   • a. Copiez la commande qui a lancé la passerelle. Cela devrait ressembler à quelque chose comme ça : java -Xrs -classpath /Applications/Cache20151/lib/cachegateway.jar:/Applications/Cache20151/lib/cachejdbc.jar com.intersys.gateway.JavaGateway 62972 /Applications/Cache20151/mgr/JDBC2.log

   • b. Obtenez l'ID du processus Java (pid), qui est le deuxième chiffre de la ligne qui contient la commande ci-dessus.

3. Arrêtez le processus avec kill <pid>.

4. Exécutez la commande que vous avez copiée à l'étape 2.a. pour lancer manuellement un processus de passerelle.

5. Jetez un coup d'oeil au journal de la passerelle (dans notre exemple, il est situé dans /Applications/Cache20151/mgr/JDBC2.log) et assurez-vous que vous voyez des entrées comme >> LOAD_JAVA_CLASS: com.intersys.jdbc.CacheDriver. Cette étape est juste pour vérifier qu'un appel à la passerelle est effectué avec succès.

6. Dans un nouveau terminal, exécutez ps -ef | grep java pour obtenir le pid du processus de la passerelle.

7. Rassemblez une trace jstack : jstack -F <pid> > /tmp/jstack.txt