SQL Back End and the Dictionary

questions
,
1

I am in the process of migrating our TPS files to MSSQL and I have a question about relationships
Is it better to have the relationships defined in the Dictionary or in the Database or both?
I am leaning toward the database, this would allow other applications other than Clarion to know how the tables are related.
Thoughts?

2

Always move as much logic as possible out of the DCT and Clarion code and into your database. While having relationships in the DCT is helpful in many ways (ie selecting tables for a browse etc), it should NOT replace what is defined in your database.

3

I tend towards both, but certainly in the backend where possible. Conversion from TPS sometimes makes that a little difficult, the more at the backend the better.

4

+1 I think definitely in the backend, and also useful to have in the dct for Clarion fuctionality

5

I generally have the backend manage relationships and the Dictionary supports this well with the ‘Server’ relationship Options. It makes sense to have the powerful SQL server and platform working for you and the templates handle messages back far better than earlier ABC releases.

Sometimes though with tables only stored for archive purposes and not generally accessed at all, you may feel it better to leave the job to your own code. I guess it is a decision very dependant on your local application.

You can also get the server to enforce required fields and that too works well with the templates. Occasionally you may have a field that is not always required but in circumstances can become so. Maybe once one field is filled you have to have an associated datetime stored too, as an example. These will then fall out of both the server and DCT and need managing in your windows.

You can also think about triggers executing stored SQL scripts. That can sometimes be better than doing it on the Clarion side, especially where your database is shared with other applications or you are integrating your programs with others in an enterprise-wide solution.

In summary having a good think about each item before committing will probably serve better than a single method.

Regards,

6

Thanks everyone for your input.

7

Hi William,

I try to keep all in Clarion. (using Capesoft Filemanager for automatic upgrading file changes). I have a turbosql procedure which can be called anywhere you like to count records and make views, triggers. Code is like this:

sql1number            PROCEDURE  (string p_scriptname,<p_beperk>) 

Scriptfile   FILE,DRIVER('ODBC','/TurboSQL=TRUE'),OWNER(GLO:DBOwner),PRE(SCR),BINDABLE,THREAD
kaart          RECORD
number               long
datum               date
               END
             END

loc:number   long
loc:datum   date
Scripttext  STRING(2000)

  CODE

  CASE upper(p_scriptname)
  OF 'NEXTRELATIENR' 
    Scripttext = 'SELECT GEN_ID("REL_RELATIENR",0) FROM RDB$DATABASE'       
  OF 'COUNTREL_CODE'      
     Scripttext = 'select count(1) from RELATIE where UPPER(TRIM(REL_CODE))=<39>'&upper(left(clip(p_beperk)))&'<39>'     
  OF 'GN_REL_RELATIENR_TRIGGER'
        Scripttext =  'CREATE OR ALTER TRIGGER GN_REL_RELATIENR FOR RELATIE '&|
                      'ACTIVE BEFORE INSERT POSITION 0 '&|
                      'AS '&|
                      'begin '&|
                      'if (New.RELATIENR = 0 or New.RELATIENR is NULL ) then '&|
                      'New.RELATIENR = GEN_ID("REL_RELATIENR",1);'&|
                      'end'
  OF 'REL_RELATIENR_GENERATOR'
         Scripttext = 'EXECUTE BLOCK AS BEGIN '&|
                      'if (not exists(select 1 from rdb$generators where rdb$generator_name = <39>REL_RELATIENR<39>)) then '&|
                      'execute statement <39>CREATE SEQUENCE REL_RELATIENR;<39>; '&|
                      'execute statement <39>ALTER SEQUENCE REL_RELATIENR RESTART WITH 0;<39>; '&|
                      'END'
  OF 'REL_CODE_TRIGGER'
        Scripttext =  'CREATE OR ALTER TRIGGER REL_CODE_RECHTS FOR RELATIE '&|
                      'ACTIVE BEFORE INSERT OR UPDATE POSITION 0 '&|
                      'AS '&|
                      'begin '&|
                      'if (substring(TRIM(NEW.REL_CODE) from 1 for 1) SIMILAR TO <39>[0-9]+<39>) then '&| 
                      'NEW.REL_CODE=lpad(NEW.REL_CODE,9); else NEW.REL_CODE=TRIM(NEW.REL_CODE);'&|
                      'end'
  ELSE
     message('Script '&p_scriptname&' not found','Test')   
  END !
  CLOSE(Scriptfile)
  SHARE(Scriptfile)

  IF ~ERRORCODE()
    Scriptfile{PROP:SQL}=Scripttext
    IF ERRORCODE()
      MESSAGE(CHOOSE(ERRORCODE()=90,FILEERROR(),ERROR(),'Error on prop:sql'),'')  
    END
    NEXT(Scriptfile)
    IF ERRORCODE()    
      IF upper(p_scriptname)='VOLGENDRELATIENR'      
         MESSAGE('Fout bij uitvoeren sql script '& clip(p_scriptname) & '|Bestandsfout= ' & FileError (),'FOUT',ICON:Exclamation)
      ELSE
            loc:number=0!   
      END      
    ELSE 
      loc:number=SCR:number 
      loc:datum=SCR:datum
    END
  ELSE
    MESSAGE('error: ' & FILEERROR()&'|||read installation or  email [email protected]', 'error',ICON:Exclamation )
  END
  CLOSE(Scriptfile)       
  CASE upper(p_scriptname)
    OF 'NEXTRELATIENR'      
        loc:number+=1
   END     
  RETURN(loc:number)
1 Like