New post
Question
· Mar 7, 2016

SQL in Atelier

#Development Environment #SQL #Caché

Hello! 

I'm trying to run a simple SQL query in atelier but it complains about the manager.  I'm not sure if this is the best way to do it but here is what I got so far:

 



	ZNSPACE "User"
	
	SET domId = 1
	SET flister=##class(%iKnow.Source.SQL.Lister).%New(domId)
	SET myloader=##class(%iKnow.Source.Loader).%New(domId)
 
 
	SET myquery="select top(10) EntityValue, CorpusFrequencyAsRelation, CorpusSpreadAsRelation from %iKnow_Objects.EntityUnique  WHERE DomainId = 1 ORDER BY  CorpusFrequencyAsRelation desc"
	SET idfld="EntityValue"
	SET grpfld="CorpusSpreadAsRelation"
	SET dataflds=$LB("CorpusSpreadAsRelation")

	SET stat=flister.AddListToBatch(myquery, idfld, grpfld, dataflds)	
		IF stat '= 1 {WRITE "The lister failed: ",$System.Status.DisplayError(stat) QUIT }
	SET stat=myloader.ProcessBatch()
		IF stat '= 1 {WRITE "The loader failed: ",$System.Status.DisplayError(stat) QUIT }

And the error I get:


ERROR #8230: Domain 1 is managed by User.SamtalSV and cannot be updated directly.1

How can I fix this? I don't need to change the table, I just need reading privileges

My second question: is this the easiest way of doing it? All I need is to print the table, maybe something like this is possible:


zwrite query("SELECT * FROM ...")

Thanks for all the great feedback so far!

Discussion (5)0
Log in or sign up to continue
Benjamin De Boe · Mar 7, 2016

Hi Benjamin,

If you just want a SQL prompt, you can open one from the COS prompt by calling "do $system.SQL.Shell()", or use the SQL page in the system management portal which you can find under the "system exploration" menu.

The SQL lister and loader functionality is meant to populate your domain (rather than query it), but should no longer be invoked directly. Managing a domain can be taken care of much more easily through domain definitions, which can be configured through the iKnow Architect as from 2016.1. But I see you're already using one, otherwise you wouldn't have seen that error message (which BTW informs you that this domain definition is configured not to allow any build/config operation other than through the domain definition itself, which is the default setting for domain defs).

 

If you want to achieve the same result through COS:

write ##class(%iKnow.Queries.EntityAPI).GetTop(.result, domainID)

zwrite result

 

regards,

benjamin

1 0
Benjamin Eriksson · Mar 8, 2016

Hello again Benjamin!
Thank you for the quick reply.

The EntityAPI would indeed do the job for the query above but I haven't found any API for the query below. However, I found out that $SYSTEM.SQL.Execute() does precisely what I need!

I have a few more minor questions about strings if you don't mind.

  1. Is string addition/concatenation possible in Object Script? set query = "select * from " + tableName
    I've read "Basic String Operations and Functions" but didn't find any answers. 
  2. Does Object Script supports multiline strings? The query below is very long and it would look nicer if it could be divided it into multiple lines.

SET myquery="select Master->EntityValue , Relation->EntityValue , Slave->EntityValue, source.SourceId FROM %iKnow_Objects.CrcUnique crc  JOIN %iKnow_Objects.CrcInSourceDetails as source ON crc.CrcUniId = source.CrcUniId WHERE source.DomainId = 1 AND Relation->EntityValue LIKE 'ring%' ORDER BY CorpusFrequency  DESC " 
	
SET result=$SYSTEM.SQL.Execute( myquery )
	
DO result.%Display()

The query should look for CRCs containing some words, and then return the CRC and the source that contains the CRC

0 0
Stefan Wittmann · Mar 8, 2016

Hi Benjamin,

regarding your questions:

1) String concatenation is possible with the underscore "_" character. For example:

set myquery = "SELECT * FROM " _ tableName

Don't use the plus "+" character as this is the addition operator. It will evaluate your string and convert it into a number. Unless your string starts with a number, it will be evaluated to 0. 

2) Multiline statements are not supported in Caché Object Script, so you have to concatenate your string if you want to spread the query on multiple lines.

HTH,

Stefan

1 0
Eduard Lebedyuk · Mar 12, 2016

Hi. About your second question, you can easily extend Caché ObjectScript, to do it. Wouldn't recommend it for use in production, but why not add some syntax sugar on you dev instance?

Create %ZLANGC00 mac routine with the following code:

 ; %ZLANGC00
 ; custom commands for ObjectScript
 ; http://docs.intersystems.com/cache20141/csp/docbook/DocBook.UI.Page.cls?KEY=GSTU_customize
  Quit  

/// Execute Query and display the results
/// Call like this:
/// zsql "SELECT TOP 10 Name FROM Sample.Person"
ZSQL(Query)
  #Dim ResultSet As %SQL.StatementResult
  Set ResultSet = ##class(%SQL.Statement).%ExecDirect(, Query)
  Do ResultSet.%Display()
  Quit

Save and compile it and then you can execute sql in a terminal like this:

zsql "SELECT TOP 10 Name FROM Sample.Person"

It would display something like this:

SAMPLES>zsql "SELECT TOP 10 Name FROM Sample.Person"
Name
Adam,Wolfgang F.
Adams,Phil H.
Ahmed,Edward V.
Ahmed,Michael O.
Ahmed,Patrick O.
Allen,Zelda P.
Alton,Samantha F.
Bach,Buzz E.
Bach,Fred X.
Bach,Patrick Y.
 
10 Rows(s) Affected

That said I myself prefer executing SQL queries in SMP because you don't need to type them there (drag&drop from the left panel or copy&paste from the code) - it's very convenient.

3 0