Re: Escaped column names (was: DIY Power PC)

On 30-Dec-2015 11:14 -0700, Buck Calabro wrote:

On 12/30/2015 12:49 PM, Rob wrote:

insert into "Table" ( "Field1", "Field2" ) values
( "Data1", "Data2" ) ;

I get Column or global variable Data1 not found.

So of course quickly I realize that it is the double quotes in the
values.... and

insert into "Table" ( "Field1", "Field2" ) values
('Data1', 'Data2' ) ;

Works just fine. So my thinking here is that DB2 sees anything in
double quotes where there is data as a Global Variable.

It's more subtle than this blanket statement suggests. <<SNIP>>

Quite. Essentially, wherever a /variable/ is allowed in a SQL statement, anything coded that is a valid /identifier/ will be treated as such; often the effect will be diagnosed as the sqlcode -206 [aka SQL0206] because what was interpreted as an identifier, is neither a field name nor a variable name, and instead more commonly just a misspelled identifier or an incorrectly delimited literal\constant value. Prior to the inclusion of Global Variables, far fewer coded-in-error statements [that accept variables] would have been diagnosed as failing with the -206; that so many mis-coded statements error with that return code versus offering an error with something more informative, can sometimes be as frustrating as receiving the generic syntax error of -104 [aka SQL0104] for /unexpected token/.

Note: the double-quote as delimiter for literals is allowed in COBOL embedded SQL, and thus AFaIK that statement as coded in the OP could have effected instead, either insert into a compatible column or have been diagnosed as either a statement-validation for the type-mismatch or a run-time failure for failure of conversion\casting [SQL0420] from the incompatible typed-as character string literal value.

P.S. FWiW, while Buck made mention that the old topic [(was: DIY...)] was off-topic for this forum, note that generic SQL topics [such as this] also are best directed to MIDRANGE-L unless the SQL usage is specific to the host\embedded High Level Language and thus best directed to the email-list\newsgroup established for that HLL.