Re: Field / Column Name Case

Interesting how wide the chasm between standards vs. common use. :-)

Here is an example to prove out it works with the response at the bottom,
FWIW. Charles beat me to the response. ;-)

<?php

// Script illustrating that file on SQL is found in i5_lib...
require_once 'credentials.php';

// User is PHPUSER...

$options = array("i5_lib" => "QGPL");

$con1 = db2_connect($db, $user, $pwd, $options);
if (! $con1) {
echo 'Error connecting to ' . $db . ' - ' . db2_conn_error() . ' - ' .
db2_conn_errormsg();
exit();
}

$sql = 'CREATE TABLE DELETEME ("Field1" INT NOT NULL WITH DEFAULT,
"Field2" VARCHAR (20) NOT NULL WITH DEFAULT, "Field3" VARCHAR (20)

NOT NULL WITH DEFAULT)';

$result = db2_exec($con1, $sql);
if (! $result) {
echo 'Error runnning SQL for ' . $sql . ' - ' . db2_stmt_error() . ' -
' . db2_stmt_errormsg();
exit();
} else
echo "Table Created <br /><br />";

$sql = "INSERT INTO DELETEME VALUES(25, 'Hello', 'World')";

$result = db2_exec($con1, $sql);
if (! $result) {
echo 'Error runnning SQL for ' . $sql . ' - ' . db2_stmt_error() . ' -
' . db2_stmt_errormsg();
exit();
} else
echo "Row inserted <br /><br />";

$sql = 'select * from deleteme';

$result = db2_exec($con1, $sql);
if (! $result) {
echo 'Error runnning SQL for ' . $sql . ' - ' . db2_stmt_error() . ' -
' . db2_stmt_errormsg();
exit();
} else {

$firstRow = db2_fetch_assoc($result);
print_r($firstRow);
}


Web output:

Table Created

Row inserted

Array ( [Field1] => 25 [Field2] => Hello [Field3] => World )


Hope this helps!


Mike Pavlak
Cell: (408)679-1011 Office: (708)233-5880


-----Original Message-----
From: WEB400 [mailto:web400-bounces@xxxxxxxxxxxx] On Behalf Of Charles
Wilt
Sent: Wednesday, December 23, 2015 10:09 AM
To: Web Enabling the IBM i (AS/400 and iSeries)
Subject: Re: [WEB400] Field / Column Name Case

Cryptic is in the eye of the beholder I guess..
Language Elements-->Identifiers-->SQL Identifiers

http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzsqlidt
s.htm

I much prefer IBM's docs over Microsoft...




On Wed, Dec 23, 2015 at 9:57 AM, Rob <rob.couch@xxxxxxxxxxxxxx> wrote:

Thank you Charles.

I am sure that this information is hidden in the cryptic documentation
I have been reading from IBM.


On 12/23/2015 07:33 AM, Charles Wilt wrote:

If you want case sensitive columns names, then when creating teh
table they have to be quoted

create table mytbl (
"MyColumn" char(25)
);

select mycolumn from mytbl;
Column or global variable MYCOLUMN not found.

select "MyColumn" from mytbl;
SELECT statement run complete.

Oracle works the same way. Not surprising; given that this follows
the SQL standards...
http://www.alberton.info/dbms_identifiers_and_case_sensitivity.html


Charles

On Tue, Dec 22, 2015 at 11:52 PM, Rob <rob.couch@xxxxxxxxxxxxxx> wrote:

My script creates the tables using Upper And Lower Case letters.

I can run sql queries using any case.

Why can the all powerful DB2 not return the field names as I created
them in the result set?

All Caps is for dead head screamers. This is a new age of

computing...

we
have a SHIFT key!

Took me a few minutes to figure out why I was not getting any data
out of a specific field. I used fetch_assoc and $aRow[ 'Name' ]
rather than $aRow[ 'NAME' ] .

I thought that 'i5_naming' => DB2_I5_NAMING_OFF was supposed to turn
that off.

This is probably just a rant with no solution.

--
Your Out-Source IT Department,
Rob Couch
IT Serenity
214 682 7638
Skype: itserenity

--
This is the Web Enabling the IBM i (AS/400 and iSeries) (WEB400)
mailing list To post a message email: WEB400@xxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/web400
or email: WEB400-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/web400.

--
Your Out-Source IT Department,
Rob Couch
IT Serenity
214 682 7638
Skype: itserenity

--
This is the Web Enabling the IBM i (AS/400 and iSeries) (WEB400)
mailing list To post a message email: WEB400@xxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/web400
or email: WEB400-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/web400.

--
This is the Web Enabling the IBM i (AS/400 and iSeries) (WEB400) mailing
list To post a message email: WEB400@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/web400
or email: WEB400-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/web400.