Error writing to database

Error writing to database

by Ivan Tomac -
Number of replies: 8

Hi,

I'm trying to insert some values in my table ( which is part of moodle database ).

Code:

    global $USER;
   
    $record = new stdClass();
    $record->courseid = $course->id;
    $record->userid = $USER->id;
    $record->categoryid = $course->category;
    echo "<br> $record->courseid <br> $record->userid <br> $record->categoryid <br>";

    try {
        $transaction = $DB->start_delegated_transaction();
        $DB->insert_record('user_courses', $record, false);
        $transaction = $DB->allow_commit();
    } catch (Exception $e) {
        $transaction->rollback($e);
    }


I'm getting this error:

Attachment 63HyqBU.png
Average of ratings: -
In reply to Ivan Tomac

Re: Error writing to database

by Darko Miletić -
Picture of Core developers Picture of Moodle HQ Picture of Plugin developers
What is the structure of your custom table? The error you get is indicating that insert is not being able to obtain value of the id field. Does your table have id field? Is it set as primary key with autoincrement?

Average of ratings: -
In reply to Darko Miletić

Re: Error writing to database

by Ivan Tomac -
It doesn't have id field. It has composite primary key ( courseid, userid, categoryid). No auto increment.
Average of ratings: -
In reply to Ivan Tomac

Re: Error writing to database

by Davo Smith -
Picture of Core developers Picture of Peer reviewers Picture of Plugin developers

Then that fully explains the problem.

All database tables in Moodle must have an autoincrement field called 'id' as the primary key.

When you use the built-in XLMDB editor to generate the relevant install.xml for your plugin (and, if needed, any upgrade steps), it will make sure that this, and any other constraints, are fulfilled.


Average of ratings: -
In reply to Ivan Tomac

Re: Error writing to database

by Darko Miletić -
Picture of Core developers Picture of Moodle HQ Picture of Plugin developers

In general Moodle expects that all tables have a primary key called id so you will have to provide one if you plan on using DB API for inserting records.



Average of ratings: -
In reply to Darko Miletić

Re: Error writing to database

by Ivan Tomac -

What's the cons if I don't use DB API for inserting records?

Average of ratings: -
In reply to Ivan Tomac

Re: Error writing to database

by Darko Miletić -
Picture of Core developers Picture of Moodle HQ Picture of Plugin developers

Than you have something that only works on one RDBMS. If you plan on distributing this to any potential user that ends up using different rdbms than this approach will backfire.

You can do something else instead that is better than using direct specific API.

$sql = "INSERT INTO {user_courses} ('col1', 'col2', 'col3') VALUES(:v1, :v2, :v3)";
$params = ['v1' => 123, 'v2' => 223, 'v3' => 323];
$DB->execute($sql, $params);



Average of ratings: -