New post
Question
· Apr 19, 2016

SQL Sequence

#SQL #Caché

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.

Discussion (4)0
Log in or sign up to continue
Kyle Baxter · Apr 19, 2016

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
1 0
Fabio Goncalves · Apr 19, 2016

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
}

}
 

1 0