#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! 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:

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

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 ??

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'

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?

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'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:

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#D...

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

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 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 FROM MergeHyland.TypeTwoDimesionalTable WHERE Key = :Key)" any ideas why 

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> ]