#SQL

I use Documatic a lot to generate class documentation from comments embedded in the code. Is there a good way to create documentation for SQL views as well? Ideally I want to document each column in the view with HTML markup similar to how I document each method of a class with Documatic.

Hi all,

In order to prioritize functional requirements for evolving our SQL developer experience, we have prepared a short survey to better understand how you’re currently using and, in an ideal world, would like most to interact with our SQL platform.

https://www.surveymonkey.com/r/DSY6YT2

Don’t hesitate to forward this survey informally to your peers you know are accessing our SQL engine on a regular basis. There’s a disclaimer at the top that indicates we are highly interested in feedback to drive our roadmap, but obviously cannot commit to implementing all of it.

Hi! We have received a request to create a new rule on CachéQuality to identify when a developer uses double quotes (" ") within any SQL statement.

We have been asked many times about SQL validation rules, and we would like to open a debate to allow everyone discuss what would you like to be checked on a SQL statement.

Current examples are for basic situations:

• Using SQL.Statement class:

Set stmt = ##CLASS(%SQL.Statement).

I am having an intermittent issue that when I make a call to MSSQL from a BPL that the response does not come back in the amount of time required. Since the call from the BPL is synchronous I tried changing the timeout to 60 but it has not helped (see below). Is there anyway to guarantee that the call waits long enough for a response before continuing on?

Thanks

Scott Roth

I am trying to convert a string to date but can not get it to work I have  function that I would like to take in a date string and covert it to date object

here is the ezample so far can not get it to work any help appreciated 

set p="12/03/2019"
 
w $System.SQL.TODATE(p,"YYYY-MM-DD")
 
<ILLEGAL VALUE>todate+32^%qarfunc

if I try this still get the wrong value returned

set p="12/03/2019" 
w $ZDATE(p,3)
1841-01-12

Hi 

Can I use a the SQL  'Table-Valued Parameter' when i call a store procedure in sql outbound adapter using ExecuteProcedure?

to pass multiple rows all together as a block, I need to process all the records in one transaction using commit and rollback (if failure)

Thanks 

Gadi

Hi Community!

There are two general ways to execute arbitrary SQL  in serverside ObjectScript code: EmbeddedSQL and ObjectScript SQL a.k.a. Dynamic SQL.

E.g. if we want to get the value of the property of instance with a certain ID using SQL we can do:

&sql(SELECT Name INTO :name FROM Sample.Person WHERE ID=1)

write name

Same result with %SQL.Statement:

set rs=##class(%SQL.Statement).%ExecDirect(,"SELECT Name as name FROM Sample.Person where ID=1")
  do rs.%Next()
  write rs.name

Let's imagine you have only SQL. You need some table where you should increment value in some property when you update this line.

We have the table

CREATE TABLE "test" (
  "identifier"   VARCHAR(200) NOT NULL,
  "value"        INTEGER,
  PRIMARY KEY ("identifier")
)

Just wondering an Insight in the difference between these two indexes

IdKey / PrimaryKey
================= 

Property Identifier As %Integer

Index Index1 on Identifier [Idkey]

Index Index2 on Identifier [PrimaryKey]

What's the difference?

1. If I don't have Index1 and only have Index2,  then cache does still make its own id.
So how and why  do I ever use the PrimaryKey.  In Joins ??

Table1.Identifier = Table2.Identifier instead of Table1.Id = Table2.id ??
But I can still use Table1.Id = Table2.Id as cache still made one ID field

So where is PrimaryKey useful in cache?

I try to create a column with computeonchange. 

Its works with CREATE TABLE sql command, but if i use ALTER TABLE ADD COLUMN sql command this computeonchange doesn't works.

any reason why?

example:

CREATE TABLE MyStudents (   Name VARCHAR(16) NOT NULL,   surname VARCHAR(16))

alter table MyStudents add column fullname Varchar(50) COMPUTECODE { SET {fullname}={Name}_ "?" _{surname}} COMPUTEONCHANGE (Name,surname)  

insert into SQLUser.MyStudents (Name, surname) values ('name1',null)

update MyStudents set name = 'name' where name = 'name1'

this not updates fullname column value.

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.

SetIdentityInsert call controls the ability of the user to specify a value for the IDENTITY property when saving a new object, a value for the IDENTITY column or an explicit ROWID value in an SQL INSERT. If IDENTITY_INSERT is false and the user specifies an explicit IDENTITY or ROWID value when saving a new object or inserting a new ROW then an error condition is reported.

Setting takes effect immediately and lasts for the duration of the process or until SetIdentityInsert is called again.

My question is how can I change this setting system-wide?

Hi Community!

Suppose I have a property in a ObjectScript class:

Property values As list Of %String

What an SQL query can help to return the values of the property as JSON array via SQL access (JDBC)?

Is there a way or can it be done to use conditional logic in sql like so

 Query Q1(formal as %String) As %SQLQuery [ Final ]
{
    
    SELECT patientnumber,ID, CASE
    WHEN ID = 50 THEN "The is 50"
    WHEN ID = 30 THEN "This is 30"
    ELSE "The quantity is under 30"
END FROM Audit.Table WHERE ID = :formal AND EndDate is null} 

}

Problem

I got a quick answer from this forum yesterday so I'm going to try my luck again today!  I've hit an error in another table when trying to extract through the Cache ODBC driver, but this one gives me less details and I'm struggling to pinpoint what might be causing the error.

The table I am trying to extract is called REF_TABLE_ONE.

Here's the Error:

[Cache Error: <<NOLINE>%0AmBx1^%sqlcq.PRD.2249>]
[Location: <ServerLoop - Query Fetch>]

Research/Trial & Error

Based on my research, it looks like the error results from something missing in a routine. I have limited access to this system.

Problem

I'm working on exporting data from an Intersystems Cache database through the Cache ODBC Driver. There is a particular table that is giving me an error message. The ODBC Driver crashes and reports an error from the Cache system. I think I was able to trace down where the error is coming from, but I do not know how to debug or fix the error.

The table I am trying to extract is called SEDMIHP.

Here's the Error:

[Cache Error: <<UNDEFINED>%0AmBd16^%sqlcq.PRD.3284 ^SEDMIHP(4,77)>]
[Location: <ServerLoop - Query Fetch>]

Let Say I have 
Class Carrier Extends %Persistent

Property Employee As Array of Class Employees

Class Employees Extends %SerialObject

Property Name As %String

Property Address As Array of Class AddressDetail

Class AddressDetail Extends %SerialObject

Property Street1 As %String

Property Street2 As %String

I can get value for Employee Name  as Employee_Name.

But I want to Get Value of Address Street1 and Street2 of Class AddressDetail using SQL Query
How can I get that?

Hi, I'm looking for a way to implement the JDBC pattern "executeBatch" in ObjectScript: https://www.tutorialspoint.com/jdbc/jdbc-batch-processing.htm

Since Ensemble 2017.2.2 there is a new method called executeParametersBatch : https://docs.intersystems.com/ens20172/csp/docbook/relnotes/index.html#…

Even in the Ensemble 2018.1 change log, mention a patch on this method: https://docs.intersystems.com/ens20181/csp/docbook/relnotes/index.html#…

Do you have any examples or tracks to use the executeParametersBatch method of %Net.Remote.Java.JDBCGateway?

I need to select my result into a list and be able to loop through the list when query finished any help appreciated here is where I am

##sql(SELECT %ID INTO :IDArray() FROM MergeHyland.TypeTwoDimesionCollection WHERE GUID = :Key AND EndDate IS NULL)
for I=1;1:$LISTLENGTH(IDArray)

{

w $Data(IDArray),i

 


}

I have this query That I am trying  to use in my class when testing on the terminal I expect to get the results printed on the terminal but I am only getting zero printed please can anyone out there advice on what I am doing wrong

Method PatientInfo(ID As %String) As %Status
{ #dim status as %Status=$$$OK
  SET myquery="SELECT GUID, IDType,IDValue FROM MergeHyland.TypeTwoDimesionCollection WHERE GUID ="_ID
  SET rset=##class(%ResultSet.SQL).%Prepare(myquery,.err,"")
    WHILE rset.%Next() {
    WRITE !,rset.GUID & ":" & rset.IDType& ":" & rset.IDValue
    }
  WRITE "End of data"
    return status
}

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.

I have a persistent class.

I want to store one of the properties there as a stream or a string depending on a size.

99% of values would be strings (less than $$$MaxStringLength characters) so I don't want to store everything as streams.

What do you think of this approach?

What's the best architecture to implement in this situation?

I've mapped multiple tables (UNION on mapped SQL classes) into a view, using CREATE VIEW.

Through ODBC, in Entity Framework, I am querying against that view and offering paging. The paging is implemented using IQueryable.Skip and IQueryable.Take. 

Skip seems to have unexpected results, I believe due to incorrect SQL generated by the Entity Framework provider, though perhaps I've done something incorrectly. The generated SQL looks similar to this (with some bits replaced or altered for security reasons), the basic structure is unaltered.

I have an sql statement that I would like to execute but I get the error

"zSearchChanges+5^MergeHyland.TypeTwoUtil.1 *KeyID

and here is my sq

l  "&sql(SELECT Key INTO :KeyID

Hi Community!

New session recording from Global Summit 2018 is available on InterSystems Developers YouTube Channel:

An Outlook on Scaling Out

Hello,

I'm looking to find if there is a datatype convert equivalent in Object Script to SQL convert function. Have a VarBinary string coming in from source application (which is really performing a SQL dump). The source application uses the standard SQL convert function to convert from varchar to varbinary on their side.

I know &sql(Convert()) should work in Object Script, but am wondering if there is a better way of doing this. 

Getting data in via flat file (Record Map), then using data transform to transpose this data to SDA3.

A very simple SQL

SELECT field1 F1, count(field2) CntF2
FROM GCT.PA_Data
where field1 is not null
group by field1

gives an error back:

 [SQLCODE: <-461>:<Communication link failure>]

  [%msg: <Connection 'TRAKCARELIVE': Execute: SQLState: (HY000) NativeError: [10053] Message: [Cache ODBC][State : HY000][Native Code 10053] [c:\intersystems\ensemble\bin\cache.exe] Software caused connection abort> ]

It does work in another (test) environment, but not on live. Any idea why?

Dear all,

In our application made in Caché object script, when we deploy some classes modified we have the option of calling "Purge queries" feature of Caché.

We only were doing this call when a %Persistent class definition (table) was modified, as we assume that no other changes affect the cached queries and we don't  want to purge them by default, as the first run of a query becomes slow.

We had a case where no table definition was changed but a purge queries was the solution.

Do you know if any other change could affect the cached queries and a purge queries is necessary? 

Regards,

I am trying to use %INLIST in  SQL query using a cursor and the query fails to return results. It appears that the variable I use against %INLIST returns an empty string. All the examples I have seen use result sets and build the query as a string. Is it possible to use %INLIST in a cursor-based query?

Hello, I need help in health insight. I am trying to generate reports on deep see but i am not able to pull in the patient ids as these are our requirements for the project. Can anyone help me in unlocking this feature. Can anyone help me with link to correct documentation on how to access the edge gateways of multiple facilities to access the clinical data on sql explorer.