#SQL

Is there a way to lock at the record level? I know that you can lock at the table level:

&sql(UNLOCK mytest IN EXCLUSIVE MODE)

but am unable to find a way to lock just at the record/row level. 

Would this also affect a SELECT statement done on the record?

I have the following class definitions:

Class PET.RadioTracer Extends %Persistent
{
Property TracerName As %String;
}
Class PET.Injection Extends %SerialObject
{
Property RadioTracer As RadioTracer;
}
Class PET.ImageStudy Extends %Persistent
{
Property Injection As array Of Injection;
}
Class PET.ImageFile Extends %Persistent
{
Relationship Study As PET.ImageStudy [ Cardinality = parent, Inverse = ImageFiles ];
Property InjKey As %String;
}

I can refer to the TracerName for a given ImageFile object with the following syntax:
obj.Study.Injection.GetAt(obj.InjKey).RadioTracer.

We have a new requirement being push down by our Data Security to no longer use Local SQL Accounts to access our Databases. So they asked me to create a Service Account that is on the Domain for our connections to each database.

I tried just changing my JDBC connection to using this Service Account and Password but I am not having any luck trying to connect to the database.

" Connection failed.
Login failed for user 'osumc\CPD.Intr.Service'.

Hi

I need to query my messages and filter by a XML node.  

In the message viewer it would be Critetion Type = 'VDoc Property Path' and class = 'Enslib.Edi.XML.Document'.

At the moment my query looks like this

SELECT *

FROM EnsLib_EDI_XML.Document

where doctype = 'avc:mydoctype'

I need then to look for 'HeadNode:SubNodeValue'

Anyone who could help me out with this?

Thank you

I have finished my 4th book about Caché and MUMPS. This will probably be my last.

I am deeply grateful and humbled for all the help I have received from this group and the WW Response Center.

You all have something very special going here. 

Unlike most groups like this, you help those who are struggling to get started, that is unique.

Never lose the attitude of graciously helping the beginner. 

=================================================================================

Following is the book cover, available on Amazon or Smile.

Hello there,

I have some problem with inserting date to table using dynamic sql.

I have Country class. This table has relationship with class Continent as parent and child. In addition I have another statistic class where property Country has type of Country class. I get ID such as "1||1" next I execute dynamic sql INSERT INTO Stats(Country) VALUES("_CountryId_"). Then I select Stats table and see that value of Country column is "11") As a result a can't get Country object.

Please advice solution of this problem

Thank you

Hi,

I have a question related with  EnsLib.SQL.OutboundAdapter adapter.

Is there a way to open an oracle transaction and do either commit or rollback depending on the query result?

Is posible to keep open an oracle transaction to execute a lot of queries on different methods on the same business operation and do a commit when the business process finished?

Thanks and Best Regards

Sergio Vidal

I'm trying to have my REST service return the entire data set for one of our legacy globals.  Currently I am parsing the object from a SQL statement into generic objects to be returned like this:

         Set specimenArray=[]
        
        WHILE resultSet.%Next() {
            Set specimen = {}
            Set specimen.SpecimenId= resultSet.SpecimenId
            Set specimen.ProjectId = resultSet.ProjectId
            
            Set Oref = specimenArray.%Push(specimen)
        }
        
        Set tProxy = specimenArray.%ToJSON()
        set %response.Status = 200
        set %response.

I would linke to have the functionalities provided by the Table Field/Property Collation, but applying a transformation which differs from the provided ones.

Does anyone know if there is a way do accomplish that?

I have a class that has a property calledTags (like DescriptiveWords, but tags), where multiple tags are possible.  I am trying to decide on list of Objects vs. array of Objects.

Based on this post: https://community.intersystems.com/post/querying-list-property-sql, sounds like using an array of Objects is the better way to go. Indeed, I already noticed that it's not possible to have duplicates when using an array of Objects.

However, I am unable to make my queries on the array of Object use an index.

Hi Community!

Please welcome a new session recording from Global Summit 2017:

What's Lurking in Your Data Lake?

I am trying to fetch the data from cache database. But i got the error like "CSP application closed the connection before sending a responce".

Below is the query.

SELECT
CallbackComment
FROM SQ.CBPhoneResult_View Where PhoneDateODBC = '2018-04-09'

I have investigated and found that "CallbackComment" contains the special character single quotes " ' "  for one result and due to this i got this error.

In this field data is enter by customer. so we cannot restrict them like Do Not use single quotes.

Please provide some solution as soon as possible.

Thanks in advance. 

June 25, 2018 – Alert: Outer Join Query Results

InterSystems has corrected two defects that can cause SQL outer joins to return incorrect results. These defects can also impact DeepSee in Caché and Ensemble, as well as InterSystems Business Intelligence in InterSystems IRIS; in these cases, building and synchronizing some analytic models may result in build errors.

These issues exist on all platforms for the following released InterSystems Data Platform products:

• InterSystems IRIS Data Platform 2018.1.1
• Caché and Ensemble 2017.2.0 and 2017.2.1
• HealthShare Health Connect 15.03 on Ensemble 2017.2.0.

I'm facing a specific sort problem.
There are several thousands of articles sold all over. 
Users expect to get a description in local language sorted by their specific collation.

I have a serial object:

Class EmbedObj Extends %SerialObject

which is stored as a  property of another object

Class ContainerObj Extends %Persistent

Property InnerObj As EmbedObj;

Property Foobar As %String;

Question:

From within the context of an instance of EmbedObj, how can I navigate to the containing instance of "ContainterObj" and find that value of its Foobar property?

Harder Question:  Is there a way I can do this as part of SQLComputeCode? (my EmbedObj has a Calculated property which now needs to depend on the value of property Foobar of the containing object).

I have several stored procedures that when I execute them they will only return a single snapshot. In my BP I have been setting this to a Snapshot variable then looping using a WHILE through the snapshot variable just to get that single value.

Since it is only a single row, is there an easier way where I don't have to do a WHILE loop to pull the values out of that row? Can I call First Row or something like that to get me just the row into the Snapshot variable?

Hello,

I want to know if there is any possible way to use the same query for different ODBC  schemas.

Thanks.

Does anyone have any sample code for updating Caché users (and specifically Full Name and Comments) via SQL?  

Bonus points for knowing specifically what security resource needs to be assigned to allow someone to run the SQL.

Thanks in Advance!

Ben

Without installing Kerberos has anyone Authenticated a SQL JDBC connection? Currently we are using local SQL Accounts to sign onto External SQL Databases, but we are being told that we need to switch to Service accounts that live on a Active Directory Domain. 

I wrote with a little help a ZAUTHENICATE to do the Authentication for Ensemble, can I use something like that to connect to an External SQL Database using a Service Account on a Active Directory Domain?

Thanks

Scott

I  have generated a class using the linked procedure wizard however  I can't get it to work if the  datatype  of one of the parameters is VARCHAR(MAX).  It works fine if I change it to say VARCHAR(500) and  rerun the  stored procedure wizard. 

I get the following  error returned.:

ErrorMsg:   SQLState: (07002) NativeError: [0] Message: [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error

Can anyone see what I am doing wrong?  I think it is to do with the way I am using the  %Stream.GlobalCharacter?   Any help is much appreciated.

Hi All

I'm looking for the a simple-quick-easy solution to monitor a SQL table thought Ensemble.

I have a process that update a DataBase ,a scheduled task that runs every night  (Not Ensemble)

In the end it updates a table (replica_status) with a new recored with two fileds:  Id, DateTime

I looked around the community but didn't find an answerd case.

I'm thinking on a Task that will run a sql outboud adapter BO that checks that table and send a alert if no new record was created yesterday

is this the right approach or is there's a better solution?

Thanks Gadi

Hi Community!

New session recording from Global Summit 2017 is already on DC YouTube Channel:

SQL – Things You Should Know

Hello glabal masters,

I am trying to develop WAS using Cache and Spring Framework.

The ON DUPLICATE KEY UPDATE is not working.

The documentation only provides the syntax but does not provide any sample DDL for this case.

I am modifying some DB2 queries to Cache SQL but always meet some errors, please provide a sample query for this case.

Thanks.

I have  a SQL  procedure that I call into and it does some processing and passes back any data validations that fail. I would like to receive those message in my operation and act upon the message received .The question is how do you get output messages from a stored procedure I am using MsSQL 2012  with ensemble

Hello Global masters!

I developed Web with Spring framework (JAVA, Jdbc Cache) and I wrote SQL when I made it.

And Window program with VB6, Cache Object and VisM.

Is there a significant performance gap? SQL and Cache Object?

same function, same computer performance just different Java and VB6.

Thank you.

Minsu.

Hello Global masters.

I am junior developer about Intersystems cache in South Korea.

I want to develop process management program on server.

My code :

TESTPID(IP)    ;
    #include %syPidtab
    Set Rset = ##class(%ResultSet).%New("%SYS.ProcessQuery:ListPids")
    d Rset.Execute()
    &sql(SELECT ClientIPAddress, Pid INTO :ClientIPAddress, :Pid
    FROM %SYS.ProcessQuery 
    WHERE ClientIPAddress = :IP)
    While Rset.Next() 
    {
​    i SQLCODE'=0 w !,"SQL Error "_SQLCODE continue ; 100 means process does not exist (halted)
    I Rset.GetData(1) = Pid D
    . w !,Rset.

My new question regards connecting to the external database to insert the data. I have the following code:

#include %occInclude
// winsurge6
// insert winsurge data into cancer registry database
// Create new Gateway connection object
set gc=##class(%SQLGatewayConnection).%New()
If gc=$$$NULLOREF quit $$$ERROR($$$GeneralError,"Cannot create %SQLGatewayConnection.")
//Make connection to target DSN
s pDSN="CGDEV"
s usr="WINSURGE_DMP"
s pwd="xxxxxxxx"
s sc=gc.Connect(pDSN,usr,pwd,0)
If $$$ISERR(sc) quit sc
if gc.

Any insights, news, alternatives or experience about sequence pattern querying like with Oracle's MATCH_RECOGNIZE, more declarative, less simple direct COS way? Both obvious solutions (shadowing data to oracle cloud or implementing MATCH_RECOGNIZE compiler in COS from scratch) seem big overkill.

I have a lookup table and record batch Table I would like to do a count on the records stored in that batch by counting the number of records in that batch that have  a certain key on the responseKey column. This column keys are stored in the look up table for comparison. So I would like to do a join sql pivot that will use my keys stored in the lookup as columns and count as values

so far I have managed to do this but this is not efficient I would like to fire that sql once not on every count

SELECT * FROM
(
SELECT tMatch.DataValue, Count(*)
FROM ResponseRMAP.