Solving NULL values are recognized as text-values within dynamic queries in PostgresSQL

Posted by Eric Bartels

I’ve written a dynamic query which is finally executed via EXECUTE my_dynamic_query; The query contained a select list in which two columns should always be NULL.

-- ... stuff before
dynamic_select_cols := 'tbl1.foo, tbl1.bar, null AS theAlias1, null AS theAlias2, tbl2.foo2';
-- ... stuff after combining the dynamic queries
EXECUTE my_dynamic_query;

In my case the result of the query will be inserted in a temporary table in memory. The destination column for theAlias1 is of type INTEGER. When executing the query PostgreSQL gives me an error:

column "theAlias1" is of type integer but expression is of type text

So there is something wrong when the query is parsed, evaluated and executed.

Solving this was easy with the use of NULLIF. Zero equals zero so NULL is returned. Just as expected.

-- ... stuff before
dynamic_select_cols := 'tbl1.foo, tbl1.bar, NULLIF(0, 0) AS theAlias1, NULLIF(0, 0) AS theAlias2, tbl2.foo2';
-- ... stuff after combining the dynamic queries
EXECUTE my_dynamic_query;

Using a C# preprocessor directive for testing custom timer-jobs

Posted by Eric Bartels

If your are writing custom timer-jobs for your SharePoint solution it will happen that you write jobs which are only executed once a day or even less often. As a developer you cannot wait that long…

You could of course comment in and out (via ctrl + k, ctrl + c / ctrl + k, ctrl + u) the SPSchedule used by the timer-job during debug-mode. But this is error-prone.

When using a C# preprocessor directive you cannot forget to switch back to the “production schedule” as this is done automatically (and even with syntax-highlighting depending on the active configuration!) when selecting the “release build”.


#if (DEBUG)
var schedule = new SPMinuteSchedule();
schedule.BeginSecond = 1;
schedule.EndSecond = 59;
schedule.Interval = 5;
#else
var schedule = new SPDailySchedule();
schedule.BeginHour = 7;
schedule.EndHour = 7;
schedule.BeginMinute = 1;
schedule.EndMinute = 59;
#endif

This code tells the compiler to use the SPMinuteSchedule when compiling the “debug build” and to use the SPDailySchedule when compiling in “release build”. All automatically!

Configure the symfony-mailer (swiftmailer) during runtime

Posted by Eric Bartels

One of my current projects is a multi-client environment. Each client has its own smtp-settings for the mailings that are done within that application.

From static …

Symfony allows the configuration of the mailer in its factories.yml. There smtp-settings can be definied. But in this case this is impossible as every client has different smtp-settings (every client can use its own smtp-settings to handle mailings).

As I did not want to create an instance of the Swift_Mailer (and Swift_SmtpTransport) myself I decided to create my own mailer-class which is derived from the symfony one. So I get the full benefits of logging.

… to dynamic

class myDynamicMailer extends sfMailer
{
  public function __construct(sfEventDispatcher $dispatcher, $options)
  {
    // Load client based configuration
    $cfg = EmailConfiguration::getCurrent();
 
    if(!$cfg->isSmtpConfigured())
    {
      $options["class"] = "Swift_MailTransport";
    }
    else
    {
      // Update settings for the current client
      $options["transport"]["param"]["host"]      = $cfg->getHostname();
      $options["transport"]["param"]["port"]      = $cfg->getPort();
      $options["transport"]["param"]["username"]  = $cfg->getUsername();
      $options["transport"]["param"]["password"]  = $cfg->getDecryptedPassword();
    }
 
    parent::__construct($dispatcher, $options);
  }
}

Simply update factories.yml with the new class and the dynamic configuration is applied.

all:
  mailer:
    class: myDynamicMailer
    # Rest follows here

Meeting workspaces in SharePoint 2010 / Office 2010. A disappointment.

Posted by Eric Bartels

SharePoint 2010 RTM (and its Foundation) are available for awhile now. After installing SharePoint Foundation I immediately took a look into the meeting workspaces.

No improvements since the Beta! Not all lists support the new AJAX-Dialogs. The page-management (for multi-paged workspaces) is still the same as in 2007. Even some old (SP 2007) graphics appear. The handling is that awful, too. So the whole “area” of meeting-workspaces leaves with an “nothing happened here”-impression.

Additionally the meeting-workspace ribbon in Outlook 2010 is gone! You can get it back, but its not that preset as in 2007. This was a really nice feature in 2007. Outlook combined with meeting workspaces.

When taking all this together I’m really disappointed.
It comes to my mind that this whole “meeting-workspace-flow” will soon be removed from SharePoint/Outlook – No real improvements in SharePoint 2010 (haven’t tested building site-definitions from meeting-workspaces but I guess its still as hard as in 2007), ribbon in Office 2010 is gone per default and somewhat hidden.

WITH-Klausel

Posted by clausbajor

In der Praxis trifft man von Zeit zu Zeit auf aufgeblaehte SQL-Queries, die ausserordentlich kostenintensiven und langwierigen Ausfuehrungsplaenen folgen.
Zeigt eine weitergehende Analyse, dass die komplexe Abfrage eine oder mehrere Subqueries enthaelt, die mehrfach ausgefuehrt werden, dann besteht eine sehr
gute Chance durch Verwendung der sogenannten WITH-Klausel, die Performance der Abfrageausfuehrung erheblich zu steigern.

Im Kern wird die schnellere Ausfuehrung der Query dadurch realisiert, dass mittels der WITH-Klausel die Ergebnismenge der Subquery vorab materialisiert, mit einem Namen versehen und schliesslich in der Hauptquery abgegriffen wird.
Hier lohnt sich auch ein Vergleich und Gebrauch von "global temporary tables", die in Ihrer Funktions- und Wirkungsweise durchaus ähnlich sind:
Durch Entkopplung geeigneter Subqueries, die urspruenglich als Bestandteile einer komplexen Abfrage fungierten, wird dieses SQL-Statement vereinfacht.

WITH   SQ1 AS (SELECT...FROM...WHERE...),
       SQ2 AS (SELECT...FROM...WHERE...)
SELECT...
  FROM Q1, SQ1, SQ2 WHERE…

Interessanter Nebeneffekt:

Durch die Verwendung der WITH-Klausel wird anscheinend in einer Systemumgebung mit voreingestelltem CURSOR_SHARING=FORCE offensichtlich fuer die Query-Ausfuehrung diese systemweit gueltige Einstellung auf CURSOR_SHARING=EXACT zurueckgenommen (vergleichbar dem Einsatz des Hint /*+ cursor_sharing_exact */).

Zeigen die QEPs vorher noch eine allgemeine Ersetzung von Konstanten durch Parameter, so weisen die QEPs nachher die direkte Verwendung der Konstanten auf.

Somit orientiert sich der Optimizer auch grundsaetzlich an anderen Zugriffsstrategien.

Was in einer Umgebung mit problematischem Parseverhalten per Bindvariablen-Peeking einen "flüssigen" Anwendungsbetrieb ermöglichen kann, wird im Einzelfall einer komplexen Query unter Verwendung von Attributen mit stark ungleicher Werteverteilung zu relativ kostenintensiven QEPs führen.

Es empfiehlt sich dann für alle entsprechenden Konstanten/Parameter einen separaten und speziellen QEP zu erzwingen.

Vorraussetzung:

SQL-99 Standard bzw. Oracle9i Release 2

Links:

SQLStandards.html

to be continued….

Next Page »