Quotes required for table owner

Hello all, I am having trouble with some SPL I need to maintain. In the database I am accessing, there are many table owners, (i.e. common, admin, user). In the SPL (and also dbaccess) I am need to type in the owner name in double quotes in order to access the table. For example, a table named dual, owned by common needs to be accessed as: "common".dual I tried creating a public synonym to get around this like: create public synonym dual for "common".dual; but I get a syntax error back on the statement. Here are my questions: * Is there a init parameter that will ignore the " "? Since there are thousands of line of SPL, all referencing the tables as owner.table (in my previous example, the table is referenced in the code as common.dual, and the user has permission to select from it, but it won't work w/o the "") that would be preferable. * If there is not a param, how can I get the synonyms to take? I think dbaccess is complaining specifically about the " ". Any help would be greatly appreciated! TIA, Foo Sent via Deja.com http://www.deja.com/ Before you buy.

First note that if your database is not ANSI mode the owner names are NOT required AT ALL either with or without the quotes. The reason for this is that the owner name is not significant in a non-ANSI mode database and this is why you cannot create a synonym with the same name as the table but with a different owner. Whether an ANSI mode database would allow the synonyms I do not know as I do not use ANSI mode databases. Art S. Kagel foobar71@my-deja.com wrote: > > Hello all, > > I am having trouble with some SPL I need to maintain. In the database > I am accessing, there are many table owners, (i.e. common, admin, user). > In the SPL (and also dbaccess) I am need to type in the owner name in > double quotes in order to access the table. For example, a table > named dual, owned by common needs to be accessed as: > > "common".dual > > I tried creating a public synonym to get around this like: > > create public synonym dual for "common".dual; > > but I get a syntax error back on the statement. Here are my questions: > * Is there a init parameter that will ignore the " "? > Since there are thousands of line of SPL, all referencing the tables > as owner.table (in my previous example, the table is referenced in the > code as common.dual, and the user has permission to select from it, > but it won't work w/o the "") that would be preferable. > * If there is not a param, how can I get the synonyms to take? I think > dbaccess is complaining specifically about the " ". > > Any help would be greatly appreciated! > > TIA, > Foo > > Sent via Deja.com http://www.deja.com/ > Before you buy.

If you are using a MODE ANSI database, all synonyms are inherently private; you cannot use the keyword PUBLIC. As Art points out, the only time you have to worry about quoting owner names is when the database runs in MODE ANSI. So, we can confidently predict that you are using a MODE ANSI database. The only way of avoiding adding the quotes to the owner names is to create the tables with the owner name in UPPER-CASE, or with the owner names unquoted, as in: CREATE TABLE "COMMON".dual ( ... ) Or CREATE TABLE common.dual ( ... ) In both these examples, the owner name is stored in upper case (as required by the ANSI standard!), and your stored procedures which use no quotes will run without problem. It is entertaining writing code that will work cleanly under both MODE ANSI and regular Informix databases. :-) "Art S. Kagel" wrote: > First note that if your database is not ANSI mode the owner names are NOT > required AT ALL either with or without the quotes. The reason for this is > that the owner name is not significant in a non-ANSI mode database and > this is why you cannot create a synonym with the same name as the table > but with a different owner. Whether an ANSI mode database would allow the > synonyms I do not know as I do not use ANSI mode databases. > > Art S. Kagel > > foobar71@my-deja.com wrote: > > I am having trouble with some SPL I need to maintain. In the database > > I am accessing, there are many table owners, (i.e. common, admin, user). > > In the SPL (and also dbaccess) I am need to type in the owner name in > > double quotes in order to access the table. For example, a table > > named dual, owned by common needs to be accessed as: > > > > "common".dual > > > > I tried creating a public synonym to get around this like: > > > > create public synonym dual for "common".dual; > > > > but I get a syntax error back on the statement. Here are my questions: > > * Is there a init parameter that will ignore the " "? > > Since there are thousands of line of SPL, all referencing the tables > > as owner.table (in my previous example, the table is referenced in the > > code as common.dual, and the user has permission to select from it, > > but it won't work w/o the "") that would be preferable. > > * If there is not a param, how can I get the synonyms to take? I think > > dbaccess is complaining specifically about the " ". -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.62 -- see http://www.perl.com/CPAN #include <disclaimer.h>