Help with strange sql/datatables issue

panzram

Hi,

A while back my database got updated and I've had many issues ever since. Most I've been able to solve, but one issue still eludes me. Whenever I try to add a new user I get an error message:

SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: '' for column 'mydb'.'users'.'id' at row 1

I've googled this and, as per the error message, I'm probably sending a blank value for the id field, for example:

INSERT INTO 'users' ('id', 'name') VALUES ('', 'dummy');

The database update forced me to either set every field to nullable or required. I've tried to set a "if blank string then null" statement, and a few other naive attempts without success. My client-side editor looks like this:

    editor = new $.fn.dataTable.Editor( {
        ajax: 'users/users',
        table: "#example",
        fields: [ 
             {
                label: "id",
                name: "id",
                type: "hidden"
            },

My server-side editor looks like this:

Editor::inst( $db, 'users', 'id')
    ->fields(
        Field::inst( 'users.id as id' ),
        Field::inst( 'profiles.user_id as user_id' )->validator( 'Validate::notEmpty' ),

I have a feeling that the solution is rather obvious to a datatables expert. Any advice is greatly appreciated.

tangerine

I use a Formatter rather than a Validator:

            Field::inst('table.field')->setFormatter( Format::ifEmpty(null) ),

explicitly supplying a null value.

panzram

Thank you.

I've tried the setFormatter and ifEmpty before, but I get an error saying that ifEmpty takes 3 parameters and I only supply 1. I guess it has to do with me being on an older version.

I've been fiddling around with the client-side and the preSubmit event:

editor.on( 'preSubmit', function ( e, data, action ) {
        if(!isNaN(data.data[0]['id'])) data.data[0]['id']=null;
        console.log(data);
    } );

The data is now null, but I still get the error...still lost.

allan

What version are you using? You could try:

Field::inst('table.field')->setFormatter( 'Format::ifEmpty', null ),

which is the old way of doing it. Make sure you remove the preSubmit on the client-side as well.

Allan

tangerine

Hi Allan.
My way is the "old" way? What's the new way then?

panzram

Hi Alan,

It looks like i'm on 1.5.4 for the editor...

Your legacy call suggesion doesn't throw an error, but the original issue is still there.

SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: '' for column 'mydb'.'users'.'id' at row 1

I did remove the clientside, preSubmit call.

panzram

tangerine, I think he meant that your way is the new way:

->setFormatter( Format::ifEmpty(null) ),

and that this is the old way:

->setFormatter( 'Format::ifEmpty', null ),

allan

My way is the "old" way? What's the new way then?
tangerine, I think he meant that your way is the new way:

Yup. Calling the function is the new way (it returns a function). The old way was passing a string with the function name in it.

It looks like i'm on 1.5.4 for the editor...

It'd be worth updating . We can debug from there if still present.

Allan

tangerine

Ah, right. Thanks, both.