SQL | InterSystems Developer Community

SQL

Syndicate content 27 

Description

SQL is a standard language for storing, manipulating and retrieving data in relational databases

Most votesArticlesAnnouncementsQuestionsNo answerNo accepted answerAll

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.

For example:

SELECT ID, JSON_OBJECT('ID': ID, 'Name':, LastName || ', ' || FirstName, 'Options': Options) as Item FROM Whatever.Whatever

Should give me:

1, '{"ID":1,"Name":"Smith, John","Options":"{\"Color\":\"Blue\",\"Count\":20}"}'

And instead is giving me the marshalled version:

1, '{"ID":1,"Name":"Smith, John","Options":{"Color":"Blue","Count":20}}

Continue Reading
Andrew Hubbard's picture
Post by Andrew Hubbard 19 November 2018
Last answer 20 November 2018 Last comment 28 November 2018
0   0 3
2

answers
10

comments
165

views

0

rating

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?   I assume so, and looking for confirmation.

Thank you,

Alex
 

Continue Reading
Alex Kogan's picture
Post by Alex Kogan 15 November 2018
Last answer 16 November 2018
0   0 2
1

answers
0

comments

85

views

0

rating

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

Continue Reading
Yaniv Ben Malka's picture
Post by Yaniv Ben Malka 6 November 2018
Last answer 10 November 2018 Last comment 12 November 2018
0   0 2
2

answers
7

comments
244

views

0

rating

Hello,

I have been trying to pull data through a linked server in SSMS from an InterSystem 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. Any guidance would be greatly appreciated!

Example of the query below:

SELECT *Â

Continue Reading
Scott Hawkins's picture
Post by Scott Hawkins 30 October 2018
Last comment 1 November 2018
0   0 2
1

answers
1

comments
161

views

0

rating

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. 

Continue Reading
Keshav Iyer's picture
Post by Keshav Iyer 30 October 2018
Last answer 30 October 2018
0   0 2
1

answers
0

comments

272

views

0

rating

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.com

Covering letter

Â

Continue Reading
Simcha Gershon's picture
Post by Simcha Gershon 20 October 2018
+ 1   0 1
0

comments

165

views

+ 1

rating

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?KEY... 

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

Continue Reading
guillaume lepretre's picture
Post by guillaume lepretre 18 October 2018
Last answer 19 October 2018 Last comment 19 October 2018
0   1 1
1

answers
1

comments
73

views

0

rating

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.

Continue Reading
Peter Kopp's picture
Post by Peter Kopp 17 October 2018
Last answer 18 October 2018
0   0 1
1

answers
0

comments

70

views

0

rating

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

Continue Reading
David Foard's picture
Post by David Foard 17 October 2018
Last answer 17 October 2018
0   0 0
1

answers
0

comments

76

views

0

rating

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

                       

Continue Reading
Lou Pizza's picture
Post by Lou Pizza 11 October 2018
Last answer 11 October 2018 Last comment 12 October 2018
0   0 1
2

answers
2

comments
135

views

0

rating

Mapping Examples

 

Clearly if you have a fourth article in the trilogy you need to go for the money grab and write the fifth, so here it is!

Note:  Many years ago Dan Shusman told me that mapping globals is an art form.  There is no right or wrong way of doing it.  The way you interpret the data leads you to the type of mapping you do.  As always there is more than one way to get to a final answer.  As you look through my samples you will see there are some examples that map the same type of data in different ways

Continue Reading
Brendan Bannon's picture
Post by Brendan Bannon 27 December 2016
Last comment 11 October 2018
+ 7   2 6
10

comments
879

views

+ 7

rating

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 ? SIZE ((10)))

It's not really a problem with the query, since there's no INTO keyword int he query, and I'm not getting it on QA or a production server -- using the same query etc.  Another programmer tends to delete cached queries when this happens.

However, I purged cached queries for this statement, this table, and for this namespace, and still receive this error (respectively).Â

Continue Reading
Laura Cavanaugh's picture
Post by Laura Cavanaugh 3 October 2018
Last comment 9 October 2018
0   0 3
2

answers
4

comments
308

views

0

rating

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?type= propertyType& path= propertyPath& unique=propertyUnique>

Port: 52773

namespaceName: fitnessnamespace

databaseName: teststream

propertyName: TotalSteps

propertyType: %Integer

propertyPath: ??

Continue Reading
Tuan Minh Do's picture
Post by Tuan Minh Do 3 October 2018
Last answer 4 October 2018 Last comment 4 October 2018
0   0 0
1

answers
1

comments
93

views

0

rating

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

Continue Reading
Hieu Dien Nguyen's picture
Post by Hieu Dien Nguyen 4 October 2018
Last answer 4 October 2018 Last comment 4 October 2018
+ 1   0 2
1

answers
1

comments
217

views

+ 1

rating

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.

Continue Reading
Suman Samanta's picture
Post by Suman Samanta 2 October 2018
Last answer 3 October 2018
+ 1   0 2
1

answers
0

comments

100

views

+ 1

rating

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

Continue Reading
Emanuel Lazar's picture
Post by Emanuel Lazar 5 September 2018
Last answer 5 September 2018 Last comment 5 September 2018
0   0 2
1

answers
3

comments
101

views

0

rating

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)

Continue Reading
Francisco López's picture
Post by Francisco López 5 September 2018
Last answer 5 September 2018 Last comment 5 September 2018
0   0 2
1

answers
3

comments
126

views

0

rating

SQL gateway. 'Locking' problem.

while debugging, is there in Caché a command to 'unlock' the instance of a class ?

close class, and kill class is not enough.

same problem in %Activate link

for each debug I have to exit (Halt) the terminal, 

make debug in C#

coming back , and re-load again in Caché.

Regards,

Emanuel

Continue Reading
Emanuel Lazar's picture
Post by Emanuel Lazar 5 September 2018
0   0 1
0

answers

0

comments

75

views

0

rating

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.TracerName

Is it possible to write an SQL statement to search the PET.ImageFile table to find a match based on the RadioTracer.TracerName?


 

Continue Reading
Virginia Rogers's picture
Post by Virginia Rogers 31 July 2018
Last comment 20 August 2018
0   0 3
0

answers

6

comments
327

views

0

rating

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'. ClientConnectionId:ade97239-c1c8-4ed1-8230-d274edb2e731 "

In reading some of the material about using a Domain Service Account it mentions having kerberos installed. Is this needed for Ensemble JDBC connection to a Microsoft SQL Database to work using a Domain Service Account?

Does anyone use JDBC connections to connect to non cache databases using a Domain Service Account? If so how was this accomplished?

Thanks

Scott

Continue Reading
Scott Roth's picture
Post by Scott Roth 8 August 2018
Last answer 16 August 2018 Last comment 16 August 2018
0   0 3
1

answers
3

comments
383

views

0

rating

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.Amazon,

the description, my special thanks, Robert, Evgeny, Dmitry,  Nikita,

and so many others. I apologize for not listing all your names

Continue Reading
Mike Kadow's picture
Post by Mike Kadow 9 July 2018
Last comment 10 August 2018
+ 12   1 2
1

comments
661

views

+ 12

rating

Good afternoon everyone,

I hope you are well,

I am currently working on the below opportunity and if anyone here would like to hear more details please feel free to contact me on 01908 886 030 or teo.rusu@identifiglobal.com

Met with the client yesterday, beautiful office in Waterloo London, latest tech available with many benefits including working from home 2 days/w as well!

Intersystems Caché Senior Software Engineer

London

Salary up to £75,000 (negotiable  for the right candidate)

An exciting Software as a Service company creating enterprise software used by 33,000+ people globally. They provides a secure, global cloud platform marketing teams use to rapidly expand services across channels and launch into new markets

Continue Reading
Teo Rusu's picture
Post by Teo Rusu 8 August 2018
+ 3   1 2
0

comments

217

views

+ 3

rating

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

Continue Reading
Alexandr Ladoshkin's picture
Post by Alexandr Ladoshkin 3 August 2018
Last answer 3 August 2018
0   0 1
2

answers
0

comments

151

views

0

rating

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()

s query="SELECT *,%VID FROM (SELECT prop FROM table WHERE prop=x) WHERE %VID BETWEEN 1 AND 100"             

s sc=sql.%Prepare(query)

s rset=sql.%Execute()

while rset.%Next() {.....

Continue Reading
MARK PONGONIS's picture
Post by MARK PONGONIS 17 July 2018
Last answer 18 July 2018 Last comment 26 July 2018
+ 1   0 1
1

answers
9

comments
544

views

+ 1

rating