Example: OUTPUT TO From VB

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?

Wrong Answer: Use the UNLOAD TO statement - that writes the file onto the computer where the SQL Anywhere server is running, not the computer where the VB application is running. You could mess around with \\UNC\file specifications, but chances are you'll never get it to work... especially if the SQL Anywhere server is running as a service and doesn't have permission to do file I/O across the network.

Another Wrong Answer: Use UNLOAD INTO CLIENT FILE statement - nice try, but no cigar! The question said "SQL Anywhere 10" and the cross-border version of UNLOAD wasn't introduced until Version 11.

Right Answer: Well, one right answer, one that works with SQL Anywhere 10, and 11, and 12...

• 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.
  

That's a whole lotta words to describe one line of code in VB:

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_output1.txt' DELIMITED BY '\X09'")
    End Sub
End Class

...but ya gotta admit, it's one UGLY line of code, and it looks even worse in Visual Studio 2008 which doesn't wrap long lines of code (nor should it):


OK, so how about we make it "one statement" instead of "one line", with some line breaks to make it readable:

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

Here's how the two calls to Shell() were constructed:

1. First, a Windows command was written and tested using a *.BAT file:
   

   "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';
   

2. Then, the command was turned into a Visual Basic string literal by wrapping it in a pair of "doublequotes":
   

   ""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';"
   

3. but that's wasn't right, because Visual Basic would choke on the single doublequotes inside the string, so those nested doublequotes had to be doubled-up like this:
   

   """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_output1.txt' DELIMITED BY '\X09'"
   

4. Then, the string was wrapped in a call to Shell():
   

   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_output1.txt' DELIMITED BY '\X09'")
   

5. That executes OK, but it's hard to read, so the string was broken up into 4 lines:
   

   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_output1.txt' DELIMITED BY '\X09'")
   

6. but that didn't work because VB needs a space and an underscore " _" to break a statement onto two lines, like this:
   

   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_output1.txt' DELIMITED BY '\X09'")
   

7. but that STILL didn't work because VB doesn't let you break a one string literal across four lines, so one separate string literal per line was required, like this:
   

   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_output1.txt' DELIMITED BY '\X09'")
   

8. but (again) that STILL didn't work because Shell() needed the four string literals to be concatenated into one string argument, using the VB concatenation operator "&" like this:
   

   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_output1.txt' DELIMITED BY '\X09'")
   

9. ...and that works again (whew!)
   

But HOW does it work?

Here's how:

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

• The VB Shell() function call starting on line 3 needs at least one argument, called "Pathname" in the docs: Name of the program to execute, together with any required arguments and command-line switches. Pathname can also include the drive and the directory path or folder. (There are three other optional arguments described in the docs, all of which look interesting... for another day.)
  

• The string "C:\Program Files\SQL Anywhere 10\win32\dbisql.exe" specifies exactly where to find the SQL Anywhere 10 utility program dbisql.exe on the workstation. If you trust the PATH, you can code just use "dbisql.exe" or "dbisql" or even leave off the "quotes". In the code shown here, the "quotes" are necessary because of the spaces in the path specification, and the full path is specified because, well, it's been many years since the Windows PATH environment variable was stable and trustworthy.
  

• The connection string on line 4 specifies how dbisql is to connect to the SQL Anywhere database. In this example, it's a simple connection to a local database, -c "ENG=ddd10;DBN=ddd10;UID=dba;PWD=sql", but it could easily include a LINKS parameter to point across the network to another computer.
  

• The SELECT statement on line 5 shows how dbisql can be run in batch mode: just code your SQL statement right on the command line, and dbisql will execute it without opening up the GUI.
  

• The Interactive SQL OUTPUT TO statement on line 6 shows how you can code more than one statement on the dbisql command line if you separate them with semicolons.
  

• The SELECT statement is sent to the database, and SQL Anywhere returns the result set to dbisql. Then, dbisql runs the OUTPUT TO statement locally (inside dbisql, not on the SQL Anywhere server) to format and write the result set to a tab-delimited text file... which is what the DELIMITED BY '\X09' is for.
  

---