Oninit cdi / Archive
Talk to an expert →

556: Cannot create, drop, or modify an object that is external to current database. ???

Posted in 2007

Summary

Poster found that calling a stored procedure in another database (EXECUTE PROCEDURE b:bb()) fails with error -556 ("Cannot create, drop, or modify an object that is external to current database", ISAM -111) when that remote procedure does SELECT ... INTO TEMP. A suggested workaround was to do the SELECT ... INTO TEMP locally, qualifying the remote table name instead. Replies cited the finderr text that you can only read from an external database, but the poster objected that a temp table isn't in the remote database and noted DROP TABLE works across databases. No definitive explanation was reached; one reply simply argued the behaviour of temp tables across database switches is undocumented/undefined. No resolution recorded.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: Stored Procedures & SPL, Error Codes & Troubleshooting

Denis.Zhuravlov@gmail.com — — source: Usenet: comp.databases.informix

database b; CREATE PROCEDURE BB() select * from table(set{1}) into temp t12345 with no log;END PROCEDURE; database a; CREATE PROCEDURE AA() EXECUTE PROCedure b:bb();END PROCEDURE; database a; EXECUTE PROCEDURE AA(); 556: Cannot create, drop, or modify an object that is external tocurrent database. 111: ISAM error: no record found. IBM Informix Dynamic Server Version 10.00.UC3

↪ replying to Denis.Zhuravlov@gmail.com
Denis.Zhuravlov@gmail.com — — source: Usenet: comp.databases.informix

database b; CREATE PROCEDURE BB() select * from table(set{1}) into temp t12345 with no log;END PROCEDURE; database a; EXECUTE PROCedure b:bb();556: Cannot create, drop, or modify an object that is external tocurrent database. 111: ISAM error: no record found. IBM Informix Dynamic Server Version 10.00.UC3

↪ replying to Denis.Zhuravlov@gmail.com
Christine Normile — — source: Usenet: comp.databases.informix

> database a; > CREATE PROCEDURE AA() > select * from b:table(set{1}) into temp t12345 with no log;> END PROCEDURE; > > database a; > EXECUTE PROCEDURE AA(); On Aug 14, 2007, at 3:20 AM, Denis.Zhuravlov@gmail.com wrote: > database b; > CREATE PROCEDURE BB() > select * from table(set{1}) into temp t12345 with no log;> END PROCEDURE; > > database a; > CREATE PROCEDURE AA() > EXECUTE PROCedure b:bb();> END PROCEDURE; > > database a; > EXECUTE PROCEDURE AA();> > 556: Cannot create, drop, or modify an object that is external to> current database. > 111: ISAM error: no record found.> > IBM Informix Dynamic Server Version 10.00.UC3 > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list

↪ replying to Christine Normile
Denis.Zhuravlov@gmail.com — — source: Usenet: comp.databases.informix

> > database a; > > CREATE PROCEDURE AA() > > select * from b:table(set{1}) into temp t12345 with no log;> > END PROCEDURE; > > > database a; > > EXECUTE PROCEDURE AA(); I don't need workaround. I want to know why my example not work.

↪ replying to Denis.Zhuravlov@gmail.com
Claus Samuelsen — — source: Usenet: comp.databases.informix

Denis.Zhuravlov@gmail.com wrote: >>> database a; >>> CREATE PROCEDURE AA() >>> select * from b:table(set{1}) into temp t12345 with no log;>>> END PROCEDURE; >>> database a; >>> EXECUTE PROCEDURE AA();> > I don't need workaround. I want to know why my example not work. > You cannot create a table in a remote database. Have you tried finderr ? $ finderr -556-556 Cannot create, drop, or modify an object that is external to current database. This statement attempts to create, drop, or alter an object in an external database, one other than the current database. You can only read the contents of an external database.

↪ replying to Claus Samuelsen
Denis.Zhuravlov@gmail.com — — source: Usenet: comp.databases.informix

> > I don't need workaround. I want to know why my example not work. > > You cannot create a table in a remote database. > But I don't create table in external database. I create temp table in current database. And why CREATE not work (-556), but DROP work fine? database b; CREATE PROCEDURE BB1() drop table t123;END PROCEDURE; database a; select * from table(set{1}) into temp t123 with no log; EXECUTE PROCEDURE b:bb1();===>Routine executed.<===

↪ replying to Denis.Zhuravlov@gmail.com
david@smooth1.co.uk — — source: Usenet: comp.databases.informix

On 15 Aug, 12:14, Denis.Zhurav...@gmail.com wrote: > > > I don't need workaround. I want to know why my example not work. > > > You cannot create a table in a remote database. > > But I don't create table in external database. I create temp table in > current database. > > And why CREATE not work (-556), but DROP work fine? > > database b; > CREATE PROCEDURE BB1() > drop table t123;> END PROCEDURE; > > database a; > select * from table(set{1}) into temp t123 with no log; > EXECUTE PROCEDURE b:bb1();> ===>Routine executed.<=== Some people may call that a bug, I say Where in the manuals it is defined what happens when you change databases whilst you have existing temp tables? I say "Unless it is stated in the manuals what happens then the behaviour is undefined and you get what ever behaviour you get".

Related threads

Glossary