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 Lawrence

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.

achopra

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_Pearce

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 Lawrence

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

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_Pearce

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.

Grzegorz Lag

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.