SQL Sequence

Primary tabs

Does Caché support SQL CREATE SEQUENCE as in PostgreSQL?

If not, what would be the best alternative? Create my own sequence logic as the example bellow? 

 Class Util.MySequence Extends %Persistent [ ClassType = persistent, ProcedureBlock ]
{


Property dummy As %String;

Property myvalue As %Integer [ Calculated, SqlComputeCode = { Set 
{myvalue}=##class(Util.MySequence).CurrentValue()}, SqlComputed ];

ClassMethod CurrentValue() As %Integer
{
        s x=$I(^MySequence)
	Quit x
}

Method myvalueGet() As %Integer [ CodeMode = expression ]
{
..CurrentValue()
}

}

 

Thanks.

 

 

  • 0
  • 0
  • 383
  • 2
  • 2

Answers

Not an answer per se, but why do you specify both "extends %Persistent" and "ClassType = persistent"? Is it necessary?

Not necessary. It was just a copy and paste. You can use a registered for this and just expose your classmethod as sqlproc.

Two examples below, one with a name and one without.  It looks like this is supposed to work like a Stored Procedure (according to my understanding of the Postgresql docs).  Having a property that uses the function as its sqlcomputecode is pretty trivial.

 


Class Test.Seq {

Classmethod KyleSeq() as %Integer [sqlproc, sqlname="KyleSeq", codemode=expression]

{

$I(^KMB("KyleSeq"))

}

Classmethod KyleSeqName(name as %String) as %Integer [sqlproc, sqlname="KyleSeqName",codemode=expression]

{

$I(^KMB($G(name,"KyleSeq")))

}

}

SAMPLES>>select Test.KyleSeqName('Fabio')

8. select Test.KyleSeqName('Fabio')

Expression_1

3



SAMPLES>>select Test.KyleSeq()           

9. select Test.KyleSeq()


Expression_1

5

Sharing with all another example.

Credits to Amir Samary.

Class Sequence.Implementation Extends %RegisteredObject
{

/// SQL Example:
/// select Sequence.NextVal('TEST')
ClassMethod NextVal(pSeqName As %String, pContext As %String = "*") As %Integer [ SqlName = NextVal, SqlProc ]
{
set tSeqName = $ZConvert(pSeqName,"U")
Set tContext = $ZConvert($Select(pContext'="":pContext,1:"*"),"U")
Set seq = ""

    if $Data(^Sequence(tSeqName))
    {
     set seq = $Increment(^Sequence(tSeqName,tContext))
     Set %Sequence(tSeqName,tContext)=seq //For use by CurrVal()
    }
    else 
    {
     throw ##class(%Exception.SQL).CreateFromSQLCODE("-460","Sequence does not exist")
    }
    
Quit seq
}

/// SQL Example:
/// select Sequence.CurrVal('TEST')
/// WARNING: This will not work when called on the Management Portal since different processes
/// on the CSP Gateway pool may be answering to the HTTP request to execute the query.
/// This should work properly vía ODBC/JDBC since a single process is kept open for us and should
/// also work on $System.SQL.Shell().
ClassMethod CurrVal(pSeqName As %String, pContext As %String = "*") As %Integer [ SqlName = CurrVal, SqlProc ]
{
set tSeqName = $ZConvert(pSeqName,"U")
Set tContext = $ZConvert($Select(pContext'="":pContext,1:"*"),"U")
Set seq = ""

    if $Data(%Sequence(tSeqName,tContext))
    {
     set seq = %Sequence(tSeqName,tContext)
    }
    else 
    {
     throw ##class(%Exception.SQL).CreateFromSQLCODE("-460","There is no current value for this sequence. Call NextVal() first.")
    }
    
Quit seq
}

/// Create a new Sequence if it not exist. If the sequence exist, then return an error message
/// Ejemplo de SQL:
/// select Sequence.CreateSequence('TEST')
ClassMethod CreateSequence(pSeqName As %String, pContext As %String = "*") As %String [ SqlName = CreateSequence, SqlProc ]
{
set tSeqName = $ZConvert(pSeqName,"U")
Set tContext = $ZConvert($Select(pContext'="":pContext,1:"*"),"U")

    if $Data(^Sequence(tSeqName))
    {
        throw ##class(%Exception.SQL).CreateFromSQLCODE("-460","Sequence already exists")
    }
    else
    {
        set ^Sequence(tSeqName) = ""
        set ^Sequence(tSeqName,tContext) = 0
    }
    
    Quit 1
}

/// Drop a sequence if this exist, if it not exist, then return an error message
/// Ejemplo de SQL:
/// select Sequence.DropSequence('TEST')
ClassMethod DropSequence(pSeqName As %String) As %String [ SqlName = DropSequence, SqlProc ]
{

set tSeqName = $ZConvert(pSeqName,"U")

    if $Data(^Sequence(tSeqName))
    {
        Kill ^Sequence(tSeqName)
        Kill %Sequence(tSeqName)
    }
    else
    {
        throw ##class(%Exception.SQL).CreateFromSQLCODE("-460","Sequence does not exist")
    }
    
    Quit 1
}

}