Executing BEGIN Blocks

In a comment on OpenXML() Rocks! Abhishek asks:

"I still have not figured out if it _has_to_be a procedure or can it be a sql query also. I am using Interactive SQL (SQL Anywhere 10) and it requires stuff like BEGIN, END etc. Please reply (at your blog) if there is a way it can be done in a SQL statement also."

The short answer is yes, it can be done. A stored procedure is just a glorified BEGIN block that's stored in the database.

The long answer is yes, if your client application development environment supports some mechanism to send an arbitrary string of SQL commands to the database, then you can execute a BEGIN block.

In the case of the OpenXML example, a BEGIN block is necessary because of the local variable DECLARE. The BEGIN tells the server to execute the entire block as one atomic operation.

In the case of Interactive SQL (dbisql), a BEGIN block is sent to the server as one single SQL string instead of executing each command separately. In other words, the BEGIN block is not specific to Interactive SQL, but Interactive SQL does have to handle it as one unit.

Aside: Interactive SQL does handle some commands itself, like INPUT and OUTPUT. Those commands can't be coded inside a stored procedure because the server doesn't recognize them. For the same reason, INPUT and OUTPUT can't be coded inside a BEGIN block because Interactive SQL sends the whole block to the server as a unit.

You can do the same thing in Java (for example) by using the JDBC Connection.Statement.execute method to send a BEGIN block to the server. Here is a simple "Hello, World!" example:

import java.sql.*; 
public class ExecuteBeginBlock {
  public static void main( String args[] ) {
    try {
      DriverManager.registerDriver ( 
        ( Driver ) Class.forName (
          "ianywhere.ml.jdbcodbc.jdbc3.IDriver" )
        .newInstance() );
      Connection conn = DriverManager.getConnection (
        "jdbc:ianywhere:driver=SQL Anywhere 10;"
        + "ENG=ddd10;DBN=ddd10;UID=dba;PWD=sql" );

      String sql
        = "BEGIN "
        + "DECLARE @hello VARCHAR ( 100 ); "
        + "SET @hello = 'Hello, World!'; "
        + "MESSAGE STRING ( CURRENT TIMESTAMP, "
        +    "' ', @hello ) TO CONSOLE; "
        + "END";

      Statement stmtSql = conn.createStatement();
      stmtSql.execute ( sql );
    } // try

    catch ( SQLException e ) {
      Integer errorCode;
      errorCode = new Integer ( e.getErrorCode() );
      System.out.println ( "SQLState:  " 
         + e.getSQLState() );
      System.out.println ( "ErrorCode: "  
         + errorCode.toString()  );
      System.out.println ( "Message:   "  
         + e.getMessage() );
      System.out.println ( "Stack trace..." );
      e.printStackTrace();
    }  
    catch ( Exception e ) {
      System.out.println ( "Error: "  
         + e.getMessage() );
      e.printStackTrace();
    }
  } // main
} // class ExecuteBeginBlock

Here's a Windows command file to compile ExecuteBeginBlock:

   C:\j2sdk1.4.0_04\bin\javac.exe ExecuteBeginBlock.java

Here are the commands to execute it:

   SET CLASSPATH=.;%SQLANY10%\java\jodbc.jar
   C:\j2sdk1.4.0_04\bin\java.exe ExecuteBeginBlock

Here's what the "Hello, World!" message looks like on the SQL Anywhere console window: