New post

#SQL

16

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

If I have defined a class query in one of my classes and I want to use that query from a method of another class, what are the pros and cons of using the %SQL.Statement interface versus the %Library.ResultSet interface?

I believe %SQL.Statement is the newer interface.

So if the old way is:

USER>s rs=##class(%Library.ResultSet).%New("%Library.File:FileSet")
 
USER>s sc=rs.Execute("c:\s\","*.txt")
 
USER>w sc
1
USER>while rs.%Next() {w !,rs.Data("Name")}

...

then the new way is:

2 12
0 1.6K

I want to override the Get and Set methods of a class property. The class maps to a pre-existing global. The property is defined like so:

Property Invalid As %Library.Boolean;

with the property mapping to a node like ^GLOBAL(Code,"INVALID")=1
Code is a property in the same class.

The value can be 0 or 1 or the node might not exist. When it doesn't exist I want the value of the SQL field to come out as 0 (false).

2 12
0 1.6K

The select query here "select Settings from Ens_Config.Item" in the SQL Query of management portal returns this:

ValidationHost 7ActOnTransformErrorHost1 ReplyCodeActionsHostE=D AlertOnErrorHost1 9 AlertGroupsHost$LabAlertGroup,1-CriticalAlertGroup BusinessPartnerHost

How do I isolate just one of the settings e.g. ReplyCodeActions or LocalInterface

For example, i want to search for all entries that have a value for ReplyCodeActions

I realize i can open the production in studio and search for those setting values.

0 5
0 1.4K

Recently I was asked by a customer with this question. In MS SQL Server, there is a function called host_name() that will return the work station name.

Here is how I would do it in Caché :

With default SQL schema name, in my case SQLUser in SQL (which is equivalent as User in Caché class definition), I have a class called User.dummy, I added a classmethod called hostname and expose it as SQL function host_name:

0 4
0 1.3K

Hi,

I'm trying to perform a SELECT with parameters using the EnsLib.SQL.OutboundAdapter. The SELECT returns results but seems to discard the parameters I try to send. I have tried two methods.

First:

Set par(1) = "20160630"
Set par(1,"SqlType") = 12
Set sql = "SELECT Cod, Ing, score FROM [bbdd].[dbo].[vw_Test] WHERE MyParam >= '?'"
Set tSC = ..Adapter.ExecuteQueryParmArray(.QueryResultSet,sql,.par)

Second:

0 4
0 1.2K

We are creating a package (written in Caché Object Script) that will provide access to an external DB (MySQL). Because applications that use our package will be run from machines with various, potentially unexpected, operating systems, we’d like to establish a connection to the external DB without using DSNs (we’ve heard that setting up DSNs on certain non-Windows machines can be cumbersome and problematic).

0 4
0 1.1K

Earlier in this series, we've presented four different demo applications for iKnow, illustrating how its unique bottom-up approach allows users to explore the concepts and context of their unstructured data and then leverage these insights to implement real-world use cases. We started small and simple with core exploration through the Knowledge Portal, then organized our records according to content with the Set Analysis Demo, organized our domain knowledge using the Dictionary Builder Demo and finally build complex rules to extract nontrivial patterns from text with the Rules Builder Demo.

This time, we'll dive into a different area of the iKnow feature set: iFind. Where iKnow's core APIs are all about exploration and leveraging those results programmatically in applications and analytics, iFind is focused specifically on search scenarios in a pure SQL context. We'll be presenting a simple search portal implemented in Zen that showcases iFind's main features.

Open Exchange app
8 1
1 1.1K

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?

2 4
0 1.1K

I have to disect thousands upon thousands of strings like this:

96842-2-AEV.00001-63561-001958-100728760I-42

I've been able to pull out the first, second and final segments as separated by hyphens, but now I have a predicament where I need to use the second segment, the 2 shown in the example above, in a join to another table. Problem is Cache apparently doesn't like doing that. Just the same, I have to find a way.

Here's the code I'm using to pull that segment out into as separate field in the query results:

0 2
0 965

In writing some code on:

Cache for Windows (x86-64) 2015.1 (Build 429U) Fri Feb 13 2015 14:37:23 EST

I noticed an unexpected "ROLLFAIL" error when a Unique Index fail was generated in a %Save() call.

The object SHOULD fail to save, since the Unique criteria is not met, but I didn't expect to see a ROLLFAIL error as the "Last Error".

When running the following class code:

0 4
0 944

We don't often use SQL within our org, which is mostly due to the performance issue we experience due to the quantity of data we are reviewing.

Aside from the standard performance measures for non-Caché databases, are there any recommended approaches when querying large tables?

The table would have roughly 50M records, but there are not a finite amount of sub-nodes.

0 7
0 908

I get this on some queries in some namespaces. For instance, this query:

SELECT TOP 10 SessionId, datediff(s,min(TimeCreated),max(TimeCreated)) as ResponseTime
FROM ens.messageheader
GROUP BY SessionId
ORDER BY ResponseTime DESC

It works fine in HSBUS but in HSREG it throws the error.

Server closed communication device

Does anyone know what would cause this? Would it log something more useful somewhere?

0 7
0 887

This tells me that there is no timezone offset on this table/field:

Select TOP 1 GETDATE() as Now, TimeCreated FROM ens.messageheader ORDER BY TimeCreated DESC;
Now TimeCreated
------------------- -------------------
2016-10-18 16:16:49 2016-10-18 16:16:31
0 13
0 792

Hello Fellow Caché Developers,

The purpose of this post is to ask for everyone's thoughts and input around the use of transient, SQLCalculated/Computed properties within persistent classes.

This approach allows for extra data values needed in SQL queries to be available without having to join to other tables.

Very simple/basic example to illustrate the topic:

Let's say I have a persistent class named ICDAutoCodeDefn to hold ICD Auto-Coding definitions, such as:

0 4
0 730

I need to do an UPDATE via SQL and I would like the statement to return the `ID` column of each row that is updated. MS SQL has an "OUTPUT" statement, but I don't see anything similar in Cache. Is there a way to do this?

0 4
0 702
Question
· Aug 18, 2016
Read only Async mirror setup

I have setup an async reporting mirror member with Read only access. My problem is that if I try to do any sql reporting against that data I am getting errors. I am sure that this is because the DB is read only, but I had assumed that setting up a reporting mirror would handle this.

I there a setting or mapping I am missing?

0 7
0 663