Business Object: dates in SQL Query

[Stephen_Cao]

I have a Business Object to count the number of folders for a process.

This works fine:
SELECT COUNT(*) AS numFolder
FROM efolder 
WHERE eMapName = 'MyProcess'
  AND eCreationTime < SysDate

But this doesn't work, it fails to generate the variable name:
SELECT COUNT(*) AS numFolder
FROM efolder 
WHERE eMapName = 'MyProcess'
  AND eCreationTime < @pEndDate  --@pEndDate is a datetime

Neither does this:
SELECT COUNT(*) AS numFolder
FROM efolder 
WHERE eMapName = 'MyProcess'
  AND eCreationTime < TO_DATE(@pEndDate,'mm/dd/yyyy') --@pEndDate is text

Has anyone seen this?  Any suggestions for a workaround...

Stephen

[Paul]

In SQL you'll need to pass the date as a formatted string, so make your parameter a text param and format the date accordingly. I normally use an ISO date format.

[Ari Chopra]

One technique that I've used to deal with this issue it to use FormatDateTime on the Metastorm date time variable.  There might be a better way to do it, but this works well for me.

For example:

FormatDateTime(ProcessContext.CurrentTime,"yyyyMMdd HH:mm")

[Stephen_Cao]

Neither does this:
SELECT COUNT(*) AS numFolder
FROM efolder 
WHERE eMapName = 'MyProcess'
  AND eCreationTime < TO_DATE(@pEndDate,'mm/dd/yyyy') --@pEndDate is text

[Jerome҉]

I have to say that I firmly disagree with formatting a datetime variable as a string so that the DBMS can then convert it back to a datetime variable! We got over all of that when we moved to version 9 and left the old text 'interpreted' language behind.

We now have a very good and almost perfect (in my experience, YMMV) solution in the ability to have proper parameters in our SQL at last. The way it has been implemented does cause a few problems, eg using the "@" symbol, which is itself used for stored proc parameters was a bit of a blunder, but in this case there should be none.

In my experience the most common cause of failure for datetime parameters is leaving the type as the default of Text. Check that. If it does not fix anything, I would be happy to post a working example you can compare with, also against an Oracle database, as this is.

[Paul]

Jerome, I agree with the principle of using type variable where possible but the reality in this scenario is that Metastorm converts a datetime parameter to a string before passing it to the DBMS (use SQL Profiler and just see what gets sent when you use a datetime parameter). Often I have date information in various formats and not always the Metastorm datetime type  - in these situations it seems a little unnecessary to convert to a datetime only for Metastorm to convert back to a string. I guess it depends on the situation but the DBMS is going to get a string one way or another.

Stephen, your second example works for me if I pass a Metastorm DateTime type or a System.DataTime.

[Stephen_Cao · Accepted solution]

I was able to work around this issue by issuing the SQL Query from a Server Side Script.

[Promote(PromotionTargets.ExpressionBuilder)]
[Category("UserCategory")]
public static int getTotalFolderProcessed(string dtmStart, string dtmEnd)
{

    MetastormDefault connMstm = null;
    try
    {
        connMstm = new MetastormDefault();
        string sSQL = "SELECT COUNT(*) FROM EFOLDER WHERE eMapName = 'MyProcess' AND eCreationTime >= TO_DATE('" + dtmStart + "','mm/dd/yyyy') AND eCreationTime <= TO_DATE('" + dtmEnd + "','mm/dd/yyyy')";
        return Mstm.SelectSql(connMstm, sSQL).Integer;

    }
    catch(Exception ex)
    {
      ...

    }
    finally
    {
        connMstm = null;    
    }   

}

[Jerome҉]

We have never had a problem passing in datetime parameters without having to do anything at all with them. It makes it so much easier to use the SQLArg() parameters the way they the SelectSQL() function is intended to be used.

[Greg]

I agree with Jerome. Use SQLArg in this case. Creating queries like this could be potential place to sql injection i think.

[Robert Vignerot]

Completely agreed with Jerome and Greg about this being a risk for SQL injection.  Business Objects absolutely support DateTime parameters.  I tried your example:

SELECT COUNT(*) AS numFolder

FROM efolder

WHERE eMapName = 'MyProcess'   AND eCreationTime < @pEndDate  --@pEndDate is a datetime

This created the parameters just fine.  The only issue as Jerome pointed out in a post above is that the parameter is created as a type Text not DateTime, so without changing this, it would fail at runtime.