Question
Evgeny Shvarov · Jan 31

Getting SQL Table Name For a Given Class

Hi folks!

Consider I have a class "X.Y.Z"

What is the SQL table name for it? How can I obtain it via ObjectScript? 

A quick search doesn't show any methods and properties. Documentation is a bit "wrong" here saying that the SQL table name is the same. It will be at least 'x_y.z'.

Any help is very appreciated!)

0
0 377
Discussion (9)1
Log in or sign up to continue

example:

set def=##class(%Dictionary.CompiledClass).%OpenId("oex.Dir")
write def.SqlSchemaName
oex
write def.SqlTableName
Dir 

set def=##class(%Dictionary.CompiledClass).%OpenId("Sample.Person.JSON")
write def.SqlSchemaName
Sample_Person
write def.SqlTableName
JSON

The simplest way is to create a classmethod, which returns the desired name:

Class DC.Evgenys.Data Extends %Persistent
{
/// which: 0=Fullname, 1=Schemaname, 2=Tablename
ClassMethod TableName(which = 0) [ CodeMode = objectgenerator ]
{
    set sch=%compiledclass.SqlSchemaName, tab=%compiledclass.SqlTableName
    do %code.WriteLine($c(9)_"quit $p("""_sch_"."_tab_","_sch_","_tab_""","","",which+1)")
    quit $$$OK
}
}

So you can do something like this:

for i=0:1:2 write ##class(DC.Evgenys.Data).TableName(i),!
DC_Evgenys.Data
DC_Evgenys
Data

See

$$TableName^%occLGUtil(pClass) (taken from sources %DeepSee.Report.Model.Report)
##class(%DeepSee.Utils).%GetSQLTableName(pClass)
##class(%ZEN.DataModelUtils).GetSQLTableName(pClass)
##class(%DeepSee.Generator).%GetSQLTableName(pClass) (my choice)

It is worth remembering that the table name can be a reserved word, so you need to put it in quotation marks.

For example, the method %DeepSee.Generator:%GetSQLTableName does not always work correctly for Caché:

Class count.sum.select Extends %Persistent SqlTableName "current_date.max" ]
{

ClassMethod TableName(which 0) [ CodeMode = objectgenerator ]
{
  set sch=%compiledclass.SqlSchemaNametab=%compiledclass.SqlTableName
  do %code.WriteLine($c(9)_"quit $p("""_sch_"."_tab_","_sch_","_tab_""","","",which+1)")
  quit $$$OK
}

/// d ##class(count.sum.select).Test()
ClassMethod Test()
{
  i=0:1:2 ..TableName(i),!

  pClass=$classname()
  !,##class(%DeepSee.Generator).%GetSQLTableName(pClass),!

  w $$TableName^%occLGUtil(pClass),!
  ##class(%DeepSee.Utils).%GetSQLTableName(pClass),!
  ##class(%ZEN.DataModelUtils).GetSQLTableName(pClass),!
}

}

USER>##class(count.sum.select).Test()
count_sum.current_date.max
count_sum
current_date.max
 
count_sum."current_date" ERROR!
count_sum.current_date.max
count_sum.current_date.max
count_sum.current_date.max

In IRIS 2021.2, an error occurs yet at the compilation stage of the class.

Thanks, @Vitaliy.Serdtsev ! This is a good catch!

If you ask me, it should be the responsibility of the class compiler to check table names against reserved words and to quote them, if neccessary.
I think, you have closer connection to the development - what about an enhancement request?
Maybe a new property, say SqlTableNameQuoted.

Hi @Julius Kavay! I agree with you! Could you please submit your suggestion on our dedicated Community Ideas site? I could do it too, but it's great to know that it's not an employee who needs that but partners, and ... I already submitted a lot :)

Sign with your DC credentials.