#JDBC

When using "IDENTITY" as my primary key, I can select the last inserted ID with 

SELECT LAST_IDENTITY() FROM %TSQL_sys.snf;

Actually this is how Hibernate + Iris' Driver acquires the inserted ID when mapping with

@GeneratedValue(strategy = GenerationType.IDENTITY)

Now, considering that I am using the type "SERIAL" as my primary key instead, how can I get the last inserted ID?

Note that with "SERIAL" I can forcefully insert any value for this ID, from which Iris will continue generating values...

Built in multi model integration using InterSystems iris data platform

Solution of hospital information inquiry business

Integration of hospital information query business solutions using InterSystems IRIS data platform with built-in multiple models

### Summary:

DataGrip is a multi-engine database environment targeting the specific needs of professional SQL developers, DataGrip makes working with databases an enjoyable and productive experience.

To work with InterSystems IRIS from DataGrip you'll need to add InterSystems JDBC driver first (once per DataGrip) and after that add all your InterSystems IRIS connections.

Part 1: Add InterSystems IRIS JDBC Driver

1. Go To File → DataSources

2. Go to + → Driver

3. Set Driver properties:

I have a case where I need to look up the NPI provider against an External MS SQL database to retrieve our Provider Identifier to send to a downstream system. In the past I would use a Business Process (BPL) to connect to the outside MS SQL via JDBC and get that information for me. But I was thinking instead of creating a BPL process to do this, would it be better just to link to the outside MS SQL database table to retrieve the information in a SQL statement within a DTL?

Keywords: Python, JDBC, SQL, IRIS, Jupyter Notebook, Pandas, Numpy, and Machine Learning 

1. Purpose

This is another 5-minute simple note on invoking the IRIS JDBC driver via Python 3 within i.e. a Jupyter Notebook, to read from and write data  into an IRIS database instance via SQL syntax, for demo purpose. 

Hi Cache team, I am in the need of listing all the user defined schemas that are present my Cache db and also the user defined tables and views and Columns of those tables and views through Queries. So that I can write some JDBC code to run the queries and fetch the above metadata. Any help is appreciated.

Thanks in Advance,

Kranthi kiran.

Hey Developers!

This week is a voting week for the InterSystems Full Stack Contest!

So, it's time to give your vote to the best solutions built with InterSystems IRIS.

🔥 You decide: VOTING IS HERE 🔥

How to vote? This is easy: you will have one vote, and your vote goes either in Experts Nomination or in Community Nomination.

Hey Developers!

We're pleased to announce the next competition of creating open-source solutions using InterSystems IRIS! Please welcome:

⚡️ InterSystems Full Stack Contest ⚡️

Duration: September 21 - October 11, 2020

Hi Community!

We are pleased to invite all the developers to the upcoming InterSystems Full Stack Contest Kick-off Webinar! The topic of this webinar is dedicated to the Full Stack Contest.

On this webinar, we’ll demo the IRIS Full Stack template and answer the questions on how to develop, build and deploy full stack applications in InterSystems IRIS.

Date & Time: Monday, September 21 — 11:00 AM EDT

Speakers:  
🗣 @Evgeny Shvarov, InterSystems Developer Ecosystem Manager
🗣 @Raj Singh, InterSystems Product Manager - Developer Experience

Hi Developers!

Here're the technology bonuses for the Full Stack Contest that will give you extra points in the voting:

• InterSystems IRIS REST API Usage
• InterSystems Native API Usage
• InterSystems JDBC Usage
• Unit Testing
• ZPM Package deployment
• Docker container usage

See the details below.

Hello,

Recently I tried to connect to JDBC on IRIS 2020.3+ and I came across the following error:

 SQL Error [461] [08S01]: [InterSystems IRIS JDBC] Communication link failure: Communication error: Server closed communication device

This error implies that the port is not open.

Checking with the netstat command:

Many times it is necessary copy or send files to your docker container instance.

In my case was with IRIS JDBC driver.

Docker has this recipe for this (credits to https://docs.docker.com/engine/reference/commandline/cp/):

docker cp [OPTIONS] CONTAINER:SRC_PATH DEST_PATH|-
docker cp [OPTIONS] SRC_PATH|- CONTAINER:DEST_PATH

But to copy you need your container name. Write this command for this:

docker ps

In my, my-iris is the container name.

If you need to know file location into your docker file system too, write:

Hi Guys,

I am trying to pull data from CacheDB and push into elasticsearch using logstash. In the configuration file i am giving the following. But it is throwing error No Suitable Driver Found for jdbc:Cache://ipaddress:port/namespace. Could anyone please help to resolve this ? I tried both JDK17 and JDK18 but no luck.

My Config file is as follows

The Caché System Management Portal includes a robust web-based SQL query tool, but for some applications it’s more convenient to use a dedicated SQL client installed on a user’s PC.

SQuirreL SQL is a well known open source SQL client built in Java, which uses JDBC to connect to a DBMS. As such, we can configure SQuirreL to connect to Caché using the Caché JDBC driver.

Finding Caché’s JDBC driver JAR

I have a class that writes to an external SQL Server database.  When the field is empty, it does not write the column and the column has the value NULL.  In some cases, I want the column to be the value of the empty stringinstead of NULL.

Class myRequestClass Extends Ens.Request
{

    Property MessageType As %String

}

The message that is passed to this class shows:

    <MessageType></MessageType>

Which writes the field as NULL with:

    tSQL = "insert into myTable (MessageType) values (?)"

    set tSC = ..Adapter.ExecuteUpdate(.tNumberOfRowsUpdated,tSQL,myRequestClass.MessageType)

Everyone

There is a setQueryTimeout method in the% Net.Remote.Java.JDBCGateway class of IRIS, how to use Ensemble2016 to achieve this function?

Intersystems use JReport as their reporting framework.

Are there any free version available for developers to try it out? If yes, can I avail the link?

Is there any free trial version for 'DeepSee'? if yes, can I avail the link? 

Thanks in advance for your feedback.

HI I am using below code to retrieve the last inserted ID of the table. It works when we do not set any custom column as IDKey. If we define any column as 

IDKey below code does not return any IDKey. I know when i am defining any column as IDKey its not auto generated , but whats the best way to get the ID column value

Hi All,

I trying to get the DisplayList data of the ValueLIst from Java using Springboot. when I try to get the DislayList data via java code but I'm getting the VALUELIST data. I don't get any idea to get that DisplayList data. Below is the example

Property PetName As %Integer(DISPLAYLIST = ",Cat,Dog,Dove,Parrot", VALUELIST = ",1,2,3,4");

Java Code:

public String PetName;

public List<Pet> getPet() {
        return ser.findAll();
    }

List<Pet> arrayList = getPet();
        for (Patient list : arrayList) {

                 String pet= list.getPet();

I am under the impression that inside HealthShare you can Services, Processes, and Operations. The service takes incoming data, the processes can process that data, and then the operations can send that data out.

I am curious if anyone has any experience, guides, or advice on how I can send data to a service using a Java application. I intend for the data to be in XML format. I am also curious if I succeed at picking up the data in the service, how I send the data back to the Java application using an Operation.

As of now I am attempting to get this to work on a local environment.

Hi all...

I am try to execute a class method from JDBC connection. My method are simple, just create a file at $HOME  like this:

set sc='$zf(-1,"touch $HOME/lixo.txt > /var/tmp/log666 2>&1")

I put a log file to this commnad because it's don't work, and in log file i get this:

touch: cannot touch ‘/root/lixo.txt’: Permission denied

after to try other commands I found that user and enviromnent  variables are diferents:
I try this:

set sc='$zf(-1,"echo $USER > /var/tmp/log666 2>&1") ;Return root

set sc='$zf(-1,"echo $HOME > /var/tmp/log666 2>&1") ;return /root

Hi, I asked last week and they gave me some little tips. But the truth is I still can't carry out this "Project".
 

I must perform a REST service, using Oracle 11 database. Eh tried anyway to achieve this.

The purpose is to make a REST service, using the database provided to me and use the procedure that is already created.

According to what I read, I made the connection via JDBC and I had no major problem

Then I managed to read the bd inside iris and capture its procedure, but at the time of doing this. The procedure does not work correctly.

Hi all....

I couldn't find documentation on how to create a blob using the cache JDBC driver.
I found only one reading example:

https://cedocs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?…

The createBlob Method of the connection class was not implemented:

https://cedocs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?…
 

Does anyone know how to create a blob without compromising the java heap space?

Hi! I am creating a Jasper Report using IRIS like DB, I've created my Data Adapter to connect with my DB configuring the JDBC connector and it works perfectly:

But when I try to create a report connecting to my DB I am getting this error:

This is the error in detail: 

Hi! I am working in a java project (Spring Boot+ Maven + Hibernate) using jpa/hibernate to manage the persistance with the IRIS database from the Docker image (store/intersystems/iris:2019.2.0.107.0-community) and I've found an issue using the IRIS instance, when I define tables with relationships OneToMany, ManyToOne or ManyToMany and I try to fetch all the rows of the tables using the default method findAll (JpaRepository implements that method to get all the rows by default) the query automatically exceeds the limit of licenses availables. I've tested the same query using a local IRIS

Hi!

I was trying to create a query that can be exposed as a stored procedure (function actually) that would return a resultset with a random number of columns. 

Unfortunately, it seems that unless I specify the ROWSPEC annotation on the Query method, I won't get any columns exposed. I was hoping to implement QueryNameGetInfo method and specify the names and number of columns I would be returning dynamically. But it seems that GetInfo information is simply ignored.

Here is my code:

Hi, We recover a large amount of data from an external database (SQLServer, about 1 million rows in JDBC).
However, we have treatment time issue.
This process takes more than 30 minutes whereas on a "classic" SQL Server Management Studio type request takes less than a minute.

While searching on the internet, I came across this article: http://makejavafaster.blogspot.com/2015/06/jdbc-fetch-size-performance.html
It explains how in Java, we can tweak the FetchSize parameter of the JDBC driver to optimize this kind of process.

Hi, I'm looking for a way to implement the JDBC pattern "executeBatch" in ObjectScript: https://www.tutorialspoint.com/jdbc/jdbc-batch-processing.htm

Since Ensemble 2017.2.2 there is a new method called executeParametersBatch : https://docs.intersystems.com/ens20172/csp/docbook/relnotes/index.html#…

Even in the Ensemble 2018.1 change log, mention a patch on this method: https://docs.intersystems.com/ens20181/csp/docbook/relnotes/index.html#…

Where do I download the package cache-jdbc-2.0.0.jar. I'm trying to develop a java app that connects to a Cache Database to grab information? Thank you.

Hi All,

We are using BI tool for reporting which required to connect TrakCare system using JDBC Connection
Can some can assist with Jar file which is required for the connection or anyone did befoe?

Thanks,
Syam