SQL processor problem

[John Carroll <jc...@micromuse.com>]

Hi,

I am trying to use the Cocoon SQL processor to access a mysql db with
the following query :

<query connection="mysql_connection">
  select MAX(TimeStamp), columnx, columny  from testdb.testtable GROUP
BY TimeStamp
 </query>

and am getting the error

<sqlerror message="DOM002 Illegal character"></sqlerror>


the query works fine when I take out MAX(TimeStamp) bit

Has anyone else seen this or knows of a workaround?

thanks,

John.

Re: SQL processor problem

[Graham Fyffe <gr...@ussurg.com>]

John - I don't know mysqldb syntax but I would say that the syntax is not
valid SQL. I don't know what you are trying to acheive but perhaps the
syntax you want is

select columnx,columny, MAX(TimeStamp) from testdb.testtable group by
columnx,columny

John Carroll wrote:

> Hi,
>
> I am trying to use the Cocoon SQL processor to access a mysql db with
> the following query :
>
> <query connection="mysql_connection">
>   select MAX(TimeStamp), columnx, columny  from testdb.testtable GROUP
> BY TimeStamp
>  </query>
>
> and am getting the error
>
> <sqlerror message="DOM002 Illegal character"></sqlerror>
>
> the query works fine when I take out MAX(TimeStamp) bit
>
> Has anyone else seen this or knows of a workaround?
>
> thanks,
>
> John.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: cocoon-users-unsubscribe@xml.apache.org
> For additional commands, e-mail: cocoon-users-help@xml.apache.org

Re: SQL processor problem

[Thorbjoern Ravn Andersen <ra...@mip.sdu.dk>]

On Tue, 4 Apr 2000, Donald Ball wrote:

> Sure. (*) aren't valid element name identifiers. if someone can suggest a
> better way to accomodate this sort of thing, by all means, i'm all ears.
> personally, i think the sql AS syntax is a perfectly fine way to do it,
> but i understand that there may be some people out there using shared
> stored procedures or whatever, where they cannot change the names of the
> columns of the resultset.

The reason is not obvious from the error message, hence my explanation.

I would suggest an optional attribute like 'map-invalid-characters-to="_"'
which in this case would map to MAX_RESULT_, and in the count(*) case to
count___.  

In this way you can at least get to the result.

Another approach might also be - as we discussed in email - to have a mode
where the column name is returned as an attribute to a tag instead of the
tag itself.  In this way a quick-and-dirty table could be returned as

<table>
<tr>
<td column="a">data</td><td column="max(result)">data</td>
...

etc.

I believe all characters valid in an SQL query is valid as attribute
values too?

-- 
  Thorbjørn Ravn Andersen               "...plus...Tubular Bells!"
  http://www.mip.sdu.dk/~ravn

Re: SQL processor problem

[Donald Ball <ba...@webslingerZ.com>]

On Tue, 4 Apr 2000, Thorbjoern Ravn Andersen wrote:

> On Mon, 3 Apr 2000, John Carroll wrote:
> 
> > the query works fine when I take out MAX(TimeStamp) bit
> > 
> > Has anyone else seen this or knows of a workaround?
> 
> Use "MAX(TimeStamp) as max_time_stamp" to give the field another
> name.  SQL Processor appears to convert columnnames uncriticaly to tags,
> and the DOM does not take well to a tag named <MAX(TimeStamp)>.
> 
> I hit the same problem with "count(*)".

Sure. (*) aren't valid element name identifiers. if someone can suggest a
better way to accomodate this sort of thing, by all means, i'm all ears.
personally, i think the sql AS syntax is a perfectly fine way to do it,
but i understand that there may be some people out there using shared
stored procedures or whatever, where they cannot change the names of the
columns of the resultset.

- donald

Re: SQL processor problem

[Thorbjoern Ravn Andersen <ra...@mip.sdu.dk>]

On Mon, 3 Apr 2000, John Carroll wrote:

> the query works fine when I take out MAX(TimeStamp) bit
> 
> Has anyone else seen this or knows of a workaround?

Use "MAX(TimeStamp) as max_time_stamp" to give the field another
name.  SQL Processor appears to convert columnnames uncriticaly to tags,
and the DOM does not take well to a tag named <MAX(TimeStamp)>.

I hit the same problem with "count(*)".

Best regards,
-- 
  Thorbjørn Ravn Andersen               "...plus...Tubular Bells!"
  http://www.mip.sdu.dk/~ravn

Re: SQL processor problem

[Donald Ball <ba...@webslingerZ.com>]

On Mon, 3 Apr 2000, John Carroll wrote:

> Hi,
> 
> I am trying to use the Cocoon SQL processor to access a mysql db with
> the following query :
> 
> <query connection="mysql_connection">
>   select MAX(TimeStamp), columnx, columny  from testdb.testtable GROUP
> BY TimeStamp
>  </query>

try this:

  select max(timestamp) as max_timestamp, columnx, columny from testdb.testtable group by timestamp

- donald