#SQL

Hi, folks!

When you deploy DeepSee solutions you often do not want grant a User %All Role to work with a particular Dashboard.

Consider a Dashboard 'Dash' with a few widgets where listings are being used.

If you manage a Role to get access to the Dash you need to grant access to %DB_DBNAME resource to have a database access, grant access to a Dashboard resource (if any) and ... grant SELECT accesses to all the tables involved in SQL queries being used in all the listings of widgets.

Suppose you have an application that allows users to write posts and comment on them. (Wait... that sounds familiar...)

For a given user, you want to be able to list all of the published posts with which that user has interacted - that is, either authored or commented on. How do you make this as fast as possible?

Here's what our %Persistent class definitions might look like as a starting point (storage definitions are important, but omitted for brevity):

Hello,

I have a global whose structure is multi-level and I am trying through a class and a SQL query to display a table which includes all the values ​​and levels.

"SELECT %SYSTEM.SQL_TableExists('table name') "could work as expected under SQL shell,

but for "CALL %SYSTEM.SQL_TableExists('table name')" does not work (not any error reported, it just show nothing).

Is there any reason why CALL could not be applied to a stored procedure?

Thanks.

Hi,

Do you have any experience in inserting a panda dataframe into SQL IRIS Table?
The dataframe.to_sql method uses sqlalchemy and it seems no dialect is available for IRIS.

thanks,
Blaise

Hi Developers!

How do you handle errors with &sql a.k.a. embedded SQL?
I think I found a universal error-handling template which works in any situation:

&sql(SELECT * FROM Sample.Person)

if SQLCODE < 0 throw ##class(%Exception.SQL).CreateFromSQLCODE(SQLCODE,"Context description")

What is your choice?

Named parameters can be achieved with SQLAlchemy :

from sqlalchemy import create_engine, text,types,engine

_engine = create_engine('iris+emb:///')

with _engine.connect() as conn:
    rs = conn.execute(text("select :some_private_name"), {"some_private_name": 1})
    print(rs.all())

or with native api

from sqlalchemy import create_engine, text,types,engine

# set URL for SQLAlchemy
url = engine.url.URL.create('iris', username='SuperUser', password='SYS', host='localhost', port=33782, database='FHIRSERVER')

_engine = create_engine(url)

with _engine.connect() as conn:
    rs = conn.execute(text("select :some_private_name"), {"some_private_name": 1})
    print(rs.all())

Let's say we have two serial classes, one as a property of another:

Class test.Serial Extends %SerialObject
{
Property Serial2 As test.Serial2;
}

Class test.Serial2 Extends %SerialObject
{
Property Property As %String;
}

And a persistent class, that has a property of test.Serial type:

Class test.Persistent Extends %Persistent
{

Property Datatype As %String;

Property Serial As test.Serial;

}

So it's a serial, inside a serial, inside a persistent object.

Hello All,

what is the best method for search a portion of text in a non-indexed global?

I need to implement an autocomplete kind of search, in a global of >1M registers (text type, not $lb)

Maybe the best way would be use a SQL mapped class, with 'Bitmap' indexes?

Thanks in advance!

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:

Hello,

We have a need to track Database changes over time - down to the SQL level of granularity if possible. Such as: User xyz runs routine ^abc and we get something similar to a changelog that tells us: table A had this value updated, insert, update etc....

Is that possible using IRIS level tools (Audit Log, Journal File, etc...) , is there a way to convert the global sets and kills from the journals into SQL level changes?

InterSystems FAQ rubric

By specifying the start and end values ​​of the IDs for which you want to rebuild indexes in the arguments of the %BuildIndices() method provided in the persistent class (=table) definition, you can rebuild only the indexes within that range.

I am very new to IRIS. We are developing a PHP application that connects to an IRIS instance of one of our vendors. I am having trouble with a specific table. When I need to query this specific table for specific fields, I am receiving an error. However, if I change the call to get all columns (`*`), there is no error. I get 37K records returned.

Error:

Hi guys,

I'm trying find a way to create a row with the number of each row.

For example:

In the SQL above, i want a way to create a collum with the number of corresponding row, in this case:

1

2

3

In SQl SERVER i can use FOR, RANK or ROM_NUMBER to do this, but, i can't find a way to do the same in cache SQL.

Can someone help me?

Thank you.