 |
MrCheviot
Novice
Joined: 02 Dec 2007
Posts: 42
|
 Posted: Thu Jan 10, 2008 2:45 am
[2.18] Local variables & Indirect variables
|
Just experimenting a bit w/ local variables, ran into an issue which is either incorrect syntax on my part or limitation of local vars or.. ?
| Code: |
#var dr_g {}
#var dr_g2 {}
#var key test
#loc dr_l
#addkey dr_g rec test
#addkey dr_g2 test hello
#addkey $dr_l test hello
#show @dr_g.rec | @dr_g2.test | $dr_l.test
#show Global: @{dr_g2.@{dr_g.rec}}
#show Local w/ Key: $dr_l.@key
#show Local w/ DR.Key (1) : $dr_l.@{dr_g.rec}
#show Local w/ DR.Key (2) : $dr_l.@dr_g.rec
#show Local w/ DR.Key (3) : {$dr_l.@{dr_g.rec}}
//#show Local w/ DR.Key (4) : ${dr_l.@{dr_g.rec}}
|
Results:
| Code: |
test | hello | hello
Global: hello
Local w/ Key: hello
Local w/ DR.Key (1) : test=hello.test
Local w/ DR.Key (2) :
Local w/ DR.Key (3) : test=hello.test
|
#show no. 4 is commented out because, while intuitively this is what I think should work, CMUD returns an 'invalid local variable' error.
Use: I'm basically trying to #addkey local dr.key %additem(new,local.{dr.key}) - dr.key in this case happens to be an ADO Recordset, so I'm effectively trying to convert RecordSet -> DataRecord (actually trying different ways of doing this).
Here's a sample of the full code using working global var (part of a function) which lead to this example:
| Code: |
#var newDR {}
#call @recSet.MoveFirst
#while (not @recSet.EOF) {
#loop 0,(@recSet.Fields.Count - 1) {
#addkey newDR @recSet(%i).Name %additem(@recSet(%i), @{newDR.@{recSet(%i).Name}})
}
#call @recSet.MoveNext
}
|
(Note if $newDR is used above, this doesn't work, hence the post).
So question 1 - is this a bug/limitation/coding error? And question 2 - while I'm testing alternatives, is there a better/different way of converting a RS with mult. fields into a DR with key = list of values?
Thanks,
-MrC |
|
|
|
|
Vijilante
SubAdmin
Joined: 18 Nov 2001
Posts: 5182
|
Posted: Thu Jan 10, 2008 3:06 am
|
Yes there is a much better way. Take a look at the SpecialMapQuery and OpenMapDatabaseForQuery functions in my Toolbox package. Those were built around using the ADO interface completely right. The main thing that makes it faster and more efficient is the use of the GetString method.
Depending on what your query structure is, a combination of the GetString method and using a carefully made %subregex will likely be able to produce any field layout and seperators you want.
Local variables do not handle indirection. What you are trying to do with one can be achieved through the %db function. |
|
_________________
The only good questions are the ones we have never answered before.
Search the Forums |
 |
|
|
MrCheviot
Novice
Joined: 02 Dec 2007
Posts: 42
|
Posted: Thu Jan 10, 2008 3:13 am
|
Just as a side note..
Test query requests just 2 fields, returns 460 rows.
#addkey DR field1 @QueryAsList($SQL_Str1)
#addkey DR field2 @QueryAsList($SQL_Str2)
Takes about 22 msec according to (%sec - $starttime)
DR = @QueryAsDataRecord($SQL_Str12)
Takes about 648 msec using above code (!)
Adding a third field bumps these times to 37 and 1079 msec, resp.
Guess I shouldn't be surprised given the double looping going on in @QueryAsDataRecord, just thought I'd provide some results to stoke the conversation.
-MrC |
|
|
|
|
MrCheviot
Novice
Joined: 02 Dec 2007
Posts: 42
|
Posted: Thu Jan 10, 2008 3:17 am
|
| Vijilante wrote: |
Yes there is a much better way. Take a look at the SpecialMapQuery and OpenMapDatabaseForQuery functions in my Toolbox package. Those were built around using the ADO interface completely right. The main thing that makes it faster and more efficient is the use of the GetString method.
Depending on what your query structure is, a combination of the GetString method and using a carefully made %subregex will likely be able to produce any field layout and seperators you want.
Local variables do not handle indirection. What you are trying to do with one can be achieved through the %db function. |
*Nod* I have a QueryAsList which is very similar to yours (using GetString). This is sort of in the interest of testing, and also since I know in VB you can set an Array = RecordSet and be on your way (at least if I remember correctly, been awhile).
So Using | as a row seperator will produce a list variable. Is there any char(s) that could be used as col seperator (along with | for row) that would produce a data record?
Thanks,
-MrC |
|
|
|
|
MrCheviot
Novice
Joined: 02 Dec 2007
Posts: 42
|
Posted: Thu Jan 10, 2008 3:25 am
|
Last side note - stupid me forgot about %db().
If anyone cares, using %db(%localDR..) vs. @globalDR.@{indirect} (local var vs. global var) shaved about 40% off of proc. time using @QueryAsDataRecord.
It's my first real "wow" w/ local variables, even if I don't ever use this particular bit of code. I'm quite chuffed.
-Mrc |
|
|
|
|
|
|
|
