Using the Database.php class with Firebird
Using the Database.php class with Firebird
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
Hi @SynapseUser ,
Could you post your client side JS code as well, please.
Cheers,
Colin
Hi @colin
Sure - here is the client java code..
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
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.
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:
Hope that gets you going,
Cheers,
Colin
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..
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
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
Hi Dave,
In your
config.php
file could you try using: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
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.
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 theEditor.php
file just after thejson_encode()
.Allan