#JDBC

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 string instead 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

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

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.

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

I'm new to Iris, trying to follow this guide ( https://community.intersystems.com/post/k-means-clustering-iris-dataset ) about using clustering. I've followed all instructions until showing the data. At that point, `dataFrame.show()` fails with `java.sql.SQLException: Access Denied`.

I'm using IRIS 2018.12.609.0 on Windows 10 Pro 64 bits. Python is 3.6.6, PySpark is 2.3.1, installed from Anaconda (Python 3.6.6 |Anaconda, Inc.| (default, Jun 28 2018, 11:27:44) [MSC v.1900 64 bit (AMD64)] on win32).

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 use DataGrip (JDBC client) to query Caché server via JDBC.

The problem I encountered, is that if I wait 10 or more minutes between queries I get an error:

[08S01][461] Communication link failure: Socket closed

To fix that I need to disconnect and connect to server again. Is there a JDBC timeout setting somewhere I can change?

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.

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.

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.

I'm referring to the document Using Cache with JDBC. Using this example, it sets the URL of the object ds of class CacheDataSource.

ds.setURL("jdbc:Cache://127.0.0.1:1972/SAMPLES");
My question is would you just copy and paste the URL from the login page to the string? I tried doing that and it returns an error saying "Invalid log file xyz"

Any help will be greatly appreciated! Thank you

We are creating a package (written in Caché Object Script) that will provide access to an external DB (MySQL). Because applications that use our package will be run from machines with various, potentially unexpected, operating systems, we’d like to establish a connection to the external DB without using DSNs (we’ve heard that setting up DSNs on certain non-Windows machines can be cumbersome and problematic).

Does anyone know of  way to create a connection to an external DB without using a DSN?

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?

I get this on some queries in some namespaces.  For instance, this query:

It works fine in HSBUS but in HSREG it throws the error.

Server closed communication device

Does anyone know what would cause this? Would it log something more useful somewhere?

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.

Is it possible to access (read, write) to an external Oracle database via Cache SQL Gateway using JDBC in Cache Object Script?  I am currently using ODBC successfully but wanted to see if JDBC was an option too.  If it is possible, does anyone have a basic Object Script example(s) that I can review?

Thanks!

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.

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

input {
    jdbc {
    clean_run => true
    jdbc_driver_library => "C:\InterSystems\Cache\dev\java\lib\JDK17\cache-jdbc-2.0.0.jar"
    jdbc_driver_class => "com.intersys.jdbc.

Hi all. When I try to do this in Apache Zeppelin:

  This error appears:

My jdbc interpreter:

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!

I'm trying to use the standard TSQL DECLARE syntax but I get the following error:

[%msg: < IDENTIFIER expected, @ found^  DECLARE @>]

How do I declare a variable?  For instance:

I need to guarantee that a parent AND child rows has been inserted successfully before any other process is able to read ANY of the data.

What is the proper way to prevent DIRTY reads?  Per the InterSystems's documentation I should be able to use ' START TRANSACTION ISOLATION LEVEL READ COMMITTED'.  I have attempted to use these commands in embedded and dynamic SQL calls to no avail.

I have to be able to insert the data via JDBC calls, but legacy Caché (.MAC) may be reading the data, and if the data is read to quickly, I could have processing errors, as all the child rows have not been inserted.

I am currently working on a issue with WRC on one of my Inbound SQL Adapters not returning all the records it should be. If I looked at the count of the records in Ensemble and compare it to that of a Microsoft SQL View, Ensemble seems to be off by a few records here and there. I am using a full dynamic select statement in my settings of the adapter.

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 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, I would like to read a row out of an external SQL table and reference the returned results directly from my cache class. I've set up a  link table and a SQL gateway connection. but I'm not real sure how to use them in COS. Anyone have examples out there, or some assistance? Thank you.

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.

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(pRequest As osuwmc.Paceart.DataStructures.SelectPaceartBillingExtract, Output pResponse As EnsLib.SQL.