SQL

Syndicate content 27 

Hi All,

I have two tables LB_TestSet and LB_Transfer

LB_Transfer has a list if TestSet row IDs, so i am doing the following join

SELECT 
LBTS_RowID,
,LBTS_CollectedDate
FROM SQLUser.LB_TestSet 
JOIN SQLUser.LB_Transfer ON ($LISTBUILD(LBTS_RowID) %INLIST LBTR_TestSetList)
WHERE LBTS_CollectedDate BETWEEN '2019-01-01' AND  '2019-05-10'

 

Without the date filter the query returns data, but if i add the date filter no data is returned

I also tried using %INTERNAL, %EXTERNAL TO_DATE('2009-01-01','YYYY-MM-DD')

if i filter by RowID for example the filters work.(this is to make sure that the where clause is working)

Only the date filter fails.

Any Suggestions

Last answer 4 hours 45 min ago
0   0 3
0

comments

71

views

0

rating

Hi everyone,

Im new  in cache, i came from Java and im missing some features that i couldn't find in the documentation,  I hope you can help me with this questions.

Just a brief introducction: 

- Im in a project with old cache version, so saddly i can't use Eclipse + Atelier, so im using Studio.

- Currently im in a project with persistent classes, we want to turn apart the globals and focus on tables. 

The questions:

Is there any way to make something like hibernate for cache %Persistent classes? For example, if i want to get all the records for a table in Java+Hibernate+Criteria i'll do something like this:

myListOfObjects = myPersistentClass.list(); // Equivalent to SELECT *

In cache i have to  create a %SQL.Statement, put the query inside and get the recordSet, iterate it and then get a list of %Persistent objects, for every object i want to use it.

0   0 1
0

answers

0

comments

25

views

0

rating

Hi! I've been fiddling with linked tables to get data from other servers, and I encountered a problem that I'm curious about. Maybe I'm not using these tools as intended or there's more going on, so I'm asking here.

I'm running a query on linked table A, something simple like this:

select name from A where id = 5983658923646

And I get this error:

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

  [%msg: <>]

If id, or anything comparison, is something smaller like 4345, it works just fine. It's only when the where reaches a certain length, not just id, that it fails.

After switching Dialects from CACHE to MSSQL, it works! Does anyone know the reason for this? Is the linked table connection using something the cache dialect doesn't understand

Last answer 12 days ago Last comment 10 days ago
0   0 3
65

views

0

rating

Hi!

I was trying to create a query that can be exposed as a stored procedure (function actually) that would return a resultset with a random number of columns. 

Unfortunately, it seems that unless I specify the ROWSPEC annotation on the Query method, I won't get any columns exposed. I was hoping to implement QueryNameGetInfo method and specify the names and number of columns I would be returning dynamically. But it seems that GetInfo information is simply ignored.

Here is my code

Last answer 12 days ago Last comment 10 days ago
0   0 3
82

views

0

rating

Class ICT.Experiments.A Extends %Persistent
{
Property Name As %String;
Property Collection As list Of ICT.Experiments.B;
Property Collection2 As array Of ICT.Experiments.C;
}

Class ICT.Experiments.B Extends %Persistent
{
Property Name As %String;
}

Class ICT.Experiments.C Extends %Persistent
{
Property Name As %String;
}

I have the classes above and I can select columns from the array collection by using two joins as follows:

SELECT 
mainTable.ID, mainTable.Name, c.Name
FROM ICT_Experiments.A mainTable
LEFT JOIN ICT_Experiments.A_Collection2 arrayTable ON arrayTable.A = mainTable.%ID
LEFT JOIN ICT_Experiments.C c ON c.%ID = arrayTable.Collection

Last answer 19 April 2019 Last comment 24 April 2019
0   1 4
71

views

0

rating

Hello all,

I am still learning CACHE and have below question.

In one of my ZEN method i am creating a table using

&sql(CREATE TABLE xxxxx(TNAMESPACE CHAR(100),TINTERFACE CHAR(100)))

Does any one knows how to concatenate a $USERNAME or any  content of a variable to the name of table

Last answer 23 April 2019 Last comment 23 April 2019
0   0 1
46

views

0

rating

Hello,

I am trying to run a simple SELECT STATEMENT in USER NAMESPACE.

SELECT * FROM projectname.table

What is the syntax if i need to get data for a table in another namespace eg. SAMPLES., SYS

SELECT * FROM SAMPLES.projectname.table  is not working.

Thanks in advance.

Jimmy Christian.

Last answer 22 April 2019 Last comment 23 April 2019
0   0 5
107

views

0

rating

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:

set query = "select JSON_OBJECT('field1': field1, 'field2":field2) from MyTable where x=? and y=?"

    set tStatement = ##class(%SQL.Statement).%New()
    set qStatus = tStatement.%Prepare(query)
    if qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT

Last answer 13 April 2019 Last comment 16 April 2019
0   2 1
163

views

0

rating

We use a lot of external MS SQL calls to look up numerous things when it comes to HL7. I am running into an issue where the outbound calls are queued but the message is sent onto the next process before it gets a response. How do I get it to wait till the response gets back from the MS SQL call before sending it onto the next process?

 

Below is a screen shot of what I am trying to explain...Select Research Study executes at 8:33 the message is sent to the next process at 8:34, but the response of the MSSQL call doesn't come till 9:31.

 

Last answer 13 April 2019 Last comment 16 April 2019
+ 1   0 4
93

views

+ 1

rating

Hello,

When i click on the menu to  run the  Data import wizard  from MP, i receive following CSP error

<UNDEFINED>zOnPageHEAD+229^%cspapp.exp.utilsqleximwizardcontent.1 *schemaname : CSP Error

 

It is happening for all the namespaces. Looks like some permission issue.  Same issue with Data Export wizard. Help to resolve this will be appreciated.

I am using

Cache for Windows (x86-64) 2017.2.2 (Build 865_0_18763U)

Thanks,

Jimmy Christian.

 

Last answer 12 April 2019 Last comment 12 April 2019
0   0 1
38

views

0

rating

Hi, I have used CSP to exec SQL selects from  any own NAMESPACE. But in our servers we have many SQL GATEWAY CONNECTIONS.

I'd like to create a CSP page that could use these gateway to exec SQL using these gateway connections, only Administrators will use that page to launch many select at many dsn. I'm not sure if we must deploy that CSP on %SYS namespace and how to use DSN(SQL Gateway connections) that are defined on server.

Anoyone has made that?

For example:

DSN CLIENT ONE

DSN CLIENT ONE

 

CSP Webpage:

TEXTBOX: Introduce your select:

select count(*) from product where stock<100 

OPTION BUTTON:

*CLIENT ONE

*CLIENT TWO

 

Result:

Product One.

Product Two.

Product Five...

 

 

 

 

0   0 1
0

answers

0

comments

36

views

0

rating

Hello everyone!
I need to have a ResultSet of type % SQL.Statement show its contents when it is trafficked in a message property by Business Process.
I tried to use the % XML.DataSet type that inherits properties of type % XML.Adaptor, but did not work.
Is there any other way to traffic as an object, other than within a Stream?

Note: I can not traffic Streams and I will not be able to use Correlate in this case.

Last answer 3 April 2019 Last comment 5 April 2019
0   0 2
46

views

0

rating

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.

Last answer 4 April 2019 Last comment 3 April 2019
0   0 2
67

views

0

rating

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).%New()
Set query = "Select Val1, Val2 FROM Table WHERE Val1=""SomeCondition"""

  • Using embedded SQL

&SQL(SELECT Val1, Val2
                INTO :val1, :val2
                FROM Table
                WHERE Val1="SomeCondition")

Last answer 1 April 2019 Last comment 28 March 2019
0   0 3
164

views

0

rating

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

Last answer 20 March 2019 Last comment 21 March 2019
0   0 3
63

views

0

rating

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 21 March 2019 Last comment 12 March 2019
0   0 6
238

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 7 March 2019
+ 1   0 3
0

comments

269

views

+ 1

rating

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

Last answer 6 March 2019 Last comment 6 March 2019
+ 1   0 4
128

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 5 March 2019 Last comment 6 March 2019
+ 1   0 6
131

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   0 3
65

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

comments

44

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   0 4
155

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   0 2
65

views

0

rating