How to populate memo field from SQL SELECT in version 9

In version 7 this was easy. You just did %myVar:=%SelectSQL("SELECT * FROM eUSER where eUserName = '%User.Name') and you got a tab delimited list of all the columns in the table for the current user. You could do this on any table.

Is there a way to achieve the same in V9? I've tried Local.myVar = SelectSql(new MetastormDefault(),"SELECT * FROM eUser Where eusername = '" + ProcessContext.UserName + "'"), but I get a single string of text, not a delimited list.

Thanks in advance, Rob.

Find more posts tagged with

BPM

Comments

Jerome_Pearce

It works quite differently now. You are not able to define the delimiters any more. SelectSql() returns a List type. Serialising to a memo variable will concatenate the list items with tabs IIRC (I may be wrong, it has changed at least twice since 9.0.0)

There are several different ways to manage this now. One thing ware doing is creating List Serialisation functions that serialise this sort of list into XML, and back again. That way you should be able to store a list and make use of it as a proper List (ie an array of typed variables).

It really depends what you are trying to do. Obviously filling the memo field is not the desired end result - you would want to do something with the data. What is that something?

Paul Lawrence

SelectSql actually returns a data container but can return a list, text, data set, etc. From a data set you can navigate this to get at the particular data you need including individual row and column items. How you craft this really depends on what you are trying to achieve but it really is more flexible than what you could do in v7 and can produce much more readable and maintainable code.

Jerome_Pearce

Paul wrote:

SelectSql actually returns a data container but can return a list, text, data set, etc.

Sorry, yes, I was misleading. I forgot as we typically use just the List, mainly having just one field returned.

There is a good example of using the return of SelectSql() as a data container (for populating option lists, for example) here:

http://processmapping.com.au/freestuff/freemetastormbpm9solutions/UserUtilities.html

RobDixon

Thanks for the suggestions. Jerome your libraries look really interesting and certainly show me how to get data from SQL through scripting alone. Nice touch. What they don't show me though is what I'm trying to achieve; an array of values that I can assign to a number of variables. Is it possible to do this, and if so how? I'm something of a C# novice so any code examples you can give me would be most welcome. Parsing the resulting array is no problem; I have a C# script which already works perfectly by looking for a delimiter.

achopra

Ron,

Have you considered using a Business Object rather than using SelectSQL for what you are trying to do? In my mind, this is exactly what a BO is intended to do...to run a query and return the results into an array of variables. You can easily set other Local or Process variables from the BO variables using the Expression Builder.

Perhaps there is a reason that you would prefer to use SelectSQL over the BO approach?

RobDixon

I have no problem using a BO and on reflection, this is one very viable option. However, in an instance where I want to return 20 or more columns of data from a table/MBO, I do not want to have to define and then populate all those variables individually. I want to use one variable, and then use my "memo 2 array" script to get at the values. This would seem to me to be a more efficient use of resources in such an instance.

Jerome_Pearce

I agree with Ari.

From a maintainability point of view, it is a great deal easier to set up a Business Object, and just read those variables as desiers. It is muc easier set

MyVar=BususinessObject.Field1

than

MyVar=SomeComplicatedFuntion(MemoVar,0)

Unfortunately, you need the 'complicated function', as you can no longer treat tab delimited strings as arrays, being in a real programming language. The Business object method also ensures strong typing rather than converting every variable to a string and then back again. You do not have to defoine any variables, BTW, the system does all that from the SQL results.

Dive into Business Objects, and you will never look back. We used to do a lot of this, and set up a load of functions to manage list processing because they had been dropped in version 9, but we have found we never use them. Business Objects do it all.