iT邦幫忙

0

PHP INSERT

class OracleDB {

    private $var_max_size = -1;
    private $statements = array();
    private $execute_status = false;
    private $charset;
    private $conn_handle;
    private $last_query;
    private $fetch_mode = OCI_BOTH;
    private $autocommit = false;

    /**
     * Set nls_lang
     *
     * @param string $charset
     */
    public function SetNlsLang($charset = ORA_CHARSET_DEFAULT){
        $this->charset = $charset;
    }
    /**
     * Set array fetching mode for Fetch methods
     *
     * @param mixed $mode
     */

    public function SetFetchMode($mode = OCI_BOTH){
        $this->fetch_mode = $mode;
    }
    /**
     * Set on|off auto commit option
     *
     * @param bool $mode
     */
    public function SetAutoCommit($mode = true){
        $this->autocommit = $mode;
    }


    /**
     * Set variable max size for binding
     *
     * @param int $size
     */
    public function SetVarMaxSize($size){
        $this->var_max_size = $size;
    }

    /**
     * Set nls_lang
     * Set FetchMode
     * Set is AutoCommit
     */
    public function __construct(){
        $this->SetNlsLang('UTF8');
        $this->SetFetchMode(OCI_ASSOC);
        $this->SetAutoCommit(false);
    }
    public function __destruct()
    {
        if (is_resource($this->conn_handle)) {
            @oci_close($this->conn_handle);
        }
    }

    public function Connect($host = 'localhost', $user='', $pass='', $mode = OCI_DEFAULT, $type = ORA_CONNECTION_TYPE_DEFAULT){
        switch ($type) {
            case ORA_CONNECTION_TYPE_PERSISTENT: {
                $this->conn_handle = oci_pconnect($user, $pass, $host, $this->charset, $mode);
            }; break;
            case ORA_CONNECTION_TYPE_NEW: {
                $this->conn_handle = oci_new_connect($user, $pass, $host, $this->charset, $mode);
            }; break;
            default:
                $this->conn_handle = oci_connect($user, $pass, $host, $this->charset, $mode) or die('connect false');
        }
        return is_resource($this->conn_handle) ? true : false;
    }

    public function Select($sql,&$bind=false){
       return $this->Execute($sql,$bind);
    }
    /**
     * Insert row into table
     *
     * @param string $table name of table
     * @param array $arrayFieldsValues define pair field => value
     * @param array $bind define pairs holder => value for binding
     * @param array $returning define fields for returning clause in insert statement for C-LOB
     * @return mixed if $returnig is defined function return array of fields defined in $returning
     * @author Sergey Pimenov
     */

    public function Insert($table, $arrayFieldsValues, &$bind = false, $returning = false){
        if (empty($arrayFieldsValues)) return false;
        $fields = array();
        $values = array();

        foreach ($arrayFieldsValues as $f=>$v){
            $fields[]=$f;
            $values[]=$v;
        }
            $fields = implode(",", $fields);
            $values = implode(",", $values);
            $ret = "";
            if ($returning){
                foreach ($returning as $f=>$h){
                    $ret_fields[]=$f;
                    $ret_binds[]=":$h";
                }
                $ret=" RETURNING ".(implode(",",$ret_fields))." INTO ".(implode(",",$ret_binds));
            }

          $SQL="INSERT INTO $table ($fields) VALUES ($values) $ret";


          $result=$this->Execute($SQL,$bind);
            if ($result===false) {
                $this->Rollback();
                return false;
            }

            $this->Commit();
            $this->FreeStatement($result);
            return true;
    }

    /**
     * Private method for execute any sql query or pl/sql
     *
     * @param string $sql_text
     * @param array | false $bind
     * @return resource | false
     */

    private function Execute($sql_text, &$bind =false){
        if (!is_resource($this->conn_handle)) return false;
        $this->last_query = $sql_text;

        $stid = @oci_parse($this->conn_handle, $sql_text);
        if (!$stid){
            echo oci_error($stid)['message'];
            return  false;
        }
        $this->statements[(int)$stid]['text'] = $sql_text;
        $this->statements[(int)$stid]['bind'] = $bind;

        if ($bind && is_array($bind)) {
            foreach($bind as $k=>$v){
                oci_bind_by_name($stid, $k, $bind[$k],$this->var_max_size,$this->GetBindingType($bind[$k]));
            }
        }
        $com_mode = $this->autocommit ? OCI_DEFAULT : OCI_NO_AUTO_COMMIT;

        $this->execute_status = oci_execute($stid, $com_mode);

        if (!$this->execute_status){
           echo oci_error($stid)['message'];
           return  false;
        }

        return $this->execute_status ? $stid : false;
    }


    private function GetBindingType($var){
        if (is_a($var, "OCI-Collection")) {
            $bind_type = SQLT_NTY;
            $this->SetVarMaxSize(-1);
        } elseif (is_a($var, "OCI-Lob")) {
            $bind_type = SQLT_CLOB;
            $this->SetVarMaxSize(-1);
        } else {
            $bind_type = SQLT_CHR;
        }
        return $bind_type;
    }

    /**
     * Fetch array of select statement
     *
     * @param resource $statement valid OCI statement id
     * @return array
     */
    public function FetchArray($statement){
        return oci_fetch_array($statement, $this->fetch_mode);
    }

    /**
     * Returns the last error found.
     *
     */
    public function GetError(){
        return @oci_error($this->conn_handle);
    }

    /**
     * Commit transaction
     *
     * @return bool
     * @author Sergey Pimenov
     */
    public function Commit(){
        if (is_resource($this->conn_handle))
            return @oci_commit($this->conn_handle);
        else
            return false;
    }

    /**
     * Rollback transaction
     *
     * @return bool
     * @author Sergey Pimenov
     */
    public function Rollback(){
        if (is_resource($this->conn_handle))
            return @oci_rollback($this->conn_handle);
        else
            return false;
    }

    public function NewDescriptor($type = OCI_D_LOB){
        return oci_new_descriptor($this->conn_handle, $type);
    }

    /**
     * Free resource of OCI statement identifier
     *
     * @param resource $stid
     * @return bool
     * @author Sergey Pimenov
     */
    public function FreeStatement($stid){
        unset($this->statements[$stid]);
        return oci_free_statement($stid);
    }

}
$DB=new OracleDB();
$DB->Connect($host,$user,$passwd);
$DB->SetFetchMode(OCI_ASSOC);
$DB->SetAutoCommit(false);

$arr=array("tbTAG1"=>":tbTAG1", "tbTAG2"=>"' '")

$bind = array(":tbTAG1"=>"'123'");
$returning = array("tbTAG1"=>"tbTAG1");
$h=$DB->Insert('testTable',$arr,$bind,$returning);

我目前在練習用OOP的方式寫PHP DB的動作,想請問各位大大為甚麼我在進行INSERT的時候網站會卡住,原本單純存3個字元的字串可以通過,但使用4000個字元測試C-LOB的存檔程序後,原本單純存3個字元的都無法正常執行了,網站會卡住請問是因為被堵塞沒有釋放先前的資源造成的嗎?或是我少了甚麼步驟嗎?謝謝大家

nlstudio iT邦新手 2 級 ‧ 2021-07-14 12:06:30 檢舉
卡住是回應什麼,log顯示什麼,你的欄位長度有確認嗎?
qpalzm iT邦新手 1 級 ‧ 2021-07-14 13:41:13 檢舉
您好 網頁一直在跑 但沒有回傳任何東西 欄位確認過是c-lob
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友回答

立即登入回答