Invoke Nav table functions from SQL Insert

kevindstanley

I have an external application writing data directly into the Nav tables via a stored procedure and unfortunately this is the only way to accomplish this.

I need to invoke some sort of action in Nav to start the business logic against the inserted records from the stored procedure.

for example if i run this stored procedure

INSERT INTO NAVDB$TestTable (name,inserted)
VALUES ('Kevin', '');

I would like to have the code below in nav on this TestTable's onInsert() function run.

inserted := 'some name was inserted from stored procedure';

my needs are deeper than this use, but once i get the onInsert() or something comparable to work I would be extremely happy.

Thanks

Luc_VanDyck

That is not possible.

NAV business logic can only be executed from 'inside' NAV. Therefore, this code needs to be executed through NAV webservices or through the NAV Application Server.

kevindstanley

Ok, after much research I have come to that conclusion as well. Well at least that you can't directly invoke Nav functions(be it table functions or a codeunit) from SQL. I still do think it is possible.

I have seen examples of people calling nav webservices in Nav 2009 and using some SQL user defined CLR functions to invoke the webservice and some that use SQL jobs to invoke them.
http://mibuso.com/blogs/ara3n/2009/11/1 ... b-service/

What I cannot find is a more current example with Nav 2013 and Visual Studio 2012(this version of visual studio doesn't allow you to add a web reference at all in a SQL Server Database project)

I think it is possible to run a codeunit through a nav webservice called from a SQL record insert trigger using a SQL CLR User Defined Function.

so something like (SQL Code)

INSERT INTO [NAVDB].[NAVCompany$Table] (someValues);
EXEC some_user_defined_function(params);

that will call the SQL CLR function named some_user_defined_function(params)(C# inside of a SQL Server Database Project)

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString webService()
    {
           //somehow invoke webservice here
    }
}

I cannot seem to get a web reference attached to this to invoke it like i would in a normal C# project.

If anyone please has any ideas, I am being driven crazy trying to solve this problem. ](*,)
Thanks!!

Luc_VanDyck

kevindstanley wrote:

Ok, after much research I have come to that conclusion as well. Well at least that you can't directly invoke Nav functions(be it table functions or a codeunit) from SQL. I still do think it is possible.

No, it's not possible.

kevindstanley wrote:

I have seen examples of people calling nav webservices in Nav 2009 and using some SQL user defined CLR functions to invoke the webservice and some that use SQL jobs to invoke them.
http://mibuso.com/blogs/ara3n/2009/11/1 ... b-service/

That is possible, but that's something different then your first statement.

kapamarou

Can't you write your data into a staging table and use NAS to periodically read that data and insert it to you Actual table that will call the Nav TRIGGER?

zacr

kapamarou wrote:

Can't you write your data into a staging table and use NAS to periodically read that data and insert it to you Actual table that will call the Nav TRIGGER?

I've done exactly this for several clients and it works great.

kevindstanley

I am following up on this post to let people know what I have done to accomplish this, because I have yet to see and example out there.

I probably could have used NAS to periodically read the data and that would have been fine, but I wanted true real time data manipulation.

Below is my example.

I have DB_A that receives data often that needed to be sent when it is filled with data from a third party.
I have Nav_DB and a Nav_instance

I created a service in c# using Microsoft's SQL Dependency class to 'watch' for data changes in DB_A. When a record is inserted into DB_A I read the record that was written and send it no Nav_instance over webservices. I chose to use SQL Dependency over SQL CLR because of the simplicity and speed that SQL Dependency offers.

In my web services I insert the data that I sent and run a process to do something with that data.

The result is sub-second transactions from the time of the write into DB_A to when Nav processes the data.

Thank you all for your suggestions.