Monday, May 26, 2008

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 (
"" )
.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!'; "
+ "' ', @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..." );
catch ( Exception e ) {
System.out.println ( "Error: "
+ e.getMessage() );
} // main
} // class ExecuteBeginBlock
Here's a Windows command file to compile ExecuteBeginBlock:
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:


Abhishek said...

Thanks once again for the detailed reply. I was not expecting an entire post for this one. You are super helpful. Sorry if my questions appeared silly/ basic but I had to ask them as I am new to this area. Currently I have got the OpenXML() stuff working in C# and it is executing much faster too.

I saw at your other posts that you have been voted as the most helpful blogger in Sybase community. If that kind of voting happens again, please let me know and I will be more than happy to vote for you.

(abhishekpandey at yahoo dot com)

Breck Carter said...

There are no silly questions, and one person's "basic" is another person's show-stopping roadblock. Personally, I like "basic" questions because (1) on many occasions other people have answered MY basic questions and they continue to do so, and (2) it is satisfying to me when I actually know the answer to a question :)

FWIW your confusion about ISQL's treatment of BEGIN blocks is VERY common... it took me *years* to realize the difference between SQL commands and ISQL commands (yeah, years, I'm not kidding... don't ask me how long it took to figure out how MobiLink works :)

The difference between you and all the other people with the same question is simple: they are shy, they don't ask. You ask... it is important to ask questions. Asking is good.

Voting happens once a year in the spring, and the invitation to vote appears in the Sybase forums (currently on the NNTP newsgroups, but they may be moving to the web at some point). Vote for me, get free drinks at Techwave... the offer stands! Look for the old fat guy on the electric scooter :)