Gossamer Forum
Home : Products : Gossamer Links : Discussions :

Repair Tables in Oracle doesn't work!

Quote Reply
Repair Tables in Oracle doesn't work!
Hello, All.

I've got a trouble. My LinksSQL 2.0.4 is on Oracle8.1.7, and it doesn't repair my tables. I even know why: Oracle doesn't know such SQL words like LEFT OUTER JOIN, and function build_orphan_check use it!

Look at the error i've got:

GT::SQL::error = Failed to prepare query: '
SELECT wdLinks.Title, wdLinks.ID
FROM wdLinks LEFT OUTER JOIN wdCatLinks ON wdCatLinks.LinkID = wdLinks.ID
WHERE (wdCatLinks.LinkID IS NULL)

Instead of it, we should use, for example, the following SQL-operator in Oracle:

select title, id
from wdlinks
where not exists (select id from wdcatlinks where wdlinks.id = wdcatlinks.id)

but we cannot execute this operator with GT::SQL::Table...

Can you help us?

With respect,
Anton Permyakov


Quote Reply
Re: Repair Tables in Oracle doesn't work! In reply to
Hi,

Can you confirm if the following SQL works for you:

SELECT wdLinks.Title, wdLinks.ID
FROM wdLinks, wdCatLinks
WHERE wdLinks.ID = wdCatLinks.LinkID(+)
AND (wdCatLinks.LinkID IS NULL)

Our Oracle book seems to think that should work. If it does, I'll update our Oracle driver so it handles this.

Cheers,

Alex

--
Gossamer Threads Inc.
Quote Reply
Yes it works. In reply to
Hi Alex,

Yes, it works!
Now, i think, we need slightly change all the functions like the build_orphan_check? I mean the following strings:

my $sth = $rel->select ('left_join', $sel, GT::SQL::Condition->new('LinkID', 'IS', \'NULL'));

Am i right, or LinksSQL will automaticly convert 'left_join' to something like '(+)' for Oracle?

Thank you very much,
Anton

Quote Reply
Re: Yes it works. In reply to
Hi,

In GT/SQL/Driver/ORACLE.pm in sub _prepare_select, can you add:

Code:
# No LEFT OUTER JOIN, reformat.
$query =~ s#FROM\s*(\w+)\s*LEFT OUTER JOIN\s*(\w+)\s*ON\s*(\w+)\s*=\s*(\w+)\s*WHERE\s*#
my ($t1, $t2, $c1, $c2) = ($1, $2, $3, $4);
if (index($c1, "$t1.") == 0) { "FROM $t1, $t2 WHERE $c1 = $c2(+)"; }
else { "FROM $t1, $t2 WHERE $c2 = $c1(+)" }
#;
It's not tested, but looks like it should work. This will convert all left outer join queries to the (+) syntax oracle likes.

Cheers,

Alex

--
Gossamer Threads Inc.
Quote Reply
Thank you! In reply to
Alex hi,

Thank you a lot!
I paste the code you gave me, it didn't work complitely, but i cought the idia, changed it little, and now it work!

Here it is:

# No LEFT OUTER JOIN, reformat.

$query =~ s#FROM\s*(\w+)\s*LEFT OUTER JOIN\s*(\w+)\s*ON\s*([\w\.]+)\s*=\s*([\w\.]+)\s*WHERE\s*#
my ($t1, $t2, $c1, $c2) = ($1, $2, $3, $4);
if (index($c1, "$t1.") == 0) { "FROM $t1, $t2 WHERE $c1 = $c2(+) AND "; }
else { "FROM $t1, $t2 WHERE $c2 = $c1(+) AND " }
#ie;

With respect and many thanks,

Anton


Quote Reply
Re: Thank you! In reply to
Oops, sorry about that! Glad you got it working!

Cheers,

Alex

--
Gossamer Threads Inc.