Stored Procedures

Syndicate content 10 

Hi

Totally new to IRIS and Cache.

Trying to evaluate it and work out how we could use it.

As a standard application database. Object or relational etc. does not matter. 

Issue is ObjectScript.

So:

1) Can we develop, maintain and use an IRIS database and never use ObjectScript i.e. use only Java, Python, C++ interfaces etc. (exactly which one does not matter)? Would that make designing and using the IRIS database more prone to inefficiency and error?

2) Can we import an existing Cache database into IRIS and convert its ObjectScript code into Java, Python whatever? Is that a big, difficult, error-prone job?

If the answers are no that may not be a showstopper but would like to know it now. 

A lot of training will be involved in any case I know and Oracle has PL/SQL but ObjectScript developers are rare.

Apologies if the answers are in the doco. Have read some of it but need some indication about the above urgently.

Last answer 14 November 2018 Last comment 14 November 2018
+ 1   0 2
237

views

+ 1

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   0 2
0

comments

148

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   0 2
142

views

0

rating

Hi,

I am afraid I am stuck again. I have ensemble connected with a MsSQL database and I have a service that calls a store procedure. All is working, and I can get strings from the database in the service. The problem is that the data the store procedure returns is suppose to be an HL7 message so I need to convert the string into HL7 format. I have been using the ##class(EnsLib.HL7.Message).ImportFromString  method but although I have a valid HL7 message from the string received from the DB...apparently the content of the message is being modified

Last answer 19 February 2018 Last comment 19 February 2018
0   0 2
377

views

0

rating

Hi, I am trying to execute a store procedure within an ensemble operation to connect to aMsSQL database and I am having problem to pass the input arguments... I have tried several things but only one seems to work and it is not the ideal solution.

ADAPTER = "EnsLib.SQL.OutboundAdapter";

This is the code that works...as you can see the parameter is added to the sql query...not the best solution but the only that works at the moment

Last answer 9 February 2018 Last comment 9 February 2018
0   0 1
416

views

0

rating

I am currently working on a issue with WRC on one of my Inbound SQL Adapters not returning all the records it should be. If I looked at the count of the records in Ensemble and compare it to that of a Microsoft SQL View, Ensemble seems to be off by a few records here and there. I am using a full dynamic select statement in my settings of the adapter.

SELECT Text, PhysicianLastName, PhysicianFirstName, PhysicianAddress1, PhysicianAddress2, PhysicianCity,

PhysicianState, PhysicianZip, PhysNum, InsertDate, FaxK, EnsIndex, MRN, AccountNum, FirstName, LastName

FROM vUHEpicMailEns
Order By FaxK

I have added some trace elements but the missing records are not getting even to the point of trace

Last answer 20 July 2017 Last comment 14 August 2017
0   0 1
320

views

0

rating

Class Queries in InterSystems Caché is a useful tool that separates SQL queries from Caché 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!

Last comment 9 December 2016
+ 14   0 9
3697

views

+ 14

rating

Given a complex method flagged with [ SqlProc ] so it is available as an SQL stored procedure, what's the best way to report a non-system error detected in that method - say, for example, an error %Status - so that the SQL query calling it fails descriptively? Is it best to create and throw an exception, or are there special % variables involved (like in a trigger)? I haven't been able to find an answer in the documentation.

Thanks in advance!

Last answer 17 August 2016
0   0 2
0

comments

164

views

0

rating

I have a MySQL server with "posts" table.

I also have a Caché server with "downloadedposts" table. 

They are connected from Caché to MySQL via SQL Gateway

I want to keep Caché table synced with MySQL one  (MySQL "posts" table is a master copy), so periodically Caché queries MySQL server and downloads data. So far so good, and if a record appears or changes in MySQL table, Caché downloads the changes.

The problem I'm encountering is that sometimes rows would be deleted from  MySQL "posts" table.

How do I synchronize deletions?

Last answer 27 June 2016 Last comment 24 June 2016
+ 2   0 1
586

views

+ 2

rating

In MySQL I have the following table:

CREATE TABLE `info` (
   `created` int(11)
);

And it is linked (via JDBC SQL Gateway) to Cache table mysql.info.  `created` field stores unix timestamp. So when I execute  this SQL in SMP:

SELECT created FROM mysql.info

I receive the following output (which is expected):

created
1435863691
1436300964

But I want to to display `created` field converted to ODBC timestamp format. To do that I call this SQL procedur

Last answer 19 May 2016 Last comment 19 May 2016
0   0 0
504

views

0

rating