#SQL

Hi folks!

Sometimes when we develop a mockup or PoC there is a need for a simple interface that will provide data in IRIS in JSON against SQL queries.

And recently I contributed a simple module that does exactly that:

accepts SQL string and returns the JSON.

How to install? Just call:

zpm "install sql-rest"

If you install it in a namespace X it will setup a /sql endpoint to your system that will accept POST requests with SQL string and will return the result for you for the data available in the namespace X.

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

Hello,

I have established an ODBC connection with our client based on credentials they provided.    The test connection was successful and after using the Link Table wizard I can now see the client's tables and properties.    However when we try to execute a query in IRIS we are getting error code -226.   I'm trying to determine if this could be just a simple setting problem in IRIS or something with the client.   

We have tried with PostgreSQL as well as with TSQL.  Same result for each.

Following instructions on this page https://docs.intersystems.com/latest/csp/docbook/Doc.View.cls?KEY=D2IMP_... I am trying to create a Data Connector to be used as a base class for a cube with update support.
That page suggests that putting an SQL query inside XData block is not suitable for a Data Connector that supports updates.
Later it also suggests that to enable updates your SQL query must include 

Hello fellow community members,

I would like to offer my services as an Intersystems Professional and am available to work on projects. 

I have more than a decade experience into  Intersystems stack of technologies including IRIS, Ensemble, Healthshare, Healthconnect, Cache Objectscript, Mumps, Zen, Analytics etc. with companies spread over US and UK  involved in multiple domains.

In the Inbound SQL Adapter settings, is it possible to specify more than 1 field as the Key Field Name? 

Because of the way the Query is being index in Ensemble by the Key Field Name, sometimes transactions get missed and I would like to see if we can add an additional key to the mix to ensure all the transactions are picked up. In this case the InterfaceTrigger is an ID that is auto generated by the table, and I would like to use that as well to ensure we don't miss transactions, and it does not throw any warning messages when it executes the Delete Query.

Does anybody know if there is an easy way to know if the execution of the command $System.SQL.PurgeForTable went well/wrong?

The documentation describes that a string is returned, but in fact there is a nice Quit "" in the code.

Maybe inside PurgeForTable^%apiSQL there is any process variable set when it goes well/wrong?

Thank you very much!

Given a complex method flagged with [ SqlProc ] so it is available as an SQL stored procedure, what's the best way to report a non-system error detected in that method - say, for example, an error %Status - so that the SQL query calling it fails descriptively? Is it best to create and throw an exception, or are there special % variables involved (like in a trigger)? I haven't been able to find an answer in the documentation.

Thanks in advance!

I have an SQL text index defined like this

Index TextIndex On (Text) As %iFind.Index.Basic(IGNOREPUNCTUATION = 1, INDEXOPTION = 0, LANGUAGE = "en", LOWER = 1);

If I run a query like this:

SELECT
Text
FROM Post
WHERE %ID %FIND search_index("TextIndex",'ABC')

I get 20 results with posts containing the term ABC.

But each post can contain the term ABC several times.

Hi folks!

Do we have an embedded REST API in IRIS which accepts arbitrary SQL and returns data in JSON?

Or if anyone did something like this?

The idea is to have something like MDX2JSON but SQL2JSON.

Thanks!

Hello community,

I need to know what permissions or resources, apart from read for the tables, a user would need to view the catalog via ODBC? The ODBC user can run queries sucessfully but cannot get the catalog from the DB via ODBC. Whereas a a user with %All can fetch the catalog.

Any help would be much appreciated.

Regards,

Juergen

Hi All, I am a Data Architect and am trying a method of deploying alter statements onto Cache DB.

My Data Modeling tool generates alters as this.. So question is if the Cache has a way to rename table? If so what is it?

ALTER TABLE <SchemaName>.GROUP_TYPE_NODES RENAME TO GROUP_TYPE_10082019140110000
;

This is giving error.

Hi,

I've a Service utilising the Adapter EnsLib.SQL.InboundAdapter, which uses a Credentials item set with the details of a local SQL account. This currently works, however, we're looking to use the credentials of an AD domain account.

The domain account is a member of an AD security group, which has the required permissions on the source SQL database. I've checked that access is possible with this account via SQL studio.

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)