I was working on a generic data drill down control which has a filtering process to eliminate data based on a date argument. Since I also wanted this date value for other purposes (expressions) on the datawindow I decided to include it within the result set. There are a variety of ways to do the same thing but this is what I fixated on while figuring out the control’s functionality. Initially my SQL was something like this (MS SQL Server 2008):

...
,IsNull(part_name, 'No Part') AS part_name
,:adt_date AS svc_date
...

Everything is fine but during my testing I encountered the message: “Select Error: Column lists do not match.”

Hmmm…

Running a SQL trace gave this result for the query:

...
,IsNull(part_name, 'No Part') AS part_name
,NULL AS svc_date
...

So my date argument in this case was null. I could code in PowerBuilder to ensure the paramenter is always populated with a date but I preferred to following:

...
,IsNull(part_name, 'No Part') AS part_name
,IsNull(:adt_date, Getdate()) AS svc_date
...

In C# (I’m using Visual Studio 2008) create a class as indicated.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Net.Mail;
using System.Net.Mime;

namespace icalEmail
{
    public class StoredProcedures
    {
        [SqlProcedure]
        public static void SendiCal(DateTime startDateTime, DateTime endDateTime, string emailSubject,
           string userEmailAddress, string cnAttendee, string methodType, DateTime createdOn, string notes, string UID, string location, string seqNo, string smtpserver, int smtpport)
        {
            // create the iCal string in the proper format
            string iCal = CreateiCalFormat(startDateTime, endDateTime, emailSubject, userEmailAddress,
            cnAttendee, methodType, createdOn, UID, notes, location, seqNo);
            // create the MIME protocol Content-Type 
            var calendarType = new ContentType("text/calendar;method=REQUEST; charset=UTF-8");
            calendarType.Parameters.Add("method", "REQUEST");

            AlternateView caledarView = AlternateView.CreateAlternateViewFromString(iCal, calendarType);
            caledarView.TransferEncoding = TransferEncoding.SevenBit;
            // connect to smtpserver
            var client = new SmtpClient(smtpserver, smtpport);
            // create the email
            var mailMesage = new MailMessage();
            mailMesage.From = new MailAddress("donotreply@email.com");
            mailMesage.To.Add(new MailAddress(userEmailAddress));
            mailMesage.Subject = emailSubject;
            mailMesage.Body = iCal;
            mailMesage.AlternateViews.Add(caledarView);
            client.Send(mailMesage);
        }

        private static string _dateFormat = "yyyyMMddTHHmmssZ";
        private static string CreateiCalFormat(DateTime startDateTime, DateTime endDateTime,
        string emailSubject, string userEmailAddress, string cnAttendee, string methodType, DateTime createdOn, string UID,
        string notes, string location, string seqNo)
        {
            string iCal =

            "BEGIN:VCALENDAR" +

            "\nPRODID:-//SampleApp//AoT//EN" +

            "\nVERSION:2.0" +
            /// REQUEST for add or update, CANCEL for cancellation
            "\nMETHOD:" + methodType +

            "\nBEGIN:VEVENT" +

            "\nORGANIZER:CN='Matt'" +

            //"\nORGANIZER:MAILTO:matt.balent@anvil-of-time.com" +

            "\nATTENDEE;CN='" + cnAttendee + "'" +

            ";ROLE=REQ-PARTICIPANT;RSVP=TRUE;CN=" + userEmailAddress + 

            "\nDTSTART:" + startDateTime.ToUniversalTime().ToString(_dateFormat) +

            "\nDTEND:" + endDateTime.ToUniversalTime().ToString(_dateFormat) +

            "\nSTATUS:CONFIRMED" +

            "\nTRANSP:OPAQUE" +
            /// 0 for add, 1 for update or cancellation
            "\nSEQUENCE:" + seqNo +

            "\nUID:" + Guid.NewGuid() +
            /// resend original UID for updates and cancellations
            //"\nUID:" + UID +

            "\nDTSTAMP:" + createdOn.ToUniversalTime().ToString(_dateFormat) +

            "\nLAST-MODIFIED:" + createdOn.ToUniversalTime().ToString(_dateFormat) +

            "\nLOCATION:" + location +

            "\nDESCRIPTION:" + notes +

            "\nSUMMARY:" + emailSubject +

            "\nPRIORITY:5" +

            "\nCLASS:PUBLIC" +

            "\nEND:VEVENT" +

            "\nEND:VCALENDAR";

            return iCal;
        }

    }
}

This would be compiled into a .dll which needs to be registered in SQL Server. This assembly is to be marked as UNSAFE so someone with sysadmin priviges needs to register it.

CREATE ASSEMBLY [eventEmail]
AUTHORIZATION [dbo]
FROM 'C:\temp\icalemail.dll'
WITH PERMISSION_SET = UNSAFE

There is an interesting discussion of ‘UNSAFE’ assemblies on Stackoverflow.

Now create the stored procedure which will call the assembly.

create procedure dbo.usp_mb_eventnotify(@startDateTime datetime
				, @endDateTime datetime
				, @emailSubject nvarchar(80)
				, @emailSummary nvarchar(1000)
				, @location nvarchar(255)
				, @attendeeName nvarchar(100)
				, @attendeeEmail nvarchar(255)
				, @organizerName nvarchar(100)
				, @organizerEmail nvarchar(255)
				, @UID nvarchar(255)
				, @status nvarchar(12)
				, @requestType nvarchar(12)
				, @smtpserver nvarchar (255)
				, @smtpport bigint)
as external name [iCalEmail].[iCalEmail.Storedprocedures].[SendiCal];

Examples of use:

Create the event

exec dbo.usp_mb_eventnotify  
'2012-08-08 13:00:00',
'2012-08-08 15:00:00',
'BigProj 2',
'New schedule of Event sent by Matt',
'CONF01',
'Balent, Matt',
'matt.balent@anvil-of-time.com',
'AoT',
'donotreply@email.com',
'AOT372711c',
'CONFIRMED',
'REQUEST',
'1234.na.corp.aot.com',
25

Update the event

exec dbo.usp_mb_eventnotify				
'2012-08-08 10:00:00',
'2012-08-08 12:00:00',
'BigProj 2',
'New schedule of Event sent by Matt',
'CONF01',
'Balent, Matt',
'matt.balent@anvil-of-time.com',
'AoT',
'donotreply@email.com',
'AOT372711c',
'CONFIRMED',
'REQUEST',
'1234.na.corp.aot.com',
25

Cancel the event

exec dbo.usp_mb_eventnotify 
'2012-08-08 10:00:00',
'2012-08-08 12:00:00',
'BigProj 2',
'New schedule of Event sent by Matt',
'CONF01',
'Balent, Matt',
'matt.balent@anvil-of-time.com',
'AoT',
'donotreply@email.com',
'AOT372711c',
'CANCELLED',
'CANCEL',
'1234.na.corp.aot.com',
25

Something to look out for is the Disable Bind setting in your connection string. In OLEDB the default is DisbleBind=1 which disables the binding. For the SNC the default is DisableBind=0 which tells Powerbuilder to bind input parameters to a compiled SQL statement. More information can be found at the following Sybase link.

The first step is still simple and straightforward.

 SELECT id
	, code
	, description 
INTO backup_Widget
FROM Widget

This copies the rows from Widget into a new table backup_Widget. The new table columns have the same datatypes as the source table.

Now remove the rows with

DELETE FROM Widget

Now if you try to ‘return’ the rows from the backup table to the regular table

INSERT INTO Widget (
	id
	,code
	,description)
	SELECT id
	,code
	,description
	FROM backup_Widget

You the an error: “Cannot insert explicit value for identity column in table ‘Widget’ when IDENTITY_INSERT is set to OFF.”

Now, if you don’t care what values are in the IDENTITY column you can just re-run the insert but without the reference to the id column. Also, if you have sequential IDENTITY values without any gaps you can simply TRUNCATE the table, which resets the IDENTITY seed value, and re-run the insert with an “ORDER BY id” clause.

If you have gaps in the IDENTITY values and must restore the data with exactly the same ids you need to do something like this.

set IDENTITY_INSERT dbo.Widget ON
GO

INSERT INTO Widget (
	id
	,code
	,description)
	SELECT id
	,code
	,description
	FROM backup_Widget
GO

Now the data is restored with the same IDENTITY values it had originally.

There are a few other commands of interest when dealing with IDENTITY values.

To check the current IDENTITY value for a table:

DBCC CHECKIDENT(Widget)

Which gives you something like: Checking identity information: current identity value ’12’, current column value ’12’

To ‘reseed’ the table which means give the table a new seed value:

DBCC CHECKIDENT(Widget, RESEED, 20)

Gives you something like: Checking identity information: current identity value ’12’, current column value ’20’.

Since the ‘current column value’ shows the last used seed value, the next record inserted into the Widget table will have an id of ’21’ (the default increment being 1). You can omit the reseed value on the statement and the new seed value will be the maximum value in the IDENTITY column if the current seed value is less than this.

Powerbuilder

date ld_eom

ld_eom = RelativeDate(Date(String(Month(Today()) + 1) + "/1/" + String(Year(Today()))), -1)

SQL Server

SELECT DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, Getdate()) +1, 0))

I quess SQL Denali has a new function (EOMONTH) to do this too.

SELECT EOMONTH(GETDATE())

So in my case I was refactoring some code to correct for a PowerBuilder datawindow issue and needed to split up a block of text from a table into individual rows based on the carriage return / linefeed codes contained within it. Since I needed additional data from another table to properly group the data I came up with the following as a datasource for the datawindow.

SELECT	sn.Item
FROM 		dbo.Customer
LEFT JOIN	dbo.CustomerNotes ON dbo.Customer.cust_id = dbo.CustomerNotes.cust_id
CROSS APPLY dbo.uft_splitnotes(dbo.CustomerNotes.note_txt, '[CRLF]') sn 
WHERE dbo.Customer.cust_id = 1

Note the use of CROSS APPLY which functions like an INNER JOIN. To simulate a LEFT JOIN use OUTER APPLY.

The following script snippits can be run against your database to demonstrate the use of this technique. I am duplicating the table function script from my previous post for the sake of completeness.

CREATE TABLE Customer (
cust_id Int
,cust_name nvarchar(100)
)

CREATE TABLE CustomerNotes (
note_id int
,cust_id INT
,note_dt datetime
,note_txt nvarchar(max)
)

INSERT INTO Customer (
cust_id, cust_name)
SELECT 1, 'Koala Quarries'

INSERT INTO Customer (
cust_id, cust_name)
SELECT 2, 'Ironclad Aluminum'


INSERT INTO CustomerNotes (
note_id, cust_id, note_dt, note_txt)
SELECT 1, 1, Getdate(), 'I spoke with' + CHAR(13) + CHAR(10) + 'Jim to discuss the June shipment.' + CHAR(13) + CHAR(10) + 'Will follow up on Monday.'


CREATE FUNCTION [dbo].[uft_splitnotes] (@StringArray NVARCHAR(MAX),
                                        @Delimiter   NVARCHAR(30))
RETURNS @Results TABLE 
(
  SeqNo INT IDENTITY(1, 1),
  Item  NVARCHAR(MAX)
)
AS
  BEGIN
      DECLARE @Next INT
      DECLARE @lenStringArray INT
      DECLARE @lenDelimiter INT
      DECLARE @ii INT
      --initialise everything
      IF UPPER(@Delimiter) = 'SPACE'
        BEGIN
            SET @Delimiter = ' '
        END
      IF UPPER(@Delimiter) = '[CR]'
        BEGIN
            SET @Delimiter = CHAR(13)
        END
      IF UPPER(@Delimiter) = '[LF]'
        BEGIN
            SET @Delimiter = CHAR(10)
        END
      IF UPPER(@Delimiter) = '[CRLF]'
        BEGIN
            SET @Delimiter = CHAR(13) + CHAR(10)
        END
      IF UPPER(@Delimiter) = 'CHAR(13)'
        BEGIN
            SET @Delimiter = CHAR(13)
        END
      IF UPPER(@Delimiter) = 'CHAR(10)'
        BEGIN
            SET @Delimiter = CHAR(10)
        END
      IF @Delimiter = 'CHAR(13) + CHAR(10)'
        BEGIN
            SET @Delimiter = CHAR(13) + CHAR(10)
        END

      SELECT @ii = 1,
             @lenStringArray = LEN(REPLACE(@StringArray, ' ', '|')),
             @lenDelimiter = LEN(REPLACE(@Delimiter, ' ', '|'))
      --notice we have to be cautious about LEN with trailing spaces!
      --while there is more of the string…
      WHILE @ii <= @lenStringArray
        BEGIN--find the next occurrence of the delimiter in the stringarray
            SELECT @Next = CHARINDEX(@Delimiter, @StringArray + @Delimiter, @ii)

            INSERT INTO @Results
                        (Item)
            SELECT SUBSTRING(@StringArray, @ii, @Next - @ii)
            --note that we can get all the items from the list by appeending a
            --delimiter to the final string
            SELECT @ii = @Next + @lenDelimiter
        END
      RETURN
  END 
  
---- selects to show the data from above
select * from CustomerNotes

SELECT	dbo.Customer.cust_id
		, dbo.Customer.cust_name
		, dbo.CustomerNotes.note_dt
		, sn.Item
FROM 		dbo.Customer
LEFT JOIN	dbo.CustomerNotes ON dbo.Customer.cust_id = dbo.CustomerNotes.cust_id
OUTER APPLY dbo.uft_splitnotes(dbo.CustomerNotes.note_txt, '[CRLF]') sn 

SELECT	dbo.Customer.cust_id
		, dbo.Customer.cust_name
		, dbo.CustomerNotes.note_dt
		, sn.Item
FROM 		dbo.Customer
LEFT JOIN	dbo.CustomerNotes ON dbo.Customer.cust_id = dbo.CustomerNotes.cust_id
CROSS APPLY dbo.uft_splitnotes(dbo.CustomerNotes.note_txt, '[CRLF]') sn

This situation I was facing involved the display of text within a PowerBuilder Datawindow. When the data for a specific report datawindow was retrieved the display was sometimes cut off or overlapped other portions of the report layout (the header usually). This only seemed to happen when the text column was included on the report and the data within the column exceeded the number of lines you would expect to print on a single page. Since the text data was originally input by users, it almost always included carriage return / linefeed (CRLF) line terminators within it. The easiest way to correct this was to change the data source of the datawindow to parse the text and split it up based on these CRLF sequences. This way I would get a result set of many lines, each with a portion of the text, rather than one with the entire text block (which was what PowerBuilder was having trouble sizing/positioning correctly).

The table function was inspired by the example found here.

CREATE FUNCTION [dbo].[uft_splitnotes] (@StringArray NVARCHAR(MAX),
                                        @Delimiter   NVARCHAR(30))
RETURNS @Results TABLE 
(
  SeqNo INT IDENTITY(1, 1),
  Item  NVARCHAR(MAX)
)
AS
  BEGIN
      DECLARE @Next INT
      DECLARE @lenStringArray INT
      DECLARE @lenDelimiter INT
      DECLARE @ii INT

      --initialise everything
      IF UPPER(@Delimiter) = 'SPACE'
        BEGIN
            SET @Delimiter = ' '
        END
      IF UPPER(@Delimiter) = '[CR]'
        BEGIN
            SET @Delimiter = CHAR(13)
        END
      IF UPPER(@Delimiter) = '[LF]'
        BEGIN
            SET @Delimiter = CHAR(10)
        END
      IF UPPER(@Delimiter) = '[CRLF]'
        BEGIN
            SET @Delimiter = CHAR(13) + CHAR(10)
        END
      IF UPPER(@Delimiter) = 'CHAR(13)'
        BEGIN
            SET @Delimiter = CHAR(13)
        END
      IF UPPER(@Delimiter) = 'CHAR(10)'
        BEGIN
            SET @Delimiter = CHAR(10)
        END
      IF @Delimiter = 'CHAR(13) + CHAR(10)'
        BEGIN
            SET @Delimiter = CHAR(13) + CHAR(10)
        END

      SELECT @ii = 1,
             @lenStringArray = LEN(REPLACE(@StringArray, ' ', '|')),
             @lenDelimiter = LEN(REPLACE(@Delimiter, ' ', '|'))
      --notice we have to be cautious about LEN with trailing spaces!
      --while there is more of the string…
      WHILE @ii <= @lenStringArray
        BEGIN--find the next occurrence of the delimiter in the stringarray
            SELECT @Next = CHARINDEX(@Delimiter, @StringArray + @Delimiter, @ii)

            INSERT INTO @Results
                        (Item)
            SELECT SUBSTRING(@StringArray, @ii, @Next - @ii)
            --note that we can get all the items from the list by appeending a
            --delimiter to the final string
            SELECT @ii = @Next + @lenDelimiter
        END
      RETURN
  END

Now since I wanted to split the string based on embedded CRLF sequences, I set up a series of IF statements to allow for various ways to designate these. I’m sure there are better ways to do this part.

Sample output:

Although this was written in SQL Server 2008, I’m sure it can be replicated in other flavors of SQL.

A quick trip to the debugger shows that the parent record is being inserted fine, but when I call my function to get the Identity value of the item just inserted, I’m getting back a NULL value.

After digging around awhile my boss remembers something. You need to modify the pbodb11.5.ini file in the appropriate sections

[MS_SQLSERVER_SYNTAX]
GetIdentity='Select SCOPE_IDENTITY()'

This has been around since 2009, see link.

and

[Microsoft SQL Server]
ServerCursor='No'

From Sybase web site:

ServerCursor database parameter
When you use the OLE DB database interface with a Microsoft SQL Server database and retrieve data into a DataWindow, server-side cursors are used to support multiple command execution. If this has a negative impact on performance, try increasing the size of the Block database parameter to 500 or more, or adding the following line to the [Microsoft SQL Server] section in the PBODB initialization file to turn off server-side cursors:

ServerCursor = ‘NO’
The ServerCursor parameter can be used only in the PBODB initialization file.

UPDATE
    Table
SET
    Table.col1 = other_table.col1,
    Table.col2 = other_table.col2
FROM
    Table
INNER JOIN
    other_table
ON
    Table.id = other_table.id 
WHERE ...

The structure of this syntax is always something I get mixed up when I want to do this.