Using the Database.php class with Firebird

Using the Database.php class with Firebird

SynapseUserSynapseUser Posts: 11Questions: 3Answers: 0

Hi -
I am trying to get the examples running for a Firebird database. I have managed to get it to connect to the database, I edited the staff.php file to include fields relevant to my situaiton, but it just throws an sql exception complaining reporting..

** "error code = -206 Column unknown id At line 1, column 1.."**

I cant see a reference to a field called "id" in staff.php or my html markup that calls the php file. Quite honestly, I find the whole system of related PhP files objects very confusing. Although the "manual" mentions that there is an Editor->where function but only a snippet of code that shows how to use it (still not sure where to call or configure this method.)

This is my version of the "staff.php" file

<?php

/*
 * Example PHP implementation used for the index.html example
 */

// DataTables PHP library
include( "../lib/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'INVOICE' )
    ->fields(
        Field::inst( 'TRANSID' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'TRANSID REQUIRED' ) 
            ) ),
        Field::inst( 'CUSTNAME' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A  name is required' )  
            ) ),

        Field::inst( 'TRANSDATE' )
            ->validator( Validate::dateFormat( 'Y-m-d' ) )
            ->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ) )
            ->setFormatter( Format::dateFormatToSql('Y-m-d' ) )
    )
    ->process( $_POST )
    ->json();

I have edited the calling html file with the same fields defined for the datatabe object.

Any hints or idea as to why the "id" field error is being thrown would be much appreciated.
Thanks
Dave.

Answers

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    Hi @SynapseUser ,

    Could you post your client side JS code as well, please.

    Cheers,

    Colin

  • SynapseUserSynapseUser Posts: 11Questions: 3Answers: 0
    edited November 2018

    Hi @colin

    Sure - here is the client java code..

    $(document).ready(function() {
        editor = new $.fn.dataTable.Editor( {
            ajax: "../../controllers/staff2.php",
            table: "#example",
            fields: [ {
                    label: "TRANS ID:",
                    name: "TRANSID"
                }, {
                    label: "Cust Name",
                    name: "CUSTNAME"
                }, {
                    label: "Trans Date",
                    name: "TRANSDATE"
                }
            ]
        } );
    
        // Activate an inline edit on click of a table cell
        $('#example').on( 'click', 'tbody td:not(:first-child)', function (e) {
            editor.inline( this );
        } );
    
        $('#example').DataTable( {
            dom: "Bfrtip",
            ajax: "../../controllers/staff2.php",
            order: [[ 1, 'asc' ]],
            columns: [
                {
                    data: null,
                    defaultContent: '',
                    className: 'select-checkbox',
                    orderable: false
                },
                { data: "TRANSID" },
                { data: "CUSTNAME" },
                { data: "TRANSDATE" }
    
            ],
            select: {
                style:    'os',
                selector: 'td:first-child'
            },
            buttons: [
                { extend: "create", editor: editor },
                { extend: "edit",   editor: editor },
                { extend: "remove", editor: editor }
            ]
        } );
    } );
    

    As you can see, I simply substituted the original fields with those to match my database table and renamed the php file to "Staff2.php"

    Thanks :smile:

  • SynapseUserSynapseUser Posts: 11Questions: 3Answers: 0
    edited November 2018

    So its basically the example simple.html file included with the examples, just with some field / table definition changes to suite my data.

    Thanks again.

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    Hi @SynapseUser ,

    I suspect it's because you haven't created a primary key on the table that you're using - see this section of the PHP manual.

    For example, in my table, I've got this:

    CREATE TABLE `datatables_demo` (
        `id`         int(10) NOT NULL auto_increment,
        `source` varchar(250) NOT NULL default '',
        `creation_date` datetime default NULL,
        `description`   varchar(250) NOT NULL default '',
        `example_url`      varchar(250) NOT NULL default '',
        `information_url`     varchar(250) NOT NULL default '',
        `notes`      varchar(1024) NOT NULL default '',
        PRIMARY KEY  (`id`)
    );
    

    Hope that gets you going,

    Cheers,

    Colin

  • SynapseUserSynapseUser Posts: 11Questions: 3Answers: 0

    Hi @colin

    Yes indeed, that was the solution to that problem, thanks! I am now receiving another error unfortunately, there seems to be some contradiction with regards the actual error.

    I get a dialog from datatables that reads..

    But on the console I get..

    jquery.dataTables.min.js:49 Uncaught TypeError: Cannot read property 'length' of undefined
    at jquery.dataTables.min.js:49
    at i (jquery.dataTables.min.js:35)
    at Object.success (jquery.dataTables.min.js:36)
    at fire (jquery-3.3.1.js:3268)
    at Object.fireWith [as resolveWith] (jquery-3.3.1.js:3398)
    at done (jquery-3.3.1.js:9305)
    at XMLHttpRequest.<anonymous> (jquery-3.3.1.js:9548)

    Now the database I am using is Firebird, so afaik should be utf8 by default ? I did however place this directive in the php file..

    define("DB_CHARSET", "utf8");

    But this does not cure the problem, thanks for your continued help :)

    Cheers..

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    Hi @SynapseUser ,

    When you created that id column in the database, did you start the table afresh, or did you just add the column so that it contains empty values for the existing rows?

    Cheers,

    Colin

  • SynapseUserSynapseUser Posts: 11Questions: 3Answers: 0

    Hi Colin -

    I did not create an ID column, I just passed the existing key to the Editor create routine and it stopped complaining about not being able to find the "id" field. So I assumed it had solved that problem.

    When I run the php script on its own, the json-packaged result seems valid,In fact, the structure looks identical to that shown in the inline editing example here

    https://editor.datatables.net/examples/inline-editing/simple.html

    So not sure what the problem is. I may have to use the sql scripts provided to re-create the "staff" table used in the example
    Cheers
    Dave

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Hi Dave,

    In your config.php file could you try using:

    "dsn" => ";charset=UTF8"
    

    in the database connection array. That will then be appended to the connection string which should ensure that everything is UTF8.

    Regarding the id field, Editor needs a primary key column in the database and by default it is assumed to be called id. You can change that to any other name using the optional third parameter for the factory method.

    Allan

  • SynapseUserSynapseUser Posts: 11Questions: 3Answers: 0
    edited November 2018

    Hi Allan -

    Thanks for your response. Good tips, but already done those (specify the charset in the dsn and include the key as a parameter,)

    Unfortunately, still get the errors I mentioned before. I am going to have to try use the sql script provided to try re-create and populate the table used in the original "inclne editing / simple" example in Firebird - and see if its the database not the data that is causing the problem and not the table/ data.

    Would be interesting t hear is anyone else has had this example (or similar editor example) working with Firebird and any help / tips they can offer,

    Cheers.

    Dave.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Hi Dave,

    To be honest, I've only heard of one or two others actually using Editor with Firebird and I'm afraid I've not heard of this specific issue.

    It might be interesting to use json_last_error() to see what caused the error. You could add that into the Editor.php file just after the json_encode().

    Allan

This discussion has been closed.