SQL

Syndicate content 26 

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

 

Last answer 3 days ago Last comment 6 days ago
0 5
77

views

0

rating

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

Last answer 11 days ago
0 3
0

comments

101

views

+ 1

rating

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?

2. If I declare Index1 , I am not able to have any Bitmap indexes [Cache throws an error on compilation saying I have an Idkey index]

 

Last answer 13 days ago Last comment 12 days ago
0 6
93

views

+ 1

rating

I have a problem,ExtentSize,Selectivity etc is stored in class definition.

I have multiple namespaces with globals maped and only one class definition for All, for example Orders.

Order definition is only one,mapped to main namespace,but in different namespaces have different ExtentSize etc.

Last answer 1 March 2019 Last comment 2 March 2019
0 3
39

views

0

rating

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

but it's works with a create table command.

CREATE TABLE MyStudents (   Name VARCHAR(16) NOT NULL,   surname VARCHAR(16),   fullname Varchar(50) COMPUTECODE { SET {fullname}={Name}_ "?" _{surname}} COMPUTEONCHANGE (Name,surname)  )

 

 

0 2
0

comments

28

views

0

rating

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.

reading the documentation , I understand that %Service_SQL privilege privilege is not enough, but I don't understand what privilege to add,

I can't add specific tables in the "SQL Tables" section of the role definition, I need all the tables to be accessible.

this the documentation I mentioned:

https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY...

Regards,

Nael

Last answer 25 February 2019 Last comment 26 February 2019
0 4
129

views

0

rating

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?

Last answer 22 February 2019 Last comment 22 February 2019
0 2
39

views

0

rating

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} 

}

Last answer 14 February 2019 Last comment 14 February 2019
0 4
100

views

0

rating

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. I am able to open up the corresponding Class in Cache Management Studio and edit/compile the code there. However, I do not have access to the "Routes" section of the Management Web Portal

Last answer 31 January 2019 Last comment 1 February 2019
0 3
79

views

0

rating

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

Research/Trial & Error

I was able to open up Cache Management Studio and find the class that matched up with the table name. I should mention that this is my very first time working with Intersystems Cache, so I apologize if I'm sounding dumb or inexperienced here.

Within the SQLMap, I found this code

Last answer 30 January 2019 Last comment 30 January 2019
0 2
77

views

0

rating

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?

Last answer 18 January 2019 Last comment 25 January 2019
0 2
114

views

0

rating

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

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

Do you have any examples or tracks to use the executeParametersBatch method of %Net.Remote.Java.JDBCGateway?
Otherwise have you found other workaround for batch insert in JDBC through an EnsLib.SQL.OutboundAdapter

Last answer 22 January 2019 Last comment 23 January 2019
0 3
94

views

0

rating

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
}
Last answer 22 January 2019
0 2
0

comments

101

views

0

rating

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?

Last answer 17 January 2019 Last comment 18 January 2019
0 2
94

views

0

rating

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.

Last answer 5 September 2017 Last comment 19 January 2019
0 3
492

views

0

rating

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

Last comment 16 January 2019
0 2
257

views

0

rating

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?

Last answer 12 December 2018 Last comment 14 December 2018
0 4
99

views

0

rating

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. 

Does someone have a suggestion or know of a better method of doing this data type conversion in Ensemble? Or is &SQL() the only feasible option available. 

Thank you

Last answer 15 December 2018 Last comment 17 December 2018
0 3
102

views

0

rating

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,

Last answer 12 December 2018 Last comment 11 December 2018
0 3
98

views

0

rating

I have a persistent class that represents cities across the United States.  It is below, but basically has a City Id, Name, Lat, Lon and a few other unimportant fields for this issue.  Anytime I attempt to query on the Latitude or Longitude it immediately returns no results.  My first thought was that it was a casting issue so I tried casting both sides to floats, ints, even strings and in all cases it immediately comes back with no results.  I then decided to cast it to a string and attempt a like statement thinking it might be something about how floats are handled, but still no joy.  Any ideas on why I cannot query on these fields?  More details are below

Last answer 8 December 2018 Last comment 8 December 2018
0 2
88

views

0

rating

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.

Last answer 28 November 2018 Last comment 25 October 2018
0 3
129

views

0

rating

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? Below is a a snippet of the code I am using:

Property mylist as %String (MAXLEN="") [InitialExpression = "route1, route2, route3"];

set routeList = $LISTBUILD(mylist)

&sql(DECLARE MyReport CURSOR FOR

SELECT

      ProcessStartDate,

      ProcessCompleteDate,

      ProcessName,

     RouteName

FROM

       ProcessRouteTbl

WHERE

       ProcessCompleteDate between :pStartDate and :pEndDate

       AND RouteName %INLIST routeList)

Last answer 3 December 2018
0 3
0

comments

89

views

0

rating

Hello everyone,

I try excuting a SQL Query like this SELECT ID, CompanyName FROM Company WHERE CompanyName LIKE 'condition' 

But if I want to search CompanyName have special char %, ex: '100% Co' , 'Group of Mr.%', 'The %TaxProp'...

How can I take % to query, with  

SELECT ID, CompanyName FROM Table1 WHERE CompanyName LIKE '%%%' 

it'll get all record in table.

Someone give me an idea. Thanks so much!

Last answer 29 November 2018 Last comment 29 November 2018
0 2
150

views

0

rating

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:

SELECT ID, JSON_OBJECT('ID': ID, 'Name':, LastName || ', ' || FirstName, 'Options': Options) as Item FROM Whatever.Whatever

Should give me:

1, '{"ID":1,"Name":"Smith, John","Options":"{\"Color\":\"Blue\",\"Count\":20}"}'

And instead is giving me the marshalled version:

1, '{"ID":1,"Name":"Smith, John","Options":{"Color":"Blue","Count":20}}

Last answer 20 November 2018 Last comment 28 November 2018
0 3
131

views

0

rating