Knex and MSSQL Stored Procedures

rldean1

I've successfully installed, and I'm running, the DT Editor/Node libraries with our own production SQL server, using the demo files you've provided, and it's working

A lot of my work revolves around Stored Procedures.

Would there be a way to expand the example below so that the Fields being established are "bound" to the results of the Stored Procedure? How do I get the "Datatables Node Library" and KNEX to work with the results of the stored procedure?

In my journeys, I have established a "data model" using Express and Sequelize. My Express route uses Sequelize to run a specific Stored Procedure, and the results are returned as JSON.

So, I can create a result set in my Stored Procedure to match the Fields in the example below... but I don't know how to "instantiate" "Editor-Server" instances of those Fields.

KNEX appears to have a "raw" option, so theoretically, I could use it to run a SP: knexjs.org/#Raw

Thank you for any advice you may have.

router.all('/api/staff', async function(req, res) {
    let editor = new Editor(db, 'datatables_demo').fields(
        new Field('first_name').validator(Validate.notEmpty()),
        new Field('last_name').validator(Validate.notEmpty()),
        new Field('position'),
        new Field('office'),
        new Field('extn'),
        new Field('age')
            .validator(Validate.numeric())
            .setFormatter(Format.ifEmpty(null)),
        new Field('salary')
            .validator(Validate.numeric())
            .setFormatter(Format.ifEmpty(null)),
        new Field('start_date')
            .validator(
                Validate.dateFormat(
                    'YYYY-MM-DD',
                    null,
                    new Validate.Options({
                        message: 'Please enter a date in the format yyyy-mm-dd'
                    })
                )
            )
            .getFormatter(Format.sqlDateToFormat('YYYY-MM-DD'))
            .setFormatter(Format.formatToSqlDate('YYYY-MM-DD'))
    );

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

allan

Hi,

The issue here isn't just Knex's understanding of SQL, but also Editor's which is much more limited. Our Editor libraries need to be able to understand each field and will then use Knex to construct the SQL needed to update it.

I'm afraid that support does not extend to stored procedures. For that you would need to bypass our Node libraries entirely and just use Knex directly. The one plus point is that the client / server data interchange Editor uses is fully documented.

Sorry I don't have any better solutions to give you at the moment.

Allan

rldean1

@allan I have used that document extensively!

I'm wondering if the .NET libraries would be a better option --- I've looked into .NET Core and EFCore....

I should be able to run Stored Procedures in .NET Core/EFCore as well, but would I run into the same issues as with Node/Express? That is, trying to bind the results of of the SP to the Fields in the Editor library?

allan

Our NodeJS, PHP and .NET libraries are all basically feature matching (with some small tweaks for be suitable for each platform). The .NET libraries we provide also do not support stored procedures I'm afraid.

Allan

rldean1

@allan

So, I've been thinking about this a lot. I don't expect an answer, I just wish I knew how to approach this.

Often times, there is a difference between the data I want to display to the end user, and the actual data that needs to be edited.

So, yes, I could tie in KNEX, and the Editor libraries, and immediately get this project working, right this minute.

HOWEVER...

What I want to show, is not exactly the same as what is to be edited. I guess you _might _say: the "display Model" is not exactly the same as the "edit Model".

The "display" is generated from a ton of complicated queries in a Stored Procedure. But the "edit" is isolated to 1 table. (That's the table I could use KNEX with, right this minute... there would be no joins)

Example: there might be an option to "remove a person from the display"
* The "person" is populated from several different tables via complicated queries in a Stored Procedure
* Removing the person really only sets the "RemovedBy" and "RemovedOn" columns
* If those fields are empty, they are excluded in the result set from the aforementioned Stored Procedure

I'm still trying to learn about Models, Classes... and I'm trying to wrap my head around .net/EFCore as well.... (DTOs, dependency injection, etc etc)

From a 60,000 foot view, with how complicated some of these queries are, I just can't imagine using the built-in joins from KNEX or Sequelize.... it's just too complex for the.... but otherwise, it would be fine for simpler edits.

allan

Could you use a VIEW to get the data from your stored procedure?

You can certainly have DataTables fetch data from a different source from where Editor is submitting it (just point their respective ajax option at different end points). The only issue with that is that you need the data returned to the client for the Editor request in the same format as what was initially loaded in to the DataTable. For that you could use a server-side script to go an query the "get" endpoint.

So what you want to do is possible, it would just need a bit of tweaking over a "normal" script.

Allan