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

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?

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,

            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.

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?

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" ]
	{
	} 
}

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.

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

Good morning!

I am developing a Java project (Spring Boot + Maven + Hibernate) but I've a big issue when I try to define the Hibernate Dialect org.hibernate.dialect.InterSystemsIRISDialect... that dialect is neither in the Hibernate library nor in the IRISDriver class com.intersystems.jdbc

How should I define the IRISDialect in my project?

Thank you in advance!

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

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.

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.

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.

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.

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.

I cannot get any further hint from the error message in red in this screenshot. Is there any clue or details about the connection failure that I could look for?

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.utils [ Abstract ]
{
/// Unix timestamp to ODBC
ClassMethod uto(unixstamp As %Integer) As %TimeStamp [ SqlName = uto, SqlProc ]
{
    set startday = $zdh("1970-01-01 00:00:00",3)
    set endday = (unixstamp \ (60 * 60 * 24))
    set endtime = (unixstamp # (60 * 60 * 24))
    set totalday = endday + startday
    quit $zdt(totalday _ "," _ endtime, 3)
}
}

We are trying to script a High Availability Shutdown/Start script in case we need to fail over to one of our other servers we can be back up within mins. Is there a way to configure the startup procedure to Automatically Stop/Start the JDBC server when shutting down or starting up cache? is there an auto setting we can change?

Thanks

Scott Roth

The Ohio State University Wexner Medical Center

Hi everyone,

i have a problem with the "ExecuteUpdateParmArray()" methode from the "EnsLib.SQL.OutboundAdapter" adaptater when i try to update a sql server 2008 table with JDBC connection.

..Adapter.ExecuteUpdateParmArray(.nRows,sqlUpdate,.param)

return to me the following error:

- "ERROR #5023: Remote Gateway Error: JDBC Gateway execUpdate(0) error -1: no value specified for parameter 1."

the sqlUpdate is like "update table SET column1 = ?,column2 = ?,column3 = ? where id = 1"

i built my param like this :

s param(1) = 'x'
s param(2) = 'y'
s param(3) = 'z'
s param = 3

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

            String sql = "INSERT INTO TestFramework_UI_Data.Execution (TotalTestSteps) VALUES (0)";
            //ResultSet rs = createExecutionSt.executeQuery("SELECT * FROM TestFramework_E2E_Data_TestData.TestCases");
            int t = createExecutionSt.

We are planning to build a REST API with the Java Quarkus Framework.

• There'll be a connection pool;
• The DataSource instance will be automatically injected from the connection pool;
• We will use JDBI to do SQL instructions and this will give us automatic Connection and Statement management;

This Caché instance already have COS applications running and consuming connections and licenses.

After doing this, we will migrate to IRIS.

Is there any pitfalls that we must be aware before taking this approach?

If you want to see some code the PoC can be found here.

Thanks.

Without installing Kerberos has anyone Authenticated a SQL JDBC connection? Currently we are using local SQL Accounts to sign onto External SQL Databases, but we are being told that we need to switch to Service accounts that live on a Active Directory Domain. 

I wrote with a little help a ZAUTHENICATE to do the Authentication for Ensemble, can I use something like that to connect to an External SQL Database using a Service Account on a Active Directory Domain?

Thanks

Scott