Thursday, May 29, 2008

Database Workbench Beta

It's not often you see a new third-party development tool for SQL Anywhere come along. Here's one, and they're looking for beta testers...

From: "Martijn Tonies"
Newsgroups: news.3rd_party_products,
sybase.public.sqlanywhere.general
Subject: Betatesters wanted for Sybase Anywhere related product
Date: 9 May 2008 05:18:01 -0700

Dear reader,

We're looking for beta-testers for a product called Database Workbench Pro. It's a cross-database developer tool, currently supporting the database engines Microsoft SQL Server, Oracle, Firebird, MySQL, InterBase and NexusDB.

As we're expanding the product, we're adding Sybase Anywhere support.

For this version, we're looking for a group of about 5 beta-testers. Beta-testers will be subscribed to a Yahoogroups mailinglist - if this is unacceptable to you, please don't apply.

There's no payment involved for selected betatesters, but you will receive a free single-user license for Database Workbench Pro 3, including the Sybase Anywhere module and another database module of your choice.

We would like to have people who:

- would use this product on a daily basis, like they're using another Sybase Anywhere tool or Sybase Central now.
- work with larger databases, both volume and database object count
- are able to report bugs in a reasonable detailed manner
- are able to discuss new features or enhancements
- are able to regularly download updates

For more information on the current functionality of Database Workbench Pro, see:

www.upscene.com - company page
www.upscene.com/documentation/dbw3 - documentation

If you think you would be the right person for this, please send an email to m.tonies@upscene.com with:

- your name, company and function
- the kind of database application you are building
- an estimate of the object count and database size- a small piece of text in which you're telling something about yourself and why you would make a good beta-tester

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

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 (
"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:

Tuesday, May 20, 2008

'Tis the season of benchmarks

And the season kicked off May 13 with the Guinness record for World's Largest Data Warehouse.

"Sybase IQ has set a Guinness World Record by powering the world's largest data warehouse on a Sun SPARC Enterprise M9000 server. The winning combination: Sybase IQ analytics server, Sun Microsystems Data Warehouse Reference Architecture, and BMMsoft Server manages 1 Petabyte of raw data holding 6 trillion rows of transactional data. The solution enables more data to be stored in less space, searched and analyzed in less time, while consuming 91 percent less energy and generating less heat and carbon dioxide than conventional solutions." - excerpt from a "Sybase Partner News" email
Petabyte? What is a petabyte, anyway? Is it 1,000,000,000,000,000 bytes, as in quadrillion?

Or is it 1,125,899,906,842,624 bytes, as in 1000 terabytes?

I'm not sure I really care... at the moment, anyway... someday the difference might be important to me, when I go shopping for my first 1 PB drive (you don't think that will ever happen? hah! just wait!)

In the meantime, it's fun to watch happy people celebrate on youtube.

Wednesday, May 7, 2008

CSCONVERT To The Rescue!

Three under-appreciated tasks: Converting data from one application database to another, building a good application installation or "setup.exe", and dealing with different character sets.

In the world of application development these are often regarded as dull, uninteresting tasks. In management's eyes that means they are unimportant, and therefore easy, and therefore they get left to the last minute, or assigned to the intern, or both.

With, as they say, predictable results.

I am pretty good at the first task, converting data between systems, where "pretty good" means "lots of bitter experience over many years". In some cases a MobiLink setup is really a glorified data conversion exercise, especially when the databases being synchronized have vastly different schema.

On the second task, building a good setup, I can code my way out of a wet paper bag but not much more. What I do know is this: A good setup is hard to build, which may be why (until recent years) there have been so few good ones.

On dealing with character sets, code pages, locales and collations, I'm absolutely hopeless. I can read the documentation (heaven knows I've read it enough times), I just can't put the docs together with real world situations.

That's what this post is about: One teeny tiny real-world problem involving character sets. That I couldn't fix without help. Where "help" means an extended round of emails with my good friend Super Duper Expert.

Here's where it started: "French characters aren't being displayed properly. They look OK in the input XML file, but all messed up when the application displays them."

Here's what "OK" means...

And here's what "messed up" means...

The problem was this:

  • The SQL Anywhere database was created on Windows with the default character set called "cp1252". Or maybe that's a code page. Whatever. It's the default, and defaults are not something to be fiddled with. Especially not THIS default: I never fiddle with defaults I don't understand.

  • The input XML file was (apparently) created with a character set called "utf8".

  • The input utf8 characters were not being converted to the cp1252 character set, but just stored in the database as is. So "OK" becomes "messed up" when the data was displayed as if it was in the cp1252 character set.

  • The XML data was directly read by xp_read_file() inside a stored procedure, not passed across the client-server boundary from an application program. This means SQL Anywhere's automatic client-server character set translation feature didn't apply. Not even when I added CHARSET=UTF8 to the connection string... that was email number three. Or was it email number six?
The solution was to call SQL Anywhere's CSCONVERT() function to force the character set conversion to be performed inside the stored procedure:
SET @xml 
= CSCONVERT
( xp_read_file ( 'filespec-for-XML-data' ),
'cp1252',
'utf8' );

If that code looks familiar, it's from the earlier post OpenXML() Rocks!

Thursday, May 1, 2008

Pitching Sets

Here's a question and answer from this morning's email...

Q: Should we use SQL Anywhere web services or traditional application-level web services for production?

A: If your web service needs access to a SQL Anywhere database, there may be huge performance advantages to building it into the database. For example, if your web service reduces large amounts of database data into a small web service response, sending the large amounts across a client-server interface (web service as client, database as server) may be less efficient than having a builtin web service do the processing inside the server.

...especially if you take advantage of "set oriented" processing offered by SQL insert/update/select statements as opposed to "record oriented" fetch loops most development tools force you to use (yes, I know some tools hide the fetch loops, but as far as the database is concerned they are still happening).

SQL Anywhere web services are certainly ready for a production environment. The database engine has included an HTTP server for several years, and they have made a lot of performance and feature improvements to the web service feature.