Re: Anybody here know anything about doing SQL Prepared Statements from PHP?

What confuses me about this whole scenario is why on earth anyone wants an ASCII control character to be retained in an otherwise EBCDIC data stream? When at some point in the future the data gets converted back to ASCII for printing or whatever doesn’t it then get converted back to the wrong value?


Jon Paris

www.partner400.com
www.SystemiDeveloper.com

On Sep 27, 2016, at 3:47 PM, CRPence <crpbottle@xxxxxxxxx> wrote:

On 27-Sep-2016 13:18 -0500, James H. H. Lampert wrote:

[…] given that hex'0A' has been entrenched in the application for
over two decades, changing the application is not an option.

If the code point 0x0A is required to be stored in the EBCDIC application data [to avoid changing the application], then consider:

That 0x0A is the Repeat (RPT) control character in EBCDIC, into which the Single Shift Two (SS2) control character should translate from the code point 0x8E in ASCII.

CDRA Appendix G. Control character mappings
[http://www.ibm.com/software/globalization/cdra/appendix_g.html#ISO-8%20to%20EBCDIC]

Where the prepared-statement version has

$temp = "Name: " . $name . "
Email Address: " . $emailAddress . "
Phone Number: " . $phoneNumber . "
Message: " . $message . "
HTTP_REFERRER: " . $_SERVER[ 'HTTP_REFERER' ]; // Notes
$nnotes = str_replace( " ", "", $temp );

which then gets passed as a parameter in the prepared statement,

A similarly improper entrenchment ;-) might be able to overcome that issue; try the following revision to the above:

$temp =
"Name: " . $name . x'8E' .
"Email Address: " . $emailAddress . x'8E' .
"Phone Number: " . $phoneNumber . x'8E' .
"Message: " . $message . x'8E' .
"HTTP_REFERRER: " . $_SERVER[ 'HTTP_REFERER' ]; // Notes
$nnotes = str_replace( " ", "", $temp );

the non-prepared-statement version has

$nnotes = " 'Name: " . $name . "' || x'0A' ||
'Email Address: " . $emailAddress . "' || x'0A' ||
'Phone Number: " . $phoneNumber . "' || x'0A' ||
'Message: " . $message . "' || x'0A' ||
'HTTP_REFERRER: " . $_SERVER[ 'HTTP_REFERER' ] . "' "; // Notes

Which probably explains the difference in behavior.

Might seem inexplicable at first glance, without learning\knowing more; but easily enough intuited, that the above text would be inserted into a statement that would be generated, in ASCII, and then passed-to and run-at the IBM i as the server -- run as&in EBCDIC. Thus the x'0A' gets inserted as an EBCDIC 0x0A rather than inserted as the originally ASCII 0x0A being translated into the EBCDIC 0x25.

$nnotes =
"'Name: " . $name . "' concat x'0A' concat
'Email Address: " . $emailAddress . "' concat x'0A' concat
'Phone Number: " . $phoneNumber . "' concat x'0A' concat
'Message: " . $message . "' concat x'0A' concat
'HTTP_REFERRER: " . $_SERVER[ 'HTTP_REFERER' ] . "' "
; // Notes

$insert_stmt_string =
"INSERT INTO " . $library . ".NOTEFILE" .
"( NRECID, NNOTES )" .
"( " . $nrecid .
", " . $nnotes .
") "

So essentially something like the above would have generated something like the following effective equivalent statement to be run with the DB2 for IBM i SQL, i.e. at the IBM i as server, as if typed in at the STRSQL statement entry; the ellipses previously replaced with the data from the corresponding $variable data:

insert into SomeLibraryNameFromVariable/NOTEFILE
( NRECID, NNOTES ) VALUES
( …
, 'Name: …' concat x'0A' concat
'Email Address: …' concat x'0A' concat
'Phone Number: …' concat x'0A' concat
'Message: …' concat x'0A' concat
'HTTP_REFERRER: …'
)

Having been a fully resolved character string passed to the IBM i, the above statement indeed inserts the 0x0A between the different resolved text variables.

Either the prepared-statement version has to behave like the
non-prepared-statement version, or they have to stick with the
non-prepared-statement version.

While I loathe making a suggestion to do so, I suspect what I offered as an effective /circumvention/ above, could be used to suffice to "overcome" the issue experienced with the prepared statement; i.e. effectively trick the conversion into effecting what is the desired result.

--
Regards, Chuck

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.