insert json object into oracle db table blob column

Question

The below is posting in dev console as POST: 404. I'm guessing my php is not correct. What am I doing wrong here?

On this JS side I'm doing:

    ........
    user = JSON.stringify(user); // updated Data, want to push to db table blob column
    pushData ();

    function pushData (){
        const ajax = $.ajax({
            method: 'POST',
            url: `./Settings.php`,
            data: {obj_json: JSON.stringify(user) },
            dataType: "json",
            success: function(result) {
                //Write your code here
                console.log("data posted?");
            }
          });
        }
  .....

/Settings.php (the class used has valid db connection, my obj_json is the column that is set to blob in db table). I am not really sure what to do with $obj

class Settingz extends \myApp\Data
{

    public function insertBlob($obj, $last_update) {
        $obj = $_POST['obj_json'];

        $sql = "INSERT INTO MY_TABLE(last_update,obj_json) VALUES(:last_update,:obj_json)";
        $stmt = $this->pdo->prepare($sql);

        $stmt->bindParam(':last_update', $last_update);
        $stmt->bindParam(':obj_json', $blob, PDO::PARAM_LOB);

        return $stmt->execute();
    }
  }

For one thing, you have :obj_json in VALUES, but binding with :prefs_json. — Funk Forty Niner
– Funk Forty Niner, Commented Feb 29, 2020 at 22:15
@FunkFortyNiner how do I handle it coming from POST? not filePath — Lieutenant Dan
– Lieutenant Dan, Commented Feb 29, 2020 at 22:16
You still have a typo objs_json with an "s". Handling files is usually done with $_FILES in a form when done in pure PHP instead of $_POST and with an proper enctype. If this is JS or OOP related, I'm not the guy for this. — Funk Forty Niner
– Funk Forty Niner, Commented Feb 29, 2020 at 22:19

Christopher Jones · Accepted Answer · 2020-03-02 00:02:35Z

With OCI8 you could use a Temporary LOB (see p.235 of The Underground PHP and Oracle Manual), but with PDO_OCI, use a RETURNING clause like:

<?php


try {
    $db = new PDO('oci:dbname=localhost/orclpdb1', 'cj', 'welcome');
}
catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
    exit;
}

$stmt = $db->prepare("drop table cjblob");
$stmt->execute();

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $db->prepare("create table cjblob (id number, data blob)");
$stmt->execute();

function do_insert($db, $id, $data)
{
    $stmt = $db->prepare("insert into cjblob (id, data) values (:id, empty_blob()) returning data into :blob");
    $stmt->bindParam(':id', $id);
    $stmt->bindParam(':blob', $blob, PDO::PARAM_LOB);
    $blob = null;
    $db->beginTransaction();
    $stmt->execute();

//  var_dump($blob);  // $blob becomes a stream

    fwrite($blob, $data);
    fclose($blob);
    $db->commit();
}

do_insert($db, 1, str_pad("Z",  40000, "Z"));

// Fetch it back
$stmt = $db->prepare('select data from cjblob where id = ?');
$id = 1;
$stmt->execute(array($id));
$row = $stmt->fetch(PDO::FETCH_ASSOC);
var_dump($row);
var_dump(stream_get_contents($row['DATA']));

?>

Collectives™ on Stack Overflow

insert json object into oracle db table blob column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related