New post
AllAccepted answers
Robert Cemper · Jan 24, 2019

You could use the feature that any Classmethod may also serve as Stored Procedure.

Like this:

Class User.Remote
{
ClassMethod Echo(inout As %String) As %String [ SqlName = Demo, SqlProc ]
{ return "Echo:"_inout }
}

And then you may call your Procedure like this: getting back

SELECT DEMO('hello WORLD') 

getting back   

Echo:hello WORLD

All you have to care for is to return something.
what happens inside your ClassMethod is up to you and doesn't need to be related.

Robert Cemper · Jan 21, 2019

As you have an installation of Caché you will also have the Documentation with it.

I recommend "Using Caché ObjectScript" to start with ObjectScript.
It is also public accessible https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=GCOS

I also recommend having a look at the free online training library.
The link is in the header of this forum:  LEARNING

Browse Catalog and search for O bjectScript Basics

All details on individual commands, functions, system variables are again in your local instance or in the
public reference on docs.intersystems.com

Robert Cemper · Jan 20, 2019

My personal preference is to a have a solution NOW.
And not wait for something that may take too long for me.

just the most simple example to solve your issue with a ClassMethod:

Class nodes.Select
{
ClassMethod Address1(town As %String = "") As %String
{ set (list,id)=""
    for  { set id=$o(^Customer(id)) quit:id=""  
            if ^(id,"Address",1)=town set list=list_$lb(id) }
   return "["_$lts(list)_"]"
}}
Robert Cemper · Jan 20, 2019

See details in NoSQL Methods for Global Nodes

Especially  Get the Next Global Subscript: next() lets you iterate over first subscript  ^Customer(id)   1, 2, 3, ...

next using Retrieve a Global Node: get() you access ^Customer(id, "Address", 1) and check if it is "London"

from docs mydata.retrieve 

This method will return a list of subscript values that are defined directly beneath a given level in the global.

In your call, you get the full global. but you have to do the detail work yourself.

There is no such thing as SELECT ...... FROM GLOBAL WHERE .......

This is NoSQL.

But you may hide your functionality in a ClassMethod inCaché and use invoke_classmethod()

Robert Cemper · Jan 17, 2019

Without offering a solution to develop this magic data type that Julius suggested
You should also define what datatype you plan to present for the SQL side.
And that is always taken from the Compiled Property definition.

All streams typically present themselves a CLOB or BLOB or similar and have no MAXsize
While a String presents itself as a VARCHAR with a MAXSIZE.
I see no way to manipulate this on the fly.

For object access, you may write a Setter and Getter that covers the real nature of your data:
For SQL access I see no chance at the moment.

Indexing is another issue. This would require another piece of magic.

My suggestion:
have a %String
have a %Stream
and have a calculated property of  %String to decide which one to present.

like a centipede with a wooden leg: 99 times tic and 1 toc

the stream is then truncated and still requires extra coding. 
 

Robert Cemper · Jan 7, 2019

Hi Wolf,

I ran into a similar problem a few weeks ago: IRIS on Linux - no studio

My solution: Custom install on my local WIN10 desktop. Only the Studio (and ODBC drivers).
Nice side effect: the new IRIS-Studio also talks seamlessly to my local Caché. 

My assumption: As it works for an isolated Linux it should work for Docker as well (if no firewall blocks you wink)

Robert Cemper · Jan 4, 2019

HIVE docs on  String Types shows me:

and

Varchar

Varchar types are created with a length specifier (between 1 and 65535), which defines the maximum number of characters allowed in the character string. If a string value being converted/assigned to a varchar value exceeds the length specifier, the string is silently truncated. Character length is determined by the number of code points contained by the character string.

Related to your  ERROR that tells us that STRING has no size limit => it is a STREAM in our terms.
 

So if you don't convert a STRING to VARCHAR   [ preferable VARCHAR(255) ] you won't be able to use an alphanumeric ID
You may, of course,  add some artificial numbering of type BIGINT to be used as ID.

In any case, just with data type STRING I'd call this a rather a text file than an SQL usable table.

Without touching the original source you may need to write your own loader:

  • reading the HIVE "table" sequentially row by row
  • insert it into a manually designed table/class with automatic id