Generate NVPair From GroovyRowResult

[Chris F.]

Hi,
        I have a requirement to be able to dynamically generate a large amount of Key / Value pairs where the key is dynamically named off of certain ID's that are returned from the database and the value is randomly returned.  I'm able to generate the key / values from a database query but I cannot figure out how I can convert the result set into a name value pair.  I've tried using the closure based method that calls map.each featured in the groovy code snippets with a result set that is returned in a map format IE: (sql.rows(query) results << to.(sqlRowResult)) but this throws errors that the values are inapplicable to the map.each functionality.  
 
Any advice?

Thanks,
Chris F.

[junoyoon]

def sqlNvs(def sql) {
    def tmp = []
    sql.eachRow('Select * from Person') {
        tmp << it.toRowResult()
    }
    def nvs = []
    tmp.each {
        key, value ->  nvs.add(new NVPair(key, value))
    }
    return nvs as NVPair[]
}

does not  this help you??
I didn't test it.

[Chris F.]

Nope, still throwing the same error exception:
groovy.lang.MissingMethodException: No signature of method: testCase.att.attWizard$_buildMapHeader_closure2.doCall() is applicable for argument types: (groovy.sql.GroovyRowResult) values: [[key:dbID, value:590]]
Possible solutions: doCall(java.lang.Object, java.lang.Object), findAll(), findAll(), isCase(java.lang.Object), isCase(java.lang.Object)

[junoyoon]

def sqltoNvs(def sql){
                def nvs = []
                def rows = sql.rows("select * from nuser")
                def keys = rows[0].keySet()
                rows.each({
                        def eachRow = it
                        keys.each({
                                nvs.add(new NVPair(it, eachRow[it].toString()))
                        })
                })
                return nvs
}

It's the working version.

[Chris F.]

Thank you that worked to get the data into the NVPair, albeit not quite in the format I was looking for.  I got it to work by tweaking the T-SQL a bit to return the results.  In a format that was useful.  

The code you gave me used the column names as the Name, and the results as the values.  The query I wrote was returning a set of data with the columns being the KEY & Value.  So each tupal was actually the name value pair I needed, in this case there were around 50 name value pairs generated.  Since this was all going into the NVPair by column I used a pivot to change the result set so that each row became a column in the returned data set.  While it would be more useful to not have to do it that way, I couldn't figure out how to get it to return the data in the way I needed so in case anyone else runs into this problem here is the t-sql I used to formulate the data so it will work with the code you supplied.  

First to note you'll need to create a temporary table that has two columns, key & value and populate it with your data.  
DECLARE @cols NVARCHAR(MAX)
DECLARE @query NVARCHAR(MAX)

SELECT  @cols =
STUFF((SELECT ',' + QUOTENAME([key])
        FROM tempdb.dbo.KeyValue
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

SELECT @query = N'SELECT ' + @cols + N' FROM
        (
                SELECT [key], value
                FROM tempdb.dbo.keyValue
                ) d
                PIVOT
                        (
                        MAX(value)
                        FOR [key] IN (' + @cols + N')
                        ) piv;'

EXEC sp_executesql @query

[junoyoon]

I think it can not make any experiment for this because I don't have any experience in the field of TSQL at all. I also didn't try any SQLs code in groovy neither.

http://groovy.codehaus.org/Tutorial+6+-+Groovy+SQL

I've spent time to investigate this for a while.

I'm not sure but the basic ideas you should consider might be..
when eachRows are executed... the local variable "it" contains.. multiple columns in a row.
It seems that you can access each column by referencing it.column_name.

E.X) it.helloworld (if the name of column you want retrieve  is "helloworld")

By mixing this with the code I already showed, I believe you can do what you want.

[Chris F]

So I recently re-visited this issue and got it working the correct way.  Just figured I'd share the method in case anyone else ever has a similiar problem.

private buildMapHeaderTest() {
        def nvs = []
        sql.eachRow("SELECT keyName, keyValue FROM dbo.KeyStore") {row ->
                nvs.add(new NVPair(row.keyName.toString(), row.keyValue.toString()))
        }
                return nvs as NVPair[]
}

[junoyoon]

Thanks! Chris! It'll be a good reference.!