Hi all,

I have a non objectscript application connecting to a cache instance via ODBC and one column is a list of serial objects. The output from the query contains a lot of special characters and I'm hoping there's a better way to get this data back so I won't have to perform extensive parsing on the application side.

I've tried using the $ListToString() function, but that didn't help much, probably because the list contains complex objects rather than primitives.

Any help will be greatly appreciated!

1 4
0 758

Hello,

We have a need to track Database changes over time - down to the SQL level of granularity if possible. Such as: User xyz runs routine ^abc and we get something similar to a changelog that tells us: table A had this value updated, insert, update etc....

Is that possible using IRIS level tools (Audit Log, Journal File, etc...) , is there a way to convert the global sets and kills from the journals into SQL level changes?

1 3
1 398

Hello, we have a few hundreds of triggers to port from Oracle to Cachè for a migration project, and many of them have to change (for example, normalize a value, null it, etc) the value which is being inserted.

The documentation says "You cannot set {fieldname*N} in trigger code." , so we're unlucky.

Is there a good workaround for this ?

SqlComputeOnChange doesn't seem the best way, but I'm not totally sure: for example normalization and validation could have a better place somewhere else than a trigger.

1 2
0 354

Hello,

searching messages in our Message Bank is quite slow, often runs into timeout.

I wanted to perform a tune table on Ens_Enterprise_MsgBank.MessageHeader because this apparently has not been done yet - the Tune Table utility shows no entries for selectivity, etc.

I tried

 w $SYSTEM.SQL.Stats.Table.GatherTableStats("""Ens_Enterprise_MsgBank"".MessageHeader")

and got this error message

1 6
1 441
Question
· Sep 19, 2019
Credentials - Domain Account

Hi,

I've a Service utilising the Adapter EnsLib.SQL.InboundAdapter, which uses a Credentials item set with the details of a local SQL account. This currently works, however, we're looking to use the credentials of an AD domain account.

The domain account is a member of an AD security group, which has the required permissions on the source SQL database. I've checked that access is possible with this account via SQL studio.

1 2
0 277

In Oracle database, the synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.

You generally use synonyms when you are granting access to an object from another schema and you don't want the users to have to worry about knowing which schema owns the object.

Is IRIS SQL sintax has something like this: (oracle sintax)

1 1
0 189

Hi All,

I am a beginner in cache. I need to extract data from multiple tables (4-5) tables based on an SQL query . The data is more than 9 lakhs of records (i checked using count). The total number columns is 16.

When I am running this SQL query through management portal, I am getting time out exception. Could you please suggest how I can retrieve those records. Which way will be good (Dynamic query using %SQL.Statement or Embedded SQL (not known to me) or Writing to a global will help or not).

1 4
0 290

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 "

1 4
0 3.7K

Hi ,

I have a code written in cache sql and trying to understand it, below is the code , can anyone help me understand what does that mean

ex - !! dosage_unit !!

SELECT (CASE WHEN (order_description IS NULL OR (order_description='')) THEN '' ELSE (order_description) END) !! (CASE WHEN (dosage IS NULL OR dosage_unit IS NULL OR (dosage='') OR (dosage_unit='')) THEN '' ELSE (', ' !! dosage !! ' ' !! dosage_unit !!

Thank You in advance.

1 12
0 265

Say I have a property in a persistent class that stores list of colours and I would like to query that field and return a list and be able to loop that list to get individual colours how will l go about achieving this I have tried something like this but its not working as expected

 &sql(SELECT colour INTO :colourList FROM favouritecolours)
 While (SQLCODE = 0) 
{
 for i=1:1:$LENGTH($P(colourList,","))
 {
 set fvalue=$P(colourList,",",i) 
write "the first"_fvalue,i, 
} 
}
0 1
0 490

I know &SQL returns only one result but is

&SQL(SELECT ID FROM Cinema.Film ORDER BY ID DESC)

and

&SQL(SELECT TOP 1 ID FROM Cinema.Film ORDER BY ID DESC)

the same in terms of processing required?

0 7
0 359

HI,

I have made a query with class definitions and all their properties and put them in a view.

All is good besides Parameters is showing junk characters. Is there a way to do it cleanly besides getting into the code??

SELECT
CC.ID As CompiledClass,
CC.SqlSchemaName,
CC.SqlTableName,
CP.Name As PropertyName,
CP.SqlFieldName,
CP.Type,
PD.Parameters

FROM %Dictionary.CompiledProperty CP
JOIN %Dictionary.CompiledClass CC
ON CP.Parent = CC.ID

0 11
0 354
Question
· Jun 22, 2020
Amending timestamp

Hi, I have a timestamp of 201906192359 with a HL7 and I need to add a minute to it to get 201906200000. Is there an easy way within Healthshare to do this?

It seems easy enough within SQL but I cannot get the SQL to work within Healthshare, this is what I have for SQL which does the job in SQL Server.

SELECT REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16),DATEADD(mi,1,STUFF(STUFF(@test,11,0,':'),9,0,' ')),120),'-',''),' ',''),':','');

0 3
0 269

I need to show the absence of data, so I have to join the list of predefined values with a result of a select statement.
However, it seems like Table Value Constructors in JOIN are either not supported, or I do not understand the syntax.
Basically, I am going for something like this:

Select v.valueId, m.name 
 From (values (1), (2), (3), (4), (5)) v(valueId)
     left Join otherTable m
        on m.id = v.valueId

Is it supported in Cache SQL or not?

0 6
0 174