#SQL

I have a Cache-compatible sql script file and each query is separate by white space.

How could I ask Cache to execute all the queries in this file and dump the result to a specified file?

Thanks.

Hello All,

what is the best method for search a portion of text in a non-indexed global?

I need to implement an autocomplete kind of search, in a global of >1M registers (text type, not $lb)

Maybe the best way would be use a SQL mapped class, with 'Bitmap' indexes?

Thanks in advance!

I get two methods below: I would run both methods concurrently.

However, the "testRead" would always read the uncommitted results from "testInsert".

Anyway to avoid that? Thanks.

ClassMethod testInsert()
{
  &sql(START TRANSACTION ISOLATION LEVEL READ COMMITTED, READ WRITE)
  &sql(insert into Test.Table(AttrA,AttrB,AttrC,AttrD) values(1,2,3,4))
  hang 15
  &sql(ROLLBACK)
}

ClassMethod testRead()
{
  &sql(START TRANSACTION ISOLATION LEVEL READ COMMITTED)
  &sql(select count(*) into :ans from Test.Table)
  &sql(COMMIT)
  w !,ans
}

I am trying to replace one of our SQL Integration Service jobs with Ensemble and I am running into an issue executing a query against a MS SQL database using JDBC drivers.

My SQL Outbound code looks like this.

 Method SelectPaceartCharges(pRequest As osuwmc.Paceart.DataStructures.SelectPaceartBillingExtract, Output pResponse As EnsLib.SQL.

The Art of Mapping Globals to Classes (3 of 3)

If you are looking to breathe new life into an old MUMPS application follow these steps to map your globals to classes and expose all that beautiful data to Objects and SQL.

If the above does not sound familiar to you please start at the beginning with the following:

The Art of Mapping Globals 1

The Art of Mapping Globals 2

This example is going to show you how to map a classic parent-child structure.

So just this morning we started to get this error from all of our SQL-client based tools that connect to our Caché SQL via ODBC: 

[%msg: <Error instantiating cached query class in Prepare(): <METHOD DOES NOT EXIST>Prepare+41^%SYS.SQLSRV *%New,%sqlcq>] (State:S1000, Native Code: 190)]

No queries work... any help? stumped over here.

doing a SQL query via Class %SQL.Statement

##class(%SQL.Statement

in Caché command line works... so that's why I think it's an ODBC issue..

I can't seem to find a SQLSRV class in the %SYS

Greetings to all!!! Suppose there is a table Mother (ID, Name) and Child (ID, Name, Mother), Mother in the table Childis a relationship. Let's say the task is to deduce the names of all the children whose their moms' names start with the letter 'A', I can do this in two ways in sql, and I can not understand the difference, the pros and cons that when to use:

1) SELECT Child.Name FROM Mother INNER JOIN Child ON Mother.ID = Child.Mother WHERE Mother.Name LIKE 'A%'
2) SELECT Child.Name FROM Child WHERE Child.Mother->Name LIKE 'A%'

Hi,

I have a class with around 400k lines and 60 columns. Class storage is Cache SQL storage (Mapped from a global).

 I want to create multiple indices on certain fields.

I am familiar with two approaches:

1. Create a new map (Index type) on a pointer global.

2. Create a bitmap index

Which approach is more recommended to be used in the case I described? If there are any other approaches, I will be happy to hear.

Thanks :)

Hi World

I've a problem when in extract a float value from my database

the problem is that i get  an interger instead of  float.

my record is 2,56 but when i do a select , the request extract only 2

can you have a solution  for it.

thank you

HI,

I have a query like below but its syntax is not accepted by Cache.

I would like to perform a left join on two tables first and then make a inner for this result with another table.

Given the constraint that we only allow one SELECT in the query, it is possible to achieve this semantics ?

Thank for your help.

Select * 
FROM ( sample . employee e 
LEFT JOIN  sample . company c 
on c . id = e . id ) g
JOIN sample . vendor v
on v . %id = g . attr 

I am pleased to announce that Release Candidates of Caché and Ensemble 2017.2 are now available.

Many customers have already downloaded the Field Test over the past few months, and we appreciate your feedback.

This release contains significant improvements, including:

• Parallel dejournaling to improve throughput of mirroring and journal restore.
• New iFind and iKnow features like co-occurrence search, performance improvements, and more.
• SQL enhancements in Query Auditing, optional ANSI SQL operator precedence, and Frozen Plan Evolution, along with our normal cast of query optimization improvements.

I have a class that has a list property, which contains a list of other objects, and I want to join against it in SQL.

Class Foo Extends %Persistent
{
Property MyBars As list Of Bar;
}

Class Bar Extends %Persistent { Property Name As %String; }

Simply querying the Foo table, I see that MyBars looks like a $LIST, so I tried using a query with the %inlist operator but that didn't seem to work as expected. The following query produces zero results:

select bar.name from foo join bar on bar.id %INLIST MyBars

Is there some convenient way to do this sort of join?

Hi, folks!

Is there any way to use $CASE or $SELECT functionality in SQL SELECT query?

E.g. something like this:

SELECT product, $CASE(status,"New":field1,"Payed":field2) as data from sales

To see either in data column either field1 or field2 values in regard of status value.

Hi, folks!

When you deploy DeepSee solutions you often do not want grant a User  %All Role to work with a particular Dashboard.

Consider a Dashboard 'Dash' with a few widgets where listings are being used.

If you manage a Role to get access to the Dash you need to grant access to %DB_DBNAME resource to have a database access,  grant access to a Dashboard resource (if any) and ...  grant SELECT accesses to all the tables involved in SQL queries being used in all the listings of widgets.

And every

Hi, i want to extract the timestamp values and order them low to hight , how can i do, you find here a screenchot of my batabase

thank's

Hi , World

this is i field in my global:

{"profile_id":"9XOzzcI8NfSUjxAhEt0cTLRejwmp6HPi","biometrics":[{"timestamp":"2017-05-17T13:45:40","utc_offset":"+02:00","resting_heartrate":120.0,"spo2":98.0,"activity_id":"591c540aac8f295479ee14ce"}]}

for information: it's respective key is "peyload"

my question is :

I want to extract the timestamps value , and the profile_id value , how can i do?

thank's

hi world, i want to browse an sql table on cache, and get min ,max value from one collumn, how can i do it ?

thank's

Hi,

I'm developing an integration between Caché servers by ODBC conection, and I have the following problem.

In this call:

call COSClass_Methode('222169^^98^155^64530^06:30^021542987897458855441112877855^1^0^281992^GC')

the ODBC driver is truncating the string to 50 characters.

If I run this same command with $system.SQL.Shell(), this doesn't occur.
I did a test creating several parameters for COSClass_Methode, and they all have a 50 character limitation.

Does anyone know why the ODBC Cache Driver limits the number of characters per parameter of this type of call?
Or, how can I adjust this?

Is it possible to execute a sql update statement from objectscript? This code isn't working for me.

Set tSQL = "UPDATE table Set Status = 'Completed' WHERE ID in (1,2,3,4)"
Set tStatement = ##class(%SQL.Statement).%New()
Set tSC = tStatement.%Prepare(tSQL)

If I write my dynamic sql to the event log, copy and execute it in the Management Portal, it works fine.

Hi everyone, I have a SQL service that is working fine, except I don't want it to run on a schedule or continuously. I'd like to only run when requested. Run once and then stop until another request. Is there anyway to set up a service like that?

I have a simple SQL service that does a simple select from an SQL database. After the select, I do an update to set the ProcessedFlag to "Y" for yes, so my next pass doesn't select records already processed. The service works fine, except when it's done I get the error below, anybody know what is causing this error?

I worked through the Community for proposals to provide end users
in an easy way with data formatted as EXCEL sheet.

There is a great article Tips & Tricks - SQL to Excel

there's an important message embedded: "EXCEL can interpret HTML tables and display them as usual"

Where's the light weight export to EXCEL ?
Good old CSP is well equipped to produce HTML tables accepted from EXCEL as input.
With modern Browsers you don't even need <head>and  <body> tags.
So the required code around your SQL result set is really slim.
And you are free to add any formatting you need either by HTML or in SQL.

In $system.SQL.Shell(), it would also report the number of global references for a given query.

Is it possible that I could also get this statistics from the %SQL.Statement interface when I launch a dynamic sql?

Thanks.

I use %SQL.Statement. The original column names are not contained in the metadata.

Sample query:

SELECT
 column1->name as name,
 column2 age
FROM sample.table

How to get:

•  column1->name
•  column2 

I am currently working on a issue with WRC on one of my Inbound SQL Adapters not returning all the records it should be. If I looked at the count of the records in Ensemble and compare it to that of a Microsoft SQL View, Ensemble seems to be off by a few records here and there. I am using a full dynamic select statement in my settings of the adapter.

In a previous exercise, I was able to show the power of Caché.
A medium-designed set of interdependent tables with some GB of data.
URLs cross reference over some million pages resulting in ~3 billion records

Competition was between

• Caché
• PostgreSQL
• MySQL

Criteria were Speed + Storage consumption
I composed  a customized loader fed over a "raw" TCP connection
Mapping the "objects" into the final table by directly writing to Global Storage.

Is it possible to import a Cube from SQLServer (format .abf) in a Cache?

I have an  .abf file and I want to open it in Cache. Is it possible?

Thanks

hi

  I execute sql  like this:  select * from DHC_PatBillDetails where PBD_PBO_ParRef>='2046121'

error message:

but   the sql:   select * from DHC_PatBillDetails where PBD_PBO_ParRef='2046121'   can be executed successfully

why?

I have a query string that I am creating programmatically, based on some user inputs.  The user might search on 5 fields, or 8 fields, or no fields.

In my sql statment, some of these fields require parameters in the %Execute statement.

For example:

if user picks lastname, sql = "select * from person where lastname = ?"

if user also picks age, sql = "select * from person where lastname=? and age > ?"

I then have these lines of code to create my result set:

set statement = %SQL.Statement

statement.%Prepare

resultset = statement.%Execute(param1, param2)

-- but it might be 

resultset = statement.

Hi,

Is it possible to use the value of a column that is populated by its own subquery,  in the WHERE clause of the outer-query ?

The following fails (it does not parse syntactically):

SELECT A, B, (SELECT S1 FROM Table2) "C" FROM Table1 WHERE C>10

I guess I could wrap it up as in inner query of it's own - this way (which works) :

Select * from 
(SELECT A, B, (SELECT S1 FROM Table2) "C" FROM Table1)
where C>10

but I was wondering if there was some syntax in the original snippet that I could do instead.

Thanks - Steve