Haven't found this in the documentation. I want:

&sql(SELECT name INTO :var FROM :table)

Is it possible?

The Caché System Management Portal includes a robust web-based SQL query tool, but for some applications it’s more convenient to use a dedicated SQL client installed on a user’s PC.

SQuirreL SQL is a well known open source SQL client built in Java, which uses JDBC to connect to a DBMS. As such, we can configure SQuirreL to connect to Caché using the Caché JDBC driver.

Finding Caché’s JDBC driver JAR

The JAR file containing the Caché JDBC driver is installed automatically by the Caché installer when installing a full Caché instance or when installing client components only.

Hi!

Is there a method in a persistent class that can return the current number of records/persistent objects in it?

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

Introduction

In the first article in this series, we’ll take a look at the entity–attribute–value (EAV) model in relational databases to see how it’s used and what it’s good for. Then we'll compare the EAV model concepts to globals.

Sometimes you have objects with an unknown number of fields, or perhaps hierarchically nested fields, for which, as a rule, you need to search.

Take, for example, an online store with diverse groups of products. Each product group has its own unique set of properties and has common properties as well.

See Part 1 here.

Part 2: Index Handling

Now you have a good idea of what kind of indices you need for your class and how to define them. Next, how do you handle them?

Query Plan

(REMEMBER: Like any modifications to a class, adding indices in a live system has its risks – if users are accessing or updating data while an index is populated, they may encounter empty or incorrect query results, or even corrupt the indices that are being built.

Hi

Sometimes I search mesage with inappropriate sql, It caused query hanged long time. 

So  Is it have way to find these hanged query and kill it?

Tks.

Hi Developers,

New to machine learning? Watch this video on InterSystems Developers YouTube to understand the basic concepts of machine learning and how it provides value in applications around the world today: 

⏯ What is Machine Learning?

During runtime I build an object which is essentially a wrapper over in-memory table:

I want to use this object as a part of INSERT or UPDATE queries, based on a value of some column (the main use case one of the columns is an ID value)

What's the best way to expose the object to SQL?

When I describe InterSystems IRIS to more technically-minded people, I always start with how it is a multimodel DBMS at its core.

In my opinion that is its main advantage (on the DBMS side). And the data is stored only once. You just choose the access API you want to use.

• You want some sort of summary for your data? Use SQL!
• Do you want to work extensively with one record? Use objects!
• Want to access or set one value and you know the key? Use globals!

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

Hey Community,

my Caché Version: 2013.1 and can't update now.

is it possible to highlight SQL Statements like in embedded SQL with all the features from SQL Statements?

Actually I use SQL Statements with a simple string like:

set myquery = "SELECT TOP 5 Name,DOB AS bdate,FavoriteColors FROM Sample.Person"

But when the queries are more complex it will be very cluttered and unstructured.

It would be nicer if I could set line breaks (I know here could I use indexed variables) and have highlighting like in an embedded SQL:

&sql(

Best regards!

Hi Community,

We're pleased to invite you to join the upcoming InterSystems IRIS 2020.1 Tech Talk: Data Science, ML & Analytics on April 21st at 10:00 AM EDT!

In this first installment of InterSystems IRIS 2020.1 Tech Talks, we put the spotlight on data science, machine learning (ML), and analytics. InterSystems IntegratedML^TM brings automated machine learning to SQL developers. We'll show you how this technology supports feature engineering and chooses the most appropriate ML model for your data, all from the comfort of a SQL interface. We'll also talk about what's new in our open analytics offerings. Finally, we'll share some big news about InterSystems Reports, our "pixel-perfect" reporting option. See how you can now generate beautiful reports and export to PDF, Excel, or HTML.

Hey Community,

my Caché Version is 2013.1 and I can't update now.

I want to serialize a SQL Answer row into an Array filled with objects and then convert it to json.

Actually I use the following, which is very error prone when I have to do that often:

set list = ##class(%Library.ListOfDataTypes).%New()

       &sql(
            declare queryONLWK01 cursor for 
            select F0103, F0104
            into :articlenumber,  :amount
            from GL.ONLWK01
            where F0001 = :BET and F0002 = :DEB and F0003 = :MIT
       )
        &sql(open queryONLWK01)
        &sql(fetch queryONLWK01)
        while (SQLCODE = 0) {
            
            
            set articleObject = ##class(%ZEN.proxyObject).%New()
            
            set articleObject.articlenumber = articlenumber
            set articleObject.amount = amount
            
                do list.insert(articleObject)
        } 
    }

Hi,

I issue a sql like follow:

select *, %vid from (select * from order_info) where %vid  between 1 and 10

I got error

 [SQLCODE: <-400>:<Fatal error occurred>]

  [%msg: <Remote JDBC error: ORA-00911: invalid character >]

So ,what's the correct paging syntax for query external data?

Tks.

This is the first in a pair of articles on SQL indices.

Part 1 - Know your indices

What is an index, anyway?

Picture the last time you went to a library. Typically they have books sorted by subject matter (and then author and title), and each shelf has an end-plate with a code describing the subject of its books. If you wanted to collect books of a certain subject, instead of walking across every aisle and reading the inside cover of every book, you could head straight for the bookshelf labelled with your desired subject matter and choose your books.

I am experimenting with Relationships, both Parent to Child and One to Many.

I have done some SQL look-ups and have searched through the documentation, however not in a lot detail, but wonder if there are more and better ways to access both sides of Relationships through SQL?

Thank you in advance for any help provided.

Hi Community!

Enjoy watching the new video on InterSystems Developers YouTube and learn about IntegratedML feature:

⏯ What is IntegratedML in InterSystems IRIS?

Hey Developers,

We're pleased to invite you to the upcoming webinar "Best Practices of In-Platform AI/ML" by InterSystems on April 3rd at 10:00 (UTC+3, Moscow time)!

When using CallableStatement to call a stored procedure, the log shows an error. SqlCode-400.

This is my calling statement.

CALL web_DHCENS_CDSS.patadviceinfo_patadviceinfo(?,?,?)

InterSystems IRIS 2020.1 brings a broad set of improved and new capabilities to help build important applications. In addition to the many significant performance improvements accrued through 2019.1 and 2020.1, we are introducing one of our biggest changes in recent SQL history: the Universal Query Cache. This article provides more context on its impact to SQL-based applications at a technical level.

Hi ,

I have a code written in cache  sql and trying to understand it, below is the code , can anyone help me understand what does that mean 

ex - !! dosage_unit !!

SELECT (CASE WHEN (order_description IS NULL OR (order_description='')) THEN '' ELSE (order_description) END) !! (CASE WHEN (dosage IS NULL OR dosage_unit IS NULL OR (dosage='') OR (dosage_unit='')) THEN '' ELSE (', ' !! dosage !! ' ' !! dosage_unit !!

Thank You in advance.

Is there a way to query the database structure?  In SSMS there are queries for finding tables with a column with a certain name (using LIKE).  And there is the redgate tool SQL Search.  But I'm not sure how to go about looking for columns that have say a value of 'PATID' and returning all tables that match.  Does anyone know?

Temporary tables are tables available for a current process only (and destroyed when process ends).

What are you approaches to creating temporary tables?

1. Process-private Globals storage can be used as a data global in storage definition. That way, each process can have its own objects for the class with ppg storage.  Here's how. Here's how 2.
2. InterSystems TSQL supports #tablename temporary tables. A #tablename temporary table is visible to the current procedure of the current process. It is also visible to any procedure called from the current procedure.

Greetings Developer Community!

InterSystems IntegratedML (formerly known as QuickML) is ready for external beta, and is looking for some users to kick the tires!

Hi All,

I need to build a search using a lot of filters, what's the best way to implement my sql requests.

1 sql request per property, or only one "big" sql request ?

what's the disadvantage of using lot of indexes ?    Thank you

KR,

Amine

Where can i get the latest odbc providers from SQL Server 2012/2016?

I see some posts on line to  an ftp site out there but I am unsuccessful at getting access to it.  I have a version loaded on my server but get errors when querying cache'.

"[Cache ODBC][State : 22005][Native Code 22005]" 

Any help would be appreciated.

the one we have might be version  2014.01.05851  64 bit (would that make sense?)

Let's say I start with a date range of '1-5-2019' to '5-25-2019' that occurs on one row.  I'd like to ultimately have this show as 5 rows in Crystal Reports as shown below

Result

1-5-2019  1-31-2019

2-1-2019 2-28-2019

3-1-2019 3-31-2019

4-1-2019 4-30-2019

5-1-2019 5-25-2019

I found a result that worked in T-SQL, but I'm not sure how to translate it to Cache SQL.  The T-SQL code is

select dateadd(d,N.number,d.begindate) adate, data
  from data d
  join Numbers N ON number between 0 and datediff(d, begindate, enddate)

This code fetches a row for every day between the begin date and end date.

In T-SQL, I can create a recursive CTE to create a subquery or a view that will hold a whole lot of numbers.  Is there a way to do this in Cache SQL?  I'd like the numbers from 0 to 10000 if possible. 

Thanks!

Hi,

Do you have any experience in server side pagination with IRIS using Angular on the client side?
Any idea on a typical SQL request on the server side?

Thanks!
Blaise