Useful Functions

Transact-SQL seems to be shy a few important functions that are useful for parsing irregular data. Sure, they include the isNull function to let you substitute something useful for a NULL value, but they don’t provide good negation functions for the opposite case. Here are a few new ones that provide some useful functionality and extend your ability to query against irregular data.

Here they are:

• IsEmpty(@argument) – Takes a string value and tests to see if it is null or contains an empty string. If it is not empty, it returns the argument, otherwise it returns NULL. Use this inside an ifNull() test on fields that may contain nulls or empty strings.
• IfEmpty(@argument, @default) – Similar to IsEmpty(), this one lets you pass in a default value to use if the argument is empty. It’s basically the same as IsNull(), except that it works with NULL and empty strings.
• NotEmpty(@argument, @default) – This is the negation case that I found missing. It returns the default value if the tested value contains something, otherwise an empty string. Great for things like zip-plus 4 on zip codes, allowing you to print the dash only if the plus 4 field has a value in it.

Limitations:

These functions were designed mainly for returning formatted text for reporting or rendering into HTML or XSLT stylesheets, so they assume string arguments. You will have to coerce your values into strings to use them, or write similar functions for other data types.

The source:

CREATE FUNCTION IsEmpty
(
@argument AS varchar(8000)
)
RETURNS varchar(8000)
BEGIN
if @argument is null return null
if len(@argument) = 0
begin
set @argument=null
end
return @argument
END

CREATE FUNCTION IfEmpty
(
@argument AS varchar(8000),
@default as varchar(8000)
)
RETURNS varchar(8000)
BEGIN
if dbo.isEmpty(@argument) is null
begin
return @default
end
return @argument
END

CREATE FUNCTION NotEmpty
(
@argument AS varchar(800),
@default as varchar(800)
)
RETURNS varchar(8000)
BEGIN
if dbo.isEmpty(@argument) is not null
begin
return @default
end
return ''
END

Project progress on SQL syntactic translation

My project to create a natural language business performance query tool is getting more complex with variations in resolving syntactic ambiguity. I have tried various UDF/other methods to best process these statements and am drawing conclusions I will reveal later taking into account IO/cpu usage as well as implementation difficulty for the business.

Impacts of a ‘have a go hero’

Recently I received the following ….. for those in the field, we all know the impacts of the business ‘have a go hero’ !!!

————————————————————————–

To: David dthorrington@gmail.com

I have a business user who tried his hand at writing his own SQL query for a report of project statistics (e.g. number of tasks, milestones, etc.). The query starts off declaring a temp table of 80+ columns. There are then almost 70 UPDATE statements to the temp table over almost 500 lines of code that each contain their own little set of business rules. It finishes with a SELECT * from the temp table.

Due to time constraints and ‘other factors’, this was rushed into production and now my team is stuck with supporting it. Performance is appalling, although thanks to some tidy up it’s fairly easy to read and understand (although the code smell is nasty).

What are some key areas we should be looking at to make this faster and follow good practice?

Is there such a thing as a developers honor code anymore ?

How did it all start? when did we give so called developers credit for a collage of code snippets stolen from the Internets vast variety of forums and support sites?. How do we search through the bullshit to find true achievement and understanding of our systems?

Reasons why plagiarism is unethical :

• You deny yourself the opportunity to learn and practice skills that may be needed in your future careers. You also deny yourself to opportunity to receive honest feedback on how to improve your skills and performance.
• You invite future employers to question your integrity and performance in general.
• You deprive another author due credit for his or her work.
• You show disrespect for your peers who have done their own work.

Bottom Line – If you lack the ability to create your own code, seek guidance and understanding from communities so that you not only increase your knowledge, but are then able to apply it appropriately.

To the guys starting out .. here’s some cheat sheets for reference :

 SQL Server

 MY SQL

 PostgreSQL

Guide to replicating SQL Server to PostgreSQL

There can be many reasons for wanting to replicate your data from a MS SQL Server installation to your PostgreSQL installation. For example, as a step in migration or to be able to use PostgreSQL features for data analysis while not having to touch existing clients working with MSSQL. For me in this case, I wanted to use tsearch2 to search some fulltext data, because the fulltext indexer in SQL Server really isn’t very good.

It turns out that SQL Server ships with replication functionality that can solve this problem with relatively little pain (depending on your schema of course), providing full transactional replication. It’s master/slave only, and SQL Server will be the master, but it’s still quite useful.

Here are the steps to do this for a simple example database – should work for more complex database as well of course. It expects you to set up a user named sqlrepl in the PostgreSQL database, that the replication system will use to connect with. Make sure that this user has permissions to connect from the SQL Server machine in pg_hba.conf.

• Make sure you have the PostgreSQL ODBC drivers installed on the SQL Server machine (I’m using version 8.01.02).
• Create the example databases:
  • In SQL Server:

CREATE DATABASE origin go USE origin go CREATE TABLE tab1( id int identity not null primary key, t varchar(128) not null) INSERT INTO tab1 (t) VALUES ('Test 1') INSERT INTO tab1 (t) VALUES ('Test 2')

• Then, in PostgreSQL:

CREATE DATABASE slave OWNER sqlrepl; \connect slave CREATE TABLE tab1(id int not null primary key, t varchar(128) not null); ALTER TABLE tab1 OWNER TO sqlrepl;

• Create a ODBC System Datasource on the SQL Server. This is done using the odbcad32 command. Make sure that you create a system datasource, and make sure you use the PostgreSQL ANSI driver (there are some problems with the UNICODE driver in the way SQL Server uses it)
• Start SQL Server Enterprise Manager. Create a new linked server. This is done by right-clicking the Linked Servers node under Security and picking New Linked Server. Enter the name of the linked server (in all uppercase, in our case PGSLAVE), and pick the driver Microsoft OLE DB Provider for ODBC Drivers. Note that you should not pick the PostgreSQL ODBC driver here. Finally, enter the name of the ODBC datasource just created. Make sure the link works by clicking the Tables node and verify that you can see the tables of you database.
• Configure the subscriber:
  • Right-click on the Replication node and select Configure publishing, subscribers, and distribution.
  • Select the tab Subscribers
  • Click New
  • Select OLE DB data source
  • Pick the linked server you created (PGSLAVE). Re-enter the login information.
  • Click OK and close all dialogs
• Create the publication:
  • Right-click Publication under Replication and select New publication
  • Select your database, click Next
  • Select Transactional publication, click Next
  • Uncheck SQL Server 2000 and check Heterogeneous data sources, click Next
  • Click Article Defaults
    • Open the Snapshot tab
    • Change name conflicts to Keep existing table unchanged. In some cases it will work with drop and recreate, but I prefer creating the tables manually to make sure there is no mixup with datatypes and such (considering MSSQL doesn’t really know about PostgreSQL datatypes)
    • Click OK
    • Put a checkbox on the tables to replicate (tab1 in this example). Click Next
    • You will get a warning about IDENTITY properties not being replicated to subscribers. This will happen if you have any IDENTITY columns in your table. In most cases, you can just ignore it. Click Next
    • Possibly modify description if you want to, click Next
    • Click next through the rest of the Wizard, and click Finish
  • Create the subscription:
    • Open properties for the publication
    • Open the Subscriptions tab
    • Click Push new
    • Select the created subscriber (PGSLAVE)
    • Click Next through the rest of the wizard, make sure to check the box for Start snapshot agent
• Now sit back and watch your MSSQL data first being bulk-loaded into PostgreSQL, and then transactionally replicated (you will see a couple of seconds delay after a commit, same as when you replicate between two SQL Servers).

Source : http://blog.hagander.net/archives/103-Replicating-from-MS-SQL-Server-to-PostgreSQL.html

Current work project

Working on answering the following question: “Can #SQL interpret a freehand business question and answer it?” … and if so should we strive to develop such apps which probably require using cartessian products ?