EDITOR will not save, results in NULL id.
EDITOR will not save, results in NULL id.
billybob1
Posts: 2Questions: 1Answers: 0
Evaluate Datatables and Editor and been having a heck of a time. I generated a basic 'proof of concept' datatable with editor (PHP) to evaluate. And while initially appeared to work, I found out it does not save edit/updates.
Each time I click update I get:
An SQL error occurred: SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: ""
Using Postgres database.
Primary key is "id" (lowercase). Though I am explicitly stating that in the editor.
Popup window Editor (not bubble or inline)
Code is as follows. This was generated using Generator. Since then, and as an attempt to figure it out, the only change I made was to add in isSrc and Field:inst('id'), which based on other readings shouldnt be needed since Iam using 'id' as the primary key.
(sorry, cant provide a link to an example)
PHP
<?php
/*
* Editor server script for DB table transaction
* Created by http://editor.datatables.net/generator
*/
// DataTables PHP library and database connection
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
$out = Editor::inst( $db, 'transaction', 'id' )
->fields(
Field::inst( 'id' )->set(false),
Field::inst( 'transaction_date' )
->validator( Validate::dateFormat( 'Y-m-d' ) )
->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ) )
->setFormatter( Format::dateFormatToSql( 'Y-m-d' ) ),
Field::inst( 'chq_num' ),
Field::inst( 'payee_orig' ),
Field::inst( 'payee' ),
Field::inst( 'memo_orig' ),
Field::inst( 'memo' ),
Field::inst( 'category_id' ),
Field::inst( 'tax_category_id' ),
Field::inst( 'transaction_amount' ),
Field::inst( 'balance' )
)
->process( $_POST )
->json();
JS
/*
* Editor client script for DB table transaction
* Created by http://editor.datatables.net/generator
*/
(function($){
$(document).ready(function() {
var editor = new $.fn.dataTable.Editor( {
ajax: 'php/table.transaction.php',
table: '#transaction',
idSrc: 'id',
fields: [
{
"label": "Date:",
"name": "transaction_date",
"type": "datetime",
"format": "YYYY-MM-DD"
},
{
"label": "Chq:",
"name": "chq_num"
},
{
"label": "Payee:",
"name": "payee_orig"
},
{
"label": "Memo:",
"name": "memo_orig"
},
{
"label": "Category:",
"name": "category_id"
},
{
"label": "Accounting:",
"name": "tax_category_id"
},
{
"label": "Amount:",
"name": "transaction_amount"
},
{
"label": "Balance:",
"name": "balance"
}
]
} );
} );
var table = $('#transaction').DataTable( {
dom: 'Bfrtip',
ajax: 'php/table.transaction.php',
columns: [
{
"data": "transaction_date"
},
{
"data": "chq_num"
},
{
"data": "payee_orig"
},
{
"data": "memo_orig"
},
{
"data": "category_id"
},
{
"data": "tax_category_id"
},
{
"data": "transaction_amount"
},
{
"data": "balance"
}
],
select: true,
lengthChange: false,
buttons: [
{ extend: 'create', editor: editor },
{ extend: 'edit', editor: editor },
{ extend: 'remove', editor: editor }
]
} );
} );
}(jQuery));
Answers
Maybe there is missing CREATE SEQUENCE statement for your id.
https://postgresql.org/docs/9.5/sql-createsequence.html
Or change id's data type to 'serial'
That's what I was thinking as well. Can you show me the SQL schema you are using for the database? Generator created pkey fields as
serial- are you using the Generator SQL or your own schema?Allan
Figured it out....
To answer the above, turns out it was not an issue with the id column after all. I am using my own schema and the "id" column was not created as a serial value. Though it did have a sequence defined. Still, flaw I had there was inconsequential to this issue (but that might have been my next issue/question).
As I said, it was not the issue because the tests I was running were for updates of existing values. The intent for this POC was not to add new data (a separate process will do that on a nightly batch). But to view, review and update records once they have been loaded inot the DB. Thus only been testing updating records at present. And frankly, if update wont work, then insert most likely wont (and it didnt, but got same/simialr error so thought it was the same issue).
Still, I recreated the table, made the ID column serial as suggested above with hope of reoslution. But still no luck.
I then downloaded examples (simple.html) and after generating the required tables into my DB (again, hoping that maybe I had something wrong in my DB as a whole) noticed that the simple.html example worked as expected. <sigh> So... I started reverse engineering the example, field by field. And it wasn’t until I got to adding in the field:
That the issue started up again.... But only when I updated the field to <blank> .... ding ding ding. The DB column allows NULL value, but the code was not formatting an empty value as null. Instead, formatting it as a string that was being rejected [by postgre].
Needless to say.
Is the winning resolution. Requirement being to set the formatted so an empty value is interpreted as 'null', not the 'empty string'.
Looking back, I noticed the Generator does not add this formatter. Maybe cause not all implementations need this. So word to the wise, if you expect null values on a number field, you need this setting .
Thank you both (@Danny Gunawan, @allan ) for your responses. While thy weren’t the actual answers for this specific case, it did start me down the right path to figuring this out.... AND, bonus, your answers did answer the question I did not know I had yet (ie: why wont it insert new rows).
Thanks
B