#JDBC

I need to reproduce a table (in fact a view, but let's start with a simple table 😊) in IRIS as the currently used in Oracle.

I need to expose/project one column to JDBC as CLOB, in my class I have the corresponding property defined as:

Property GlobStream As %Stream.GlobalCharacter;

In JDBC this column is projected as LONGVARCHAR, this is compliant with the documentation but I need to project it as CLOB.

In addition, I'm no expert in JDBC but wit seems that LONGVARCHAR has a Maximum Length 32,700 characters in JDBC, not quite enough for an arbitrary stream.

Hello Community,

I am facing a JDBC connection issue after migrating from Caché 2016 to Caché 2018.1. When I attempt to connect using the following connection settings:

CACHE_DATASOURCE_URL=jdbc:Cache://localhost:1972/TEST

CACHE_DB_USERNAME=test

CACHE_DB_PASSWORD=test
 

I consistently receive the following error:

[Cache JDBC] Communication link failure: Access Denied

This configuration worked perfectly with Caché 2016. I have verified the following:

1. The namespace (TEST) exists and is correctly specified in the connection URL.
2. The credentials (username: test, password: test) are correct.

I have a business service that actively reads data from a remote Postgres database. OnProcessInput opens a XDBC (actually JDBC) connection, executes an SQL query, fetches several thousand rows, iterates the resultset, and closes the connection. On each iteration I also need to update each source row in the remote database using PreparedStatement.

In other words, in every OnProcessInput call I have a long running SELECT statement and several thousands small UPDATE statements.

Hello!

I'm trying to create some foreign tables to a PostgreSQL database. In some cases, columns with certain datatypes cannot be consumed by IRIS and the following error is thrown:

 [SQLCODE: <-237>:<Schema import for foreign table did not return column metadata>]

  [%msg: <Unkown data type returned by external database>]

For example: serial4 typed ID columns are typical examples. Is it possible, what's the best way of resolving these datatypes, which- seemingly- don't have proper JDBC metadata mappings?

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?

Hello. Currently, we are developing using Cache 2018 version.
Our team is working on improving an existing legacy program so that it can also be used on the web.

Before asking my question, here is the development environment.

• IDE: IntelliJ
• Framework: Spring Boot, MyBatis
• DB Connection: JDBC (using the library provided by InterSystems)

Currently, we are successfully mapping global data through the %PERSISTENT class and able to query it with SQL. However, the problem is that the retrieved "Korean" data is all broken.

Here is my code:

Method getStocks(pRequest As Stock.Message.Req, Output pResponse As Ens.StreamContainer) As %Status
{
     s tSC = pRequest.NewResponse(.pResponse)
     q:$$$ISERR(tSC) tSC

     #dim pRS As EnsLib.SQL.GatewayResultSet

     s tSC = ..Adapter.ExecuteQuery(.pRS, "select jsonb_agg(s) #>> '{}' FROM prod.stocks s where s.""Warehouse"" = ?", pRequest.Warehouse)
     q:$$$ISERR(tSC) tSC

     s pResponse = ##class(Ens.StreamContainer).%New()
     s pResponse.Stream = ##class(%GlobalCharacterStream).%New()

     i pRS.Next() {
          d pResponse.Stream.CopyFrom(pRS

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.

In MySQL I have the following table:

CREATE TABLE `info` (
   `created` int(11)
);

And it is linked (via JDBC SQL Gateway) to Cache table mysql.info.  `created` field stores unix timestamp. So when I execute  this SQL in SMP:

SELECT created FROM mysql.info

I receive the following output (which is expected):

But I want to to display `created` field converted to ODBC timestamp format. To do that I call this SQL procedure

Class mysql.

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.

We connect to MS SQL Databases using the Microsoft JDBC Driver 12.2 using the following URL

jdbc:sqlserver://<server>:<port>;database=<database name>;trustServerCertificate=true;integratedSecurity=true;authenticationScheme=NTLM;domain=osumc;authentication=NotSpecified

They want to migrate the databases to the Azure Cloud and in doing so we need the Authentication to change to go through Microsoft Entra.

I am using a JDBC connection to MS SQL server to execute a stored procedure to select data and bring it into InterSystems as a EnsLib.SQL.Snapshot. I loop through the EnsLib.SQL.Snapshot using a while loop, but I also want to iterate through the Columns within that Row to do logic.

Is there a way to iterate through the Columns of the current Row of the EnsLib.SQL.Snapshot so I can apply logic/rules for further processing?

Thanks

Scott

Hi Community,
I am getting below "The driver cannot be loaded" error on a MacBook.

obdbc8.jar file is present in the mentioned folder.
Looking forward to resolve the above issue or workaround
Thanks

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.

I have been trying to track down an issue we are seeing in our TEST environment with Memory usage.

We have Several BP's for years now that take a HL7 message, parse it apart, and make calls to a Custom EnsLib.SQL.OutboundAdapter to have it execute Insert/Select/Update/Delete stored procedures against a MS SQL Database via JDBC connection. We are using Microsoft's JDBC 12.2 driver to do this.

What we are seeing is that IRIS.WorkQueue globals are being defined for these calls but then the IRIS.WorkQueue is not being cleaned up and taking up large amounts of Memory.

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.

LS,

I'm executing a query using JDBC on a PostgreSQL db:

SET statement_timeout TO 600000000; COMMIT SELECT * FROM bi_hour

The query is aborted with the following message:

FOUT #5023: Fout in Java Gateway: JDBC Gateway getClob(0,2) errorBad value for type long : active

The column 'blocked_status' contains the value 'active' is of type 'text'. I figure somewhere the SQL Gateway tries to convert the text value into a long but I can't find where, any suggestions?

From time to time we develop an Ensemble Production with simple SQL Inbound data from external databases, we need to develop a few new classes. There are at least:

• 1 Ens.Request class with the fields captured from the SQL ResultSet
• 1 Business Service class using SQL Inbound Adaptor, and in the OnProcessInput(), copy the relevant field data from ResultSet to the new Ens.Request, and call either ..SendRequestSync() or ..SendRequestAsync().

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.

@Vicky Li
@Thanongsak Chamung
@Warlin Garcia
@Eduard Lebedyuk
@Enrico Parisi
@LuisAngel.PérezRamos
We are getting this error. All the drivers, URL and credential are tested and existed. it works fine sometime but also getting this error more often. Please advise the root cause and its respective solution. The business service that is using out of the box Java service (classname: EnsLib.JavaGateway.Service adapter classname: EnsLib.JavaGateway.ServiceAdapter) is throwing this error.

How to query BLOB type data in MYSQL database through JDBC or ODBC

I have questions about Java Versions needed for setting up a %JDBC External Language Server

I'm trying to start the %JDBC. External Language Server my local instance of IRIS (2023.1.2). I've recently upgraded Java to version 21.0.1 

When starting the External Language Server, I get this error message:

Need to fetch data from SQL server management studio to ensemble, using jdbc or ODBC. 

Step by step solution would be highly appreciated. 

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?

Tring to Performing following script in IRIS but do not know how to format Date. Normally, I would use TO_DATE or TO_TIMESTAMP in the actual queue statement below, but since I'm using Prepared Statements, I do not know how to format date in 'values' that is being used in the %Execute. It generates errors. I'm updating an Oracle Database via a SQL JDBC Gateway Connection. Does anyone know how to pass in Dates?