Error saving field of type CLOB

[luiz-andrade]

Boa tarde. Não estou conseguindo salvar um dado do tipo clob Estou fazendo desta forma

$dados =  \DB::table('tabela')->where('id', $id)->updateLob(
   array('name'        =>'demo update blob'),
   array('observacao'  => 'blob content here')
);

Apresenta este erro:

SQLSTATE[HY093]: Invalid parameter number: Columns/Parameters are 1-based

System details

• Windows
• PHP Version 5.6
• Laravel Version 5.1

[mstaack]

please use english language

[luiz-andrade]

Error saving field of type CLOB I'm doing this way

$dados =  \DB::table('mytable')->where('id', $id)->updateLob(
   array('name'        =>'demo update blob'),
   array('content'  => 'blob content here')
);

Displays this error:

SQLSTATE[HY093]: Invalid parameter number: Columns/Parameters are 1-based

[mstaack]

hmm isnt this:

[
'name'=>'blabla',
'content'=>'content'
]

give it a try, but i might be wrong

[luiz-andrade]

Displays this error:

ErrorException in OracleBuilder.php line 65:

Argument 2 passed to Yajra\Oci8\Query\OracleBuilder::updateLob() must be of the type array, string given

[mstaack]

ah okay its nested.... try:

[
   ['name'=>'blabla'],
   ['content'=>'blabla']
]

your code passes two args each with one array.. that is wrong

[luiz-andrade]

same error above

[yajra]

You don't need to use updateLob for CLOB. You can directly set it's value like a string.

updateLOB is only necessary if you are updating a BLOB columns.

$dados =  \DB::table('mytable')->where('id', $id)->update([
    'name'        =>'demo update blob', 
    'content'  => 'blob content here'
]);

[luiz-andrade]

When I use UPDATE only it throws the error below. Ps: I want to enter more than 2000 characters

QueryException in Connection.php line 666:

SQLSTATE[HY000]: General error: 1461 OCIStmtExecute: ORA-01461: � It is possible to connect a LONG value only for insertion into a LONG column (ext\pdo_oci\oci_statement.c:148)

[yajra]

Make sure that clob field bindings is at the end of statement like the one did on https://github.com/laravel/framework/pull/13469.

[luiz-andrade]

Yes, I do exactly the way you mentioned above

$dados =  \DB::table('mytable')->where('id', $id)->update([
    'name'        =>'demo update blob', 
    'content'  => 'blob content here'
]);

[yajra]

I just tried from a fresh install app. All works well.

// migration
        Schema::create('demos', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->text('content');
        });

// tinker
DB::table('demos')->insert(['name' => 'test', 'content' => 'asdasd']); // true
DB::table('demos')->whereId(1)->update(['name' => 'texxxst', 'content' => 'asdasd edited blob content']); // true

[luiz-andrade]

When I enter less than 3000 characters it works. The problem is when you exceed this amount

[mstaack]

maybe it's something how your columns are defined....

but i works for me too on a local test app! so it must be on your side

[luiz-andrade]

@mstaack , Did you try to insert more than 3000 characters?

[luiz-andrade]

Help me, please.

[yajra]

@luiz-andrade I just tried saving 3575 chars on an article and it works for me. This might be on your oci8 driver / environment. I tested on the ff:

• Ubuntu 16, instantclient 11g.
• MacOS High Sierra, instantclient 11g.

Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod
tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam,
quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo
consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse
cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non
proident, sunt in culpa qui officia deserunt mollit anim id est laborum.
Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod
tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam,
quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo
consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse
cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non
proident, sunt in culpa qui officia deserunt mollit anim id est laborum.
Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod
tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam,
quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo
consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse
cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non
proident, sunt in culpa qui officia deserunt mollit anim id est laborum.
Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod
tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam,
quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo
consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse
cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non
proident, sunt in culpa qui officia deserunt mollit anim id est laborum.
Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod
tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam,
quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo
consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse
cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non
proident, sunt in culpa qui officia deserunt mollit anim id est laborum.
Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod
tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam,
quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo
consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse
cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non
proident, sunt in culpa qui officia deserunt mollit anim id est laborum.
Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod
tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam,
quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo
consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse
cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non
proident, sunt in culpa qui officia deserunt mollit anim id est laborum.
Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod
tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam,
quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo
consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse
cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non
proident, sunt in culpa qui officia deserunt mollit anim id est laborum.

[luiz-andrade]

inserted using updateLob() ?

[luiz-andrade]

Excuse the insistence, but I really need to sort this out.

On the error in line 63, I discovered through the documentation the in function saveLob() the $parameter variable should start at: 1 and not 0, here http://php.net/manual/en/pdostatement.bindparam.php

PDOException in OracleProcessor.php line 63:
SQLSTATE[HY093]: Invalid parameter number: Columns/Parameters are 1-based

Changed it stays this way

 98     public function saveLob(Builder $query, $sql, array $values, array $binaries)
 99     {
100       $id        = 0;
101       $parameter = 1;
102       $statement = $this->prepareStatement($query, $sql);
103 
104       $parameter = $this->bindValues($values, $statement, $parameter);
105
106         $countBinary = count($binaries);
107         for ($i = 0; $i < $countBinary; $i++) {
108           $statement->bindParam($parameter, $binaries[$i], PDO::PARAM_LOB, -1);
109           $parameter++;
110         }
111
112         // bind output param for the returning clause.
113         $statement->bindParam($parameter, $id, PDO::PARAM_INT, 10);
114        if (! $statement->execute()) {
115             return false;
116         }
117
118           return (int) $id;
119        }

When $parameter = 0, it does not pass from here

$parameter = $this->bindValues($values, $statement, $parameter);

Put when $parameter = 1:

ErrorException in OracleProcessor.php line 114: PDOStatement::execute(): supplied argument is not a valid stream resource

[yajra]

@luiz-andrade it was inserted using Model::create([]) method. Saving contents on CLOB fields using direct binding works for me. It also works for me this way:

$model = Model::find(1);
$model->clob = 'test';
$model->save();

Anyways, can you please submit a PR on your findings? Thanks!

[luiz-andrade]

@yajra, Did not work. Sorry, but what is PR?

[Sithcarlos]

this works for me:

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use PDO;

$sql = "begin name_package.save_clob_sp(:p_clob,:p_id); end;";
return DB::transaction(function($conn) use ($sql, $data_clob) {
    try {
        $pdo = $conn->getPdo();
        $stmt = $pdo->prepare($sql);
        $stmt->bindParam(':p_clob', $data_clob, OCI_B_CLOB);
        $stmt->bindParam(':p_id', $p_id, PDO::PARAM_INT);
        $stmt->execute();
        return ["ok" => true, "result" => $p_id,];
    } catch (\Throwable $e) {
        Log::error($e->getMessage());
        return ["ok" => false, "result" => 'error please read the LOG',];
    }
}, 3);

environment configured with: php 7.3, oracle 11g