Moodle in English: Database API Problem

Hi there,

I have a curious problem in that I try to insert an object called $slides into the database using

 $id = $DB->insert_record('slideshow', $slides);

It returns a value for the ID incremented from the previous one but does NOT insert the data into the database.

Any clues would be gratefully received. (debugging is on full power).

Cheers

Dave

Average of ratings: -

Re: Database API Problem

by Mark Johnson - Thursday, 22 June 2017, 8:59 PM

I can't tell you what's going on, but some info that might help shed some light, if you're able to provide it:

The structure of the slideshow table.
The output from calling print_object($slides) just before that line.
Which RDMBS software you're using.

Average of ratings: -

Re: Database API Problem

by Dave Emsley - Thursday, 22 June 2017, 9:08 PM

Hi Mark,

MySQL is the database.

Output from print_object is:

stdClass Object ( [name] => Yet another attempt [mform_isexpanded_id_packagehdr] => 1 [slidetype] => local [packagefile] => 91794512 [popup] => 0 [nav] => 0 [timeopen] => 0 [timeclose] => 0 [maxattempt] => 0 [forcenewattempt] => 0 [lastattemptlock] => 0 [redirect] => no [redirecturl] => ../mod/slides/view.php?id= [visible] => 1 [cmidnumber] => [groupmode] => 0 [groupingid] => 0 [availabilityconditionsjson] => {"op":"&","c":[],"showc":[]} [tags] => [course] => 2 [coursemodule] => 52 [section] => 3 [module] => 30 [modulename] => slides [completion] => 0 [completionview] => 0 [completionexpected] => 0 [intro] => This is the description.&nbsp; Should be displayed on course page<br><p><br></p> [introformat] => 1 [options] => scrollbars=0,directories=0,location=0,menubar=0,toolbar=0,status=0 )

And the Database structure is:

Hope that makes sense,

Cheers

Dave

Average of ratings: -

Re: Database API Problem

by Conn Warwicker - Thursday, 22 June 2017, 9:37 PM

Can you do a screenshot of:

print_object($slides)
print_object($id) - After you've called insert_record()
And then a screenshot of the latest records actually in the database table?

Average of ratings: -

Re: Database API Problem

by Dave Emsley - Thursday, 22 June 2017, 9:57 PM

Hi Conn,

The $slides object is:

stdClass Object ( [name] => This is a the name of the slides package [showdescription] => 1 [mform_isexpanded_id_packagehdr] => 1 [slidetype] => local [packagefile] => 154063038 [popup] => 0 [nav] => 0 [timeopen] => 0 [timeclose] => 0 [maxattempt] => 0 [forcenewattempt] => 0 [lastattemptlock] => 0 [datadir] => [pkgtype] => [launch] => [redirect] => no [redirecturl] => ../mod/slides/view.php?id= [visible] => 1 [cmidnumber] => [groupmode] => 0 [groupingid] => 0 [availabilityconditionsjson] => {"op":"&","c":[],"showc":[]} [tags] => [course] => 2 [coursemodule] => 64 [section] => 3 [module] => 30 [modulename] => slides [instance] => [add] => slides [update] => 0 [return] => 0 [sr] => 0 [competency_rule] => 0 [submitbutton2] => Save and return to course [completion] => 0 [completionview] => 0 [completionexpected] => 0 [completiongradeitemnumber] => [conditiongradegroup] => 0 [conditionfieldgroup] => 0 [intro] => This is the description of the slides package

[introformat] => 1 [completionstatusallscos] => 0 [options] => )

$id is a variable not an object from:

 $id = $DB->insert_record('slideshow', $slides);

which has kept returning incremented values during testing. Currently 40.

I created a dummy file to test the actual database and force data so all that it contains is below.

Cheers

Dave

Average of ratings: -

Re: Database API Problem

by Conn Warwicker - Thursday, 22 June 2017, 10:35 PM

Hi,

You can still call print_object on a variable that isn't an object, it will just print it out. I just wanted to see what id it prints out, compared to what the latest in the database is.

Can you give us the code for the full function or script that is doing this?

Average of ratings: -

Re: Database API Problem

by Dave Emsley - Thursday, 22 June 2017, 11:10 PM

Hi Conn,

print_object($id) gives the value 17 which is correct but it doesn't insert it into the table. The code is an adaptation of the SCORM module.

function slides_add_instance($slides, $mform=null) {
    global $CFG, $DB;

    require_once($CFG->dirroot.'/mod/slides/locallib.php');

    $cmid       = $slides->coursemodule;
    $cmidnumber = $slides->cmidnumber;
    $courseid   = $slides->course;

    $context = context_module::instance($cmid);

    $slides = slides_option2text($slides);
    print_object($slides);
    $id = $DB->insert_record('slideshow', $slides); 
    print_object($id);
    echo "<br><br>Record ADDED!!!<br><br>";
    echo "ID of the insert is ".$id;

Cheers

Dave

Average of ratings: -

Re: Database API Problem

by Conn Warwicker - Friday, 23 June 2017, 1:51 AM

You said earlier when you ran it, the id printed out "40", but in the database the latest record was "39", so the record is not being inserted into the database. Where did the record "39" come from? Was that inserted some other way? Or is it simply returning the wrong value of the id?

Assuming you';ve checked all the things D.Smith put in his post, my next suggestion would be to put an exit straight after the insert_record and see if the record gets put into the database. If it is there, then that must mean that something is causing it to be deleted.

Failing that, go into /lib/dml/yourdatabaseengine_native_moodle_database.php, find the method "insert_record_raw" and start debugging in there. Do print outs and exits to see what exactly it is doing when you call your insert_record method.

Average of ratings: -

Re: Database API Problem

by Richard Oelmann - Thursday, 22 June 2017, 10:24 PM

Shouldn't the instruction to insert the record simply be

$DB->insert_record('slideshow', $slides);

Is it the adding the $id = at the beginning that gets it to simply return the id value?

(At least, in some of my code, that's what I've used and what works for me - possibly I've done it wrong, but it works! )

Richard

Average of ratings: -

Re: Database API Problem

by Dave Emsley - Thursday, 22 June 2017, 10:31 PM

Will give it a go Richard - but https://docs.moodle.org/dev/Data_manipulation_API#Inserting_Records says otherwise.

No that didn't work.

Cheers

Dave

Average of ratings: -

Re: Database API Problem

by Richard Oelmann - Thursday, 22 June 2017, 10:36 PM

Yep, I realise,

but a code search for $DB->insert_record through the moodle core code shows thats how it is used there in many locations

eg:

./course/lib.php:2200: $DB->insert_record('role_names', $rolename);

./course/format/lib.php:765: $DB->insert_record('course_format_options', array(

./webservice/tests/externallib_test.php:70: $DB->insert_record('external_services_functions', array('externalserviceid' => $externalserviceid,

./repository/lib.php:2061: $DB->insert_record('repository_instance_config', $config);

./grade/grading/lib.php:402: $DB->insert_record('grading_areas', $area);

And many others, as well as many instances where there is a variable in front, but I haven't looked at the code to see if there is another reason for the variable - for instance I also have code that gets the variable for the new record id so I can then update the record a couple of lines further on in my code.

Hmmm - works for me... (not a helpful response I know)

Average of ratings: -

Re: Database API Problem

by Conn Warwicker - Thursday, 22 June 2017, 10:38 PM

It's the same thing, returning the value from the method call or not shouldn't affect anything.

Average of ratings: -

Re: Database API Problem

by Richard Oelmann - Thursday, 22 June 2017, 10:43 PM

Fair enough - just one thing ruled out then...

Average of ratings: Useful (1)

Re: Database API Problem

by Dave Emsley - Thursday, 22 June 2017, 10:57 PM

Yep, any thoughts are gratefully received I've been ay this all day so far.

Cheers

Dave

Average of ratings: -

Re: Database API Problem

by Davo Smith - Thursday, 22 June 2017, 11:46 PM

A few random thoughts, some of which might help (or may not):

Are you definitely connected to the database you think you are (assuming you are using Adminer or PHPMyAdmin or similar to check whether or not the data is being inserted)?
- Related to that, are other expected changes being written into that database (e.g. does adding a forum post insert the data as expected?)
Is the insert wrapped inside a transaction that is being aborted, rather than being committed?
Have you put a breakpoint on the insert_record line and stepped through the code to see exactly what is going on?
Maybe using $DB->set_debug(true) before that line and then trying to manually run the generated SQL directly on your database via Adminer / PHPMyAdmin (but you will have to manually insert the variables + table prefixes before you can run it directly).
Is there any code path that could cause the record to be deleted immediately after it has been inserted (are there any $DB->delete_records() calls that might remove it?)
Did you define the table using the XMLDB editor (otherwise Moodle might not know about the fields in the database table and may remove them all before the insert takes place)?

Average of ratings: -

Re: Database API Problem

by Dave Emsley - Friday, 23 June 2017, 4:30 PM

Hi Davo, thanks for responding. System is on a local server so I have full access to everything.

I believe so - I can insert directly using code:

<?php
require_once("../../config.php");
require_once($CFG->dirroot.'/mod/slides/locallib.php');
global $CFG, $DB;
// Cut out all the fields I do not need and do noy use.
$record = new stdClass();
    $record->name='Yet another very silly Slides Activity';
    $id = $DB->insert_record('slideshow', $record); //replaced $slides by $record
    echo "<br><br>Record ADDED!!!<br><br>";
    echo "ID of the insert is ".$id;
    // Reload slide instance.
    $record = $DB->get_record('slideshow', array('id' => $id));
    echo "<br>SLIDETYPE IS ".$record->slidetype;
    echo "<br><br><br>";
?>

"Insert wrapped inside a transaction" - sorry not sure what you mean by that.
Not used breakpoint but I've tried echoing to screen to see what is happening which is why I am puzzled that gets beyond that point AND increments id without inserting. Looking into this now. It is a new technique to me for php.
set-debug(true) gives the correct SQL - INSERT INTO mdl_slideshow (name) VALUES(?) [array ( 0 => 'This is getting very silly now', )]
table defined in the installation file mod/slides/db/install.xml.
(edit)
Just looked at the XMLDB editor and get "XML Error: Element 'FIELDS': Character content other than whitespace is not allowed because the content type is 'element-only' at line 9."
Unsure what that means so going back to step 1 and creating the db from scratch.
(end edit)

Cheers

Dave

Average of ratings: -

Re: Database API Problem

by Dave Emsley - Friday, 23 June 2017, 5:47 PM

Used the XMLDB editor to remake the entire database but to no effect. Still the same issues.

Average of ratings: -

Re: Database API Problem

by Davo Smith - Friday, 23 June 2017, 7:02 PM

On the transaction point, it is possible to wrap multiple DB updates in a transaction, so either they are all committed, or none of them are.

You do this by writing:

$transaction = $DB->start_transaction();

... various db updates ...

$transaction->allow_commit();

If an error occurs, then the transaction is rolled back and none of the changes are written to the database.

It was a long-shot, but worth checking.

I would still strongly advise using an IDE to put a breakpoint in the code and step through it - that is usually the best way to understand exactly what is going wrong.

Average of ratings: Useful (1)

General developer forum

Database API Problem

Database API Problem

Re: Database API Problem

Re: Database API Problem

Re: Database API Problem

Re: Database API Problem

Re: Database API Problem

Re: Database API Problem

Re: Database API Problem

Re: Database API Problem

Re: Database API Problem

Re: Database API Problem

Re: Database API Problem

Re: Database API Problem

Re: Database API Problem

Re: Database API Problem

Re: Database API Problem

Re: Database API Problem

Re: Database API Problem

Empowering educators to improve our world

Moodle

Support

Get Involved

Contributions

Downloads

Tracker

Development

Empowering educators to improve our world