SQL

This is a coding example working on IRIS 2020.1 and on Caché 2018.1.3 
It will not be kept in sync with new versions      
It is also NOT serviced by InterSystems Support !   

We use ExecureProcedure() to execute a stored procedure which returns a result set. But we see lot of "Invalid cursor state" errors when the result set is empty.  The connection to SQL server database is made through ODBC. 

ERROR #6022: Gateway failed: Fetch. + ERROR <Ens>ErrGeneral: SQLState: (24000) NativeError: [0] Message: [Microsoft][ODBC Driver 11 for SQL Server]Invalid cursor state 

Here is the code snippet from the business operation class which uses EnsLib.SQL.OutboundAdapter  

Hi Community,

See how IntegratedML in InterSystems IRIS can be used to make machine learning predictions directly from SQL commands:

⏯ IntegratedML in a Nutshell

Class Queries in InterSystems IRIS (and Cache, Ensemble, HealthShare) is a useful tool that separates SQL queries from Object Script code. Basically, it works like this: suppose that you want to use the same SQL query with different arguments in several different places.In this case you can avoid code duplication by declaring the query body as a class query and then calling this query by name. This approach is also convenient for custom queries, in which the task of obtaining the next row is defined by a developer. Sounds interesting? Then read on!

Hey, 

I have a value in epoch time in which I need convert in order to be able to lookup a table that has dates in YYYY-MM-DD HH:MM:SS format

I thought the below would work but it is not.

select COUNT ('Arrival Time') FROM dbo.table where 'Arrival Time' < DATEADD(ms, CONVERT(int,LEFT(1603173432000, 20)), '1970-01-01 00:00:00')

Hi

We have multiple edge production namespaces and want to run query to get errors/alerts from Ens_Util.Log table across namespaces. is it possible?

In MS SQL Server i can do this:

SELECT 
Category, CrewNumber, MedicalCertificationDate, Seat, SeatbeltUsed, Sex, ShoulderHarnessUsed, ToxicologyTestPerformed, childsub
INTO #tempfemale
FROM Aviation.Crew
WHERE Sex = 'F'

Would like to know if there is an alternative or better way to paginate through a dataset using dynamic SQL than what I am using below. The problem is that as the potential pool of data gets larger, this code slows down to the point of not being useable. In analyzing each line of code below, it appears the slow down is related to the initial rset.%Next() iteration. Is there anything available which does not require a subquery/%VID such as a simple LIMIT/OFFSET?

My code is similar to :

s sql=##class(%SQL.Statement).%New()

Hi,

I'm trying to ultimately create a function that I can use with HL7 messages that calls a stored procedure held on a SQL Server.

Initially I tried extending the function class to use the sql inbound adapter and/or EnsLib.SQL.Common, but this wouldn't work from the class method for the function.

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?

I am trying to create a Procedure in Caché, but this message is showing:

<UNDEFINED>frmit+118^%qaqpsq *mt("v",1)

This is the procedure:

CREATE PROCEDURE testebi.sp_cargainicial()
BEGIN
    INSERT INTO testebi.Fato_Atendimentos (
    PK_OsProcedimento )
    SELECT ID
     from dado.TblOsProcedimento    ;    

Hi guys!!

In the system that I work, I came across an iterator pattern that uses the %Resultset library without performing the close after executing the query. Does anyone know how to say what are the impacts of not performing such a procedure?

If you have any model of iterator pattern made in caché to recommend as a good example, I will be grateful hehe  :D

Caché2010

Caché2016

Date range queries going too slow for you?  SQL Performance got you down?  I have one weird trick that might just help you out! (SQL Developers hate this!)*

If you have a class that records timestamps when the data is added, then that data will be in sequence with your IDKEY values - that is, TimeStamp₁ < TimeStamp₂ if and only if ID₁ < ID₂ for all IDs and TimeStamp values in table - then you can use this knowledge to increase performance for queries against TimeStamp ranges.  Consider the following table: