SQLBlox

 Last Updated: 06/05/2003
SQLBlox is an API for producing SQL statements. When developers construct SQL statements there is usually lots of string + string + var + string type code. Even simple statements can be hard to read. SQLBlox is a very basic prototype of something that may be a better solution. SQLBlox works by obtaining SQL statement instances(select, insert, update, etc.) from a factory. From these statements you can set the things to query, insert, or update. For example, consider the following code fragment:
	SqlInsertStatement insert_stmt = sqlfactory.getInsertStatement("CUSTOMER");
	insert_stmt.setColumn("CID", CUSTOMER_ID); // customer's id number.
	insert_stmt.setColumn("CUSTOMERNAME", "ABC's Inc.");
	insert_stmt.setColumn("ADDRESS1", "1020 Rose St.");
	insert_stmt.setColumn("ADDRESS2", null, true);
	insert_stmt.setColumn("CITY", "San Jose");
	insert_stmt.setColumn("STATE", "CA");
	insert_stmt.setColumn("ZIP", "95125");
	insert_stmt.setColumn("DESCRIPTION", "A good customer.");
	insert_stmt.setColumn("TIMESTAMP", new Date(), formatter);
		
	System.out.println("INSERT 1 RESULT");
	System.out.println(insert_stmt.toSql());
	System.out.println("***");
	System.out.println();

This code would produce the following SQL statement.

        INSERT INTO CUSTOMER (CUSTOMERNAME, CID, CITY, DESCRIPTION, ZIP,
	STATE, ADDRESS2, ADDRESS1, TIMESTAMP) VALUES ('ABC''s Inc.', 1022,
	'San Jose', 'A good customer.', '95125', 'CA', NULL, '1020 Rose St.',
	{ts '2003-02-07 08:54:56'})

Those that have had some experience with writing database code might like dealing with the above insert statement better than the string + string type code. I think it's a little safer since it minimizes syntax related SQL errors. Also statements are reusable so it may be slightly more efficiant(remains to be determined).

I offer this as a prototype of something that may be useful. Please don't use it in your production code. There is a small unit test program that produces various statements and outputs similar to above. Please send me your comments. If people are interested in this idea we will take it further. Thanks!

Milton Smith brahma_bull_sj@yahoo.com