SQL

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.

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

Cache version: Cache for Windows (x86-64) 2017.2.1 (Build 801_3U)

Good Afternoon,

I have a co-worker who is trying to run the below query via ODBC. The issue is that the query appears to be running extremely slow (nearly 2 hours)

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

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.

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.

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

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.

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

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

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

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

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.

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