Apache OpenOffice (AOO) Bugzilla – Issue 22355
left outer joins fail w/ JDBC
Last modified: 2013-08-07 15:45:41 UTC
When using JDBC connecting to postgresql-7.3.4, the Query Designer creates bad SQL when connecting using JDBC. I have gotten info indicating the this is correct for ODBC, where the driver modifies the SQL depending on how the db server handles this kind of SQL construct. Problem is when using left outer joins. The SQL created contains ... FROM { OJ table_a alias_a left outer join table_b alias_b } the "{ OJ" part is plain wrong SQL, but as I said, is probably rewritten by the ODBC driver. I want to use JDBC instead, since it is soo much easier, esp on unix. This patch seems to fix it for JDBC, but may break ODBC? --- dbaccess/source/ui/querydesign/QueryDesignView.cxx.orig Tue Nov 11 16:57:22 2003 +++ dbaccess/source/ui/querydesign/QueryDesignView.cxx Tue Nov 11 16:57:48 2003 @@ -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; } } A good fix probably includes conditionally handling both JDBC and ODBC SQL-statements, I guess? FreeBSD 4.9-RELEASE, postgresq-7.3.4, se-openoffice-1.1.0_1 from FreeBSD ports collection.
Hi, send to the right developer, set target, confirm. please see also issue 14296 bye Marc
Hi, we have to investigate if the normal SQL syntax can be used for all databases now. E.g. from a outer join b on a.id = b.id Could please test if this also works for pstregesql? Every help is welcome. Thanks in advance. Best regards, Ocke
Girgen, thanks for this patch :). Additionally to what Ocke already said: Possible solutions I see: - make this dependent on the data source. There already are a number of settings per data source (stored in the Info property) which define some behavior. We could introduce an additional flag/enum/whatever which specifies how to create JOIN clauses. This could be defaulted so that for ODBC, the OJ-notation is used, and for everything else, the other notation. (Ocke, does our parser understand the second notation?) - well, always use your notation :), but only after we're *sure* that it works with all relevant databases. Since being sure is really hard to achieve (since there are databases which we cannot test, not to talk about driver versions and such), I would propose the first way. Would you be interested to extend your patch this way? We could give you pointers to the code in question, plus any additional help you need for this. Would be great :)
You must say "left", "right" or "full" as well, but I guess that's what you meant? from a left outer join b on a.id = b.id OK from a right outer join b on a.id = b.id OK from a full outer join b on a.id = b.id OK from a outer join b on a.id = b.id <--- BAD this is with postgresql-7.1 or higher, outer joins where introduced in 7.1, release 2001-04-13. Syntax has always been this one. On the other hand, I got info that the JDBC standard allows for constructs like the one discussed here: »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.« This indicates that postgresql's jdbc implementation is responsible for it, unless we now agree that "enough DBMSs support the more advanced SQL-2 syntax and semantics". Postgresql does. :)
I don't think that "enough" JDBC drivers support the ODBC syntax - for this term the percentage should be very near to 100 :). I think we should introduce a per-data source option to switch of the ODBC syntax, and by default use it for JDBC data sources.
.
argh s/ODBC/SQL-2/
Basically, what I wanted to say: Would be nice if we had an option to use the SQL-2 syntax instead of the ODBC syntax - I suppose for quite a while, there will be enough drivers supporting only one of both, so we need to work with both.
set this one as duplicate *** This issue has been marked as a duplicate of 14296 ***
change subcomponent to 'none'
Closing it.