MSDE Altre istruzioni per l'uso (parte 3) - Creare/Eliminare Tipi dati utente, Default e Messaggi di sistema
a cura di Sabrina Cosolo (requisiti: Livello intemedio di conoscenza generale)

Tipi dati utente, Default e Messaggi di sistema
Sono alcuni degli strumenti che SQL Server mette a disposizione dell'ammministratore del database e dei programmatori per rendere loro più semplice

Tipi dati utente:
I Tipi di dati definiti dall'utente, non sono un vero e proprio modo per definire dei nuovi tipi di dato quanto un modo per organizzare la forma dei dati ripetuti più volte all'interno del nostro database per fare in modo di non doverci ricordare quale è la definizione corretta e non rischiare di trovarci nei guai quando aggiungiamo tabelle su progetti non recenti.

Facciamo un esempio concreto per rendere meno nebulose le cose:
In fase di progettazione del nostro database, stabiliamo che:

• Tutte le chiavi primarie devono essere di tipo Intero Long (Int SQL);
• Tutte le descrizioni delle tabelle di uso comune da inserire nelle combo box devono essere Stringhe da 50 caratteri;
• Tutti i codici alfanumerici da usare ad esempio per il magazzino o clienti e fornitori per la ricerca veloce devono essere stringhe da 25 caratteri;
• La Partita IVA deve essere una stringa di 20 caratteri (per compatibilità CEE);
• Il codice fiscale deve essere una stringa di 16 caratteri.

Utilizzando il linguaggio SQL possiamo definire a livello di Database tutti questi tipi di dati assegnando a ciascuno un nome significativo, possiamo sostituire poi i tipi di dati utente alle definizioni con l'uso di tipi standard per costruire i campi all'interno delle tabelle che inseriremo nel nostro Database. Questo ci permetterà una organizzazione dei dati più pulita e di non doverci ricordare a memoria le lunghezze di determinati campi anche se andremo a definire nuove tabelle magari un anno dopo la prima stesura del database.

Vediamo quale è la sintassi per effettuare questo tipo di operazione, ricordiamo che, come per la creazione del database, l'utente che effettua queste operazioni deve avere diritti di amministrazione sul server dati, perciò utilizzare l'utente SA o un utente che appartiene al gruppo Builtin/administrators se vi collegate con trusted connection.

  EXEC sp_addtype NomeDatoUtente,'TipoDatoStandard', 'ComportamentoNull'

La definizione di un tipo di dato utente viene effettuata utilizzando sp_addtype, una delle stored procedure di sistema fornite all'interno di SQL Server; i parametri da passare alla procedura sono:

• NomeDatoUtente = Identificatore univoco per il nostro nuovo tipo di dato
• TipoDatoStandard = Stringa (compresa fra singoli apici) che descrive il nuovo tipo di dato derivandolo dai tipi di dati standard, Es. 'int' oppure 'nvarchar(25)' etc.
• ComportamentoNull = Se il tipo di dato ammette o meno il NULL può assumere i valori 'Null' o 'Not Null' e può essere comunque ridefinito a livello di tabella.

Di seguito la sintassi per la definizione dei Tipi di dati per il nostro Database di Prova:

  EXEC sp_addtype UDtID,'int', 'not null'
  EXEC sp_addtype UDtDesRic,'nvarchar(25)', 'not null'
  EXEC sp_addtype UDtDesShort,'nvarchar(50)', 'null'
  EXEC sp_addtype UDtDesLong,'nvarchar(256)', 'null'
  EXEC sp_addtype UDtTel,'nvarchar(20)', 'null'
  EXEC sp_addtype UDtCodFis,'nvarchar(16)', 'null'
  EXEC sp_addtype UDtParIva,'nvarchar(20)', 'null'
  EXEC sp_addtype UDtISBN,'nvarchar(16)', 'null'

Così come possono essere definiti, i Tipi di dati utente possono essere cancellati. Ricordo che i Tipi di dati utente vengono definiti all'interno del singolo Database e non a livello di Server, perciò, se costruiamo due database per due diverse applicazioni, dobbiamo ridefinire i dati utente in ciascun database.

La sintassi del comando di cancellazione è la seguente:

  EXEC sp_droptype NomeDatoUtente

NomeDatoUtente è l'identificatore univoco utilizzato per la definizione del nostro tipo di dato.

Ricordo che non è possibile eliminare un tipo di dato utente che fosse in uso, perciò, se genero un tipo di dato utente, creo una tabella in cui un campo usa questo tipo di dato e poi cerco di cancellarlo, si verificherà un errore e il tipo di dato non verrà cancellato.

Di seguito la sintassi per la cancellazione dei tipi di dati da noi definiti:

  EXEC sp_droptype UDtID
  EXEC sp_droptype UDtDesRic
  EXEC sp_droptype UDtDesShort
  EXEC sp_droptype UDtDesLong
  EXEC sp_droptype UDtTel
  EXEC sp_droptype UDtCodFis
  EXEC sp_droptype UDtParIva
  EXEC sp_droptype UDtISBN

Default:
Tutti i campi delle tabelle di un Database possono essere impostati per assumere un valore predefinito (default) in caso non vengano riempiti dall'utente, soprattutto nel caso in cui una tabella contenga dati di pertinenza di uffici diversi, ad esempio un ufficio che inserisce i record e compila solo i dati anagrafici e un ufficio amministrazione che aggiunge tutti i dati di tipo fiscale o commerciale.

Per aiutarci in questo compito, SQL Server ci mette a disposizione due strumenti, il primo utilizzabile con il comando di INSERT TABLE o ALTER TABLE in cui è possibile definire il valore di default per un campo; il secondo, quello che tratteremo tra poco, è quello di definire degli oggetti Default a livello di Database ed assegnarli poi ai campi che ne avessero necessità.

Perché usare gli oggetti default anziché la definizione di default in dichiarazione del campo? Perché Microsoft dice: gli oggetti Default assegnati ai campi sono più veloci.
Perché?
Perché sono predefiniti e quindi precompilati, non devono essere effettuati controlli e preparazioni ogni volta che un record viene generato, quindi vanno più veloci.
Quali sono i casi in cui un valore di default può servirmi? Ad esempio:

• Se ho un campo numerico e voglio che quando non è inizializzato sia posto a 0
• Se ho un campo di tipo boolean (bit per SQL Server con valore 0 1) e voglio impostargli un valore predefinito a Vero o Falso.
• Se ho un campo Data di creazione o Data ultima modifica per non dovermi ricordare a programma di assegnar loro un valore quando inserisco il record e limitarmi all'aggiornamento della data modifica quando effettuo il salvataggio del record.

Questi sono alcuni dei casi in cui è utile avere un valore di Default, ma ve ne potrebbero essere molti altri.

Vediamo ora la sintassi SQL per poter generare un Default, per l'assegnazione ai campi delle tabelle vi rimando alla pagina dedicata all'assegnazione dei Vincoli alle tabelle (ma non c'è fretta).

  CREATE DEFAULT NomeDefault AS Valore

La definizione di un Default viene effettuata con il comando SQL CREATE DEFAULT, cui si passano due parametri:

• NomeDefault = Nome che vogliamo assegnare all'oggetto Default per poterlo poi utilizzare
• Valore = Valore che il Default assume

Come si può notare, il Default non ha un tipo perché il tipo viene stabilito dal Valore che gli facciamo assumere ed è generico, perciò il default UDFZero = 0 potrà essere indifferentemente assegnato a qualsiasi campo numerico (Bit, Int, Float, Numeric etc.).

Di seguito la sintassi della creazione dei Default per il nostro Database di prova:

  CREATE DEFAULT [UDFZero] AS 0
  CREATE DEFAULT [UDFUno] AS 1
  CREATE DEFAULT [UDFAdesso] AS current_timestamp
  CREATE DEFAULT [UDFNote] AS ' '

Il solo Default che necessiti di una spiegazione è quello chiamato UDfAdesso che assume un valore curioso ovvero current_timestamp, che è in realtà il valore restituito da una funzione di sistema di SQL Server che legge data e ora corrente.

Come si cancella un oggetto Default?
Premesso che un oggetto Default non può essere cancellato se è in uso da parte di una qualsiasi delle tabelle di un database, la sintassi per la cancellazione è la seguente:

  DROP DEFAULT [NomeDefault]

Come possiamo notare, anche questa procedura di eliminazione è semplice, Nomedefault è il nome dell'oggetto Default che vogliamo cancellare.

Di seguito la sintassi per l'eliminazione degli oggetti Default che abbiamo generato per il nostro Database di Test.

  DROP DEFAULT [UDFZero]
  DROP DEFAULT [UDFUno]
  DROP DEFAULT [UDFAdesso]
  DROP DEFAULT [UDFNote]

Come i Tipi di Dati Utente anche i Default sono definiti a livello di Database, perciò è necessario ridefinirli per ogni Database che noi creiamo all'interno di SQL Server, quindi lo script che eseguiremo usando un ADODB Command oppure OSQL.EXE deve essere eseguito sul Database su cui lavoriamo (nel nostro caso Musicbox).

Messaggi di Sistema:

SQL Server, ha all'interno del Database Master oltre alla definizione di tutti i Database che sono inseriti al suo interno, di tutti gli User e di tutti i dati cosiddetti di Sistema, una tabella che è chiamata SysMessages, in questa tabella sono registrati tutti i messaggi di avviso e di errore del server in Inglese ed eventualmente nella lingua locale se la versione di SQL Server installata è localizzata.

In questa tabella è possibile aggiungere Messaggi di avviso e di errore personalizzati per le nostre applicazioni. Ogni messaggio è identificato da un codice di errore numerico. Microsoft consiglia di inserire i messaggi Utente dal codice 50000 in su.

Perché definire dei messaggi di Errore a livello di Server SQL? Per poterli gestire esattamente come i codici di errore di sistema quindi con gli eventi. Ma soprattutto per poter sollevare errori a livello di Stored Procedure dando al programma chiamante tutte le informazioni relative ai motivi dell'errore.

Un messaggio di errore viene inserito nella tabella di sistema utilizzando una delle Stored Procedure di sistema: sp_addmessage. I messaggi sono inseriti a livello di Server e devono quindi essere definiti una volta sola e possono essere utilizzati con tutti i database.

Vediamo la sintassi della Stored procedure:

  EXEC sp_addmessage NumErrore , Livello , Messaggio, Lingua, Log, Sostituisci

Descriviamo i parametri della procedura:

• NumErrore = Codice da assegnare all'errore, intero long da 50000 in su
• Livello = Livello di severità dell'errore; può assumere un valore da 1 a 18, maggiore è il valore più grave è l'errore. Gli User error solitamente hanno severità 10, il livello 10 è solitamente un livello di AVVISO e quindi adatto alla gestione di errori procedurali; è sconsigliabile usare livelli di errore più alti se non per procedure di tipo critico a livello di Database.
• Messaggio = Testo del messaggio, in cui possono essere inseriti dei segnaposto corrispondenti a parametri passati alla funzione di "RaisError" nelle stored procedure per evidenziare in modo dinamico dove si sono verificati gli errori, in quale procedura, database, quale utente, etc.
• Lingua = Lingua del messaggio, se omessa il messaggio viene identificato dalla lingua di default impostata sul server, il valore che noi utilizziamo come standard è 'us_english'; è possibile visualizzare la lingua dei messaggi installati, facendo una select sulla tabella Sysmessages del DB Master.
• Log = Può assumere il valore 'TRUE' o 'FALSE' a seconda che vogliamo oppure no che l'evento gestito con questo messaggio di errore venga riportato o meno sull' Event Log di Windows 2000/XP/NT.
• Sostituisci = 'replace': se volessimo usare un messaggio per sostituirne uno esistente, per esempio se abbiamo un server in inglese e volessimo sostituire i messaggi traducendoli. (Sconsiglio vivamente la cosa anche se è fattibile).

Vediamo quali sono alcuni messaggi che potrebbero esserci utili nella nostra attività di programmazione e come li abbiamo definiti.

  exec sp_addmessage 100001
    , 10
    ,'Parametri obbligatori mancanti %s Procedura %s! utente %s'
    , us_english
    , true
  exec sp_addmessage 100002
    , 10
    , 'Il campo univoco %s esiste nella tabella %s! utente %s'
    , us_english
    , true
  exec sp_addmessage 100003
    , 10
    , 'Il campo %s della tabella %s non contiene dati validi! utente %s'
    , us_english
    , true
  exec sp_addmessage 100004
    , 10
    , 'Riferimento a tabella %s Nella tabella %s Non Trovato! utente %s'
    , us_english
    , true
  exec sp_addmessage 100005
    , 10
    , 'Il record da modificare %s Nella tabella %s Non Esiste! utente %s'
    , us_english
    , true
  exec sp_addmessage 100006
    , 10
    , 'Il record %s Nella tabella %s Non Deve essere cancellato! utente %s'
    , us_english
    , true

Come potete vedere, il messaggio contiene una serie di %s al suo interno, ognuno di essi corrisponde ad una stringa che verrà passata come parametro alla procedura di errore e ci permetterà di dare maggiori informazioni all'utente o al programma che gestisce l'errore.
Ad esempio, il messaggio 100001 ci permette di verificare se ad una Stored Procedure non è stato passato un parametro obbligatorio e indicare quale è il nome del parametro. Oppure, se costruiamo una Stored Procedure per validare ed inserire i dati in una tabella, possiamo verificare che un campo con vincolo di UNICITA' non risponde a tale vincolo e gestire quindi l'errore a livello di programma. Allo stesso modo possiamo inserire controlli di validità per valore o, come nel messaggio 100006, impedire la cancellazione di un record che sappiamo non deve essere eliminato. Un esempio di uso dei codici di errore può essere il seguente:

  create procedure O4uSpAddUser  @UserName O4uDtDesRic = NULL
                               , @IDDipe O4uDtID = NULL
                               , @SqlUser O4uDtDesRic = NULL
                               , @SQLPassword O4uDtDesRic = NULL
                               , @SQLTrusted bit = 0
                               , @DataIniz datetime = NULL
                               , @DataFine datetime = NULL
                               , @UserInse O4uDtDesRic = NULL

  as
    declare @utente varchar(255)
    declare @IDUserInse O4uDtID 
    set @utente = user_name() 
    set @IDUserInse = 1
    if(@UserInse is null )
      begin
        select @IDUserInse = iduser from O4uTbUsers where UserName = '4U'
      end
    else
      begin
        select @IDUserInse = iduser from O4uTbUsers where UserName = @UserInse
      end
    if( @Username is null )
      begin
        -- effettuo il raiserror 
        RaisError( 100001, 10, 1, 'UserName', 'O4uSpAddUser', @utente ) with log 
        -- with log forza la registrazione nel log dell' event viewer
        return
      end 
    if exists (select * from O4uTbUsers where UserName = @Username)
      begin
        -- effettuo il raiserror 
        RaisError( 100002, 10, 1, @UserName, 'O4uTbUsers', @utente ) with log 
        -- with log forza la registrazione nel log dell' event viewer
        return
      end 
 
    begin tran
      insert into O4uTbUsers 
            ( UserName
            , Password 
            , DataPwd
            , IDDipe 
            , SqlUser
            , SQLPassword
            , SQLTrusted
            , DataIniz
            , DataFine
            , IdUserInse
            , DataInse
            , IdUserModi
            , DataModi)
        values
            ( @UserName
            , '=s'
            , current_timestamp 
            , @IDDipe
            , @SqlUser
            , @SQLPassword
            , @SQLTrusted
            , @DataIniz
            , @DataFine
            , @IdUserInse
            , current_timestamp
            , @IdUserinse
            , current_timestamp)
    commit tran

In questa stored procedure per l'inserimento dei dati in una tabella abbiamo predisposto il controllo relativo al campo obbligatorio e univoco @UserName utilizzando due dei messaggi di errore inseriti.

La funzione utilizzata per generare un evento di errore RaisError ha la seguente sintassi:

  RaisError( N_Err, Level, State, MsgString1, MsgString2, ..., MsgStringN ) with log

Dove:

• N_Err = Numero errore
• Level = Livello di errore ( 10 = User error )
• State = Codice di stato arbitrario tra 1 e 255 default 1
• Msgstring1 - MsgStringN = Stringhe parametro per il messaggio di errore
• With Log = Inserire questo comando se vogliamo inserire l'errore nel log di sistema di Windows.

Così come possono essere generati, i messaggi di sistema possono anche essere cancellati, utilizzando una apposita stored procedure, sp_dropmessage, opposta a quella di creazione; consiglio gli utenti, soprattutto se inesperti, di fare molta attenzione nell'uso di questa procedura, in quanto potete cancellare anche i messaggi di sistema se indicate un numero di messaggio ad essi corrispondente. Come si dice: A buon intenditor, poche parole...
Vi ricordo che anche questa procedura va eseguita a livello di Server e non di singolo database e quindi collegandosi al DB Master naturalmente con un utente che abbia diritti di System Administrator.
Questa è la sintassi:

  EXEC sp_dropmessage NumErrore , Lingua

Descriviamo i parametri della procedura:

• NumErrore = Codice dell'errore da cancellare, intero long da 50000 in su
• Lingua = Lingua del messaggio, se omessa il messaggio viene identificato dalla lingua di default impostata sul server, il valore che noi utilizziamo come standard è 'us_english' è possibile visualizzare la lingua dei messaggi installati, facendo una select sulla tabella Sysmessages del DB Master.

Vediamo come cancellare i messaggi precedentemente impostati.

  EXEC sp_dropmessage 100001 , us_english
  EXEC sp_dropmessage 100002 , us_english
  EXEC sp_dropmessage 100003 , us_english
  EXEC sp_dropmessage 100004 , us_english
  EXEC sp_dropmessage 100005 , us_english
  EXEC sp_dropmessage 100006 , us_english

Anche per questi comandi SQL si può utilizzare OSQL.EXE (vedi, anche per i parametri).

La sintassi per chiamare un comando SQL usando OSQL è la seguente:

  osql -S Localhost -U SA -P as -i creaduosql.sql

Un possibile file di input da utilizzare per realizzare uno degli esempi, creauduosql.sql, è un file di testo contenente il comando da eseguire che è leggermente diverso da quello sopra descritto, riporto la sintassi per il crea dati Utente:

  USE MusicBox
  GO
 
  EXEC sp_addtype UDtID,'int', 'not null'
  EXEC sp_addtype UDtDesRic,'nvarchar(25)', 'not null'
  EXEC sp_addtype UDtDesShort,'nvarchar(50)', 'null'
  EXEC sp_addtype UDtDesLong,'nvarchar(256)', 'null'
  EXEC sp_addtype UDtTel,'nvarchar(20)', 'null'
  EXEC sp_addtype UDtCodFis,'nvarchar(16)', 'null'
  EXEC sp_addtype UDtParIva,'nvarchar(20)', 'null'
  EXEC sp_addtype UDtISBN,'nvarchar(16)', 'null'
  GO
 
  QUIT

Il comando USE MUSICBOX serve a dire al server che dobbiamo lavorare sul Database Musicbox (database di esempio creato nel precedente articolo); il comando GO esegue i comandi richiesti, il comando QUIT esce da OSQL.EXE.

La sintassi da usare con OSQL è la seguente:

  USE Musicbox
  GO 
 
  script da eseguire
 
  GO
 
  QUIT

Questa sintassi è valida per tutti gli script generati in questa pagina, tranne i due script relativi ai messaggi di sistema, per i quali nella prima riga va inserito il comando USE MASTER, in quanto devono essere definiti a livello di Server e non per singolo database.

<<< Creare/Eliminare un Database

Creare/Eliminare Tabelle >>>