What does SelectSql("").List return?

rposener
rposener Member
in AppWorks #1

I'm trying to use the SelectSql("SELECT STATEMENT").List function to return several columns of data.  It seems that the list I get back has 0 items in it.  Does anyone have any ideas why?

Tagged:

Comments

  • Jerome_Pearce
    Jerome_Pearce
    #2

    As far as I am aware, this should return a 'list of lists' if there are multiple columns, or a list if there is one column, I think. It has changed a few times in SR 1, 2 & 3, however, and I have not kept up. I'll try to have a look today.

  • Jerome_Pearce
    Jerome_Pearce
    #3

    Wow! That has changed a bit!

     

    OK, here we go. It seems that a two column query returns a key value pair. I can, then do the following:

     

                string s = "";
                List l = Mstm.SelectSql(null, "SELECT eProcedureName,eMapName FROM eMap").List;
            
                foreach ( KeyValuePair<string, string> kvp in l.KeyValuePairList() )
                {
                    s +=  kvp.Key + " and " + kvp.Value + "\r\n";
                }

    If you only have one column selected, it fills both the key and value. If you have more than two, it just returns nothing.

     

    OK, so how do I get lots of data? Well, I could try converting to a memo.

     

    Tried that, and one column selects return a nice list separated by CRs.

    Two column selects return [col1, col2] (exactly like that) separated by CRs.

    Three column selects return nothing.

     

    It seems that is sort of like the List, although at least we get a simple list for a one column select.

     

    So then we look at other possible casts:

    If you return any data type apart from a memo, it returns the first row, first column (assuming it casts correctly).

     

    So what about the functions?

    There is GetEnumerator(). Oddly enough, despite its name, what it returns does not support the IEnumerator interface, so cannot be used in the foreach loop. Obviously there is some way to use this, and I'd love to know how, if anyone can tell me (I'll not hold my breath, however).

    and there is GetDataSet() and GetDataTable(). They return the standard .net types you would expect, although from my experience the table definition in the Tablset is not 'standard' at having attempted to get information from it in the past.

     

    So the answer seems to be, "here's the makings, roll-your-own, mate". Much as I am loathe to recommend it, you have to get the underlying objects and use them, as the supported functionality is a bit sparse. Having said that, once you go down that road, you have much more control. It seems a shame that we are forced more and more to become .Net developers, however, as that is excluding a decade's worth of very productive process designers form effective use of Metastorm BPM 9 in my experience. Ho hum.

  • Jerome_Pearce
    Jerome_Pearce
    #4

    Do bear in mind that the Key Value Pair used in Metastorm list options seems to be the wrong way around, being a Value Key Pair (value first, then key). This is at odds with the name, and expectations, of the class. That may be open to interpretation, but I see it as the following definition found online:

    A key-value pair (KVP) is a set of two linked data items: a key, which is a unique identifier for some item of data, and the value, which is either the data that is identified or a pointer to the location of that data.

    It is clear that the 'Key' is the unique item, or ID, Metastorm appear to see the 'Key' as what you see, and the 'Value' as what is stored. I may be wrong, and please let me know if I am, but I think that is the way round they use it.

    I consider the most important principle in software development, the "Principle of Least Astonishment" to be violated.

     

  • Jerome_Pearce
    Jerome_Pearce
    #5

    It appears, when I do it correctly, that the SelectSql() call returns a collection of DataRow objects. It is quite useful then, although you have to call the function within the foreach statement itself as the function does not return an object that is enumerable*, only the interface itself.

    (* I never expected to use that word in a proper sentence)

  • rposener
    rposener Member
    #6

    Wow!  Thanks Jerome. 

     

    I think that I will have to revert to using the System.Data.SqlClient namespace classes in this case.  The good thing is that I can still leverage the Metastorm Connection Objects to get connection strings.  At least Microsoft has clear and consistent documentation about how to use their data connection objects - and nice methods as well such as ExecuteScalar(), ExecuteNonQuery(), and ExecuteReader() that provide clearly documented results.  Would much rather deal with verbose code that has well documented support than the guessing that I have been doing with the metastorm SelectSql function.

     

    Maybe I will put together my own code library for commonly used / needed functions using the nice Microsoft counterparts...

  • Tony Lam
    Tony Lam
    #7

    Not sure if this might help you out but heres how I normally handle working with data in V9:

     

    DataTableReader dt = Mstm.SelectSql(null,"SELECT * FROM SomeTable").GetDataTable().CreateDataReader();

     

    If (dt.HasRows)

    {

      while (dt.Read())

      {

         someValue1 = (int)dt.GetValue(0);

         someValue2 = dt.GetValue(1).ToString();

      }

     

    Pretty much the same syntax as using sqlclient

  • Grzegorz Lag
    Grzegorz Lag
    #8

    This code look much easier and works fine :)

     

    DataTable tbl = Mstm.SelectSQL(null, "SELECT... ").GetDataTable();

    foreach(DataRow dr in tbl.Rows)

    {

    // do enything with dr["colName"]

    }

     

    Does enyone know the difference between putting null, or new MetastormDefault() into first parameter? I was trying to get this from helpdesk, but ... i'm not sure it they know ;(

  • Iain_Lindsay
    Iain_Lindsay
    #9

    Greg

     

    If you use "new MetastormDefault()" the engine will perform a select against the eConnection table to get the connection details to use.

     

    If you use "null" the engine doesn't perform the above select and will use its own connection.

     

    So you save yourself a DB roundtrip by using "null"

     

    Iain

  • William Tanner
    William Tanner
    #10

    I generally do my queries using the following structure...

     

    Metastorm.Runtime.Contracts.IDataContainer result = Mstm.SelectSql(null, "<YOU SQL STATEMENT HERE>");

foreach(System.Data.DataRow row in result)
{
    blah = row["COLUMNS HERE"].ToString();
}

     Saves a few operations.

  • Grzegorz Lag
    Grzegorz Lag
    #11

    Iain

     

    In v7 if we will set dsn to Metastorm engine create new connection. Else get some from connection poll. Do you mean that in v9 this works the same with null and new MetastormDefault()?

     

    This is important, becouse in some cases in v7 of we set DSN we could get deadlock.

     

    Regards

    Greg

  • Iain_Lindsay
    Iain_Lindsay
    #12

    My understanding is that it works the same with null as it does with new MetastormDefault()

     

    Iain

Categories

image
OpenText CE Products
AppWorks
Gateway & Platform