SQL Transformer generates illegal element names

["Schweigl, Johann" <Jo...@softwareag.com>]

Hi all,

 

recently I ran into a problem with SQL Transformer. The column names
returned from the database are used for naming the children of sql:row, but
those names can be something like "count(distinct xxx)" or 'SYS$xxxx', which
violates the wellformed conditions of the return document.

 

Is there any implementation of SQLTransformer that generates <column
name="xxx"> instead of <xxx>, which would solve the problem?  For predefined
statements I can control the SQL that is passed to the transformer, but I
run into big hassle  when letting the user enter SQL directly.

 

Thx,

Johann

Re: SQL Transformer generates illegal element names

[Alexander Schatten <al...@gmx.at>]

Schweigl, Johann wrote:

> Hi all,
>
>  
>
> recently I ran into a problem with SQL Transformer. The column names 
> returned from the database are used for naming the children of 
> sql:row, but those names can be something like "count(distinct xxx)" 
> or 'SYS$xxxx', which violates the wellformed conditions of the return 
> document.
>
>  
>
> Is there any implementation of SQLTransformer that generates <column 
> name="xxx"> instead of <xxx>, which would solve the problem?  For 
> predefined statements I can control the SQL that is passed to the 
> transformer, but I run into big hassle  when letting the user enter 
> SQL directly.
>
>  
>

Hi Johnny,

As far as I know, there is no such transformer;

but alternatively you could use xsp with esql, which is not far more 
complex, but offers an individual mapping from columns to xml-tag-names.


Alex


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org

Re: SQL Transformer generates illegal element names

[Jorg Heymans <jh...@domek.be>]

> 
> Not necessarily, since you don't have to parse the entire SQL-grammar (that
> would have been stupid in XSLT, not brave... ;)
> This is a simplified, untested template-match that could do the trick:

ah, i did not know that the sql statement was available in such form in 
the SQLTransformer.
> 
> select="str:tokenize(substring-before(substring-after($sql, 'SELECT'),
> 'FROM'),',')">

This fails on embedded comma's in the SQL.


But we're digressing, let us know how you solved it Johann :)

Jorg



---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org

Re: SQL Transformer generates illegal element names

[Askild Aaberg Olsen <as...@xangeli.com>]

> > This is a standard SQL-feature, that should work in any 
> SQL-compliant 
> > database (at least I know it works in MSSQL and DB2 also). 
> But I guess 
> > that the problem is that Johann lets the user enter SQL directly, 
> > loosing control of whats submitted to the DB. (which seems 
> like a huge 
> > security risk, but I guess that this is adressed elsewhere in the 
> > application...)
> > 
> > Johann - if you are brave ;) - you could use XSLT to parse the 
> > submitted SQL, and then extend the SELECT-clause with aliases?
> :) indeed he would need to be pretty darn brave to parse a 
> SQL statement 
> using XSLT.
> 
> Less braveness though required with antlr, i'm sure there are 
> few SQL92 
> grammars around that suit his needs.
> 
> Alternatively there are various opensource java relational databases 
> around, they must have already done the hard work.
>
> Jorg

Not necessarily, since you don't have to parse the entire SQL-grammar (that
would have been stupid in XSLT, not brave... ;)
This is a simplified, untested template-match that could do the trick:

<xsl:template match="sql:query">
	<xsl:copy>
		<xsl:copy-of select="@*"/>
		<!-- ensure CASE on keywords -->
		<xsl:variable name="sql" select="translate(., 'fromselect' ,
'FROMSELECT')"/>
		<xsl:text>SELECT</xsl:text>
		<!-- Go trough every select column -->
		<xsl:for-each
select="str:tokenize(substring-before(substring-after($sql, 'SELECT'),
'FROM'),',')">
			<xsl:value-of select="."/>
			<!-- Check if it contains illegal characters for
XML-names -->
			<xsl:if test="string-length(translate(.,'all
characters not allowed in XML NCNAME')) != string-length(.)">
				<!-- If so, set the column name to x1, x2,
etc. -->
				<xsl:text> AS x</xsl:text>
				<xsl:value-of select="position()"/>
			</xsl:if>
			<xsl:if test="position() != last()">,</xsl:if>
		</xsl:for-each>
		<!-- Output  the rest of the SQL -->
		<xsl:text> FROM</xsl:text>
		<xsl:value-of select="substring-after($sql, 'FROM')"/>
	</xsl:copy>
</xsl:template>

Askild


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org

Re: SQL Transformer generates illegal element names

[Jorg Heymans <jh...@domek.be>]

<snipped>
> 
> This is a standard SQL-feature, that should work in any SQL-compliant
> database (at least I know it works in MSSQL and DB2 also).
> But I guess that the problem is that Johann lets the user enter SQL
> directly, loosing control of whats submitted to the DB.
> (which seems like a huge security risk, but I guess that this is adressed
> elsewhere in the application...)
> 
> Johann - if you are brave ;) - you could use XSLT to parse the submitted
> SQL, and then extend the SELECT-clause with aliases?
:) indeed he would need to be pretty darn brave to parse a SQL statement 
using XSLT.

Less braveness though required with antlr, i'm sure there are few SQL92 
grammars around that suit his needs.

Alternatively there are various opensource java relational databases 
around, they must have already done the hard work.

> 
> Askild
Jorg


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org

Re: SQL Transformer generates illegal element names

[Askild Aaberg Olsen <as...@xangeli.com>]

> > recently I ran into a problem with SQL Transformer. The column names
> > returned from the database are used for naming the children 
> of sql:row, 
> > but those names can be something like "count(distinct xxx)" or 
> > 'SYS$xxxx', which violates the wellformed conditions of the 
> return document.
> > 
> alias your columns as a temporary workaround?
> ie "select sysdate-1 as mydate from dual;"
> I know oracle can do this , dunno about other databases.

This is a standard SQL-feature, that should work in any SQL-compliant
database (at least I know it works in MSSQL and DB2 also).
But I guess that the problem is that Johann lets the user enter SQL
directly, loosing control of whats submitted to the DB.
(which seems like a huge security risk, but I guess that this is adressed
elsewhere in the application...)

Johann - if you are brave ;) - you could use XSLT to parse the submitted
SQL, and then extend the SELECT-clause with aliases?

Askild
-


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org

Re: SQL Transformer generates illegal element names

[Jorg Heymans <jh...@domek.be>]

> 
> recently I ran into a problem with SQL Transformer. The column names 
> returned from the database are used for naming the children of sql:row, 
> but those names can be something like "count(distinct xxx)" or 
> 'SYS$xxxx', which violates the wellformed conditions of the return document.
> 
alias your columns as a temporary workaround?
ie "select sysdate-1 as mydate from dual;"
I know oracle can do this , dunno about other databases.


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org