node js library knex & mssql are dated, but latest version doesnt work properly.

Clark S

hi, just found if I install knex & mssql with their latest version then editor stops working... any suggestion what version I can go for without a bunch of critical warnings? thnx.

colin

When you said it doesn't work, what errors are you seeing? Also, could you please let us know the versions of both that you've installed.

Colin

Clark S

hi Colin, so... it started working after I restarted my mac... no sure exactly what happened. will post in detail if this happens again.

Clark S

hi Colin, so here is the issue with the latest version of mssql & knex.
i have an upload filed and an error below occurs when using the latest knex & mssql.
,
any thoughts? thnx in advance.

allan

Nothing immediately springs to mind I'm afraid. Can you show me your NodeJS code for the Editor part and also the database schema for the upload table please?

Thanks,
Allan

Clark S

Node js Code is pretty much generated by the generator...

router.all("/api/inventory/canadian-import", async function (req, res) {
  let editor = new Editor(db, "inventory_canadian_import", "id").fields(
    new Field("invoice_no")
      .validator(Validate.notEmpty())
      .validator(Validate.minMaxLen(3, 50)),
    new Field("purchase_from").validator(Validate.notEmpty()),
    new Field("bol")
      .validator(Validate.notEmpty())
      .validator(Validate.minMaxLen(3, 50)),
    new Field("purchase_location")
      .validator(Validate.notEmpty())
      .validator(Validate.minMaxLen(3, 50)),
    new Field("carrier_number")
      .validator(Validate.notEmpty())
      .validator(Validate.minMaxLen(1, 50)),
    new Field("hauler").validator(Validate.notEmpty()),
    new Field("fuel_type")
      .set(false)
      .validator(Validate.notEmpty())
      .validator(Validate.minMaxLen(3, 50)),
    new Field("pickup_date")
      .validator(Validate.notEmpty())
      .validator(Validate.dateFormat("MM-DD-YYYY"))
      .getFormatter(Format.sqlDateToFormat("MM-DD-YYYY"))
      .setFormatter(Format.formatToSqlDate("MM-DD-YYYY")),
    new Field("receive_date")
      .validator(Validate.dateFormat("MM-DD-YYYY"))
      .getFormatter(Format.sqlDateToFormat("MM-DD-YYYY"))
      .setFormatter(Format.formatToSqlDate("MM-DD-YYYY")),
    new Field("dollar_amount")
      .validator(Validate.notEmpty())
      .validator(Validate.numeric()),
    new Field("litre_amount")
      .validator(Validate.notEmpty())
      .validator(Validate.numeric()),
    new Field("fuel_tax").validator(Validate.numeric()),
    new Field("paid").validator(Validate.numeric()),
    new Field("comment").validator(Validate.maxLen(500)),
    new Field("attachment").setFormatter(Format.ifEmpty(null)).upload(
      new Upload(__dirname + "/../www/uploads/{id}.{extn}")
        .db("attachments", "id", {
          filename: Upload.Db.FileName,
          filesize: Upload.Db.FileSize,
          web_path: "/uploads/{id}.{extn}",
          system_path: Upload.Db.SystemPath,
        })
        .validator(
          Validate.fileSize(1024 * 1024, "Files must be smaller than 1 MB")
        )
        .validator(
          Validate.fileExtensions(
            ["pdf"],
            "Only pdf files can be uploaded (*.pdf)"
          )
        )
        .dbClean(async function (data) {
          for (let i = 0, ien = data.length; i < ien; i++) {
            await unlink(data[i].system_path);
            //fs.rmSync(data[i].system_path);
          }
          return true;
        })
    )
  );

  await editor.process(req.body, req.files);
  res.json(editor.data());

allan

I think I might know what it is - does your id column auto increment (i.e. is it a serial)? It is marked as a bigint in the above but I'm not sure if the UI are are using there would show a serial differently or not.

If it does auto increment as a serial, then I suspect that is the issue.

If it is a serial, then the issue is that the drivers aren't returning the newly inserted row Id for some reason. If that is the case, could you add debug: true to your Knex database connection information object and show me the SQL debug it fires to the console when you upload a file please?

Thanks,
Allan

Clark S

hi Allan, here u go.

{
method: 'insert',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [ 'Invoice_33389540_10022022221139.pdf', 35240, '-', '-' ],
__knexQueryUid: 'l0lNCdD0eS_AEHZvhSaO0',
sql: 'insert into [attachments] ([filename], [filesize], [system_path], [web_path]) output inserted.[id] values (?, ?, ?, ?)',
returning: 'id'
}
{
method: 'update',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [
'/uploads/[object Object].pdf',
'/Users/xxxxxxxxxxxxx/Projects/shadowCMS/routes/../www/uploads/[object Object].pdf',
{ id: 33 }
],
__knexQueryUid: '1cRS7O-3Lk2wXmTIJEkHI',
@rowcount',
@rowcount'
}
08:51:34 info: Unhandled promise error: [object Promise]Error: update [attachments] set [web_path] = @p0, [system_path] = @p1 where [id] = @rowcount - Validation failed for parameter 'p2'. Invalid string.
08:51:34 info: stack: Error: update [attachments] set [web_path] = @p0, [system_path] = @p1 where [id] = @rowcount - Validation failed for parameter 'p2'. Invalid string.

and currently im using
** "knex": "^0.95.14" along with "mssql": "^8.0.2", which doesn't make any issue, however as long as I switch to knex 1.00+ then the error pops up when uploading. **

allan

Many thanks for that!

It looks like the error is being caused by this change in Knex and our use of the returning method for the upload.

I've committed this change to address that incompatibility.

I've also committed a typings change that is needed for Knex 1.

I'll package up an Editor release soon, but if you like you can grab the latest files from git.

Allan

Clark S

thanks Allan.