"{ OJ " group outer joins in queries from OpenOffice.org?

Hi,

This was discussed on the pgsql-sql list. Tom Lane asked if someone knows
about this, no answer at that list, so I guess I'll ask here:

OpeoOffice adds "{ OJ " .., "}" around outer joins. Apparently, as Tom
recalls, the ODBC standard expects this, since ODBC rephrases the query to
be compatible with different db servers. I don't think this construct
exists in JDBC? Anyone knows?

Two bug reports on OO.org with the same matter, only seem to involve
postgresql jdbc driver?

http://www.openoffice.org/issues/show_bug.cgi?id=14296
http://www.openoffice.org/issues/show_bug.cgi?id=22355

See (parts of) my original mail below:

>> Palle Girgensohn <girgen(at)pingpong(dot)net> writes:
>>> Here's their code snippet (from
>>> oo_1.1_src/dbaccess/source/ui/querydesign/QueryDesignView.cxx):
>>
>>> if(aJoin.getLength())
>>> {
>>> ::rtl::OUString aStr = ::rtl::OUString::createFromAscii("{ OJ ");
>>> aStr += aJoin;
>>> aStr += ::rtl::OUString::createFromAscii(" },");
>>> aTableListStr += aStr;
>>> }
>>
>> Now that I look at it, I bet that they are expecting the ODBC driver to
>> translate "{ OJ ... }" into the appropriate syntax for the database
>> (either SQL standard or not, depending on the DB :-().
>>
>> There are a number of ODBC brace-constructs that our driver knows how to
>> translate, but I don't think that's one of them. You should bug the
>> ODBC guys about it.
>>
>> regards, tom lane

Palle Girgensohn <girgen(at)pingpong(dot)net> writes:
> Hi!
>
> I just tried using openoffice to create a SQL query, but it failed when I
> tried using left outer joins. The contructed query from OO contains curly
> braces to group things. I've never seen this construct before. Is it OO
> doing bad things, or does postgresql not understand it? It's on pg-7.3.4.
> Perhaps 7.4 behaves differently?
>
> SELECT "personer"."foretag", "personer"."fnamn", "personer"."enamn",
> "befattningnamn"."namn" FROM { OJ "public"."befattning" "befattning"
> LEFT OUTER JOIN "public"."personer" "personer" ON
> "befattning"."personid" = "personer"."personid" }, "public"."kommentar"
> "kommentar", "public"."personer" "personer", "public"."befattning"
> "befattning", "public"."befattningnamn" "befattningnamn" WHERE 0 = 1
>
>
> ERROR: parser: parse error at or near "{" at character 100
>
> (The WHERE 0 = 1 seems to be OO's way of checking the query syntax.)
>
> Thanks
> Palle

Definitely not, we do not intercept the query and modify it other than
escaping some characters.

Why would they think that JDBC is similiar to ODBC?

Dave
On Wed, 2003-11-12 at 22:50, Palle Girgensohn wrote:
> Hi,
>
> This was discussed on the pgsql-sql list. Tom Lane asked if someone knows
> about this, no answer at that list, so I guess I'll ask here:
>
> OpeoOffice adds "{ OJ " .., "}" around outer joins. Apparently, as Tom
> recalls, the ODBC standard expects this, since ODBC rephrases the query to
> be compatible with different db servers. I don't think this construct
> exists in JDBC? Anyone knows?
>
> Two bug reports on OO.org with the same matter, only seem to involve
> postgresql jdbc driver?
>
> http://www.openoffice.org/issues/show_bug.cgi?id=14296
> http://www.openoffice.org/issues/show_bug.cgi?id=22355
>
> See (parts of) my original mail below:
>
> >> Palle Girgensohn <girgen(at)pingpong(dot)net> writes:
> >>> Here's their code snippet (from
> >>> oo_1.1_src/dbaccess/source/ui/querydesign/QueryDesignView.cxx):
> >>
> >>> if(aJoin.getLength())
> >>> {
> >>> ::rtl::OUString aStr = ::rtl::OUString::createFromAscii("{ OJ ");
> >>> aStr += aJoin;
> >>> aStr += ::rtl::OUString::createFromAscii(" },");
> >>> aTableListStr += aStr;
> >>> }
> >>
> >> Now that I look at it, I bet that they are expecting the ODBC driver to
> >> translate "{ OJ ... }" into the appropriate syntax for the database
> >> (either SQL standard or not, depending on the DB :-().
> >>
> >> There are a number of ODBC brace-constructs that our driver knows how to
> >> translate, but I don't think that's one of them. You should bug the
> >> ODBC guys about it.
> >>
> >> regards, tom lane
>
> Palle Girgensohn <girgen(at)pingpong(dot)net> writes:
> > Hi!
> >
> > I just tried using openoffice to create a SQL query, but it failed when I
> > tried using left outer joins. The contructed query from OO contains curly
> > braces to group things. I've never seen this construct before. Is it OO
> > doing bad things, or does postgresql not understand it? It's on pg-7.3.4.
> > Perhaps 7.4 behaves differently?
> >
> > SELECT "personer"."foretag", "personer"."fnamn", "personer"."enamn",
> > "befattningnamn"."namn" FROM { OJ "public"."befattning" "befattning"
> > LEFT OUTER JOIN "public"."personer" "personer" ON
> > "befattning"."personid" = "personer"."personid" }, "public"."kommentar"
> > "kommentar", "public"."personer" "personer", "public"."befattning"
> > "befattning", "public"."befattningnamn" "befattningnamn" WHERE 0 = 1
> >
> >
> > ERROR: parser: parse error at or near "{" at character 100
> >
> > (The WHERE 0 = 1 seems to be OO's way of checking the query syntax.)
> >
> > Thanks
> > Palle
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
>

--On torsdag, november 13, 2003 08.21.31 -0500 Dave Cramer
<pg(at)fastcrypt(dot)com> wrote:

> Definitely not, we do not intercept the query and modify it other than
> escaping some characters.

Cool. As you can see in the issuezilla records, they state that they use
non-standard jdbc. just too true... :-/

> Why would they think that JDBC is similiar to ODBC?

I don't think they do, really, they just made it work for ODBC and didn't
think enough about JDBC...

/Palle

>
> Dave
> On Wed, 2003-11-12 at 22:50, Palle Girgensohn wrote:
>> Hi,
>>
>> This was discussed on the pgsql-sql list. Tom Lane asked if someone
>> knows about this, no answer at that list, so I guess I'll ask here:
>>
>> OpeoOffice adds "{ OJ " .., "}" around outer joins. Apparently, as Tom
>> recalls, the ODBC standard expects this, since ODBC rephrases the query
>> to be compatible with different db servers. I don't think this
>> construct exists in JDBC? Anyone knows?
>>
>> Two bug reports on OO.org with the same matter, only seem to involve
>> postgresql jdbc driver?
>>
>> http://www.openoffice.org/issues/show_bug.cgi?id=14296
>> http://www.openoffice.org/issues/show_bug.cgi?id=22355
>>
>> See (parts of) my original mail below:
>>
>> >> Palle Girgensohn <girgen(at)pingpong(dot)net> writes:
>> >>> Here's their code snippet (from
>> >>> oo_1.1_src/dbaccess/source/ui/querydesign/QueryDesignView.cxx):
>> >>
>> >>> if(aJoin.getLength())
>> >>> {
>> >>> ::rtl::OUString aStr = ::rtl::OUString::createFromAscii("{ OJ ");
>> >>> aStr += aJoin;
>> >>> aStr += ::rtl::OUString::createFromAscii(" },");
>> >>> aTableListStr += aStr;
>> >>> }
>> >>
>> >> Now that I look at it, I bet that they are expecting the ODBC driver
>> >> to translate "{ OJ ... }" into the appropriate syntax for the database
>> >> (either SQL standard or not, depending on the DB :-().
>> >>
>> >> There are a number of ODBC brace-constructs that our driver knows how
>> >> to translate, but I don't think that's one of them. You should bug
>> >> the ODBC guys about it.
>> >>
>> >> regards, tom lane
>>
>> Palle Girgensohn <girgen(at)pingpong(dot)net> writes:
>> > Hi!
>> >
>> > I just tried using openoffice to create a SQL query, but it failed
>> > when I tried using left outer joins. The contructed query from OO
>> > contains curly braces to group things. I've never seen this construct
>> > before. Is it OO doing bad things, or does postgresql not understand
>> > it? It's on pg-7.3.4. Perhaps 7.4 behaves differently?
>> >
>> > SELECT "personer"."foretag", "personer"."fnamn", "personer"."enamn",
>> > "befattningnamn"."namn" FROM { OJ "public"."befattning" "befattning"
>> > LEFT OUTER JOIN "public"."personer" "personer" ON
>> > "befattning"."personid" = "personer"."personid" },
>> > "public"."kommentar" "kommentar", "public"."personer" "personer",
>> > "public"."befattning" "befattning", "public"."befattningnamn"
>> > "befattningnamn" WHERE 0 = 1
>> >
>> >
>> > ERROR: parser: parse error at or near "{" at character 100
>> >
>> > (The WHERE 0 = 1 seems to be OO's way of checking the query syntax.)
>> >
>> > Thanks
>> > Palle
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 8: explain analyze is your friend
>>
>>
>

On 13/11/2003 13:21 Dave Cramer wrote:
> Definitely not, we do not intercept the query and modify it other than
> escaping some characters.
>
> Why would they think that JDBC is similiar to ODBC?

Its actually covered in section 13.4 of the JDBC 3 specification.

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants |
http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+---------------------------------------------+

Dave Cramer wrote:
> Definitely not, we do not intercept the query and modify it other than
> escaping some characters.

Not entirely true. The driver does support the ODBC escape syntax for
dates and stored procedures/functions but not for outer joins, likes and
scalar functions.

>
> Why would they think that JDBC is similiar to ODBC?
>

Perhaps because the JDBC spec says so:

11.1 SQL Escape Syntax
JDBC supports the same DBMS-independent escape syntax as ODBC for stored
procedures, scalar functions, dates, times, and outer joins. A driver
maps this escape syntax into DBMSspecific syntax, allowing portability
of application programs that require these features. The
DBMS-independent syntax is based on an escape clause demarcated by curly
braces and a keyword:
{keyword ... parameters ...}
This ODBC-compatible escape syntax is in general not the same as has
been adopted by ANSI in SQL-2 Transitional Level for the same
functionality. In cases where all of the desired DBMSs support the
standard SQL-2 syntax, the user is encouraged to use that syntax instead
of these escapes. When enough DBMSs support the more advanced SQL-2
syntax and semantics these escapes should no longer be necessary.

The logic that is currently supported by the driver (i.e. the date
stuff) is implemented in AbstractJdbc1Statement.replaceProcessing().
This would be where handling the outer join syntax should be added, if
anyone so desired.

thanks,
--Barry

--On torsdag, november 13, 2003 08.48.17 -0800 Barry Lind
<blind(at)xythos(dot)com> wrote:

> The logic that is currently supported by the driver (i.e. the date stuff)
> is implemented in AbstractJdbc1Statement.replaceProcessing(). This would
> be where handling the outer join syntax should be added, if anyone so
> desired.

Ah, cool. And handling this should simple be done by removing "{ OJ" and
"}", or is there more to it?

/Palle

Palle Girgensohn <girgen(at)pingpong(dot)net> writes:
> Ah, cool. And handling this should simple be done by removing "{ OJ" and
> "}", or is there more to it?

I think you might want to translate them to "(" and ")"; not sure if
there are cases where it will matter or not, but it can't hurt.

regards, tom lane

Barry Lind wrote:
> Not entirely true. The driver does support the ODBC escape syntax for
> dates and stored procedures/functions but not for outer joins, likes and
> scalar functions.
Would a patch that add support for some scalar functions have a chance
to be accepted ?