#SQL

When using the JSON_OBJECT() function in Caché SQL on a %String property that contains JSON syntax, it converts the %String into a JSON object instead of escaping it as a string literal. How can I prevent this? (without ridiculous hacks like "add a space to the beginning of the value" as we don't always know which properties will contain these values and I certainly don't want to have to check for nulls and add/remove a space every single place this value is used in the application)

I don't want these strings automatically marshalled into JSON objects.

Hi

Is this a bug?  We came across it on an older version of Cache but I've also just tried it in version "Cache for Windows (x86-64) 2017.2.2 (Build 865U)" and got the same result.

Create a Class like so:

Class My.Email Extends %Persistent
{
Property AddSeq As %Integer(MAXVAL = 32767, MINVAL = -32768) [ Required ];
Property EffBegDat As User.SQLdatetime;
Property EffEndDat As User.

Greetings all,

I have a quick SQL question.  I am working with a class, which has a calculated property, which in turned indexed.
The calculation for this property is created based on another property value via: 
SqlComputeOnChange = attributes  - so basically every time I add a property attribute to my class, my new property 
is calculated based on some SqlComputeCode which calls some class method.

That initial setup works as designed.

Question: If my code for whatever reason gets to a ROLLBACK, does the calculated property always rolls back,
and it's index always cleared?

Hi,

Most of my classes are mapped from Globals. I want to access Cache classes from a BI software through ODBC connection.

'Last update' information does not exist in most of the classes. My question is whether there is a 'last update' timestamp that is automatically generated for each line in classes I can extract to external systems?

Thanks

Hi Everyone,

I am trying to built a SSIS package thru BIDS Visual Studio 2013. My Datasource is a InterSystems Cache Database, I wanted to Import Tables records from the Datasource to MS SQL Server 2014.

As a Sanity check. I only created one Package to import one Table to MSSQL Server to try out. The connection to the InterSystems Cache Database was successful. The DSN for the InterSystems Cache Database is created in the System for 32 and 64 Bit.

Here is where my Problem lies.

Hello,

I have been trying to pull data through a linked server in SSMS from an InterSystems Cache Database for a while, this is to enable us to join the data to other source systems in our Data Warehouse.

I have set up an ODBC connection and a linked server to the database and can execute queries through OPENQUERY in Management studio, but the data is huge (> 100million rows). So when I execute a SQL query with a WHERE clause the query just spins.

If I SELECT TOP 100 with no where clause the query returns data, so I know that all the connections are correct.

What is the difference between %ID and ID in a database table? Both seem to reference the same column labelled ID.

For context, I am trying to create a viewer class for an existing persistent class.

Let us call the persistent class A, with SqlTableName = OldA.

The viewer class will be B with SqlTableName = A and ViewQuery = {select %ID, <other fields> from <some other class with the same fields as A>}

In A, there exists a class query: select %ID from A. However, A fails to compile, giving an error: "Field '%ID' not found in applicable tables". If the %ID is replaced with ID, the class compiles. 

Hi every body

I hope it's the right place & I'm not offending any body.

I'm a veteran Mumps/Cache/Ensemble programmer .  I think my only draw back is my age, although my brain is still young & bubbly.

I'm looking a long time for work without success.

I gathered a lot of knowledge & experience during those long years , developed many projects & i'm ready to do any work only to feel useful.

I'll add my resume, I hope & wish that this post will bring some change as I'm desperate to get some work.

Thank you very much Simcha my email is shiluvg@gmail.

Hello everyone,

I want to fill data into my select component with sql request and parameter.

I tried to follow this example https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KE… 

I need to call a method because my parameter can be different. I tried something like this but it doesn't work... 

ClientMethod changeParams() [ Language = javascript ]{var query = zenPage.getComponentById('categorieBeneficiaire');query.setProperty('parameter', 1, 'OS');quer

I have a ZEN page with nine tablepanes. Each tablepane queries a table in the same SQLServer db. I have a single SQLGateWay(odbc) to this SQLServer db. I need to get better performance when I query all nine table at the same time. Would my performance improve if I had nine SQLGateWays(nine odbc configurations/connections), one for each query? I would appreciate any and all suggestions for getting the very best performance when using SQLGateWays. Thank you.

I have created a view to stage some data in a different format and then want to reference that view in a SQL query from a table that filters the data from the view using a property of the table. 

Example:

select
   MsgId,
   FileName,
   (select
      ReportName
     from
      custom_view
     where
       MsgId = ReportId
    ) as ReportName
from
   main_table

Is this even possible? When I try this, I get an error table not found for the view?

David

Hi,

I am trying to have the output of a field in sql have double quotes around them. The script is in Studio and I am accessing It through a task.

I have tried """"tect"""" and others...Please help

Aloha

Lou

I'm running this code in our application, and in the terminal:

>set query="SELECT * FROM PMG_Data_Private.RemitInstance  WHERE  (VoucherNumber %INLIST ? SIZE ((10)))"

>set tRS = ##class(%ResultSet).%New("%DynamicQuery:SQL")

>set ok=tRS.Prepare(query)

>d $system.Status.DisplayError(ok)
 
ERROR #5540: SQLCODE: -76 Message: Cardinality mismatch between the SELECT-list and INTO-list
SQLTEXT: SELECT * FROM PMG_Data_Private.RemitInstance  WHERE  (VoucherNumber %INLIST ?

Hello,

I have a question about creating properties with curl.

I already did create properties in Java with the following command.

<DO db.%CreateProperty("TotalSteps","%Integer","$.TotalSteps")>

It created the property TotalSteps with the type %Integer and the data path $.TotalSteps (since the header of my data source is also TotalSteps).

Now I would like to create the same property in curl with the following command

<curl -i -X POST -H "Content-Type: application/json" http://localhost:53774/api/docdb/v1/namespaceName/prop/databaseName/ propertyName?

Hi everyone,

I need submit a object have a json string as property of class, but I get an issue, object can't save to SQL table if I take json data to json property. I try making a test class with only json property and get the same issue, no data row in SQL table after run ClassMethod . Some one know what's problem, please help me. Here my test class:

I would like to know , if its safe to use %NOLOCK  in insert query while doing some parallel processing in Ensemble. We will have pool size of more than 1 for a business process. The  business process  does an entry in internal meta data table , which will be used for some internal reporting. The same row might get updated by some other process  later point of time.  i could not  produce any error or any issues . Looking forward to hear some opinion on this.

SQL gateway.

It is not "compiling" correct the properties(get; set;)  in the class, and generating 2 methods of :

GetuProperty and SetuProperty.

so it's not equivalent to standard  methods interface handling.

Regards,

Emanuel

Hi Community!

We are pleased to invite you to the upcoming  webinar "Taking Control of Your SQL Performance" on 12th of September at 11:00 a.m. EDT!

SQL gateway.

There is a basic problem with sql gateway,

while building the proxy methods, with:

d ##class(%Net.Remote.Gateway).%ExpressImport(class, port, host, 0, classObj)
it causes error messages as something in the class is not correct,

while this class passed OK all Visual Studio of C#

and same class working OK under the %Activate Assembly link

I think there is also problem  with overload methods (same methods names).

Regards,

Emanuel

Hi all,

We have a big problem because we have the following message when I try to open the messages in a production.

ERROR <Ens>ErrException: <DATABASE>zfindStatement+29^%SQL.DynamicStatement.1 ^%sqlcq("MYNAMESPACE","Query",8,"Gns8AZO5dJclytqv13l9gUuLUyo=",""),c:\intersystems\healthshare\mgr\cache\ -- logged as '-' number - @''

SOURCE ELEMENT: %ZEN.Component.tablePane (resultsTable)

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.