Example: OUTPUT TO From VB, Part 2

Part 1 of this article explained how to run SQL Anywhere's SELECT and OUTPUT TO statements via dbisql.exe from inside a Visual Basic application.

This article describes how to use the same technique to run much larger SQL scripts, scripts that might not fit on one command line...

...but first, here's a recap of Part 1:

---

Question: How do I write the result set from a SQL Anywhere 10 SELECT statement to a local tab-delimited text file from a Visual Basic 2008 application running on a client workstation?

Answer...

• Use the VB Shell() function to launch the dbisql.exe utility in batch mode on the client workstation,
  

• have it connect to to SQL Anywhere 10 database server across the network,
  

• run the SELECT SQL statement against the database and then
  

• use the Interactive SQL OUTPUT TO statement to write the result set to a local text file on the workstation.
  

Here's the code:

Public Class Form1
    Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Shell("""C:\Program Files\SQL Anywhere 10\win32\dbisql.exe"" " _
                      & "-c ""ENG=ddd10;DBN=ddd10;UID=dba;PWD=sql"" " _
                      & "SELECT * FROM t1 ORDER BY pkey; " _
                      & "OUTPUT TO 'C:\data\select_output.txt' DELIMITED BY '\X09'")
    End Sub
End Class

---

Part 2: Run a SQL Script Via Interactive SQL

SQL Anywhere's dbisql utility let's you put SQL statements inside text files and then load-and-execute those scripts via the READ statement.

The READ statement...

• can appear on the dbisql.exe command line,
  

• it can appear inside a SQL script file to load and execute another script file,
  

• and it can even pass parameters to the script.
  

Those last two features are great for building large hierarchies of SQL scripts that are customized at runtime, but this article only talks about running one single script from the command line.

Here's what the VB code looks like when the SELECT and OUTPUT TO statements have been moved into a script file:

Public Class Form1
    Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Shell("""C:\Program Files\SQL Anywhere 10\win32\dbisql.exe"" " _
                      & "-c ""ENG=ddd10;DBN=ddd10;UID=dba;PWD=sql"" " _
                      & "READ ENCODING Cp1252 ""C:\data\select_output.sql""")
    End Sub
End Class

Here's what the C:\data\select_output.sql file looks like (OK, it's not a "much larger SQL script" like was promised earlier, but it COULD be larger, with no limit):

SELECT * FROM t1 ORDER BY pkey; 
OUTPUT TO 'C:\data\select_output.txt' DELIMITED BY '\X09';

The whole business of the double-doublequotes and the "&" concatenation operator is explained in Part 1 of this article. For the record, the original dbisql.exe command line has been changed from this (CAUTION: these are one-line commands that have been split to avoid display problems on different browsers)

"C:\Program Files\SQL Anywhere 10\win32\dbisql.exe" 
-c "ENG=ddd10;DBN=ddd10;UID=dba;PWD=sql" 
SELECT * FROM t1 ORDER BY pkey; OUTPUT TO 'C:\data\select_output.txt' DELIMITED BY '\X09';

to this

"C:\Program Files\SQL Anywhere 10\win32\dbisql.exe" 
-c "ENG=ddd10;DBN=ddd10;UID=dba;PWD=sql" 
READ ENCODING Cp1252 "C:\data\select_output.sql"

and then massaged to fit inside the VB call to Shell().


The ENCODING Cp1252 is semantic sugar: On Windows computers it is sometimes necessary to specify the "code page" to get READ to work properly. This is like waving a dead chicken over the keyboard: it never hurts, it sometimes helps, and very few people care understand why.

Tip: Like the OUTPUT TO statement, the READ statement is executed by the Interactive SQL (dbisql.exe) utility, not by the SQL Anywhere database server. This can lead to confusion, like "Why can't I put an OUTPUT TO statement inside a stored procedure? Answer: Because stored procedures are executed inside the SQL Anywhere server, and the server doesn't understand OUTPUT TO."

The fog lifts once you realize that, from the server's point of view, dbisql.exe is just another steenking client application, and that OUTPUT TO and READ statements are parsed and executed on the client... the server never sees them.

---