AllAccepted answers
Evgeny Shvarov · Sep 24

Thanks @David Hockenbroch !

An almost ideal pattern to run an SQL query in ObjectScript. 

If you could also share with error handling around?

My typical pattern looks like this one below. Includes try-catch in case when I need to rollback something:

try {

set tStatement = ##class(%SQL.Statement).%New()

set query = "ALTER Table "_table_" ADD PRIMARY KEY ("_column_")"

$$$TOE(st,tStatement.%Prepare(query))

set rset=tStatement.%Execute()

if rset.%SQLCODE < 0 {

write "Error adding primary key: "_rset.%SQLCODE_" "_rset.%Message

set ex=##class(%Exception.SQL).CreateFromSQLCODE(rset.%SQLCODE,rset.%Message)

$$$ThrowStatus(ex.AsStatus())

}

}

catch ex {

set st=ex.AsStatus()

}
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 class
set 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.