New post
Question
· May 31, 2016

How to get workstation name of current connection in SQL from Caché

#SQL #Caché

Recently I was asked by a customer with this question. In MS SQL Server, there is a function called host_name() that will return the work station name.

Here is how I would do it in Caché :

With default SQL schema name, in my case SQLUser in SQL (which is equivalent as User in Caché class definition), I have a class called User.dummy, I added a classmethod called hostname and expose it as SQL function host_name:

 

/// 
Class User.Dummy Extends %Persistent [ ClassType = persistent, DdlAllowed, ProcedureBlock, SqlRowIdPrivate, SqlTableName = dummy, StorageStrategy = "" ]
{

Property f1 As %Library.Integer(MAXVAL = 2147483647, MINVAL = -2147483648) [ SqlColumnNumber = 2 ];

ClassMethod hostname() As %Library.String [ SqlName = host_name, SqlProc ]
{
    //quit $ZU(67,12,$j)
    set process=##CLASS(%SYS.ProcessQuery).Open($j)
    quit process.ClientNodeName

}


}

}

I have already populated one row in my SQLUser.dummy table.

From SQL client, I can do this SQL statement:

 

select host_name() from dummy

 

#             Expression_1

1              tongwinmac

  Complete

Discussion (4)1
Log in or sign up to continue
Peter Everett · Jun 2, 2016

%SYS.System.GetNodeName() looks like the replacement for that $zu function.

Correction - %SYS.System.GetNodeName() is the replacement for $zu(110) which you were using in the initial version of this post.  The replacement for $zu(67,12) looks like it could be either %SYS.ProcessQuery.ClientNodeName or %SYSTEM.Process.ClientNodeName().  The full list of replacement functions is at http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

0 0
Jenna Poindexter · Jun 2, 2016

You could create a SQL Stored procedure to return ##class(%Library.Functions).HostName(), such as:

 Class Utils.Procedures Extends %RegisteredObject
{

ClassMethod hostname() As %String [ SqlProc ]
{
 /* method code */
      Quit ##class(%Library.Function).HostName()
}

}

And once that was done,  you can then use that stored procedure from a sql query, such as:

SELECT Utils.Procedures_HostName()

which on my system returns

poindextwin10vm

which is the hostname of my Windows system

2 0
Harry Tong · Jun 2, 2016

I made same mistake in my original post when I used $ZU(110), just like yours and another comments on this post, we will all get the hostname of the Cache server, not the workstation name of the xDBC connection comes from. My customer was specifically looking for the workstation name establishes the connection, not the host where Cache runs.

0 0