Connessione a file di testo utilizzando il linguaggio SQL

---

Un file di testo può essere interrogato via SQL come se si trattasse di una tabella di database.
Condizioni per poter utilizzare il linguaggio SQL direttamente sul file sono:

1. La prima riga del file dovrebbe contenere le intestazioni di colonna per poter assegnare un nome ad ogni campo della tabella. Se così non fosse o fossero presenti intestazioni non valide, allora verranno assegnati per default i nomi F1, F2, Fi, ...., Fn dove i è l'i-esima colonna del foglio.

2. Nel comando SQL, il nome del file (e non l'intero path) va racchiuso tra parentesi quadre quando contiene degli spazi.
Es. Il file Product List.csv sarà interrogabile come una tabella dal nome [Product List.csv]

---

# Esempio 1
# Recuperare la lista dei primi 50 clienti dal file "C:\Data\CustomersList.txt".
# I campi di ogni riga del file sono separati dal carattere punto e virgola (;)

Connect_Text { File = "C:\Data\CustomersList.txt",
               Delimiter = ";",
               ColumnHeaders = Yes }
Data_GetSQL { SQL = "SELECT TOP 50 * FROM CustomersList.txt ORDER BY Orders DESC" }
Connect_Close

---

# Esempio 2
# Importare nella Current data Matrix il contenuto del file "C:\Data\Orders.dat".
# Nel file NON è presente una intestazione e le quattro colonne hanno larghezza fissa:
#
# Colonna 1: Identificativo dell'ordine, testo, 14 caratteri
# Colonna 2: Data o ora dell'ordine, testo, 10 caratteri
# Colonna 3: Ragione sociale del cliente, testo, 36 caratteri # Colonna 4: Importo dell'ordine, numero intero, 10 caratteri.

Connect_Text {
  File          = "C:\Data\Orders.dat,
  ColumnHeaders = No,
  SchemaColumns = "ORD_ID|Text|14|DATE|Text|10|CUSTOMER_NAME|Text|36|EURO|Long|10" }

Data_GetSQL {
   SQL = "SELECT CUSTOMER_NAME, SUM(EURO) AS TOTALS
          FROM Orders.dat
          GROUP BY CUSTOMER_NAME" }

Connect_Close

Connessione a file di testo utilizzando l'action Data_GetTextFile

---

Un file di testo con campi separati da un carattere delimitatore può essere inserito in blocco nella Current data Matrix utilizzando l'action Data_GetTextFile.
Il vantaggio rispetto all'utilizzo di Connect_Text, è che in una stessa colonna possono trovarsi più tipi di dato eterogenei; con Data_GetTextFile, tuttavia, il tipo di dato è scelto automaticamente dall'Engine è non può essere forzato dall'utente. Non sarà possibile, ad esempio, interpretare una serie di cifre come stringa e, viceversa, una serie di cifre preceduti o seguiti da spazi può essere intrepretato soltanto come stringa e non come intero o decimale.

# Esempio 1
# Recuperare la lista dei primi 50 clienti dal file "C:\Data\CustomersList.txt".
# I campi di ogni riga del file sono separati dal carattere punto e virgola (;)

Data_GetTextFile { File = "C:\Data\CustomersList.txt",
                   Delimiter = ";",
                   ColumnHeaders = Yes }

Data_DeleteRow { Row = "51:" }

---

# Esempio 2
# Importare nella Current data Matrix il contenuto del file "C:\Data\CustomersList.txt".
# La prima colonna deve essere interpretata come stringa e non come numero
Declare r Integer

Data_GetTextFile { File = "C:\Data\CustomersList.txt",
                   Delimiter = ";",
                   ColumnHeaders = Yes }

For r = 1 To @DataRows
    # Per ciascuna riga delle Current Data Matrix, trasforma il contenuto
    # numerico nella prima colonna in formato stringa
    Data_Update { Row = r, Column = 1, Value = ToString( DataValue(r, 1)) }
Next

 

Concatenazione di più file di testo utilizzando l'action Data_GetTextFile

---

# Esempio
# Concatena tutti i file .csv presenti nella cartella indicata dall'utente
# Si presuppone che i file abbiano intestazione e campi separati da virgola (,).
# Dichiara una variabile per contenere il nome del file corrente
Declare CurrentFile String

# L'utente deve specificare dove sono i file di testo
Dialog_SelectFolder { Caption = "Indicare la cartella contenente i file .csv..." }

# Trova tutti i file del tipo *.csv nella cartella indicata
Set CurrentFile = FileList (@SelectedFolder + "\*.csv")

# Per ogni file csv trovato...
Do While CurrentFile <> ""

   If @DataRows = 0 Then

      # Per il primo file importa anche le intestazioni di colonna
      Data_GetTextFileSQL { File = @SelectedFolder + "\" + CurrentFile,
                            Delimiter = ",",
                            ColumnHeaders = Yes }
   Else

      # I file successivi vanno accodati senza intestazioni di colonna
      Data_GetTextFileSQL { File = @SelectedFolder + "\" + CurrentFile,
                            Delimiter = ",",
                            ColumnHeaders = No,
                            Append = Yes }
  End If

  # Passa al file successivo
  Set CurrentFile = FileList ("") 

Loop

 

Connessione a file di MS Excel utilizzando il linguaggio SQL

---

- La prima riga di ogni foglio dovrebbe contenere le intestazioni di colonna per poter assegnare un nome ad ogni campo della tabella. Se così non fosse o fossero presenti intestazioni non valide, allora verranno assegnati per Default i nomi F1, F2, Fi, ...., Fn dove i è l'i-esima colonna del foglio.

- Nel comando SQL, il nome di ogni foglio va sempre seguito dal simbolo $ e racchiuso tra parentesi quadre .
Es. Il foglio ProductList sarà interrogabile come una tabella dal nome [ProductList$].

# Esempio 1
# Recuperare la lista dei primi 50 clienti dal foglio Customers di una cartella Excel.
Connect_Excel { File = "C:\Data\MarketingList.xls" }
Data_GetSQL { SQL = "SELECT TOP 50 * FROM [Customers$] ORDER BY Orders DESC" }
Connect_Close

---

# Esempio 2
# Recuperare la lista dei primi 50 clienti in Francia e dei primi 25 in Spagna
# Nel file Excel ad ogni stato è riservato un foglio con l'elenco dei clienti.
Connect_Excel { File = "C:\Data\InternationalCustomers.xls" }

Data_GetSQL { SQL = "SELECT TOP 50 * FROM [France$]
                     UNION ALL
                     SELECT TOP 25 * FROM [Spain$]" }
Connect_Close

---

# Esempio 3
# Relazionare i dati nello sheet Orders e nello sheet Details
# riguardanti un ipotetico ordine n.1446.
Connect_Excel { File = "C:\Data\Orders_2001.xls" }

Data_GetSQL { SQL = "SELECT
                            [Orders$].OrderID,
                            [Orders$].Date,
                            [Details$].ProductID,
                     FROM   [Orders$], [Details$]
                     WHERE  [Orders$].ID = [Details$].ID
                     AND    [Orders$].ID = 1446" }
Connect_Close

 

Connessione a file di MS Excel utilizzando l'action Data_GetRange

---

# Esempio
# Importare nella Current Data Matrix i dati nelle colonne da A ad F
# dello sheet Orders_Spain e dello sheet Orders_Italy del file MarketingData.xls.
# Si suppone che i due sheet abbiano un layout identico, ci sia una riga
# di intestazione dei dati e ci siano al massimo 1000 righe per sheet.

# Dal primo sheet: recuperare anche le intestazioni di colonna
Data_GetRange { File   = "C:\Data\MarketingData.xls",
                Sheet  = "Orders_Spain",
                Range  = "A1:F1001",
                ColumnHeaders = Yes }
# Dal secondo sheet: accodare i dati dalla seconda riga (saltando l'intestazione)
Data_GetRange { File   = "C:\Data\MarketingData.xls",
                Sheet  = "Orders_Italy",
                Range  = "A2:F1001",
                ColumnHeaders = No,
                Append = Yes }
# Rimuovere le righe che non contengono informazione
Data_FilterRow { Filter = "Not Null" }

 

INTERROGAZIONE DI SORGENTI DATI

Evitare l'uso di Stored Procedures parametrizzando comandi SQL

---

Molto spesso le estrazioni complesse di dati da database sono realizzate non con comandi SQL, ma con la creazione di Stored Procedures a cui l'utente deve specificare pochi semplici parametri di input.
I database Miscrosoft mettono a disposizione queste funzionalità (in MS Access le Stored Procedures che restituiscono dati in output sono definite Query).
Tuttavia non in tutti i database o le sorgenti di dati sono definibili Stored Procedures che restituiscono un insieme di record in output; se è vero in MS SQL Server o MS Access (in quest'ultimo vengono chiamate Query) non è vero, ad esempio, in database Oracle.
Inoltre non sempre gli utenti hanno sufficienti privilegi per creare o eseguire Stored Procedures su DB remoti.
Si vedrà ora come interrogazioni SQL anche molto complesse possono essere semplicemente e dinamicamente paramettrizzate dall'Engine (o dall'utente, con l'uso di dialog) per poi essere eseguite una o più volte sul database. La procedura è semplice:

1)	Comporre e testare il comando SQL complesso con il tool di interrogazione nativo del DB (MS Query Analyzer, TOAD, PL/SQL Developer, ...)
2)	Sostituire ogni riferimento a tabelle, campi, valori, indici che si vogliono parametrizzare con delle etichette arbitrarie (esempio: **TABLENAME, [[FIRSTDATE]], ^^LASTDATE, #ORDER_ID) e salvare il comando su un file di testo .sql o, semplicemente, in una variabile stringa dello script.
3)	Comporre lo script di report .rsl in cui i parametri per l'esecuzione vengono definiti dinamicamente o richiesti all'utente con dei dialog interattivi.
4)	Utilizzando la funzione ReadTextFile, caricare il contenuto del file .sql in una variabile stringa e, con la funzione Replace sostituire ogni etichetta definita al suo interno con il corrispondente valore definito dallo script o dall'utente.
5)	Eseguire il comando SQL. Per lanciare lo stesso comando con diversi parametri è sufficiente ripercorrere il punto 4 assegnando diversi valori alle etichette.

Il concetto della sostituzione etichetta/valore è ovviamente applicabile anche se il comando SQL non è salvato su un file esterno ma è dichiarato in una variabile stringa all'interno dello script. La prima ipotesi tuttavia è preferibile quando i comandi SQL da eseguire sono numerosi e molto complessi.

Si supponga di dover eseguire molteplici volte su un database Oracle un comando SQL simile al seguente, in cui però possono variare la data, il nome del cliente e, a seconda della data richiesta, anche la tabella da interrogare:

SELECT   Customer_Name,          Order_Date,          Order_Class,          SUM (Order_Value) AS OrderSUM,          COUNT (Order_ID) AS OrderCOUNT FROM     TBL_Customers, TBL_Order2004 WHERE    TBL_Customers.CustID = TBL_Order2004.CustID AND      Customer_Name = 'Consolidated Holdings' AND      Order_Date = TODATE('2004-06-01', 'YYYY-MM-DD') GROUP BY Customer_Name,          Order_Date,          Order_Class

Verificata la correttezza del comando e dell'output restituito, si proceda parametrizzando il testo SQL con opportune etichette (evidenziate in **ROSSO**) e si salvi il comando in un file di testo, ad esempio in Cust_Orders.sql:

File Cust_Orders.sql

SELECT   Customer_Name,          Order_Date,          Order_Class,          SUM (Order_Value) AS OrderSUM,          COUNT (Order_ID) AS OrderCOUNT FROM     TBL_Customers, TBL_Order**YEAR** WHERE    TBL_Customers.CustID = TBL_Order**YEAR**.CustID AND      Customer_Name = '**CUSTOMER**' AND      Order_Date = TODATE('**DAY**', 'YYYY-MM-DD') GROUP BY Customer_Name,          Order_Date,          Order_Class

Si noti che è stato parametrizzato anche il nome di una tabella, cioè una stessa interrogazione può essere eseguita su diversi oggetti di database. Questo è uno degli aspetti più interessanti dei comandi SQL definiti dinamicamente.
Ora rimangono solo da definire le istruzioni opportune nello script repSL.

# Esempio
Declare SQLCmd String
...
# Interazione con l'utente per la valorizzazione dei parametri
# Richiede la ragione sociale del cliente
Dialog_InputString { Caption = "Selecting customer..." }
...
# Richiede la data di interesse
Dialog_SelectDate { Caption = "Selecting date...", Format = "YYYY-MM-DD" }
...
# Il contenuto del file .sql viene caricato nella variabile utente SQLCmd
Set SQLCmd = GetTextFile ("C:\Report\SQL\Cust_Orders.sql", Yes)
# Tutte le etichette nella stringa SQLCmd vengono sostituite con i rispettivi
# valori specificati dall'utente nei dialog.
Set SQLCmd = Replace (SQLCmd, "**YEAR**", ToString(Year(@SelectedDate)), No)
Set SQLCmd = Replace (SQLCmd, "**CUSTOMER**", @InputString, No)
Set SQLCmd = Replace (SQLCmd, "**DAY**", @SelectedDate, No)

# Apre la connessione verso il database Oracle
Connect_ODBC { DSN = "MYORACLEDB", Login = "Guest", Password = "" }

# Estrae i dati dal database usando il comando SQL appena definito
Data_GetSQL { SQL = SQLCmd, Timeout = 0 }
...

INTERAZIONE CON L'UTENTE

Realizzazione di pratiche procedure guidate con repSL

---

Il linguaggio repSL permette la visualizzazione di dialog personalizzabili tramite i quali l'utente può interagire con il motore di reportistica. Queste funzionalità, unite alle istruzioni If...Then...Else...End If e GoTo, consentono di realizzare dei Wizard (cioè procedure guidate) per la creazione di un documento.
Il concetto alla base in un Wizard è schematizzato qui sotto:

Ogni dialog consente di impostare uno o più opzioni o parametri che serviranno alla realizzazione del report.

In ciascuno di essi devono essere definiti i pulsanti che consentono di procedere in avanti (passando alla visualizzazione del dialog successivo), all'indietro (tornando al dialog precedente) oppure di abbandonare la procedura guidata e disattivare repSL Engine.
Generalmente i tre pulsanti vengono proposti con le intestazioni Prev, Next e Cancel utilizzando il parametro Buttons configurabile nelle action che richiamano un dialog. Il primo dialog, quelli intermedi e l'ultimo di solito mostrano, rispettivamente:

# Dialog iniziale
Buttons = "Next >> | Cancel"
# Dialog intermedio
Buttons = "<< Prev | Next >> | Cancel"
# Dialog finale
Buttons = "<< Prev | Cancel"

Nel momento in cui un dialog viene chiuso, lo script dovrà considerare, sfruttando la variabile di sistema @PressedButton, il pulsante che è stato premuto e, tramite blocchi di istruzioni If...Then...Else...End If decidere quali operazioni eseguire successivamente.
Per muoversi avanti e indietro nella visualizzazione dei dialog con l'istruzione GoTo è necessario definire una label nelle righe precedenti ogni action per la visualizzazione dei dialog.

# Esempio
# Il codice mostra come costruire un Wizard costituito da 5 dialog
# di cui i tre centrali sono utilizzati per configurare il report

ShowDialog1:
Dialog_Message { Caption = "Welcome",
                 Text    = "This will show you how create Wizards!",
                 Buttons = "Next >> | Cancel" }

If @PressedButton = 2 Then
    # Se viene premuto Cancel l'esecuzione viene interrotta
    End
End If

######################################################
# ...istruzioni tra il primo e il secondo Dialog     #
######################################################

ShowDialog2:
Dialog_SelectDateInterval { Caption = "1st Dialog",
                            Text    = "Select a time interval ",
                            Clock   = Yes,
                            Buttons = "<< Prev | Next >> | Cancel" }

If @PressedButton = 3 Then
    # Se viene premuto Cancel l'esecuzione viene interrotta
    End
ElseIf @PressedButton = 1 Then
    # Se viene premuto Prev l'esecuzione torna indietro al dialog precedente
    GoTo ShowDialog1
End If

######################################################
# ...istruzioni tra il secondo e il terzo Dialog     #
######################################################

ShowDialog3:
Dialog_SelectOption { Caption = "2nd Dialog",
                      Options = " First Option | Second Option",
                      Buttons = "<< Prev | Next >> | Cancel" }

If @PressedButton = 3 Then
    # Se viene premuto Cancel l'esecuzione viene interrotta
    End
ElseIf @PressedButton = 1 Then
    # Se viene premuto Prev l'esecuzione torna indietro al dialog precedente
    GoTo ShowDialog2
End If

######################################################
# ...istruzioni tra il terzo e l'ultimo Dialog       #
######################################################

Dialog_Message { Caption = "3rd Dialog",
                 Text    = "All is ready for execution!",
                 Buttons = "<< Prev | Cancel" }

If @PressedButton = 2 Then
    # Se viene premuto Cancel l'esecuzione viene interrotta
    End
ElseIf @PressedButton = 1 Then
    # Se viene premuto Prev l'esecuzione torna indietro al dialog precedente
    GoTo ShowDialog3
End If

######################################################
# ...istruzioni per la generazione del report...     #
######################################################

 

ASPETTI GENERALI

Rappresentare i nomi dei mesi e dei giorni in lingua inglese

---