Lezione
6 - Operazioni su Database tramite oggetti Data Esempio
Vogliamo realizzare un Programma di utilità che ci permetta di vedere la struttura di un Database Access e lo chiameremo MdbUti.
Nella finestra di progettazione abbiamo creato un nuovo progetto, chiamiamo il Form MdbUti, scegliamo come font Arial 9 (per schermo 800x600), inseriamo un Menù con le voci Apri Database, Scegli Tabella, Scegli Query, Esci.
Aggiungiamo un Common Dialog, due ListBox, delle Label ed il nostro Form avrà questo aspetto:
|
Dim FormH As Single, FormW As Single Dim I As Integer, J As Integer, RCode As Integer Dim XSave As Single, YSave As Single, XSkip As Single ' dichiarazione Oggetti Dim MyDb As Database, MyTb As Recordset ' SwDb e SwTb servono per memorizzare ' se vi sono oggetti aperti Dim SwDb As Integer, SwTb As Integer Dim TabTipo(15) As String |
TabTipo ci serve per memorizzare le descrizioni dei Tipo Campo e la inizializziamo nella routine Form_Load, così come SwDb e SwTb (=0) e FormH e FormW (dimensioni del Form).
Il primo evento che dobbiamo gestire è la scelta di menù Apri; dobbiamo nascondere controlli, disabilitare voci di menù usare il Common Dialog, aprire il Database e memorizzare le Tables e le Queries contenute. Vediamo il dettaglio:
|
Private Sub Apri_Click() Dim PassW As String ' nascondi controlli e disabilita voci menù List1.Visible = False List2.Visible = False Me.Cls ' useremo il metodo Print per scrivere sul Form Label1(0).Visible = False Label1(1).Visible = False ApriTab.Enabled = False ApriQuery.Enabled = False ' se vi è già un Db aperto lo chiude If SwDb > 0 Then MyDb.Close CommonDialog1.ShowOpen Me.Refresh If Len(CommonDialog1.FileTitle) = 0 Then Exit Sub If Len(Dir(CommonDialog1.FileName, vbNormal)) = 0 Then Exit Sub End If ' è stato scelto il Database Screen.MousePointer = 11 PassW = "" On Error GoTo ErrOpen ApriMdb: If Len(PassW) = 0 Then Set MyDb = OpenDatabase(CommonDialog1.FileName, False, True) Else Set MyDb = OpenDatabase(CommonDialog1.FileName, False, True, "; pwd=" & PassW) End If On Error GoTo 0 'disabilita gestione errori SwDb = 1 'segnala che c'è un Database aperto SwTb = 0 List1.Clear 'conterrà gli ogetti Table List2.Clear 'conterrà gli ogetti Query For I = 0 To MyDb.TableDefs.Count - 1 ' le Table il cui nome comincia con MSys sono di Sistema ' e non possono essere aperte, pena un errore di Run-time If Left$(MyDb.TableDefs(I).Name, 4) <> "MSys" Then _ List1.AddItem MyDb.TableDefs(I).Name Next I If MyDb.QueryDefs.Count > 0 Then For I = 0 To MyDb.QueryDefs.Count - 1 List2.AddItem MyDb.QueryDefs(I).Name Next I ApriQuery.Enabled = True End If Label1(0).Caption = MyDb.Name Label1(0).Visible = True ApriTab.Enabled = True Screen.MousePointer = 0 Exit Sub ErrOpen: Screen.MousePointer = 0 If Err.Number = 3031 Then ' protetto con Password PassW = "" PassW = InputBox("Immettere Password", "DATABASE PROTETTO") PassW = Trim$(PassW) Err.Clear On Error GoTo 0 Screen.MousePointer = 11 Resume ApriMdb Else MsgBox ("Errore " & Format$(Err.Number) & Chr$(13) & Err.Description) Err.Clear On Error GoTo 0 Exit Sub End If End Sub |
Vediamo di commentare i punti più importanti: innanzitutto If SwDb > 0 Then MyDb.Close, perché se c'è un Db aperto lo dobbiamo chiudere; poi, se è stato scelto un file .mdb, Set MyDb = OpenDatabase(CommonDialog1.FileName, False, True) per aprirlo; ma se è protetto con password otterremo l'errore 3031 e qui entra in gioco la routine di gestione errori che ci avverte del fatto e ci chiede la password, e poi ritorna alla open (ApriMdb:). Questa volta anziché la prima viene eseguita la 2a open Set MyDb = OpenDatabase(CommonDialog1.FileName, False, True, "; pwd=" & PassW) e possiamo aprire il Db.
Non ci resta che memorizzare nei due ListBox l'elenco delle Tables e delle Queries del DB: utilizziamo la proprietà Name delle collections TableDefs e QueryDefs (di quest'ultime controlliamo anche l'esistenza o meno tramite la proprietà Count). Abilitiamo anche ApriTab e, se esistono, ApriQuery.
Nelle due routine ApriTab_Click e ApriQuery_Click dobbiamo solo rendere visibile il ListBox del caso ed invisibile l'altro; tutta la gestione avverrà nelle routine di Click dei ListBox.
Poiché la routine List1_Click è piuttosto lunga a causa della gestione degli allineamenti con il metodo Print non la riporto, ma mi limito ad evidenziare i punti che ci interessano.
|
Set MyTb = MyDb.OpenRecordset(List1.List(List1.ListIndex), dbOpenTable) Label1(1).Caption = MyTb.Name MyTb.RecordCount 'numero records presenti; MyTb.DateCreated 'data di creazione: MyTb.LastUpdated 'data ultimo aggiornamento. 'Esaminiamo gli Indici: se il Count è > 0 scorriamo la collection For I = 0 To MyTb.Indexes.Count - 1 MyTb.Indexes(I).Name 'nome dell'Indice IndEspr = "" For J = 0 To MyTb.Indexes(I).Fields.Count - 1 'vediamo i campi dell'indice IndEspr = IndEspr & MyTb.Indexes(I).Fields(J).Name & ", " 'tramite la proprietà Name Next J '........ '...... otteniamo l'espressione dell'indice.. Next I 'Esaminiamo i campi della tabella: For I = 0 To MyTb.Fields.Count - 1 '.... posizionamento per Print Me.Print MyTb.Fields(I).Name '.... posizionamento per Print Me.Print TabTipo(MyTb.Fields(I).Type) 'tipo del campo '.... se Tipo 11 o 12 (OLE o Memo) abbiamo finito Me.Print Format$(MyTb.Fields(I).Size) 'altrimenti Lunghezza del campo Next I '........ '...... adattiamo l'altezza del Form (ecco percé avevamo salvato le dimensioni originali) |
Per quanto riguarda invece la Query è più semplice: dell'oggetto MyDb.QueryDefs(List2.ListIndex) utilizzeremo le seguenti proprietà:
L'unica avvertenza è di scorrere SQL eliminando eventuali Carriage Return presenti prima di stamparla e di gestire la larghezza massima stampabile spezzandola se necessario in più righe e ridimensionando il Form.
Per Esci_Click dobbiamo solo gestire eventuali oggetti aperti:
|
Private Sub Esci_Click() ' prima di uscire chiudere gli ogetti aperti If SwTb = 1 Then MyTb.Close If SwDb = 1 Then MyDb.Close Unload Me End Sub |
Il nostro programma è terminato: i files di progetto Vb6 e i 2 Db di prova sono contenuti in Esempio3.zip (Download - 15kb); la password di Provaprot.mdb è Pippo Approfondimenti sulle SQL
Creazione di nuovi campi dagli esistenti nella clausola SELECT
Abbiamo già trattato l'argomento nella Lezione 5; tenete presente che i campi utilizzati per creare quello nuovo possono anche essere su Tabelle diverse collegate con JOIN, se vogliamo gli importi scontati degli Ordini e lo sconto è su Clienti scriviamo
SELECT elenco dei campi, Ordini.Importo * (1 - Clienti.Sconto / 100) As Scontato FROM Ordini INNER JOIN Clienti ON Ordini.CodCli = Clienti.CodiceID ...
Utilizzo di campi numerici o date nella clausola WHERE
Occorre tener presente che la SQL utilizza i formati numerici USA indipendentemente dal linguaggio impostato nel Sistema; pertanto se vogliamo i records con Lunghezza > 1,15 dobbiamo scrivere ... WHERE Lunghezza > 1.15 ....
Per i campi data inoltre il valore deve essere delimitato con #; se vogliamo i records con DataAgg >= data immessa in Text1 dobbiamo scrivere
"... WHERE DatAgg >= #" & Format$(CDate(Text1.Text), "mm/dd/yyyy") & "# ......"
Esecuzione di Query di comando
Vengono in genere utilizzate tutte le volte che si vuole aggiornare o cancellare gruppi di records. In genere si ricorre alla creazione di un oggetto QueryDef temporaneo a cui si applica poi il metodo Execute.
La sintassi della creazione è:
Set oggettoquerydef = oggettoDb.CreateQueryDef ("", testosql)
Il primo parametro (che abbiamo posto a "") è il Nome da assegnare alla query; se presente la query verrà aggiunta in modo permanente al Database (se il nome esiste già avremo un errore), se stringa lunghezza 0 l'oggetto è temporaneo.
La sintassi della esecuzione è:
oggettoquerydef.Execute dbFailOnError
Occorrerà attivare la gestione errori prima della esecuzione e gestire cosa fare se si verifica un errore.
E' consigliabile utilizzare il parametro dbFailOnError perchè se si verifica un errore annulla (dice Microsoft, ma io non ho provato) l'intera operazione, cioè non avremo un aggiornamento a mezzo.
E' possibile anche ricorrere all'altra sintassi del metodo Execute
oggettoDb.Execute stringaSQL, dbFailOnError
Ad esempio se vogliamo aggiungere alla Tabella Giornale del nostro Db i records della Tabella PrimaNota con Contabilizzato = 1 ed eliminarli quindi da PrimaNota possiamo farlo molto semplicemente con 2 query di comando, ma dobbiamo pensare a come eliminarli da Giornale se va male la query di Delete su PrimaNota per ripristinare la situazione corretta.
Sia per provare MdbUti che per fare esercizi SQL potete utilizzare i database forniti con Visual Basic, Biblio.mdb e Nwind.mdb; se dovete provare query di comando è meglio che lo facciate su delle copie (errare humanun est).