Code with SQL

aavio

hai friends...
i am new to sql,
i have a general doubt... is it possible to code with in sql tables(with sql), instead of using CAL in NAV :-k . :?: if its possible... which one is recommended more??? 8-[

thank you!

DenSter

All NAV business logic is programmed in C/AL

aavio

DenSter wrote:

All NAV business logic is programmed in C/AL

yes, but is it possible to code same logic with sql...??

ara3n

With several limitations, yes it's possible, but you will be wasting your time.

kine

aavio wrote:

DenSter wrote:

All NAV business logic is programmed in C/AL

yes, but is it possible to code same logic with sql...??

But do not forget that when the object is compiled in NAV, you can lost your changes, they can be in conflict with NAV or you can totally mess your database (e.g. when writing wrong data into CODE field in NAV etc.). I see the way of SQL only in some exceptional cases e.g. to create special view which can be connected into NAV as common table...

DenSter

aavio wrote:

DenSter wrote:

All NAV business logic is programmed in C/AL

yes, but is it possible to code same logic with sql...??

You would have to find a way to turn off the NAV code where you write SQL Code, the system will become a disastrous mess that is virtually unmanageable, and the support you get from MSFT will become null and void. All around this is a very bad idea, don't even go there.

stryk

Well, the question is what you mean by "code in sql tables"? The only "code" you could directly put in SQL Server tables is in SQL triggers, and generating customized triggers should happen only in very few/specific cases, due to the limitations/risks which were already mentioned here.

But: it might be smart to put some specific - custom - functions in SQL Stored Procedures, to benefit from the more powerful SQL features in querying, combining/joining and aggregating data! If there is not too much NAV business logic involved, this could remarkably improve your system!
These SP could be executed within NAV by using MS ADO as Automation Server; also the result set could be returned and processed inside NAV ...

So the short answer is: Yes, technically you could use SQL programming with NAV.
But if it's wise to it or not, depends on the actual purpose ...

aavio

thanks friends....
now i understood the issues while coding in database... and spl thanks to stryk, you had made my doubt more specific...ie with sql stored procedures will there be any problem with version change :?:

stryk wrote:

generating customized triggers should happen only in very few/specific cases, due to the limitations/risks which were already mentioned here.

and also can you specify those few/specific cases

Thanks a Lot 8)

stryk

If you mean changing the version of NAV, then No, there will be no impact on the stored procedures.
When changing the NAV version it could happen that tables are re-created and this could mean that customized sql trigger code is deleted.
But though the custom stored procedures are part of the NAV database, the C/SIDE environment is not able to drop or change themn, actually NAV is not even aware these SP exist.

Hence, with using MSADO to execute SP you just create an "exit & entry point" in/to NAV.

Speaking of changing the SQL Server version, then of course there could be an issue with the SP; e.g. if you are using TSQL commands which were removed in a newer version (yes, this happens frequently).

aavio

i actually asked for NAV version change, since SP are part of database there wont be any problem... sorry for my silly question :roll:

stryk wrote:

generating customized triggers should happen only in very few/specific cases,

can you please mention those few cases....

stryk

Well, I would suggest NOT to use custom triggers on tables which are heavily used by NAV transactions as this might slow them down or - in worst case - joepardizes the data-consistency (depends on what the rigger is doing).
Further, I would not add triggers to tables which already have a standard NAV SIFT Trigger.

For example, I used - experimental - a trigger on the Object table for some kind of auditing (http://dynamicsuser.net/blogs/stryk/archive/2009/05/18/object-auditing.aspx) ...

But then again: it always depends ...

DenSter

aavio wrote:

can you please mention those few cases....

Only in very VERY rare cases, where the NAV process proves to have performance issues or thins like that, and only for certain types of batch processes, NEVER for daily processing, NEVER for instance to post transactions or for field validation. For instance, I've heard that a certain type of cost adjustment was programmed as a stored procedure, but only because there wasn't enough time window available to run the NAV process itself. This was done in cooperation between very senior NAV people that know this process in and out, and very senior SQL people, and this was a project that took months to get right.

For general purposes, I do NOT agree that SQL programming should EVER be used AT ALL, especially when you don't know anything about NAV. Do not think, just because someone says "technically it is possible", that this means that you should start programming away in T-SQL. It is a road down to disaster.

aavio

hi,
okkkey ,..i understand the issues...i just want to clear my doubts....so creating triggers and stored procedures, are not recommended unless issue hav no other way...right??...ok...

Then just viewing data by qureying it will be ok naa... but in sql tables, i could not find the flowfields... why its soo...? :-k is it means that in sql database level tables doesnt maintain the relations between them... and only with finsql.exe application it will be maintained??? :shock:

kamranshehzad

Then just viewing data by qureying it will be ok naa... but in sql tables, i could not find the flowfields... why its soo...?

Flow fields are runtime fields and in database table, they are not part of database tables. System maintains them and can be used only in Navision UI (cal etc) only as far as I know.

May be some one else can put some more notes on it if it is possible to use it directly in database.

I was thinking to create some dataviews to populate some aggregated data in separate sql tables but still working on the best option.

It was a useful post for me. Cheers.

stryk

"kamranshehzad wrote:

Flow fields are runtime fields and in database table, they are not part of database tables. System maintains them and can be used only in Navision UI (cal etc) only as far as I know.

May be some one else can put some more notes on it if it is possible to use it directly in database.

I was thinking to create some dataviews to populate some aggregated data in separate sql tables but still working on the best option.

It was a useful post for me. Cheers.

Yes, FlowFields are just defined within C/SIDE and are not part of the SQL site table. The C/SIDE client creates sub-queries for the FlowFields, depending on the CalcFormula.

Here I publish a little tool which should create this sub-query TSQL: http://www.mibuso.com/dlinfo.asp?FileID=1101
(please refer to the related forum discussion about details)

Regards,
Jörg

ara3n

What are you trying to do in sql?

Format doesn't work on sql.