#JDBC

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

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'. ClientConnectionId:ade97239-c1c8-4ed1-8230-d274edb2e731 "

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

  This error appears:

My jdbc interpreter:

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

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.

This is a sample Ensemble/Health Connect production which demonstrates how to receive an HL7 order (ORM) inbound from a file, extract fields (in this case, basic demographic information), and insert those into a table in an external SQL database via ODBC.  

Included in the zip file:

• Exported code
• Sample ORM message
• 'How to configure' doc 

Has anyone use any other setting then -1 on the Stay Connected setting on a SQL.OutboundAdapter? I am having issues with a particular database and wonder if it would be beneficial since I only connect once a day to read the data from the Adapter to create an HL7 message.

Thanks

Scott Roth

Hi all.

I have some problem with getting data from external source (PostgreSQL) via JDBC and load it in DeepSee and I resolved some problems while writing this question :)

So..Below I proved my steps of working:

Hi

I am fairly new to Ensemble development.

I was wondering what is the best process of returning the resultset from a SQL outbound adapter to the BPL that had invoked it.

Thank you for your help.

Kind Regards,

Salma

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

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.

I defined the variables in Cache, as follows:

Property Content As% Stream.GlobalCharacter [Required, SqlColumnNumber = 3, SqlFieldName = B_Content];

And then use the SQL statement to add:

insert into table_name (B_Content) values ('very long string');

But the error:

Apache Spark has rapidly become one of the most exciting technologies for big data analytics and machine learning. Spark is a general data processing engine created for use in clustered computing environments. Its heart is the Resilient Distributed Dataset (RDD) which represents a distributed, fault tolerant, collection of data that can be operated on in parallel across the nodes of a cluster. Spark is implemented using a combination of Java and Scala and so comes as a library that can run on any JVM. Spark also supports Python (PySpark) and R (SparkR) and includes libraries for SQL

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.

SELECT Text, PhysicianLastName, PhysicianFirstName, PhysicianAddress1, PhysicianAddress2, PhysicianCity,

PhysicianState, PhysicianZip, PhysNum, InsertDate, FaxK, EnsIndex, MRN, AccountNum, FirstName, LastName

FROM vUHEpicMailEns
Order By FaxK

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.

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.

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

i have no proleme for using

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!

Hi

I created a jdbc connection in the Caché 2010.2.3  with SQLServer 2008R2.

The connection to this DB works correctly. (Conection Sucess)

I try to perform table binding but this connection, even though it is successful, does not load my tables and schemas.

I did the same test on Caché 2015 , with same jar drivers files and works perfectly!

Any idea?

Caché 2010.2.3

Red Hat Enterprise Linux Server release 6.2 (Santiago)

java version "1.7.0_09"

Caché 2015

Red Hat Enterprise Linux Server release 6.2 (Santiago)

java version "1.7.0_09"

Hi!

                  I'm using Caché JDBC driver to store, retrieve and some other operations. I have another question, "Is there any possible to execute the cache commands using JAVA".

Thanks

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?

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

I've asked a lot of questions leading up to this, so I wanted to share some of my progress.

The blue line represents the number of messages processed.  The background color represents the average response time.  You can see ticks for each hour (and bigger ticks for each day).   Hovering over any point in the graph will show you the numbers for that period in time.

This is super useful for "at a glance" performance monitoring as well as establishing patterns in our utilization.

Here is the query used:

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.

It would be convenient to be able to fetch the whole query result in one operation instead of looping through every row. A fetch_all() method or something similar would provide this functionality I guess. Is there a reason why this is not implemented?

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.

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

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?

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