0
969

Written by

Evgeny Shvarov
Senior Startups and Community Programs Manager at InterSystems Corporation
STAFF
Follow
Question Evgeny Shvarov · Jan 31, 2022

Getting SQL Table Name For a Given Class

#InterSystems IRIS #ObjectScript #SQL

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!)

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

Comments

Robert Cemper · Jan 31, 2022

example:

set def=##class(%Dictionary.CompiledClass).%OpenId("oex.Dir")
write def.SqlSchemaName
oex
write def.SqlTableName
Dir 
0
Robert Cemper · Jan 31, 2022 
set def=##class(%Dictionary.CompiledClass).%OpenId("Sample.Person.JSON")
write def.SqlSchemaName
Sample_Person
write def.SqlTableName
JSON
0
Julius Kavay · Jan 31, 2022

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
0
Vitaliy Serdtsev · Feb 1, 2022

See

<FONT COLOR="#0000ff">$$</FONT><FONT COLOR="#ff0000">TableName</FONT><FONT COLOR="#000000">^%occLGUtil(</FONT><FONT COLOR="#800000">pClass</FONT><FONT COLOR="#000000">)</FONT> (taken from sources %DeepSee.Report.Model.Report)
<FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%DeepSee.Utils</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%GetSQLTableName</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">pClass</FONT><FONT COLOR="#000000">)</FONT>
<FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%ZEN.DataModelUtils</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">GetSQLTableName</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">pClass</FONT><FONT COLOR="#000000">)</FONT>
<FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%DeepSee.Generator</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%GetSQLTableName</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">pClass</FONT><FONT COLOR="#000000">)</FONT> (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é:

<FONT COLOR="#000080">Class count.sum.select Extends %Persistent </FONT><FONT COLOR="#000000">[ </FONT><FONT COLOR="#000080">SqlTableName </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#008000">"current_date.max" </FONT><FONT COLOR="#000000">]
{

</FONT><FONT COLOR="#000080">ClassMethod </FONT><FONT COLOR="#000000">TableName(</FONT><FONT COLOR="#ff00ff">which </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#000080">0</FONT><FONT COLOR="#000000">) [ </FONT><FONT COLOR="#000080">CodeMode </FONT><FONT COLOR="#000000">= objectgenerator ]
{
  </FONT><FONT COLOR="#0000ff">set </FONT><FONT COLOR="#800000">sch</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#800000">%compiledclass</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">SqlSchemaName</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#800000">tab</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#800000">%compiledclass</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">SqlTableName
  do </FONT><FONT COLOR="#800000">%code</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">WriteLine</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#0000ff">$c</FONT><FONT COLOR="#000000">(9)</FONT><FONT COLOR="#008000">"quit $p("""</FONT><FONT COLOR="#000000"></FONT><FONT COLOR="#800000">sch</FONT><FONT COLOR="#000000"></FONT><FONT COLOR="#008000">"."</FONT><FONT COLOR="#000000"></FONT><FONT COLOR="#800000">tab</FONT><FONT COLOR="#000000"></FONT><FONT COLOR="#008000">","</FONT><FONT COLOR="#000000"></FONT><FONT COLOR="#800000">sch</FONT><FONT COLOR="#000000"></FONT><FONT COLOR="#008000">","</FONT><FONT COLOR="#000000"></FONT><FONT COLOR="#800000">tab</FONT><FONT COLOR="#000000">_</FONT><FONT COLOR="#008000">""","","",which+1)"</FONT><FONT COLOR="#000000">)
  </FONT><FONT COLOR="#0000ff">quit $$$OK
</FONT><FONT COLOR="#000000">}
</FONT><FONT COLOR="#000080">/// d ##class(count.sum.select).Test()
ClassMethod </FONT><FONT COLOR="#000000">Test()
{
  </FONT><FONT COLOR="#0000ff">f </FONT><FONT COLOR="#800000">i</FONT><FONT COLOR="#000000">=0:1:2 </FONT><FONT COLOR="#0000ff">w </FONT><FONT COLOR="#000000">..</FONT><FONT COLOR="#0000ff">TableName</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">i</FONT><FONT COLOR="#000000">),!
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">pClass</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#0000ff">$classname</FONT><FONT COLOR="#000000">()
  </FONT><FONT COLOR="#0000ff">w </FONT><FONT COLOR="#000000">!,</FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%DeepSee.Generator</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%GetSQLTableName</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">pClass</FONT><FONT COLOR="#000000">),!
  </FONT><FONT COLOR="#0000ff">w $$</FONT><FONT COLOR="#ff0000">TableName</FONT><FONT COLOR="#000000">^%occLGUtil(</FONT><FONT COLOR="#800000">pClass</FONT><FONT COLOR="#000000">),!
  </FONT><FONT COLOR="#0000ff">w </FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%DeepSee.Utils</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%GetSQLTableName</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">pClass</FONT><FONT COLOR="#000000">),!
  </FONT><FONT COLOR="#0000ff">w </FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%ZEN.DataModelUtils</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">GetSQLTableName</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">pClass</FONT><FONT COLOR="#000000">),!
}
}</FONT>
USER><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">count.sum.select</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">Test</FONT><FONT COLOR="#000000">()</FONT>
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.
0
Julius Kavay  Feb 1, 2022 to Evgeny Shvarov

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.

0
Evgeny Shvarov  Feb 3, 2022 to Julius Kavay

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. 

0
Enrico Parisi · Mar 10

I know this is an old question, but in case someone will search for the same question, another option (better, IMHO) is to use:

$$$ClassSQLTable("my.ClassName")

In case you need it for current class:

$$$ClassSQLTable($classname())

To use the $$$ClassSQLTable macro your class needs:

Include Ensemble

0
Evgeny Shvarov  Mar 10 to Enrico Parisi

Thank you, @Enrico Parisi! 

The only thing I have a concern about here is "what is Ensemble,"? and why not Include IRIS? :)

But it is not a question to you of course.

0
Vitaliy Serdtsev  Mar 10 to Evgeny Shvarov

IRIS for Windows (x86-64) 2024.3 (Build 217U) Thu Nov 14 2024 17:59:58 EST

Or "Include EnsUtil"

Now you can do it like this:

<FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%BigData.ShardedSQL</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">ClassNameToTableName</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#808000">pClassName</FONT><FONT COLOR="#000000">)</FONT>
0
Enrico Parisi  Mar 10 to Vitaliy Serdtsev

Be careful, in some cases that method does not work correctly.

I was testing this using a class where the package redefine the SQL schema name for the classes, in that case the ClassNameToTableName() returns a wrong result.

0
Evgeny Shvarov · Aug 21

BTW, faced the same situation today to have an option of getting full sqlname for a class, and generated a classmethod in VSCode with AI (no my personal touch):

ClassMethod GetSQLTableName(pclass As%String) As%String
{
// returns the SQL table name for a classset tablename = ##class(%DeepSee.Utils).%GetSQLTableName(pclass)
if tablename="" {
set tablename=$TR($P(pclass,".",1,*-1),".","_")_"."_$p(pclass,".",*)
}
return tablename
}

And its working. Not bad for a bot.

0