TableType::ExternalSQL

One very cool feature in NAV2016 is the table type “ExternalSQL”. In older versions you had to use a view in SQL for each table and set the property LinkedObject. Now you don’t have to do that any more.

Let’s take a look at it:

table

Here you can see how you can use TableType, ExternalName and ExternalSchema instead of LinkedObject. In NAV this table is called “My Table” but in SQL it’s called “mytable”.

You can also set ExternalName for fields. Below I want to use the field name “No.” although it’s called “number” in the SQL table.

field

 

The connection to the server, database and the user credentials is controlled with C/AL.

Here’s a sample:

UNREGISTERTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL, 'MyDatabaseName');

REGISTERTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL, 'MyDatabaseName', 'Data Source='MyServerName';Initial Catalog='MyDatabaseName';User ID='MySQLUserID';Password='MyPassword');

SETDEFAULTTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL, 'MyDatabaseName');

This code will first try to unregister any existing connection to the database I want to connect to. Then it will register using a connectionstring (which you of course shouldn’t hard code like this) and then it will set the connection as the default connection.

The connection needs to be established on each login so it would make sense to store server name, database name and credentials in a setup table and register the datasource on login.

According to the MSDN documentation any rollbacks will also take place in the external tables. I haven’t tested that myself though.

I’m on my first project using this feature so I’ll probably update this post later on when I’ve got some more time on this functionality.

4 Responses to “TableType::ExternalSQL”

  1. Michael 2016-06-06 at 13:37 #

    Hello,

    I think this a great Feature. But how do you deal with datatypes, that doesn’t exists in Dynamics NAV? Such as SQL-datatype “bit”

    Greetings

    • Lars Westman 2016-06-21 at 16:11 #

      Sorry for super late reply, but as far as I know you can only use datatapes supported in NAV. But you should be able to cast unsupported datataypes in a view in SQL and then use that view instead of a table.

  2. rj 2016-06-21 at 16:02 #

    Dear Lars,

    is it possible to create a page (list) based on ExternalSQL without creating a table (pulling ID) before?
    The requirement ist to create a page on XML (maybe XML->SQL->Page).

    Many Thanks

    • Lars Westman 2016-06-21 at 16:08 #

      Hi.

      You need a table declared as ExternalType=SQL. On that table you can then create a page.

Leave a Reply