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:
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:
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).
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.