DBTable.RunQuery

SVB Example

Creating and analyzing a streaming DB connection:

Sub Main

    'Create a streaming DB connector
    Dim streamingDB As New DBTable
    'Connect it to the dataset "baseball". Normally, you would connect to a remote
    'dataset or database, but for simplicity we will use a local spreadsheet in this example.
    'Note that we are using the Statistica OLE DB provider to connect to a STA file.
    streamingDB.ConnectionString = "Provider=STATISTICA.StaOLEDB.1;Data Source=" & Path & "\examples\datasets\baseball.sta" & ";Integrated Security=SSPI;"
    'Specify what we want to query. In this case, we only SELECT the columns "YEAR", "WIN", and "RUNS".
    'Because "baseball" is the name of the dataset, that will be the name of the database in the SQL command.
    'Next, we request only winning records (i.e., greater than or equal to .500) by using a WHERE statement.
    'Finally, we sort the RUNS column from smallest to largest using the ORDER BY statement.
    streamingDB.QueryString = "SELECT YEAR, WIN, RUNS FROM 'baseball'" & " WHERE WIN >= .5 ORDER BY RUNS ASC"
    'We will set asynchronous to false, which will tell the system to wait for
    'all data to be fetched before proceeding
    streamingDB.AsyncQuery = False
    'Specify to run the query on the server, with a read-only, forward cursor.
    'Again, for simplicity we are using a local spreadsheet in this example,
    'but for connections to data on a server these options will be optimal.
    streamingDB.CursorLocation = scServerSideCursor
    streamingDB.CursorType = scForwardOnlyCursor
    streamingDB.LockType = scLockReadOnly
    'Perform the query and show the results
    streamingDB.RunQuery
    streamingDB.Visible = True

    'Perform a by-group, descriptive statistics on the data
    Dim newanalysis As Analysis
    Set newanalysis = Analysis(scBasicStatistics, streamingDB)

    'Basic Statistics and Tables
    Dim oAD1 As STABasicStatistics.BasStartup
    Set oAD1 = newanalysis.Dialog
    oAD1.Statistics = scBasDescriptives

    newanalysis.Run
    'The year will be our "By" (grouping) variable
    newanalysis.ByGroupEnabled = True
    newanalysis.ByGroupVariables = "1"

    'Descriptive Statistics
    Dim oAD2 As STABasicStatistics.BasDescriptiveStatistics
    Set oAD2 = newanalysis.Dialog
    'Analyze the runs by year
    oAD2.Variables = "3"

    newanalysis.RouteOutput(oAD2.Summary).Visible = True

End Sub