Primary key doesn't work on Node.js

jschoi

I set editor object as below

let editor = new Editor( db, 'CLIENT_MASTER', 'CLIENT_MASTER_ID')

CLIENT_MASTER is table
CLIENT_MASTER_ID is primary key of above table

then submitted data as below
{
data: [
{
CLIENT_MASTER_ID: '7',
COMPANY_NAME: 'ttttt',
ABN: 'ttttt',
CONTACT_PERSON: 'ttttt',
ADDRESS: 'ttttt',
POSTAL_ADDRESS: 'ttttt',
PHONE: 'ttttt',
FAX: 'ttttt',
EMAIL_ADDRESS: 'ttttt',
CREATED_AT: '2023-02-26T14:10:11.000Z',
UPDATED_AT: '2023-02-26T14:10:11.000Z'
}
],
action: 'remove'
}

but built query as below
delete from CLIENT_MASTER where (CLIENT_MASTER_ID = ?)

As shown as primary key value is 7
but it is not applied built query

allan

It looks okay to me. Are you wondering what the question mark is for? It is a bound parameter - ie a value that will be escaped automatically by the SQL server, so as to not allow any injection attacks.

You need the debug to show you the bound values as well, which should show 7 in this case.

Allan

jschoi

below is knex debug log

binding is '0', it seems like that key value of data is not binding to sql.

{
method: 'del',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [ '0' ],
__knexQueryUid: 'p9sF0Kv8JV76_qrFjOUKt',
sql: 'delete from CLIENT_MASTER where (CLIENT_MASTER_ID = ?)'
}

how can I fix it?

allan

Can you show me your full controller code for Editor, and also a screenshot of the Ajax request data being sent to the server please?

Thanks,
Allan

jschoi

javascript code of web page

`
editor = new $.fn.dataTable.Editor({
ajax : "/process/set_list_client",
table : "#itpGrid1",
idSrc : "CLIENT_MASTER_ID",
fields : [
{label : 'Company Name', name : 'COMPANY_NAME'}
,{label : 'ABN', name : 'ABN'}
,{label : 'Contact Person', name : 'CONTACT_PERSON'}
,{label : 'Address', name : 'ADDRESS'}
,{label : 'Postal Address', name : 'POSTAL_ADDRESS'}
,{label : 'Phone', name : 'PHONE'}
,{label : 'Fax', name : 'FAX'}
,{label : 'Email Address', name : 'EMAIL_ADDRESS'}
]
})

$("#itpGrid1").DataTable({
ajax : {
url : "/process/get_list_client",
type : "post",
dataSrc : "data"
},
dom: 'Bfrtip',
columns : [
{data : 'COMPANY_NAME'}
,{data : 'ABN'}
,{data : 'CONTACT_PERSON'}
,{data : 'ADDRESS'}
,{data : 'POSTAL_ADDRESS'}
,{data : 'PHONE'}
,{data : 'FAX'}
,{data : 'EMAIL_ADDRESS'}
//,{data : 'CLIENT_MASTER_ID' , visible : false}
],
select : true,
buttons : [
{extend: "create", editor:editor},
{extend: "edit", editor:editor},
{extend: "remove", editor:editor}
]
})
`

Server js code

`
router.all('/set_list_client', async (req, res) => {
console.log(req.body)

let editor = new Editor( db, 'CLIENT_MASTER', "CLIENT_MASTER_ID")
    .fields(
          new Field('COMPANY_NAME')
        , new Field('ABN')
        , new Field('CONTACT_PERSON')
        , new Field('ADDRESS')
        , new Field('POSTAL_ADDRESS')
        , new Field('PHONE')
        , new Field('FAX')
        , new Field('EMAIL_ADDRESS')
        //, new Field('CLIENT_MASTER_ID')
    ).on('postRemove', (editor, id, values) => {
        // id = req.body.data[0].CLIENT_MASTER_ID
        // console.log(id)
        // console.log(editor.pkey())
        //console.log(values);
    })

let result = await editor.process(req.body)

//console.log(result)

res.json( editor.data() );

})
`

Ajax request data

{
data: [
{
CLIENT_MASTER_ID: '7',
COMPANY_NAME: 'ttttt',
ABN: 'ttttt',
CONTACT_PERSON: 'ttttt',
ADDRESS: 'ttttt',
POSTAL_ADDRESS: 'ttttt',
PHONE: 'ttttt',
FAX: 'ttttt',
EMAIL_ADDRESS: 'ttttt',
CREATED_AT: '2023-02-26T14:10:11.000Z',
UPDATED_AT: '2023-02-26T14:10:11.000Z'
}
],
action: 'remove'
}

it is including remark for debugging, but it is short and simple, so you can check I think.

Thank you

jschoi

{
  DT_RowId: 'row_8',
  COMPANY_NAME: 'TEST COMPANY',
  ABN: '12345678',
  CONTACT_PERSON: 'dyne choi',
  ADDRESS: 'test address',
  POSTAL_ADDRESS: 'test postal address',
  PHONE: '010-0000-0000',
  FAX: '010-0000-0000',
  EMAIL_ADDRESS: 'testaddress@gmail.com'
}

I checked DT_RowId is including received data from server side.
but this problem is not solved yet..

jschoi

I found the way to solve this problem

new Field('CLIENT_MASTER_ID').getFormatter( (val) => {
                return 'row_' + val;
            } ).set(false)

idSrc Field should be include above code.

I think It should be included manual of Node js.
I found it on sample code of jsonId.js in package for node js.

Thank you!

allan

Just to check my understanding - the .set(false) is what resolved this issue? If so, I'll make sure to add a clarification for this to the docs.

Allan

jschoi

.set(false) is for ignored input parameter such as ID

I think .getFormatter ( (val) => { return 'row_' + val ;})

is resolve this issue.

please check again and add to the manual.

Thank you

allan

What should be happening is that you can have:

new Field('CLIENT_MASTER_ID').set(false)

Assuming you want to display the ID in the table. If you don't, just remove that fields.

Then in Editor's client-side Javascript, remove the idSrc option, since the Editor server-side libraries will automatically add DT_RowId as a property to the data retrieved and Editor will use that by default.

Allan

jschoi

Good. As you said, it is working with only set(false) and remove option idSrc

It is better way than I had.

I appreciate it!