I'm having trouble creating a view in cache with query like:

SELECT column1, column2, column 3
    FROM  table
    WHERE
        (column1, columnn 2) IN (SELECT c1, c2 FROM table2 GROUP BY c1)
    ORDER BY column1

I think the problem is in where clause, because it cannot take two columns .

the exact error: [SQLCODE: <-1>:<Invalid SQL statement>] [%msg: < ) expected, , found^ (column1,>]

Any idea on how to solve this?

Thank you.

I like to use something like this:

select * from MyTable where MyFieldName like ‘%[^a-zA-Z0-9 !”%#$&”()*+,-./:;<=>?@]%’

to find field that contains special characters that are only allowed.

I need to pass a parameter string that contains slashes to a REST call.

I tried URL encoding, or making URL map to accept query parameter instead of path parameter but apparently we only allow for path parameters.

I can come with several workarounds, but these involve some transformation at client and then on server, but I'd prefer a direct support.

Q1: is there any Cache specific trick to pass slashes in path parameters

Q2: are we going to support query parameters in REST?

something like this would help: 

 Dan Kutac

I have a query string that I am creating programmatically, based on some user inputs.  The user might search on 5 fields, or 8 fields, or no fields.

In my sql statment, some of these fields require parameters in the %Execute statement.

For example:

if user picks lastname, sql = "select * from person where lastname = ?"

if user also picks age, sql = "select * from person where lastname=? and age > ?"

I then have these lines of code to create my result set:

set statement = %SQL.Statement

statement.%Prepare

resultset = statement.%Execute(param1, param2)

-- but it might be 

resultset = statement.

Hello,

I am trying to copy an xml file generated on an Apache server into the Jenkins workspace post-build. I was thinking to use a 'send files over ssh' post-build script, but have not done this before and do not know how to refer to the file location on Apache server vs on Jenkins.

For example, if I want to copy from Apache's location of: "classes/UnitTest/Results.xml" into Jenkin's workspace: "/ReportFiles/Results.xml",

How does the script differentiate between whether the location address refers to the Apache server or Jenkins workspace?

Or is there a better alternative way to do this?
 

Hi,

We are having an issue with the class %Net.SSH.Session - it accepts a username, publicKeyFile, privateKeyFile and passPhrase.

This is our example code:

            set ssh = ##class(%Net.SSH.Session).%New()

            set tSC = ssh.Connect("172.10.10.1", 22)

            $$$ThrowOnError(tSC)

            set Username = "a"

            set passphrase = "123"

            set pub = "C:\FTPServer\pubkey\publicKey.pub"           

            set priv = "C:\FTPServer\private.key"

            set tSC = ssh.

Hi All,

I would like to download odbc drivers for Cache database to be able to install it on our SQL server.

I am trying to create a linked server to Cache database.

I couldn't find a link for the drivers, there was one post with FTP reference to download but it doesn't seem to work.

I will be grateful for any help.

Thank you

Sap.

I have a class that writes to an external SQL Server database.  When the field is empty, it does not write the column and the column has the value NULL.  In some cases, I want the column to be the value of the empty string instead of NULL.

Class myRequestClass Extends Ens.Request
{

    Property MessageType As %String

}

The message that is passed to this class shows:

    <MessageType></MessageType>

Which writes the field as NULL with:

    tSQL = "insert into myTable (MessageType) values (?)"

    set tSC = ..Adapter.ExecuteUpdate(.tNumberOfRowsUpdated,tSQL,myRequestClass

Hi All,

I have created a REST class in which have the Parameter HandleCorsRequest = 1;

I can able to access the API using Postman, but not with my web application. It throws the below error.

Access to XMLHttpRequest at 'http://localhost:52773/IrisVSCode/app/test' from origin 'null' has been blocked by CORS policy: Response to preflight request doesn't pass access control check: No 'Access-Control-Allow-Origin' header is present on the requested resource.

Please note that I have tried all option which are mentioned on following URL:

https://cedocs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.

I'm trying to send an HTTP POST request with form-data from an Angular app to a Cache backend, but my request fails with a 406 Not Acceptable error.

httpRequestHeaders = new HttpHeaders({
  Authorization: 'Bearer ' + sessionStorage.getItem('access_token'),
  Accept: ''
}); 

uploadFile(file: File) {
  const formData: FormData = new FormData();
  formData.append("file", file);
  const req = new HttpRequest("POST", this.postURL, formData, {
    reportProgress: true,
    responseType: "json",
    headers: this.httpRequestHeaders,
  });
  return this.http.request(req);
}

Now I want to return a large amount of data to the front end. The string length has reached 40000 +, and the returned data needs to be encrypted by AES + Base64. I can convert the string into a stream. AES can use the AESCBCEncryptStream method to encrypt, but Base64 has no stream method。Anyone who get the solution  would you kindly share the solution please。

Any help would be appreciated. Thanks!

Hi everyone,

I have a project which requires the sending of JSON messages to an external service provider using REST. The service provider requires the message contents to be signed.

Their instructions:

1. Add a header called "Date" with the the date and time in a specific format - done
2. Add the client's certificate password in a field in the header - done
3. Create a string which consist of the {Date}{newline}{Password}{newline}{etc}{Message Body}.

Any suggestions on the most efficient way to convert yyyymmdd string to mm/dd/yyyy?

Hi all,

I have an incoming JSON message with a string field exceeding in length the 'caché long-string limit' of 3641144 characters.

Using {}.%FromJSON(instream) I am able to create %DynamicObject properly. However, I am unable to access the long property, as in every assignment, I get the <MAXSTRING> error.

Is there any way for me to obtain contents of the 'too long' string field as a stream?

Thanks

Jiri

WebSockets look to be supported reasonably well in Cache. I have yet to use them in production so I am wondering how well it has worked for other developers.

In particular what happens when the browser does not support WebSockets, or when a firewall blocks the connection.

Have you had to write your own long polling fall-back?

I've read the documentation and found this interesting article...

https://community.intersystems.com/post/asynchronous-websockets-quick-tutorial

but no mention of long polling fall-backs that I can see.

Hi,

I have a large XML zen report file that I want to convert to a CSV, what is the most efficient way to do it.

XML file:

<Details>
<Col1> aa1 </Col1>
<Col2> bb1</Col2>
<Col3> cc1 </Col3>
</Details>
<Details>
<Col1> aa2 </Col1>
<Col2> bb2</Col2>
<Col3> cc3 </Col3>
</Details>

Expected Output:

Col1,Col2,col3
aa1,bb1,bb1
aa2,bb2,bb2

Thank you.

Hello,
Having difficulty with sending POST request.

How I can send parameters in the body of request, if i'm using ContentType:"application/x-www-form-urlencoded".

Version of ensemble: 2010

My code is bellow:

Set httpRequest=##class(%Net.HttpRequest).%New()
set httpRequest.SSLConfiguration="1"
Set httpRequest.ContentCharset="utf-8"
Set httpRequest.Server="test.oath.com"
Set httpRequest.Port=443
Set httpRequest.Https=1
Set httpRequest.Location = "/auth/key"
set httpRequest.ContentType="application/x-www-form-urlencoded"
Set httpRequest.ContentCharset="utf-8"    
do httpRequest.EntityBody.

Where can I download the Cache ODBC drivers for Windows?

I went here https://wrc.intersystems.com/wrc/Login.csp but it said 

User is not enabled for WRC login. If this is unexpected, please contact InterSystems Support.

What does that mean? What do I need to do?

Where can I download the Cache ODBC drivers?

Where can I download the Cache ODBC drivers for Windows?

I need to connect to a cache database by odbc but the FPTs that I have found do not work, 
could you tell me where to download the ODBC drivers

I am still new to cache objectscript and am trying to figure out how I would go about removing the escape characters from my JSON below. When I call the $toJSON method it's adding the "\" character in the file path.

{"FileStatus":"P","Path":"\/somepath\/test\/test123\/filename.txt","InterchangeOID":"100458"}

My Code:

set tJSONFile = {"FileStatus":pRequest.FileStatus,"Path":pRequest.Path,"InterchangeOID":pRequest.InterchangeOID}

set tJSONFile = tJSONFile.$toJSON()

Thanks.

Hello,

I'm working on integrating a Cache database into an existing .NET project of mine.  I'm trying to execute certain queries utilizing the Cache ODBC driver.  The first error I ran into was "[S1000] [Cache ODBC][State : S1000][Native Code 417] Access Denied".  The part that's throwing me for a loop though is that the application ran ~200 successful queries before I started getting that.  After some of those Access Denied errors, they turned into "[Cache ODBC][State : S1T00][Native Code 450] Request timed out due to user timeout".  This was all with the regular (2.5?) driver.

Hi everyone,

This is:

Cache for UNIX (Red Hat Enterprise Linux for x86-64) 2016.2 (Build 736U) Fri Sep 30 2016 12:25:56 EDT

The server is on RedHat Linux while the browser is running on Windows (Firefox).

I am helping a partner build a complex modern HTML5 web application that talks with Caché using REST calls. They have been using %CSP.REST very successfully. Security works great as well. 

Some time ago, I had defined the parameter CONVERTINPUTSTREAM = 1 on their %CSP.REST classes and that seemed to work.

We use ExecureProcedure() to execute a stored procedure which returns a result set. But we see lot of "Invalid cursor state" errors when the result set is empty.  The connection to SQL server database is made through ODBC. 

ERROR #6022: Gateway failed: Fetch. + ERROR <Ens>ErrGeneral: SQLState: (24000) NativeError: [0] Message: [Microsoft][ODBC Driver 11 for SQL Server]Invalid cursor state 

Here is the code snippet from the business operation class which uses  EnsLib.SQL.OutboundAdapter  

do ..Adapter.DSNSet(DataSourceName)

set sp = "{call sqlstoredprocedure(?,?,?)}"
set tSC = ..

This question came on the Ensemble in Healthcare email list. It's a great question. I'm working on an answer, but am posting it here to get any other input. I'll also address the option of using %CSP.REST.

Hello everyone,

Using Intersystems Healthshare/Ensemble, I am trying to make a RESTful Interface between Android/iOS app and Cache database. I have some queries and I am unable to find where exactly I can get any resources on them.

1. By extending the EnsLib.REST.

My issue is that I don't know why Ensemble processes incoming data in the wrong encoding.

Update: Issue was solved, I think, see my answer below.

our customer uploads UTF-8 encoded CSV files to our sFTP server. When I view the files on the server side, the scandinavian characters Ä and Ö (A and O with two dots on top of themselves, respectively) display correctly in the source file. When Ensemble downloads the files using a FTP Service, the characters display incorrectly in Ensemble. 

I am unable to pinpoint the reason for this behavior and I was hoping this is easily solvable.

Can I connect to Cache using SSMS (SQL Server Management Studio) and view schemas and build queries as I do for SQL Server?

If yes, step-by-step instructions for setting up the connection would be greatly appreciated.

I googled the topic but didn't find any results that were on target.

Thanks,

Christian Bahnsen

I'm building a .Net Core Gateway container. Here's the issue.

As a final step I'm building a .Net Core 2.1 library. It has a runnable dependency - .Net Gateway, which I need to start first.

However dotnet publish generates .runtimeconfig.json and .deps.json only for my library and not for my dependency (.Net Gateway).

Is there a way to force dotnet to generate and .deps.json for a dependency?

I have tried:

Hello!  I have basic web services application that java clients connect to the cache web services.  Using the browser, the user enters in the following url.

http://localhost:57772/csp/foia/%25SOAP.WebServiceInvoke.cls?CLS=SOAPFoia.SOAPServiceFoiaWeb&OP=FoiaApp30
'

which runs a soap method called FoiaApp30.   I am not sure how to add https  security to the web service calls.  I have reviewed documentation pertaining configuring SST/TTL configuration and other information.   Can somebody please help me out on what I need to do?

Hi, I was hoping that someone could point me to the error in my ways.  I am trying to follow the examples to setup Ensemble 2017.1 in a docker container on an Ubuntu 16.04 virtual machine.

I have a directory that contains

Dockerfile
cache.key
ensemble-lnxubuntux64.tar.gz

When I execute:

docker build -t ensemble-simple .

I am getting the following error:

Notice the error /bin/sh: 1:yum: not found

Here is the contents of my Dockerfile

FROM ubuntu

MAINTAINER Dmitry Maslennikov <Dmitry.Maslennikov@csystem.cz>

# update OS + dependencies & run Caché silent instal
RUN yum -y update \
 && yum -y install which tar hostname net-tools wget \
 && yum -y clean all \ 
 && ln -sf /etc/locatime /usr/share/zoneinfo/Europe/Prague

ARG password="Qwerty@12"
ARG cache=ensemble-2016.2.1.803.0

ENV TMP_INSTALL_DIR=/tmp/distrib

# vars for Caché silent install
ENV ISC_PACKAGE_INSTANCENAME="ENSEMBLE" \
    ISC_PACKAGE_INSTALLDIR="/opt/ensemble/" \
    ISC_PACKAGE_UNICODE="Y" \
    ISC_PACKAGE_CLIENT_COMPONENTS="" \
    ISC_PACKAGE_INITIAL_SECURITY="Normal" \
    ISC_PACKAGE_USER_PASSWORD=${password} 

# set-up and install Caché from distrib_tmp dir 
WORKDIR ${TMP_INSTALL_DIR}

ADD $ensemble-lnxubuntux64.tar.gz .

# cache distributive
RUN ./$ensemble-lnxubuntux64/cinstall_silent \
 && ccontrol stop $ISC_PACKAGE_INSTANCENAME quietly \
# Caché container main process PID 1 (https://github.com/zrml/ccontainermain)
 && curl -L https://github.com/daimor/ccontainermain/raw/master/distrib/linux/ccontainermain -o /ccontainermain \
 && chmod +x /ccontainermain \
 && rm -rf $TMP_INSTALL_DIR 

WORKDIR ${ISC_PACKAGE_INSTALLDIR}

# TCP sockets that can be accessed if user wants to (see 'docker run -p' flag)
EXPOSE 57772 1972

ENTRYPOINT ["/ccontainermain", "-cconsole", "-i", "ensemble"]

Hi!

Suppose I have full access to InterSystems IRIS database instance A and want to export a consistent part of the data and import it into another InterSystems IRIS instance B. Classes are equal.

What are the most general and convenient options for me?

TIA!