RE: Synonym, stored proc, parameter

I believe there are some problems with synonyms of tables on remote databases. Maybe you can try to reproduce the bug when referring to a table in the same database. Frido ------------------------------------------------------------------------ - Frido van Orden FAA Partners BV Planetenbaan 117 3606 AK Maarssen The Netherlands Phone: +31-346-587076 Fax: +31-346-587086 Email: fridoo@faapartners.com ------------------------------------------------------------------------ - } -----Original Message----- } From: Jon Myatt [SMTP:jon@jonners.cix.co.uk] } Sent: Saturday, November 01, 1997 6:33 AM } To: informix-list@rmy.emory.edu } Subject: Synonym, stored proc, parameter } } Hi all. } } This one's really bugging me. } } I have a stored procedure which I pass a parameter to. } } I then select some rows from one table to insert into } another, using the passed parameter in the select } statement so it's value gets inserted too. } This worked fine until I changed the table I'm selecting } from to be a synonym to a table in another instance. } } I now get a 201 syntax error when I try and execute the } procedure. It's as if the the SQL is passed to the other } instance without "expanding" the reference to the passed } parameter, so (I guess) the other instance is wrongly } trying to select the item as if it's a column. Here's an example, and } it } doesn't matter if I use an absolute } reference to the remote table or a synonym: } } CREATE PROCEDURE friday1(passed_instance_id CHAR(8)) } } INSERT INTO sometable (instance_id, week) } SELECT } passed_instance_id, } a_week_no } FROM other@xxxxtcp:remote_table ; -- Could be synonym } } END PROCEDURE; } } Does anyone have any ideas? } } This is On-Line 7.23UC1 on AIX 4.2.1 by the way. } } Cheers, } } Jon Myatt } IBM OATD UK