Raising flags from a stored procedure part 2

This is in addition to the first post.

So we have a process with a flag called "My Flag", which starts the process.

Firing flags from a stored procedure.

This is something that you may need to do from time to time. And it is not without it's problems.

This post deals with what you need to do to raise flag by inserting a row in the eRaisedFlag table.

This post does not deal with whether or not you should do this. I have used this technique without problems for years, and know of implementations that use this technique and still sleep soundly at night.

Firstly let us look at the structure of the eRaisedFlag table

The important points to note are :

• The eSequenceNo is automatically generated, so there is no need to insert a value
• The eEngineName is either a valid engine name or, in the case of only one engine, the defaults is SPACE
• The eThreadNo should be 0.

And not in the documentation are the following points

• The eFlagData cannot be NULL. If there is no flag data, it should be set to empty string ''
• It is not necessary to set the eNextEntry column.
• There should be a corresponding row in the eWait table for the flag you are attempting to fire.

Just to expand on this last point a bit.

Let's say I have a process called "Absence tracking", which has a flag named "Archive Folder" that moves the folder to an archive stage.

I would insert a row into eRaisedFlag like so

INSERT INTO eRaisedFlag ( eEngineName,eThreadNo,eFlagName, eFlagFolder,eFlagData ) VALUES ( ' ',0,'Archive_Folder', '0000000000000000000000000000001','' )

For this flag to fire, there would need to be a row in eWait.

SELECT * FROM eWait WHERE eFolderId = '0000000000000000000000000000001'

Would return a row like

[Quote] eSequenceNo,eFlagName,eWaitTime,eFolderId,eMapName,eEngineName,eThreadNo,ePriority,eFlagFolder,eActionName 123456,Archive_Folder,1753-01-01 00:01:00.000,0000000000000000000000000000001,,,0,0,0000000000000000000000000000001,Archive Folder [/Quote]

It is vitally important that you observe all of the gotchas I mentioned earlier. If there is no corresponding row in the eWait table then this means the flag will not fire.

One other bear trap is that you notice the flag name was "Archive Folder", and yet in the eWait table the flag name is Archive_Folder. Although it is possible to give a flag a name with spaces in, in the Designer, under the bonnet Metastorm will replace the spaces with underscores.

Good luck, and happy flag raising

Date query in grid.

I have come across this problem many times. Let's say for example you want to query a table to get all rows in a given date range.

SELECT * FROM PERSON WHERE DateOfBirth >= '01/01/1965' AND DateOfBirth <= '01/01/1970'

The above SQL will work just fine. The problem arises when you try to use a Metastorm grid to do likewise. The where clause is now the Row(s) box on the grid properties, and we would expect something like

(Person.DateOfBirth <= '01/01/1965') AND (Person.DateOfBirth >= '01/01/1970')

to work. But it doesn't.

You can expect an error such as

[Quote] Failed to process 'eExecuteSQL' Metastorm engine Database Connector request. ErrorCode: '-2147217913'. Description: 'The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.'. Source: 'Microsoft OLE DB Provider for SQL Server'. SQL State: '22007'. NativeErrorCode: '242' Procedure GetRecordSet call failed. Position: 411 [/Quote]

The output from SQL profiler (this is a great way to see how Metastorm is actually preparing the SQL, which can in some circumstances be a great way of getting to the crux of a problem should the Metastorm error be too cryptic - although in this example the error message is informative enough) is this :

SELECT Field1,Field2,.... FROM PERSON WHERE (Person.DateOfBirth >= '01/01/1965') AND (Person.DateOfBirth <= '01/01/1970')

Now this looks like it should work, and if you paste it into a query window, it does.

I have to confess, the reason why this fails escapes me, but the workaround works so until we find out why I suggest you just go with it. The workaround is of course to use a date formatter.

We could use

%FormatTime(%dateOfBirth,"dd/mm/yyyy")

but that would still give the same output.

The answer is to format your dates like

%FormatTime(%dateOfBirth,"dd mmm yyyy")

Which in our profiler trace gives you

(Person.DateOfBirth <= '01 jan 1965') AND (Person.DateOfBirth >= '01 jan 1970')

By formatting the dates in this way, you are asking the database engine to do the conversion, which gets us the result we need.

I recently came across a problem with inserting rows into a SQL database table from within a Metastorm procedure. The problem was that even if a date was not entered into the date picker, and in spite of using a stored procedure, the date would always end up in the database as 01^st January 1900.

If from a SQL Query window you type

CAST('' AS DATETIME)

You get

1900-01-01 00:00:00.000

So this means that the date picker must be passing a string to the stored procedure.

After doing a SQL trace, sure enough the values passed to the DATETIME parameter of the stored procedure was N''

The stored procedure had declared the DATETIME parameter as

myDateTime DATETIME = NULL

So it already defaults to NULL, but Metastorm passes an empty string, which is unsurprising considering the date picker stores the date in an html text box.

The simple workaround is inside the stored procedure to check for the date being passed in as empty string and to set it to NULL.

IF @myDateTime='' SET @myDateTime = NULL