Datatables Editor gives error when update data with column name have special characters in it.

Datatables Editor gives error when update data with column name have special characters in it.

Aarti SharmaAarti Sharma Posts: 8Questions: 5Answers: 0
edited March 2020 in Free community support

I am using Datatables Editor to update data server side.Json data i get from server is -

{
  "data": [
    {
      "DT_RowId": "row_1",
      "resistors": {
        "Part_Name_Prefix": "",
        "Part_Number": "RES 25 Ohms - \u00b11% - 0402",
        "Voltage": "",
        "Tolerance": "\u00b15%",
        "Value": "25 Ohms",
        "CPN": "800-00001",
        "Library_Ref": "RES",
        "Library_Path": "LIB001.SchLib",
        "Comment": " =Value",
        "Component_Kind": "Standard",
        "Component_Type": "Standard",
        "Description": "0402, 25 OHM",
        "Designator": "",
        "Manufacturer": "AVX Corporation",
        "Manufacturer_Part_Number": "UBR0402B250FZTR500",
        "Manufacturer1": "",
        "Manufacturer_Part_Number1": "",
        "Packaging": "",
        "Pin_Count": "2",
        "Signal_Integrity": "",
        "Simulation": "",
        "Supplier": "Digi-Key",
        "Supplier_Part_Number": "478-12027-2-ND",
        "Supplier1": "122456-43",
        "Supplier_Part_Number1": "mouser",
        "Footprint_Path": "LIB001.PcbLib",
        "Footprint_Ref": "RES0402",
        "ComponentLink1Description": "Datasheet",
        "ComponentLink1URL": "http:\/\/datasheets.avx.com\/AVX-UBR-Series.pdf",
        "Status": "green",
        "revise_datasheet": "unknown",
        "revision": "O",
        "date": "2019\/04\/12",
        "RoHS": "RoHS Compliant",
        "Life_cycle": "Active",
        "Lead_free": "Lead free",
        "part_img": "pdfimg\/LIB001\/partimg\/RES-L9-PIMG-UBR0402B250FZTR500.jpg",
        "sym_img": "",
        "pcb_img": "",
        "threeD_img": "",
        "Power Watts": "",
        "Composition": "",
        "Temperature Coefficient": "",
        "Size Dimension": "",
        "Height Seated Max": "",
        "Co_PN": "123-blah",
        "[Footprint Path 2]": "LIB001.PcbLib",
        "[Footprint Ref 2]": "RES0805"
      },
      "revision": {
        "userID": "System User"
      }
    },

As you can see last two column names have brackets around it.

Datatables not showing data for these two columns and giving error while updating.

Error given in debugger is-

"<br />
<b>Notice</b>: Undefined index: [Footprint Path 2] in <b>C:\xampp\htdocs\members\DataTables-1.10.18\extensions\Editor-1.9.0\php\Editor\Field.php</b> on line <b>574</b><br />
<br />
<b>Notice</b>: Undefined index: [Footprint Ref 2] in <b>C:\xampp\htdocs\members\DataTables-1.10.18\extensions\Editor-1.9.0\php\Editor\Field.php</b> on line <b>574</b><br />
{"error":"SQLSTATE[HY093]: Invalid parameter number: parameter was not defined"}"

Could you please help me to solve how is it possible to do work on editor with this column name in database.

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

Answers

  • allanallan Posts: 61,451Questions: 1Answers: 10,055 Site admin

    You are using [] in the database column name? I'm not sure I've seen that before to be honest, and as you are seeing here, it isn't something we've tested against.

    You could try this:

    Field::inst('[Footprint Path 2]', 'FootprintPath2')
    

    Then update your client-side code to use FootprintPath2 as the parameter to work with (both DataTables and Editor).

    That code tells the PHP libs to use the first parameter as the database column name, while the second is the one to use for JSON and HTTP parameters (code).

    Allan

  • briandouglasbdcbriandouglasbdc Posts: 1Questions: 0Answers: 0

    SQL Server puts brackets around columns if they have spaces. I have the same problem but I have columns that looks like 'Column[Sub1]', 'AnotherColumn[Sub2]'. It seems datatables just strips out the [Sub1] and makes it 'Column'

    I suspect the only fix is to hack the names from the server and convert them back in the backend.

  • allanallan Posts: 61,451Questions: 1Answers: 10,055 Site admin

    This is where we are tripping up. Our libraries do attempt to escape identifiers for SQL Server by wrapping square brackets around them, but because the libraries don't implement a full SQL parser, they naively assume that if there is a space, then you are operating on some kind of expression (e.g. a function) as use it as is.

    At the moment, the workaround is to not include spaces in the identifier names. The other option is to remove that check for the spaces in the libraries. A final option might be to introduce something like a SPACE identifier that the libraries will see and replace as a space while escaping the SQL identifiers, but that seems non-discoverable to me.

    Allan

This discussion has been closed.