Re: PHP odbc translation

["Joe Sam Shirah" <jshirah@xxxxxxxxxxxxx>]

    Hi Beppe,

    I sort of missed where J-a-v-a fits into your question; it's really an
AS/400 DB2/SQL specific question.  But, since this kind of issue also comes
up in JDBC, here goes.

    The culprit is one of my favorite gripes, CCSID 65535.  I've always
felt, and expressed to the NLS guy at COMMON, that the default behavior was
the wrong choice.

    The basics are that whenever a dynamic column is created, the
database returns it as a CCSID 65535 field.  I'll be a little vague, but
"dynamic" here means that data outside of the table data is
joined/concatenated/whatever "inside" the DBMS and returned.  So, even
though you probably have "translate binary" set, this statement:

"select  Code, trim(FirstName) concat ' ' concat LastName from Table"

still returns CCSID 65535 data and looks like garbage.  The blank ( ' ' )
causes the problem.

    When I ran across it a few years ago, I was in a rush and used this
solution:

SELECT
    CAST(  yourDynamicColumnExpression
        AS CHAR CCSID 37 )
            AS whatYouDecideToCallTheDynamicColumn

    I notice you are in Italy, so CCSID 37 may not be right for you; check
the CCSID for your standard jobs and use that.  The Unicode CCSID may also
work; I haven't tried that ( yep, bad for an i18n guy. )

    Notice that this is completely non-portable.  If I were doing it today,
with time, I would look for a more portable solution or do it from the
client.

    HTH,


                                                         Joe Sam

Joe Sam Shirah -        http://www.conceptgo.com
conceptGO         -        Consulting/Development/Outsourcing
Java Filter Forum:       http://www.ibm.com/developerworks/java/
Just the JDBC FAQs: http://www.jguru.com/faq/JDBC
Going International?    http://www.jguru.com/faq/I18N
Que Java400?             http://www.jguru.com/faq/Java400



----- Original Message -----
From: "Beppe Costagliola" <beppecosta@xxxxxxxx>
To: <JAVA400-L@xxxxxxxxxxxx>
Sent: Thursday, September 25, 2003 4:35 AM
Subject: PHP odbc translation


> I'm using a php script to get data via odbc (both db and Php - in Pase
> mode - are on the same Iseries).
>
> If I run this query :
> "select  Code, trim(FirstName) concat LastName from Table"
> I get the correct result for both fields returned, apart that FirstName
and
> LastName are joined together.
>
> But if I run this:
> "select  Code, trim(FirstName) concat ' ' concat LastName from Table"
> I get the correct result for Code but ebcdic untraslated data for the
second
> column.
>
> However if I code:
> "select  Code, trim(FirstName) concat substr(FirstName, 30, 1) concat
> LastName from Table" (assuming that position 30 of Firstame is a blank) I
> get FirstName and LastName separated by one blank char in readable form.
>
> Does anybody has got such a problem ?
>
> Thanks.
> Beppe.
>