Monthly Archives: March 2016

ZF2 – Zend_DB Support for number sign # (AKA pound, hash) in a field / column on the IBMi DB2

If you use Zend Framework 2’s Zend\Db\Sql\Select or Zend\Db\Sql\Join “on expression” to create queries for the IBM i DB2 you’ll sometime’s run into the error

“Column or global variable SomeField”# not found. SQLCODE=-206”

This is because the the Adapter/Platform/AbstractPlatform’s quoteIdentifierInFragment function is used to parse the input you provide. It currently doesn’t allow for the pound sign # to be in a column name and it incorrectly puts quotes around the #.  SomeField# becomes “SomeField””#” , i believe.

If you have this problem you can use my change to /Adapter/Platform/IbmDb2.php found here https://github.com/zendframework/zend-db/pull/95/files .  This change creates a quoteIdentifierInFragment function in /Adapter/Platform/IbmDb2.php that overrides the Base Class AbstractPlatform’s quoteIdentifierInFragment function and uses one that supports # by adding # to the regex.

More info on this getting added to the master branch can be found on my pull request here.  Please feel free to add comments, or commit other code that could be helpful:

https://github.com/zendframework/zend-db/pull/95

Insert statements are not effected because columns are quoted differently.  An array of columns is process and passes the entire column element to: $platform->quoteIdentifier($column); https://github.com/zendframework/zend-db/blob/master/src/Sql/Insert.php#L164

The main change is the regex and you can try different cases here: http://sandbox.onlinephpfunctions.com/code/9b50346237ed0de83f2244282081ae5080ad9f75

 

Advertisements

Netbeans: Creating Stored Procedures or running Dynamic compound statement on IBMi DB2

I finally figured out how you can create a stored procedure or run Dynamic compound statements in Netbean’s Database service on the IBMi.  My problem was that whenever I tried to run a create stored procedure script the SQL editor didn’t know how to parse it correctly so it would send the command line by line to the database using the semi colon as the delimiter instead of as one contiguous command.

The key to getting this to work in Netbeans is to create a Delimiter that marks the end of the SQL that you want to send to the system.  In the example below I send a request that creates 2 temp tables, and then 2 requests to pull the data from those tables.  This example is simple and doesnt show the full power of a DCS.  The dynamic compound statements allow you to use variables, control logic, cursors, etc… to do database specific tasks.  Its like a stored procedure except you don’t have to create it first.   see:

https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/Dynamic%20Compound%20statement

and

https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/sqlp/rbafydyncompound.htm

It did seem that a dynamic compound statement can’t return a result set for some reason (at least when i tried).

Below is an example of the error that I was getting when i was running into this issue where the database service was not parsing the CREATE PROCEDURE statement as one contiguous chunk.

Error code -104, SQL state 42601: [SQL0104] Token <END-OF-STATEMENT> was not valid. Valid tokens: ;.
Line 2, column 1

Below is me trying DCS in different IBMi tools

cimmfjkweaexyhx

PayPal Payflow Pro – Invalid Cust Ref in CUSTCODE parameter

If you ever get a response code 30 and “auth resp text” of “Invalid Cust Ref” it means you have an Invalid Customer Reference (ID). According to PayPal this field can be up to 16 characters and ALPHANUMERIC. If you put bad data in there such as an underscore _ you will probably get the response code 30 and “Invalid Cust Ref” error or “invalid value in message”. You may also get result code of 121 “Not enabled for feature” or a response message “Unknown error”.  The sale payment transaction will also be declined at the time of purchase.

Lesson of the story don’t put non-alphanumeric values into the Customer Code field AKA CUSTCODE AKA Customer Reference (ID).

Kill all jobs with a record lock on a specific file via “DB2 for i” SQL

Below is a stored procedure that uses the DB2 for i service QSYS2.RECORD_LOCK_INFO to get all the record locks on a specific file.  It then iterates over all the records and passes the JOB_NAME to ENDJOB to kill the jobs that are creating the lock on the file.  Of course this is very dangerous and should be only used in certain instances where you are sure that the jobs you’ll be killing will not matter.