PDO error when trying to insert to iSeries DB2 with php on Linux

0
votes

I've been having issues trying to run an insert statement via php to the iSeries DB2 via PDO odbc.

The following is my php script: 

<?php

$empl_number  = $argv[1];
$empl_estatus = $argv[2];
$empl_lname   = $argv[3];
$empl_fname   = $argv[4];
$empl_user    = $argv[5];

try {

    $sql = "INSERT INTO `USER_PROFILE_AD` (`EMPLOYEE_NUMBER`, `EMPLOYEE_STATUS`, `LAST_NAME`, `FIRST_NAME`, `LDAP_RDN`, `ACTIVE`, `USER_PROFILE`) VALUES (:numb, :stat, :lname, :fname, :usern, :active, :profile)";
    $conn = new PDO("odbc:AS400TST");
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $stmt = $conn->prepare($sql);

    $stmt->bindParam(':numb', $empl_number, PDO::PARAM_INT);
    $stmt->bindParam(':stat', $empl_estatus, PDO::PARAM_STR);
    $stmt->bindParam(':lname', $empl_lname, PDO::PARAM_STR);
    $stmt->bindParam(':fname', $empl_fname, PDO::PARAM_STR);
    $stmt->bindParam(':usern', $empl_user, PDO::PARAM_STR);
    $stmt->bindParam(':active', 0, PDO::PARAM_INT);
    $stmt->bindParam(':profile', NULL, PDO::PARAM_NULL);
    $stmt->execute();

} catch (PDOException $e) {
    echo $e->getMessage();
}

?>

When I run the script via command line... 

user@server: /usr/bin/php myscript.php 1234 AC DOE JOHN JOHN.DOE

I get the following error message:

SQLSTATE[42000]: Syntax error or access violation: 0 [IBM][System i Access ODBC Driver]Statement violates access rule: Connection is set to read only. (SQLPrepare[0] at /builddir/build/BUILD/php-5.4.16/ext/pdo_odbc/odbc_driver.c:206)

I double-checked with the iSeries developer and the username that I using DOES have fully access to the database/table/etc...

Would you please help me to see why the insert doesn't work?

NOTES:

  • Connection works fine.

  • "Select" statements works just fine.

  • at /etc/odbc.ini, the CommitMode = 2

Thanks in advance,

EGMWEB

3
phplinuxpdoodbcibm-midrange

3 Answers

0
votes

From my research, this error is commonly caused by an upgrade or PTF applied to the IBM i OS. The fix is to obtain a current ODBC driver. Your administrator should be able to help with this.

0
votes

I found the solution -

For your reference in case some one else is having this issue...

The following article: https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_71/rzatv/rzatvodbcman.htm

Explains the settings to be done at the odbc.ini

...

DefaultLibraries = QGPL
Database =
ConnectionType = 0
CommitMode = 2
ExtendedDynamic = 0
DefaultPkgLibrary = QGPL
DefaultPackage = A/DEFAULT(IBM),2,0,1,0,512
AllowDataCompression = 1
LibraryView = 0
AllowUnsupportedChar = 0
ForceTranslation = 0
Trace = 0

... Remember to change "QGPL" on both params for yours

I adjusted the parameters and I got it to work.

Thank you all!!

EGMWEB

0
votes

The table is not journaled if selects work. You'll have to start journaling of the table for add change delete to work. ACD doesn't work.

on the as400.

STRJRNPF ...