#JDBC

The current jdbc driver version is 3.1.0, but it is not published in the public mvn repository, only the old version, see:

https://mvnrepository.com/artifact/com.intersystems/intersystems-jdbc

It is very bad to the Java Community. And why not publish IRIS Hibernate dialect in mvn repository too?

These actions are important to Java Developers.

Hi all,

I'm trying to use LOAD DATA to insert 11k (11,377) rows of data. LOAD BULK DATA is not available for the version of IRIS I am using.

After calling LOAD DATA it says only 5,500 rows has been inserted. The LOAD DATA docs says any error rows are skipped and a count of skipped rows can be found in %SQL_Diag.Result however there are no results here. There are no errors in the xDBC error log either.

Why have over half the rows been skipped?

Any news about JDBC driver and Hibernate dialect on a public java repository, like mvnrepository? Today I need to download the jdbc driver and hibernate jar, add as an external resource on my maven config file to works.

Hi all,

I'm trying to access via JDBC my community installation but the connection is always rejected. The same code (python code) using the stand-alone version of Intersystems IRIS works fine.

Is the community edition limited in this case? I can't see it in the official documentation: https://irisdocs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Pag…

Problem: Caché instance connects periodically to an Oracle database via jdbc and downloads data. Sometimes data on a remote server would change.

Hello,

I work on deploying IRIS using Kubernetes operator and Red Hat OpenShift. I encouraged another team working on Java application to consider using IRIS as database. My team deployed IRIS cluster using two mirrored data pods for the other team. The other team asked me for the connection information.

To learn how to use Java with IRIS, I attempted to deploy two apps from Open Exchange:

https://openexchange.intersystems.com/package/CRUD-GLOBALS-IRISNATIVEAP…

Here I got errors like this:

package com.intersystems.jdbc does not  exist

https://openexchange.intersystems.

Is it planned that LOAD DATA takes into account several DATE/DATETIME formats with, for example, a parameter indicating the format used in the source data?

example :

LOAD DATA .../...
USING
{
  "from": {
    "file": {
       "dateformat": "DD/MM/YYYY"

I have a MySQL server with "posts" table.

I also have a Caché server with "downloadedposts" table. 

They are connected from Caché to MySQL via SQL Gateway

I want to keep Caché table synced with MySQL one  (MySQL "posts" table is a master copy), so periodically Caché queries MySQL server and downloads data. So far so good, and if a record appears or changes in MySQL table, Caché downloads the changes.

The problem I'm encountering is that sometimes rows would be deleted from  MySQL "posts" table.

How do I synchronize deletions?

Hi All,

            I have a general query in regards to developers experience on extracting data from cache databases and the most efficient way to do so. I work with a number of clients who have applications with cache databases and require the data off the host system and onto data warehouse platforms for research and analysis. Often they require the data in source state which means the extracts are often simply a table scan of the entire database table without any aggregation or manipulation.

Is it possible to authenticate an xDBC (ODBC/JDBC) connection to InterSystems IRIS via (a 3rd party) OAuth server?

For REST APIs this is possible, but could this be achieved with OAuth? 

Out-of-the-box the ODBC/JDBC Drivers don't seem to have this option, but maybe some custom code could enable this? perhaps via Delegated Authentication and some OAuth classes customization, or some other way?

Has anyone done this already and can share how it was implemented, or someone with some guideline suggestions?

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#…

Do you have any examples or tracks to use the executeParametersBatch method of %Net.Remote.Java.JDBCGateway?

I’ve been exploring options for connecting Google Cloud Pub/Sub with InterSystems IRIS/HealthShare, but I noticed that IRIS doesn’t seem to ship with any native inbound/outbound adapters for Pub/Sub. Out of the box, IRIS offers adapters for technologies like Kafka, HTTP, FTP, and JDBC, which are great for many use cases, but Pub/Sub appears to be missing from the list.

Has anyone here implemented such an integration successfully?

For example:

• Would it make sense to leverage IRIS’s Business Service + REST API adapter to connect to Google’s Pub/Sub REST endpoints?

I'm looking for an efficient way in DBeaver to filter system tables (ex: belonging to a schema starting with "%").

By using a user with the %All role, DBeaver shows us a long list of system schemas, which forces us to go down the list before accessing the user tables.

We have a new requirement being push down by our Data Security to no longer use Local SQL Accounts to access our Databases. So they asked me to create a Service Account that is on the Domain for our connections to each database.

I tried just changing my JDBC connection to using this Service Account and Password but I am not having any luck trying to connect to the database.

" Connection failed.
Login failed for user 'osumc\CPD.Intr.Service'.

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:

Class Test.Test
{
	ClassMethod MyCustomQueryClose(ByRef qHandle As %Binary) As %Status
	{
	    Quit $$$OK
	}
	
	ClassMethod MyCustomQueryExecute(ByRef qHandle As %Binary, ByVal pCubeName As %Library.String) As %Status
	{
	     Quit $$$OK
	}
	
	ClassMethod MyCustomQueryFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status
	{
		Set Row=$ListBuild(1,"Amir",2,"Test")
		Set AtEnd=1
	    
	    Quit $$$OK
	}
	
	ClassMethod MyCustomQueryGetInfo(ByRef colinfo As %List, ByRef parminfo As %List, ByRef idinfo As %List, ByRef qHandle As %Binary, extoption As %Integer = 0, ByRef extinfo As %List) as %Status
	{
		Set colinfo=$lb($lb("C1","10","C1"),$lb("C2","10","C2"),$lb("C3","10","C3"),$lb("C4","10","C4"))
		Set parminfo=$lb($lb("pCubeName","10"))
		Set idinfo=$lb(0,"")
		If extoption 
		{
			Set extinfo=$lb("","")
		}	
		Quit $$$OK
	}

	
	Query MyCustomQueryCube(ByVal pCubeName As %Library.String) As %Query [ SqlProc, SqlName = "GetFeaturesFromCube" ]
	{
	} 
}

Ran into an issue this morning, that I am having a hard time trying to track down what might have caused the issue. We have a Business Rule that sends HL7 ADT to a Business Process that inserts the data into a MS SQL Server using a Custom Business Operation (SQL Outbound Adapter).

Early yesterday morning the Custom Business Operation reset its connection through the Java Gateway Server to the MS SQL Database, when it reconnected to the Java Gateway/MS SQL Database it stopped processing. So, the Business Rule had over 40,000 queued up to process.

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.

Is there a way to access this JDBC parameter through the EnsLib.SQL.

I am currently experiencing frustration with trying to Authenticate an Active Directory account through JDBC as the Hospital System moves from OnPrem SQL Server to using Azure SQL Server with Microsoft Entra Authentication.

Microsoft cannot give me a straight answer of what is required from a JDBC standpoint to authenticate from a Linux environment.

I am working with WRC, but we are both struggling to find the specific answer for JDBC. If I use my local desktop and JDBC to connect through DBeaver I don't have an issue.

Hello all,

On one of my team's systems, we utilize a business operation with the EnsLib.SQL.OutboundAdapter to make SQL queries to another IRIS system using JDBC. To authenticate the connection, we utilize a user account on the target system.

We recently had a failure where this user account expired due to inactivity, causing all queries to error until the account was reactivated on the target system.

Hello everyone,

I'm trying to authenticate a user(Health Share clinician) from a Java Application. 

I 'm already connected to Caché and able to run SQL commands.

My question is: How can I authenticate a user using only SQL? In fact, what I want is verify if the users exists in the base and if the given password is the same used in Health Share.

There is a column 'password' in Security.users table but I'm not able to see its content, even so, I don't know which hash function to use to compare with.

I am trying to replace one of our SQL Integration Service jobs with Ensemble and I am running into an issue executing a query against a MS SQL database using JDBC drivers.

My SQL Outbound code looks like this.

 Method SelectPaceartCharges(As 

have set the query property to a max length of 10000.

Property query As %String(MAXLEN = 10000);

Hello all,

I'm trying to setup the JDBC Gateway Server so customers can connect to IRIS remotely using JDBC and not ODBC. But I'm facing a problem connecting, as our system department tells me IRIS is using the loopback address (127.0.0.1) and that makes remote systems cannot connect to port 53773 (the default port for that).

So, I would like to change this 127.0.0.1 host for the hostname but I cannot see where to do it:

Anyone with experience here to help me? :-)

Thanks a lot

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

Hello everyone!

I am writing a SQL CALL (using JDBC) to a stored procedure that outputs a structured object (Oracle Object).

Hello, dear colleagues.

I need to connect to a remote JavaGateway from an Ensemble service.

I am trying to use the EnsLib.JavaGateway.Service with a remote host where the JVM is running.

I can successfully ping the remote Java Gateway from EnsLib.JavaGateway.Service, and Ensemble reports that the service status is OK.

There are no network issues, and all necessary ports are accessible.

My requests work without any problems when I specify localhost in EnsLib.JavaGateway.Service.

I am trying to set up a sensor in PRTG to connect to Cache, specifically the ens_util.log, so that I can have a live feed of my error count. I am having trouble getting the sensor to log in to Cache. Has anyone had any luck getting PRTG to connect on the database level? Thanks!

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.

Recently I started moving some of our JDBC Gateway configurations over to using the Microsoft JDBC Gateway driver instead of using the jTDS open source driver. I have noticed since then that those Objects that are using Microsoft JDBC Driver are throwing a lot more errors than the jTDS driver ever did, which I can understand however I am puzzled that I keep running into an issue with the Java Gateway Service within the Interoperability engine.

Every so often I will start seeing "Gateway Server failure timeout on 127.0.0.1:55550" followed by "Gateway Server did not respond to PING request.

Hello Global masters!

I developed Web with Spring framework (JAVA, Jdbc Cache) and I wrote SQL when I made it.

And Window program with VB6, Cache Object and VisM.

Is there a significant performance gap? SQL and Cache Object?

same function, same computer performance just different Java and VB6.

Thank you.

Minsu.

Has anyone successfully tested the new THROUGH command in IRIS 2024.2 with a FOREIGN SERVER?https://docs.intersystems.com/iris20242/csp/docbook/Doc.View.cls?KEY=RS…

I have connected from a Docker instance to a VM. I was able to successfully set up the JDBC connection through the UI.

I then configured a foreign server with this connection:

But I am unable to send a SQL 'THROUGH' to the DB. I always get a:

I've get the same message if i try it via Management Portal.
I've also tried this:
and this

Always with the same error.