column names
A DBA asked whether columns named CREATE, UPDATE, DELETE, AMOUNT, NAME etc. (created by a developer on IDS 10.0 FC7/HP-UX) are risky, since the server accepted them despite looking like reserved words. Consensus: many such words aren't strictly reserved by the SQL standard, so the CREATE succeeds, but using keyword-like names invites trouble in SQL and tools — one poster cited a column named "status" causing a -1213 (character-to-numeric conversion) error in 4GL unless fully qualified as table.status. Advice was to rename/prefix columns and avoid keywords; no other fix recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, My developer has created some columns during development and the name of some of the columns are CREATE, UPDATE, DELETE, AMOUNT, NAME etc... Will that cause any problem in future? I thought those are reserved words but database has allowed to create those names. Are there any risks involved in future? Your response will be greatly appreciated. I am on Informix 10.0 FC7 with HP-UX platform. Thanks, Sunita Raina
Hi, one must not use reserved words as db object identifier. refer below link for more details http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sq ls.doc/sqls1204.htm Thanks, Nilesh
SUNITA RAINA wrote: > Hi, > > My developer has created some columns during development and the name of some > of the columns are CREATE, UPDATE, DELETE, AMOUNT, NAME etc... Will that cause > any problem in future? I thought those are reserved words but database has > allowed to create those names. Are there any risks involved in future? > > Your response will be greatly appreciated. > > I am on Informix 10.0 FC7 with HP-UX platform. > By SQL Standard fiat these and others are not reserved. HOWEVER, there are MANY situations where having named your column by such names, and other similar names, will cause you no end of trouble. Art S. Kagel Oninit > Thanks, > Sunita Raina > > > ================================================================================ =========== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ================================================================================ ===========
"CREATE" is not a terribly useful column name. I would kick back on that from a standpoint of understandabilityness. j. Sane ego te vocavi. Forsitan capedictum tuum desit. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of Art S. Kagel (Oninit LLC) Sent: Monday, February 11, 2008 6:18 PM To: ids@iiug.org Subject: Re: column names [11279] SUNITA RAINA wrote: > Hi, > > My developer has created some columns during development and the name of some > of the columns are CREATE, UPDATE, DELETE, AMOUNT, NAME etc... Will that cause > any problem in future? I thought those are reserved words but database has > allowed to create those names. Are there any risks involved in future? > > Your response will be greatly appreciated. > > I am on Informix 10.0 FC7 with HP-UX platform. > By SQL Standard fiat these and others are not reserved. HOWEVER, there are MANY situations where having named your column by such names, and other similar names, will cause you no end of trouble. Art S. Kagel Oninit > Thanks, > Sunita Raina > > > ============================================================================ =============== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ============================================================================ =============== **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!!
Hi, Those are the key words. It would give problem when you write sql statement on the table with the field names. It is always better to avoid using database keywords as field names. Have some prefix to the field names. Regards, shiller ________________________________ From: ids-bounces@iiug.org on behalf of SUNITA RAINA Sent: Tue 2/12/2008 1:01 AM To: ids@iiug.org Subject: column names [11276] Hi, My developer has created some columns during development and the name of some of the columns are CREATE, UPDATE, DELETE, AMOUNT, NAME etc... Will that cause any problem in future? I thought those are reserved words but database has allowed to create those names. Are there any risks involved in future? Your response will be greatly appreciated. I am on Informix 10.0 FC7 with HP-UX platform. Thanks, Sunita Raina ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!! DISCLAIMER: This email (including any attachments) is intended for the sole use of the intended recipient/s and may contain material that is CONFIDENTIAL AND PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying or distribution or forwarding of any or all of the contents in this message is STRICTLY PROHIBITED. If you are not the intended recipient, please contact the sender by email and delete all copies; your cooperation in this regard is appreciated.
Consider this situation: A table named stcvcntr with a field accidentally created as "status" (instead of status_code). When executing a SQL statement like this " select vcn_no, cust_code from stcvcntr where status = 'U'" in a 4GL program, it ends with the -1213 error (character to numeric conversion error). To avoid this error we have to explicitly say "where stcvcntr.status = 'U'". In short we should not use reserved words for field names to avoid unexpected troubles in run time. Long N ==================================== "SUNITA RAINA" <sraina@idoc.idah To: ids@iiug.org o.gov> cc: Sent by: Subject: column names [11276] ids-bounces@iiug. org 12/02/2008 05:31 AM Please respond to ids Hi, My developer has created some columns during development and the name of some of the columns are CREATE, UPDATE, DELETE, AMOUNT, NAME etc... Will that cause any problem in future? I thought those are reserved words but database has allowed to create those names. Are there any risks involved in future? Your response will be greatly appreciated. I am on Informix 10.0 FC7 with HP-UX platform. Thanks, Sunita Raina ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!! Disclaimer: This correspondence is for the named person's use only. It may contain confidential or legally privileged information or both. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this correspondence in error, please immediately delete it together with any attachments from your system and notify the sender. You must not disclose, copy or rely on any part of this correspondence if you are not the intended recipient. Any opinions expressed in this message are those of the individual sender, except where the sender expressly, and with authority, states them to be the opinions of Ruralco Holdings Limited or any of its subsidiaries (collectively "Ruralco"). Although all care has been taken to screen this communication for viruses, neither the sender nor Ruralco warrants that any communication via the Internet is free of errors, viruses, interception or interference. Information is distributed without warranties of any kind.