Hi,

When Creating a custom security role, what privileges do I need to add to it, in order for the users that I grant him the role, to be able to use

dynamic SQL to perform read only queries on all tables in a namespace?

I have a security role, that contains the %Service_SQL privilege,

And yet when I connect to a terminal and try to run an SQL query using dynamic SQL , I get a "user is not privileged for the operation" error.

0 7
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.4K

Hi All,

I use SQL function JSON_OBJECT to get data as a JSON object.

However, sometimes I get error with function JSON_OBJECT when values contain [, ], { or }.

Error:

[SQLCODE: <-400>:<Fatal error occurred>]

Unexpected error occurred in JSON_OBJECT() function execution of <JSON_OBJECT>.%FromJSON().Parsing error

For example,

Query: SELECT JSON_OBJECT('idSQL':id, 'content':content) FROM DocBook.block

0 9
0 1.4K
Question
· Apr 6, 2018
sql insert from cache

I am tasked with using CACHE to insert data retrieved from a CACHE data base and insert it into an sql database. The columns in the sql table that I am trying to insert data into have names that contain underscores such as "ACCESSION_DATE" I found a utility in CACHE to connect to the sql data base and perform inserts. I have the data I need to insert. I need to pass the data into the sql utility referencing the column names.

I tried using indirection to set the data into underscored variables, but that isn't allowed in cache object script.

0 8
0 1.4K
Question
· Dec 13, 2017
SQL or Cache Function?

Hi -

I'm creating a custom report in Health Insight via SQL and one of the requirements says that I must include patients who are >= 13 years old (which is easy) but I also need to include patients who WILL be 13 years old as of December 31st of current year that the report is ran in.

I see that there is an Age column and an AgeInMonths column in HSAA.Patient. and of course a DOB column. I'm wondering if it is possible to do this purely using SQL (and SQL functions) or if I will have to incorporate a COS function to achieve this requirement.

Any suggestions?

0 12
0 1.4K
Question
· May 27, 2020
Read data from Excel

Hello,

I work as a contractor for the Department of Veterans Affairs. I want to read data from an Excel workbook. I searched posts here and saw Apache POI suggested. I believe Apache POI is not approved for use within VA. I also believe we should be able to run SQL query against Excel. Has anybody done such a thing or are there other ways to read data from Excel workbooks?

Thanks

0 11
0 1.4K

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:

1 5
3 1.4K
Question
· Oct 2, 2018
%NOLOCK in insert query

I would like to know , if its safe to use %NOLOCK in insert query while doing some parallel processing in Ensemble. We will have pool size of more than 1 for a business process. The business process does an entry in internal meta data table , which will be used for some internal reporting. The same row might get updated by some other process later point of time. i could not produce any error or any issues . Looking forward to hear some opinion on this.

1 1
0 1.3K
Question
· Oct 30, 2018
ID vs %ID in tables

What is the difference between %ID and ID in a database table? Both seem to reference the same column labelled ID.

For context, I am trying to create a viewer class for an existing persistent class.

Let us call the persistent class A, with SqlTableName = OldA.

The viewer class will be B with SqlTableName = A and ViewQuery = {select %ID, <other fields> from <some other class with the same fields as A>}

0 1
0 1.3K

When using the JSON_OBJECT() function in Caché SQL on a %String property that contains JSON syntax, it converts the %String into a JSON object instead of escaping it as a string literal. How can I prevent this? (without ridiculous hacks like "add a space to the beginning of the value" as we don't always know which properties will contain these values and I certainly don't want to have to check for nulls and add/remove a space every single place this value is used in the application)

I don't want these strings automatically marshalled into JSON objects.

For example:

0 12
0 1.3K
Question
· Feb 9, 2018
SQL prepare error

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.

0 5
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.3K

Let's say I have this property:

Property FavoriteColors As List Of %String;

I heed to convert it to JSON using SQL or at least without object access (so direct global access).

What's the fastest way to do that?

I thought about JSON_ARRAY and JSON_ARRAYAGG sql functions but they don't do that.

0 5
0 1.3K
Question
· Jun 22, 2017
Hash values of columns

I try to find a function, which generates hash values of columns. In MS SQL Server I can use

select hashbytes('sha2_256', my_column) ...

to create hash values of my_column. Is it possible to use such things in Caché?

Thank you
André

0 1
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.2K

Hi all,

I am new in Cache. I have to update a record with a long text field, for that, I am using ODBC but the issue is that it is returning an error when I execute the ODBC SQL statement because the field contains some commas, colon, double quotes, single quotes, and CR LF characters.

There is a way to escape this chars? I have seen I can save the field when I replace the single quotes with a double single quote (it's -> it''s) but I can not save the information when the field contains double quote chars. What about the other chars?

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