Comments in SELECT via PHP/ODBC DB2 UDB v8.1.9 Linux, PHP 4.4.0

[Bob Stearns]

Several weeks ago I asked what comments I could pass to DB2 in a SELECT
statement. I don't remember whether I said via PHP/ODBC. I was assured
that both /* */ style comment blocks and -- comment lines were allowed.
Unfortunately, neither work. Both -- and /* */ comments cause a
"premature end of statement". Any sage advice?

[Knut Stolze]

Bob Stearns wrote:

Several weeks ago I asked what comments I could pass to DB2 in a SELECT
statement. I don't remember whether I said via PHP/ODBC. I was assured
that both /* */ style comment blocks and -- comment lines were allowed.
Unfortunately, neither work. Both -- and /* */ comments cause a
"premature end of statement". Any sage advice?

The /* ... */ should work, I believe. I would be careful with -- style
comments because this will comment out everything till the end of the line
and if PHP strips away the linebreaks, you'll have a problem.

What's the exact error message that you got with?

You might also want to collect a CLI trace (PHP just uses CLI under the
covers). There you will see the exact statement that is send to DB2.

Finally, make sure that you're using the ibm-db2 PHP driver and not the unix
odbc stuff. The latter contains several issues when it comes to DB2.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Serge Rielau]

Knut Stolze wrote:

Bob Stearns wrote:

Several weeks ago I asked what comments I could pass to DB2 in a SELECT
statement. I don't remember whether I said via PHP/ODBC. I was assured
that both /* */ style comment blocks and -- comment lines were allowed.
Unfortunately, neither work. Both -- and /* */ comments cause a
"premature end of statement". Any sage advice?

The /* ... */ should work, I believe. I would be careful with -- style
comments because this will comment out everything till the end of the line
and if PHP strips away the linebreaks, you'll have a problem.

What's the exact error message that you got with?

You might also want to collect a CLI trace (PHP just uses CLI under the
covers). There you will see the exact statement that is send to DB2.

Finally, make sure that you're using the ibm-db2 PHP driver and not the unix
odbc stuff. The latter contains several issues when it comes to DB2.

-- Comments in CLI/ODBC will be supported in V8 FP12.
/* */ are today.

-- Comments in CLP will be supported in DB2 Viper eGA
/* */ are today

JDBC supports both today in the type 4 driver.

I don't now about PHP.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Kiran]

PHP driver is written using CLI/ODBC so It should support /* */
comments as Serge poined out if you are using v8 FP12

FYI : the redbook about PHP and DB2/Informix/Cloudscape development is
in draft state:
may be of help to you.
http://www.redbooks.ibm.com/Redbooks...7218.html?Open

Regards,

Kiran

[Dave Hughes]

Kiran wrote:

PHP driver is written using CLI/ODBC so It should support /* */
comments as Serge poined out if you are using v8 FP12

Just a quick correction here: C-style /*...*/ comments are supported
since FP11 (is there an FP12? I thought 11 was the latest?)

As pointed out by Knut, SQL-style -- comments can be problematic as
some clients strip line breaks (which obviously means that anything
after the first -- comment becomes part of the comment ... d'oh!).
Furthermore, as I understand it, DB2 strictly interprets the SQL
standard's definition of SQL-style comments which apparently (I don't
have a copy of the SQL standard, so I can't verify) states that the
opening "--" in SQL-style comments must be the first non-whitespace
characters in the line.

This can be verified by trying it in the CLP (not sure what Serge meant
when he said SQL-style comments will be supported in CLP in Viper as
they've been supported in CLP for several versions now, provided they
adhere to the above rule):

db2 => SELECT
db2 (cont.) => -- This is a valid comment
db2 (cont.) => VARCHAR(SCHEMANAME,8) AS SCHEMANAME
db2 (cont.) => FROM SYSCAT.SCHEMATA;

SCHEMANAME
----------
DOCCAT
DOCDATA
DOCTOOLS
NULLID
SQLJ
SYSCAT
SYSFUN
SYSIBM
SYSPROC
SYSSTAT
SYSTOOLS

11 record(s) selected.

db2 => SELECT -- This is NOT a valid comment
db2 (cont.) => VARCHAR(SCHEMANAME,8) AS SCHEMANAME
db2 (cont.) => FROM SYSCAT.SCHEMATA;
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"SELECT".
Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601
HTH,

Dave.

--

[Knut Stolze]

Dave Hughes wrote:

Kiran wrote:

PHP driver is written using CLI/ODBC so It should support /* */
comments as Serge poined out if you are using v8 FP12
Just a quick correction here: C-style /*...*/ comments are supported
since FP11 (is there an FP12? I thought 11 was the latest?)

FP12 is not yet out.
As pointed out by Knut, SQL-style -- comments can be problematic as
some clients strip line breaks (which obviously means that anything
after the first -- comment becomes part of the comment ... d'oh!).
Furthermore, as I understand it, DB2 strictly interprets the SQL
standard's definition of SQL-style comments which apparently (I don't
have a copy of the SQL standard, so I can't verify) states that the
opening "--" in SQL-style comments must be the first non-whitespace
characters in the line.
I haven't found this in the standard. SQL:2003 only says in the syntax
rules for "<token> and <separator>":

11) SQL text containing one ore more instances of <comment> is equivalent to
the same SQL statement with the <comment> replaced with <newline>.

<comment> can be a <simple comment>, i.e. -- ...\n, or a <bracketed
comment>, i.e. /* ... */.
This can be verified by trying it in the CLP (not sure what Serge meant
when he said SQL-style comments will be supported in CLP in Viper as
they've been supported in CLP for several versions now, provided they
adhere to the above rule):

db2 => SELECT
db2 (cont.) => -- This is a valid comment
db2 (cont.) => VARCHAR(SCHEMANAME,8) AS SCHEMANAME
db2 (cont.) => FROM SYSCAT.SCHEMATA;

SCHEMANAME
----------
DOCCAT
DOCDATA
DOCTOOLS
NULLID
SQLJ
SYSCAT
SYSFUN
SYSIBM
SYSPROC
SYSSTAT
SYSTOOLS

11 record(s) selected.

db2 => SELECT -- This is NOT a valid comment
db2 (cont.) => VARCHAR(SCHEMANAME,8) AS SCHEMANAME
db2 (cont.) => FROM SYSCAT.SCHEMATA;
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"SELECT".
Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

This looks like a bug in the parser to me.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Dave Hughes]

Knut Stolze wrote:

Dave Hughes wrote:

[snip]

As pointed out by Knut, SQL-style -- comments can be problematic as
some clients strip line breaks (which obviously means that anything
after the first -- comment becomes part of the comment ... d'oh!).
Furthermore, as I understand it, DB2 strictly interprets the SQL
standard's definition of SQL-style comments which apparently (I
don't have a copy of the SQL standard, so I can't verify) states
that the opening "--" in SQL-style comments must be the first
non-whitespace characters in the line.

I haven't found this in the standard. SQL:2003 only says in the
syntax rules for "<token> and <separator>":

11) SQL text containing one ore more instances of <comment> is
equivalent to the same SQL statement with the <comment> replaced with
<newline>.

<comment> can be a <simple comment>, i.e. -- ...\n, or a <bracketed
comment>, i.e. /* ... */.

Interesting, do you happen to have an earlier version of the standard,
say SQL-92 or even SQL-89? I just wonder if the definition has been
changed (well, I guess it has changed to add /*...*/ comments)?

This can be verified by trying it in the CLP (not sure what Serge
meant when he said SQL-style comments will be supported in CLP in
Viper as they've been supported in CLP for several versions now,
provided they adhere to the above rule):

db2 => SELECT
db2 (cont.) => -- This is a valid comment
db2 (cont.) => VARCHAR(SCHEMANAME,8) AS SCHEMANAME
db2 (cont.) => FROM SYSCAT.SCHEMATA;
[snip]
db2 => SELECT -- This is NOT a valid comment
db2 (cont.) => VARCHAR(SCHEMANAME,8) AS SCHEMANAME
db2 (cont.) => FROM SYSCAT.SCHEMATA;
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"SELECT".
Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

This looks like a bug in the parser to me.

Hmmm ... not so sure. A few years ago I wrote a little SQL parser to
handle DB2 syntax and had to thoroughly read the "languages elements",
"tokens", and other bits of the DB2 SQL Reference to make sure it was
right.

I could swear I remember the behaviour described above (-- comments
must be the first non-whitespace characters in a line) mentioned
explicitly in the reference (implying it is intended behaviour).
Unfortunately either my memory is faulty, or that statement's been
excised from the latest version.

Still, it could well be a bug or "feature": I've recently been
discovering how truly bizarre the CLP parser is compared to the SQL
parser (I suspect it's at least partly line-based, instead of "purely"
token-based).

Cheers,

Dave.
--

[Knut Stolze]

Dave Hughes wrote:

Knut Stolze wrote:

Dave Hughes wrote:

[snip]

> As pointed out by Knut, SQL-style -- comments can be problematic as
> some clients strip line breaks (which obviously means that anything
> after the first -- comment becomes part of the comment ... d'oh!).
> Furthermore, as I understand it, DB2 strictly interprets the SQL
> standard's definition of SQL-style comments which apparently (I
> don't have a copy of the SQL standard, so I can't verify) states
> that the opening "--" in SQL-style comments must be the first
> non-whitespace characters in the line.

I haven't found this in the standard. SQL:2003 only says in the
syntax rules for "<token> and <separator>":

11) SQL text containing one ore more instances of <comment> is
equivalent to the same SQL statement with the <comment> replaced with
<newline>.

<comment> can be a <simple comment>, i.e. -- ...\n, or a <bracketed
comment>, i.e. /* ... */.

Interesting, do you happen to have an earlier version of the standard,
say SQL-92 or even SQL-89? I just wonder if the definition has been
changed (well, I guess it has changed to add /*...*/ comments)?

SQL92 already used a very similar wording. The only difference is that
bracketed comments were not supported 14 years ago. There were some
restrictions as to the use of comments in dynamic SQL etc.

SQL99 introduced the bracketed comments

> This can be verified by trying it in the CLP (not sure what Serge
> meant when he said SQL-style comments will be supported in CLP in
> Viper as they've been supported in CLP for several versions now,
> provided they adhere to the above rule):
>
> db2 => SELECT
> db2 (cont.) => -- This is a valid comment
> db2 (cont.) => VARCHAR(SCHEMANAME,8) AS SCHEMANAME
> db2 (cont.) => FROM SYSCAT.SCHEMATA;
> [snip] >
> db2 => SELECT -- This is NOT a valid comment
> db2 (cont.) => VARCHAR(SCHEMANAME,8) AS SCHEMANAME
> db2 (cont.) => FROM SYSCAT.SCHEMATA;
> SQL0104N An unexpected token "END-OF-STATEMENT" was found following
> "SELECT".
> Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

This looks like a bug in the parser to me.

Hmmm ... not so sure. A few years ago I wrote a little SQL parser to
handle DB2 syntax and had to thoroughly read the "languages elements",
"tokens", and other bits of the DB2 SQL Reference to make sure it was
right.

Maybe that has changed since then. I'm aware that some work is going on to
close many gaps in DB2 that relate to the handling of comments.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany