Hi,

I'm having problems with dynamic queries I create on the fly in oml.
I really would be gratefull for any help.

In my application I allow user to define the logical structure of database . All that information (entities/attributes definitions, inluding my own types: locations, styles, colors, symbols) are stored in meta-data part of my database.

Data iself are stored in such a way that there is one table which stores ids of all entitites (and of course type of entity), but all attributes of entities are stored in seperate tables for each specific type (there is seperate table to store all 'int' attributes, seperate to store 'locations' attribute, seperate to store 'strings'...).


Then based on that metadata I build queries to retrieve entity data

1. based on entity definition I construct single query which is supposed to retrieve entity data
Simple example of Person entity:

              SELECT entity.id,
                  firstname.value AS firstname,
                  lastname.value AS lastname,
                  birthyear.value AS birthyear
               FROM data_entity as entity
                      LEFT JOIN data_attr_string as firstname
                           ON firstname.data_id = entity.id AND firstname.attr_id = 'firstname'
                      LEFT JOIN data_attr_string as lastname
                          ON lastname.data_id = entity.id AND lastname.attr_id = 'lastname'
                      LEFT JOIN data_attr_int as birthyear
                          ON birthyear.data_id = entity.id AND birthyear.attr_id = 'birthyear'
                WHERE entity.entity_id = 'Person'

Notice that firstname,lastname,birthyear are attributes defined by user and are  stored in meta-data.


3. Now having above query and entity definition I would like to retrieve data but I'm not able to do this
        <db:execute connection="wisdom" sql="{$viewQuery}">
            <data>

    HOW TO ACCESS 'firstname' ,'lastname'  attributes here since these name are stored in some variables and are not know at OML compilation time?
   
            </data>
        </db:execute>

'firstname' name is know in runtime not in compilation time, so I can't use {$firstname} statement.
I need to use something like {$<o:eval select="attributeName"/>} which of course doesn't work.


Thanks for any hints !


Tomasz Rakowski


PS.
I thought about generating and compiling oml on fly but above aproach seems to me be little more simple (of course if it would be feasible).

I could also retrieve entity data using several seperate queries (for each seperate attribute) but it would be much slower (specially in case of entity lists...)

Hi,

I noticed that I can't access function parameters/variables inside db:execute statement:

    <o:function name="Fun">
        <o:param name="param1"/>
    <o:do>
          <o:variable name="var1" select="'value'"/>
            <db:execute connection="db" sql="SELECT * from table">
            
          I CAN'T ACCESS here parameter param1  and variable var1.

            </db:execute>
    </o:do>
    </o:function>

The only workaround I found was to include these parameters in SELECT statement :

    <o:function name="Fun">
        <o:param name="param1"/>
    <o:do>
          <o:variable name="var1" select="'value'"/>

             <o:variable name="query">
                 SELECT attr1, attr2, ...  ,
                            <o:eval select="$param1"/> as param1, 
                            <o:eval select="$var11"/> as var1
                FROM table
             </o:variable>
            <db:execute connection="db" sql="{$query}">
            
          NOW I CAN ACCESS here parameter param1  and variable var1.

            </db:execute>
    </o:do>
    </o:function>

Is there any more elegant workaround for this ?



Tomasz Rakowski

Thanks for suggestions,

 I completely understand your design decision, but from the other hand
 it is also usefull to have in db:execute tag an access to parameters and variables which are in function context like you have done eg. in for-each tag where you also create new variable but still has access to others variables.

Sometimes function variables I would like to use inside db:execute are quite complex documents , which I wouldn't like/can't pass through SQL query.

Anyway I found it quite annoying that I have to do such strange things to pass parameters to db:execute tag. And we have to do this quite frequently.

At the moment you use two different notation to access db variables {$xx} and function variables <o:_eval select="$xxx">, but internally you store both variables in the same place RuntimeContext.variables (as a result you can access db variables also using <o:_eval select="$xxx"> notation).  Maybe in db:execute you could create seperate container/variable just for db variables and {$xx} tag would access variables only in such container. You could add this container to RuntimeContext.variables as a single variable along other function variables  and remove it when db:execute finishes  (exactly as for o-foreach).

I guess that in such case it would be not possible anymore to access db variables using <o:_eval select="$XXX">, but as I understand suggested notation is {$xxx} anyway. 

As I think about it longer maybe it would be even better solution if we access db variables like <o:_eval select="$db/xxxxx"> where $db would be the name of variable defined in db:execute tag (like in o:foreach tag).
In such case we could access db variables even in nested queries even if they return columns with the same name.


Tomasz




mars-LFbRVgPzq8JQ7AJdzaG9Tg@public.gmane.org wrote: Hi Tomasz,

I'll try give a quick answer to this -

With the o:XML db extensions, all local and type variables (ie all variables!) are out-of-scope within the result template of a SQL query. Only the db result set values are available, as variables. This is by design, in order to avoid conflicts. Otherwise local variables and type variables may be accidentally reassigned, which could get very confusing. If you think this is not the way it should be, pls advice as to your preferred solution!

The workaround you've found is not a bad one, though you can make it a little easier for yourself using attribute value substitution:






NOW I CAN ACCESS here parameter param1 and variable var1.




remember that string values have to be quoted, eg with the db:quote() function:


NOW I CAN ACCESS here parameter param1



hope this helps!

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Hi,

I think that I didn't clearly expressed my problem.

Here is another small example :

I would like to create general purpose function which will take two parameters:
  SQL Query and name of the column. Function should execute given query and from
ResultSet it should return data from given column name.

QUERY: "SELECT lastname,birth,sex FROM person WHERE id=23" and second parametr
'lastname'

How to do this ?
Normally in db:execute statement I would use{$lastname} but in this case
'lastname' is stored in some variable (function parameter) and I don't it during
compilation time ....


Tomasz Rakowski



 <mars <at> ...> writes:

>
> Hi Tomasz,
>
> I think this was maybe answered in response to your other mail, let me know if
you still have problems with this.
>
> Another approach might be to treat the problem as two sets of data; one being
the database result, the other
> the dynamic information necessary to execute the query. You can then merge the
two into whatever form you require.
>
> You could also use o:XML types to represent the datastructures, then set the
dynamic values programmatically.
> For example, if you define a type DataEntity with a suitable constructor and
setter functions, then:
> <o:variable name="entities">
>     <db:execute sql="...">
>         <o:eval select="DataEntity($firstname, $lastname, $birthyear)"/>
>         <!-- creates one DataEntity for each row in result set -->
>     </db:execute>
> </o:variable>
> <o:do select="$entities.SetSomeAttribute('firstname')"/>
> <!-- sets some attribute on _all_ DataEntity objects in $entities -->
>
> Now if you want to produce an XML result set from the DataEntity object, you
can overload the nodes()
> function to do so. Eg
> <o:type name="DataEntity">
>  ...
>  <o:function name="nodes">
>     <o:do>
>         <entity><o:eval select="$SomeAttribute"/></entity>
>     </o:do>
>  </o:function>
> </o:type>
>
> hope this makes sense -
> there's lots more you could do with o:XML types, let me know if you want any
more specific info!
>
> /m
>
> On 12 Nov 2006, at 17:50, Tomasz Rakowski wrote:
>
> Hi,
>
> I'm having problems with dynamic queries I create on the fly in oml.
> I really would be gratefull for any help.
>

Sponsored Link

$420,000 Mortgage for $1,399/month - Think You Pay Too Much For Your Mortgage? Find Out!