Postgres Pro
Downloads
Home   >   mailing lists

Thread: curly braces to group outer joins in queries from OpenOffice.org?

curly braces to group outer joins in queries from OpenOffice.org?

From
Palle Girgensohn
Date:
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

Re: curly braces to group outer joins in queries from OpenOffice.org?

From
Tom Lane
Date:
Palle Girgensohn <girgen@pingpong.net> writes:
> 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.

The SQL standard has never heard of it, either.  Parentheses would be
correct here.
        regards, tom lane

Re: curly braces to group outer joins in queries from

From
Palle Girgensohn
Date:
What about the `OJ'?

FROM ( OJ table alias left join table2 alias2 )

OJ seems plain wrong, right?

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;
 
}

You mean simple parathesis, no `OJ' would be OK?

like:

@@ -1141,9 +1141,9 @@
if(aJoin.getLength()){
-    ::rtl::OUString aStr = ::rtl::OUString::createFromAscii("{ OJ ");
+    ::rtl::OUString aStr = ::rtl::OUString::createFromAscii("( ");    aStr += aJoin;
-    aStr += ::rtl::OUString::createFromAscii(" },");
+    aStr += ::rtl::OUString::createFromAscii(" ),");    aTableListStr += aStr;}


Thanks for the prompt reply, as usual!

Regards,
Palle

--On tisdag, november 11, 2003 09.39.52 -0500 Tom Lane <tgl@sss.pgh.pa.us> 
wrote:

> Palle Girgensohn <girgen@pingpong.net> writes:
>> 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.
>
> The SQL standard has never heard of it, either.  Parentheses would be
> correct here.
>
>             regards, tom lane

Re: curly braces to group outer joins in queries from OpenOffice.org?

From
Tom Lane
Date:
Palle Girgensohn <girgen@pingpong.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

Re: curly braces to group outer joins in queries from

From
Palle Girgensohn
Date:
Only, I set up postgreql to connect using JDBC... :(

So, the bug is that OO expects an ODBC connection, and I use JDBC... Seems 
logical. I'll go ask the ODBC guys. Thanks for you help,

/Palle

--On tisdag, november 11, 2003 11.28.57 -0500 Tom Lane <tgl@sss.pgh.pa.us> 
wrote:

> Palle Girgensohn <girgen@pingpong.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

Re: curly braces to group outer joins in queries from OpenOffice.org?

From
Tom Lane
Date:
Palle Girgensohn <girgen@pingpong.net> writes:
> Only, I set up postgreql to connect using JDBC... :(
> So, the bug is that OO expects an ODBC connection, and I use JDBC... Seems 
> logical. I'll go ask the ODBC guys. Thanks for you help,

I do not know if the JDBC spec includes ODBC-style brace constructs
or not; if it does, then there's an omission in our JDBC driver.

Anyone know?
        regards, tom lane

Re: curly braces to group outer joins in queries from OpenOffice.org?

From
Tõnu Põld
Date:
Hi

AFAK jdbc 3.0 requires driver to support curly braces for escape sequences.

For example:
{d yyy-mm-dd}
{t hh:mm:ss}
{ts yyyy-mm-dd hh:mm:ss[.f...]}
{oj outer-join}
{fn scalar-function}

Chapter 6.2 states that drivers must support escape syntax.
Chapter 13.4 defines escape syntax.

http://java.sun.com/products/jdbc/download.html

Regards,
Tõnu Põld