Primary key doesn't work on Node.js

Primary key doesn't work on Node.js

jschoijschoi Posts: 13Questions: 5Answers: 0

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

This question has accepted answers - jump to:

Answers

  • allanallan Posts: 63,523Questions: 1Answers: 10,473 Site admin

    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

  • jschoijschoi Posts: 13Questions: 5Answers: 0

    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?

  • allanallan Posts: 63,523Questions: 1Answers: 10,473 Site admin
    Answer ✓

    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

  • jschoijschoi Posts: 13Questions: 5Answers: 0
    edited February 2023

    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

  • jschoijschoi Posts: 13Questions: 5Answers: 0
    {
      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..

  • jschoijschoi Posts: 13Questions: 5Answers: 0
    edited February 2023

    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!

  • allanallan Posts: 63,523Questions: 1Answers: 10,473 Site admin
    Answer ✓

    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

  • jschoijschoi Posts: 13Questions: 5Answers: 0

    .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

  • allanallan Posts: 63,523Questions: 1Answers: 10,473 Site admin
    Answer ✓

    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

  • jschoijschoi Posts: 13Questions: 5Answers: 0

    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!

Sign In or Register to comment.