Orbeon Forms community mailing list

oxf:sql query using between .... and

Classic

List

Threaded

3 messages Options

bsteuhl

oxf:sql query using between .... and

I have a table with addresses, latitude and longitude values populated using yahoo geocode web service using REST submission. Users are able to query this database using oxf:sql based on various parameters and using oxf:xslt convert xml into appropriate google .kml xml structure passed into another pipeline to write the results out to file directory so google linked server can display results in Google Earth. My problem:

I wanted to enhance the search criteria to include the ability to search residences within a 5 mile, 10 mile, etc range. So the sql statement in MySQL which I tested first to test the sql structure returned correct results and looks like this:

Select * from address where longitude between (-74.654117 - .1/69) and (-74.654117 + .1/69) and latitude between (40.503010 - .1/69) and (40.503010 + .1/69);

However, when I take this same exact statement and translate into oxf:sql my submission fails. Here is the oxf:sql xpl file:

<p:config xmlns:p="http://www.orbeon.com/oxf/pipeline"
xmlns:sql="http://orbeon.org/oxf/xml/sql"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:oxf="http://www.orbeon.com/oxf/processors"
xmlns:xi="http://www.w3.org/2001/XInclude">

<p:param type="input" name="instance"/>
<p:param type="output" name="data"/>

<p:processor name="oxf:sql">
<p:input name="data" href="#instance"/>
<p:input name="config">
<sql:config>
<myQuery>
<sql:connection>
<sql:datasource>crimestat</sql:datasource>
<sql:execute>
<sql:query>
SELECT street, city, state, zip, name, OTN, longitude, latitude, investigation FROM address
where longitude
between (<sql:param type="xs:decimal" select="/myQuery/result/longitude"/> + <sql:param type="xs:decimal" select="/myQuery/result/distance") and (<sql:param type="xs:decimal" select="/myQuery/result/longitude"/> - <sql:param type="xs:decimal" select="/myQuery/result/distance") and
latitude between (<sql:param type="xs:decimal" select="/myQuery/result/latitude"/> + <sql:param type="xs:decimal" select="/myQuery/result/distance") and (<sql:param type="xs:decimal" select="/myQuery/result/latitude"/> - <sql:param type="xs:decimal" select="/myQuery/result/distance");
</sql:query>
<sql:result-set>
<sql:row-iterator>
<result>
<street>
<sql:get-column-value type="xs:string" column="street"/>
</street>
<city>
<sql:get-column-value type="xs:string" column="city"/>
</city>
<state>
<sql:get-column-value type="xs:string" column="state"/>
</state>
<zip>
<sql:get-column-value type="xs:string" column="zip"/>
</zip>
<name>
<sql:get-column-value type="xs:string" column="name"/>
</name>
<longitude>
<sql:get-column-value type="xs:decimal" column="longitude"/>
</longitude>
<latitude>
<sql:get-column-value type="xs:decimal" column="latitude"/>
</latitude>
<OTN>
<sql:get-column-value type="xs:string" column="OTN"/>
</OTN>
<precision/>
<investigation/>
<distance/>
</result>
</sql:row-iterator>
</sql:result-set>
</sql:execute>
</sql:connection>
</myQuery>
</sql:config>
</p:input>
<p:output name="data" ref="data"/>
</p:processor>

</p:config>

My instance being passed in is below:

<xforms:instance id="investigation">
<myQuery>
<result>
<street/>
<city/>
<state/>
<zip/>
<name/>
<OTN/>
<latitude/>
<longitude/>
<investigation/>
<crimeKey/>
<precision/>
<distance/>
</result>
</myQuery>
</xforms:instance>

I have the data types set to decimal in the model:

<xforms:bind nodeset="instance('investigation')/result/latitude" type="xs:decimal"/>
<xforms:bind nodeset="instance('investigation')/result/longitude" type="xs:decimal"/>
<xforms:bind nodeset="instance('investigation')/result/distance" type="xs:decimal"/>

Any help would be appreciated. I have tried hours worth of modifications to isolate the error before asking for help. The closest I come is when I remove all the <sql:param .... select="/myQuery/result/distance"/> statements and replace them with hard coded decimal values - then the submission succeeds.

The orbeon version I am using is: Orbeon Forms 3.7.0beta1.200808260135

Thank you again for any asistance someone may provide.

Brian

fl.schmitt(ops-users)

Re: oxf:sql query using between .... and

Brian,

> Any help would be appreciated. I have tried hours worth of modifications to
> isolate the error before asking for help. The closest I come is when I
> remove all the <sql:param .... select="/myQuery/result/distance"/>
> statements and replace them with hard coded decimal values - then the
> submission succeeds.

did you use debugging attributes to trace the input and how the query
looks like? I found it useful to debug _every_ pipeline or processor
input/output until i'm sure the inputs/outpus looks as they should. So,
before working on the SQL query, you should make sure that the input
instance containing the query values looks as it should.

If it does, you could try to check how the query looks like before it's
executed. I'm not sure if this can be done by adding a debug attribute
to the config input of the sql processor:

<p:input name="config" debug="sqlquery">...</p:input>

If it doesn't, you could try to log the query sql-server-side, or you
try to build the query using xslt.

hth
florian

--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
OW2 mailing lists service home page: http://www.ow2.org/wws

Brian_Steuhl

Re: Re: oxf:sql query using between .... and

Florian,

Thanks for the tip and in the future I will try that. As it turn outs, I just stepped through the parameters again this late last night putting in hard coded values for each param. Then one at a time, I replaced the hard coded values with the appropriate sql:param and in the end - all 8 sql:params finally worked. Do not know if it was just because it was a late night but back on the right track. And I will try the debug attribute as you point out in the future - thanks.

-----Florian Schmitt <[hidden email]> wrote: -----

To: [hidden email]
From: Florian Schmitt <[hidden email]>
Date: 02/03/2009 04:15AM
Subject: [ops-users] Re: oxf:sql query using between .... and

Brian,

> Any help would be appreciated. I have tried hours worth of modifications to
> isolate the error before asking for help. The closest I come is when I
> remove all the <sql:param .... select="/myQuery/result/distance"/>
> statements and replace them with hard coded decimal values - then the
> submission succeeds.

did you use debugging attributes to trace the input and how the query
looks like? I found it useful to debug _every_ pipeline or processor
input/output until i'm sure the inputs/outpus looks as they should. So,
before working on the SQL query, you should make sure that the input
instance containing the query values looks as it should.

If it does, you could try to check how the query looks like before it's
executed. I'm not sure if this can be done by adding a debug attribute
to the config input of the sql processor:

<p:input name="config" debug="sqlquery">...</p:input>

If it doesn't, you could try to log the query sql-server-side, or you
try to build the query using xslt.

hth
florian

--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: [hidden email]
For general help: [hidden email]
OW2 mailing lists service home page: http://www.ow2.org/wws

--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
OW2 mailing lists service home page: http://www.ow2.org/wws