SQL

Syndicate content 26 

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?


 

Last comment 20 August 2018
0 3
0

answers

294

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

Last answer 16 August 2018 Last comment 16 August 2018
0 3
301

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

Last comment 10 August 2018
1 2
624

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

1 2
0

comments

198

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

Last answer 3 August 2018
0 1
0

comments

135

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

Last answer 18 July 2018 Last comment 26 July 2018
0 1
474

views

+ 1

rating

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

Last comment 24 July 2018
0 2
0

answers

129

views

0

rating

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.ContentType = "application/json"
        Set %response.CharSet="utf-8"

        Write tProxy

Last answer 23 July 2018 Last comment 23 July 2018
0 2
184

views

+ 1

rating

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.  

Here is a code example, with queries and the cost of the query (see the comments for each Index)I have found when running the query in Mgmt POrtal

Last answer 13 July 2018 Last comment 12 July 2018
0 3
166

views

0

rating

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. 

Last answer 7 July 2018 Last comment 9 July 2018
0 2
319

views

0

rating

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

Thanks in advance for any help on how to walk this relationship via Object or SQL access.

Last answer 13 June 2018 Last comment 17 June 2018
0 4
247

views

0

rating

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?

Last answer 14 June 2018
0 2
0

comments

131

views

0

rating

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. I can successfully execute the SSIS Package in the BIDS Visual Studio 2013 BUT when i deploy the SSIS Package into the Integration Services Catalogs in the SSMS (SQL Server Management Studio) i get the following errors:

Last answer 12 June 2018 Last comment 12 June 2018
0 2
322

views

0

rating

The Art of Mapping Globals to Classes (4 of 3)

The forth in the trilogy, anyone a Hitchhikers Guide to the Galaxy fan?

If you are looking to breathe new life into an old MUMPS application follow these steps to map your globals to classes and expose all that beautiful data to Objects and SQL.

If the above does not sound familiar to you please start at the beginning with the following:

The Art of Mapping Globals to Classes (1 of 3)

The Art of Mapping Globals to Classes (2 of 3)

The Art of Mapping Globals to Classes (3 of 3)

Last comment 12 June 2018
0 6
765

views

+ 5

rating

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

Last comment 7 June 2018
0 3
0

answers

147

views

0

rating

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< TimeStampif and only if ID1 < IDfor 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

Last comment 4 June 2018
0 8
4378

views

+ 12

rating

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.

Class generated by linked procedure wizard

Last answer 10 May 2018 Last comment 1 June 2018
0 4
245

views

0

rating

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

Last answer 24 May 2018 Last comment 29 May 2018
0 3
165

views

0

rating

Hi Community!

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

SQL – Things You Should Know

 

Last comment 25 May 2018
0 3
127

views

+ 1

rating

Hello glabal masters,

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

The ON DUPLICATE KEY UPDATE is not working.

Source : 

INSERT INTO 
            ocsGUMCHECDINFO (CODE,DODATE,NAME,DAY,PURPOSE,REQUIREMENT,BEFORENOTE,AFTERNOTE,CNT,REPORT,CONSENT,CONTACT)
        VALUES
            (#{code},#{doDate},#{name},#{day},#{purpose},#{requirement},#{beforeNote},#{afterNote},#{cnt},#{report},#{consent},#{contact})
        ON DUPLICATE KEY UPDATE DAY=#{name},PURPOSE=#{day},REQUIREMENT=#{requirement},BEFORENOTE=#{beforeNote},AFTERNOTE=#{afterNote},CNT=#{cnt},REPORT=#{report},CONSENT=#{consent},CONTACT=#{contact}

Is "ON DUPLICATE KEY UPDATE" not working in Cache SQL? Is there any other way if this isn't working?

Thank you

Last answer 25 May 2018 Last comment 25 May 2018
0 2
124

views

0

rating

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

Last answer 14 May 2018 Last comment 14 May 2018
0 2
136

views

0

rating

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.

Last answer 11 May 2018 Last comment 11 May 2018
0 2
219

views

0

rating