New post

#SQL

16

SQL is a standard language for storing, manipulating and retrieving data in relational databases.

Customer has a connection set up to connect to an ISC sftp server but it keeps failing the connection with:

ERROR <Ens>ErrOutConnectFailed: SFTP Connect failed for sftp.il.intersystems.com:22/NPPES/SSL='!SFTP'/PubKey=''/PrivKey=''
with error ERROR #7500: SSH Connect Error '-2146430933': SSH Error [8010102B]: Failed getting banner [FFFFFFFF8010102B] at Session.cpp:238,0

A manual connection can be made successfully and I have verified that the credentials are correct. The Connection Settings are:

SSL Configuration - !SFTP

0 1
1 71

It is probably somewhere in the doc. So hard to search. Hope InterSystems going to AI on their community/support data.

How to see a meaningful value in management portal - sql explorer. For instance
Class MySetting Extends %Persistent{
Property Name As %String;

Class MyObj Extends %Persistent{
Property Setting As MySetting;

0 1
0 53

Hello,

In my project, I am calling a store proc on third party database. I am using SQL Gateway and ODBC. Everything is working fine until the store proc changed. In store proc they have changed the return alias name for a column. In Ensemble Snapshot I am not seeing the modified alias name until I restarted the whole Healthshare instance.

Here is my question, is there any other way? We do not want to take the instance down if it happens after we go live.

Thanks

Raghu

0 1
0 546
Question
· Jun 22, 2017
Hash values of columns

I try to find a function, which generates hash values of columns. In MS SQL Server I can use

select hashbytes('sha2_256', my_column) ...

to create hash values of my_column. Is it possible to use such things in Caché?

Thank you
André

0 1
0 1.1K

Ok so I am way outside of my comfort zone, and had to build an application using CSP to give users the ability to access SQL configuration tables. These SQL Configuration tables will affect the data that is sent to the downstream system.

I saw in the examples where we were able to import GIF's/IMAGES into the CSP folders to use as a reference in our CSP pages. My question is how do you do that? If I try to import through studio, it tells me the file is invalid.

Just trying to make it a little more user friendly then blocks on a page.

0 1
0 810
Question
· Dec 4, 2020
SQL Query returns x00

Hey Intersystems Community-Member,

Following Issue: When I update a SQL table like (update OnlineV3Admin.ParameterApp set popuptext = '' where ID = '1') and then read it for my Json WebService there is a /"x00" in my Json.

What is the best option to disable that? I need here a empty String and not /x00.

Any ideas?

Best regards,

Florian Hansmann

0 1
0 235

In Oracle database, the synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.

You generally use synonyms when you are granting access to an object from another schema and you don't want the users to have to worry about knowing which schema owns the object.

Is IRIS SQL sintax has something like this: (oracle sintax)

1 1
0 181

When using "IDENTITY" as my primary key, I can select the last inserted ID with

SELECT LAST_IDENTITY() FROM %TSQL_sys.snf;

Actually this is how Hibernate + Iris' Driver acquires the inserted ID when mapping with

@GeneratedValue(strategy = GenerationType.IDENTITY)

Now, considering that I am using the type "SERIAL" as my primary key instead, how can I get the last inserted ID?

Note that with "SERIAL" I can forcefully insert any value for this ID, from which Iris will continue generating values...

0 1
0 235

Hi,

I am trying to input an entire xml into a sql server table. I am doing this via an operation in Ensemble.

I keep getting the following error

[SQL Server]XML parsing: line 0, character 0, unrecognized input signature

I have setup the table with a column parameter XML.

Here is the request object that is being sent to the store procedure

0 0
0 345

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?

1 0
0 91

Hello Community,

I'm executing the same query with same column name but in different case. An unique cached query generated while query executed first time. The query preparser only normalize the keywords and send to the SQL engine generates the Hash. Eventually use the cached query next use.

Now my question, The hash values are same for both of the queries. Then why it creates two cached queries.

Query1: select * from MyLearn.Test where Name['Kev1'

Query2: select * from MyLearn.Test where NamE['Kev1'

0 0
0 35

I am exporting data from Cache using the SQL export wizard. This is on a docker image of Cache hosted on a Linux server.

I select my database, schema, and table, make sure that all columns are being exported, check other settings, and then finish the export.

All server settings look correct and permissions are rwe on the directory. I have tried various directories, including a system level directory. I tried creating the file before I do the export in case there was an issue with creating the files, and I also made sure the created files had rwe permisisons.

0 0
0 76

I'm trying to execute SQL on a EC2 via SSM:

import boto3

instanceid = "i-123456789"
sql = """SELECT path FROM Security.Applications WHERE ID = '/csp/sys'"""
template = """su - irisusr -c 'cat << EOF | iris sql iris -U %SYS
                """ + sql + """
                        q
                        EOF'
                       """
template = [line.strip() for line in template.splitlines()] 
template = """\n""".join(template) 
ssm_client = boto3.client('ssm') 
response = ssm_client.send_command(
            InstanceIds=[instanceid],
            DocumentName="AWS-RunShellScript",
            Comment=AWS,
            Parameters={'commands': template})

0 0
0 138

Hello all,

I'm trying to build a cube based on a linked table but seems that IRIS is not able to do it :O

Long story short, I have a linked table in IRIS that sources a Microsoft SQL table (using standard linked feature from the portal). It works fine, I can access it using SQL as many other times. On top of that, I've created in DeepSee (ok, Analytics) a cube that uses this class as source. It compiles correctly, no errors given. When I build it with 100 records, all goes well and using Analyzer I can see results.

0 0
0 108