SQL Related Articles: Class to retrieve UniqueIdentifier
2004-01-16 -- Chris Husting
Newsgroups: softvelocity.clarion.databasedrivers
> I'm working on a MSSQL project where the primary keys are
> Uniqueidentifiers. How are you guys handling this with CW?
>
> Specificially, I need to know parent uniqueidentifier values when
> inserting both parent and children (think classic invoice header /
> detail procedure), but there is not anything akin to @@identity for
> guids.
>
> Currently the server is setup to use newid() for the default value.
> I'm considering generating the guid on the client, but maybe there is
> a better way?
Attached is a class (derived from FileManager) that I wrote to generate
a Guid for me on Inserts. And it timestamps on every Update. Before it
gets a Guid, it checks that the first field is of type CSTRING and is
named SYSID. Before it timestamps, it checks that the last two fields
are DATE and TIME, and their names are DateModified_DATE and
DateModified_TIME. So, you'll probably have to modify the code a little
to do exactly what you want. But it was very simple for me to
override the FileManager class in my data DLL and never have to worry
about it again.
Thanks,
Harley
The attached files are listed below as hjsql.inc and hjsql.clw:
FILE: hjsql.inc
!ABCIncludeFile
OMIT('_EndOfInclude_', _hjSqlPresent_)
_hjSqlPresent_ EQUATE(1)
INCLUDE('ABFILE.INC'), ONCE
hjSql CLASS(FileManager), TYPE, |
MODULE('hjSql.CLW'), |
LINK('hjSql.CLW', _ABCLinkMode_), |
DLL(_AbcDllMode_)
IdentityPrime PROCEDURE, PROTECTED
Insert PROCEDURE, BYTE, PROC, VIRTUAL, DERIVED
PrimeAutoInc PROCEDURE, BYTE, PROC, VIRTUAL, DERIVED
TimeStamp PROCEDURE, PROTECTED
TryInsert PROCEDURE, BYTE, PROC, VIRTUAL, DERIVED
TryPrimeAutoInc PROCEDURE, BYTE, PROC, VIRTUAL, DERIVED
TryUpdate PROCEDURE, BYTE, PROC, VIRTUAL, DERIVED
Update PROCEDURE, BYTE, PROC, VIRTUAL, DERIVED
END
_EndOfInclude_
FILE: hjsql.clw
MEMBER
!------------------------------------------------------------------------------
INCLUDE('hjSql.INC'), ONCE
!------------------------------------------------------------------------------
!------------------------------------------------------------------------------
MAP
END
OMIT('Link MSSQL Driver', LIB_MODE)
PRAGMA ('link (C%V%MSS%X%%L%.LIB)')
! Link MSSQL Driver
!------------------------------------------------------------------------------
!------------------------------------------------------------------------------
hjSql.IdentityPrime PROCEDURE !, PROTECTED
sav USHORT
tmp ANY
sGuid CSTRING(37)
CODE
IF SELF.File{PROP:Type, 1} = 'CSTRING' AND SELF.File{PROP:Name, 1} = 'SYSID'
sav = SELF.SaveFile()
SELF.File{PROP:Sql} = 'SELECT newid()'
NEXT(SELF.File)
tmp &= WHAT(SELF.Buffer, 1)
sGuid = tmp
SELF.RestoreFile(sav)
tmp = sGuid
END
RETURN
hjSql.Insert PROCEDURE !, BYTE, PROC, VIRTUAL, DERIVED
bRetval BYTE, AUTO
CODE
SELF.IdentityPrime()
SELF.TimeStamp()
RETURN PARENT.Insert()
hjSql.PrimeAutoInc PROCEDURE !, BYTE, PROC, VIRTUAL, DERIVED
CODE
SELF.IdentityPrime()
SELF.TimeStamp()
RETURN PARENT.PrimeAutoInc()
hjSql.TimeStamp PROCEDURE !, PROTECTED
nTimePos LONG, AUTO
nDatePos LONG, AUTO
tmp ANY
CODE
nTimePos = SELF.File{PROP:Fields}
nDatePos = nTimePos - 1
IF SELF.File{PROP:Type, nDatePos} = 'DATE' AND |
SELF.File{PROP:Name, nDatePos} = 'DATEMODIFIED_DATE' AND |
SELF.File{PROP:Type, nTimePos} = 'TIME' AND |
SELF.File{PROP:Name, nTimePos} = 'DATEMODIFIED_TIME'
tmp &= WHAT(SELF.Buffer, nDatePos)
tmp = TODAY()
tmp &= WHAT(SELF.Buffer, nTimePos)
tmp = CLOCK()
END
RETURN
hjSql.TryInsert PROCEDURE !, BYTE, PROC, VIRTUAL, DERIVED
bRetval BYTE, AUTO
CODE
SELF.IdentityPrime()
SELF.TimeStamp()
RETURN PARENT.TryInsert()
hjSql.TryPrimeAutoInc PROCEDURE !, BYTE, PROC, VIRTUAL, DERIVED
CODE
SELF.IdentityPrime()
SELF.TimeStamp()
RETURN PARENT.TryPrimeAutoInc()
hjSql.TryUpdate PROCEDURE !, BYTE, PROC, VIRTUAL, DERIVED
CODE
SELF.TimeStamp()
RETURN PARENT.TryUpdate()
hjSql.Update PROCEDURE !, BYTE, PROC, VIRTUAL, DERIVED
CODE
SELF.TimeStamp()
RETURN PARENT.Update()
!------------------------------------------------------------------------------
Printed May 4, 2024, 6:02 pm
This article has been viewed/printed 35110 times.
Google search
has resulted in 93 hits on this article since January 25, 2004.
|