#SQL

New to CSP and Zen. I've been going through tutorials and have made some progress. Using the "Contacts" tutorial as an example, I'm trying to create a "ViewContact" page. I want this to be linkable so I'm using URI Parameters, which I understand. However, what I'm not sure about is how to retrieve a specific record. Should I use a SQL statement? If so, how?

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.

Hi guys!!

In the system that I work, I came across an iterator pattern that uses the %Resultset library without performing the close after executing the query. Does anyone know how to say what are the impacts of not performing such a procedure?

If you have any model of iterator pattern made in caché to recommend as a good example, I will be grateful hehe :D

We have a routine in one namespace.

We have a Cache SQL table in a different namespace.

What is the syntax for inserting into this table from the routine in the other namespace?

Can this be done without using ZN?

Thanks.

I'm trying to learn how To use SQL in CACHE, so I hope I don't bore you with "Dumb" questions....

I'm getting "ERROR #6022: Gateway failed" message following this line of code.

s sc=gc.Prepare(hstmt,pQuery) 

This line of code comes from an example I found in the documentation. pQuery is the "Select" statement setting up the variables and tables I'm trying to pull information from.

What does that error indicate?

Thank you.

Hi,

Is there way to set up an automated batch job in the Management Portal to execute an SQL query. Also, how can the related view be exported. I have executed the SQL queries and see the view and created files manually. I could not find any related documentation on the batch processes, but I thought batch or automated jobs could be set up via the Management Portal. Any information is appreciated...

Thanks

Hi everybody,

I am writing an SQL query of the following form:

When I use Escape logic when inserting or updating Oracle Table I'm getting Max-Length exceeded error. With the original value the length is good but after I add Escape Logic, it causes value to be greater than max-length. The original value was "I visited O'Brien before heading out of town." and after added Escape logic it was "I visited O''Brien before heading out of town." Max-Length is 45.

INSERT INTO MyText
    (text)
VALUES
    ('I visited O''Brien before heading out of town.')
                 /\
             right here  

Any ideas around this?

Referencing this post:

https://community.intersystems.com/post/producing-json-sql

I'm not sure how to actually interact with the result set I get from doing something like this. I want to return something like:

[{"field1":1, "field2":2}, {"field1":2, "field2":10}]

I'm finding it very difficult to get it in this format, since %Print appends a newline onto the end of the {} object it prints.

Here's the closest I've gotten:

Hi there,

Does anyone have an example of the method used to run an SQL query and generate the view in a Mumps routine. I have several SQL queries created from a vendor that need to be converted and the corresponding view referenced. I cannot seem to find any examples in IDX routines or in the IRIS/Cache documentation. Any help is appreciated.

-Alicia

Hello,

Is there a way to list all items in a production (incl their status/color) by using a sql query? This is for monitoring purposes.

Thank you in advance!

Regards,

Luk

Hello,

I'm looking for a way to write a stored procedure or something to return a ResultSet with Embedded Python.

My goal is the following:

I have a Goal table with a Text field that is free text.

Hi All,

I need to build a search using a lot of filters, what's the best way to implement my sql requests.

1 sql request per property, or only one "big" sql request ?

what's the disadvantage of using lot of indexes ? Thank you

KR,

Amine

Does anybody know if there is an easy way to know if the execution of the command $System.SQL.PurgeForTable went well/wrong?

The documentation describes that a string is returned, but in fact there is a nice Quit "" in the code.

Maybe inside PurgeForTable^%apiSQL there is any process variable set when it goes well/wrong?

Thank you very much!